Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1474
  • Last Modified:

SQL - Openquery max length too long

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
holemania
Asked:
holemania
  • 5
  • 2
  • 2
1 Solution
 
holemaniaAuthor Commented:
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
 
Vadim RappCommented:
> 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
 
SharathData EngineerCommented:
Are you getting any error? If yes, can you post the error message?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
holemaniaAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
Vadim RappCommented:
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
 
holemaniaAuthor Commented:
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
 
holemaniaAuthor Commented:
Okay got it to work now.  Was formatting of dates passed to the 2nd temp table.
0
 
holemaniaAuthor Commented:
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

Technology Partners: 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!

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now