February 7, 2005 at 7:44 am
Hello guys,
i have a problem with this statement:
USE SQLDoku
DECLARE servername_cursor CURSOR
FOR
SELECT SRVNAME
FROM master.dbo.sysservers
OPEN servername_cursor
DECLARE @servername varchar(30)
--SET @servername = 'authors'
FETCH NEXT FROM servername_cursor INTO @servername
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
--Print @servername
--SELECT @servername = RTRIM(@servername)
EXEC ('drop table '+ @servername+'data')
EXEC ('SELECT name into '+@servername+'data FROM '+ @servername+ '.master.dbo.sysdatabases')
END
FETCH NEXT FROM servername_cursor INTO @servername
--EXEC ('SELECT name FROM '+ @servername+ '.master.dbo.sysdatabases into'+ @servername+'data')
END
CLOSE servername_cursor
DEALLOCATE servername_cursor
When I use the script, it works fine for all Servers with no instances, but when there are servers with instances i get an error message like this:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '\'.
when I use the normal query like this:
SELECT name from "tkdmun02\test01".master.dbo.sysdatabases
it works fine.
I there a way to configure the cursor script to set the variable @servername to ignore the "\" sign in it??
I hope anyone can help me....
February 7, 2005 at 7:54 am
Have you tried doubling it "tkdmun02\\test01"?
February 7, 2005 at 8:11 am
Yes see my second script i sayd it works fine, but i cant do this with the varialbe @servername, the affect gives a error like this:
Line 9: Incorrect syntax near '@servername'
There must be an other way, but i cant find anything in BOL.
February 7, 2005 at 8:35 am
The only difference I see between the working script and the other is the quotation marks :
"tkdmun02\test01"
FROM '+ @servername+ '.master.dbo.sysdatabases
maybe if you tried this :
FROM "'+ @servername+ '".master.dbo.sysdatabases
But this is just a shot in the dark since I cannot test this script on my pc.
February 7, 2005 at 8:42 am
You can test it. You can use thew northwind or pubs database. The script creates new tables for each SQL Server in your invoirment(linked Servers)called "servername"data.
The script works wheen you insert linked servers to sql server.
It make s no changes to your existing data.
February 7, 2005 at 8:53 am
That's why i can test it.. have no linked server and no other server to link to.
February 8, 2005 at 7:15 am
It's not essential to have linked server to test this syntax
I wrote simple script to test your syntax and found out. Try to replace such parts as
EXEC ('SELECT name into '+@servername+'data FROM '+ @servername+ '.master.dbo.sysdatabases')
with following:
declare @s-2 nvarchar(100)
set @s-2 = 'SELECT name into '+@servername+'data FROM '+ @servername+ '.master.dbo.sysdatabases'
EXEC @s-2
Simple split your all exec statements into 2 parts.
Sometimes to write a longer and clearlier script is the best way than multiplex and with errors
Hope this helps
February 8, 2005 at 7:44 am
Did you get it to work? I would try following Remi's advice by doing something like this:
FETCH NEXT FROM servername_cursor INTO @servername
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SET @servername = Replace(@servername, '\', '\\')
--Print @servername
February 8, 2005 at 11:11 am
I believe that '[' + @servername + ']' is what you are looking for. This will handle named instances for you in your script.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
February 9, 2005 at 1:17 am
Hey, this script is working well for me. what is your server may be key words are used for databaases
When i run that script i get
(18 row(s) affected)
My Blog:
February 9, 2005 at 1:21 am
Yes it runs, but when you use Servers with instance names it didn´t work.
I will spend a lot of time to fix the problem, but i don´t have checked the messages from the other guys.
I will do this later this day an post a feedback here.
Kind Regards
Michael
February 9, 2005 at 1:31 am
include [
EXEC ('drop table ['+ @servername+'data]')
EXEC ('SELECT name into ['+@servername+'data] FROM ['+ @servername+ '].master.dbo.sysdatabases')
My Blog:
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply