SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Problem executing dynamic SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

neilshep
Starting Member

United Kingdom
2 Posts

Posted - 10/02/2013 :  05:33:46  Show Profile  Reply with Quote
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

USA
37466 Posts

Posted - 10/02/2013 :  06:10:14  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

neilshep
Starting Member

United Kingdom
2 Posts

Posted - 10/02/2013 :  07:21:13  Show Profile  Reply with Quote
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);

Edited by - neilshep on 10/02/2013 07:22:04
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 10/02/2013 :  15:44:09  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 10/02/2013 :  20:32:16  Show Profile  Reply with Quote

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



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000