January 12, 2009 at 3:20 am
Hello All,
I have ODBC DSN. I created Linked server in SQLServer 2005.
When I select 'script as select to' is failing. I have getting following error.
-- [LNK_NAME].[mySCHEMA]..[DIVISION] contains no columns that can be selected or the current user does not have permissions on that object.
But, I able to query using four part query and Open query.
What could be the reason?
Thanks and Regards,
Chandu
January 12, 2009 at 4:04 am
can you post the script that you are trying to execute 🙂
January 12, 2009 at 4:16 am
Hi,
I am not running any script for creatig Linked server.
I using Management Studio Express. When you right click on server object we have option for creatinig Linked server. I enter apporiprate values. On the created Linked server context menu, I am using "script as ... select to...new query editor window".
Thanks and Regards,
Chandu
January 12, 2009 at 5:01 am
Thats the script we are trying to look to, there might be some problems with the values u have entered, please check the values and permissions for the login that you are creating linked server from 🙂
January 13, 2009 at 12:53 pm
Not sure what you are linking into, but I have a similar issue with my DB2 Linked Server. Management Studio will come back with the table names, but it will not display column names. I believe it is a limitation of the database driver. In my case there is no workaround that I am aware of.
January 13, 2009 at 10:41 pm
I am linking to custom ODBC driver implemeted usign DataDirect OpenAccess SDK.
January 16, 2009 at 9:42 am
I doubt Ent Manager can do this. Scripting in this case means "Create an SQL Statement to build the table", and that requires knowledge of a bunch of parameters, many of which are not obvious or available from various ODBC Drivers.
Especially since Ent Manager doesn't use ODBC, It uses OLEDB, in this case wrappering the driver.
ODBC can supply all the stuff required to describe a table, and there are some apps (freeware and commercial) that can do this.
It just isn't something an app like ent manager was made to do.
BTW, you can use the ms MDAC sdk to explore the tables, if you'd like. You can use supplied example to manipulate the ODBC and OLEDB interfaces directly. Including getting lists of the fields, the names, and the required identifiers. If you know how to interpret them.
November 22, 2013 at 3:41 pm
Gentlemen,
I came across this thread while searching for a solution to the original question. However, I came across something that may provide a solution, yet add a bit of mystery as well.
On my workstation I am running an instance of SQL 2008 Express (10.0.2531), and I have a linked server named "DB2TEST01" that points to a DB2 database via a system DSN that uses the DataDirect Shadow Client 7.3 ODBC driver.
Now here's the mystery... When I use SQL 2005 Management Studio, I can run the following queries and retrieve schema info from the DB2 database. However, if I execute the exact same queries from SQL 2008R2 Managment Studio, I get the error; "-- [DB2TEST01]..[DB2TAB].[<tableName>] contains no columns that can be selected or the current user does not have permissions on that object."
Interesting... 2005 can do it, but 2008 R2 can't? WTH? Any ideas gentlemen? Oh, and here are the queries;
-- Get the list of databases from DB2
SELECT DISTINCT DBNAME
FROM OPENQUERY(DB2TEST01, 'SELECT DBNAME FROM SYSIBM.SYSTABLESPACE')
ORDER BY DBNAME ASC
-- Get the list of databases and tables from DB2
SELECT DBNAME, NAME
FROM OPENQUERY(DB2TEST01, 'SELECT DBNAME, NAME FROM SYSIBM.SYSTABLESPACE')
ORDER BY DBNAME, NAME ASC
-- Get all table info from DB2
SELECT * FROM OPENQUERY(DB2TEST01, 'SELECT * FROM SYSIBM.SYSTABLES WHERE TYPE = ''T'' ')
ORDER BY NAME ASC
-- Get all view info from DB2
SELECT * FROM OPENQUERY(DB2TEST01, 'SELECT * FROM SYSIBM.SYSTABLES WHERE TYPE = ''V'' ')
ORDER BY NAME ASC
-- Get all column info from DB2
SELECT * FROM OPENQUERY(DB2TEST01, 'SELECT * FROM SYSIBM.SYSCOLUMNS')
ORDER BY TBNAME, NAME ASC
-- Get all the column names from the 'OURCUST' table
SELECT * FROM OPENQUERY(DB2TEST01, 'SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = ''OURCUST'' ')
ORDER BY COLNO ASC
August 12, 2014 at 9:20 am
Try this:
In the ODBC Data Source Administrator panel on the server, configure your linked server from System DSN.
Under the Performance tab, uncheck the box 'Enable pre-fetch of data for queries'. OK, OK.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply