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 a proc. on a certain date help?

DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 3

SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,b.DateEntered,b.DateCompleted,b.Compile dBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID)ORDER BY a.HospitalID" queryout c:\test.txt -c -Sserver -Usa -Ptest
EXEC master.dbo.xp_cmdshell @.query

EXEC master.dbo.xp_sendmail @.recipients='test@.hotmail.com',
@.copy_recipients = 'test@.hotmail.com',
@.message='Submitting Results for the previous month.',
@.subject='BloodBank results for the previous month',@.attachments = '\\cen\c$\test.txt'

SELECT @.@.ERROR As ErrorNumber

I am trying to get this procedure to execute every month on the 4th of the month but if I run it today, or tomorrow it or any day it still runs,therefore the not looking at the date.
Is this correct,can this be done in this way,how can I get it to run when it recognizes the date number in the current dateUse SQL Agent (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_cs_6x0l.asp) to schedule it ?

-PatP|||DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = day(GetDate())
If @.returnDay = 3
begin
SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,b.DateEntered,b.DateCompleted,b.Compile dBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID)ORDER BY a.HospitalID" queryout c:\test.txt -c -Sserver -Usa -Ptest
EXEC master.dbo.xp_cmdshell @.query

EXEC master.dbo.xp_sendmail @.recipients='test@.hotmail.com',
@.copy_recipients = 'test@.hotmail.com',
@.message='Submitting Results for the previous month.',
@.subject='BloodBank results for the previous month',@.attachments = '\\cen\c$\test.txt'

SELECT @.@.ERROR As ErrorNumber
endsql

Running a DTS Packing in a Stored Proc

Does anyone know how to run a DTS package in a stored procedure?
Also, what security rights does one have to have to run the DTS package in a
stored proc?
Thanks,
Yosh>> Does anyone know how to run a DTS package in a stored procedure?
Check out the command line utility DTSRUN in SQL Server Books Online. You
should be able to call this utility using xp_cmdshell procedure.
Anith|||Use xp_cmdshell to access the DTS run utility from a stored proc. I am
pretty sure the only security rights needed is to be able to execute the
stored procedure.
Derek Davis
ddavis76@.gmail.com
"Yosh" <yoshi@.nospam.com> wrote in message
news:uh%23lU4KuFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Does anyone know how to run a DTS package in a stored procedure?
> Also, what security rights does one have to have to run the DTS package in
> a stored proc?
> Thanks,
> Yosh
>|||no need for peculiarities of xp_cmdshell...this works:
EXEC sp_run_DTSPackage 0, 'DTSName', @.sql_error_code out
IF (@.sql_error_code <> 0)
begin
select @.text = 'Error -60: Execution of DTS Package DTSName failed - ERROR '
+ CONVERT(VARCHAR, @.sql_error_code)
select @.result = -60
GOTO ERROR_POINT
end
where sp_run_DTSPackage is as follows:
CREATE PROCEDURE sp_run_DTSPackage (
@.o_run_id smallint,
@.o_pkg_desc varchar(50),
@.error_code int output )
AS
-- Script: sp_run_DTSPackage.sql
-- Date: October 2004
-- Author: Marc McGuckian
--
-- Description: Use OLE Automatiopn to load and execute the DTS package
@.o_pkg_desc
--
--
-- Return Values: 0 - SUCCESS
-- 1 - DATABASE ERROR
-- 11 - Failed to create an instance of the DTS.Package OLE object
-- 12 - Failed to load package
-- 13 - Failed to set/get DTS Package variable
-- 14 - Failed to obtain system parameter value
-- 15 - Execution of DTS Package Failed
-- 16 - Failed to destroy instance of the DTS.Package OLE object
-- 20 - Package Step Failed
--
--
-- Routines Called: sp_OACreate
-- sp_OAMethod
-- sp_OASetProperty
-- sp_OAGetProperty
-- sp_OADestroy
-- sp_OAGetErrorInfo
--
-- Tables Used:
--
-- OBJECTS Created:
-- TABLES:
-- STORED PROCS:
-- TEMP TABLES:
--
-- GRANTS EXECUTE TO PUBLIC
--
-- Modification History:
--
========================================
====================================
====
-- Name Date Description
--
----
--
--
--
========================================
====================================
====
BEGIN
DECLARE @.hres int, -- HRESULT returned by OLE
method call
@.pkg int, -- Package Object
@.errsrc varchar(255), -- DTS Package Error Source
@.errdesc varchar(255), -- DTS Package Error
Description
@.prop int, -- Package Property
@.o_pkg_prop_desc varchar(150), -- Package Property Name
@.o_pkg_prop_value varchar(150), -- Package Property Value
@.o_pkg_prop_order tinyint,
@.prop_to_set char(1),
@.dtsaccess varchar(50),
@.o_syslog_desc varchar(150) -- Message to be logged to
system log
SELECT @.error_code = 0, @.prop_to_set = 'Y',
@.o_pkg_prop_order = 0, @.dtsaccess = ''
UPDATE ot_lu_dts_properties
SET o_pkg_prop_value = o_param_value
FROM ot_lu_dts_properties dts,
ot_lu_system_parameter sys
WHERE dts.o_param_code = sys.o_param_code
SELECT @.error_code = @.@.ERROR
IF (@.error_code <> 0)
BEGIN
SELECT @.o_syslog_desc = 'Failed to update DTS properties
[ot_lu_dts_properties] - ERROR : ' + CONVERT(VARCHAR, @.error_code)
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
RETURN @.error_code
END
EXEC sp_run_DTS @.dtsaccess out
--Creates an instance of the DTS.Package OLE object
EXEC @.hres = sp_OACreate 'DTS.Package', @.pkg out
IF @.hres <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.pkg, @.errsrc OUT, @.errdesc OUT
SELECT @.o_syslog_desc = 'Error Number : ' + convert(varchar,@.hres)
+ ' ' + ' Source : ' + @.errsrc
+ ' ' + ' Description : ' + @.errdesc
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.o_syslog_desc = 'Failed to create an instance of the
DTS.Package OLE object'
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.error_code = 11
RETURN 11
END
--Load DTSLoadStaging package
EXEC @.hres = sp_OAMethod @.pkg, 'LoadFromSQLServer', null, '(local)',
@.PackageName = @.o_pkg_desc, @.PackagePassword = @.dtsaccess, @.Flags = 256
IF @.hres <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.pkg, @.errsrc OUT, @.errdesc OUT
SELECT @.o_syslog_desc = 'Error Number : ' + convert(varchar,@.hres)
+ ' ' + ' Source : ' + @.errsrc
+ ' ' + ' Description : ' + @.errdesc
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.o_syslog_desc = 'Failed to load package'
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.error_code = 12
RETURN 12
END
-- Set all DTS package properties
WHILE (@.prop_to_set = 'Y')
BEGIN
-- Find the next property to set
SELECT @.prop_to_set = 'N' -- Assume no more properties to set
SELECT @.prop_to_set = 'Y',
@.o_pkg_prop_desc = pro.o_pkg_prop_desc,
@.o_pkg_prop_value = pro.o_pkg_prop_value,
@.o_pkg_prop_order = pkp.o_pkg_prop_order
FROM ot_lu_dts_package pkg,
ot_lu_dts_properties pro,
ot_lu_dts_package_properties pkp
WHERE pkg.o_pkg_desc = @.o_pkg_desc
AND pkg.o_pkg_id = pkp.o_pkg_id
AND pkp.o_pkg_prop_id = pro.o_pkg_prop_id
AND pkp.o_pkg_prop_order = ( SELECT MIN(tbl.o_pkg_prop_order)
FROM ot_lu_dts_package_properties
tbl
WHERE tbl.o_pkg_id =
pkg.o_pkg_id
AND tbl.o_pkg_prop_order >
@.o_pkg_prop_order )
IF (@.prop_to_set = 'Y')
BEGIN
-- Set DTS Package property value
EXEC @.hres = sp_OASetProperty @.pkg, @.o_pkg_prop_desc,
@.o_pkg_prop_value
IF @.hres <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.pkg, @.errsrc OUT, @.errdesc OUT
SELECT @.o_syslog_desc = 'Error Number : ' +
convert(varchar,@.hres)
+ ' ' + ' Source : ' + @.errsrc
+ ' ' + ' Description : ' +
@.errdesc
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE',
'ERROR', @.o_syslog_desc
SELECT @.o_syslog_desc = 'Failed to set DTS Package
property ' + @.o_pkg_prop_desc
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE',
'ERROR', @.o_syslog_desc
SELECT @.error_code = 13
RETURN 13
END
END -- IF (@.prop_to_set = 'Y')
END -- WHILE (@.prop_to_set = 'Y')
--Set DTS Package Global Variables gvRun
SELECT @.o_pkg_prop_desc = 'GlobalVariables("gvRun").value'
EXEC @.hres = sp_OASetProperty @.pkg, @.o_pkg_prop_desc, @.o_run_id
IF @.hres <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.pkg, @.errsrc OUT, @.errdesc OUT
SELECT @.o_syslog_desc = 'Error Number : ' + convert(varchar,@.hres)
+ ' ' + ' Source : ' + @.errsrc
+ ' ' + ' Description : ' + @.errdesc
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.o_syslog_desc = 'Failed to set DTS Package Global Variable
gvRun'
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.error_code = 13
RETURN 13
END
--Execute Package
EXEC @.hres = sp_OAMethod @.pkg, 'Execute'
IF @.hres <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.pkg, @.errsrc OUT, @.errdesc OUT
SELECT @.o_syslog_desc = 'Error Number : ' + convert(varchar,@.hres)
+ ' ' + ' Source : ' + @.errsrc
+ ' ' + ' Description : ' + @.errdesc
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.o_syslog_desc = 'Execution of DTS Package DTSLoadStaging
Failed'
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.error_code = 15
RETURN 15
END
SELECT @.o_pkg_prop_desc = 'GlobalVariables("gvPkgStatus").value'
EXEC @.hres = sp_OAGetProperty @.pkg, @.o_pkg_prop_desc, @.error_code OUT
IF @.hres <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.pkg, @.errsrc OUT, @.errdesc OUT
SELECT @.o_syslog_desc = 'Error Number : ' + convert(varchar,@.hres)
+ ' ' + ' Source : ' + @.errsrc
+ ' ' + ' Description : ' + @.errdesc
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.o_syslog_desc = 'Failed to obtain DTS Package Global
Variable gvPkgStatus'
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.error_code = 13
RETURN 13
END
SELECT @.o_pkg_prop_desc = 'GlobalVariables("gvPkgStatusDesc").value'
EXEC @.hres = sp_OAGetProperty @.pkg, @.o_pkg_prop_desc, @.o_syslog_desc OUT
IF @.hres <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.pkg, @.errsrc OUT, @.errdesc OUT
SELECT @.o_syslog_desc = 'Error Number : ' + convert(varchar,@.hres)
+ ' ' + ' Source : ' + @.errsrc
+ ' ' + ' Description : ' + @.errdesc
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.o_syslog_desc = 'Failed to obtain DTS Package Global
Variable gvPkgStatus'
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.error_code = 13
RETURN 13
END
--Clean up
EXEC @.hres = sp_OADestroy @.pkg
IF @.hres <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.pkg, @.errsrc OUT, @.errdesc OUT
SELECT @.o_syslog_desc = 'Error Number : ' + convert(varchar,@.hres)
+ ' ' + ' Source : ' + @.errsrc
+ ' ' + ' Description : ' + @.errdesc
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.o_syslog_desc = 'Failed to destroy instance of the
DTS.Package OLE object'
EXEC sp_lo_insert_dsslog @.o_run_id, 'RUNDTSPACKAGE', 'ERROR',
@.o_syslog_desc
SELECT @.error_code = 16
RETURN 16
END
RETURN @.error_code
END
you're welcome

