November 2, 2012 at 7:57 am
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
November 2, 2012 at 7:58 am
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.
November 2, 2012 at 8:15 am
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'.
November 2, 2012 at 8:22 am
Try changing the EXEC (@SQLCmd) before the update.
November 2, 2012 at 8:25 am
I'm sorry for my ignorance but i'm unsure what you mean with changing EXEC (@SQLCmd) before update
November 2, 2012 at 8:30 am
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
November 2, 2012 at 8:35 am
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
November 2, 2012 at 8:37 am
Yes this is what i am trying to achieve with the lookup searching for the correct hostname and db name
November 2, 2012 at 8:50 am
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'.
November 2, 2012 at 8:58 am
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 updateuse [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/
November 2, 2012 at 9:04 am
The code is a Luis Cazares suggested.
November 2, 2012 at 10:25 am
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/
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply