May 5, 2009 at 3:10 am
Hi All,
I have a requirement of writing a stored procedure which gets me the data of all the sqlservers. I have do this using linked server.
As I am a not got in programming.
Any kind of help is greatly appreciated.
Regards
Hassan
May 5, 2009 at 3:54 am
Hello Hassan,
Have the Linked Servers already been configured?
If so, the main work has been done. You can gather data from Tables and Views on the Linked Servers simply by using the Four Part Name e.g.
Select MyColumn1, MyColum2 From MyLinkedServer1.MyDB1.MySchema1.MyTable1
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
May 5, 2009 at 4:08 am
Hi John,
Thanks for immediate reply..I have configure the linked server and was able to query the data by manually adding the linked servername to the select query.
My requirement is to make this a dynamic stored procedure where I just need to pass the linked server name and it should fetch me the list of databases of the linked server.
Below is my stored proc
___________________
CREATE PROCEDURE sp_ServerList
-- Parameters for the stored procedure here
@srvname varchar(20) --server name
AS
Declare @check varchar(20)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Select srvname from master.sys.sysservers where srvname=@srvname
--Below query fails to compile
select *from @srvname.master.sys.sysdatabases
END
GO
Please help me to create such procedure..
Thanks again
Hassan
May 5, 2009 at 4:48 am
Hello Hassan,
“select *from @srvname.master.sys.sysdatabases” won’t work.
You could use dynamic SQL for this i.e. build the Select statement inside a string variable and then execute it.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply