Increase Timeout ?

I am using VB.net to execute a stored procedure... Sometimes I get a
timeout, other times I do not. How can I increase the time out ? Is
this a server setting ?Can;t help you on the timeout...but would it makes sense to look at the
performance of your procedure?
How long is the VB.net timeout currently?
--
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>|||That doesn't really sound like a "fix" to me at all. You can increase the
timeout to infinity, but users are still going to get bored at some point.
Have you considered looking at the stored procedure itself, and making it
faster, instead of trying to find ways to make its slowness more acceptable?
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>|||Hi,
The stored procedure is very straightforward. I am currently using an under
powered test server. I never get the timeout on the production server...
So how do I increase the timeout ?
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
> That doesn't really sound like a "fix" to me at all. You can increase the
> timeout to infinity, but users are still going to get bored at some point.
> Have you considered looking at the stored procedure itself, and making it
> faster, instead of trying to find ways to make its slowness more
> acceptable?
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>|||Rob
In VB sourcer when you connect to the server there's method called
CommandTimeout
Set Acn = New ADODB.Connection
Acn.CommandTimeout = 0
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>|||The default ADO.NET timeout is 30 seconds. This can be changed with the
SqlCommand object CommandTimeout property. For example:
myCommand.CommandTimeout = 60
However, I agree with the others that the timeout should be changed only
after ensuring proper index and query tuning is done. It could be that the
production server simply has enough power to compensate for inefficiencies.
Developing on an underpowered server can provide the motivation to improve
performance ;-)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
I just had to learn this one where I am. A query that basically returns the
DB as an XML feed (don't ask) now takes longer than 30 seconds. And even if
we improve it again, it's just going to get worse, again. Arrgh...
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks !
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob" <robc1@.yahoo.com> wrote in message
> news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
>> Hi,
>> The stored procedure is very straightforward. I am currently using an
>> under powered test server. I never get the timeout on the production
>> server...
>> So how do I increase the timeout ?
>> Thanks
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>>
>sql

Increase Timeout ?

I am using VB.net to execute a stored procedure... Sometimes I get a
timeout, other times I do not. How can I increase the time out ? Is
this a server setting ?
Can;t help you on the timeout...but would it makes sense to look at the
performance of your procedure?
How long is the VB.net timeout currently?
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com. ..
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>
|||That doesn't really sound like a "fix" to me at all. You can increase the
timeout to infinity, but users are still going to get bored at some point.
Have you considered looking at the stored procedure itself, and making it
faster, instead of trying to find ways to make its slowness more acceptable?
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com. ..
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>
|||Hi,
The stored procedure is very straightforward. I am currently using an under
powered test server. I never get the timeout on the production server...
So how do I increase the timeout ?
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
> That doesn't really sound like a "fix" to me at all. You can increase the
> timeout to infinity, but users are still going to get bored at some point.
> Have you considered looking at the stored procedure itself, and making it
> faster, instead of trying to find ways to make its slowness more
> acceptable?
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com. ..
>
|||Rob
In VB sourcer when you connect to the server there's method called
CommandTimeout
Set Acn = New ADODB.Connection
Acn.CommandTimeout = 0
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com. ..
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
|||The default ADO.NET timeout is 30 seconds. This can be changed with the
SqlCommand object CommandTimeout property. For example:
myCommand.CommandTimeout = 60
However, I agree with the others that the timeout should be changed only
after ensuring proper index and query tuning is done. It could be that the
production server simply has enough power to compensate for inefficiencies.
Developing on an underpowered server can provide the motivation to improve
performance ;-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com. ..
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
I just had to learn this one where I am. A query that basically returns the
DB as an XML feed (don't ask) now takes longer than 30 seconds. And even if
we improve it again, it's just going to get worse, again. Arrgh...

> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Thanks !
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob" <robc1@.yahoo.com> wrote in message
> news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com. ..
>

Increase nvarchar field value like a num

Hi everybody,
i want write a stored procedure. This is increase NVARCHAR (7) field like a
number.
Example :
A00001
A00002
:
:
A99999
B00001
:
:
Z99999
AA00001
:
ZZ99999
:
Error
How can i do ? can i do this with t-sql?
thanksUse an insert trigger
"SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
news:OA7jsm08FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Hi everybody,
> i want write a stored procedure. This is increase NVARCHAR (7) field like
> a number.
> Example :
> A00001
> A00002
> :
> :
> A99999
> B00001
> :
> :
> Z99999
> AA00001
> :
> ZZ99999
> :
> Error
>
> How can i do ? can i do this with t-sql?
> thanks
>|||Stored procedure must return a value
"Martin" <x@.y.z>, haber iletisinde unlar
yazd:ORRgvV18FHA.476@.TK2MSFTNGP15.phx.gbl...
> Use an insert trigger
> "SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
> news:OA7jsm08FHA.1248@.TK2MSFTNGP14.phx.gbl...
>|||The fastest way to to do this would be to use a lookup table; set a
bigint value to be the order-determinant (eg, 1, 2, 3,) and use the
other values as a lookup:
CREATE TABLE (ID bigint, Value NVARCHAR(7))
INSERT INTO TABLE (ID, Value)
--write a routine to populate this
VALUES (1, 'A00001')
Your stored procedure would then return the ID value bases on the
values you supply, increment the ID by one, and return the next value
in sequence. Kind of like a calendar table or a table of numbers.
HTH,
Stu|||SharkSpeed (sharkspeedtr@.yahoo.com) writes:
> i want write a stored procedure. This is increase NVARCHAR (7) field
> like a number.
> Example :
> A00001
> A00002
> :
> :
> A99999
> B00001
> :
> :
> Z99999
> AA00001
> :
> ZZ99999
> :
> Error
>
> How can i do ? can i do this with t-sql?
DECLARE @.letters varchar(2)
@.digits varchar(5)
SELECT @.digits = right(@.input, 5),
@.letters = substring(@.input, 1,
CASE len(@.input) WHEN 6 THEN 1 ELSE 2 END)
IF @.digits <> '99999'
BEGIN
SELECT @.digits = substring(convert(varchar(
convert(int, @.digits) + 100001)), 2, 5)
END
ELSE IF len(@.letters) = 1 and @.letters <> 'Z'
SELECT @.letters = char(ascii(@.letters) + 1))
ELSE IF @.letters = 'Z'
SELECT @.letters = 'AA'
ELSE IF @.letters NOT LIKE '_Z'
SELECT @.letters = substring(@.letters, 1, 1) +
char(ascii(substring(@.letters, 2, 1) + 1))
ELSE IF @.letters <> 'ZZ'
SELECT @.letters = char(ascii(substring(@.letters, 1, 1)) + 1) + 'A'
ELSE
RAISERROR ('Cannot compute a successor key to ZZ99999', 16, 1)
I did not test this, nor did I try to compile. You should be able to
make something out of it anyway.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In line with what Erland posted I had started on something.
I have now also tested so you could implement this as it is.. no
warranties though.
first create this table:
CREATE TABLE nextIDTable (preChar varchar(2), postInt int)
INSERT INTO nextIDTable (preChar, postInt) values ('A', 1)
Then once you have the table and inserted the values above you can
implement the stored procedure, calling this will increment the varchar
"number" as you described you wanted:
CREATE PROC getNextID
@.nextID nvarchar(7) OUTPUT
AS
DECLARE @.MyCounter INT, @.LeadingZeros char(4), @.preChar varchar(2),
@.postInt int
-- Initialize the variable.
SET @.MyCounter = 0
SET @.postInt = (SELECT postInt FROM nextIDTable)
SET @.preChar = (SELECT RTRIM(preChar) FROM nextIDTable)
IF(@.postInt < 10) SET @.LeadingZeros = '0000'
IF(@.postInt >= 10 AND @.postInt < 100) SET @.LeadingZeros = '000'
IF(@.postInt >= 100 AND @.postInt < 1000) SET @.LeadingZeros = '00'
IF(@.postInt >= 1000 AND @.postInt < 90001) SET @.LeadingZeros = '0'
WHILE (@.MyCounter <= 51)
BEGIN
-- the loop is exited when @.MyCounter reaches -1
-- as all from ZZ to A have been checked
IF @.MyCounter = -1 return
-- for A through to Z
IF(@.MyCounter <= 25)
BEGIN
IF(@.postInt = 99999 and @.preChar = 'Z')
BEGIN
SET @.nextID = 'AA00001'
UPDATE nextIDTable SET preChar = 'AA', postInt = 1
BREAK
END
IF(@.postInt = 99999 AND @.preChar <> 'Z')
BEGIN
IF(@.preChar = (CHAR(((@.MyCounter) + ASCII('A')))))
BEGIN
SET @.nextID = (CHAR(((@.MyCounter + 1) + ASCII('A')))) + '00001'
UPDATE nextIDTable SET preChar = CHAR(((@.MyCounter + 1) +
ASCII('A'))), postInt = 1
BREAK
END
END
ELSE
BEGIN
SET @.nextID = (@.preChar + RTRIM(@.LeadingZeros) + (CONVERT( char,
@.postInt)))
UPDATE nextIDTable SET postInt = postInt + 1
BREAK
END
END
-- for AA through to ZZ
IF(@.MyCounter > 25)
BEGIN
IF(@.postInt = 99999 AND @.preChar = 'ZZ')
BEGIN
-- reached the max value
RAISERROR('reached max val', 16, 1)
BREAK
END
IF(@.postInt = 99999 AND @.preChar <> 'ZZ' AND @.preChar NOT IN (select
preChar from nextIDTable where len(preChar) < 2))
BEGIN
-- next char sequence + 00001
SET @.nextID = CHAR(((@.MyCounter - 26) + ASCII('A'))) +
CHAR((@.MyCounter-26 + ASCII('A'))) + '00001'
UPDATE nextIDTable SET preChar = CHAR((@.MyCounter-26 + ASCII('A')))
+ CHAR((@.MyCounter-26 + ASCII('A'))), postInt = 1
BREAK
END
IF(@.postInt < 99999 AND @.preChar <> 'ZZ' AND @.preChar NOT IN (select
preChar from nextIDTable where len(preChar) < 2))
BEGIN
SET @.nextID = CHAR(((@.MyCounter-26) + ASCII('A'))) +
CHAR((@.MyCounter-26 + ASCII('A'))) + RTRIM(@.LeadingZeros) + CONVERT(
char, @.postInt)
UPDATE nextIDTable SET postInt = @.postInt + 1
BREAK
END
END
SET @.MyCounter = @.MyCounter + 1
END
GO
good luck with it..
Gerard|||actually I just found there is a wee bug in the part after
IF(@.MyCounter > 25)
if your value is AA99999 it will jump to GG00001 but I think there's
enough here to make this work
Gerard

increase CPU time

Hi All,
I've a problem regarding increased CPU time.....
I've a stored procedure which joins approx 6
table and i'm using table variable to hold data. Stored procedure is working
fine and absolutely OK but when it runs on LIVE server through Web
Application, It increase CPU time and increase READS Per runs.
i added SET NOCOUNT ON and SET NOCOUNT OFF
as well as i added WITH RECOMPILE option, but no use it is still giving too
much reads per run.
is anybody having solution of this problem? Please
reply me ASAP...
Sword is hanging on my neck ';' help me
Manish SukhijaManish
What reads? Logical?
How about indexes defined on the tables? Do you see the optimizer is
available to create an efficient execution plan , i mean it uses the
indexes?
Actually if want more accurate answer please post DDL+ Sample data
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:49ACC575-E519-4473-9B0C-E7337BF94B17@.microsoft.com...
> Hi All,
> I've a problem regarding increased CPU time.....
> I've a stored procedure which joins approx 6
> table and i'm using table variable to hold data. Stored procedure is
> working
> fine and absolutely OK but when it runs on LIVE server through Web
> Application, It increase CPU time and increase READS Per runs.
> i added SET NOCOUNT ON and SET NOCOUNT OFF
> as well as i added WITH RECOMPILE option, but no use it is still giving
> too
> much reads per run.
> is anybody having solution of this problem? Please
> reply me ASAP...
> Sword is hanging on my neck ';' help me
> Manish Sukhija|||Hi Uri,
as i defined that i've stored procedure which joins approximately 6
table and when it runs om live server and when it is being checked by hosted
party in Profiler it shows
database is producing a high amount of cpu usage as well as page
reads in a perticular Stored procedure...
then i put a index on a column of one of joined table,
it reduce some time, now i want to know that is this the only solution to
decrease CPU time or is there any other way around, if this is only way then
on which table and ofcourse on which column should i put index. As you know
Stored procedure joins 6 table and they are having so may columns.
so should i put index on all columns of 6
table but as fas as i know it's not best practise to put index on all column
s
it can make adverse afftect on performance.
make me right if i'm wrong... or is there any other
way to decrease CPU time in that Stored procedure.
If you want code of that Stored procedure
i'll send it to you...
Manish
"Uri Dimant" wrote:

> Manish
> What reads? Logical?
> How about indexes defined on the tables? Do you see the optimizer is
> available to create an efficient execution plan , i mean it uses the
> indexes?
> Actually if want more accurate answer please post DDL+ Sample data
>
>
>
> "Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in messag
e
> news:49ACC575-E519-4473-9B0C-E7337BF94B17@.microsoft.com...
>
>|||Manish
Do you have FOREIGN KEY constraints? Does the key of referncing table have
an index?
http://www.sql-server-performance.c...or_counters.asp
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:1A433AA4-D9A0-47E2-82A4-1EEB092DD27B@.microsoft.com...
> Hi Uri,
> as i defined that i've stored procedure which joins approximately
> 6
> table and when it runs om live server and when it is being checked by
> hosted
> party in Profiler it shows
> database is producing a high amount of cpu usage as well as page
> reads in a perticular Stored procedure...
> then i put a index on a column of one of joined table,
> it reduce some time, now i want to know that is this the only solution to
> decrease CPU time or is there any other way around, if this is only way
> then
> on which table and ofcourse on which column should i put index. As you
> know
> Stored procedure joins 6 table and they are having so may columns.
> so should i put index on all columns of 6
> table but as fas as i know it's not best practise to put index on all
> columns
> it can make adverse afftect on performance.
> make me right if i'm wrong... or is there any other
> way to decrease CPU time in that Stored procedure.
> If you want code of that Stored procedure
> i'll send it to you...
> Manish
>
> "Uri Dimant" wrote:
>|||You may want to look at DBCC SHOWCONTIG if you aren't already running some
kind of defrag maintenence on your indexes.
--
Regards,
Jamie
"Manish Sukhija" wrote:

> Hi All,
> I've a problem regarding increased CPU time.....
> I've a stored procedure which joins approx 6
> table and i'm using table variable to hold data. Stored procedure is worki
ng
> fine and absolutely OK but when it runs on LIVE server through Web
> Application, It increase CPU time and increase READS Per runs.
> i added SET NOCOUNT ON and SET NOCOUNT OFF
> as well as i added WITH RECOMPILE option, but no use it is still giving to
o
> much reads per run.
> is anybody having solution of this problem? Please
> reply me ASAP...
> Sword is hanging on my neck ';' help me
> Manish Sukhijasql

Incorrect value returned from Stored Procedure

I have an asp.net 1.1 website that uses sql server 2000 and vb.

I have a bit of a dilema, when I run a stored procedure in a webpage it returns the wrong value, but if I run it

in the query analyzer the correct value is returned.

 Dim orderHistory As nlb.OrdersDB = New nlb.OrdersDB ' Obtain Order ID from QueryString Dim OrderID As Integer = CInt(Request.Params("ID")) ' Get the customer ID too Dim myNewCustomerId As Integer = 0 myNewCustomerId = orderHistory.GetOrderCustomer(OrderID) Public Function GetOrderCustomer(ByVal orderID As Integer) As Integer ' Create Instance of Connection and Command Object Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim myCommand As SqlCommand = New SqlCommand("nlbsp_OrdersCustomerID", myConnection) ' Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure ' Add Parameters to SPROC Dim parameterOrderID As New SqlParameter("@.order_id", SqlDbType.Int, 4) parameterOrderID.Value = orderID myCommand.Parameters.Add(parameterOrderID) Dim parameterOrderCustID As New SqlParameter("@.customer_id", SqlDbType.Int, 4) parameterOrderCustID.Value = ParameterDirection.Output myCommand.Parameters.Add(parameterOrderCustID) 'Open the connection and execute the Command myConnection.Open() myCommand.ExecuteNonQuery() myConnection.Close() ' Return the customer_id (obtained as out paramter of SPROC) If parameterOrderCustID.Value <> 0 Then Return CInt(parameterOrderCustID.Value) Else Return 0 End If End Functionthe stored procdure isCREATE PROCEDURE [dbo].[nlbsp_OrdersCustomerID]( @.order_id int, @.customer_id int OUTPUT)AS/* Return the customer_id from the Orders. */SELECT @.customer_id = customer_id FROM nlb_Orders WHERE order_id = @.order_idGO

I know a particular order_id returns a value of 1. But when I run it in the webpage it always comes back as 2.

Any ideas would be appreciated

Thanks

Pete

Seems you made a small mistake:

parameterOrderCustID.Value = ParameterDirection.Output

It should be:

Dim CustomerID As Integer
parameterOrderCustID.Value = CustomerID
parameterOrderCustID.Direction=ParameterDirection.Output

Incorrect table Definitions

I am using Transactional replication and from time to time I get an Invalid
column error on the distribution agent for either a stored procedure or view.
When I take a look at the table definition script I see that it is missing
the newest columns that were added. Does anyone else have this problem?
Does anyone know what causes this and how to fix it? The only fix I've found
so far is to drop the table from replication and add it again and produce a
new snapshot and then it seems to see the new columns.
Where are these extra columns? On the Publisher or subscriber?
It sounds like they are on the subscriber, which means someone is changing
the schema there. Make schema changes on the publisher using
sp_repladdcolumn or sp_repldropcolumn.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jencis10" <jencis10@.discussions.microsoft.com> wrote in message
news:B8E2DAEF-629C-4BCD-B87B-33650C14B0B9@.microsoft.com...
> I am using Transactional replication and from time to time I get an
Invalid
> column error on the distribution agent for either a stored procedure or
view.
> When I take a look at the table definition script I see that it is
missing
> the newest columns that were added. Does anyone else have this problem?
> Does anyone know what causes this and how to fix it? The only fix I've
found
> so far is to drop the table from replication and add it again and produce
a
> new snapshot and then it seems to see the new columns.
|||The Extra columns are ones we added with scripts to the publisher database.
But then when we push a snapshot those new columns are not getting scripted.
I'm not sure why the replication script generator would generate the scripts
any differently than when you use Enterprise manager's script generating
tools, but they are not working the same.
"Hilary Cotter" wrote:

> Where are these extra columns? On the Publisher or subscriber?
> It sounds like they are on the subscriber, which means someone is changing
> the schema there. Make schema changes on the publisher using
> sp_repladdcolumn or sp_repldropcolumn.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "jencis10" <jencis10@.discussions.microsoft.com> wrote in message
> news:B8E2DAEF-629C-4BCD-B87B-33650C14B0B9@.microsoft.com...
> Invalid
> view.
> missing
> found
> a
>
>
|||Reply at bottom.
"jencis10" <jencis10@.discussions.microsoft.com> wrote in message
news:797A9487-C13E-4746-B594-518CA0757521@.microsoft.com...[vbcol=seagreen]
> The Extra columns are ones we added with scripts to the publisher
> database.
> But then when we push a snapshot those new columns are not getting
> scripted.
> I'm not sure why the replication script generator would generate the
> scripts
> any differently than when you use Enterprise manager's script generating
> tools, but they are not working the same.
> "Hilary Cotter" wrote:
Don't you have to mark the replication for reinitialistion prior to creating
the new snapshot? I'm still a beginner on the replication side (well, most
of SQL Server :P), but whenever I make changes to publication properties the
EM dialog always points out that the publication has to be reinitialised, so
I'd assume that for the snapshot agent to pick up changes to the schema the
same thing would need to be done.
Dan
|||Yes, you do have to reinitialize and I am doing that as well. Basically we
drop the article (table) from both the subscription and publication, then I
modify the table structure, add the table back into the publication and
subscription and then reinitialize the subscription. Then I push the new
snapshot and look at the generated files and see that the table was not
scripted with the new columns.
"Daniel Crichton" wrote:

> Reply at bottom.
> "jencis10" <jencis10@.discussions.microsoft.com> wrote in message
> news:797A9487-C13E-4746-B594-518CA0757521@.microsoft.com...
> Don't you have to mark the replication for reinitialistion prior to creating
> the new snapshot? I'm still a beginner on the replication side (well, most
> of SQL Server :P), but whenever I make changes to publication properties the
> EM dialog always points out that the publication has to be reinitialised, so
> I'd assume that for the snapshot agent to pick up changes to the schema the
> same thing would need to be done.
> Dan
>
>
|||"jencis10" <jencis10@.discussions.microsoft.com> wrote in message
news:5EA098FA-2F35-421C-B426-038EF2AC1158@.microsoft.com...
> Yes, you do have to reinitialize and I am doing that as well. Basically
> we
> drop the article (table) from both the subscription and publication, then
> I
> modify the table structure, add the table back into the publication and
> subscription and then reinitialize the subscription. Then I push the new
> snapshot and look at the generated files and see that the table was not
> scripted with the new columns.
Oh well, that's my involvement finished then - so far I've not needed to
modify any replicated tables, and as everything is still in development I'd
likely be lazy and use EM to rebuild them anyway. Sorry.
Dan

Incorrect Syntax using IF statement

Hi,

I'm new to SQL Server Programming, I work with ASP a lot, but lately
I've been trying to create Stored Procedures, etc. I'm having a
problem writing a simple IF statement.. I don't seem to understand why
it's giving me this error. I've search around on Google Groups, but I
still don't get it.

=================
USE msdb

IF NOT EXISTS (SELECT * FROM sysjobs WHERE name = 'Scheduled Nightfax')

END
=================

My error is:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'END'.

Thanks for any help.you need a BEGIN for every END
example

DECLARE @.v BIT
SELECT @.v = 1

IF @.v = 1
BEGIN
select 'yes'
END
ELSE
BEGIN
select 'No'
END

Or without begin...end
IF @.v = 1
select 'yes'
ELSE
select 'No'

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks, I played with it a bit and I got the result I was looking for.

==============
USE msdb
DECLARE @.JobName varchar(255)
SELECT @.JobName = name FROM sysjobs WHERE name = 'Scheduled Nightfax'

IF @.JobName = 'Scheduled Nightfax'
PRINT 'YES'
ELSE
PRINT 'NO'
==============

Incorrect syntax near the keyword 'OR'.

Hi,
I have a stored procedure
CREATE PROCEDURE dbo.Retrieve
(
@.SEARCH_STRING nvarchar(200),
@.COUNT int
)
AS
DECLARE @.STRING_COUNT varchar(3)
DECLARE @.SQL varchar(1000)
SET @.STRING_COUNT = CAST(@.COUNT AS varchar(3))
SET @.SQL='SELECT TOP ' + @.STRING_COUNT + '[ID] FROM [EMPLOYEES]
WHERE ([NAME] LIKE ' + @.SEARCH_STRING + '% OR [EMPLOYEE_REFERENCE] LIKE ' +
@.SEARCH_STRING + '% )'
EXEC (@.SQL)
The stored procedure is created successfully.
But I get the error when I try to use it: (Retrieve '',10)
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'OR'.
Thanks
KiranAnswered in .programming. Please don't multi-post.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Kiran" <Kiran@.nospam.net> wrote in message
news:O9oiPrX#EHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a stored procedure
> CREATE PROCEDURE dbo.Retrieve
> (
> @.SEARCH_STRING nvarchar(200),
> @.COUNT int
> )
> AS
> DECLARE @.STRING_COUNT varchar(3)
> DECLARE @.SQL varchar(1000)
>
> SET @.STRING_COUNT = CAST(@.COUNT AS varchar(3))
> SET @.SQL='SELECT TOP ' + @.STRING_COUNT + '[ID] FROM [EMPLOYEES]
> WHERE ([NAME] LIKE ' + @.SEARCH_STRING + '% OR [EMPLOYEE_REFERENCE] LIKE '
+
> @.SEARCH_STRING + '% )'
> EXEC (@.SQL)
> The stored procedure is created successfully.
> But I get the error when I try to use it: (Retrieve '',10)
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'OR'.
>
> Thanks
> Kiran
>sql

Incorrect syntax near the keyword ELSE.

Hi,

I have written a stored procedure to add the records to the table in DB from the report I generate, but the sored procedure gives me this error:

Incorrect syntax near the keyword 'ELSE'.

I am using Sql Server 2005.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate](
@.ReportDate smalldatetime,
@.SiteID int,
@.DataFeedID int,
@.FromCode varchar,
@.Sent int,
@.Delivered int,
@.TotalOpens REAL,
@.UniqueUserOpens REAL,
@.UniqueUserMessageClicks REAL,
@.Unsubscribes REAL,
@.Bounces REAL,
@.UniqueUserLinkClicks REAL,
@.TotalLinkClicks REAL,
@.SpamComplaints int,
@.Cost int
)
AS
DECLARE @.PKID INT
DECLARE @.TagID INT

SELECT @.TagID=ID FROM Tag WHERE SiteID=@.SiteID AND FromCode=@.FromCode

SELECT @.PKID=PKID FROM DimTag
WHERE TagID=@.TagID AND StartDate<=@.ReportDate AND @.ReportDate< ISNULL(EndDate,'12/31/2050')
IF @.PKID IS NULL BEGIN
SELECT TOP 1 @.PKID=PKID FROM DimTag WHERE TagID=@.TagID AND SiteID=@.SiteID
END

DECLARE @.LastReportDate smalldatetime, @.LastSent INT, @.LastDelivered INT, @.LastTotalOpens Real,
@.LastUniqueUserOpens Real, @.LastUniqueUserMessageClicks Real, @.LastUniqueUserLinkClicks Real,
@.LastTotalLinkClicks Real, @.LastUnsubscribes Real, @.LastBounces Real, @.LastSpamComplaints INT, @.LastCost INT

SELECT @.Sent=@.Sent-Sent,@.Delivered=@.Delivered-Delivered,@.TotalOpens=@.TotalOpens-TotalOpens,
@.UniqueUserOpens=@.UniqueUserOpens-UniqueUserOpens,@.UniqueUserMessageClicks=@.UniqueUserMessageClicks-UniqueUserMessageClicks,
@.UniqueUserLinkClicks=@.UniqueUserLinkClicks-UniqueUserLinkClicks,@.TotalLinkClicks=@.TotalLinkClicks-TotalLinkClicks,
@.Unsubscribes=@.Unsubscribes-Unsubscribes,@.Bounces=@.Bounces-Bounces,@.SpamComplaints=@.SpamComplaints-SpamComplaints,
@.Cost=@.Cost-Cost
FROM CrmPublisherSummary
WHERE @.LastReportDate < @.ReportDate
AND SiteID=@.SiteID
AND TagPKID=@.PKID

UPDATE CrmPublisherSummary SET
Sent=@.Sent,
Delivered=@.Delivered,
TotalOpens=@.TotalOpens,
UniqueUserOpens=@.UniqueUserOpens,
UniqueUserMessageClicks=@.UniqueUserMessageClicks,
UniqueUserLinkClicks=@.UniqueUserLinkClicks,
TotalLinkClicks=@.TotalLinkClicks,
Unsubscribes=@.Unsubscribes,
Bounces=@.Bounces,
SpamComplaints=@.SpamComplaints,
Cost=@.Cost
WHERE ReportDate=@.ReportDate
AND SiteID=@.SiteID
AND TagPKID=@.PKID

ELSE
SET NOCOUNT ON

INSERT INTO CrmPublisherSummary(
ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens, UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes,
Bounces, SpamComplaints, Cost, DataFeedID, TagID)

SELECT
@.ReportDate,
@.SiteID,
@.PKID,
@.Sent,
@.Delivered,
@.TotalOpens,
@.UniqueUserOpens,
@.UniqueUserMessageClicks,
@.UniqueUserLinkClicks,
@.TotalLinkClicks,
@.Unsubscribes,
@.Bounces,
@.SpamComplaints,
@.Cost,
@.DataFeedID,
@.TagID

SET NOCOUNT OFF

Hi,

I think you find that the End Statement must be immediately before the Else (IF... BEGIN...END ELSE). Certainly if you move the END to just before the ELSE the SP compiles OK.

Hope this helps,

Paul

|||

How could I miss that.

Thanks a lot!!!

|||

No problems, sometimes these things just need a fresh pair of eyes.

Cheers

|||Try
ALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate](
@.ReportDate smalldatetime,
@.SiteID int,
@.DataFeedID int,
@.FromCode varchar,
@.Sent int,
@.Delivered int,
@.TotalOpens REAL,
@.UniqueUserOpens REAL,
@.UniqueUserMessageClicks REAL,
@.Unsubscribes REAL,
@.Bounces REAL,
@.UniqueUserLinkClicks REAL,
@.TotalLinkClicks REAL,
@.SpamComplaints int,
@.Cost int
)
AS
SET NOCOUNT ON -- moved this
DECLARE @.PKID INT
DECLARE @.TagID INT
SELECT @.TagID=ID FROM Tag WHERE SiteID=@.SiteID AND FromCode=@.FromCode
SELECT @.PKID=PKID FROM DimTag
WHERE TagID=@.TagID AND StartDate<=@.ReportDate AND @.ReportDate< ISNULL(EndDate,'12/31/2050')
IF @.PKID IS NULL BEGIN
SELECT TOP 1 @.PKID=PKID FROM DimTag WHERE TagID=@.TagID AND SiteID=@.SiteID
DECLARE @.LastReportDate smalldatetime, @.LastSent INT, @.LastDelivered INT, @.LastTotalOpens Real,
@.LastUniqueUserOpens Real, @.LastUniqueUserMessageClicks Real, @.LastUniqueUserLinkClicks Real,
@.LastTotalLinkClicks Real, @.LastUnsubscribes Real, @.LastBounces Real, @.LastSpamComplaints INT, @.LastCost INT
SELECT @.Sent=@.Sent-Sent,@.Delivered=@.Delivered-Delivered,@.TotalOpens=@.TotalOpens-TotalOpens,
@.UniqueUserOpens = @.UniqueUserOpens-UniqueUserOpens,
@.UniqueUserMessageClicks = @.UniqueUserMessageClicks-UniqueUserMessageClicks,
@.UniqueUserLinkClicks = @.UniqueUserLinkClicks-UniqueUserLinkClicks,
@.TotalLinkClicks = @.TotalLinkClicks-TotalLinkClicks,
@.Unsubscribes = @.Unsubscribes-Unsubscribes,
@.Bounces = @.Bounces-Bounces,
@.SpamComplaints = @.SpamComplaints-SpamComplaints,
@.Cost = @.Cost-Cost
FROM CrmPublisherSummary
WHERE @.LastReportDate < @.ReportDate AND SiteID=@.SiteID AND TagPKID=@.PKID
UPDATE CrmPublisherSummary SET
Sent=@.Sent,
Delivered=@.Delivered,
TotalOpens=@.TotalOpens,
UniqueUserOpens=@.UniqueUserOpens,
UniqueUserMessageClicks=@.UniqueUserMessageClicks,
UniqueUserLinkClicks=@.UniqueUserLinkClicks,
TotalLinkClicks=@.TotalLinkClicks,
Unsubscribes=@.Unsubscribes,
Bounces=@.Bounces,
SpamComplaints=@.SpamComplaints,
Cost=@.Cost
WHERE ReportDate=@.ReportDate AND SiteID=@.SiteID AND TagPKID=@.PKID
END
ELSE
INSERT INTO CrmPublisherSummary(
ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens,
UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes,
Bounces, SpamComplaints, Cost, DataFeedID, TagID)
VALUES ( -- Should be values
@.ReportDate, @.SiteID, @.PKID, @.Sent, @.Delivered, @.TotalOpens, @.UniqueUserOpens,
@.UniqueUserMessageClicks, @.UniqueUserLinkClicks, @.TotalLinkClicks, @.Unsubscribes,
@.Bounces, @.SpamComplaints, @.Cost, @.DataFeedID, @.TagID)
SET NOCOUNT OFF|||You have an ELSE with no matching IF.

