August 11, 2005 at 3:30 am
L.S.
On this moment w're updating the front end from access97 to access2003. We use some stored procedures which returns a sql_variant. In Access2003 we get an error "string to long" when calling this sp from a pass through query. Using CASt to nvarchar or int etc, works fine, but is for us not an option.
What can it be, in Access97 the call works fine
regards
Marcel
August 11, 2005 at 7:07 am
Marcel,
Please provide more details about the SP parameters (iinput and outut), and please post your VBA code that calls the SP.
Does the SP work when you double click it in the Queries Windwow? Do you see any results there?
Are you using an MDB or an ADP? Does the call work from an ADP, but not from an MDB?
Have you upgraded to a newer version of MDAC recently?
August 11, 2005 at 8:05 am
hello Richard
The sp is like this
CREATE procedure dbo.[sp.DDSGetdetail]
(@TABL nvarchar(100),
@ID sql_variant,
@Detail nvarchar(50),
@ID2 sql_variant = Null,
@idname nvarchar (50) = Null,
@numfield smallint = Null,
@VAR01 nvarchar (50) = null,
@VAR02 nvarchar (50) = null,
@VAR03 nvarchar (50) = null,
@VAR04 nvarchar (50) = null,
@var05 nvarchar (50) = null,
@VAR06 sql_variant = null,
)
as
BEGIN
declare @returnvalue sql_variant
set @returnvalue = Null
IF dbo.DDSempty(@ID)= -1 RETURN('');
-- Tables starting with B (After DDS)
Set @returnvalue =
CASE
WHEN @TABL= 'Audit_Orders:FO' THEN (Select dbo.[FX_Audit_Orders:FO](@TABL, @ID, @Detail, @ID2, @idname, @numfield))
WHEN @TABL= 'Audit_Orders:FO' THEN (Select dbo.[FX_IMG_GETfeetax](@TABL,@VAR01,@VAR02,@VAR03,@Detail))
end
select returnvalue=@returnvalue
END
GO
De code in acces97 to make the call
Function DDSGetdetail_code(Tabl As String, ID As Variant, _
Detail As String, Optional ID2 As String, _
Optional idname As String, Optional Numfield As Boolean, _
Optional Var01 As Variant, Optional Var02 As Variant, _
Optional Var03 As Variant, Optional Var04 As Variant, _
Optional Var05 As Variant) As Variant
Var01 = IIf(IsMissing(Var01), " ", Var01)
Var02 = IIf(IsMissing(Var02), " ", Var02)
Var03 = IIf(IsMissing(Var03), " ", Var03)
Var04 = IIf(IsMissing(Var04), " ", Var04)
Var05 = IIf(IsMissing(Var05), " ", Var05)
If DDSempty(ID) = True Then DDSGetdetail_code = "": Exit Function
Dim Mydb As Database, MyQry As QueryDef, MyRS As RecordSet
Set Mydb = CurrentDb()
Set MyQry = Mydb.CreateQueryDef("")
' Type a connect string using the appropriate values for your server.
MyQry.Connect = "ODBC;DSN=DATABASE_x;UID=sa;PWD=abcd;DATABASE=X"
' Set the SQL property and concatenate the variables.
MyQry.SQL = "dbo.[sp.DDSGetdetail] " & "'" & Tabl & "'," & ID & ",'" & Detail & "','" & _
ID2 & "','" & idname & "','" & IIf(Numfield, "-1", "0") & "'" _
& ",'" & Var01 & "','" & Var02 & "','" & Var03 & "','" & Var04 & "','" & Var05 & "'"
MyQry.ReturnsRecords = True
If DDSempty(MyQry.SQL) = False Then
Set MyRS = MyQry.OpenRecordset()
MyRS.MoveFirst
DDSGetdetail_code = MyRS!returnvalue
MyQry.Close
MyRS.Close
Mydb.Close
End If
End Function
This code is called in some modules for making calculations in the front end
regards
Marcel
PS I found some information like this (we use SQL 2000)
SQL Server converts sql_variant values to nvarchar(4000) when working with applications that have connected with the following interfaces:
The OLE DB Provider for SQL Server version 7.0.
The SQL Server ODBC Driver from SQL Server 7.0.
If the resulting string exceeds 4000 characters, SQL Server returns the first 4000 characters.
SQL Server converts sql_variant values to varchar(255) when working with applications that have connected with the following interfaces:
The SQL Server ODBC Drivers from SQL Server version 6.5 or earlier.
Any version of the DB-Library dll.
If the resulting string exceeds 255 characters, SQL Server returns the first 255 characters.
August 11, 2005 at 6:50 pm
It's a bit difficult to get a handle on your code without knowing what it really does. Without suggesting that you rewrite your SP to be more "strongly typed," I'll give it a try with a few comments.
I have not done DAO/MDB stuff in a while, and obviously you are using a native DAO querydef (using JET/ODBC) to send a pass-thru.
To get a handle on this, I would take a look at the SQL profiler trace when you execute this command.
I am somewhat concerned that the "ID" value, which may (or may not) be internally converted to a text format in your current version of DAO/ODBC/MDAC, is not surrounded by single quotes when it contains a text value. I would try to add quotes around it, at least when the ID value is text. If the value is always converted to text in the newer DAO/ODBC/MDAC version, then I could presume quotes are mandatory.
I wanted to suggest rewriting this using ADO instead of DAO. But then I found the following in BOL:
"However, ADO does not yet support this (sql_variant) data type completely, and usage may cause unpredictable results."
I also found the following at MSDN:
"In client applications using MDAC 2.5, sql_variant data may be used with queries against Microsoft SQL Server 2000. However, the values of the sql_variant data are treated as strings. Such client applications should be upgraded to MDAC 2.7 and later. "
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdrefsqlprovspec.asp)
Also see:
http://www.sqlteam.com/item.asp?ItemID=7881
I know it's a mess, but perhaps my comments will be of some help.
Rich
August 12, 2005 at 8:57 am
I've upgraded a number of Microsoft Applications that were dependent on DAO/MDAC libraries and have experieced the least pain by upgrading the libraries during the process. If you could change your datatype from a sql_variant the MDAC 2.7/2.8 libraries would be a good choice but if not upgrade to the latest DAO libraries.
ALSO, the default libraries for Access 97 were DAO so you could dimension them without a prefix such as:
Dim rs As Recordset
But with Access 2003 if you want to use the same code without rewriting some subs/functions you would have to dimension it as a dao.recordset. The default would use the ADODB libraries are require modification of some of the code procedures. I no longer dimension dao or adodb objects without the prefixes because I've had to upgrade too many databases.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply