Running a Stored Proc before report runs

Hi. I've got a report with 4 different sections - the datasets coming from some tables that are populated via a stored procedure. I'd love it if the the first thing this report did was run that stored procedure and then the data would be available for the actual reporting piece. Is that possible? And if so, how do I make it work?

Thanks!

You can make individual datasets be populated by a stored procedures. I think what you're eluding to is having one stored proc return multiple tables/results which is not supported.

The only way to achieve this is potentially to use a custom data delivery extension.

|||

I haven't tried this, but if your dataset's are coming from stored procedures, you could just call your 'data generation' stored procedure at the beginning of your reporting stored procedure.

Hope this helps.

Jarret

|||Nope, not alluding to one stored procedure return mulitple data-sets. :) I knew that wouldn't work. Actually, the stored procedure populates 4 tables with data from various other tables. Those 4 tables are used in the 4 different data-sets in the report. I'd like to be able to run my data-populating stored procedure before the report runs. I can do this using a scheduler and make sure it runs before the report. But sometimes things go wrong, and I can see the stored procedure not running and then the report will go out with no data...or something of that nature. I just thought it would be great if the running of the stored procedure could be tied to the report somehow.|||Actually, I tried that. I put the execution of the SP in the beginning of the dataset of the first table on the report. That 1st table had data. But the other 4 don't. I was hoping that since that was the first one, it might run in sequential order. :) Guess not.|||

Is there anyway you can break up the 'data generation' into 4 stored procedures so that each report calls an individual one to populate the data?

Jarret

|||

Nope. They all use the same tables.

Just for further clarification (don't feel like you need to read this).... The "data generation" SP takes all the call the calls that come into our call-center, gets the number of the person calling, the length of the call, etc. This data (after much manipulation) goes into one table (CTICalls). Then it creates another table for all the "work tickets" that were created due to the calls that came in. Now, all this data is in various other separate tables from the CTI Calls. It's a completely different system. Because of this, you can't just match up the name of the person who called to a ticket...further manipulation is required. All these tickets go into another table (CRMTickets). On top of that, the silly people who want this report want to know that name of the person who called. :) Of course, all I've got is the phone number. This requires another table because the table which actually contains the phone numbers has lots of duplicates and other bad things. So now I've got a phone number table with the name of the person calling. Great. So now the SP creates another table, which matches the CTI calls to the CRM tickets, sticks in the name of the person calling. So now I've got the table I need for the report. The first report is a summary - the number of calls per call center agent, and the number of tickets created. Next report lists all the calls that have no ticket created. Then we just list all of the calls, and then all of the tickets.

Hopefully, now you can see why I want to run the data generation first and why it can't really be broken up.

Jennifer

|||The only way I can think of ensuring the order in which the dataset queries are executed is to use dependant parameters, even if you just use some dummy values.|||

Hi,

I had quite similar problem with multiple sp-based datasets. The first one populated the global temporary tables and subsequent datasets displayed the data. To make sure the master SP will be executed first and no other datasets will be run before master SP completes, you have to do the following:

- Organize your datasets in exact order you need them to run. Datasets can only be moved by editing the RDL file. Find the <Datasets> section and move individual dataset sections.

- Enable transaction flag of the data source to prevent you datasets from being executed concurrently. In the RDL file, add <Transaction> element to the data source properties. Below is the example.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="ds_inv_rep">
<Transaction>true</Transaction>
<DataSourceReference>ds_inv_rep</DataSourceReference>
<rd:DataSourceID>7f21a5bb-83e1-4c9c-a32b-5ee080055ed6</rd:DataSourceID>
</DataSource>

Wapper

Running a Stored Proc before report runs

Hi. I've got a report with 4 different sections - the datasets coming from some tables that are populated via a stored procedure. I'd love it if the the first thing this report did was run that stored procedure and then the data would be available for the actual reporting piece. Is that possible? And if so, how do I make it work?

Thanks!

You can make individual datasets be populated by a stored procedures. I think what you're eluding to is having one stored proc return multiple tables/results which is not supported.

The only way to achieve this is potentially to use a custom data delivery extension.

|||

I haven't tried this, but if your dataset's are coming from stored procedures, you could just call your 'data generation' stored procedure at the beginning of your reporting stored procedure.

Hope this helps.

Jarret

|||Nope, not alluding to one stored procedure return mulitple data-sets. :) I knew that wouldn't work. Actually, the stored procedure populates 4 tables with data from various other tables. Those 4 tables are used in the 4 different data-sets in the report. I'd like to be able to run my data-populating stored procedure before the report runs. I can do this using a scheduler and make sure it runs before the report. But sometimes things go wrong, and I can see the stored procedure not running and then the report will go out with no data...or something of that nature. I just thought it would be great if the running of the stored procedure could be tied to the report somehow.|||Actually, I tried that. I put the execution of the SP in the beginning of the dataset of the first table on the report. That 1st table had data. But the other 4 don't. I was hoping that since that was the first one, it might run in sequential order. :) Guess not.|||

Is there anyway you can break up the 'data generation' into 4 stored procedures so that each report calls an individual one to populate the data?

Jarret

|||

Nope. They all use the same tables.

Just for further clarification (don't feel like you need to read this).... The "data generation" SP takes all the call the calls that come into our call-center, gets the number of the person calling, the length of the call, etc. This data (after much manipulation) goes into one table (CTICalls). Then it creates another table for all the "work tickets" that were created due to the calls that came in. Now, all this data is in various other separate tables from the CTI Calls. It's a completely different system. Because of this, you can't just match up the name of the person who called to a ticket...further manipulation is required. All these tickets go into another table (CRMTickets). On top of that, the silly people who want this report want to know that name of the person who called. :) Of course, all I've got is the phone number. This requires another table because the table which actually contains the phone numbers has lots of duplicates and other bad things. So now I've got a phone number table with the name of the person calling. Great. So now the SP creates another table, which matches the CTI calls to the CRM tickets, sticks in the name of the person calling. So now I've got the table I need for the report. The first report is a summary - the number of calls per call center agent, and the number of tickets created. Next report lists all the calls that have no ticket created. Then we just list all of the calls, and then all of the tickets.

Hopefully, now you can see why I want to run the data generation first and why it can't really be broken up.

Jennifer

|||The only way I can think of ensuring the order in which the dataset queries are executed is to use dependant parameters, even if you just use some dummy values.|||

Hi,

I had quite similar problem with multiple sp-based datasets. The first one populated the global temporary tables and subsequent datasets displayed the data. To make sure the master SP will be executed first and no other datasets will be run before master SP completes, you have to do the following:

- Organize your datasets in exact order you need them to run. Datasets can only be moved by editing the RDL file. Find the <Datasets> section and move individual dataset sections.

- Enable transaction flag of the data source to prevent you datasets from being executed concurrently. In the RDL file, add <Transaction> element to the data source properties. Below is the example.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="ds_inv_rep">
<Transaction>true</Transaction>
<DataSourceReference>ds_inv_rep</DataSourceReference>
<rd:DataSourceID>7f21a5bb-83e1-4c9c-a32b-5ee080055ed6</rd:DataSourceID>
</DataSource>

Wapper

running .sql file

i am creating a few dynamic tables and views , is there a way to execute a .sql file from asp.net (vb.net) ?
Sure, check out the advice given by Thona in this thread:http://forums.asp.net/613349/showpost.aspx.
|||thank you

Run multiple SSIS packages in order

I built 3 diffrent packages and i want to be executed in order. The first one is exporting some tables to another database and the other two packages are based on the database built in the first package.

Any suggestions?

Thank you in advance:)

I assume you wish to automate this process. The simplest option would be to create a SQL Server Agent job, and just have three steps within the job, one for each package.

A more SSIS orientated approach would be to use a master package, that has three execute package tasks, one for each of your existing packages. Link the Execute package Tasks together with workflow constraints to enforce the order of execution for the tasks and hence the packages.

|||

Darren,

Just a question: when we're talking about the maximum number of DTSX inside a DTSPROJ only limitation is the memory?

TIA

|||

Yes.

If you do have a lot then I'd be tempted to write a more dynamic process.

Perhaps use a SQL table to contain the list of packages, and also have a column for the order. You could then use the Exec SQL Task with simple SELECT query to extract the list of packages in order of execution. You could store this results in a variable, and then loop and shred this via the For Each Loop.

This example uses Data Flow task to get the result set into a variable, but I normally find an Exec SQL Task easier. It does show the shredding of the results via the loop though.

Shredding a Recordset

(http://www.sqlis.com/default.aspx?59)

|||

I used the SQL Server Agent Job.

thanks for the help :)

Run all .sql files in a folder

I have created scripts to create approximately 200 tables. I have all of
these .sql files saved in a folder on a server. Is there a way to execute o
r
run all of the files at once in this folder instead of running them one at a
time?
ThanksYou could write a batch file - use a 'for' loop and call osql passing the
file path and other appropriate parameters.
"bbasile" wrote:

> I have created scripts to create approximately 200 tables. I have all of
> these .sql files saved in a folder on a server. Is there a way to execute
or
> run all of the files at once in this folder instead of running them one at
a
> time?
> Thanks|||Sounds good. Are there any examples out there I could take a look at?
"KH" wrote:
> You could write a batch file - use a 'for' loop and call osql passing the
> file path and other appropriate parameters.
>
> "bbasile" wrote:
>|||that's exactly what I was going to suggest.
another way would be to use the OA methods and FileSystemObjects to load
each of the files and then execute them.
> You could write a batch file - use a 'for' loop and call osql passing the
> file path and other appropriate parameters.
>
> "bbasile" wrote:
>
new

Run a select from against two dbs.

Hi,
is there any possiblity to run a select statement against the tables of two different databases which are both installed on the same database server ? Does anybody know how to join those tables reasonable way ?
Thnxselect *
from db1.dbo.table1 t1 inner join db2.dbo.table2 t2
on t1.pk = t2.pk|||Check BOL (http://msdn2.microsoft.com/en-us/library/ms187879.aspx).

-PatP|||Use Linked Servers concept and BOL is your friend.|||Linked servers unnecessary since both dbs on same server.

run a query against another server

Hi, I have got SSMSE installed and am seeing how I can use it for my work stuff rather than MSDE.

I used to import using Enterprise Manager tables from our corp servers into my local MSDE to work with.

I have managed to create the required tables in SQL Exprerss and can see I have a connection to my local server (SQL Express) and my corp server.

When I query sys.servers I only see SQL Express and not the corp one. How do I run a select query that selects from the corp and inserts into the local. I have all the insert fields etc but it errors stating the corp database is not in sys.serevrs?

Thanks

hi,

you have to define a "linked server"..

select the Server Objects->Linked Servers node in SSMSE in your local connection server... add a linked server pointing to the corp server providing the appropriate credentials..

you can then query the linked server providing the 4 part naming of the object like

SELECT * FROM linked_server_name.database_name.schema_name.object_name;

once you have the connection working, you can then INSERT SELECT into your local database..

regards

|||Hi, I tried this but get errors?

I can connect in server explorer within VS.NET 2005 Pro IDE and get the following information:

Provider=
.NET Framework Data Provider for SQL Server

Connection String=
Data Source=Virtue;Initial Catalog=orbital;Integrated Security=True

I do not get an option to choose this provider in the combobox on the New Linked Server page?

So I get this error?

TITLE: Microsoft SQL Server Management Studio Express

"The linked server has been created but failed a connection test. Do you want to keep the linked server?"

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

The OLE DB provider "SQLNCLI" for linked server "VIRTUE" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "VIRTUE".
OLE DB provider "SQLNCLI" for linked server "VIRTUE" returned message "Invalid authorization specification".
OLE DB provider "SQLNCLI" for linked server "VIRTUE" returned message "Invalid connection string attribute". (Microsoft SQL Server, Error: 7399)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

|||

hi,

pachjo wrote:

Hi, I tried this but get errors?

I can connect in server explorer within VS.NET 2005 Pro IDE and get the following information:

Provider=
.NET Framework Data Provider for SQL Server

Connection String=
Data Source=Virtue;Initial Catalog=orbital;Integrated Security=True

I do not get an option to choose this provider in the combobox on the New Linked Server page?

this information is relative to the "Add connection" wizard in the Server Explorer, isn't it?

actually the .NET Framework Data Provider for SQL Server is SQL Native Client and, in my installation, it is obviously available in SSMSE as well as SQLNCLI..

So I get this error?

TITLE: Microsoft SQL Server Management Studio Express

"The linked server has been created but failed a connection test. Do you want to keep the linked server?"

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

The OLE DB provider "SQLNCLI" for linked server "VIRTUE" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "VIRTUE".
OLE DB provider "SQLNCLI" for linked server "VIRTUE" returned message "Invalid authorization specification".
OLE DB provider "SQLNCLI" for linked server "VIRTUE" returned message "Invalid connection string attribute". (Microsoft SQL Server, Error: 7399)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

did you provide the correct credential info for the remote server?

regards

|||Well I can connect no problem through server explorer and accerss the data. I used the same connection string from there and tried various things such as:

Linked Server = Virtue
Provider=SQL Native Client
Product Name=Virtue (not sure what to put here?)
Data source=Virtue
Connection String=Data Source=Virtue;Initial Catalog=orbital;Integrated Security=True
Catalog=blank

I am unsure what I am doing wrong here?
|||

hi,

Linked Server: network name of the remote SQL Server instance

Provider = SQL Native Client

Product Name = Provider = SQL Native Client

then you can go on bypassing the additional info of the "General" tab of the "Add linked server" dialog...

in the "Security" tab select the appropriate mapping or specify a specif SQL Server login (Be made using this security context)..

regards

|||

This is really getting me down as I am getting no where

I set the details as above and tried entering my nt logon details in various ways to include/omit the domain name but still no joy!

What I dont get is within VS.NET it connects straight away without bother and I can acces the tables! ?

|||

HI,

OK I am sort of there, but not completely?

I have created a connection to the remote server and created a linked server to it using the same coalation as the remote.

But when I create the tables, import the date all is well until I try to run a script and I get a coaltion conflict?

The remote is SQL_Latin1_General_CP1_CI_AS but I get an error complaining about

Latin1_General_CI_AS?

When I look at the remote and the linked they both say SQL_Latin1_General_CP1_CI_AS so I don't know where SQL_Latin1_General_CI_AS is coming from?

Help anyone....

|||

Oh...hold the phone!

I think I have found it?

The local database has the wrong coalation so.......I will start again ;-)