OPENDATASOURCE questions - SQL Server Forums

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

OPENDATASOURCE questions

Author

Topic

devils3cups
Starting Member

23 Posts

Posted - 2008-06-13 : 15:42:35

Is it possible to query another server using a sql statement and connecting though windows authentication?

Thanks

devils3cups
Starting Member

23 Posts

Posted - 2008-06-13 : 15:43:49

Ah and if they are not linked servers

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-13 : 15:51:09

You can use OPENQUERY.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

devils3cups
Starting Member

23 Posts

Posted - 2008-06-13 : 15:52:54

openqurey needs the server to be linked

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-13 : 16:01:05

It doesn't require a linked server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-14 : 00:32:33

openquery requires linked server. However you can use OPENDATASOURCE for adhoc connection to remote server:-

http://msdn.microsoft.com/en-us/library/ms179856.aspx

devils3cups
Starting Member

23 Posts

Posted - 2008-06-16 : 16:02:47

OK so I tried using OPENDATASOURCE with this query
SELECT *
FROM OPENDATASOURCE('SQLOLEDB','Data Source=pcsdev2k;Integrated Security=SSPI').kraigssand.dbo.ifox

only to get back this
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-16 : 16:55:02

Linked server(openquery) is faster than OpendataSource. We always use linked server in our environment.

devils3cups
Starting Member

23 Posts

Posted - 2008-06-17 : 10:10:47

But these machines will not be linked.
I tried using OPENDATASOURCE but got the errors above. Anyone?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-17 : 10:12:32

Did you try linking server with sql login?

devils3cups
Starting Member

23 Posts

Posted - 2008-06-17 : 10:36:22

No the servers are NOT going to be linked. We do not want them to be.

devils3cups
Starting Member

23 Posts

Posted - 2008-06-18 : 08:46:13

Bump

devils3cups
Starting Member

23 Posts

Posted - 2008-06-19 : 09:22:12

Anyone?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 11:08:53

quote:
Originally posted by devils3cups

Anyone?

Have you tried using DTS package. You could create a connection for server you want execute query against, then use a Execute SQL task to execute the query?

devils3cups
Starting Member

23 Posts

Posted - 2008-06-19 : 11:34:26

I'm curious why OPENDATASOURCE doesnt work because it would help me with other projects

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-19 : 11:36:22

Because your windows login is not trusted accross servers. Thats why I specified to go with SQL Login.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources