SSMS Masks Some Error Messages

Published On: 2013-01-30By:

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

Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via