Click here to Skip to main content
15,174,232 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi ,

I am getting below error messagge

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

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.
Maciej Los 12-May-14 9:01am
Please, see my answer ;)
CHill60 12-May-14 9:04am
Yes! Your solution (3) is the better one
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...
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 ;)

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

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900