September 26, 2011 at 3:27 am
I have executed following query to get Database Info from linked server... query executes successfully...but showing no result....
DECLARE @srvname varchar(100)
DECLARE @getDatabase CURSOR
declare @sql varchar(200)
SET @getDatabase = CURSOR FOR select @srvname from master.dbo.sysservers
OPEN @getDatabase
FETCH NEXT
FROM @getDatabase INTO @srvname
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql='select * from' + @srvname+ '.master.dbo.sysdatabases '
exec (@sql)
PRINT @srvname
FETCH NEXT
FROM @getDatabase INTO @srvname
END
CLOSE @getDatabase
DEALLOCATE @getDatabase
Sagar Sonawane
** Every DBA has his day!!:cool:
September 26, 2011 at 3:58 am
Try this:
DECLARE @srvname varchar(100)
DECLARE @getDatabase CURSOR
declare @sql varchar(200)
SET @getDatabase = CURSOR FOR
SELECT srvname -- << No need for a variable here, let the cursor fetch it
FROM
master.dbo.sysservers
OPEN @getDatabase
FETCH NEXT
FROM @getDatabase INTO @srvname
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql='select * from ' + QUOTENAME(@srvname)+ '.master.dbo.sysdatabases ' -- << You were missing a space here
exec (@sql)
PRINT @srvname
FETCH NEXT
FROM @getDatabase INTO @srvname
END
CLOSE @getDatabase
DEALLOCATE @getDatabase
-- Gianluca Sartori
September 28, 2011 at 12:53 am
You may run this also
exec Sp_helplinkedserver
exec sp_helplinkedsrvlogin
Ali
MCTS SQL Server2k8
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply