Equivalent Query to Another Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Equivalent Query to Another Server

Author

Topic

rockster8
Starting Member

7 Posts

Posted - 2007-06-11 : 12:12:59

Hello all,
I'm making a query for this application that is not in the same server where the database is located.

This query I can easily get results when making the query within the server:

USE ADW_Publish;
SELECT distinct b.Subject
FROM dbo.F_Class_Exam a
LEFT OUTER JOIN dbo.D_Course_Catalog b
ON a.Course_ID = b.Course_ID
WHERE a.Term = '$536870994$' AND a.Class_Exam_Type = 'FIN' AND b.Term = '$536870994$'
ORDER BY b.Subject

Here are the details:
Servername: SERVER01

What would an equivalent query be if I apply this to an external application (located outside the server?)

I've gotten some hints to use:
SELECT *
FROM OPENDATASOURCE('MSDASQL', 'DataSource=DataSourceName;UserID=User;Password=SomePassword').DBName.dbo.TableName

But I dont know how to apply the query above to use that..

Any thoughts? Or are there other ways of doing it?

Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-11 : 12:17:44

Have a look at openrowset.

Easier to add a linked server then
exec linkedserver.ADW_Publish.dbo.sp_executesql N'SELECT distinct b.Subject
FROM dbo.F_Class_Exam a
LEFT OUTER JOIN dbo.D_Course_Catalog b
ON a.Course_ID = b.Course_ID
WHERE a.Term = ''$536870994$'' AND a.Class_Exam_Type = ''FIN'' AND b.Term = ''$536870994$''
ORDER BY b.Subject'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rockster8
Starting Member

7 Posts

Posted - 2007-06-11 : 12:25:51

hello nr,
thanks! i have one question though. how will the server know i am authorized to make this query? do i need to have some login procedure? do i have to give the userid and password?

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-11 : 13:08:30

If you create a linked server, give yourself permissions on it then your login gets mapped to the linked server login. There is an option to use your own login or another one for the linked server.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rockster8
Starting Member

7 Posts

Posted - 2007-06-11 : 16:06:48

hello,
sorry i am not quite familiar with linked server.
is there any way to convert the query i posted above to use opendatasource?

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-12 : 09:54:27

try

select * from
openrowset('MSDASQL', 'driver={sql server};server=serverName;UID=User;Pwd=SomePassword','SELECT distinct b.Subject
FROM ADW_Publish.dbo.F_Class_Exam a
LEFT OUTER JOIN ADW_Publish.dbo.D_Course_Catalog b
ON a.Course_ID = b.Course_ID
WHERE a.Term = ''$536870994$'' AND a.Class_Exam_Type = ''FIN'' AND b.Term = ''$536870994$''
ORDER BY b.Subject') t

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources