Solved

SQL - Openquery max length too long

Posted on 2013-06-03
9
1,191 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to base a filter depending on fields contents? 15 57
SQL: launch actions one before the other 10 23
Dcount using a date in a table compared to today's date 3 30
SQL Help 27 45
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…
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.​
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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