CODEWITHSUNDEEP
c#.netsql-serverdatabase
Anil Namde
Anil Namde

Reputation: 6608

Need assistance with querying SQL Server

I am planning for monitoring application which will use SQL Server 2005/08 database. It will monitor old server(s) which are using SQL Server 2000 database. Tools task is to pick the data from the servers do some statistics and insert the data back into tools own database for reporting purpose.

I am using .NET(C#) as development environment.

What I would really like to do is implement stored procedures which will pick the monitoring data from other servers and insert it into the monitoring tools databases table.

Now I would like to know whether its possible to write stored procedure like this ? Or I would have to pick the data from servers in program using one connection and then insert it into tools database using the other connection?

Please let me know if in case you can foresee any complication that I should be aware of.

Also some informative/helpful pointers(links, books) will be helpful.

Thanks all for valuable inputs.

Upvotes: 2

Views: 76

Answers (2)

K Richard
K Richard

Reputation: 1984

AdaTheDev's answer is right on, but here are some additional pointers.

Make sure you give the table name an alias if you call out specific columns. Otherwise you will get an error for basically having too many qualifications (4 is the max). Also, if you are linked to an instanced server, make sure you use brackets [ ] in the server name. For example:

SELECT FiscalCalendar.Week, FiscalCalendar.Year
FROM [LinkedServer\Instance].MyDatabase.dbo.FiscalCalendar FiscalCalendar

Upvotes: 2

AdaTheDev
AdaTheDev

Reputation: 147224

You can do it using stored procedures (assuming both servers can see each other of course), by using Linked Servers.

Once setup, you can query across servers by fully qualifying the object name:

SELECT * FROM [AnotherDbServer].[DatabaseName].[Schema].[TableName]

Upvotes: 4

Related Questions