How to access/Run query on other SQL Servers

Question

How to access/Run query on other SQL Servers

Jim Covington

Old Hand

Points: 348
More actions
August 18, 2009 at 9:06 am

#138158

I have two SQL Servers on my network. One SQL 2000 and the other SQL 2005.
I wish to query files on each server and place the totals in a file on the SQL2005.
How best to run a query on a file on the 2000 box from the 2005 one?
Jim

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

you can use a linked server to do this, but you will need to enable MSDTC and distrtibuted transactions in order to pump the results into a table

once you have created a linked server you can then access it using 4 part naming

select * from [servername].[databasename].[dbo].[tablename]

however -i have found that it is much easier to create a CLR assembly on the 2005 server and use it as a stored proc so that you can query any server (regardless of whether you have a linked server or not) - there are also certain things you can't do with linked servers (such as run DBCC commands) that you can using the above method

i can post the CLR assembly and code if required.

we use this to monitor our servers and provide our daily checks (where DBCC SQLPERF(logspace) would not execute across a linked server)

MVDBA

Jim Covington Old Hand Points: 348 More actions · Answer 2

Yes, I would love to have the CLR assembly.

Thanks

Jim

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

ok

1st you need to create a database in sql2005/2008 compatibility mode to host the proc

CREATE DATABASE test

Go

then set it to allow the use of unsigned CLR -

ALTER DATABASE test SET TRUSTWORTH ON

GO

now you need to add the assembly - for this example i have used a binary format that represents the code of the compiled CLR - As a DBA i would recommend you look at the CLR code first and compile it yourself rather than trusting code posted on a forum - but here it is in case you don't have visual studio

SQL Code in file "pollrotator.rar"

then you need to bind this to a proc

USE [test]

GO

/****** Object: StoredProcedure [dbo].[Rotator2] Script Date: 08/18/2009 16:49:01 ******/

CREATE PROCEDURE [dbo].[Rotator2]

@SourceServer [nvarchar](4000),

@SourceDB [nvarchar](4000),

@SourceQuery [nvarchar](4000)

WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [PollRotator].[StoredProcedures].[Rotator2]

GO

now simply call the proc (example only)

exec test.dbo.Rotator2 @sourceserver='myotherserver', @sourcedb='master',@sourcequery='select * from sysusers'

if you want to insert this into a local table

insert into #sometable exec test.dbo.Rotator2 @sourceserver='myotherserver', @sourcedb='master',@sourcequery='select * from sysusers'

hope this helps

attached is also the entire set of c sharp code

MVDBA