Run two stored procs in parallel

Hi
I have a simple stored proc which runs in less than a second.
This stored proc calculates different values based on parameters
passed.
For example sp_name "test 1 ' and
sp_name 'test 2 ' are totally different.
Now The issue is this stored proc is called more than 500 times in some
applications.
So these applications run for 500+ seconds . Although load on sql
server is almost negligible. Memory is also hugely available.
I need your suggestion in running these all at the same time.
In other words all 500 must be started at the same time ..there is no
need for one procedure to complete and the next to start. None of the
results are depenedent on any other results.
pls help me .
KannanIs this SQL 2000 or SQL 2005?
In 2005 is very easily accomplished using Service Broker. Instead of calling
the procedure 500 times, send 500 messages to a service that uses an
activated stored procedure. The message body would contain the arguments you
now pass in. Using the 'max_queue_readers' clause of CREATE QUEUE (see
http://msdn2.microsoft.com/en-us/library/ms190495(en-US,SQL.90).aspx) you
can control the maximum number of procedures to run in paralel.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegroups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>|||> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
Remus provided some SQL 2005 suggestions to improve throughput but I suggest
you do root cause performance analysis rather than throw parallel processing
at the problem.
Why do the procs run so long? Disk i/o? CPU? Network utilization? Do you
have SET NOCOUNT ON in your procs? If the load on SQL Server is low and you
have no resource problem, the procs should either run very fast or you
should observe high resource utilization.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegroups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>|||Its SQL server 2000.
Its not a single proc that runs for 500 seconds its becuse the same
procedure is called 500 times with different parameters.
As I mentioned if you run the stored proc once it wont even run for a
second.
There are applications which take input from users and invoke my stored
procedure , The user may enter one value or 500 value.
If the user enters 500 values then the application calls my stored proc
500 times one after another.
That is what is causing a problem , So I created another proc which can
take any number of parameters and then invoke my orignal proc .
Like exec super_proc a,b,c will invoke
exec original_proc 'a'
exec original_proc 'b'
exec original_proc 'c'
Here again SQL server starts executing the next proc after it finishes
first.
I dont want sql server to execute serially but paralelly.
This explains why the load and memory are not utilized fully.
regards
Kannan|||> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
Yes, I gathered this from your original post. My concern is that one second
is a *very* long time. I have seen single-threaded applications repeatedly
execute an insert stored procedure several hundred times per second. If
each of your proc executions run for a second, I would expect that you would
see high resource utilization (CPU, disk or network) for the duration of the
application run. If you don't, then something is wrong. You can certainly
throw additional threads at the problem but I suspect the performance
improvement won't be as good as you would like. For example, if you are I/O
bound, parallel processing might actually hurt performance.
> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
This might make a miniscule difference due to reduced latency but it's
obviously not the long pole in the tent. I don't think you are addressing
the root cause of your slowness. Can you provide more details on the
calculations the proc performs. Perhaps some code and DDL would help.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134591179.063554.66800@.g14g2000cwa.googlegroups.com...
> Its SQL server 2000.
> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
> There are applications which take input from users and invoke my stored
> procedure , The user may enter one value or 500 value.
> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
> Like exec super_proc a,b,c will invoke
> exec original_proc 'a'
> exec original_proc 'b'
> exec original_proc 'c'
> Here again SQL server starts executing the next proc after it finishes
> first.
> I dont want sql server to execute serially but paralelly.
> This explains why the load and memory are not utilized fully.
> regards
> Kannan
>|||I used one second just to illustrate my point. It actually runs in
milliseconds as u have mentioned.
I am not sure if i can post the code / Let me check on this .
will opening multiple connections from end application help ' multiple
connections will be run without any wait time right.
In other words if I run the same proc from two places at the same time
then the second proc will not wait for first one to complete right ?
This is what I want to achive using one connection !.
Kannan|||>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
Is the 500 seconds total elapsed time accurate? Total elapsed time should
be roughly the sum the proc duration plus application processing time. So,
if you run a proc 500 times at an average of 10 ms. each, total duration
ought to be about 5 seconds.
> will opening multiple connections from end application help ' multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
There should be very little 'wait' time between proc executions on a single
connection. Perhaps a little latency but with only 500 executions, it
shouldn't add up to more than a few milliseconds total.
Multiple connections (multi-threaded processing) might help if you are not
resource constrained but you really need to know the cause of the problem is
before you develop a solution.
> I am not sure if i can post the code / Let me check on this .
The code might not be needed if the proc only runs in few milliseconds.
There's something else going on if the total elapsed time is high.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134594222.338716.212980@.g43g2000cwa.googlegroups.com...
>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
> I am not sure if i can post the code / Let me check on this .
> will opening multiple connections from end application help ' multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
> Kannan
>

Run two stored procs in parallel

Hi
I have a simple stored proc which runs in less than a second.
This stored proc calculates different values based on parameters
passed.
For example sp_name "test 1 ' and
sp_name 'test 2 ' are totally different.
Now The issue is this stored proc is called more than 500 times in some
applications.
So these applications run for 500+ seconds . Although load on sql
server is almost negligible. Memory is also hugely available.
I need your suggestion in running these all at the same time.
In other words all 500 must be started at the same time ..there is no
need for one procedure to complete and the next to start. None of the
results are depenedent on any other results.
pls help me .
Kannan
Is this SQL 2000 or SQL 2005?
In 2005 is very easily accomplished using Service Broker. Instead of calling
the procedure 500 times, send 500 messages to a service that uses an
activated stored procedure. The message body would contain the arguments you
now pass in. Using the 'max_queue_readers' clause of CREATE QUEUE (see
http://msdn2.microsoft.com/en-us/library/ms190495(en-US,SQL.90).aspx) you
can control the maximum number of procedures to run in paralel.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegr oups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>
|||> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
Remus provided some SQL 2005 suggestions to improve throughput but I suggest
you do root cause performance analysis rather than throw parallel processing
at the problem.
Why do the procs run so long? Disk i/o? CPU? Network utilization? Do you
have SET NOCOUNT ON in your procs? If the load on SQL Server is low and you
have no resource problem, the procs should either run very fast or you
should observe high resource utilization.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegr oups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>
|||Its SQL server 2000.
Its not a single proc that runs for 500 seconds its becuse the same
procedure is called 500 times with different parameters.
As I mentioned if you run the stored proc once it wont even run for a
second.
There are applications which take input from users and invoke my stored
procedure , The user may enter one value or 500 value.
If the user enters 500 values then the application calls my stored proc
500 times one after another.
That is what is causing a problem , So I created another proc which can
take any number of parameters and then invoke my orignal proc .
Like exec super_proc a,b,c will invoke
exec original_proc 'a'
exec original_proc 'b'
exec original_proc 'c'
Here again SQL server starts executing the next proc after it finishes
first.
I dont want sql server to execute serially but paralelly.
This explains why the load and memory are not utilized fully.
regards
Kannan
|||> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
Yes, I gathered this from your original post. My concern is that one second
is a *very* long time. I have seen single-threaded applications repeatedly
execute an insert stored procedure several hundred times per second. If
each of your proc executions run for a second, I would expect that you would
see high resource utilization (CPU, disk or network) for the duration of the
application run. If you don't, then something is wrong. You can certainly
throw additional threads at the problem but I suspect the performance
improvement won't be as good as you would like. For example, if you are I/O
bound, parallel processing might actually hurt performance.

> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
This might make a miniscule difference due to reduced latency but it's
obviously not the long pole in the tent. I don't think you are addressing
the root cause of your slowness. Can you provide more details on the
calculations the proc performs. Perhaps some code and DDL would help.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134591179.063554.66800@.g14g2000cwa.googlegro ups.com...
> Its SQL server 2000.
> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
> There are applications which take input from users and invoke my stored
> procedure , The user may enter one value or 500 value.
> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
> Like exec super_proc a,b,c will invoke
> exec original_proc 'a'
> exec original_proc 'b'
> exec original_proc 'c'
> Here again SQL server starts executing the next proc after it finishes
> first.
> I dont want sql server to execute serially but paralelly.
> This explains why the load and memory are not utilized fully.
> regards
> Kannan
>
|||I used one second just to illustrate my point. It actually runs in
milliseconds as u have mentioned.
I am not sure if i can post the code / Let me check on this .
will opening multiple connections from end application help ? multiple
connections will be run without any wait time right.
In other words if I run the same proc from two places at the same time
then the second proc will not wait for first one to complete right ?
This is what I want to achive using one connection !.
Kannan
|||>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
Is the 500 seconds total elapsed time accurate? Total elapsed time should
be roughly the sum the proc duration plus application processing time. So,
if you run a proc 500 times at an average of 10 ms. each, total duration
ought to be about 5 seconds.

> will opening multiple connections from end application help ? multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
There should be very little 'wait' time between proc executions on a single
connection. Perhaps a little latency but with only 500 executions, it
shouldn't add up to more than a few milliseconds total.
Multiple connections (multi-threaded processing) might help if you are not
resource constrained but you really need to know the cause of the problem is
before you develop a solution.

> I am not sure if i can post the code / Let me check on this .
The code might not be needed if the proc only runs in few milliseconds.
There's something else going on if the total elapsed time is high.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134594222.338716.212980@.g43g2000cwa.googlegr oups.com...
>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
> I am not sure if i can post the code / Let me check on this .
> will opening multiple connections from end application help ? multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
> Kannan
>

Run two stored procs in parallel

Hi
I have a simple stored proc which runs in less than a second.
This stored proc calculates different values based on parameters
passed.
For example sp_name "test 1 ' and
sp_name 'test 2 ' are totally different.
Now The issue is this stored proc is called more than 500 times in some
applications.
So these applications run for 500+ seconds . Although load on sql
server is almost negligible. Memory is also hugely available.
I need your suggestion in running these all at the same time.
In other words all 500 must be started at the same time ..there is no
need for one procedure to complete and the next to start. None of the
results are depenedent on any other results.
pls help me .
KannanIs this SQL 2000 or SQL 2005?
In 2005 is very easily accomplished using Service Broker. Instead of calling
the procedure 500 times, send 500 messages to a service that uses an
activated stored procedure. The message body would contain the arguments you
now pass in. Using the 'max_queue_readers' clause of CREATE QUEUE (see
http://msdn2.microsoft.com/en-us/library/ms190495(en-US,SQL.90).aspx) you
can control the maximum number of procedures to run in paralel.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegroups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>|||> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
Remus provided some SQL 2005 suggestions to improve throughput but I suggest
you do root cause performance analysis rather than throw parallel processing
at the problem.
Why do the procs run so long? Disk i/o? CPU? Network utilization? Do you
have SET NOCOUNT ON in your procs? If the load on SQL Server is low and you
have no resource problem, the procs should either run very fast or you
should observe high resource utilization.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegroups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>|||Its SQL server 2000.
Its not a single proc that runs for 500 seconds its becuse the same
procedure is called 500 times with different parameters.
As I mentioned if you run the stored proc once it wont even run for a
second.
There are applications which take input from users and invoke my stored
procedure , The user may enter one value or 500 value.
If the user enters 500 values then the application calls my stored proc
500 times one after another.
That is what is causing a problem , So I created another proc which can
take any number of parameters and then invoke my orignal proc .
Like exec super_proc a,b,c will invoke
exec original_proc 'a'
exec original_proc 'b'
exec original_proc 'c'
Here again SQL server starts executing the next proc after it finishes
first.
I dont want sql server to execute serially but paralelly.
This explains why the load and memory are not utilized fully.
regards
Kannan|||> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
Yes, I gathered this from your original post. My concern is that one second
is a *very* long time. I have seen single-threaded applications repeatedly
execute an insert stored procedure several hundred times per second. If
each of your proc executions run for a second, I would expect that you would
see high resource utilization (CPU, disk or network) for the duration of the
application run. If you don't, then something is wrong. You can certainly
throw additional threads at the problem but I suspect the performance
improvement won't be as good as you would like. For example, if you are I/O
bound, parallel processing might actually hurt performance.

> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
This might make a miniscule difference due to reduced latency but it's
obviously not the long pole in the tent. I don't think you are addressing
the root cause of your slowness. Can you provide more details on the
calculations the proc performs. Perhaps some code and DDL would help.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134591179.063554.66800@.g14g2000cwa.googlegroups.com...
> Its SQL server 2000.
> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
> There are applications which take input from users and invoke my stored
> procedure , The user may enter one value or 500 value.
> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
> Like exec super_proc a,b,c will invoke
> exec original_proc 'a'
> exec original_proc 'b'
> exec original_proc 'c'
> Here again SQL server starts executing the next proc after it finishes
> first.
> I dont want sql server to execute serially but paralelly.
> This explains why the load and memory are not utilized fully.
> regards
> Kannan
>|||I used one second just to illustrate my point. It actually runs in
milliseconds as u have mentioned.
I am not sure if i can post the code / Let me check on this .
will opening multiple connections from end application help ' multiple
connections will be run without any wait time right.
In other words if I run the same proc from two places at the same time
then the second proc will not wait for first one to complete right ?
This is what I want to achive using one connection !.
Kannan|||>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
Is the 500 seconds total elapsed time accurate? Total elapsed time should
be roughly the sum the proc duration plus application processing time. So,
if you run a proc 500 times at an average of 10 ms. each, total duration
ought to be about 5 seconds.

> will opening multiple connections from end application help ' multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
There should be very little 'wait' time between proc executions on a single
connection. Perhaps a little latency but with only 500 executions, it
shouldn't add up to more than a few milliseconds total.
Multiple connections (multi-threaded processing) might help if you are not
resource constrained but you really need to know the cause of the problem is
before you develop a solution.

> I am not sure if i can post the code / Let me check on this .
The code might not be needed if the proc only runs in few milliseconds.
There's something else going on if the total elapsed time is high.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134594222.338716.212980@.g43g2000cwa.googlegroups.com...
>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
> I am not sure if i can post the code / Let me check on this .
> will opening multiple connections from end application help ' multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
> Kannan
>

Run SP from UDF?

Hi,
Is it possible to run a Stored Proc from a UDF?
If it is possible, how do I store the resultset that the SP produces? The
code I have does not work, as I don't think you can use the EXEC keyword in
a
UDF but here it is anyway:
DECLARE @.tempMeterData TABLE (
RoundedTimestamp smalldatetime NOT NULL,
[Value] float NULL)
INSERT INTO @.tempMeterData EXEC sp_MySP @.ID
Any help on how I could achieve this would be gratefully received.
Thanks,
SteveAs you said, you can not call a stored procedure (you can call exteded ones)
inside a udf.
What are you trying to accomplish?
AMB
"SteveN" wrote:

> Hi,
> Is it possible to run a Stored Proc from a UDF?
> If it is possible, how do I store the resultset that the SP produces? The
> code I have does not work, as I don't think you can use the EXEC keyword i
n a
> UDF but here it is anyway:
> DECLARE @.tempMeterData TABLE (
> RoundedTimestamp smalldatetime NOT NULL,
> [Value] float NULL)
> INSERT INTO @.tempMeterData EXEC sp_MySP @.ID
> Any help on how I could achieve this would be gratefully received.
> Thanks,
> Steve
>|||I am updating an old UDF which is used widely in an ASP.NET application. Th
e
UDF returns a table, so it probably should be an SP, but that is a different
matter.
Data the UDF needs to return now (depending on parameters) is calculated in
an existing SP, so I want to be able to get the return data from that SP to
use in the existing UDF.
Any ideas on what I can do?
Thanks,
Steve
"Alejandro Mesa" wrote:
> As you said, you can not call a stored procedure (you can call exteded one
s)
> inside a udf.
> What are you trying to accomplish?
>
> AMB
> "SteveN" wrote:
>|||Replace the function with an SP or move the logic from the existing SP
into the function.
David Portas
SQL Server MVP
--|||What is the reason of returning this resultset using a udf an not calling th
e
sp directly?
Are you planning to do extra manipulation on the result of the sp?
How complicated is the process inside the sp?
AMB
"Steve Norman" wrote:
> I am updating an old UDF which is used widely in an ASP.NET application.
The
> UDF returns a table, so it probably should be an SP, but that is a differe
nt
> matter.
> Data the UDF needs to return now (depending on parameters) is calculated i
n
> an existing SP, so I want to be able to get the return data from that SP t
o
> use in the existing UDF.
> Any ideas on what I can do?
> Thanks,
> Steve
> "Alejandro Mesa" wrote:
>|||Further manipulation on the resultset will be done in the UDF before it is
returned as a table, the SP that is called is 367 lines and is very
complicated indeed.
I am trying to find an alternative to re-writing the UDF as a SP as there
are other UDF's that use this UDF as the core to their data manupulation, so
I would also need to re-write all of these as SPs.
If that is the only answer than that is what I will have to do, but I was
trying to save a couple of days of re-writing and testing by simply getting
data from an exisitng SP in a couple of lines of code.
Steve
"Alejandro Mesa" wrote:
> What is the reason of returning this resultset using a udf an not calling
the
> sp directly?
> Are you planning to do extra manipulation on the result of the sp?
> How complicated is the process inside the sp?
>
> AMB
>
> "Steve Norman" wrote:
>|||Steve,
Your working with .Net,
Run you sp and output the parameters then run your function from .net with
newly acquired params ?
"Steve Norman" wrote:
> Further manipulation on the resultset will be done in the UDF before it is
> returned as a table, the SP that is called is 367 lines and is very
> complicated indeed.
> I am trying to find an alternative to re-writing the UDF as a SP as there
> are other UDF's that use this UDF as the core to their data manupulation,
so
> I would also need to re-write all of these as SPs.
> If that is the only answer than that is what I will have to do, but I was
> trying to save a couple of days of re-writing and testing by simply gettin
g
> data from an exisitng SP in a couple of lines of code.
> Steve
> "Alejandro Mesa" wrote:
>

Run a stored proc using vbscript

I understand that it may not be a good thing to use an ADO execute statement
using VBscript to update a SQL database. Rather the preferred method is to
execute a stored procedure. How does one do this using VBscript, and how
would you confirm that the update took place properly ?
Thanks !Hi,
You will receive an error by Errors collection on connection object.
Tomasz B.
"Rob C" wrote:

> I understand that it may not be a good thing to use an ADO execute stateme
nt
> using VBscript to update a SQL database. Rather the preferred method is
to
> execute a stored procedure. How does one do this using VBscript, and how
> would you confirm that the update took place properly ?
> Thanks !
>
>|||How To Invoke a Stored Procedure with ADO Query Using VBA/C++/Java
http://support.microsoft.com/?kbid=185125
AMB
"Rob C" wrote:

> I understand that it may not be a good thing to use an ADO execute stateme
nt
> using VBscript to update a SQL database. Rather the preferred method is
to
> execute a stored procedure. How does one do this using VBscript, and how
> would you confirm that the update took place properly ?
> Thanks !
>
>|||Rob C wrote:
> I understand that it may not be a good thing to use an ADO execute
> statement using VBscript to update a SQL database. Rather the
> preferred method is to execute a stored procedure. How does one do
> this using VBscript, and how would you confirm that the update took
> place properly ?
> Thanks !
This was written with ASP in mind. Remove "Server." from the createobject
statements to make it relevant to straight vbscript :
http://groups.google.com/groups?hl=...FTNGP12.phx.gbl
As somebody else stated, errors will be returned to the connection's errors
collection (the first error in the collection will usually bubble up to the
vbscript error-handler as well)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Run a stored proc thru the index tuning wizard?

Is there any way to run the index tuning wizard on a stored proc, other than
copying the content of the stored proc into sql query analyzer?
I tried highlighting a sql call to the sp, and running itw on that along
with all tables in the db, but it just said it couldn't find anything to
add. And I know that is not the right answer.
Thanks in advance!
Hi Derrick
"Derrick" wrote:

> Is there any way to run the index tuning wizard on a stored proc, other than
> copying the content of the stored proc into sql query analyzer?
> I tried highlighting a sql call to the sp, and running itw on that along
> with all tables in the db, but it just said it couldn't find anything to
> add. And I know that is not the right answer.
> Thanks in advance!
>
If your stored procedure uses temporary tables then ITW will not be able to
tune it.
John

Run a stored proc thru the index tuning wizard?

Is there any way to run the index tuning wizard on a stored proc, other than
copying the content of the stored proc into sql query analyzer?
I tried highlighting a sql call to the sp, and running itw on that along
with all tables in the db, but it just said it couldn't find anything to
add. And I know that is not the right answer.
Thanks in advance!Hi Derrick
"Derrick" wrote:

> Is there any way to run the index tuning wizard on a stored proc, other th
an
> copying the content of the stored proc into sql query analyzer?
> I tried highlighting a sql call to the sp, and running itw on that along
> with all tables in the db, but it just said it couldn't find anything to
> add. And I know that is not the right answer.
> Thanks in advance!
>
If your stored procedure uses temporary tables then ITW will not be able to
tune it.
John

Run a stored proc thru the index tuning wizard?

Is there any way to run the index tuning wizard on a stored proc, other than
copying the content of the stored proc into sql query analyzer?
I tried highlighting a sql call to the sp, and running itw on that along
with all tables in the db, but it just said it couldn't find anything to
add. And I know that is not the right answer.
Thanks in advance!Hi Derrick
"Derrick" wrote:
> Is there any way to run the index tuning wizard on a stored proc, other than
> copying the content of the stored proc into sql query analyzer?
> I tried highlighting a sql call to the sp, and running itw on that along
> with all tables in the db, but it just said it couldn't find anything to
> add. And I know that is not the right answer.
> Thanks in advance!
>
If your stored procedure uses temporary tables then ITW will not be able to
tune it.
John

run 3 dynamic selects from stored proc

I am trying to run 3 dynamic selects from stored proc, really only
the table name is dynamic.. Anway I'm kinda lost on how I can
accomplish this.. this is what I have but it only returns the first
result.. that being basic

CREATE PROCEDURE email_complexity

@.TableName VarChar(100)

AS
Declare @.SQL VarChar(1000)
Declare @.SQL1 VarChar(1000)

Set nocount on

SELECT @.SQL = 'SELECT Count(complexity) AS basic FROM '
SELECT @.SQL = @.SQL + @.TableName
SELECT @.SQL = @.SQL + ' WHERE len(complexity) = 5'

Exec ( @.SQL)

SELECT @.SQL1 = 'SELECT Count(complexity) AS moderate FROM '
SELECT @.SQL1 = @.SQL1 + @.TableName
SELECT @.SQL1 = @.SQL1 + ' WHERE len(complexity) = 8'

Exec ( @.SQL1)

Return

Is there a better way of doing this??

tia

DaveOn 30 Sep 2004 10:12:28 -0700, dave wrote:

> I am trying to run 3 dynamic selects from stored proc, really only
> the table name is dynamic.. Anway I'm kinda lost on how I can
> accomplish this.. this is what I have but it only returns the first
> result.. that being basic
> CREATE PROCEDURE email_complexity
> @.TableName VarChar(100)
> AS
> Declare @.SQL VarChar(1000)
> Declare @.SQL1 VarChar(1000)
> Set nocount on
> SELECT @.SQL = 'SELECT Count(complexity) AS basic FROM '
> SELECT @.SQL = @.SQL + @.TableName
> SELECT @.SQL = @.SQL + ' WHERE len(complexity) = 5'
> Exec ( @.SQL)
> SELECT @.SQL1 = 'SELECT Count(complexity) AS moderate FROM '
> SELECT @.SQL1 = @.SQL1 + @.TableName
> SELECT @.SQL1 = @.SQL1 + ' WHERE len(complexity) = 8'
> Exec ( @.SQL1)
>
> Return
> Is there a better way of doing this??
> tia
> Dave

If your client isn't prepared to accept multiple resultsets, then you'll
only see the first one. You could join them together with a union:

CREATE PROCEDURE email_complexity

@.TableName VarChar(100)

AS
Declare @.SQL VarChar(1000)
Declare @.SQL1 VarChar(1000)

Set nocount on

SELECT @.SQL = 'SELECT Count(complexity) AS basic FROM '
SELECT @.SQL = @.SQL + @.TableName
SELECT @.SQL = @.SQL + ' WHERE len(complexity) = 5'

SELECT @.SQL = @.SQL + ' UNION ALL '

SELECT @.SQL1 = 'SELECT Count(complexity) AS moderate FROM '
SELECT @.SQL1 = @.SQL1 + @.TableName
SELECT @.SQL1 = @.SQL1 + ' WHERE len(complexity) = 8'

Exec ( @.SQL1)

Return|||Ross Presser <rpresser@.imtek.com> wrote in message news:<nt2t9zlcjwzx.dlg@.rpresser.invalid>...
> On 30 Sep 2004 10:12:28 -0700, dave wrote:
> > I am trying to run 3 dynamic selects from stored proc, really only
> > the table name is dynamic.. Anway I'm kinda lost on how I can
> > accomplish this.. this is what I have but it only returns the first
> > result.. that being basic
> > CREATE PROCEDURE email_complexity
> > @.TableName VarChar(100)
> > AS
> > Declare @.SQL VarChar(1000)
> > Declare @.SQL1 VarChar(1000)
> > Set nocount on
> > SELECT @.SQL = 'SELECT Count(complexity) AS basic FROM '
> > SELECT @.SQL = @.SQL + @.TableName
> > SELECT @.SQL = @.SQL + ' WHERE len(complexity) = 5'
> > Exec ( @.SQL)
> > SELECT @.SQL1 = 'SELECT Count(complexity) AS moderate FROM '
> > SELECT @.SQL1 = @.SQL1 + @.TableName
> > SELECT @.SQL1 = @.SQL1 + ' WHERE len(complexity) = 8'
> > Exec ( @.SQL1)
> > Return
> > Is there a better way of doing this??
> > tia
> > Dave
> If your client isn't prepared to accept multiple resultsets, then you'll
> only see the first one. You could join them together with a union:
> CREATE PROCEDURE email_complexity
> @.TableName VarChar(100)
> AS
> Declare @.SQL VarChar(1000)
> Declare @.SQL1 VarChar(1000)
> Set nocount on
> SELECT @.SQL = 'SELECT Count(complexity) AS basic FROM '
> SELECT @.SQL = @.SQL + @.TableName
> SELECT @.SQL = @.SQL + ' WHERE len(complexity) = 5'
> SELECT @.SQL = @.SQL + ' UNION ALL '
> SELECT @.SQL1 = 'SELECT Count(complexity) AS moderate FROM '
> SELECT @.SQL1 = @.SQL1 + @.TableName
> SELECT @.SQL1 = @.SQL1 + ' WHERE len(complexity) = 8'
> Exec ( @.SQL1)
>
> Return

But if you do that, you should be aware that what you'll receive in
return is two rows, under the single column "basic". There are ways to
improve this (to either add a second column with the texts "basic" and
"moderate", or by converting it into a single row, with columns
"basic" and "moderate").

If the OP is interested in either of these approaches, reply back
here, and I'll post more.

Run 2nd SQL in stored proc

I have a stored procedure (see below) that inserts records into a history
table from a source table (PayTotals). After that is successful, I would
like to run a delete statement "DELETE FROM dbo.PayTotals WHERE PaidYr =
@.PaidYr" but only if the previous INSERT is successful. What would I need
to add to the proc below to make that happen? Thanks.
David
CREATE PROCEDURE [mc_inshstPayTotals]
(@.PaidYr [smallint])
AS INSERT INTO dbo.hstPayTotals
([EmployerCaseNumber],
[EmployerMax],
[PayFirst],
[PaySecond],
[PaidYr],
[PaidMo])
SELECT
EmployerCaseNumber,
EmployerMax,
PayFirst,
PaySecond,
PaidYr,
PaidMo
FROM dbo.PayTotals
WHERE PaidYr = @.PaidYr
GOWhat does "successful" mean? If it means exactly one row is inserted:
IF @.@.ROWCOUNT = 1
BEGIN
DELETE ...
END
Otherwise, you'll have to be more specific...
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:OdaU4pF8FHA.2616@.TK2MSFTNGP15.phx.gbl...
>I have a stored procedure (see below) that inserts records into a history
>table from a source table (PayTotals). After that is successful, I would
>like to run a delete statement "DELETE FROM dbo.PayTotals WHERE PaidYr =
>@.PaidYr" but only if the previous INSERT is successful. What would I need
>to add to the proc below to make that happen? Thanks.
> David
> CREATE PROCEDURE [mc_inshstPayTotals]
> (@.PaidYr [smallint])
> AS INSERT INTO dbo.hstPayTotals
> ([EmployerCaseNumber],
> [EmployerMax],
> [PayFirst],
> [PaySecond],
> [PaidYr],
> [PaidMo])
> SELECT
> EmployerCaseNumber,
> EmployerMax,
> PayFirst,
> PaySecond,
> PaidYr,
> PaidMo
> FROM dbo.PayTotals
> WHERE PaidYr = @.PaidYr
> GO
>|||The system variable @.@.rowcount will return the number of rows affected (in
this case inserted). Also, there is the variable @.@.error that contains <> 0
in the event of an error.
For example:
insert into ...
if @.@.rowcount > 0
begin
..
end
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:OdaU4pF8FHA.2616@.TK2MSFTNGP15.phx.gbl...
>I have a stored procedure (see below) that inserts records into a history
>table from a source table (PayTotals). After that is successful, I would
>like to run a delete statement "DELETE FROM dbo.PayTotals WHERE PaidYr =
>@.PaidYr" but only if the previous INSERT is successful. What would I need
>to add to the proc below to make that happen? Thanks.
> David
> CREATE PROCEDURE [mc_inshstPayTotals]
> (@.PaidYr [smallint])
> AS INSERT INTO dbo.hstPayTotals
> ([EmployerCaseNumber],
> [EmployerMax],
> [PayFirst],
> [PaySecond],
> [PaidYr],
> [PaidMo])
> SELECT
> EmployerCaseNumber,
> EmployerMax,
> PayFirst,
> PaySecond,
> PaidYr,
> PaidMo
> FROM dbo.PayTotals
> WHERE PaidYr = @.PaidYr
> GO
>|||The INSERT will always handle thousands of records, so I would think
that if the @.@.rowcount was > 0 then it worked, correct?
David
*** Sent via Developersdex http://www.examnotes.net ***|||JT,
If there IS an error, won't the @.@.rowcount = 0?
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***|||Yes, I would expect that to be the case.
Just because @.@.error = 0, it doesn't necessarily mean that 0 rows were
inserted, becuase the select query may return no rows or a trigger on
hstPayTotals may rollback the insert. Therefore, you will want to at least
check the status of @.@.rowcount.
"David" <daman@.lifetime.com> wrote in message
news:unZmE0F8FHA.1864@.TK2MSFTNGP12.phx.gbl...
> JT,
> If there IS an error, won't the @.@.rowcount = 0?
> Thanks.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||> The INSERT will always handle thousands of records, so I would think
> that if the @.@.rowcount was > 0 then it worked, correct?
Well, again, just because you inserted a bunch of rows doesn't necessarily
mean they were the right ones. :-)
But yes, if all you care about is that at least one row was inserted, then
checking for a positive @.@.ROWCOUNT should suffice.|||>I have a stored procedure (see below) that inserts records into a history
>table from a source table (PayTotals). After that is successful, I would
>like to run a delete statement "DELETE FROM dbo.PayTotals WHERE PaidYr =
>@.PaidYr" but only if the previous INSERT is successful. What would I need
>to add to the proc below to make that happen? Thanks.
I must be missing something in the midst of all this discussion of
@.@.ROWCOUNT and @.@.ERROR. Perhaps I'm being retarded today, but why not just
do this:
BEGIN TRANSACTION
INSERT stuff
DELETE stuff
COMMIT TRANSACTION
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%23kJ4e7G8FHA.2616@.TK2MSFTNGP15.phx.gbl...
> I must be missing something in the midst of all this discussion of
> @.@.ROWCOUNT and @.@.ERROR. Perhaps I'm being retarded today, but why not
> just do this:
> BEGIN TRANSACTION
> INSERT stuff
> DELETE stuff
> COMMIT TRANSACTION
It depends what you want to do if the INSERT fails.
Do you still want to DELETE?
And if the INSERT succedes and the DELETE fails.
Do you still want to Commit the INSERT.
Normally you don't.
So after each one, check @.@.rowcount and @.@.errors and Commit only if both
succede.
Else, Rollback.