Solved

SQL - Openquery max length too long

Posted on 2013-06-03
9
1,115 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
  • 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 40

Expert Comment

by:Sharath
ID: 39218044
Are you getting any error? If yes, can you post the error message?
0
 

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 40

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now