Using A Stored Procedure In Custom Sql Statement

Legacy Poster

• 26. Januar 2014 16:27

Does anyone know if using a stored procedure in the Custom SQL Statement of the "Database Connection Setup" dialog is supported in Bartender 10.1 SR2?  I've tested the Stored Procedure in SQL Server, and Profiled the DB to verify that Bartender is hitting the DB with the correct statement, but when trying to hit the "Browse" tab of the Dialog, get IDispatch #3105 or if I try to print the label, I get "Unknown error. .... IDispatch error #3081"  In looking through other forum posts, I found this reference to the error, but no resolution. (http://forums.seagullscientific.com/index.php?/topic/446-delay-timeout-error/?hl=%2Bidispatch+%2Berror+%2B3105)

 

The Custom SQL statement from the BarTender window is:

exec [sp].[GetCompsWithAdditionalFields] @CabSerialNum='PURCPS13480005' ,@Field1='FuncVar', @Field2='RealizeVar', @Field3='MfgRev'

 

While the corrisponding SQL Profiler shows this call to the DB which I've tested and works:

exec [sp].[GetCompsWithAdditionalFields] @CabSerialNum='PURCPS13480005' ,@Field1='FuncVar', @Field2='RealizeVar', @Field3='MfgRev'
 

I'd like to know whether I can execute a stored procedure in the Custom SQL statment and I'm making a mistake in implementing or if I need to pick a different path either in the DB implementation or in VBScript. 

 

Any input would be appreciated and thanks for your time in looking at this.

~Shane

0

• 

  Domingo Rodriguez

  Moderator

  ★ BarTender Hero ★

  • 10. Februar 2014 16:24

  Find below our general instructions for running a stored procedure from within BarTender:

   

  • Set your Database Connection to use a Custom SQL stored procedure.
  • Open the label format.
  • Go to File > Database Connection Setup.
  • If not already connected, connect to your database.
  • Go to the SQL Tab.
  • Check Custom SQL.
  • Enter your stored procedure into the text box like this (T-SQL example):

  EXEC  [dbo].[Test_Procedure]

   

  To use query prompts with your Stored Procedure

   

  • Open Database Connection Setup.
  • Go to the SQL tab.
  • Click the Query Prompts button.
  • Create a prompt for each piece of data the stored procedure calls for.
  • Specify the query prompt data in your SQL string like this (T-SQL example):

  EXEC  [dbo].[Test_Procedure] @field1 = N'?QueryPrompt1'

  Depending on the type of database in use, you may also need to check the “Use Client Cursor” checkbox. This checkbox is located on the Options tab in Database Connection Setup for all databases that support a local client cursor. In your case, please enable it.

   

  Do these general instructions help you to move forward? Do you perhaps need to first define 1 prompt for each piece of data the stored procedure calls for (4 in this case)?

  0
• 

  Jason Ruiter

  ★ BarTender Hero ★

  • 1. Februar 2017 18:58

  I tried this.  It doesn't work because the Database Field (that I plan to put in my label) is blank.  I can "test" it in the tool, but I can't actually use it in a label.  Is there a way to force a label on a database field?

  0
• 

  Bob Steele

  Moderator

  ★ BarTender Hero ★

  • 3. Februar 2017 18:51

  Jason,

   

  Can I ask why the field will be blank or what you would be using the field for?

  You may want to submit a support request from the main page of our support site explaining exactly what you are trying to do, Our support staff will be happy to assist in any way we can.

  0

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.