Solved

SQL - Openquery max length too long

Posted on 2013-06-03
9
1,258 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SubQuery link 4 44
Select values in a row based on values in another row in sql 4 31
question about results where i dont have a match 3 36
sql update 2 37
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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 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