Stored Procedures フォローする

0
Avatar
Legacy Poster

Are you able to use Stored Procedures as a data source in BarTender? If so how is that done?

5 コメント

0
Avatar
Gene Henson
コメントアクション Permalink

Although BarTender offers a very robust database query interface, you may find that the only way to get the data you really want is through a SQL Stored Procedure. Here is how you can use your stored procedure in BarTender.

[b]Set your Database Connection to use a Custom SQL stored procedure[/b]
[list=1]
[*]Open the label format
[*]Go to [b]File > Database Connection Setup[/b]
[*]If not already connected, connect to your database (see [url="http://www.seagullscientific.com/aspx/training-video-%28reading-data-from-database%29.aspx"]this[/url] video for a tutorial)
[*]Go to the [b]SQL [/b]Tab
[*]Check [b]Use Custom SQL Statement[/b]
[*]Enter your stored procedure into the text box like this (T-SQL example):
[list]
[*]“[i]EXEC [dbo].[Test_Procedure][/i]”
[/list]
[/list]

[b]To use Prompts with your Stored Procedure[/b]
[list=1]
[*]Open [b]Database Connection Setup[/b]
[*]Go to the [b]SQL [/b]tab
[*]Click the [b]Query Prompts[/b] 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):
[list]
[*]“[i]EXEC [dbo].[Test_Procedure] @field1 = N'?QueryPrompt1'[/i]”
[/list]
[/list]

Depending on the type of database in use, you may also need to check the “[b]Use Client Cursor[/b]” checkbox. This checkbox is located on the [b]Options [/b]tab in [b]Database Connection Setup[/b] for all databases that support a local client cursor.
0
Avatar
Legacy Poster
コメントアクション Permalink

[quote name='Gene H - Seagull Support' timestamp='1322508326' post='1161']
Although BarTender offers a very robust database query interface, you may find that the only way to get the data you really want is through a SQL Stored Procedure. Here is how you can use your stored procedure in BarTender.

[b]Set your Database Connection to use a Custom SQL stored procedure[/b]
[list=1]
[*]Open the label format
[*]Go to [b]File > Database Connection Setup[/b]
[*]If not already connected, connect to your database (see [url="http://www.seagullscientific.com/aspx/training-video-%28reading-data-from-database%29.aspx"]this[/url] video for a tutorial)
[*]Go to the [b]SQL [/b]Tab
[*]Check [b]Use Custom SQL Statement[/b]
[*]Enter your stored procedure into the text box like this (T-SQL example):
[list]
[*]“[i]EXEC [dbo].[Test_Procedure][/i]”
[/list]
[/list]

[b]To use Prompts with your Stored Procedure[/b]
[list=1]
[*]Open [b]Database Connection Setup[/b]
[*]Go to the [b]SQL [/b]tab
[*]Click the [b]Query Prompts[/b] 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):
[list]
[*]“[i]EXEC [dbo].[Test_Procedure] @field1 = N'?QueryPrompt1'[/i]”
[/list]
[/list]

Depending on the type of database in use, you may also need to check the “[b]Use Client Cursor[/b]” checkbox. This checkbox is located on the [b]Options [/b]tab in [b]Database Connection Setup[/b] for all databases that support a local client cursor.
[/quote]
0
Avatar
Legacy Poster
コメントアクション Permalink

Thankyou I will try that.
0
Avatar
Legacy Poster
コメントアクション Permalink

The SP's are already created and stored in our DB - so there is no way to just connect to the SP like you do tables? I would have to recreate the SP's withthin your tool?
0
Avatar
Gene Henson
コメントアクション Permalink

Hello,

Yes, you can just connect to your already existing stored procedures. The only limitation is that BarTender does not see the stored procedures as selectable tables when you are choosing the tables to connect to. However, once you have connected to the database as normal, you can use a custom SQL statement to call the stored procedures that already exist in your database.

The only thing you really have to recreate is the user prompts if you want to query with user input at print time.

Does that make sense?

サインインしてコメントを残してください。