Trouble LinkedServer dynamic SQL

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

SQL Server Administration (2008)

Trouble LinkedServer dynamic SQL

Author

Topic

erber
Starting Member

2 Posts

Posted - 2013-06-05 : 06:49:33

Background: My company has a webapp developed to keep track of all servers and SQL servers. The database is PostgreSQL. But this database is most manually updated. I want now to feed this system with databases on server A, B and B etc. We have a lot of db:s so if you can script this it will help a lot.
I have set up LinkedServer to server A, B, C etc.....
From a PostgreSQL db I import servernames to a temptable i MS SQL.
So far so good. But what I then want is to use the servername as an inparameter for a select statement against all servers in a coursor. And since you cant have variable directly in the FROM-statement I use dynamic SQL.

DECLARE @sqlcommand VARCHAR(1000)
DECLARE @servername VARCHAR(20)
SET @servername = ' EXAMPLESERVER'
SET @sqlcommand =
'select * from' + @servername + '.master.dbo.sysdatabases WHERE name NOT IN (''master'',''model'',''msdb'',''tempdb'')'
PRINT @sqlcommand --to see the statement
EXEC @sqlcommand

ERROR message:
Msg 7202, Level 11, State 2, Line 7
Could not find server 'select * from EXAMPLESERVER' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

If I use the question outside the EXECSQL and execute it directly it works like a charm.

Can anybody see that I do wrong?

Erber

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 06:52:04

As far as i can see its missing a space


DECLARE @sqlcommand VARCHAR(1000)
DECLARE @servername VARCHAR(20)
SET @servername = ' EXAMPLESERVER' 
SET @sqlcommand = 
'select * from ' + @servername + '.master.dbo.sysdatabases WHERE name NOT IN (''master'',''model'',''msdb'',''tempdb'')'
PRINT @sqlcommand --to see the statement
EXEC @sqlcommand

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 06:54:13

Hmm...reading again i see you've leading space put in @servname so thats not the issue then...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

erber
Starting Member

2 Posts

Posted - 2013-06-05 : 08:16:22

Found it I think, the EXAMPLESERVER.master.dbo.sys.databases' contains more than the maximum number of prefixes. The maximum is 3.

This works: select * from EXAMPLESERVER.master.sys.databases WHERE name NOT IN ('master','model','msdb','tempdb')
Thanks visakh16 for trying!

=)
Erber

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 13:59:27

quote:
Originally posted by erber

Found it I think, the EXAMPLESERVER.master.dbo.sys.databases' contains more than the maximum number of prefixes. The maximum is 3.

This works: select * from EXAMPLESERVER.master.sys.databases WHERE name NOT IN ('master','model','msdb','tempdb')
Thanks visakh16 for trying!

=)
Erber

sorry but your post query had no . between sys and databases

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources