Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error 137 - you must declare the scalar variable Expand / Collapse
Author
Message
Posted Friday, August 2, 2013 5:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 7:59 AM
Points: 23, Visits: 257
Hi,

I'm running a script against 16 registered servers (6 SQL 2000, 4 SQL 2005, 6 SQL 2008R2) within the same connection.

It works well, except for 2 of 6 the SQL 2008R2.

I receive the error: Error 137 - you must declare the scalar variable.

The script starts like that:

Declare @datefull datetime
Declare @datelog datetime

Set @datefull = '01/05/13 23:12:00.500'
Set @datelog = '28/09/13 14:05:23.100'

Select .....
From ....
GO


I can't understand why those 2 SQL 2008R2 return this error, as it is the same script for all registered servers.

Could it be an Instance configuration ?

Thanks,
Dok
Post #1480650
Posted Saturday, August 3, 2013 10:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:05 PM
Points: 35,832, Visits: 32,505
Without seeing the rest of the proc, error line number, etc, it's difficult to tell but it could be an artifact later in the code caused by the date format or language settings and the fact that it appears that your dates are in the dd/mm/yyyy format. Total shot in the dark, though.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1480702
Posted Tuesday, August 6, 2013 2:33 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 11:44 AM
Points: 395, Visits: 2,699
dokledon (8/2/2013)
Hi,

I'm running a script against 16 registered servers (6 SQL 2000, 4 SQL 2005, 6 SQL 2008R2) within the same connection.

It works well, except for 2 of 6 the SQL 2008R2.

I receive the error: Error 137 - you must declare the scalar variable.

The script starts like that:

Declare @datefull datetime
Declare @datelog datetime

Set @datefull = '01/05/13 23:12:00.500'
Set @datelog = '28/09/13 14:05:23.100'

Select .....
From ....
GO


I can't understand why those 2 SQL 2008R2 return this error, as it is the same script for all registered servers.

Could it be an Instance configuration ?

Thanks,
Dok


You could also doublecheck that these two servers don't have one of those "case sensitive" collations. If it had a case sensitive collation and you aren't consistant with your naming of variables (the declaration has all lowercase and subsequent use of that variable maybe has an uppercase spelling), this could lead to these specific servers complaining while others successfully run.

Just a possibility you might want to eliminate!
Post #1481560
Posted Tuesday, August 6, 2013 6:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 7:59 AM
Points: 23, Visits: 257
Thanks to Jeff and Patrick for their answers.

It was indeed a collation problem, as one of the variable was using Upper case and was called with Lower case.

Dok.
Post #1481621
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse