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
