[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server can't run query directly, only using openquery()

Posted on 2011-10-14
2
Medium Priority
?
905 Views
Last Modified: 2012-05-12
Hi experts,

I can't seem to run this query directly:

select acct#, CLSDCD, LKOTCD, LNTYPE, COUNCD, WARNCD From HALS.HALS.MTGLIBP1.CHMSTRAA# where acct# in (
                  74270449, 64709121)

because I get this message: Msg 7340, Level 16, State 4, Line 1
Cannot create a column accessor for OLE DB provider "IBMDASQL" for linked server "HALS".

But I can run it through an openquery statement:

select acct#, CLSDCD, LKOTCD, LNTYPE, COUNCD, WARNCD from openquery(HALS, 'select acct#, CLSDCD, LKOTCD, LNTYPE, COUNCD, WARNCD From HALS.MTGLIBP1.CHMSTRAA# where acct# in (74270449, 64709121)')

My problem is, the source table is so huge that I can't really join on it. I need to pass in a long list of acct#s (like I'm doing, just a lot longer). But the string size limit inside the open query statement is 8,000 characters. Any ideas?
0
Comment
Question by:JC_Lives
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 36969037
I fear that you are out of options, unless you can upload the list of values into a table in the HALS server, and join hence on the server itself ...
aka, do a
DELETE HALS.HALS.TEMP_JOIN_TABLE
INSERT INTO HALS.HALS.TEMP_JOIN_TABLE (acct) VALUES ( 74270449 )
INSERT INTO HALS.HALS.TEMP_JOIN_TABLE (acct)  VALUES ( 64709121)
select acct#, CLSDCD, LKOTCD, LNTYPE, COUNCD, WARNCD from openquery(HALS, 'select acct#, CLSDCD, LKOTCD, LNTYPE, COUNCD, WARNCD From HALS.MTGLIBP1.CHMSTRAA# where acct# in (select acct from TEMP_JOIN_TABLE ) ')
etc ...

hope this helps
0
 

Author Comment

by:JC_Lives
ID: 36969073
That is not what I wanted to hear! hehe ok thanks for your time :)
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question