Openquery String is too long. Maximum length is 8000

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi ,

I am getting below error messagge

Quote:
The character string that starts with 'SELECT AGENT_PROFILE_ID, CAN_PAYOUT_BY_CHEQUE, ' is too long. Maximum length is 8000.

I am inserting data through Openquery.My table has around 210 columns and keep on added columns based on requirement.

Now In my SP ,I am using Insert into Openquery (SVR,'Select col1,col2,...')
Select col1,col2....

I was planned to take 'Select * from tab1' as inside of openquery but unfortunately I am not going to insert all the columns
Here in my ex I had 210 cols but I used to insert into 205 cols only.

I browsed many blogs and I came here.

Thanks for your help!!

Posted 12-May-14 2:28am

pratap420

Add a Solution

3 solutions

Solution 2

One way around this would be to split the query.

For example, the first query would do the insert - making sure there is some unique identifier supplied or captured.

The 2nd query updates the missing columns based on the unique identifier.

Or, possibly less efficient, do a SELECT * from the linked server to a temporary table on your local server and then issue your query against the temp table.

Posted 12-May-14 2:56am

CHill60

Comments

Maciej Los 12-May-14 9:01am

A5!
Please, see my answer ;)

CHill60 12-May-14 9:04am

Yes! Your solution (3) is the better one

Solution 1

Hi,

As per my understanding you should use column names in select query instead of using * in select query. if you will use column names then it will solve your problem.

Posted 12-May-14 2:49am

coolnavjot31

Comments

CHill60 12-May-14 8:50am

? The OP *is* using Column names ... that's the problem

pratap420 12-May-14 8:52am

yes correct CHill. My openquery using column names only.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-05-12T02:59:00

Solution 3

Have a look here: OPENQUERY parameter length limitation of 8000 characters[^] - last answer.

But... i'd strongly recommend you to re-think the database design! The number of columns in one table equal to 210 sounds terrible and could be the reason of several troubles. Sorry...

Posted 12-May-14 2:59am

Maciej Los

Comments

CHill60 12-May-14 9:03am

5'd - I've learned (something else) new today!

Maciej Los 12-May-14 9:10am

Thank you, Caroline ;)