Converting to OpenQuery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Converting to OpenQuery

Author

Topic

MaritimeMadness
Starting Member

10 Posts

Posted - 2009-06-16 : 11:14:15

I am currently running a storedproc that just takes way to long to return. It looks like this:

[code]
@InvoiceNo int

SELECT INVOICE."INVOICENO", INVOICE."ISSUEDATE", INVOICE."BRANCH_LINKNO", INVOICE."RECORDLOCATOR", INVOICE."PAYSTATUS_LINKCODE", INVOICE."INVOICETYPE_LINKCODE", INVOICE."INVOICENUMBER", BOOKING."BOOKINGNO", BOOKING."DEPARTDATE", BOOKING."FINALPAYDATE", BOOKING."RETURNDATE", BOOKING."TICKETTYPE_LINKNO", BOOKING."ORIGTICKETNO", BOOKING."STARTINGTICKETNO", BOOKING."ITINERARY", BOOKING."CLIENTPAYSTATUS_LINKCODE", BOOKING."SUBMITTO_LINKCODE", BOOKING."CLIENTREMARKS", BOOKING."CONFIRMNO", BOOKING."PASSENGERDEPT", BOOKING."PASSENGERNAME", BOOKING."TAX1AMT", BOOKING."TAX2AMT", BOOKING."TAX3AMT", BOOKING."TAX4AMT", BOOKING."TOTALFARE", BOOKING."CLIENTGSTAMT", BOOKING."CALCBASEFARE",
INVOICETYPE."INVOICETYPE",
BRANCH."BRANCHNO", BRANCH."ADDRESS" as BranchAddress, BRANCH."CITYSTZIP" as BranchCity, BRANCH."NAME" as BranchName,
BRANCH."BRANCHID",
PROFILE_client."PROFILETYPE_LINKCODE", PROFILE_client."NAME" as ClientName, PROFILE_client."FIRSTNAME", PROFILE_client."LASTNAME", PROFILE_client."MIDDLEINIT", PROFILE_client."COURTESYTITLE",
PROFILE_vendor."NAME" as VendorName,
PROFILE_agent."NAME" as AgentName,
ADDRESS_client."ADDRESS1" as ClientAddress1, ADDRESS_client."ADDRESS2" as ClientAddress2,
ADDRESS_client."CITY" as ClientCity, ADDRESS_client."STATE" as ClientProvince, ADDRESS_client."ZIP" as ClientPostalCode,
TRAVELTYPE."TRAVELTYPENO", TRAVELTYPE."TRAVELCATEGORY_LINKNO", TRAVELTYPE."TRAVELTYPE",
ADDRINSTANCE_client."ADDRTYPE_LINKNO",
ADDRESS_vendor."ADDRESS1" as VendorAddress1, ADDRESS_vendor."ADDRESS2" as VendorAddress2,
ADDRESS_vendor."CITY" as VendorCity, ADDRESS_vendor."STATE" as VendorProvince, ADDRESS_vendor."ZIP" as VendorPostalCode,
TRAVELCATEGORY."TRAVELCATEGORY",
ADDRINSTANCE_vendor."ADDRTYPE_LINKNO"
FROM
{ oj ((((((((((((Trams.dbo.INVOICE INVOICE LEFT OUTER JOIN Trams.dbo.AGENTBKG AGENTBKG ON
INVOICE."FIRSTINSIDEAGENTBKG_LINKNO" = AGENTBKG."AGENTBKGNO")
LEFT OUTER JOIN TramsEasySoft4...INVOICETYPE INVOICETYPE ON
INVOICE."INVOICETYPE_LINKCODE" = INVOICETYPE."INVOICETYPECODE")
LEFT OUTER JOIN Trams.dbo.BRANCH BRANCH ON
INVOICE."BRANCH_LINKNO" = BRANCH."BRANCHNO")
INNER JOIN Trams.dbo.PROFILE PROFILE_client ON
INVOICE."CLIENT_LINKNO" = PROFILE_client."PROFILENO")
LEFT OUTER JOIN Trams.dbo.BOOKING BOOKING ON
INVOICE."INVOICENO" = BOOKING."INVOICE_LINKNO")
LEFT OUTER JOIN Trams.dbo.PROFILE PROFILE_agent ON
AGENTBKG."AGENT_LINKNO" = PROFILE_agent."PROFILENO")
INNER JOIN Trams.dbo.ADDRESS ADDRESS_client ON
PROFILE_client."PROFILENO" = ADDRESS_client."PROFILE_LINKNO")
LEFT OUTER JOIN Trams.dbo.TRAVELTYPE TRAVELTYPE ON
BOOKING."TRAVELTYPE_LINKNO" = TRAVELTYPE."TRAVELTYPENO")
LEFT OUTER JOIN Trams.dbo.PROFILE PROFILE_vendor ON
BOOKING."VENDOR_LINKNO" = PROFILE_vendor."PROFILENO")
INNER JOIN Trams.dbo.ADDRESS ADDRESS_vendor ON
PROFILE_vendor."PROFILENO" = ADDRESS_vendor."PROFILE_LINKNO")
INNER JOIN TramsEasySoft4...ADDRINSTANCE ADDRINSTANCE_client ON
ADDRESS_client."PROFILE_LINKNO" = ADDRINSTANCE_client."PROFILE_LINKNO" AND
ADDRESS_client."ADDRESSNO" = ADDRINSTANCE_client."ADDRESS_LINKNO")
LEFT OUTER JOIN Trams.dbo.TRAVELCATEGORY TRAVELCATEGORY ON
TRAVELTYPE."TRAVELCATEGORY_LINKNO" = TRAVELCATEGORY."TRAVELCATEGORYNO")
INNER JOIN TramsEasySoft4...ADDRINSTANCE ADDRINSTANCE_vendor ON
ADDRESS_vendor."PROFILE_LINKNO" = ADDRINSTANCE_vendor."PROFILE_LINKNO" AND
ADDRESS_vendor."ADDRESSNO" = ADDRINSTANCE_vendor."ADDRESS_LINKNO"}
WHERE
INVOICE."INVOICENO" = @InvoiceNo AND
ADDRINSTANCE_client."ADDRTYPE_LINKNO" = 1 AND
ADDRINSTANCE_vendor."ADDRTYPE_LINKNO" = 1
ORDER BY
BRANCH."BRANCHNO" ASC,
INVOICE."INVOICENO" ASC
[code/]

