Output parameters in SQL 2005

[RayMond]

I have an extended stored procedure that returns a single nvarchar parameter, but am getting the following error when ran on a SQL2005 (Sept 2005 CTP release) server:

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 (""): Data type 0xE7 has an invalid data length or metadata length.

The code I'm using to call it looks like this:

qryMain.SQL.Text := 'master..myxproc 8, :settings OUTPUT';
qryMain.ParamByName('settings').ParamType := ptOutput;
qryMain.ParamByName('settings').DataType := ftWideString;
qryMain.ParamByName('settings').Size := 1024;


Using MadExcept (I don't have the SDAC source), the error happens in the CheckAndAnalyze procedure in the OleDBAccess unit.

The same extended stored procedure runs fine on an SQL 2000 database. It also runs fine in Query Analyzer and SDAC when ran against both SQL 2000 and SQL 2005, using this code:

DECLARE @settings nvarchar(1024)
exec master..sqbutility 8, @settings OUTPUT
SELECT @settings

I'm using SDAC 3.55.1.18. Any help would be much appreciated. Thanks.

Ray Mond

[Ikar]

Now SDAC does not support SQL Server 2005 completely. This support will be added in next SDAC version. It will be implemented near SQL Server 2005 release.

[RayMond]

After some testing, I noticed that the output parameters of varchar/nvarchar types always have a maximum size of 8000/4000, regardless of the size that is defined by the user. Is there a reason for this?

I then contacted someone whom I know has the SDAC source, and he confirmed that there is a call to a GetMaxLen procedure in the OLEDBAccess unit, that overrides the size entered by the user. Having commented this out, I can now run my extended stored procedure without problems on SQL 2005.

So, is it safe to remove the part of the code that forces the maximum length:

if DataType in [dtString, dtWideString] then
Result := MaxNonBlobFieldLen + 1
else
Result := MaxNonBlobFieldLen;


Thanks.

Ray Mond

[Ikar]

SDAC 3.55 does not support new features of MS SQL 2005. We will add that support in next SDAC version

[RayMond]

It does not look like a new feature in SQL 2005, but more like a bug in SDAC.

Using the Borland ADO components, running an extended stored procedure with an output parameter, the size of the output parameter is as given by the user. I can confirm this because I debugged my own extended stored procedure from within SQL Server, and SQL Profiler shows the size of the output parameter too.

Using SDAC, the size of string and widestring output parameters are always set to the maximum possible value, and does not use the user defined size. Why is this done?

The comments for that part of the code indicate that it's used to ensure the user defined size does not exceed the maximum permissible size, but no check is done for this. It automatically forces the parameter size to the maximum possible value.

Thanks.


Ray Mond

[Ikar]

Because of http://msdn.microsoft.com/library/defau ... b_7msw.asp, varchar fields can't have length more than 8,000.

[RayMond]

That's correct. But if I specify the size as 1024, it shouldn't overwrite this with the value 8000. That's what I mean when I mentioned that there is no check in the code for it. SDAC just sets the size of all string/widestring output parameters to 8000.

Ray Mond

[Ikar]

This behavior is implemented due to requests of other users. It concerns output parameters only.

[RayMond]

I can understand why it's requested by users as it saves the trouble of having to define the size of the output parameters explicitly. Unfortunately, this causes problems with SQL 2005. So it's safe to comment out that part of the code that increases the size to the maximum possible value?

Ray Mond

[Ikar]

> So it's safe to comment out that part of the code that increases the size
> to the maximum possible value?

You can try doing this, but we can't give any guarantee.

[jeanmarc]

Can't not access a stored procedure with nvarchar OUTPUT Param in SQL SERVER 2005. When is it fixed? I need to upgrade sql servers from my customers from 2000 to 2005 version. i've a lot of such stored procedures because i use only nvarchar type in my databases. My version is 3.80

[Jackson]

We couldn't reproduce the problem.
Please send us (sdac*crlab*com) a complete small test project to reproduce the problem; it is desirable to use Northwind or Master schema objects, otherwise include definition of your own database objects; don't use third party components

Also supply us following information
- Exact version of Delphi or C++ Builder
- Exact version of SDAC. You can see it in About sheet of TMSConnection Editor
- Exact version of Microsoft SQL Server and OLE DB provider that you use. You can see it in Info sheet of TMSConnection Editor