Pass Large Text Parameter To A Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Pass Large Text Parameter To A Stored Procedure

Author

Topic

Speeder
Starting Member

4 Posts

Posted - 2005-09-22 : 13:02:16

Hi All,

Help!

Using VB, I'm trying to pass a large amount of text to a stored procedure that has an input parameter of type 'text'. the value I'm trying to pass has around 32000 characters.
Here's the gist of what I'm trying to do, but it makes my SQL Server keel over:

myCom = New ADODB.Command

myCom .ActiveConnection = myCon
myCom .CommandText = "sp_Test"
myCom .CommandType = ADODB.CommandTypeEnum.adCmdStoredProc

myCom .Parameters.Append(myCom .CreateParameter("text", ADODB.DataTypeEnum.adBSTR, ADODB.ParameterDirectionEnum.adParamInput, 50000, myLargeText)

myCom .Execute()

I've tried another method, using System.Data.SqlClient in VB.NET, but it fails also - it won't allow the parameter (SQL Exception error).

Any ideas? I don't mind starting again from scratch.

Thanks,

Speeder.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-09-22 : 15:38:41

do you have specific errors you can share with us?

if you eliminate the ADO part, can you successfully pass the text parameter to your sproc, or does it still blow up (test from QA for example).

have you read this KB article http://support.microsoft.com/default.aspx?scid=kb;en-us;555266

-ec

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-09-22 : 15:50:05

Do you need a VB6 or a VB.net example?

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-09-22 : 15:55:33

VB.net example


Dim oCmd As New SqlCommand("yourstoredprocnamehere", GetConnection())
        oCmd.CommandType = CommandType.StoredProcedure

        '@UserID
        oCmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = gUserID

        '@Text
        oCmd.Parameters.Add("@Text", SqlDbType.Text).Value = sCommandString

        Try
            oCmd.Connection.Open()
            oCmd.ExecuteNonQuery()

        Catch e As Exception
            Throw e

        Finally
            If oCmd.Connection.State = ConnectionState.Open Then
                oCmd.Connection.Close()
            End If

        End Try

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

PRRSQL
Starting Member

8 Posts

Posted - 2005-10-06 : 13:58:41

An VB.NET Example with Return Params...

If Not Connect() Then
Return 1
End If

Dim cmd As New SqlCommand("AUSP_Insert_Product_ChgService", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int))
cmd.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(New SqlParameter("@TransNumber", SqlDbType.Decimal, 9)).Value = TransNumber
cmd.Parameters.Add(New SqlParameter("@TransSequence", SqlDbType.Int)).Value = 1
cmd.Parameters.Add(New SqlParameter("@ProdNumber", SqlDbType.VarChar, 4)).Value = ProdNumber

'Run the stored proc
cmd.ExecuteNonQuery()

'Return the error code from the proc
Return cmd.Parameters("@RETURN_VALUE").Value

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources