March 8, 2005 at 2:21 pm
Has anyone gotton a link to Informix DB, with the ability to run a query with paramaters, which OpenQuery method does not support. I can get this to work, but have a severe performance hit: Looking at the query optimizer in I can see that first the SQL pulls metadata out of the system tables, which is creating lots of overhead. I have tried several different options of selecting informix information into SQL server. I have had either an error or an extreme performance hit. Inside of Informix you can see the SQL that was parsed to the query optimizer. In the instances where the SQL worked, but took a performance hit, the SQL that was being passed was pulling metadata out of system tables.
I have tried linking useing the ODBC connection
EXEC sp_addlinkedserver --USED TO ADD SERVER USING ODBC CONNECTIONS
@server = @strServerName,
@provider = 'MSDASQL',
@srvproduct = 'Informix', --Infomix
@datasrc = @strDSN,
@catalog = @strDBName
I have been able to get this method to work through a straight SELECT but with the performance hit described above:
SELECT aisle FROM u028win1_win_soc.WINDB.informix.aisle WHERE aisle = 1003
This also works, but with performance hit
SET @sql = 'SELECT * FROM u028win1_win_soc.WINDB.informix.aisle WHERE aisle = 1003'
EXEC sp_executesql @sql
This method has also worked using the OPENQUERY method and does not appear to have the same performance hit as using the four part name.. HOWEVER I need to be able to use a paramater and OPENQUERY does not allow the use of variables:
For example this works:
SELECT * FROM OPENQUERY(u028win1_win_soc,'SELECT aisle FROM aisle WHERE aisle = 1003')
This does not work (as SQL server does not support it)
SET @sql = 'SELECT * FROM aisle WHERE aisle = 1003'
SELECT * FROM OPENQUERY(u028win1_win_soc,@sql)
I have also tried to linking using an IBM driver
EXEC sp_addlinkedserver -- USED TO ADD SERVER USING IBM INFORMIX OLE DB PROVIDER
@server = 'u028win1_win_soc',
@provider = 'Ifxoledbc',
@srvproduct = 'Informix',
@datasrc = 'WINDB@u028win1_win_soc'
Using this method, the four part name will not work (This does NOT Work)
SELECT aisle FROM u028win1_win_soc.WINDB.informix.aisle WHERE aisle = 1003
RETURNS ERROR FROM Statement above
In Straight SELECT Method returns error
Server: Msg 7312, Level 16, State 1, Line 15
Invalid use of schema and/or catalog for OLE DB provider 'Ifxoledbc'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.
Using the OPENQUERY method did return records, however this still does not meet the need of using a paramater in the SQL that is sent
SELECT * FROM OPENQUERY(u028win1_win_soc,'SELECT aisle FROM aisle WHERE aisle = 1003')
March 9, 2005 at 1:13 am
hi
i had the same problem with oracle, so i done this procedure,you can put the query in @QER for the linked database, itworks fine for me
CREATE PROCEDURE dbo.syp_DOLINKQUERY1
@QER varchar(2000)
AS
-- query a linked table
-- wm 01.2005
declare @QER1 varchar(2000)
declare @LINK varchar(100)
DECLARE @nsql nvarchar(4000)
set @LINK = 'YOURLINK'
set @QER1 = REPLACE(@QER,char(39),char(39)+char(39))
set @QER = 'select * from openquery (' + @LINK + ',' + char(39) + @QER1 + char(39)+ ') where 1=0'
SET @nsql = CAST( @QER AS nvarchar(4000) )
EXEC sp_executesql @nsql
return
GO
wilmos
March 9, 2005 at 1:23 am
here is another one for insert
CREATE PROCEDURE dbo.syp_DOLINKINSERT
@TABLE varchar(2000), -- the table name
@FIELDS varchar(2000), -- AA,AB,AC
@VALUES varchar(2000) -- 'TEST',4711,'AA'
AS
-- do the INSERT on a linked table
-- wm 01.2004
declare @QER varchar(2000)
declare @QER1 varchar(2000)
declare @LINK varchar(100)
declare @XUSER varchar(100)
select @XUSER = user
set @LINK = 'YOURLINK'
-- this is for the linked table
set @QER1 = 'SELECT ' + @FIELDS + ' FROM ' + @TABLE + ' WHERE 1=0'
set @QER1 = REPLACE(@QER1,char(39),char(39)+char(39))
-- do the hole thing
set @QER = 'INSERT INTO OPENQUERY (' + @LINK + ',' + char(39) + @QER1 + char(39)+ ')'
+ ' VALUES (' + @VALUES + ')'
DECLARE @nsql nvarchar(4000)
SET @nsql = CAST( @QER AS nvarchar(4000) )
-- do it
EXEC sp_executesql @nsql
GO
wilmos
April 27, 2005 at 9:04 am
In this scenario, with the Where....is it possible to put a variable in this location? A string variable? The problem I have been having trying to pass in the following for @QER1
'SELECT externalaccountno FROM vaiSLaccount WHERE accountid=' + char(39) + RTrim(@Account) + char(39)
I have tried multiple variations of ' and '' and anything else I could think of, and the formatting that comes back in the error is correct. Here is what is returned:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SELECT externalaccountno FROM vaiSLaccount WHERE accountid='A6UJ9A002K5E''.
Is this even possible?
Thanks..cd
April 29, 2005 at 4:52 am
Sorry,
the dolinkquery is a bit wrong! , the where-clause is wrong for the link
--------------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.syp_DOLINKQUERY1
@QER varchar(2000)
AS
-- query a linked table
-- wm 01.2005
declare @QER1 varchar(2000)
declare @LINK varchar(100)
DECLARE @nsql nvarchar(4000)
set @LINK = 'YOURLINK'
set @QER1 = REPLACE(@QER,char(39),char(39)+char(39))
set @QER = 'select * from openquery (' + @LINK + ',' + char(39) + @QER1 + char(39)+ ')'
SET @nsql = CAST( @QER AS nvarchar(4000) )
EXEC sp_executesql @nsql
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--------------------------------------
i tryed this in the query-analyzer ond it works fine
DECLARE @rc int
DECLARE @QER varchar(2000)
declare @Acc varchar(20)
declare @Prod varchar(20)
-- Parameterwerte festlegen
set @Acc = '00987551'
set @Prod = '80024'
set @QER ='SELECT PRODUKT_NR FROM PALETTEN WHERE PALETTEN_ID= ' + char(39) + RTrim(@Acc) + char(39)
+ ' AND PRODUKT_NR = ' + char(39) + @Prod + char(39)
print @QER
EXEC @rc = [suw_pal1].[dbo].[syp_DOLINKQUERY1] @QER
the printout is:
SELECT PRODUKT_NR FROM PALETTEN WHERE PALETTEN_ID= '00987551' AND PRODUKT_NR = '80024'
(1 row(s) affected)
hope it helps
wilmos
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply