SQL Server with Mr. Denny

Jan 30 2013   2:00PM GMT

SSMS Masks Some Error Messages



Posted by: Denny Cherry
Tags:
SQL Server

So the other day I was working on a linked server problem where we wanted to run queries against a linked server to a SQL Server Analysis Services database so that a SQL Server stored procedure could hit the SSAS database directly.  I was just running a sample query against a database that I knew didn’t exist looking for an error message which said that the cube didn’t exist.  But what I kept getting was this.

OLE DB provider “MSOLAP” for linked server “SSAS” returned message “An error was encountered in the transport layer.”.

OLE DB provider “MSOLAP” for linked server “SSAS” returned message “The peer prematurely closed the connection.”.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider “MSOLAP” for linked server “SSAS”.

Now knowing that I have to use Windows Authentication to make this work I connected to the SQL Server’s console and ran sqlcnd and ran the same query and  got back an error about the object not existing.  OK, must be a Kerberos problem.

Well it turns out that SQL Server Management Studio doesn’t exactly display all the errors which come back from linked servers correctly because when I ran the same query from sqlcmd on my workstation I got back the missing object error message.  This tells me that it’s not a Kerberos error message that I’m chasing at all and that it’s just a bad query.

Lesson learned, when using SSMS and the query goes over a linked server to something besides SQL Server, run the query with sqlcmd to see the actual error message.

Denny

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: