Currently work with multiple servers and need to return the results in a table, how can I do? I intend to get data from each bank and return all through a single procedure. Example, caught available space of the server and return in table format. You must be in .NET
Asked
Active
Viewed 64 times
-1
-
1"*You must be in .NET*"?? ask it politely and show something you tried !!! – huMpty duMpty Oct 26 '15 at 16:06
-
I do not know where to start. clr know only send in text form, must return table type – Fabiano Carvalho Oct 26 '15 at 16:09
-
1What do you mean by "You must in .NET"? What are you trying to do? Please show an example as it is not very clear. – Solomon Rutzky Oct 26 '15 at 16:34
1 Answers
0
First create the links to other server
Example:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'SecondServer',
@srvproduct=N'SQL Server' ;
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'SecondServer',
@locallogin = NULL ,
@useself = N'True' ;
GO
Then you can create procedure that can query multiple server.
Example:
CREATE PROCEDURE procedureName
(
@dateFrom datetime,
@dateTo datetime
)
AS
BEGIN
SELECT * FROM CurrentServerName.DatabaseName.TableName
UNION
SELECT * FROM SecondServerName.DatabaseName.TableName;
END
Aizaz Ahmed
- 210
- 1
- 6
-
Correct. It's not just these information I need, I need some information level system, which is idel use any IDE So I intend to do in .NET – Fabiano Carvalho Oct 26 '15 at 16:30
-
Thats was an reply to your question. Personally I would not go with Linked server. Follow this http://stackoverflow.com/questions/14055314/accessing-multiple-databases-from-sql-server-2008-r2-in-my-windows-application – Aizaz Ahmed Oct 26 '15 at 17:07