Remote Server Query

  • I'm trying to write a piece of code that will minimize required updates as locations are added.

    Basically what I need to do is query a remote server to see if an Employee Exists.  Since we add in offices from time to time I'd prefer not to have to update code by maintaining if-then statements.

    Therefore what I did was create a table for my Offices and attached their IP information to them (VPN connections).  For instance :

    Office = 1 / SQLIP = 192.168.0.1

    Office = 2 / SQLIP = 192.168.2.1,etc.

    What I want to do is be able to determine if a particular Login exists in a table in each office while not having to rewrite code if I add in an Office 3,etc.

    Is there any nice and easy ways to do this?

    IE, while @office>0

    begin

    if exists select login from [192.168.0.1].Database.dbo.Employee where Login=@ID

    But letting me make the 192.168.0.1 be dynamic? 

  • This was removed by the editor as SPAM

  • Here is a quick example of how you might do something like what you want to do:

    DECLARE

    @ServerName NVARCHAR(128)

    DECLARE @sqlString NVARCHAR(4000)

    CREATE

    table #tmpServers(

    SRV_NAME nvarchar(128) NOT NULL,

    SRV_PROVIDERNAME nvarchar(128) NOT NULL,

    SRV_PRODUCT nvarchar(128) NOT NULL,

    SRV_DATASOURCE nvarchar(128) NULL,

    SRV_PROVIDERSTRING nvarchar(4000) NULL,

    SRV_LOCATION nvarchar(4000) NULL,

    SRV_CAT sysname NULL

    )

    INSERT #tmpServers EXECUTE sp_linkedservers

    DECLARE

    curServers CURSOR FOR

    SELECT SRV_NAME

    FROM #tmpServers

    WHERE SRV_PROVIDERNAME = 'SQLOLEDB'

    OPEN curServers

    FETCH NEXT FROM curServers

    INTO @ServerName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @sqlString = N'SELECT Name FROM ' + @ServerName + '.master.dbo.syslogins'

    exec sp_executesql @sqlString

    FETCH NEXT FROM curServers

    INTO @ServerName

    END

    CLOSE curServers

    DEALLOCATE curServers

    DROP TABLE #tmpServers

  • The prior was an example for SQL 2000, I noticed you wanted something from SQL 2005, so here is a different example. There are new views that make it easy, actually for SQL 2000 you could go against the system table, but that is not advised as they may change something there.

    DECLARE

    @ServerName NVARCHAR(128)

    DECLARE @sqlString NVARCHAR(4000)

    DECLARE curServers CURSOR FOR

    SELECT NAME AS SRV_NAME

    FROM sys.servers

    WHERE Provider = 'SQLNCLI'

    OPEN curServers

    FETCH NEXT FROM curServers

    INTO @ServerName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @sqlString = N'SELECT Name FROM [' + @ServerName + '].master.dbo.syslogins'

    exec sp_executesql @sqlString

    FETCH NEXT FROM curServers

    INTO @ServerName

    END

    CLOSE

    curServers

    DEALLOCATE curServers

Viewing 4 posts - 1 through 3 (of 3 total)

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