Access2003 and sql_variant

  • 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

  • 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?

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

  • 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

  • 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