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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
38200 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
38200 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
17689 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  
 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.09 seconds. Powered By: Snitz Forums 2000