December 22, 2006 at 8:53 am
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?
December 25, 2006 at 8:00 am
This was removed by the editor as SPAM
December 29, 2006 at 7:55 am
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
December 29, 2006 at 9:04 am
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