Post reply

SP help. Trying to find databases on remote servers

  • August 12, 2009 at 10:41 am

    #137250

    I won't put the entire SP here because its not relevant but I can't get this to work at all.

    I can't get the full path to work correctly with a variable.

    declare @server sql_variant

    set @server = 'ServerName'

    drop table #databases;

    select name into #databases from @server.Master.dbo.sysdatabases

    where name != 'master' and

    name != 'model' and

    name != 'msdb' and

    name != 'tempdb'

    Any suggestions ?

    Kraig

  • August 12, 2009 at 1:56 pm

    #1038265

    you will need to use dynamic sql and make sure you have a linked server defined. You can use database_id > 4 to exclude system databases. you should used sys.databases system view because the sysdatabases view is deprecated and will be removed in future versions.

    declare @sql nvarchar(max)

    declare @server varchar(255)

    set @server = 'ServerName'

    drop table #databases;

    set @sql = '

    select name into #databases from '+@Server+'.Master.sys.databases

    where dbid > 4

    '

    exec sp_executesql @sql

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

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