Incorrect syntax near my_stored_procedure

I have a stored procedure (sp) named 'ins_MemberPayment'. This procedure creates a record of a member's payment in the MemberPayments table. The sp looks like this:
___
CREATE PROCEDURE ins_MemberPayment
(@.MemberId VarChar(10),
@.CCNum Char(4),
@.Amount smallmoney)
AS

INSERT INTO MemberPayments
(MemberId, PaymentDate, CCNum, Amount)
VALUES
(@.MemberId, GetDate(), @.CCNum, @.Amount)
GO
--

The ASP.NET page looks like this:
___
Dim UserName As String = txtUserName.Text 'hotrodjimmy73
Dim CreditCard As String = Trim(txtCreditCard.Text) '5464655458776221
Dim Amount As String = "1.00"

Dim cnn As New SqlConnection(Application("SQLConnectionString"))
Dim trans As SqlTransaction
Dim cmd2 As New SqlCommand(dbo() & "ins_MemberPayment", cnn, trans)
cmd.CommandType = CommandType.StoredProcedure

With cmd2.Parameters
.Add("@.MemberID", UserName)
.Add("@.CCNum", Right(CreditCard, 4))
.Add("@.Amount", Convert.ToDecimal(Amount))
End With

cnn.Open()
cmd2.ExecuteNonQuery()
cnn.Close()
--
When I run the page, I get the error:
___
Incorrect syntax near 'ins_MemberPayment'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'ins_MemberPayment'.

Source Error:

Line 261: cmd2.ExecuteNonQuery()
--

Does anybody see the error? I'm blind to it after looking for a couple hours now.In this line

Dim cmd2 As New SqlCommand(dbo() & "ins_MemberPayment", cnn, trans)

what is the purpose of the dbo() function call?

Terri|||What does dbo() evaluate to? Does it translate to "dbo."?

Try this alternative syntax and see if it works.

Dim cm as SqlCommand = New SqlCommand()
cm.Connection= cnn
cm.CommandType= CommandType.StoredProcedure
cm.CommandText= "ins_MemberPayment"
cm.Transaction = trans

|||Oh, sorry. I should have simplified that for the purpose of posting to the forum. The dbo function call simply returns a conditional string: "dbo." if the page is running in it's production environment, and "" if it is running on my local development machine.

It's not relevant for the question at hand.|||I didn't want to "lead the witness" by originally saying that I have a hunch that the problem has something to do with smallmoney and string conversion. But now that I've tested this in Query Analyzer, I'm pretty sure this is the case.

I can get the procedure to work when passing it a value of 12 or 12.00, but as soon as I put single quotes around it, I get the error:

Implicit conversion from data type varchar to smallmoney is not allowed. Use the CONVERT function to run this query.

If I hope to accomplish the conversion in ASP.NET before sending the value to SQL Server, what do I need to convert it too? Int16, Int32, Double?

What's the "RIGHT" data type to convert to?|||The right .NET Framework data type is SQLMoney. (Here's across reference map of SQL Server and .NET Framework data types).

I suggest adding your parameters as such:

With cmd2.Parameters
 .Add("@.MemberID", SqlDbType.Varchar, 10, UserName)
 .Add("@.CCNum", SqlDbType.Char,4,Right(CreditCard, 4))
 .Add("@.Amount", SqlDbType.SmallMoney,4,System.Data.SqlTypes.SqlMoney.Parse(Amount))
End With

Terri|||Using your code I get:

Value of type 'System.Data.SqlTypes.SqlMoney' cannot be converted to 'String'.|||Going back to your originally supplied code, change this:

cmd.CommandType = CommandType.StoredProcedure

to this:

cmd2.CommandType = CommandType.StoredProcedure

Terri|||Oops. Thanks for the fresh pair of eyes. That did one good thing for me; It allowed me to get more accurate errors returned.

Now it works as:

.Add("@.Amount", SqlTypes.SqlMoney.Parse(Amount))

But not as:

.Add("@.Amount", SqlDbType.SmallMoney,4,System.Data.SqlTypes.SqlMoney.Parse(Amount))

Hmm. Strange. Oh, well. It works for my purposes. THANKS!!|||I normally don't add my parameters that way, so I am sure I provided you with syntactical errors :-(

But I am glad you got it working now!

Terri

Incorrect syntax near LEFT

Hi guys;

I am trying to create a stored procedure in my database with dynamic filter, how ever i cant even let my query work.

When i run my stored procedure i get this errorIncorrect syntax near 'LEFT'

I dnt knw wats wrong with my code.

Please help me...
below is my stored procedure..

Thanks is advance.

ALTER PROCEDURE dbo.GetClassByCustomFilter
@.pcCustomFilterNVARCHAR(500)=''
AS
DECLARE @.sSqlString nvarchar(1024)
SET @.sSqlString = 'SELECT R.cRoomNo, P.cFirstName, P.cLastName, P.cMiddleName, U.cCode AS cSubjectCode, U.cName AS cSubjectName, '
SET @.sSqlString = @.sSqlString + 'U.cDescription AS cSubjectDescription, B.cCode AS cSection, H.cName AS cRecurenceName, C.iClassID, C.iInstructorID, C.iSubjectid, C.iEnrollmentID, '
SET @.sSqlString = @.sSqlString + 'C.iRecordTypeID, C.iBlockSectionID, C.cCode, C.cType, C.iRoomID, C.cRecurrence, CAST(CONVERT(nvarchar, GETDATE(), 1) + '' '' + CONVERT(nvarchar, '
SET @.sSqlString = @.sSqlString + 'C.tStartTime,8) AS datetime) AS tStartTime, CAST(CONVERT(nvarchar, GETDATE(), 1) + '' '' + CONVERT(nvarchar, C.tEndTime,8) AS datetime) '
SET @.sSqlString = @.sSqlString + 'AS tEndTime, C.fUnits, C.nAllowed, C.nMaxAllowed, C.mNotes, C.tCreated, C.tEdited, C.iEditedBy, C.iCreatedby, C.adGUID '
SET @.sSqlString = @.sSqlString + 'FROM dbo.PERSONALINFO P INNER JOIN dbo.INSTRUCTORS I ON P.iPersonalInfoId = I.iPersonalInfoID RIGHT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.CLASSES C INNER JOIN dbo.BLOCKSECTIONS B ON C.iBlockSectionID = B.iBlockSectionId INNER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.SUBJECTS U ON C.iSubjectid = U.iSubjectID LEFT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.SCHEDULERECURRENCE H ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID LEFT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.ROOMS R ON C.iRoomID = R.iRoomId WHERE (C.cType <> ''0'')'
IF LEN(@.pcCustomFilter) > 0
BEGIN
SET@.sSqlString = @.sSqlString + ' AND ' + @.pcCustomFilter
END
EXEC sp_executesql @.sSqlString

Well, thanks for reminding me why I hate complex dynamic SQL strings.

Thisa line is causing you problems:

SET @.sSqlString = @.sSqlString + 'dbo.SCHEDULERECURRENCE H ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID LEFT OUTER JOIN '

