Query Size Limitation 1

[LilProgrammerGirl]

Hello all,
I have a query that will run perfectly fine in Oracle, but when I put it into SQL Query Analyzer and try to run it I get a syntax error. It used to run fine on the SQL Server, however now I had to update the query and add codes into a criteria's "IN ()" clause. It made the query significantly bigger and will not run anymore. Has anyone ran into this problem before? What can I do to fix it? I would hate to break the query up and for execution and then mesh it together later....

I run the query using:

select * from openquery(server, '
here's the query
')

Thank you!!
Hailey

 

[philhege]

Hi Hailey,

Are you escaping any single quotes that might happen to show up in the query statement?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[LilProgrammerGirl]

Hey Phil,
Well, what the query would look like as far as single quotes, for example, is this:

select * from openquery(server, '
select * from table.a
where site = ''1A''
 and where key in (''AB'',''AC'',''AD'',''AE'',''AF'')
')

Hailey

 

[LilProgrammerGirl]

Is there a better way of escaping the single quotes than how I am doing it above?

Hailey

 

[SQLBill]

Are you using any variables? According to the BOL, OPENQUERY does not accept variables.

Have you tried double clicking on the error message? It will 'jump' back to the upper pane and highlight where it 'sees' the error. (The error could actually be a line or two above/below the highlighted one, but it will give you the general area).

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[LilProgrammerGirl]

Nope, not using any variables. The error message I get is:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '
SELECT A.ACCOUNT_NUMBER
 ,H.HOUSE_KEY
 ,S.NAME
 ,S.ADDRESS_1
 ,S.ADDRESS.

 

[SQLSister]

HOw long is the query? Openquery has an abosulte limit of 8000 characters in the SQL statement.

http://support.microsoft.com/default.aspx?scid=kb;en-us;286370

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[LilProgrammerGirl]

A-ha - that's what I was looking for.. thank you SQL Sister. I just threw my query into Word and did a character count and it was 8,525 characters without spaces. It's a monster. 8,000 characters?!?!? How weak! ;-)

 

[SQLBill]

From you post, it appears you want to run this query on Oracle via SQL Server. Why not create a procedure/package on Oracle with the script and then just use SQL Server to run the procedure/package?

-SQLBill

Posting advice: FAQ481-4875

 

[LilProgrammerGirl]

No, I was saying that it does run on Oracle just fine, but not in SQL Server. SQL Server is where it needs to run. Our Oracle server is going away and I am converting everything from Oracle to SQL :-( What a mess... I just have to break the query down into seperate chunks...bummer!

 

[SQLBill]

Okay, I'm confused. Why are you using OPENSERVER if you are running it only on SQL Server?

OPENSERVER is used when you are running a script on SQL Server that is accessing an Oracle server.

-SQLBill

Posting advice: FAQ481-4875

 

[LilProgrammerGirl]

Oh, yes, sorry. The Oracle server I am trying to hit is at our Corporate offices and are read-only. We cannot create procedures on their Oracle Server. I should have explained that, but I didn't think of it. Sorry!!! I had an Oracle Server here, locally, but it's going away and they gave me the SQL Server to convert everything over to. The Corporate Oracle server is staying, but I have to report off of it through my SQL Server.

 

[philhege]

Could you create a link server to the Oracle instance? You could then use the four-part naming convention and dump OPENQUERY.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[philhege]

And I must add it's a shot in the dark because I'm not familiar with Oracle's schema.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.