I want to convert it to be dynamic and use OPENQUERY. So far I have this with no dice:

[code]
declare @InvoiceNo int
set @InvoiceNo = '22'

DECLARE @OPENQUERY varchar(MAX),@LinkedServer varchar(MAX)

SET @LinkedServer = 'TramsEasySoft'

SET @OPENQUERY = 'SELECT INVOICE."INVOICENO", INVOICE."ISSUEDATE", INVOICE."BRANCH_LINKNO", INVOICE."RECORDLOCATOR",
INVOICE."PAYSTATUS_LINKCODE", INVOICE."INVOICETYPE_LINKCODE", INVOICE."INVOICENUMBER", BOOKING."BOOKINGNO",
BOOKING."DEPARTDATE", BOOKING."FINALPAYDATE", BOOKING."RETURNDATE", BOOKING."TICKETTYPE_LINKNO", BOOKING."ORIGTICKETNO",
BOOKING."STARTINGTICKETNO", BOOKING."ITINERARY", BOOKING."CLIENTPAYSTATUS_LINKCODE", BOOKING."SUBMITTO_LINKCODE",
BOOKING."CLIENTREMARKS", BOOKING."CONFIRMNO", BOOKING."PASSENGERDEPT", BOOKING."PASSENGERNAME", BOOKING."TAX1AMT",
BOOKING."TAX2AMT", BOOKING."TAX3AMT", BOOKING."TAX4AMT", BOOKING."TOTALFARE", BOOKING."CLIENTGSTAMT", BOOKING."CALCBASEFARE",
INVOICETYPE."INVOICETYPE", BRANCH."BRANCHNO", BRANCH."ADDRESS" as BranchAddress, BRANCH."CITYSTZIP" as BranchCity,
BRANCH."NAME" as BranchName, BRANCH."BRANCHID", PROFILE_client."PROFILETYPE_LINKCODE", PROFILE_client."NAME" as ClientName,
PROFILE_client."FIRSTNAME", PROFILE_client."LASTNAME", PROFILE_client."MIDDLEINIT", PROFILE_client."COURTESYTITLE",
PROFILE_vendor."NAME" as VendorName, PROFILE_agent."NAME" as AgentName, ADDRESS_client."ADDRESS1" as ClientAddress1,
ADDRESS_client."ADDRESS2" as ClientAddress2, ADDRESS_client."CITY" as ClientCity, ADDRESS_client."STATE" as ClientProvince,
ADDRESS_client."ZIP" as ClientPostalCode, TRAVELTYPE."TRAVELTYPENO", TRAVELTYPE."TRAVELCATEGORY_LINKNO",
TRAVELTYPE."TRAVELTYPE", ADDRINSTANCE_client."ADDRTYPE_LINKNO", ADDRESS_vendor."ADDRESS1" as VendorAddress1,
ADDRESS_vendor."ADDRESS2" as VendorAddress2, ADDRESS_vendor."CITY" as VendorCity, ADDRESS_vendor."STATE" as VendorProvince,
ADDRESS_vendor."ZIP" as VendorPostalCode, TRAVELCATEGORY."TRAVELCATEGORY", ADDRINSTANCE_vendor."ADDRTYPE_LINKNO"
FROM
{ oj ((((((((((((INVOICE INVOICE LEFT OUTER JOIN AGENTBKG AGENTBKG ON
INVOICE."FIRSTINSIDEAGENTBKG_LINKNO" = AGENTBKG."AGENTBKGNO")
LEFT OUTER JOIN INVOICETYPE INVOICETYPE ON
INVOICE."INVOICETYPE_LINKCODE" = INVOICETYPE."INVOICETYPECODE")
LEFT OUTER JOIN BRANCH BRANCH ON
INVOICE."BRANCH_LINKNO" = BRANCH."BRANCHNO")
INNER JOIN PROFILE PROFILE_client ON
INVOICE."CLIENT_LINKNO" = PROFILE_client."PROFILENO")
LEFT OUTER JOIN BOOKING BOOKING ON
INVOICE."INVOICENO" = BOOKING."INVOICE_LINKNO")
LEFT OUTER JOIN PROFILE PROFILE_agent ON
AGENTBKG."AGENT_LINKNO" = PROFILE_agent."PROFILENO")
INNER JOIN ADDRESS ADDRESS_client ON
PROFILE_client."PROFILENO" = ADDRESS_client."PROFILE_LINKNO")
LEFT OUTER JOIN TRAVELTYPE TRAVELTYPE ON
BOOKING."TRAVELTYPE_LINKNO" = TRAVELTYPE."TRAVELTYPENO")
LEFT OUTER JOIN PROFILE PROFILE_vendor ON
BOOKING."VENDOR_LINKNO" = PROFILE_vendor."PROFILENO")
INNER JOIN ADDRESS ADDRESS_vendor ON
PROFILE_vendor."PROFILENO" = ADDRESS_vendor."PROFILE_LINKNO")
INNER JOIN ADDRINSTANCE ADDRINSTANCE_client ON
ADDRESS_client."PROFILE_LINKNO" = ADDRINSTANCE_client."PROFILE_LINKNO" AND
ADDRESS_client."ADDRESSNO" = ADDRINSTANCE_client."ADDRESS_LINKNO")
LEFT OUTER JOIN TRAVELCATEGORY TRAVELCATEGORY ON
TRAVELTYPE."TRAVELCATEGORY_LINKNO" = TRAVELCATEGORY."TRAVELCATEGORYNO")
INNER JOIN ADDRINSTANCE ADDRINSTANCE_vendor ON
ADDRESS_vendor."PROFILE_LINKNO" = ADDRINSTANCE_vendor."PROFILE_LINKNO" AND
ADDRESS_vendor."ADDRESSNO" = ADDRINSTANCE_vendor."ADDRESS_LINKNO"}
WHERE
INVOICE."INVOICENO" = 22 AND
ADDRINSTANCE_client."ADDRTYPE_LINKNO" = 1 AND
ADDRINSTANCE_vendor."ADDRTYPE_LINKNO" = 1
ORDER BY
BRANCH."BRANCHNO" ASC,
INVOICE."INVOICENO" ASC'

EXEC @OPENQUERY

[code/]

I am having issues because of the parameter I need as part of the query which I hear is not allowed. Also, this complex join has my head spinning. Does anyone have a solution or can point me in the right direction?

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-16 : 11:32:56

Something like this?
Can add the variables to the string.

select @openquery = 'select * from openquery(' + @LinkedServer + ',"' + @openquery + '")'
exec (@openquery)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MaritimeMadness
Starting Member

10 Posts

Posted - 2009-06-16 : 11:38:12

I get this error when I try that suggestion:

The name 'select * from openquery(TramsEasySoft,"SELECT INVOICE."INVOICENO", INVOICE."ISSUEDATE", INVOICE."BRANCH_LINKNO", INVOICE."RECORDLOCATOR",
INVOICE."PAYSTATUS_LINKCODE", INVOICE."INVOICETYPE_LINKCODE", INVOICE."INVOICENUMBER", BOOKING."BOOKINGNO",
BOOKING."DEPARTDATE", BOOKING."FINALPAYDATE", BOOKING."RETURNDATE", BOOKING."TICKETTYPE_LINKNO", BOOKING."ORIGTICKETNO",
BOOKING."STARTINGTICKETNO", BOOKING."ITINERARY", BOOKING."CLIENTPAYSTATUS_LINKCODE", BOOKING."SUBMITTO_LINKCODE",
BOOKING."CLIENTREMARKS", BOOKING."CONFIRMNO", BOOKING."PASSENGERDEPT", BOOKING."PASSENGERNAME", BOOKING."TAX1AMT",
BOOKING."TAX2AMT", ' is not a valid identifier.

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-16 : 11:42:49

