Problem executing dynamic SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Problem executing dynamic SQL

Author

Topic

neilshep
Starting Member

2 Posts

Posted - 2013-10-02 : 05:33:46

Hi

I have put together the following statement that I want to use to find the current version of all client databases from table dba.ver_tb.
However it only creates the queries but does not execute them
Where am I going wrong?

declare @query nvarchar (250)

set @query = ' select ''select top 1 Version from '' + name + ''.dba.ver_tb where Updated = (Select MAX (Updated) from '' + name + ''.dba.Ver_TB);''
from sys.databases
where name not in (''master'',
''tempdb'',
''model'',
''msdb'') and name not like ''%SF%'''

exec sp_executesql @query

Thanks
Neil

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-02 : 06:10:14

It'll only execute the first one it finds since you have top 1. Run "print @query" to see what I mean. Try sp_MSforeachdb instead.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

neilshep
Starting Member

2 Posts

Posted - 2013-10-02 : 07:21:13

Hi Tara

I'm not sure I understand. The output on a test server is as follows..
How can I then get these scripts to execute?

select top 1 Version from AUSGlobal.dba.ver_tb where Updated = (Select MAX (Updated) from AUSGlobal.dba.Ver_TB);
select top 1 Version from Carrier.dba.ver_tb where Updated = (Select MAX (Updated) from Carrier.dba.Ver_TB);
select top 1 Version from CarrierProd.dba.ver_tb where Updated = (Select MAX (Updated) from CarrierProd.dba.Ver_TB);
select top 1 Version from NeilR6.dba.ver_tb where Updated = (Select MAX (Updated) from NeilR6.dba.Ver_TB);
select top 1 Version from OspreyTrace.dba.ver_tb where Updated = (Select MAX (Updated) from OspreyTrace.dba.Ver_TB);
select top 1 Version from PlatMel.dba.ver_tb where Updated = (Select MAX (Updated) from PlatMel.dba.Ver_TB);
select top 1 Version from PlatSyd.dba.ver_tb where Updated = (Select MAX (Updated) from PlatSyd.dba.Ver_TB);

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-02 : 15:44:09

Using sp_executesql is fine, but you will have to loop through it. You can use a WHILE loop to set @query for each database name and execute sp_executesql with it. Loop until all databases have been processed.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-10-02 : 20:32:16

[code]
declare @query nvarchar (250)

select @query = isnull(@query, '')
+ 'select top 1 Version from ' + name + '.dba.ver_tb where Updated = (Select MAX (Updated) from ' + name + '.dba.Ver_TB);'
from sys.databases
where name not in ('master', 'tempdb', 'model', 'msdb')
and name not like '%SF%'

exec sp_executesql @query[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources