February 16, 2005 at 3:31 pm
I am inserting records from SQL to DB2 through linked server using four-part name. I am connecting with DSN of iSeries AS 400 for DB2. But I am getting strange errors. I have full rights on the table as per my system admin. Basically, I cannot figure out the names of catalog and schema.
The statement I want is
SELECT * FROM <linkedserver name>.<catalog name>.<schema name>.<table name>
1. When I specify Linked server name and table name and skip the catalog and schema names using dots, giving: SELECT * FROM MyLinkedServer1...MyTable
it says:
OLE DB provider 'MyLinkedServer1' does not contain table 'MyTable'. The table either does not exist or the current user does not have permissions on that table.
2. When I repeat the same thing as above and give table name in uppercase, it says:
Invalid schema or catalog specified for provider 'MSDASQL'.
3. When I try to specify ADMINISTRATOR in schema along with linked server and table name values, it says:
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver]Invalid string or buffer length.]
4. When I do the same as in point 3 above, with any other schema name (valid or invalid) it says:
OLE DB provider '<linked server name>' does not contain table '"MySchemaName"."MyTable"'. The table either does not exist or the current user does not have permissions on that table.
Any idea what's happening. Thanks.
February 17, 2005 at 6:46 am
Do you have iSeries client installed on the SQL Server box?
Have you created the linked server on the SQL Server box?
the four part name should be:
<linked server name>.<catalog/database name>.<library/schema name>.<table name>
February 17, 2005 at 6:54 am
Try something like this,
SELECT *
FROM AS400LinkedServerName.AS400DatabaseName.AS400Library.AS400FileName
You can use the WRKRDBDIRE command to get the AS/400 database name.
December 14, 2005 at 1:03 pm
A couple of notes here. .
Using Enterprise manager On the Linked server listing of the tables connected through the “client Access” System DNS ODBC it tells you the "schema" and "catalog" names to use (on the right side of the screen once you select the tables under your linked server name) !!
and for me these needed to be all in caps ..and it was case sensitive!!
May 11, 2007 at 5:37 pm
I use select statement to select one order. It take 9 seconds.
But OPENQUERY only take 0.1 seconds to select one order.
Do anyone know why?
How can we make select statement run faster?
Our application only support select statement.
Thanks,
David
May 14, 2007 at 1:24 pm
tony,
Try comparing the execution plans for both methods from inside Query Analyzer.
November 27, 2007 at 7:31 am
From a microsoft article http://msdn2.microsoft.com/en-us/library/aa936673(sql.80).aspx
Avoiding Four-Part Naming
It is possible to access the data of a cube directly from SQL Server using queries with four-part naming. (The four parts are linked-server-name, catalog, schema, and table.) However, this option is not recommended because SQL Server attempts to copy the contents of the entire fact table and then perform the calculations for aggregating the data itself, substantially increasing the query response time.
I know that this doesn't precisely apply to your situation, but I had a similar problem when comparing performance on openquery() vs. four-part naming and this is as close as I could find to an explanation. Hope it helps.
November 27, 2007 at 8:01 am
Openquery passes the query to the linked server and it runs on the linked server. With 4 part naming the query runs on the machine the query was started on and pulls data over.
It will really make a difference if you have a lot it joins.
November 27, 2007 at 8:05 am
I recognize the advantage of using OpenQuery, but my delima is that I need to use parameter values passed into a SP w/in the OpenQuery function. i.e.:
SELECT * FROM OPENQUERY(COINS_IMAGING, '
SELECT po.ohd_dbid, po.ohd_po, cust.rcm_name
FROM pub.po_header po
INNER JOIN pub.sm_wkord wkord ON po.ohd_wonum = wkord.swo_wo_no
INNER JOIN pub.ar_cust cust ON wkord.swo_cust = cust.rcm_num
WHERE po.ohd_dbid = ''' + @Company + '''
and po.ohd_po = ''' + @PO_num + '''
');
I get an error w/ this syntax, though:
Msg 102, Level 15, State 1, Procedure uspPO_VendorName_WO, Line 13
Incorrect syntax near '+'.
I'm not sure it's possible.
November 27, 2007 at 8:13 am
I don't think you can.
From 2000 BOL, "OPENQUERY does not accept variables for its arguments."
November 27, 2007 at 10:56 am
With some help, I found a workaround!!! By creating a string that contained the OpenQuery function and arguments as I needed them to be, I was able to use the sp_ExecuteSQL sp to run the statement:
CREATE PROCEDURE dbo.uspPO_VendorName_WO
@Company nvarchar(2),
@PO_num nvarchar(50)
AS
DECLARE @SQLStr nvarchar(1000)
SET @SQLStr = N'SELECT * FROM OPENQUERY(COINS_IMAGING,''SELECT po.ohd_dbid, po.ohd_po, cust.rcm_name FROM pub.po_header po INNER JOIN pub.sm_wkord wkord ON po.ohd_wonum = wkord.swo_wo_no INNER JOIN pub.ar_cust cust ON wkord.swo_cust = cust.rcm_num WHERE po.ohd_dbid = ''''' + @Company + ''''' and po.ohd_po = ''''' + @PO_num + ''''''')'
EXEC sp_ExecuteSQL @SQLStr
November 27, 2007 at 10:59 am
Clever! Thanks for posting.
December 29, 2009 at 3:51 pm
a
October 10, 2011 at 6:12 am
Hi guys, late post but this might help someone else...
I got OPENQUERY to work with variables using the code below. The main problem I had was that I couldn't use the double-quotes and had to use single quotes (you have to replace these with two single-quotes to get it to work). Other blogs will explain why if you google it.
My first eureka moment I had was to use MS-Excel to 'Get External Data' using the DSN to verify that the linked server I had created did indeed work. If Excel can pull the data, surely SQL Server can...!
The second was to get my script working with a hard-coded variable: in this case MA103 will become a variable. Again, note that I've had to use single-quotes as doubles are not valid.
Select * from OPENQUERY(myAAdb_19, 'Select * from bpcsf.ilnl01 WHERE LSTLOT = ''MA103''')
The third was to use the PRINT commands to check that the SQL being passed was as intended: the single-quote issue is a killer!
Final SQL Query:
DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000), @slot nvarchar(4000)
SET @LinkedServer = 'myAAdb_19'
SET @slot = 'MA102'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = 'SELECT * from bpcsf.ilnl01 WHERE LSTLOT = '''''
SET @TSQL = @TSQL + @slot
SET @TSQL = @TSQL + ''''''')'
print @tsql
print @openquery+@TSQL
EXEC (@OPENQUERY+@TSQL)
December 19, 2012 at 9:03 am
a
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply