Querying a ODBC Linked Server from MSSQL

When trying to browse a linked server in MSSQL through Microsoft SQL Server Management Studio I got an error from the GUI "[LinkedServerName]...[TableName] contains no columns that can be selected or the current user does not have permissions on that object." What tha? I confirmed that the user had access through checking the properties on the connection. So what else? I tried querying it from the Query Editor directly... select * from [LinkedServerName]...[TableName] This gave another error "OLE DB Provider "MSDASQL" for linked Server "[LinkedServerName]" returned an invalid definition for table "[TableName]". WHAT THA? Ok, so did some more digging on this error and found that the error was being caused because the underlying Linked Server did not support TSQL statements. I think the linked server doesn't like the ...[TableName] portion of the query. Hmmm, so how do you get around this? Turns out there is syntax to wrap the query! SELECT * FROM OPENQUERY([LinkedServerName],'select * from [TableName]') Sweet, now it works.

Comments

Post a Comment

Popular posts from this blog

PHP timezones explained

--------------------- PHP timezones example written by Simon Hutchison - http://www.econfirm.com.au/ --------------------- date_default_timezone_set("Australia/Sydney") Sun, 02 Mar 2008 14:57:11 +1100 date_default_timezone_set("Australia/Perth") Sun, 02 Mar 2008 12:57:11 +0900 --------------------- The comments in this example assumes server is Sydney time. --------------------- Demonstrating that timezone is not important for mktime(), date("U") and time(). Get GMT 0. Uses the server time to adjust to GMT 0 so need to ensure server time is correct for these functions to be accurate. date_default_timezone_set("Australia/Perth") mktime() : 1204430231 date("u") : 1204430231 time() : 1204430231 date_default_timezone_set("Australia/Sydney") mktime() : 1204430231 date("u") : 1204430231 time() : 1204430231 --------------------- Now that we know this we can use these functions to compare to other GMT 0 adjuste...
Read more

iPhone Internet Tethering settings

iPhone Internet Tethering settings. For newer iPhones it should there already under: Settings->General->Network->Internet Tethering. If it's not there then go to this link: http://help.benm.at/help.php (on your iphone). Select on mobileconfigs which goes to here: http://help.benm.at/tethering.php Select Australia (or whatever country you are in) which goes here: http://help.benm.at/au.php Pick your provider.
Read more