from varchar(8000) to varchar(max)

  • Thread starter Thread starter Keith G Hicks
  • Start date Start date
K

Keith G Hicks

I'm working on an adp that was originally written for SQL 2000. There are
lots of varchar(8000)'s in it for passing in long strings of parameters to
procs. We converted the backend to SQL 2005 some time ago and I'm working on
changing the varchar(8000)'s to varchar(max)'s. I'm some places this is not
a big deal, like the following code that sets the source for a report:

sSql = "EXEC sp_RptSalesList '" _
& Format(dteGetDate, "mm/dd/yyyy") & "', '" _
& strInOrOutCounty & "', '" _
& Replace(strGetCounties, "'", "''''") & "', '" _
& strGetAttyIDs & "'"

rs.Open sSql, cnnCurrProj, adOpenStatic

Me.RecordSource = rs.Source

But in some places where I call proces to do things I have the following:

..Parameters.Append .CreateParameter("@ParentAttyIDs", adVarChar,
adParamInput, 8000, strGetParentAttyIDs)

I have no idea what to change adVarChar and it's associated 8000 to in order
to use the input param that's now a varchar(max).

Could someone let me know where to find this info or what to use instead?

Thanks,

Keith
 
I should mention that I found posts claiming that you can just leave out the
8000 but that crashes. I also found soem information that shows this:

adLongVarWChar, adParamInput, -1

and some that show this:

adLongVarChar, adParamInput, 2147483647

both seem to work without errors but I cannot find anything clearly showing
in no uncertain terms what is the proper way to handle this.

Keith
 
I did a test with adLongVarChar, adParamInput, -1 by inserting a 80,000
character string into a varchar(max) column in a test table using a
varchar(max) input param in a proc. The entire string inserted without
problems. So I'm guessing I'm all set but if someone in the know could
confirm that what i'm doign is correct, I'd appreciate it. :-)
 
Well, if adLongVarChar works then it's OK but if you have any problem, use
the Text type instead of varchar(Max) along with adLongVarChar. See also the
following article for some more information, especially if you want to open
a different ADO connection to make some direct backend update:

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

If I remember correctly, the length to provide should be 4 bytes because
it's a pointer that is passed along but I would think that the system will
determine that from the type of adLongVarChar. One way to be sure would be
to repeat the test but with some other parameters after this one and check
that these other parameters are passed OK, too.

As a side note, here's a little procedure to get the list of all parameters
with their properties directly from the SP and that could be useful:

Sub ListOfParameters (ProcedureName As String)

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = ProcedureName

cmd.Parameters.Refresh

Dim p As ADODB.Parameter

For Each p In cmd.Parameters
Debug.Print "name = " & p.name
Debug.Print "Direction = " & p.Direction
Debug.Print "Type = " & p.Type
Debug.Print "Size = " & p.Size
Debug.Print "Precision = " & p.Precision
Debug.Print "NumericScale = " & p.NumericScale
Debug.Print
Next

Set cmd = Nothing

End Sub


IMPORTANT! For using this procedure, you must *not* give the prefix dbo. to
the name of the SP and see
http://www.asp101.com/articles/john/adovbs/adojavas.inc for a list of the
numerical values.

Of course, you could also simply use the cmd.Parameters.Refresh method and
forget the rest.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)
 
Thnaks Sylvain.

Sylvain Lafontaine said:
Well, if adLongVarChar works then it's OK but if you have any problem, use
the Text type instead of varchar(Max) along with adLongVarChar. See also
the following article for some more information, especially if you want to
open a different ADO connection to make some direct backend update:

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

If I remember correctly, the length to provide should be 4 bytes because
it's a pointer that is passed along but I would think that the system will
determine that from the type of adLongVarChar. One way to be sure would
be to repeat the test but with some other parameters after this one and
check that these other parameters are passed OK, too.

As a side note, here's a little procedure to get the list of all
parameters with their properties directly from the SP and that could be
useful:

Sub ListOfParameters (ProcedureName As String)

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = ProcedureName

cmd.Parameters.Refresh

Dim p As ADODB.Parameter

For Each p In cmd.Parameters
Debug.Print "name = " & p.name
Debug.Print "Direction = " & p.Direction
Debug.Print "Type = " & p.Type
Debug.Print "Size = " & p.Size
Debug.Print "Precision = " & p.Precision
Debug.Print "NumericScale = " & p.NumericScale
Debug.Print
Next

Set cmd = Nothing

End Sub


IMPORTANT! For using this procedure, you must *not* give the prefix dbo.
to the name of the SP and see
http://www.asp101.com/articles/john/adovbs/adojavas.inc for a list of the
numerical values.

Of course, you could also simply use the cmd.Parameters.Refresh method and
forget the rest.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)
Click to expand...
 
Back
Top