Click here to Skip to main content
14,382,805 members
Rate this:
Please Sign up or sign in to vote.
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
Rate this:
Please Sign up or sign in to vote.

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.
   
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
Rate this:
Please Sign up or sign in to vote.

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...
   
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 ;)
Rate this:
Please Sign up or sign in to vote.

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

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100