Did you put brackets round the @openquery?

should be
select @openquery = 'select * from openquery(' + @LinkedServer + ',''' + @openquery + ''')'
exec (@openquery)

may also need
select @openquery = 'select * from openquery(' + @LinkedServer + ',''' + replace(@openquery,'''','''''') + ''')'
exec (@openquery)

It's best to start with a simple query then build up
try
select @openquery = select a=1
select @openquery = 'select * from openquery(' + @LinkedServer + ',''' + replace(@openquery,'''','''''') + ''')'
select @openquery
exec (@openquery)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MaritimeMadness
Starting Member

10 Posts

Posted - 2009-06-16 : 11:51:43

When i use this:
-----------------------------------------------------------------
select @openquery = 'select * from openquery(' + @LinkedServer + ',''' + replace(@openquery,'''','''''') + ''')'

EXEC (@OPENQUERY)
-----------------------------------------------------------------
I get:

Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "ADDRTYPE_LINKNO" is a duplicate.

When I use the first method I get:

An error about the invoice variable

MaritimeMadness
Starting Member

10 Posts

Posted - 2009-06-16 : 11:53:51

I know I should build small first then add, but I'm in a time crunch and I was hoping to find a quick way to convert. Sometimes I guess it's just not posible.

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-16 : 11:59:08

That's a problem with your query

ADDRINSTANCE_client."ADDRTYPE_LINKNO",
ADDRINSTANCE_vendor."ADDRTYPE_LINKNO"

You need to alias at least one of those.
Also would be better to use [] rather than "" for identifier delimiters if the destination is sql server.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MaritimeMadness
Starting Member

10 Posts

Posted - 2009-06-16 : 12:45:21

That was definitely the issue. Thanks so much for your help. This enabled us to trim our return time from 2:13 to :06sec! Mush better.

Thanks Again!

MaritimeMadness
Starting Member

10 Posts

Posted - 2009-06-16 : 12:52:21

Much better! lol

However... unfortunately we got excited a little too soon over here. The issue still remains on how we get around using our parameter (@InvoiceNo) in the WHERE clause. We do not want it to be hard coded obviously as '22'. How do we get around this?

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-16 : 13:22:01

change
INVOICE."INVOICENO" = 22 AND

to

INVOICE."INVOICENO" = ' + convert(varchar(20),@InvoiceNo) + ' AND

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MaritimeMadness
Starting Member

10 Posts

Posted - 2009-06-16 : 13:26:16

PERFECT!

I was trying something similar with the ++'s inside the brackets not out.

Thanks again for all you help nr. Much appreciated!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources