Find if a stored procedure called from vba fails

C

ChrisOswald

Active Member
Joined
Jan 19, 2010
Messages
454
Hi,
I'm attempting to find out if a stored procedure to a SQL server database fails when called. The test I'm doing is putting an excessively long string into a short text field. (1500+ character string into a field that can hold 512 characters according to the SP definition, ~900 according to the table definition in SQL server, and dbMemo according to a linked access table.)

The way the stored procedure is supposed to work is: there are three requried fields in the table being updated. If a record with the 3 keys exist, then update fields where the parameters are not null, otherwise create a new record with the 3 keys.

In the code below, if I exclude the lines
Code: 
    'add error checking parameter
    Set prm = cmd.CreateParameter("@@Error", adInteger, adParamReturnValue)
    cmd.Parameters.Append prm
, then the SP fails silently. If I include them, I get this error message at the cmd.execute line:

Run-time error '-2147217900 (80040e14)':
[Microsoft][ODBC SQL Server Driver][SQL Server] The formal parameter "@Shrt_Desc" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.

Any ideas on how to make sure I can capture a failed sp? I know I could trap for the specific error of too long of a string by checking for string length, but I'm suspecting that these field lengths will end up being longer than they are currently set at and i'd prefer to not have to do maintainence on this sub.

SP Sub VBA code:
Code: 
Private Sub RunBenStatmentSP(sKeyCntryCd As String, sKeyLang As String, _
                             sKeyPartID As String, sFncal_nm As String, _
                             sComml_nm As String, sBens_Stmt As String, _
                             sShrt_Desc As String, sAddl_Gdsn_Desc As String)
'Note:  This implementation of the sp doesn't allow overwriting to an empty string
'or null value.
    Dim cnn                             As ADODB.Connection
    Dim cmd                             As ADODB.Command
    Dim sErrorMsg                       As String
    Dim prm                             As ADODB.Parameter
    Dim lErrNbr                         As Long
    Set cnn = New ADODB.Connection
    cnn.Open csConnection
    Set cmd = New ADODB.Command
    cmd.CommandText = "spGS1MarketingStatement"
    cmd.CommandType = adCmdStoredProc
    cmd.ActiveConnection = cnn
    Set prm = cmd.CreateParameter("@Cntry_CD", adBSTR, adParamInput)
    cmd.Parameters.Append prm
    cmd.Parameters("@Cntry_CD").Value = sKeyCntryCd
    Set prm = cmd.CreateParameter("@LANG_CD", adBSTR, adParamInput)
    cmd.Parameters.Append prm
    cmd.Parameters("@LANG_CD").Value = sKeyLang
    Set prm = cmd.CreateParameter("@SRC_SYS_ID", adBSTR, adParamInput)
    cmd.Parameters.Append prm
    cmd.Parameters("@SRC_SYS_ID").Value = sKeyPartID
    If Trim(sBens_Stmt) <> "" Then
        Set prm = cmd.CreateParameter("@Desc", adBSTR, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("@Desc").Value = sBens_Stmt
    End If
    If Trim(sShrt_Desc) <> "" Then
        Set prm = cmd.CreateParameter("@Shrt_Desc", adBSTR, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("@Shrt_Desc").Value = sShrt_Desc
    End If
    If Trim(sAddl_Gdsn_Desc) <> "" Then
        Set prm = cmd.CreateParameter("@ADDL_GDSN_DESC", adBSTR, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("@ADDL_GDSN_DESC").Value = sAddl_Gdsn_Desc
    End If
    If Trim(sComml_nm) <> "" Then
        Set prm = cmd.CreateParameter("@COMML_NM", adBSTR, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("@COMML_NM").Value = sComml_nm
    End If
    If Trim(sFncal_nm) <> "" Then
        Set prm = cmd.CreateParameter("@FNCAL_NM", adBSTR, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("@FNCAL_NM").Value = sFncal_nm
    End If
    'add error checking parameter
    Set prm = cmd.CreateParameter("@@Error", adInteger, adParamReturnValue)
    cmd.Parameters.Append prm
    cmd.Execute
    lErrNbr = cmd.Parameters("@@Error").Value
Debug.Print lErrNbr
 
    Set cmd = Nothing
    Set prm = Nothing
 
    cnn.Close
    Set cnn = Nothing
End Sub
calling code(obviously a test sub)
Code: 
Sub TestIt4()
    Dim sKeyCntrCd                      As String
    Dim sKeyLang                        As String
    Dim sKeyPartID                      As String
    Dim sFncal_nm                       As String
    Dim sComml_nm                       As String
    Dim sBens_Stmt                      As String
    Dim sShrt_Desc                      As String
    Dim sAddlGdsn_Desc                  As String
 
    'Test 4: Update one item to excessively long string.
    sKeyCntrCd = "US"
    sKeyLang = "EN"
    sKeyPartID = "10065"
    sFncal_nm = ""
    sComml_nm = ""
    sBens_Stmt = "New Bens Stmt" & String(1500, "!")
    sShrt_Desc = ""
    sAddlGdsn_Desc = ""
    RunBenStatmentSP sKeyCntrCd, sKeyLang, sKeyPartID, sFncal_nm, _
                     sComml_nm, sBens_Stmt, sShrt_Desc, sAddlGdsn_Desc
 
End Sub
Stored Procedure Definition:
Code: 
[COLOR=blue][FONT=Courier New]USE[/FONT][/COLOR][FONT=Courier New] [SSBT]<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT]
[COLOR=blue][FONT=Courier New]GO<o:p></o:p>[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]/****** Object:  StoredProcedure [dbo].[spGS1MarketingStatement]    Script Date: 11/01/2011 11:30:25 ******/<o:p></o:p>[/FONT][/COLOR]
[COLOR=blue][FONT=Courier New]SET[/FONT][/COLOR][FONT=Courier New] [COLOR=blue]ANSI_NULLS[/COLOR] [COLOR=blue]ON<o:p></o:p>[/COLOR][/FONT]
[COLOR=blue][FONT=Courier New]GO<o:p></o:p>[/FONT][/COLOR]
[COLOR=blue][FONT=Courier New]SET[/FONT][/COLOR][FONT=Courier New] [COLOR=blue]QUOTED_IDENTIFIER[/COLOR] [COLOR=blue]OFF<o:p></o:p>[/COLOR][/FONT]
[COLOR=blue][FONT=Courier New]GO<o:p></o:p>[/FONT][/COLOR]
[COLOR=blue][FONT=Courier New]ALTER[/FONT][/COLOR][FONT=Courier New] [COLOR=blue]PROCEDURE[/COLOR] [dbo][COLOR=gray].[/COLOR][spGS1MarketingStatement]<o:p></o:p>[/FONT]
[FONT=Courier New]    [COLOR=green]-- Add the parameters for the stored procedure here<o:p></o:p>[/COLOR][/FONT]
[FONT=Courier New]    @CNTRY_CD [COLOR=blue]as[/COLOR] [COLOR=blue]nchar[/COLOR][COLOR=gray]([/COLOR]2[COLOR=gray]),<o:p></o:p>[/COLOR][/FONT]
[FONT=Courier New]    @LANG_CD [COLOR=blue]as[/COLOR] [COLOR=blue]nchar[/COLOR][COLOR=gray]([/COLOR]2[COLOR=gray]),<o:p></o:p>[/COLOR][/FONT]
[FONT=Courier New]    @SRC_SYS_ID [COLOR=blue]as[/COLOR] [COLOR=blue]nvarchar[/COLOR][COLOR=gray]([/COLOR]16[COLOR=gray]),<o:p></o:p>[/COLOR][/FONT]
[FONT=Courier New]    @Desc [COLOR=blue]As[/COLOR] [COLOR=blue]nvarchar[/COLOR][COLOR=gray]([/COLOR]512[COLOR=gray])[/COLOR] [COLOR=gray]=[/COLOR] [COLOR=gray]Null,[/COLOR] <o:p></o:p>[/FONT]
[FONT=Courier New]    @Shrt_Desc [COLOR=blue]as[/COLOR] [COLOR=blue]nvarchar[/COLOR][COLOR=gray]([/COLOR]35[COLOR=gray])[/COLOR] [COLOR=gray]=[/COLOR] [COLOR=gray]Null,<o:p></o:p>[/COLOR][/FONT]
[FONT=Courier New]    @ADDL_GDSN_DESC [COLOR=blue]as[/COLOR] [COLOR=blue]nvarchar[/COLOR][COLOR=gray]([/COLOR]350[COLOR=gray])[/COLOR] [COLOR=gray]=[/COLOR] [COLOR=gray]Null,<o:p></o:p>[/COLOR][/FONT]
[FONT=Courier New]    @COMML_NM [COLOR=blue]as[/COLOR] [COLOR=blue]nvarchar[/COLOR][COLOR=gray]([/COLOR]80[COLOR=gray])[/COLOR] [COLOR=gray]=[/COLOR] [COLOR=gray]Null,<o:p></o:p>[/COLOR][/FONT]
[FONT=Courier New]    @FNCAL_NM [COLOR=blue]as[/COLOR] [COLOR=blue]nvarchar[/COLOR][COLOR=gray]([/COLOR]80[COLOR=gray])[/COLOR] [COLOR=gray]=[/COLOR] [COLOR=gray]Null<o:p></o:p>[/COLOR][/FONT]
[COLOR=blue][FONT=Courier New]AS<o:p></o:p>[/FONT][/COLOR]
[COLOR=blue][FONT=Courier New]BEGIN<o:p></o:p>[/FONT][/COLOR]
[FONT=Courier New]    [COLOR=green]-- SET NOCOUNT ON added to prevent extra result sets from<o:p></o:p>[/COLOR][/FONT]
[FONT=Courier New]    [COLOR=green]-- interfering with SELECT statements.<o:p></o:p>[/COLOR][/FONT]
[FONT=Courier New]    [COLOR=blue]SET[/COLOR] [COLOR=blue]NOCOUNT[/COLOR] [COLOR=blue]ON[/COLOR][COLOR=gray];<o:p></o:p>[/COLOR][/FONT]
[COLOR=gray][FONT=Courier New]<o:p></o:p>[/FONT][/COLOR]
[COLOR=blue][FONT=Courier New]IF[/FONT][/COLOR][FONT=Courier New] [COLOR=gray]EXISTS[/COLOR][COLOR=gray]([/COLOR][COLOR=blue]SELECT[/COLOR] [COLOR=gray]*[/COLOR] [COLOR=blue]FROM[/COLOR] [dbo][COLOR=gray].[/COLOR][GS1_MKTG_STMT] [COLOR=blue]WHERE[/COLOR] CNTRY_CD [COLOR=gray]=[/COLOR] @CNTRY_CD [COLOR=gray]and[/COLOR]  LANG_CD [COLOR=gray]=[/COLOR] @LANG_CD [COLOR=gray]and[/COLOR] SRC_SYS_ID [COLOR=gray]=[/COLOR] @SRC_SYS_ID[COLOR=gray])<o:p></o:p>[/COLOR][/FONT]
[FONT=Courier New]     [COLOR=blue]UPDATE[/COLOR] [SSBT][COLOR=gray].[/COLOR][dbo][COLOR=gray].[/COLOR][GS1_MKTG_STMT]<o:p></o:p>[/FONT]
[FONT=Courier New]   [COLOR=blue]SET[/COLOR] <o:p></o:p>[/FONT]
[FONT=Courier New]          BENS_STMT [COLOR=gray]=[/COLOR] [COLOR=fuchsia]isNull[/COLOR][COLOR=gray]([/COLOR]@Desc[COLOR=gray],[/COLOR]BENS_STMT[COLOR=gray]),<o:p></o:p>[/COLOR][/FONT]
[FONT=Courier New]          SHRT_DESC [COLOR=gray]=[/COLOR] [COLOR=fuchsia]isNull[/COLOR][COLOR=gray]([/COLOR]@Shrt_Desc[COLOR=gray],[/COLOR]SHRT_DESC[COLOR=gray]),<o:p></o:p>[/COLOR][/FONT]
[FONT=Courier New]          ADDL_GDSN_DESC [COLOR=gray]=[/COLOR] [COLOR=fuchsia]isNull[/COLOR][COLOR=gray]([/COLOR]@ADDL_GDSN_DESC[COLOR=gray],[/COLOR]ADDL_GDSN_DESC[COLOR=gray]),<o:p></o:p>[/COLOR][/FONT]
[FONT=Courier New]          COMML_NM [COLOR=gray]=[/COLOR] [COLOR=fuchsia]isNull[/COLOR][COLOR=gray]([/COLOR]@COMML_NM[COLOR=gray],[/COLOR]COMML_NM[COLOR=gray]),<o:p></o:p>[/COLOR][/FONT]
[FONT=Courier New]          FNCAL_NM [COLOR=gray]=[/COLOR] [COLOR=fuchsia]isNull[/COLOR][COLOR=gray]([/COLOR]@FNCAL_NM[COLOR=gray],[/COLOR]FNCAL_NM[COLOR=gray])<o:p></o:p>[/COLOR][/FONT]
[FONT=Courier New]   [COLOR=blue]WHERE[/COLOR] CNTRY_CD [COLOR=gray]=[/COLOR] @CNTRY_CD [COLOR=gray]and[/COLOR]  LANG_CD [COLOR=gray]=[/COLOR] @LANG_CD [COLOR=gray]and[/COLOR] SRC_SYS_ID [COLOR=gray]=[/COLOR] @SRC_SYS_ID<o:p></o:p>[/FONT]
[COLOR=blue][FONT=Courier New]ELSE<o:p></o:p>[/FONT][/COLOR]
[FONT=Courier New]        [COLOR=green]-- Insert statements for procedure here<o:p></o:p>[/COLOR][/FONT]
[FONT=Courier New]    [COLOR=blue]INSERT[/COLOR] [COLOR=blue]INTO[/COLOR] [SSBT][COLOR=gray].[/COLOR][dbo][COLOR=gray].[/COLOR][GS1_MKTG_STMT]<o:p></o:p>[/FONT]
[COLOR=gray][FONT=Courier New]([/FONT][/COLOR][FONT=Courier New][CNTRY_CD]<o:p></o:p>[/FONT]
[FONT=Courier New]         [COLOR=gray],[/COLOR][LANG_CD]<o:p></o:p>[/FONT]
[FONT=Courier New]         [COLOR=gray],[/COLOR][SRC_SYS_ID]<o:p></o:p>[/FONT]
[FONT=Courier New]         [COLOR=gray],[/COLOR][BENS_STMT]<o:p></o:p>[/FONT]
[FONT=Courier New]         [COLOR=gray],[/COLOR][SHRT_DESC]<o:p></o:p>[/FONT]
[FONT=Courier New]         [COLOR=gray],[/COLOR][ADDL_GDSN_DESC]<o:p></o:p>[/FONT]
[FONT=Courier New]         [COLOR=gray],[/COLOR][COMML_NM]<o:p></o:p>[/FONT]
[FONT=Courier New]         [COLOR=gray],[/COLOR][FNCAL_NM][COLOR=gray])<o:p></o:p>[/COLOR][/FONT]
[FONT=Courier New]   [COLOR=blue]VALUES<o:p></o:p>[/COLOR][/FONT]
[COLOR=gray][FONT=Courier New]([/FONT][/COLOR][FONT=Courier New]@CNTRY_CD<o:p></o:p>[/FONT]
[FONT=Courier New]         [COLOR=gray],[/COLOR]@LANG_CD<o:p></o:p>[/FONT]
[FONT=Courier New]         [COLOR=gray],[/COLOR]@SRC_SYS_ID<o:p></o:p>[/FONT]
[FONT=Courier New]         [COLOR=gray],[/COLOR]@Desc<o:p></o:p>[/FONT]
[FONT=Courier New]         [COLOR=gray],[/COLOR]@Shrt_Desc<o:p></o:p>[/FONT]
[FONT=Courier New]         [COLOR=gray],[/COLOR]@ADDL_GDSN_DESC<o:p></o:p>[/FONT]
[FONT=Courier New]         [COLOR=gray],[/COLOR]@COMML_NM<o:p></o:p>[/FONT]
[FONT=Courier New]         [COLOR=gray],[/COLOR]@FNCAL_NM[COLOR=gray])<o:p></o:p>[/COLOR][/FONT]
[COLOR=blue][FONT=Courier New]IF[/FONT][/COLOR][FONT=Courier New] [COLOR=fuchsia]@@Error[/COLOR] [COLOR=gray]<>[/COLOR] 0<o:p></o:p>[/FONT]
[FONT=Courier New]     [COLOR=blue]RAISERROR[/COLOR] 50001 [COLOR=red]'spGS1MarketingStatement execution'<o:p></o:p>[/COLOR][/FONT]
[COLOR=blue][FONT=Courier New]END<o:p></o:p>[/FONT][/COLOR]
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
You must log in or register to reply here.

Similar threads

K
  • Question Question
Run-time error '-2147217900 (80040e14)': Syntax error or access violation
Replies
0
Views
158
kristianb63
K
T
  • Question Question
VBA to display data loaded into user form on the main sheet.
Replies
5
Views
619
sijpie
sijpie
D
  • Question Question
VBA code works when called directly, but not from another routhine
Replies
7
Views
966
davidepstein22
D
M
  • Question Question
Calling stored procedure with variables in SQL SERVER from Excel
Replies
0
Views
2K
mrfitness79
M
A
  • Question Question
SQL Server Parameter transfer
Replies
2
Views
2K
AadKabel
A

Forum statistics

Threads
1,224,973
Messages
6,182,093
Members
453,088
Latest member
Chaoxite

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top