SELECT stmt across databases? 1

[JFoushee]

Hello.

I'm trying to write a simple SQL statement where the WHERE clause calls another SQL server:

 SELECT field1
 FROM table1
 WHERE field1
   NOT IN (SELECT field1
           FROM [Server2].[dbo].[DatabaseX].[Table1])
[code]
All I get is "Login failed for user '\'. " Both servers connect me as NT-authentication.
Thanks for pointing anything out of the ordinary.

 

[Cagliostro]

are you the dbo for Server2? John Fill

ivfmd@mail.md

 

[JFoushee]

The DBA has told me that I have dbo permissions.

I just now tried replacing "dbo" with my username, but I still get that same error message.

 

[Tim1]

Hiya,

Should the syntax not be:

SELECT field1
FROM table1
WHERE field1
NOT IN (SELECT field1
FROM
[Server2].[DatabaseX].[dbo].[Table1])

 

[Cagliostro]

dbo you should put after database name and before table name. John Fill

ivfmd@mail.md

 

[JFoushee]

It, too, gave the same error message, and I tried the switch of "dbo" and my username on that.

DTS suggests the order above if you look around in the design of a package.

 

[tlbroadbent]

I've found that with SQL Server 7, SP1 we must login to Server1 with SQL authentication to be able to login on Server2 (the linked server). I haven't sought a reason or a fix for that. Perhaps, someone could tell us if a later version allows NT authenticated logins to access linked servers.

BTW: The correct syntax for referencing linked servers is the four-part name - Server2.DatabaseX.dbo.Table1. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[AccessSQLUser]

I posted this same question yesterday (It's called "Help Setting Up a Linked Server&quot. I'm using SQL Server 2000 and I am able to connect to my linked SQL Server with SQL Server Authentication but I wanted to know if there was a way to connect with Windows authentication. So far I did not get a response. If anyone has the answer, please post it. Thanks!

 

[chrislarkin]

CREATE VIEW sop10100_v AS
SELECT a.*

FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=apr-erp;UID=sa;PWD=whoknew',
APRIS.dbo.SOP10100) AS a

The example above is how I connect to a SQL server across the wires in the same site. I will tell you that the performance hit is HUGE, but you can use OLE to connect. If you are getting an authentication problem, you should create an ODBC connection on the server you are executing SQL on that points to the other SQL server. This will solve the authentication error.

Regards,
Chris

 

[JFoushee]

Thanks, that worked!

 

[AccessSQLUser]

Did you get it to work with Chris' first or second suggestion?

 

[AccessSQLUser]

Chris,
If I go with the ODBC suggestion, does that mean that every time I reboot the server, I'll have to make the connection again?

 

[tlbroadbent]

I found an answer to my question regarding NT Authentication. Microsoft has posted an article for SQL Server 7.0 indicating NT Authentication will not work for distributed queries on Linked Servers. The work around from Microsoft is...

"To work around this problem, map the clients on server A to a standard security login on server B, by using either the sp_addlinkedsrvlogin stored procedure or the Security tab of the Linked Server Properties dialog box in Enterprise Manager."

Read the entire article for more info.

PRB: Message 18456 from a Distributed Query

http://support.microsoft.com/support/kb/articles/Q238/4/77.ASP

Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot