Find all Databases on my servers

  • 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....

     

     

  • Have you tried doubling it "tkdmun02\\test01"?

  • 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.

     

  • 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.

  • 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.

     

  • That's why i can test it.. have no linked server and no other server to link to.

  • 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

  • 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

  • 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."

  • 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: http://dineshasanka.spaces.live.com/

  • 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

  • include [

    EXEC ('drop table ['+ @servername+'data]')

    EXEC ('SELECT name into ['+@servername+'data] FROM ['+ @servername+ '].master.dbo.sysdatabases')




    My Blog: http://dineshasanka.spaces.live.com/

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply