Openquery String太长了。最大长度为8000 [英] Openquery String is too long. Maximum length is 8000

问题描述

我收到以下错误消息

Hi ,

I am getting below error messagge

Quote：

以'SELECT AGENT_PROFILE_ID，CAN_PAYOUT_BY_CHEQUE'开头的字符串太长。最大长度为8000.

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

我通过Openquery插入数据。我的表有大约210列，并根据需要保留添加的列。



现在在我的SP中，我使用Insert into Openquery（SVR，'select col1，col2，...'）

选择col1，col2 ....



我计划将'Select * from tab1'作为openquery的内部，但不幸的是我不打算插入所有列

在我的前任中，我有210列，但我过去只插入205列。



我浏览了很多博客，我来到这里。



感谢您的帮助!!

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

推荐答案

解决这个问题的一种方法是拆分查询。



例如，第一个查询会执行插入 - 确保提供或捕获了一些唯一标识符。



第二个查询根据唯一标识符更新缺失的列。



或者，可能效率较低，从链接服务器到本地服务器上的临时表执行 SELECT * ，然后针对该服务器发出查询临时表。

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.

看看这里： OPENQUERY参数长度限制为8000个字符 [ ^ ] - 最后答案。



但是......我强烈建议你重新考虑数据库设计！一个表中的列数等于210听起来很糟糕，可能是几个麻烦的原因。抱歉...

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

您好，



根据我的理解，您应该在select查询中使用列名而不是使用* in选择查询。如果你将使用列名，那么它将解决你的问题。

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.

这篇关于Openquery String太长了。最大长度为8000的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！