Workaround for passing variables to openquery used in a join?

Question

Post reply

Workaround for passing variables to openquery used in a join?

Mudbugz

SSC Veteran

Points: 235
More actions
May 14, 2004 at 10:56 am

#111236

I would like to create a stored procedure that would return me a recordset based on two parameters passed in. The problem is that part of the data for the query is in a foxpro database and the openquery() function doesn't accept parameters.
@param1 must be passed in the openquery() to greatly restrict the amount of data returned from the linked server.
CREATE PROCEDURE dbo.GetData
(
     @Param1 varchar(16),
     @Param2 varchar(10)
)
AS
select foxdata.*, t1.f1, t1.f2, t2.f3
from
     OPENQUERY(SomeLinkedServer,
          'SELECT sometable1.abc, sometable1.bcd, sometable2.cde
          FROM sometable1, sometable2
          WHERE sometable1.xyz = sometable2.xyz
                AND sometable1.asdf = ''' + @Param1 + '''') foxdata
     inner join table1 t1 on t1.abc = foxdata.abc
     INNER JOIN table2 t2 ON t2.abc = foxdata.abc
where t2.someotherfield = @Param2
I've already tried creating a temporary table, and

insert into #sometemptable
   execute('select foxdata.*
from
     OPENQUERY(SomeLinkedServer,
          'SELECT sometable1.abc, sometable1.bcd, sometable2.cde
          FROM sometable1, sometable2
          WHERE sometable1.xyz = sometable2.xyz
                AND sometable1.asdf = ''' + @Param1 + '''') foxdata')
If this would work, I could use the temporary table to join the sqlserver tables. Unfortunely, the latest foxpro driver from microsoft doesn't appear to support distributed transactions.

The error message:
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]Driver not capable]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any suggestions?
May 17, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

gljjr SSChampion Points: 10080 More actions · Answer 1

Have you tried using a normal join using the fully qualified table name for the linked fox pro tables?

select a.*, b.*, t1.f1, t1.f2, t2.f3

from SomeLinkedServer.RemoteDBName.dbo.sometable1 a

JOIN SomeLinkedServer.RemoteDBName.dbo.sometable2 b ON a.xyz = b.xyz

AND a.asdf = @Param1

JOIN table1 t1 on t1.abc = a.abc

JOIN table2 t2 ON t2.abc = a.abc

where t2.someotherfield = @Param2

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

MosbachWil Old Hand Points: 363 More actions · Answer 2

Hi I have done something similar,

I think the openquery with parameters will not work directly

so I have done it by dynamic query, make cursor for org Tabel and parse

every record ??

hop it helps

@LINK varchar(50) -- name of link

@TABLE varchar(2000), -- the table name

@FIELDS varchar(2000), -- AA,AB,AC

@VALUES varchar(2000), -- 'TEST',4711,'AA'

@WHERECLAUSE varchar(2000), -- where clause

-- this is for the UPDATED table

set @QER1 = 'SELECT ' + @FIELDS + ' FROM ' + @TABLE + @WHERECLAUSE

set @QER1 = REPLACE(@QER1,char(39),char(39)+char(39))

-- do the hole thing

set @QER = 'UPDATE OPENQUERY (' + @LINK + ',' + char(39) + @QER1 + char(39)+ ')'

+ ' VALUES (' + @VALUES + ')'

DECLARE @nsql nvarchar(4000)

SET @nsql = CAST( @QER AS nvarchar(4000) )

-- do it

print @nsql

EXEC sp_executesql @nsql

wilmos