我可以从存储过程返回varchar（max）吗？

VB.net web system with a SQL Server 2005 backend. I've got a stored procedure that returns a varchar, and we're finally getting values that won't fit in a varchar(8000).

带有SQL Server 2005后端的VB.net Web系统。我有一个返回varchar的存储过程,我们最终得到的值不适合varchar(8000)。

I've changed the return parameter to a varchar(max), but how do I tell the OleDbParameter.Size Property to accept any amount of text?

我已将return参数更改为varchar(max),但如何告诉OleDbParameter.Size属性接受任何数量的文本?

As a concrete example, the VB code that got the return parameter from the stored procedure used to look like:

作为一个具体的例子,从存储过程获得返回参数的VB代码看起来像:

objOutParam1 = objCommand.Parameters.Add("@RStr", OleDbType.varchar)
objOutParam1.Size = 8000
objOutParam1.Direction = ParameterDirection.Output

What can I make .Size to work with a (max)?

我能做什么。尺寸与(最大)一起工作?

Update:

To answer some questions:

回答一些问题:

For all intents and purposes, this text all needs to come out as one chunk. (Changing that would take more structural work than I want to do - or am authorized for, really.)

对于所有意图和目的,本文都需要作为一个块出来。 (改变这将需要比我想做的更多的结构性工作 - 或者我的授权,真的。)

If I don't set a size, I get an error reading "String[6]: the Size property has an invalid size of 0."

如果我没有设置大小,我会收到一个错误,读取“String [6]:Size属性的大小为0”。

10 个解决方案

#1

Upvoted Ed Altofer. (He answered first, so if you like my answer vote his too).

赞成Ed Altofer。 (他首先回答,所以如果你喜欢我的回答,他也会投票)。

OleDb is your problem. It's a generic database connection that needs to talk to more than just SQL Server, and as a result you have a lowest common denominator situation where only the weakest composite feature set can be fully supported. One of the lost features is varchar(max) support.

OleDb是你的问题。它是一个通用的数据库连接,需要与SQL Server进行通信,因此您有一个最小的公分母情况,其中只能完全支持最弱的复合特征集。其中一个丢失的功能是varchar(max)支持。

You're using SQL Server 2005 and VB.Net. What's stopping your from using System.Data.SqlClient rather than System.Data.OleDb?

您正在使用SQL Server 2005和VB.Net。是什么阻止您使用System.Data.SqlClient而不是System.Data.OleDb?

Edit
I found the documentation on the issue. See here:
http://msdn.microsoft.com/en-us/library/ms131035.aspx

编辑我找到了有关该问题的文档。请参见此处:http://msdn.microsoft.com/en-us/library/ms131035.aspx

The relevant portion:

#2

Can you use ADO.NET?

你能用ADO.NET吗?

Edit: To clarify, I am just suggesting that you might want to consider ADO.NET since you're working with VB.NET 2005 and SQL Server 2005--OLEDB was the pre-.NET way of accessing databases, so you may find more flexibility by using ADO.NET instead.

编辑:为了澄清,我只是建议你可能想要考虑ADO.NET,因为你正在使用VB.NET 2005和SQL Server 2005 - OLEDB是访问数据库的pre -.NET方式,所以你可能会发现通过使用ADO.NET来提高灵活性。

You shouldn't return VARCHARs from a stored procedure. I'm not even sure you can.

您不应该从存储过程返回VARCHAR。我甚至不确定你能不能。

However, if you use an OUT parameter, you shouldn't have to specify it by size. For example:

但是,如果使用OUT参数,则不必按大小指定它。例如:

SqlParameter p = new SqlParameter("@RStr", SqlDbType.VarChar);
p.Direction = ParameterDirection.Output;

Not sure whether this will suit your needs, but it should work just fine.

不确定这是否符合您的需求,但它应该可以正常工作。

#3

I think using -1 for the size would work. At least it should with ADO.NET. Like this:

我认为使用-1作为大小会起作用。至少它应该与ADO.NET。像这样:

objOutParam1 = objCommand.Parameters.Add("@RStr", OleDbType.varchar, -1)

objOutParam1 = objCommand.Parameters.Add(“@ RStr”,OleDbType.varchar,-1)

This is a long article, but it shows using -1 in the last example:

这是一篇很长的文章,但它在最后一个例子中显示使用-1:

http://msdn.microsoft.com/en-us/library/bb399384.aspx

#4

Have you tried not specifying the size?
Could you return a TEXT instead of a VARCHAR(MAX)?

你试过不指定尺寸吗?你可以返回TEXT而不是VARCHAR(MAX)吗?

#5

What does this large string look like? Is it perhaps something that could be better returned through an additional record set, or is it just note text?

这个大字符串是什么样的?它是否可能通过额外的记录集更好地返回,或者只是注释文本?

#6

Have you tried specifying:

你试过指定:

objOutParam1.Size = Int32.MaxValue;

#7

Just use int.MaxValue for the parameter size. The byte[] out of the sproc will be of the correct length. (I'm acutally using varbinary but the results will be the same).

只需使用int.MaxValue作为参数大小。 sproc中的byte []将具有正确的长度。 (我实际上使用varbinary但结果将是相同的)。

 param.Size = int.MaxValue;
 param.SqlDbType = SqlDbType.VarBinary;

#8

Have you tried with "OleDbType.LongVarChar", this type maps to Text in SQL server 2K, and lets you retrieve more than 8K characters.

您是否尝试使用“OleDbType.LongVarChar”,此类型映射到SQL Server 2K中的Text,并允许您检索超过8K的字符。

#9

The -1 option works pretty well. I use it in several cases where I have a varchar(max) return from a stored proc.

-1选项效果很好。我在几种情况下使用它,我从存储过程中返回varchar(max)。

#10

-1

The short answer is use TEXT instead of VARCHAR(max). 8K is the maximum size of a database page, where all your data columns should fit in except BLOB and TEXT. Meaning, your available capacity is less than 8k because of your other columns.

简短的回答是使用TEXT而不是VARCHAR(max)。 8K是数据库页面的最大大小,除了BLOB和TEXT之外,所有数据列都应该适合。这意味着,由于您的其他列,您的可用容量小于8k。

BLOB and TEXT is so Web 1.0. Bigger rows mean bigger database replication time, and bigger file I/O. I suggest you maintain a separate file server with an HTTP interface for that.

BLOB和TEXT就是Web 1.0。较大的行意味着更大的数据库复制时间和更大的文件I / O.我建议您维护一个带有HTTP接口的单独文件服务器。

And, for the previous column

而且,对于上一篇专栏文章

DataUrl VARCHAR(255) NOT NULL,

DataUrl VARCHAR(255)NOT NULL,

When inserting a new row, first compute the MD5 checksum of the data. Second, upload the data to the file server with the checksum as the filename. Third, INSERT INTO ...(...,DataUrl) VALUES(..., "http://fileserver/get?id=" . md5_checksum_data)

插入新行时,首先计算数据的MD5校验和。其次,将校验和作为文件名上传到文件服务器。三,INSERT INTO ...(...,DataUrl)VALUES(...,“http:// fileserver / get?id =”。md5_checksum_data)

With this design, your database will stay calm even if the average data size becomes 1000x.

使用此设计,即使平均数据大小变为1000倍,您的数据库也将保持平静。

#1