January 17, 2008 at 5:23 am
QUERY:
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=COM31; Trusted_Connection=yes;',
'SELECT *
FROM WorkOrderHeader') AS a;
ERROR:
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Deferred prepare could not be completed."
What is the problem here?!
January 17, 2008 at 5:27 am
ANOTHER ONE:
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=COM17\SQL2005; Trusted_Connection=yes;',
'SELECT *
FROM WorkOrderHeader
') AS a;
ERROR:
Login failed for user 'abc\xyz$'.
January 17, 2008 at 5:38 am
The connection, being trusted, would be made using the user account under which your sql server is executing. Note that this is NOT necessarily the account you're using to connect to sql server yourself. Chances are it could be NETWORK_SERVICE, LOCAL SERVICE, etc... None of these machine local accounts will have access to a SQL Server on another computer.
I suggest you set up an explicit linked server using the GUI tools and let the GUI tool both guide you and then test the connection for you. Your queries will be easier to code and understand as well 🙂
January 17, 2008 at 5:38 am
The first one usually means that there's a syntax error in the statement that you're sending to the remote server.
Thse second one is because the SQL server that you're running the query on is running as Local system (The login name in the error lessage is of the form Domain\ServerName$?) and you asked for a trusted connection.
Local system only has rights on the local machine and not on a remove SQL server
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply