Must declare the scalar variable when running script

Question

Post reply

Must declare the scalar variable when running script

nmcgowan

Old Hand

Points: 394
More actions
November 2, 2012 at 7:57 am

#393530

I am getting errors when trying to run an update script. the errors are
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@SQLCmd".
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@SQLCmd".
The script I am trying to run is as follows, it appears i have declared the variable but still it wont run. Please can someone help?
SET NOCOUNT ON
DECLARE @DBName AS SYSNAME;
DECLARE @SQLCmd AS NVARCHAR(4000)
create table #Lookup (subscriber_server varchar(100), database_name sysname)
insert into #Lookup (subscriber_server, database_name) values ('000005PTABR\SQLEXPRESS', 'apereira_ecristal')
insert into #Lookup (subscriber_server, database_name) values ('000066PTABR\SQLEXPRESS', 'DJesus_eCristal')
insert into #Lookup (subscriber_server, database_name) values ('000070PTABR\SQLEXPRESS', 'rferreira_ecristal')
insert into #Lookup (subscriber_server, database_name) values ('000075PTABR\SQLEXPRESS', 'hcalado_ecristal')
insert into #Lookup (subscriber_server, database_name) values ('000076PTABR\SQLEXPRESS', 'JB_eCristal')
SELECT @DBName = database_name from #Lookup where subscriber_server = @@SERVERNAME
SELECT @SQLCmd = 'use [' + @DBName + ']' + CHAR(10) + 'GO' + CHAR(10)
--SELECT @SQLCmd = 'use [' + @DBName + '];
UPDATE dbo.Application_Settings
SET [Description] = 'AppVersion=''v32''' + '~' + 'ProdVersion=''' +
CONVERT(VARCHAR,SERVERPROPERTY ('ProductVersion')) + '''' + '~' + 'ProdLevel='''+
CONVERT(VARCHAR,SERVERPROPERTY ('ProductLevel')) + '''' + '~' + 'Edition=''' +
CONVERT(VARCHAR,SERVERPROPERTY ('Edition')) + '''' ,
Updated_ON = GetDate()
WHERE Name like '%'+ HOST_NAME() +'%' AND [Value] = 'Patch'
GO
print @SQLCmd
EXEC (@SQLCmd)
DROP TABLE #lookup

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You have your @SCLCmd Declaration in a different batch that ended at GO.

What are you trying to do?

The dynamic part is just changing the database you're on but your not doing anything after that.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

nmcgowan Old Hand Points: 394 More actions · Answer 2

Thanks for that, seemed to get a little further.

I am now having problems with it finding the Application_Settings table which i assume is because it has not found my local db in the lookup even though its present in the list.

here is the error

Msg 208, Level 16, State 1, Line 217

Invalid object name 'dbo.Application_Settings'.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Try changing the EXEC (@SQLCmd) before the update.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

nmcgowan Old Hand Points: 394 More actions · Answer 4

I'm sorry for my ignorance but i'm unsure what you mean with changing EXEC (@SQLCmd) before update

Ant-Green SSC Guru Points: 113566 More actions · Answer 5

Based on the code provided, the update is outside of the set @SQLCmd

Do you need the UPDATE to be inside the variable so that you execute the use and update via the exec (@SQLCmd)

So the contents of @SQLCmd are something like

USE JB_eCristal

GO

UPDATE dbo.Application_Settings

SET [Description] = 'AppVersion='v32' + '~' + 'ProdVersion=''' +

CONVERT(VARCHAR,SERVERPROPERTY ('ProductVersion')) + '''' + '~' + 'ProdLevel='''+

CONVERT(VARCHAR,SERVERPROPERTY ('ProductLevel')) + '''' + '~' + 'Edition=''' +

CONVERT(VARCHAR,SERVERPROPERTY ('Edition')) + '''' ,

Updated_ON = GetDate()

WHERE Name like '%'+ HOST_NAME() +'%' AND [Value] = 'Patch'

GO

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Yoou really shouldn't be dealing with T-SQL if you don't know what UPDATE or EXEC are and what are they capable of doing.

Here's the script with the correction that I'm suggesting.

SET NOCOUNT ON

DECLARE @DBName AS SYSNAME;

DECLARE @SQLCmd AS NVARCHAR(4000)

create table #Lookup (subscriber_server varchar(100), database_name sysname)

insert into #Lookup (subscriber_server, database_name) values ('000005PTABR\SQLEXPRESS', 'apereira_ecristal')

insert into #Lookup (subscriber_server, database_name) values ('000066PTABR\SQLEXPRESS', 'DJesus_eCristal')

insert into #Lookup (subscriber_server, database_name) values ('000070PTABR\SQLEXPRESS', 'rferreira_ecristal')

insert into #Lookup (subscriber_server, database_name) values ('000075PTABR\SQLEXPRESS', 'hcalado_ecristal')

insert into #Lookup (subscriber_server, database_name) values ('000076PTABR\SQLEXPRESS', 'JB_eCristal')

SELECT @DBName = database_name from #Lookup where subscriber_server = @@SERVERNAME

SELECT @SQLCmd = 'use [' + @DBName + ']' + CHAR(10) + 'GO' + CHAR(10)

--SELECT @SQLCmd = 'use [' + @DBName + '];

print @SQLCmd

EXEC (@SQLCmd)

UPDATE dbo.Application_Settings

SET [Description] = 'AppVersion=''v32''' + '~' + 'ProdVersion=''' +

CONVERT(VARCHAR,SERVERPROPERTY ('ProductVersion')) + '''' + '~' + 'ProdLevel='''+

CONVERT(VARCHAR,SERVERPROPERTY ('ProductLevel')) + '''' + '~' + 'Edition=''' +

CONVERT(VARCHAR,SERVERPROPERTY ('Edition')) + '''' ,

Updated_ON = GetDate()

WHERE Name like '%'+ HOST_NAME() +'%' AND [Value] = 'Patch'

GO

DROP TABLE #lookup

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

nmcgowan Old Hand Points: 394 More actions · Answer 7

Yes this is what i am trying to achieve with the lookup searching for the correct hostname and db name

nmcgowan Old Hand Points: 394 More actions · Answer 8

It appears to be finding the correct db now as i get the following but still get an error performing the update

use [DJesus_eCristal]

GO

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near 'GO'.

Msg 208, Level 16, State 1, Line 220

Invalid object name 'Application_Settings'.

Sean Lange SSC Guru Points: 286609 More actions · Answer 9

nmcgowan (11/2/2012)
It appears to be finding the correct db now as i get the following but still get an error performing the update
use [DJesus_eCristal]
GO
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'GO'.
Msg 208, Level 16, State 1, Line 220
Invalid object name 'Application_Settings'.

What does the code that you are running look like now? There have been many suggestions about what to change and we can't see you screen to know what you are running.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

nmcgowan Old Hand Points: 394 More actions · Answer 10

nmcgowan

Old Hand

Points: 394

November 2, 2012 at 9:04 am

#1555524

The code is a Luis Cazares suggested.

Sean Lange SSC Guru Points: 286609 More actions · Answer 11

nmcgowan (11/2/2012)
The code is a Luis Cazares suggested.

The problem with the code Luis posted is there is a USE inside dynamic sql followed by an update. The use does not change the current connection. It will only change the current database of the batch executing inside the dynamic sql.

Here is an example of what I mean:

use master

go

declare @SQL nvarchar(4000)

set @SQL = 'use tempdb; select DB_NAME();'

exec (@sql)

select DB_NAME()

Notice that DB_NAME when execute will return tempdb, but even after it runs it will return master.

To do what you are trying to do you need to make your entire piece be dynamic sql. I would recommend that instead of using GO you instead reference your table with 3 part naming convention inside your dynamic sql.

It is kind of painful to modify that type of string correctly so I will leave that up to you but something like this should get you started.

SELECT @SQLCmd = 'UPDATE ' + @DBName + '.dbo.Application_Settings '

+ 'SET [Description] = ''AppVersion=''''v32''''' + '~' + 'ProdVersion='''

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/