Note the:ON C.cRecurrence = H.cRecurrenceON I.iInstructorID = C.iInstructorID (two ON clauses for a single JOIN clause).|||Thanks for the reply .

The Query is correct.

I get this error because the lenght of the variable@.sSqlString is only 1024 but my query string is longer than that.

Regards|||Well, be that as it may, having to ON statements for a single JOIN clause will give you a syntax error...|||Of course it will. But the SQL statement below has 6JOIN and 6ON.

enewe thanks 4 the time.

Regards.|||The following, cut and pasted from you original post, is what I'm referencing:

SET @.sSqlString = @.sSqlString + 'dbo.SUBJECTS U ON C.iSubjectid = U.iSubjectID LEFT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.SCHEDULERECURRENCE H ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID LEFT OUTER JOIN '

Note the 'ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID' of the second line. That will cause issues.

However, I'm glad you got it working...|||Of course it wont work bcoz its just a part of the statement, not the whole statement. :) jst Kid'N. But I am 101% sure that the SQL Statement below will work because I just copied it from Visual Studio .NET View Designer.

8)

Incorrect syntax near 'E' in SQL Server 2005

We have over 20 stored procedures and they all seem to work fine.
Every once in a while one of the stored procedures will glitch and
return the error, "Incorrect syntax near 'E'." One w it will be one
stored procedure. Another w it will be another. We try to 'solve'
the problem by dropping and recreating the procedure and it works fine
again. (Note that we did not change any syntax in the stored
procedure.) After we recreate the stored procedure it will work a few
times and then it will stop working. It's appears random and we cannot
isolate what is causing it. Simply running it a few times can cause
it.
One time we found out that playing around with the indexes of a column
caused the error (which is very strange.) We have no clue what is
going on.
So now I am attempting to debug it in Visual Studio by attaching a
process to the sqlservr.exe.
Does anyone out there have any idea what is going on? I truly believe
it is a SQL Server bug and not on our end because it is so random and
the syntax of the sp has nothing to do with the error.If this is reproducible, can you post one culprit stored procedure & the
structures of the tables involved?
Anith|||After ws of this issue we have found a solution. Here is the
problem and the solution:
Problem: I have two stored procedures. One (p1) does inserts, and
updates. The second one (p2) just selects. p1 is run before p2. If I
run p1 multiple times before p1 finishes, it will mess up p2 with the
error message (Incorrect syntax near 'E'). This is crazy because the
syntax for p2 never changes.
Solution: For p1 add "WITH RECOMPILE" to the stored procedure. It
forces p1 to compile everytime. Seems to work.

Incorrect syntax near .

I try to use stored procedure to do a search for Email column. But I always get a error "Incorrect syntax near '.'", when I input keyword like "ash11.tw." And if I put keyword without dot, it works fine. It seems like the dot caused the problem. But I dont know how to fix it. Can someone help me out? Thanks.

My where expression is following.

@.spWhereSearch = 'where cont.Email like "%' + @.Email + '%"'

Try this

@.spWhereSearch = Where cont.Email Like "'% + @.Email + %'"|||

Probably try this

@.spWhereSearch = 'where cont.Email like %' + @.Email + '%'

|||

Change the double quotes " in your where search to two single quotes '' and that should fix your issue.

|||

Thanks for answering.

I have tried above three but they still dont work.

The error is still the same.

Does anyone have other suggestions?

|||If you show us the code then it would be more helpful. I dont see any reason why this would fail

@.spWhereSearch = Where cont.Email Like '% + @.Email + %'|||That line needs to look *exactly* like this (which was Motley's suggestion and is the resolution to your prolem):

@.spWhereSearch = 'where cont.Email like ''%' + @.Email + '%'''

|||

Thanks for replying. it works!!!

tmorton and Motley's suggestion is correct.

Thanks again.

Incorrect syntax near ')' SQL 2000

Hi

I keep receiving the error message Incorrect syntax near ')' whilst trying to save a stored procedure from within visual studio, I get the same error from within enterprise manager. The procedure is incomplete but valid, any idea appreciated. The procedure follows:

CREATE PROCEDURE dbo.ProcessComment
@.source AS VARCHAR(50) = NULL

AS
SET NOCOUNT ON

DECLARE @.sourceID AS INT
DECLARE @.counter AS INT
DECLARE @.Sources TABLE
(
sourceid AS INT,
lastconversation AS DATETIME
)

SET @.sourceID = -1
SET @.counter = -1

/*We need to see if this is a new or existing source*/
IF NOT @.source = NULL
BEGIN

INSERT @.Sources SELECT sourceID, lastconversation
FROM sources
WHERE name = @.source
ORDER BY lastconversation

--Do we have any matching sources or is this a new source?

--We need to pick the most likely source from the table
--The most likely source will be either one with an open conversation
--or the latest conversation or if we are lucky the only
--one in the result set

END

Thanks

Gav:

These two lines:

sourceid AS INT,
lastconversation AS DATETIME

need to be changed to:

sourceid AS INT,
lastconversation AS DATETIME

The compiler "thinks" that you are defining a computed column when you include the "AS" keyword.

Dave

|||Thanks Mugambo, that sorted me out :)

I wonder where I picked up that 'AS' syntax from in a table declaration, it just rolled off my fingertips onto the keyboard.

Thanks a lot.sql

Incorrect parameters being received by SQL Server Stored Procedure ...

Hi Everybuddy,
I have a strange problem with VB6/SQL Server 2K. I am calling a stored
procedure from VB using ADODB.Command object. When I check the incomming
parameters into the stored procedure, that values in named parameters are
inter-changed (e.g. : Calling ABC(X=3, Y=7, Z=12) will result into- X=12,
Y=3 and Z=7 inside the procedure ABC).
I am totally having no clue of whats making this wierd situation. The code
snippet of Caller (VB 6.0) and Callee (SQL Server 2000 SP4, Stored
Procedire) are below.
[VB Code]
Set cnBuilty = New ADODB.Connection
Set cmdBuilty = New ADODB.Command
cnBuilty.CursorLocation = adUseClient
cnBuilty.Open APP_StdConnectionString
'cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.RETURN_VALUE",
adInteger, adParamReturnValue, 4, nRetVal)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLR_No",
adInteger, adParamInput, 4, in_LR_No)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nDestinationId",
adInteger, adParamInput, 4, in_Town_ID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsignorId",
adInteger, adParamInput, 4, in_SenderID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsigneeId",
adInteger, adParamInput, 4, in_ReceiverID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMBT",
adInteger, adParamInput, 4, in_MBT)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMST",
adInteger, adParamInput, 4, in_MST)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLH_FL",
adInteger, adParamInput, 4, in_LH_FL)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nCBB",
adInteger, adParamInput, 4, in_CBB)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nSCBB",
adInteger, adParamInput, 4, in_SCBB)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMilkCaret",
adInteger, adParamInput, 4, in_Milk_Crt)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nPOP",
adInteger, adParamInput, 4, in_POP)
cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@.nConsignmentType", adInteger, adParamInput, 4,
in_ConsignmentType)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nInvoiceType",
adTinyInt, adParamInput, 1, in_InvType)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtInv_Date",
adDBTimeStamp, adParamInput, 8, in_LR_Date)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtLR_Date",
adDBTimeStamp, adParamInput, 8, in_LR_Date)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szIsCancelled",
adVarChar, adParamInput, 1, "")
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szInvoiceId",
adVarChar, adParamInput, 20, Trim(in_InvoiceNo))
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szClubbedId",
adVarChar, adParamInput, 20, Trim(in_ClubbedWithId))
cmdBuilty.Name = "InsertConsignmentRecord"
cmdBuilty.ActiveConnection = cnBuilty
cmdBuilty.CommandText = "InsertConsignmentRecord"
cmdBuilty.CommandType = adCmdStoredProc
cmdBuilty.CommandTimeout = 45
Set rsBuilty = cmdBuilty.Execute()
'nRetVal = cmdBuilty.Parameters("@.RETURN_VALUE")
Dim qq As Integer
For qq = 0 To (rsBuilty.Fields.Count - 1) Step 1
Debug.Print rsBuilty.Fields(qq).Name & " = " & rsBuilty.Fields(qq)
Next qq
[Stored Procedure Code]
CREATE PROCEDURE dbo.InsertConsignmentRecord
@.nLR_No As Int,
@.dtLR_Date As DateTime,
@.nDestinationId As Int,
@.nConsignorId As Int,
@.nConsigneeId As Int,
@.nMBT As Int,
@.nMST As Int,
@.nLH_FL As Int,
@.nCBB As Int,
@.nSCBB As Int,
@.nMilkCaret As Int,
@.nPOP As Int,
@.dtInv_Date As DateTime,
@.szInvoiceId As VarChar(21),
@.szIsCancelled As VarChar(1),
@.nInvoiceType As TinyInt,
@.nConsignmentType As Int,
@.szClubbedId As VarChar(21)
AS
-- Problem: Received value is different from what is passed ...
-- Received- InvoiceType=03 Oct 2006; MST=3; CBB=1; SCBB=0; LH=2;
ConsignmentType=36528001;
-- Originally Passed: Says- InvoiceType=1; MST=0; CBB=2; SCBB=1; LH=3;
ConsignmentType=1;
Select @.nInvoiceType As "InvoiceType", @.nMBT As "MBT", @.nMST As "MST",
@.nLH_FL As "LH", @.nCBB As "CBB", @.nSCBB As "SCBB", @.nConsignmentType As
"ConsignmentType", @.szInvoiceId As "InvoiceId", @.szClubbedId As "ClubbedId"
-- Insert Into Consignment_Note
-- ( LR_NO, LR_DATE, DESTINATION_ID, CONSIGNOR_CODE, CONSIGNEE_CODE,
-- MBT, MST, CBB, LH_FL, SCBB, MILK_CRT, POP,
-- INVOICE_NO, INV_DATE, IS_CANCELLED,
-- INVTYPE, CONSIGNMENT_TYPE, CLUBBED_ID)
-- Values
-- ( @.nLR_No,@.dtLR_Date, @.nDestinationId, @.nConsignorId, @.nConsigneeId,
-- @.nMBT, @.nMST, @.nCBB, @.nLH_FL, @.nSCBB, @.nMilkCaret, @.nPOP,
-- @.szInvoiceId, @.dtInv_Date, @.szIsCancelled,
-- @.nInvoiceType, @.nConsignmentType, @.szClubbedId)
Return 0
If any one can trap some problem here, that would be great ...
Thanks,
*(Vipul)() ;
"Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
news:OZlVvvh6GHA.4304@.TK2MSFTNGP03.phx.gbl...
> Hi Everybuddy,
> I have a strange problem with VB6/SQL Server 2K. I am calling a stored
> procedure from VB using ADODB.Command object. When I check the incomming
> parameters into the stored procedure, that values in named parameters are
> inter-changed (e.g. : Calling ABC(X=3, Y=7, Z=12) will result into- X=12,
> Y=3 and Z=7 inside the procedure ABC).
> I am totally having no clue of whats making this wierd situation. The code
> snippet of Caller (VB 6.0) and Callee (SQL Server 2000 SP4, Stored
> Procedire) are below.
> [VB Code]
> ----
> Set cnBuilty = New ADODB.Connection
> Set cmdBuilty = New ADODB.Command
> cnBuilty.CursorLocation = adUseClient
> cnBuilty.Open APP_StdConnectionString
> 'cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.RETURN_VALUE",
> adInteger, adParamReturnValue, 4, nRetVal)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLR_No",
> adInteger, adParamInput, 4, in_LR_No)
> cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@.nDestinationId",
> adInteger, adParamInput, 4, in_Town_ID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsignorId",
> adInteger, adParamInput, 4, in_SenderID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsigneeId",
> adInteger, adParamInput, 4, in_ReceiverID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMBT",
> adInteger, adParamInput, 4, in_MBT)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMST",
> adInteger, adParamInput, 4, in_MST)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLH_FL",
> adInteger, adParamInput, 4, in_LH_FL)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nCBB",
> adInteger, adParamInput, 4, in_CBB)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nSCBB",
> adInteger, adParamInput, 4, in_SCBB)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMilkCaret",
> adInteger, adParamInput, 4, in_Milk_Crt)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nPOP",
> adInteger, adParamInput, 4, in_POP)
> cmdBuilty.Parameters.Append
> cmdBuilty.CreateParameter("@.nConsignmentType", adInteger, adParamInput, 4,
> in_ConsignmentType)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nInvoiceType",
> adTinyInt, adParamInput, 1, in_InvType)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtInv_Date",
> adDBTimeStamp, adParamInput, 8, in_LR_Date)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtLR_Date",
> adDBTimeStamp, adParamInput, 8, in_LR_Date)
> cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@.szIsCancelled",
> adVarChar, adParamInput, 1, "")
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szInvoiceId",
> adVarChar, adParamInput, 20, Trim(in_InvoiceNo))
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szClubbedId",
> adVarChar, adParamInput, 20, Trim(in_ClubbedWithId))
> cmdBuilty.Name = "InsertConsignmentRecord"
> cmdBuilty.ActiveConnection = cnBuilty
> cmdBuilty.CommandText = "InsertConsignmentRecord"
> cmdBuilty.CommandType = adCmdStoredProc
> cmdBuilty.CommandTimeout = 45
> Set rsBuilty = cmdBuilty.Execute()
> 'nRetVal = cmdBuilty.Parameters("@.RETURN_VALUE")
> Dim qq As Integer
> For qq = 0 To (rsBuilty.Fields.Count - 1) Step 1
> Debug.Print rsBuilty.Fields(qq).Name & " = " & rsBuilty.Fields(qq)
> Next qq
> ----
>
> [Stored Procedure Code]
> ----
> CREATE PROCEDURE dbo.InsertConsignmentRecord
> @.nLR_No As Int,
> @.dtLR_Date As DateTime,
> @.nDestinationId As Int,
> @.nConsignorId As Int,
> @.nConsigneeId As Int,
> @.nMBT As Int,
> @.nMST As Int,
> @.nLH_FL As Int,
> @.nCBB As Int,
> @.nSCBB As Int,
> @.nMilkCaret As Int,
> @.nPOP As Int,
> @.dtInv_Date As DateTime,
> @.szInvoiceId As VarChar(21),
> @.szIsCancelled As VarChar(1),
> @.nInvoiceType As TinyInt,
> @.nConsignmentType As Int,
> @.szClubbedId As VarChar(21)
> AS
> -- Problem: Received value is different from what is passed ...
> -- Received- InvoiceType=03 Oct 2006; MST=3; CBB=1; SCBB=0; LH=2;
> ConsignmentType=36528001;
> -- Originally Passed: Says- InvoiceType=1; MST=0; CBB=2; SCBB=1; LH=3;
> ConsignmentType=1;
> Select @.nInvoiceType As "InvoiceType", @.nMBT As "MBT", @.nMST As "MST",
> @.nLH_FL As "LH", @.nCBB As "CBB", @.nSCBB As "SCBB", @.nConsignmentType As
> "ConsignmentType", @.szInvoiceId As "InvoiceId", @.szClubbedId As
"ClubbedId"
> -- Insert Into Consignment_Note
> -- ( LR_NO, LR_DATE, DESTINATION_ID, CONSIGNOR_CODE, CONSIGNEE_CODE,
> -- MBT, MST, CBB, LH_FL, SCBB, MILK_CRT, POP,
> -- INVOICE_NO, INV_DATE, IS_CANCELLED,
> -- INVTYPE, CONSIGNMENT_TYPE, CLUBBED_ID)
> -- Values
> -- ( @.nLR_No,@.dtLR_Date, @.nDestinationId, @.nConsignorId, @.nConsigneeId,
> -- @.nMBT, @.nMST, @.nCBB, @.nLH_FL, @.nSCBB, @.nMilkCaret, @.nPOP,
> -- @.szInvoiceId, @.dtInv_Date, @.szIsCancelled,
> -- @.nInvoiceType, @.nConsignmentType, @.szClubbedId)
> Return 0
> ----
> If any one can trap some problem here, that would be great ...
> Thanks,
> *(Vipul)() ;
>
Best guess...
There is no 'data binding' in ADO. It is pure positional - it looks like you
have your parameters out of order from what the SP is expecting. Take a look
at the LR_DATE.
-ralph
|||Thanks a lot Ralph,
You are damn correct in one shot. It worked in first time after the
suggested change.
BTW, does this mean, we are not passing named parameters? The name of
parameter is *not* useful?
Thanks for your help ...
*(Vipul)() ;
"Ralph" <nt_consulting64@.yahoo.com> wrote in message
news:7sGdnbeE3_xPXLrYnZ2dnUVZ_qudnZ2d@.arkansas.net ...[vbcol=seagreen]
> "Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
> news:OZlVvvh6GHA.4304@.TK2MSFTNGP03.phx.gbl...
are[vbcol=seagreen]
X=12,[vbcol=seagreen]
code[vbcol=seagreen]
cmdBuilty.CreateParameter("@.RETURN_VALUE",[vbcol=seagreen]
> cmdBuilty.CreateParameter("@.nDestinationId",
cmdBuilty.CreateParameter("@.nConsignorId",[vbcol=seagreen]
cmdBuilty.CreateParameter("@.nConsigneeId",[vbcol=seagreen]
4,[vbcol=seagreen]
cmdBuilty.CreateParameter("@.nInvoiceType",[vbcol=seagreen]
> cmdBuilty.CreateParameter("@.szIsCancelled",
cmdBuilty.CreateParameter("@.szInvoiceId",[vbcol=seagreen]
cmdBuilty.CreateParameter("@.szClubbedId",[vbcol=seagreen]
rsBuilty.Fields(qq)
> "ClubbedId"
> Best guess...
> There is no 'data binding' in ADO. It is pure positional - it looks like
you
> have your parameters out of order from what the SP is expecting. Take a
look
> at the LR_DATE.
> -ralph
>
|||"Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
news:ue$sCwi6GHA.4732@.TK2MSFTNGP03.phx.gbl...
> Thanks a lot Ralph,
> You are damn correct in one shot. It worked in first time after the
> suggested change.
> BTW, does this mean, we are not passing named parameters? The name of
> parameter is *not* useful?
> Thanks for your help ...
> *(Vipul)() ;
>
<snipped>
Essentially - yes.
That is what is meant by "not bounded". You should note that this is not
necessarily true for all providers or data access libraries. Unfortunately,
usually we have to get 'burnt' to find out if it is supported or not. <g>
Providing named parameters shouldn't be underestimated in any case. As it
makes your intentions very clear and identifying errant parameters becomes
easier. An advantage which becomes very invaluable should you have to
revisit this routine six months from now. <g>
-ralph

Incorrect parameters being received by SQL Server Stored Procedure ...

Hi Everybuddy,
I have a strange problem with VB6/SQL Server 2K. I am calling a stored
procedure from VB using ADODB.Command object. When I check the incomming
parameters into the stored procedure, that values in named parameters are
inter-changed (e.g. : Calling ABC(X=3, Y=7, Z=12) will result into- X=12,
Y=3 and Z=7 inside the procedure ABC).
I am totally having no clue of whats making this wierd situation. The code
snippet of Caller (VB 6.0) and Callee (SQL Server 2000 SP4, Stored
Procedire) are below.
[VB Code]
----
Set cnBuilty = New ADODB.Connection
Set cmdBuilty = New ADODB.Command
cnBuilty.CursorLocation = adUseClient
cnBuilty.Open APP_StdConnectionString
'cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.RETURN_VALUE",
adInteger, adParamReturnValue, 4, nRetVal)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLR_No",
adInteger, adParamInput, 4, in_LR_No)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nDestinationId",
adInteger, adParamInput, 4, in_Town_ID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsignorId",
adInteger, adParamInput, 4, in_SenderID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsigneeId",
adInteger, adParamInput, 4, in_ReceiverID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMBT",
adInteger, adParamInput, 4, in_MBT)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMST",
adInteger, adParamInput, 4, in_MST)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLH_FL",
adInteger, adParamInput, 4, in_LH_FL)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nCBB",
adInteger, adParamInput, 4, in_CBB)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nSCBB",
adInteger, adParamInput, 4, in_SCBB)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMilkCaret",
adInteger, adParamInput, 4, in_Milk_Crt)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nPOP",
adInteger, adParamInput, 4, in_POP)
cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@.nConsignmentType", adInteger, adParamInput, 4,
in_ConsignmentType)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nInvoiceType",
adTinyInt, adParamInput, 1, in_InvType)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtInv_Date",
adDBTimeStamp, adParamInput, 8, in_LR_Date)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtLR_Date",
adDBTimeStamp, adParamInput, 8, in_LR_Date)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szIsCancelled",
adVarChar, adParamInput, 1, "")
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szInvoiceId",
adVarChar, adParamInput, 20, Trim(in_InvoiceNo))
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szClubbedId",
adVarChar, adParamInput, 20, Trim(in_ClubbedWithId))
cmdBuilty.Name = "InsertConsignmentRecord"
cmdBuilty.ActiveConnection = cnBuilty
cmdBuilty.CommandText = "InsertConsignmentRecord"
cmdBuilty.CommandType = adCmdStoredProc
cmdBuilty.CommandTimeout = 45
Set rsBuilty = cmdBuilty.Execute()
'nRetVal = cmdBuilty.Parameters("@.RETURN_VALUE")
Dim qq As Integer
For qq = 0 To (rsBuilty.Fields.Count - 1) Step 1
Debug.Print rsBuilty.Fields(qq).Name & " = " & rsBuilty.Fields(qq)
Next qq
----
[Stored Procedure Code]
----
CREATE PROCEDURE dbo.InsertConsignmentRecord
@.nLR_No As Int,
@.dtLR_Date As DateTime,
@.nDestinationId As Int,
@.nConsignorId As Int,
@.nConsigneeId As Int,
@.nMBT As Int,
@.nMST As Int,
@.nLH_FL As Int,
@.nCBB As Int,
@.nSCBB As Int,
@.nMilkCaret As Int,
@.nPOP As Int,
@.dtInv_Date As DateTime,
@.szInvoiceId As VarChar(21),
@.szIsCancelled As VarChar(1),
@.nInvoiceType As TinyInt,
@.nConsignmentType As Int,
@.szClubbedId As VarChar(21)
AS
-- Problem: Received value is different from what is passed ...
-- Received- InvoiceType=03 Oct 2006; MST=3; CBB=1; SCBB=0; LH=2;
ConsignmentType=36528001;
-- Originally Passed: Says- InvoiceType=1; MST=0; CBB=2; SCBB=1; LH=3;
ConsignmentType=1;
Select @.nInvoiceType As "InvoiceType", @.nMBT As "MBT", @.nMST As "MST",
@.nLH_FL As "LH", @.nCBB As "CBB", @.nSCBB As "SCBB", @.nConsignmentType As
"ConsignmentType", @.szInvoiceId As "InvoiceId", @.szClubbedId As "ClubbedId"
-- Insert Into Consignment_Note
-- ( LR_NO, LR_DATE, DESTINATION_ID, CONSIGNOR_CODE, CONSIGNEE_CODE,
-- MBT, MST, CBB, LH_FL, SCBB, MILK_CRT, POP,
-- INVOICE_NO, INV_DATE, IS_CANCELLED,
-- INVTYPE, CONSIGNMENT_TYPE, CLUBBED_ID)
-- Values
-- ( @.nLR_No,@.dtLR_Date, @.nDestinationId, @.nConsignorId, @.nConsigneeId,
-- @.nMBT, @.nMST, @.nCBB, @.nLH_FL, @.nSCBB, @.nMilkCaret, @.nPOP,
-- @.szInvoiceId, @.dtInv_Date, @.szIsCancelled,
-- @.nInvoiceType, @.nConsignmentType, @.szClubbedId)
Return 0
----
If any one can trap some problem here, that would be great ...
Thanks,
*(Vipul)() ;"Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
news:OZlVvvh6GHA.4304@.TK2MSFTNGP03.phx.gbl...
> Hi Everybuddy,
> I have a strange problem with VB6/SQL Server 2K. I am calling a stored
> procedure from VB using ADODB.Command object. When I check the incomming
> parameters into the stored procedure, that values in named parameters are
> inter-changed (e.g. : Calling ABC(X=3, Y=7, Z=12) will result into- X=12,
> Y=3 and Z=7 inside the procedure ABC).
> I am totally having no clue of whats making this wierd situation. The code
> snippet of Caller (VB 6.0) and Callee (SQL Server 2000 SP4, Stored
> Procedire) are below.
> [VB Code]
> ----
> Set cnBuilty = New ADODB.Connection
> Set cmdBuilty = New ADODB.Command
> cnBuilty.CursorLocation = adUseClient
> cnBuilty.Open APP_StdConnectionString
> 'cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.RETURN_VALUE",
> adInteger, adParamReturnValue, 4, nRetVal)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLR_No",
> adInteger, adParamInput, 4, in_LR_No)
> cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@.nDestinationId",
> adInteger, adParamInput, 4, in_Town_ID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsignorId",
> adInteger, adParamInput, 4, in_SenderID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsigneeId",
> adInteger, adParamInput, 4, in_ReceiverID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMBT",
> adInteger, adParamInput, 4, in_MBT)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMST",
> adInteger, adParamInput, 4, in_MST)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLH_FL",
> adInteger, adParamInput, 4, in_LH_FL)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nCBB",
> adInteger, adParamInput, 4, in_CBB)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nSCBB",
> adInteger, adParamInput, 4, in_SCBB)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMilkCaret",
> adInteger, adParamInput, 4, in_Milk_Crt)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nPOP",
> adInteger, adParamInput, 4, in_POP)
> cmdBuilty.Parameters.Append
> cmdBuilty.CreateParameter("@.nConsignmentType", adInteger, adParamInput, 4,
> in_ConsignmentType)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nInvoiceType",
> adTinyInt, adParamInput, 1, in_InvType)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtInv_Date",
> adDBTimeStamp, adParamInput, 8, in_LR_Date)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtLR_Date",
> adDBTimeStamp, adParamInput, 8, in_LR_Date)
> cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@.szIsCancelled",
> adVarChar, adParamInput, 1, "")
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szInvoiceId",
> adVarChar, adParamInput, 20, Trim(in_InvoiceNo))
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szClubbedId",
> adVarChar, adParamInput, 20, Trim(in_ClubbedWithId))
> cmdBuilty.Name = "InsertConsignmentRecord"
> cmdBuilty.ActiveConnection = cnBuilty
> cmdBuilty.CommandText = "InsertConsignmentRecord"
> cmdBuilty.CommandType = adCmdStoredProc
> cmdBuilty.CommandTimeout = 45
> Set rsBuilty = cmdBuilty.Execute()
> 'nRetVal = cmdBuilty.Parameters("@.RETURN_VALUE")
> Dim qq As Integer
> For qq = 0 To (rsBuilty.Fields.Count - 1) Step 1
> Debug.Print rsBuilty.Fields(qq).Name & " = " & rsBuilty.Fields(qq)
> Next qq
> ----
>
> [Stored Procedure Code]
> ----
> CREATE PROCEDURE dbo.InsertConsignmentRecord
> @.nLR_No As Int,
> @.dtLR_Date As DateTime,
> @.nDestinationId As Int,
> @.nConsignorId As Int,
> @.nConsigneeId As Int,
> @.nMBT As Int,
> @.nMST As Int,
> @.nLH_FL As Int,
> @.nCBB As Int,
> @.nSCBB As Int,
> @.nMilkCaret As Int,
> @.nPOP As Int,
> @.dtInv_Date As DateTime,
> @.szInvoiceId As VarChar(21),
> @.szIsCancelled As VarChar(1),
> @.nInvoiceType As TinyInt,
> @.nConsignmentType As Int,
> @.szClubbedId As VarChar(21)
> AS
> -- Problem: Received value is different from what is passed ...
> -- Received- InvoiceType=03 Oct 2006; MST=3; CBB=1; SCBB=0; LH=2;
> ConsignmentType=36528001;
> -- Originally Passed: Says- InvoiceType=1; MST=0; CBB=2; SCBB=1; LH=3;
> ConsignmentType=1;
> Select @.nInvoiceType As "InvoiceType", @.nMBT As "MBT", @.nMST As "MST",
> @.nLH_FL As "LH", @.nCBB As "CBB", @.nSCBB As "SCBB", @.nConsignmentType As
> "ConsignmentType", @.szInvoiceId As "InvoiceId", @.szClubbedId As
"ClubbedId"
> -- Insert Into Consignment_Note
> -- ( LR_NO, LR_DATE, DESTINATION_ID, CONSIGNOR_CODE, CONSIGNEE_CODE,
> -- MBT, MST, CBB, LH_FL, SCBB, MILK_CRT, POP,
> -- INVOICE_NO, INV_DATE, IS_CANCELLED,
> -- INVTYPE, CONSIGNMENT_TYPE, CLUBBED_ID)
> -- Values
> -- ( @.nLR_No,@.dtLR_Date, @.nDestinationId, @.nConsignorId, @.nConsigneeId,
> -- @.nMBT, @.nMST, @.nCBB, @.nLH_FL, @.nSCBB, @.nMilkCaret, @.nPOP,
> -- @.szInvoiceId, @.dtInv_Date, @.szIsCancelled,
> -- @.nInvoiceType, @.nConsignmentType, @.szClubbedId)
> Return 0
> ----
> If any one can trap some problem here, that would be great ...
> Thanks,
> *(Vipul)() ;
>
Best guess...
There is no 'data binding' in ADO. It is pure positional - it looks like you
have your parameters out of order from what the SP is expecting. Take a look
at the LR_DATE.
-ralph|||Thanks a lot Ralph,
You are damn correct in one shot. It worked in first time after the
suggested change.
BTW, does this mean, we are not passing named parameters? The name of
parameter is *not* useful?
Thanks for your help ...
*(Vipul)() ;
"Ralph" <nt_consulting64@.yahoo.com> wrote in message
news:7sGdnbeE3_xPXLrYnZ2dnUVZ_qudnZ2d@.ar
kansas.net...
> "Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
> news:OZlVvvh6GHA.4304@.TK2MSFTNGP03.phx.gbl...
are[vbcol=seagreen]
X=12,[vbcol=seagreen]
code[vbcol=seagreen]
cmdBuilty.CreateParameter("@.RETURN_VALUE",[vbcol=seagreen]
> cmdBuilty.CreateParameter("@.nDestinationId",
cmdBuilty.CreateParameter("@.nConsignorId",[vbcol=seagreen]
cmdBuilty.CreateParameter("@.nConsigneeId",[vbcol=seagreen]
4,[vbcol=seagreen]
cmdBuilty.CreateParameter("@.nInvoiceType",[vbcol=seagreen]
> cmdBuilty.CreateParameter("@.szIsCancelled",
cmdBuilty.CreateParameter("@.szInvoiceId",[vbcol=seagreen]
cmdBuilty.CreateParameter("@.szClubbedId",[vbcol=seagreen]
rsBuilty.Fields(qq)[vbcol=seagreen]
> "ClubbedId"
> Best guess...
> There is no 'data binding' in ADO. It is pure positional - it looks like
you
> have your parameters out of order from what the SP is expecting. Take a
look
> at the LR_DATE.
> -ralph
>|||"Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
news:ue$sCwi6GHA.4732@.TK2MSFTNGP03.phx.gbl...
> Thanks a lot Ralph,
> You are damn correct in one shot. It worked in first time after the
> suggested change.
> BTW, does this mean, we are not passing named parameters? The name of
> parameter is *not* useful?
> Thanks for your help ...
> *(Vipul)() ;
>
<snipped>
Essentially - yes.
That is what is meant by "not bounded". You should note that this is not
necessarily true for all providers or data access libraries. Unfortunately,
usually we have to get 'burnt' to find out if it is supported or not. <g>
Providing named parameters shouldn't be underestimated in any case. As it
makes your intentions very clear and identifying errant parameters becomes
easier. An advantage which becomes very invaluable should you have to
revisit this routine six months from now. <g>
-ralphsql

Incorrect Order in rendering report

Hi,

I have this problem on Reporting Services 2005 SP2:

There is a stored procedure that is the source of a dataset in report, this procedure return a recordset ordered by some fileds (es. order by fields1, fields2, ecc...). This procedure also have some parameters, but this isn't important.

If I launch the stored procedure in sql server management studio the data are returned in the correct order, instead, when I run the report, the data are showed in wrong order.

Some one have informations about this issue?

Kind Regards,

Elia.

Did you try sorting in the report? If so doesn't it still sort in the order that you selected.

If not try sorting in the report, within table properties you will see sorting within which you can specify the sort order

|||

Thanks,

I have resolved the problem.

Regards,

Elia.

inconsistent Timeout Expired with extended SP execute

All,
I've successfully executed my extended stored proc multiple times via
the following. If I directly execute(e.g., right-click 'Execute'
within my DB editor) this stored procedure, it runs everytime and
finishes in 1-2 seconds. If I try to execute the SP via my trigger, it
returns a 'Timeout Expired' message. But - not every time. I have got
the trigger to fire the SP a few (seemingly random) times without
timing out. Any help would be fantastic...
Pre-Thanks,
sara
my SP:
ALTER procedure dbo.WriteDNFiles
as
declare @.return varchar
EXEC @.return = master.dbo.XYRunProc 'C:\WRITE_DN_FILES.exe'
my trigger:
ALTER TRIGGER TriggerEmp
ON dbo.Employees
FOR INSERT, UPDATE
AS
exec WriteDNFilesHi sara
Running a program in a trigger is not a good idea as it will prolong the
transaction and there increase contention which could possibly be what you
are experiencing. You may want to make sure that you are not having any file
access problems if two process are wanting to call the program simultanously
.
Another general point is that you should change @.@.ROWCOUNT in the trigger to
see if anything has changed, if not don't call your procedure.
John
"skeddy@.gmail.com" wrote:

> All,
> I've successfully executed my extended stored proc multiple times via
> the following. If I directly execute(e.g., right-click 'Execute'
> within my DB editor) this stored procedure, it runs everytime and
> finishes in 1-2 seconds. If I try to execute the SP via my trigger, it
> returns a 'Timeout Expired' message. But - not every time. I have got
> the trigger to fire the SP a few (seemingly random) times without
> timing out. Any help would be fantastic...
> Pre-Thanks,
> sara
> my SP:
> ALTER procedure dbo.WriteDNFiles
> as
> declare @.return varchar
> EXEC @.return = master.dbo.XYRunProc 'C:\WRITE_DN_FILES.exe'
> my trigger:
> ALTER TRIGGER TriggerEmp
> ON dbo.Employees
> FOR INSERT, UPDATE
> AS
> exec WriteDNFiles
>

inconsistent Timeout Expired with extended SP execute

All,
I've successfully executed my extended stored proc multiple times via
the following. If I directly execute(e.g., right-click 'Execute'
within my DB editor) this stored procedure, it runs everytime and
finishes in 1-2 seconds. If I try to execute the SP via my trigger, it
returns a 'Timeout Expired' message. But - not every time. I have got
the trigger to fire the SP a few (seemingly random) times without
timing out. Any help would be fantastic...
Pre-Thanks,
sara
my SP:
ALTER procedure dbo.WriteDNFiles
as
declare @.return varchar
EXEC @.return = master.dbo.XYRunProc 'C:\WRITE_DN_FILES.exe'
my trigger:
ALTER TRIGGER TriggerEmp
ON dbo.Employees
FOR INSERT, UPDATE
AS
exec WriteDNFilesHi sara
Running a program in a trigger is not a good idea as it will prolong the
transaction and there increase contention which could possibly be what you
are experiencing. You may want to make sure that you are not having any file
access problems if two process are wanting to call the program simultanously.
Another general point is that you should change @.@.ROWCOUNT in the trigger to
see if anything has changed, if not don't call your procedure.
John
"skeddy@.gmail.com" wrote:
> All,
> I've successfully executed my extended stored proc multiple times via
> the following. If I directly execute(e.g., right-click 'Execute'
> within my DB editor) this stored procedure, it runs everytime and
> finishes in 1-2 seconds. If I try to execute the SP via my trigger, it
> returns a 'Timeout Expired' message. But - not every time. I have got
> the trigger to fire the SP a few (seemingly random) times without
> timing out. Any help would be fantastic...
> Pre-Thanks,
> sara
> my SP:
> ALTER procedure dbo.WriteDNFiles
> as
> declare @.return varchar
> EXEC @.return = master.dbo.XYRunProc 'C:\WRITE_DN_FILES.exe'
> my trigger:
> ALTER TRIGGER TriggerEmp
> ON dbo.Employees
> FOR INSERT, UPDATE
> AS
> exec WriteDNFiles
>

Inconsistent stored procedure syntax error

We have a stored procedure that is being called from a vb6 application. The exact line of code is below.

mats_Reports_CSR_OB_Hourly_Percent('11/1/2006 4:51:27 PM','12/1/2006 4:51:27 PM')

when executing this in query analyzer it will always fail due to the parenthesis. However when this same line of code is passed to sql server in the vb6 application it usually works. There are times it fails with a syntax error message similar to:

Incorrect syntax near '{

I fixed the problem last time by removing the parenthesis in the vb6 code. However I can now put the parenthesis back into the code and it works again now.

Prior to my recent intervention the code was not being changed as it will work for days and then randomly stop working. Then without any change start working by itself again.

What is causing this behavior? How do I make it always work?

Calling a sql stored procedure is not like calling a vb function. Since you're calling a stored procedure, consider using Parameters collection to pass into desired inputs.

http://windowssdk.msdn.microsoft.com/en-gb/library/ms675869.aspx|||

Thank you for your response.

Your response seems to imply there is a bug in the way ODBC handles stored procedure calls?

This application was built over 3 years ago. It is not a realistic solution to switch connections from ODBC to ADO, as it would require the entire application to be recoded.

I have spent the better part of the day yesterday doing research on this problem and it seems it has something to do with ODBC escape sequences. What conditions would change how sql server parses a statement? There has to be something the server is automatically deciding on using that changes, because the same code works and then doesn't work (running against the same data).

|||No. What I hinted at is that you shouldn't call a proc like so:

myproc(para1,para2)

Instead you should do:

cmd.CommandText="myproc para1, para2"

or create a parameters collection for your parameters.|||I already know not using the parenthesis will work. I was looking for why it's happening.|||There is a simple answer: Because its the syntax of calling stored procedures that way. Did you ever ask yourself why a vb function is called like SomeFunction('Something') and not like SomeFunction*'Something'* :-) Its simply the syntax that is specified for calling the procedure.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

OK, your missing what I am getting at. I will make an analogy:

Say your using your keyboard, everytime you press the the 'k' key you'd expect the letter k every time you pressed it.

Now if sometimes when you pressed the 'k' key you got the letter q you'd pull the keyboard and replace it with one that works, or you'd send the keyboard in for repair.

Now what I am describing is the same way. If you call a function using the syntax I already described above you'd expect it to work every time you used it. What I am getting though is sometimes the same syntax is sucessfully running, other times it is giving a syntax error. I am asking why is this occuring? What can you think of that would change to cause this behavior?

|||Where is the error thrown ? Could it be that this is some SQL Server error (not actually in the frontend) which is caused by some malformed composed dynamic SQL in the backend, producing error messages like this ? Are you able to start profiler on the server to see what happends behind the scenes and what comamdns are fired against the database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de