Solved

SQL - Openquery max length too long

Posted on 2013-06-03
9
1,282 Views
Last Modified: 2013-06-05
Hello Experts,

I have an extremely long query with openquery so that I can have the query resides in one database and pull information across 12 different databases from various sql instances.  It works well.  However, my query for this particular one is quite long.  What can I do to shorten it down?

Here's just a small portion of my query.

DECLARE @DBNAME VARCHAR(100)
SET @DBNAME = 'DB1'

DECLARE @SQL VARCHAR(MAX)

DECLARE @TEST_TEMP TABLE(
		ID VARCHAR(10),
		NAME VARCHAR(50),
		ADDR1 VARCHAR(80),
		ADDR2 VARCHAR(80),
		CITY VARCHAR(100),
		STATE VARCHAR(50),
		ZIPCODE VARCHAR(10)
		);
		
DECLARE TEST_CURSOR CURSOR FOR
SELECT	DB, SERVERNAME FROM DBS WHERE NAME = @DB

OPEN TEST_CURSOR

FETCH NEXT FROM TEST_CURSOR INTO @DB, @SERVER
WHILE (@@FETCH_STATUS = 0 )
BEGIN

SELECT @SQL = 
'
SELECT	DISTINCT
		ID, NAME, ADDR1, ADDR2, CITY, STATE, ZIPCODE
FROM	'+@DB+'.DBO.EMPLOYEE
'

SET @SQL = N'SELECT * FROM OPENQUERY(' + @LNKSVR + ', ''' + REPLACE(@SQL, '''', '''''') + ''')'


INSERT INTO @TEST_TEMP
EXECUTE(@SQL)

FETCH NEXT FROM TEST_CURSOR INTO @DB, @SERVER
END

CLOSE TEST_CURSOR
DEALLOCATE TEST_CURSOR

SELECT	ID, ADDR1, ADDR2, CITY, STATE, ZIPCODE
FROM	@TEST_TEMP

Open in new window


Seems like the variable @SQL can only have up to 8000 characters long and my query went past that.  Can I break the above into 2 sections?
0
Comment
Question by:holemania
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 

Author Comment

by:holemania
ID: 39216381
Is it possible to create 1 stored procedure on primary sql instance and execute that stored procedure against another database on a different sql instance?

I don't want 12 stored procedure across 12 sql instances.  Makes it hard to maintain the code.  So just curious if it's possible to create 1 stored procedure that only resides on one server, and call it to execute on another?  This might fix the above issue.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39217953
> if it's possible to create 1 stored procedure that only resides on one server, and call it to execute on another?

This is exactly what you are doing by openquery; problem is, it will get the data on the server where stored procedure is.

You can create DTS package that will copy your centralized stored procedure to each server, and then you execute it there.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39218044
Are you getting any error? If yes, can you post the error message?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:holemania
ID: 39219294
This is the error:

Msg 103, Level 15, State 1, Line 1
The character string that starts with '

SELECT DISTINCT NAME, ADDR1, ADDR2, CITY, STATE, ZIPCODE' is too long.  Maximum Length is 8000.

Above is what I get.  I can see if I can shorten down my query.  I'm using a bunch of nested queries and can be what's causing it to be long.  Just wondering if there are alternatives so that I can use openquery from one sql instances instead having to copy it to all databases.  Could do what vandimrapp1 suggested by using DTS to copy to each database.  Just would like to avoid that.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39219420
Is this the complete query? Or you just posted a sample query? This query seems to be small and not exceeding max char length. Can you print the query instead of EXEC?
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
ID: 39219520
couple of practical ways to shorten:

1. create couple of views, and replace portions of the large sql (such as subquery) by view.


Such as, instead of

select a,b from (select a,b,c from mytable where <ten lines of conditions>) derived_table ...


you use this

select a,b from myview

2. use aliases, so instead of

select myfield from mydatabase.dbo.mytable where mydatabase.dbo.mytable.myfield=1

you have

select myfield from mydatabase.dbo.mytable s where s.myfield=1

when "s" occurs many times, you'll save some bytes.

3. Plus manual work, if the query was created by some tool - if certain column is unique across all the participating tables , use column name without all the qualifiers. Also group conditions in parenthesis: the tool will create something like this:

where a=1 and b=2 or a=1 and c=3

which you can group into

where a=1 and (b=2 or c=3)




4. Then in the very end remove all unnecessary parenthesis.
0
 

Author Comment

by:holemania
ID: 39222300
The query is a bunch of unions and nested queries making it extremely large.

I was able to get it to work with 2 openqueries within the same stored procedure and then join the 2 temp tables together to get the end result.

However, I'm running into a new issue.  The stored procedure works fine if I execute it from SQL Management Studio.  All the columns are showing data.

When I run the stored procedure from SSRS 2005, it is not getting information from the 2nd openquery.  Anyone know why?
0
 

Author Comment

by:holemania
ID: 39222447
Okay got it to work now.  Was formatting of dates passed to the 2nd temp table.
0
 

Author Closing Comment

by:holemania
ID: 39222453
Thanks for the tips.  Was able to get it to work by breaking up the queries into multiple openqueries and then join the temp tables.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
Suggested Courses

742 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