November 17, 2011 at 1:17 pm
We would like to develop a procedure that will work through defined linked servers and pull back some data from specific schemas. The server names were created similar to www-xyz with the "-" causing problems with querying the data from the linked server. I need to know how to work around this so that we can automate the collection of data from our linked servers.
An example of what we are attempting to do is as follows:
BEGIN
DECLARE @Lserver SYSNAME
DECLARE @rc BIT
DECLARE @Rcount int
DECLARE cServer CURSOR
FOR Select name, QUOTENAME(name,'"') from sys.servers where is_linked = 1
OPEN cServer
FETCH cServer into @Lserver
WHILE @@FETCH_STATUS <> -1
BEGIN
exec @rc = sys.sp_testlinkedserver @Lserver
PRINT @Lserver
Select @Rcount = COUNT(*) from [@Lserver].[SOME_DATABASE].dbo.[SOME_TABLE]
PRINT @Rcount
FETCH cServer into @Lserver, @Vserver
END
CLOSE cServer
DEALLOCATE cServer
END
The actual processing is more involved but this example generate the problems we are seeing. So, when we execute this we receive the error: Msg 7202, Level 11, State 2 Could not find server. However, if we print out the contents of the variable @Lserver they are correct. And if we hard code the server name, [my-server], in the query we get the record count we are looking for. We just need to know how to use the value we are receiving from sys.servers in our query.
Any and all help is appreciated.
November 17, 2011 at 2:22 pm
When you set your @LServer variable wrap it within square brackets. This gets rid of the issues with linked server names like 'abc-server'. The result will be [abc-server] and then all of your sp_testlinkedserver's will work. Great thing about this method is it will work for any naming convention...
Example:
DECLARE cServer CURSOR
FOR Select name, '[' + RTRIM(name) + ']' from sys.servers where is_linked = 1
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply