Running an SSRS 2005 report against a stored procedure with multiple

Hi, I'm looking to confirm what I suspect - that if I have a single
stored procedure that returns 2 results, I can only "design" against the
first result in the designer.
I realize that I can create multiple data sources, and call the stored
proc twice with a parameter to indicate which of the 2 results to return
- I just want to verify that you CAN'T create a single data source that
calls a stored proc that returns two (or more) results, and then try to
design the report against both results.
Thanks,
KevinRS can only use one dataset from a stored procedure.
"Kevin" wrote:
> Hi, I'm looking to confirm what I suspect - that if I have a single
> stored procedure that returns 2 results, I can only "design" against the
> first result in the designer.
> I realize that I can create multiple data sources, and call the stored
> proc twice with a parameter to indicate which of the 2 results to return
> - I just want to verify that you CAN'T create a single data source that
> calls a stored proc that returns two (or more) results, and then try to
> design the report against both results.
> Thanks,
> Kevin
>

Running ALTER on stored procedure removes delay

Every so often we have problems running several stored procedures on our database. Specifically, our applications begin timing out. We have been able to fix the problem everytime by running an ALTER on our stored procedures. Note that the we don't change anything in the tsql, we just run ALTER. Example:
If we created the SP using:
CREATE PROCEDURE [dbo].[Test]
AS
SELECT *
FROM TABLES

running

ALTER PROCEDURE [dbo].[Test]
AS
SELECT *
FROM TABLES

will fix our timeout problem. What could running the ALTER do which which could get rid of timeout issues?

It's recompiling, or marking the procedure for re-compile.

Are there a lot of data changes on the underlying tables?

Are you keeping the stats of the tables current?

|||

hello,

when you execute a stored procedure you must write a particular thing i.e.

you must give a command in your query window for using your database.

for example,

if the database name is XYZ the

use xyz

exec test

|||

I figured it was recompiling it, but how would that effect the speed of the procedure?

The underlying tables are altered quite a bit. How do I know if the stats are being updated?

|||

If you have large amounts of data modifications then the execution plan for the SP can become out-dated.

When you recompile it a new plan is constructed based on the current information.

You can view the last update date using this:

Code Snippet

SELECT'Index Name'= i.name,'Statistics Date'=STATS_DATE(i.object_id, i.index_id)

FROMsys.objects o

JOINsys.indexes i ON o.name ='Address'AND o.object_id= i.object_id;

And you can refresh, and keep the statistics current, using the UPDATE STATISTICS statement.

BOL can give you the parameters and options.

|||How do you do that in SQL 2000? We're in the midsts of updating, but we're not quite there yet.|||

Code Snippet

SELECT'Index Name'= i.name,'Statistics Date'=STATS_DATE(i.id, i.indid)

FROMsysobjects o

JOINsysindexes i ON o.name ='Address'AND o.id = i.id;

|||If you are making major changes to the underlying structure on a continuing basis, you need to set the "RECOMPILE" option so the execution plan can be updated. Use this:

CREATE PROC name WITH RECOMPILE
AS
....

|||What do you mean by "changes to the underlying structure"? We're not changing the number of columns or adding and removing tables or indexes. Just continually adding data.|||Changing data changes the statistics on the indexes and could cause the stored execution plan to pick a "non-optimal" index. Either doing what you have been doing with "ALTER PROC" or using the "WITH RECOMPILE" will cause it to recreate the execution plan every time the proc is run, and should pick and optimal path.
sql

Running a system stored procedure

I write applications in VS.net that run against SQL Server 2000. I am
trying to write a stored procedure that accesses a database in different SQL
server than the one hosting the data. I entered into the FROM statement
"<OtherServerName>.<DatabaseName>.MyTable" and I get back a message that it
can't find the server "OtherServerName" and tells me to run the store
procedure "sp_addlinkedserver" to correct this. My question is:
Where do I find this sp? Do I run it from the VS.net environment, at the
console of one of the servers? What is the procedure to run it?
Any help is greatly apperceived.
Hi,
You have to create a linked server from local server when you want to access
a database resides in another SQL server. So you have to login into SQL
server
using Query Analyzer or Enterprise Manager and create a linked server.
See sp_addlinkedserver and sp_addlinkedsrvlogin in books online or use the
enterprise manager -- conenct to source sql server --
Expand the security -- select linked server and create a new connection to
new server.
Thanks
Hari
MCDBA
"Greg Smith" <gjs@.umn.edu> wrote in message
news:eJ0ZWcYdEHA.384@.TK2MSFTNGP10.phx.gbl...
> I write applications in VS.net that run against SQL Server 2000. I am
> trying to write a stored procedure that accesses a database in different
SQL
> server than the one hosting the data. I entered into the FROM statement
> "<OtherServerName>.<DatabaseName>.MyTable" and I get back a message that
it
> can't find the server "OtherServerName" and tells me to run the store
> procedure "sp_addlinkedserver" to correct this. My question is:
> Where do I find this sp? Do I run it from the VS.net environment, at the
> console of one of the servers? What is the procedure to run it?
> Any help is greatly apperceived.
>
sql

Running a Stored Procedure without passing parameters

HI all,

I'd like to run a simple stored procedure on the Event of a button click, for which I don't need to pass any parameters, I am aware how to run a Stored Procedure with parameters, but I don't know how without, any help would be appreciated please.

thanks.

The code would be similar to what you have if you had parameters. Just chop off the part of the code where you add the parameter.|||

thanks for that, however I can't use the normal way I used with parameters :

SQLDataSource1.InsertParameters("Till_Name").DefaultValue = tillname

As for the above I've used the InsertParameters method of my SQLDataSource1 object, which requires a parameter, although all I want to do is trigger a Stored Procedure. Do you follow?

|||

Have a look at the link

http://msdn2.microsoft.com/en-us/library/w1kdt8w2.aspx

Quoting the relevant portion from the link

"

If the database you are working with supports stored procedures, you can set theSelectCommand property to the name of the stored procedure and theSelectCommandType propertyStoredProcedure to indicate that theSelectCommand property refers to a stored procedure. The following example demonstrates a simple stored procedure that you can create in SQL Server:

CREATE PROCEDURE sp_GetAllEmployees AS
SELECT * FROM Employees;
GO

To configure theSqlDataSource to use this stored procedure, set theSelectCommand text to "sp_GetAllEmployees" and theSelectCommandType property toStoredProcedure.

Most stored procedures use parameters. For more information about using stored procedures with parameters, seeUsing Parameters with the SqlDataSource Control.

At run time,SqlDataSource control submits the text in theSelectCommand property to the database, and the database returns the result of the query or stored procedure to theSqlDataSource control. Any Web controls that are bound to the data source control display the result set on your ASP.NET page.

"

|||

thanks for that ambarsihg, I have my stored procedure as a SelectCommand, however my problem is the syntax of running the SelectCommand from a button press for example.

e.g.

SqlDataSource2.SelectCommandType = SqlDataSourceCommandType.StoredProcedureSqlDataSource2.SelectCommand()

This doesnt work, so how do I do it please?|||Dim myConnectionAsNew SqlConnection([your connection])
Dim myCommandAsNew SqlCommand("[your stored procedure]", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
myCommand.ExecuteNonQuery()

ExecuteNonQuery executes the Stored Procedure

|||Thanks very much, much head scratching avoided!

Running a Stored procedure or a query

Hi Guys
I woud like to run either a stored procedure before runing the actual
report. The stored procedure will create and update a table and on the second
run the report query will run and produce the report.
Is it possible to achieve this?
Regards
Tofigh> I woud like to run either a stored procedure before runing the actual
> report. The stored procedure will create and update a table and on the
second
> run the report query will run and produce the report.
> Is it possible to achieve this?
I guess your report is scheduled. Just edit the scheduled job and insert a
step that executes the stored procedures before the step that causes the
report to run.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks for the reply, the report is not scheduled. the users will run the
report on demand.
Regards
Tofigh
"Dejan Sarka" wrote:
> > I woud like to run either a stored procedure before runing the actual
> > report. The stored procedure will create and update a table and on the
> second
> > run the report query will run and produce the report.
> >
> > Is it possible to achieve this?
> I guess your report is scheduled. Just edit the scheduled job and insert a
> step that executes the stored procedures before the step that causes the
> report to run.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>|||Then maybe you can use two SPs - one outer that is called from the report
and returns the data, and inside it you first make a call to the second
procedure that prepares the tables.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"TA" <TA@.discussions.microsoft.com> wrote in message
news:9C1789C5-6FF7-469B-888A-6B0E7547F2BA@.microsoft.com...
> Thanks for the reply, the report is not scheduled. the users will run the
> report on demand.
> Regards
> Tofigh
> "Dejan Sarka" wrote:
> > > I woud like to run either a stored procedure before runing the actual
> > > report. The stored procedure will create and update a table and on the
> > second
> > > run the report query will run and produce the report.
> > >
> > > Is it possible to achieve this?
> >
> > I guess your report is scheduled. Just edit the scheduled job and insert
a
> > step that executes the stored procedures before the step that causes the
> > report to run.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > Associate Mentor
> > www.SolidQualityLearning.com
> >
> >
> >

Running a stored procedure in Query Analyzer vs ADO - Execution Di

I am running SQL Server 2000 SP3. I have a stored procedure that I call from
a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler and see that the
stored proc executes in 22 seconds. I take the stored procedure call straight
out of profiler and paste it into SQL Query Analyzer and run the stored
procedure. It executes in Query Analyzer in 4 seconds. When I look at the
execution plan, Query Analyzer uses a different plan (more efficient) than
the plan used when the stored procedure is executed through ADO. How is this
possible? What am I doing wrong with ADO? How come the stored procedure will
not execute in 4 seconds when I call it through ADO? I can consistently
duplicate this.
Todd.Danner@.wachovia.com wrote:
> I am running SQL Server 2000 SP3. I have a stored procedure that I
> call from a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler
> and see that the stored proc executes in 22 seconds. I take the
> stored procedure call straight out of profiler and paste it into SQL
> Query Analyzer and run the stored procedure. It executes in Query
> Analyzer in 4 seconds. When I look at the execution plan, Query
> Analyzer uses a different plan (more efficient) than the plan used
> when the stored procedure is executed through ADO. How is this
> possible? What am I doing wrong with ADO? How come the stored
> procedure will not execute in 4 seconds when I call it through ADO? I
> can consistently duplicate this.
How are you executing the procedure from ADO? Post the code you are
testing on ADO (all relevant code) and the SQL you use in QA..
David G.

Running a stored procedure in Query Analyzer vs ADO - Execution Di

I am running SQL Server 2000 SP3. I have a stored procedure that I call from
a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler and see that the
stored proc executes in 22 seconds. I take the stored procedure call straigh
t
out of profiler and paste it into SQL Query Analyzer and run the stored
procedure. It executes in Query Analyzer in 4 seconds. When I look at the
execution plan, Query Analyzer uses a different plan (more efficient) than
the plan used when the stored procedure is executed through ADO. How is this
possible? What am I doing wrong with ADO? How come the stored procedure will
not execute in 4 seconds when I call it through ADO? I can consistently
duplicate this.Todd.Danner@.wachovia.com wrote:
> I am running SQL Server 2000 SP3. I have a stored procedure that I
> call from a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler
> and see that the stored proc executes in 22 seconds. I take the
> stored procedure call straight out of profiler and paste it into SQL
> Query Analyzer and run the stored procedure. It executes in Query
> Analyzer in 4 seconds. When I look at the execution plan, Query
> Analyzer uses a different plan (more efficient) than the plan used
> when the stored procedure is executed through ADO. How is this
> possible? What am I doing wrong with ADO? How come the stored
> procedure will not execute in 4 seconds when I call it through ADO? I
> can consistently duplicate this.
How are you executing the procedure from ADO? Post the code you are
testing on ADO (all relevant code) and the SQL you use in QA..
David G.

Running a stored procedure from VBScript

Googled around and found this for VBScript code to run a SP:
dim conn
set conn=createobject("adodb.connection")
conn.connectionstring="provider=sqloledb.1;persist security info=false;user
id=xx;password=xx;initial catalog=xx;data source=xx"
conn.open
conn.execute "test_sp"
conn.close
set conn=nothing
test_sp:
RAISERROR ('Test message',20,1) WITH LOG
When I run the VBScript, it basically outputs 'test message' as an error
from what I can tell.
I want the VBScript to run quietly, and not output anything.
How can this be done or is there a better VBScript script that I can use?
I'm hoping I can also catch errors with the same script if the SP fails...
Thanks,
MarcoObviously, your SP raises an error and pass it to the calling process (your
VBScript's ADODB.Connection.Execute() call). You simply need to handle the
error in your VBScript code (unfortunately, VBScript has poor exception
handling method):
Public Sub DoSQLStuff()
...
On Error Resume Next
cn.Open
If Err.Number<>0 Then
'Do something on error, if you want to
Exit Sub
End If
cn.Execute "test_sp"
If Err.Number<>0 Then
'Do something of your choice
Exit Sub
End If
...
End Sub
The point is, after each line of code, if the code could cause runtime
error, you need to check Err object to see if there is error or not.
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message
news:OM2G86oqGHA.4760@.TK2MSFTNGP05.phx.gbl...
> Googled around and found this for VBScript code to run a SP:
> dim conn
> set conn=createobject("adodb.connection")
> conn.connectionstring="provider=sqloledb.1;persist security
> info=false;user
> id=xx;password=xx;initial catalog=xx;data source=xx"
> conn.open
> conn.execute "test_sp"
> conn.close
> set conn=nothing
> test_sp:
> RAISERROR ('Test message',20,1) WITH LOG
> When I run the VBScript, it basically outputs 'test message' as an error
> from what I can tell.
> I want the VBScript to run quietly, and not output anything.
> How can this be done or is there a better VBScript script that I can use?
> I'm hoping I can also catch errors with the same script if the SP fails...
> Thanks,
> Marco
>sql

Running a stored procedure from VBScript

Googled around and found this for VBScript code to run a SP:
dim conn
set conn=createobject("adodb.connection")
conn.connectionstring="provider=sqloledb.1;persist security info=false;user
id=xx;password=xx;initial catalog=xx;data source=xx"
conn.open
conn.execute "test_sp"
conn.close
set conn=nothing
test_sp:
RAISERROR ('Test message',20,1) WITH LOG
When I run the VBScript, it basically outputs 'test message' as an error
from what I can tell.
I want the VBScript to run quietly, and not output anything.
How can this be done or is there a better VBScript script that I can use?
I'm hoping I can also catch errors with the same script if the SP fails...
Thanks,
MarcoObviously, your SP raises an error and pass it to the calling process (your
VBScript's ADODB.Connection.Execute() call). You simply need to handle the
error in your VBScript code (unfortunately, VBScript has poor exception
handling method):
Public Sub DoSQLStuff()
...
On Error Resume Next
cn.Open
If Err.Number<>0 Then
'Do something on error, if you want to
Exit Sub
End If
cn.Execute "test_sp"
If Err.Number<>0 Then
'Do something of your choice
Exit Sub
End If
...
End Sub
The point is, after each line of code, if the code could cause runtime
error, you need to check Err object to see if there is error or not.
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message
news:OM2G86oqGHA.4760@.TK2MSFTNGP05.phx.gbl...
> Googled around and found this for VBScript code to run a SP:
> dim conn
> set conn=createobject("adodb.connection")
> conn.connectionstring="provider=sqloledb.1;persist security
> info=false;user
> id=xx;password=xx;initial catalog=xx;data source=xx"
> conn.open
> conn.execute "test_sp"
> conn.close
> set conn=nothing
> test_sp:
> RAISERROR ('Test message',20,1) WITH LOG
> When I run the VBScript, it basically outputs 'test message' as an error
> from what I can tell.
> I want the VBScript to run quietly, and not output anything.
> How can this be done or is there a better VBScript script that I can use?
> I'm hoping I can also catch errors with the same script if the SP fails...
> Thanks,
> Marco
>

running a stored procedure from one server to access jobs on another

I have two servers linked. I can run an SP on one server to access jobs on
the other server. I cannot make this work the other way around?
this works from server1
server2.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
Logs',@.enabled =1
This fails with the error msg Could not connect to server 'server1' because
'sa' is not defined as a remote login at the server.
server1.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
Logs',@.enabled =1Did you map logins from server 2 to server 1? Also, it seems you login as sa on server 2, I never
recommend anyone logging in as sa.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Phillips" <andy.phillips@.callatg.com> wrote in message
news:%23eH52BB2EHA.3820@.TK2MSFTNGP11.phx.gbl...
> I have two servers linked. I can run an SP on one server to access jobs on
> the other server. I cannot make this work the other way around?
> this works from server1
> server2.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
> Logs',@.enabled =1
> This fails with the error msg Could not connect to server 'server1' because
> 'sa' is not defined as a remote login at the server.
> server1.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
> Logs',@.enabled =1
>|||Do you have a linked server set up on both servers? A linked server only
works in one direction. You need a linked server set up on both servers to be
able to go both ways.
"Andy Phillips" wrote:
> I have two servers linked. I can run an SP on one server to access jobs on
> the other server. I cannot make this work the other way around?
> this works from server1
> server2.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
> Logs',@.enabled =1
> This fails with the error msg Could not connect to server 'server1' because
> 'sa' is not defined as a remote login at the server.
> server1.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
> Logs',@.enabled =1
>
>|||All I did was link the servers.Please explain what I should do.
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Obs8SCE2EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Did you map logins from server 2 to server 1? Also, it seems you login as
sa on server 2, I never
> recommend anyone logging in as sa.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andy Phillips" <andy.phillips@.callatg.com> wrote in message
> news:%23eH52BB2EHA.3820@.TK2MSFTNGP11.phx.gbl...
> > I have two servers linked. I can run an SP on one server to access jobs
on
> > the other server. I cannot make this work the other way around?
> >
> > this works from server1
> > server2.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
> > Logs',@.enabled =1
> >
> > This fails with the error msg Could not connect to server 'server1'
because
> > 'sa' is not defined as a remote login at the server.
> >
> > server1.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
> > Logs',@.enabled =1
> >
> >
>|||If you use EM, right.click the linked server, Properties, and the security tab. Map the login there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Phillips" <andy.phillips@.callatg.com> wrote in message
news:%23ogJfYJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> All I did was link the servers.Please explain what I should do.
> Thanks
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Obs8SCE2EHA.2568@.TK2MSFTNGP11.phx.gbl...
> > Did you map logins from server 2 to server 1? Also, it seems you login as
> sa on server 2, I never
> > recommend anyone logging in as sa.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Andy Phillips" <andy.phillips@.callatg.com> wrote in message
> > news:%23eH52BB2EHA.3820@.TK2MSFTNGP11.phx.gbl...
> > > I have two servers linked. I can run an SP on one server to access jobs
> on
> > > the other server. I cannot make this work the other way around?
> > >
> > > this works from server1
> > > server2.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
> > > Logs',@.enabled =1
> > >
> > > This fails with the error msg Could not connect to server 'server1'
> because
> > > 'sa' is not defined as a remote login at the server.
> > >
> > > server1.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
> > > Logs',@.enabled =1
> > >
> > >
> >
> >
>|||I cannot link the other server. It sys it already exists although it shows
no items in the list?
"Robert Davis" <RobertDavis@.discussions.microsoft.com> wrote in message
news:59EB1C48-4211-4D42-BC9E-79D354C5C3B6@.microsoft.com...
> Do you have a linked server set up on both servers? A linked server only
> works in one direction. You need a linked server set up on both servers to
be
> able to go both ways.
> "Andy Phillips" wrote:
> > I have two servers linked. I can run an SP on one server to access jobs
on
> > the other server. I cannot make this work the other way around?
> >
> > this works from server1
> > server2.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
> > Logs',@.enabled =1
> >
> > This fails with the error msg Could not connect to server 'server1'
because
> > 'sa' is not defined as a remote login at the server.
> >
> > server1.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
> > Logs',@.enabled =1
> >
> >
> >|||It'sa fixed. Thanks Robert. I had to remove remote servers and linked
servers and then relink them. I have links each way now and all is working
top. Thanks
"Andy Phillips" <andy.phillips@.callatg.com> wrote in message
news:uFGWTEC3EHA.3932@.TK2MSFTNGP12.phx.gbl...
> I cannot link the other server. It sys it already exists although it shows
> no items in the list?
>
> "Robert Davis" <RobertDavis@.discussions.microsoft.com> wrote in message
> news:59EB1C48-4211-4D42-BC9E-79D354C5C3B6@.microsoft.com...
> > Do you have a linked server set up on both servers? A linked server only
> > works in one direction. You need a linked server set up on both servers
to
> be
> > able to go both ways.
> >
> > "Andy Phillips" wrote:
> >
> > > I have two servers linked. I can run an SP on one server to access
jobs
> on
> > > the other server. I cannot make this work the other way around?
> > >
> > > this works from server1
> > > server2.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB
Trans
> > > Logs',@.enabled =1
> > >
> > > This fails with the error msg Could not connect to server 'server1'
> because
> > > 'sa' is not defined as a remote login at the server.
> > >
> > > server1.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB
Trans
> > > Logs',@.enabled =1
> > >
> > >
> > >
>

running a stored procedure from one server to access jobs on another

I have two servers linked. I can run an SP on one server to access jobs on
the other server. I cannot make this work the other way around?
this works from server1
server2.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
Logs',@.enabled =1
This fails with the error msg Could not connect to server 'server1' because
'sa' is not defined as a remote login at the server.
server1.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
Logs',@.enabled =1
Did you map logins from server 2 to server 1? Also, it seems you login as sa on server 2, I never
recommend anyone logging in as sa.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Phillips" <andy.phillips@.callatg.com> wrote in message
news:%23eH52BB2EHA.3820@.TK2MSFTNGP11.phx.gbl...
> I have two servers linked. I can run an SP on one server to access jobs on
> the other server. I cannot make this work the other way around?
> this works from server1
> server2.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
> Logs',@.enabled =1
> This fails with the error msg Could not connect to server 'server1' because
> 'sa' is not defined as a remote login at the server.
> server1.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
> Logs',@.enabled =1
>
|||All I did was link the servers.Please explain what I should do.
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Obs8SCE2EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Did you map logins from server 2 to server 1? Also, it seems you login as
sa on server 2, I never[vbcol=seagreen]
> recommend anyone logging in as sa.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andy Phillips" <andy.phillips@.callatg.com> wrote in message
> news:%23eH52BB2EHA.3820@.TK2MSFTNGP11.phx.gbl...
on[vbcol=seagreen]
because
>
|||If you use EM, right.click the linked server, Properties, and the security tab. Map the login there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Phillips" <andy.phillips@.callatg.com> wrote in message
news:%23ogJfYJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> All I did was link the servers.Please explain what I should do.
> Thanks
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Obs8SCE2EHA.2568@.TK2MSFTNGP11.phx.gbl...
> sa on server 2, I never
> on
> because
>

running a stored procedure from one server to access jobs on another

I have two servers linked. I can run an SP on one server to access jobs on
the other server. I cannot make this work the other way around?
this works from server1
server2.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
Logs',@.enabled =1
This fails with the error msg Could not connect to server 'server1' because
'sa' is not defined as a remote login at the server.
server1.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
Logs',@.enabled =1Did you map logins from server 2 to server 1? Also, it seems you login as sa
on server 2, I never
recommend anyone logging in as sa.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Phillips" <andy.phillips@.callatg.com> wrote in message
news:%23eH52BB2EHA.3820@.TK2MSFTNGP11.phx.gbl...
> I have two servers linked. I can run an SP on one server to access jobs o
n
> the other server. I cannot make this work the other way around?
> this works from server1
> server2.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
> Logs',@.enabled =1
> This fails with the error msg Could not connect to server 'server1' becaus
e
> 'sa' is not defined as a remote login at the server.
> server1.msdb..sp_update_job @.job_name = 'Copy And Restore FFICDB Trans
> Logs',@.enabled =1
>|||All I did was link the servers.Please explain what I should do.
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Obs8SCE2EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Did you map logins from server 2 to server 1? Also, it seems you login as
sa on server 2, I never
> recommend anyone logging in as sa.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andy Phillips" <andy.phillips@.callatg.com> wrote in message
> news:%23eH52BB2EHA.3820@.TK2MSFTNGP11.phx.gbl...
on[vbcol=seagreen]
because[vbcol=seagreen]
>|||If you use EM, right.click the linked server, Properties, and the security t
ab. Map the login there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Phillips" <andy.phillips@.callatg.com> wrote in message
news:%23ogJfYJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> All I did was link the servers.Please explain what I should do.
> Thanks
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:Obs8SCE2EHA.2568@.TK2MSFTNGP11.phx.gbl...
> sa on server 2, I never
> on
> because
>

Running A Stored Procedure From Enterprise manager?

How do I run a stored procedure from within the Enterprise Manager?
ThanksWell you don't. You can use Query Analyzer though. click on the tools menu
and choose Query analyzer.
Andrew J. Kelly SQL MVP
"Chris Moore" <chris@.dblayoutdotcom> wrote in message
news:Xns97D3A5559BC6cabubba@.207.46.248.16...
> How do I run a stored procedure from within the Enterprise Manager?
> Thanks|||Ok. That is already what I am doing.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#Cz3liEhGHA.4304@.TK2MSFTNGP05.phx.gbl:

> Well you don't. You can use Query Analyzer though. click on the
> tools menu and choose Query analyzer.
>sql

Running a stored procedure from an other

dear all
i need to execute a stored procedure in sql server from another stored
procedure.
this mean that my application calls a stored procedure but i need when i
call this stored proceure to run an other one.
is that possible and can anybody help me to acheive this task'
Thank you.GMK wrote:
> dear all
> i need to execute a stored procedure in sql server from another stored
> procedure.
> this mean that my application calls a stored procedure but i need
> when i call this stored proceure to run an other one.
> is that possible and can anybody help me to acheive this task'
> Thank you.
Sure it's possible. Just execute the other proc:
From Proc1
Exec dbo.Proc2 @.param1, @.param2
If you need a return value, then use:
Exec @.iRet = dbo.proc2 @.param1, @.param2
David Gugick
Imceda Software
www.imceda.com|||exec sp_name @.ParameterName = @.ParameterValue, ...

> dear all
> i need to execute a stored procedure in sql server from another stored
> procedure.
> this mean that my application calls a stored procedure but i need when
> i
> call this stored proceure to run an other one.
> is that possible and can anybody help me to acheive this task'
> Thank you.
>

Running a Stored Procedure from ADO

Hi,
From one of my forms in an Access Project I want to run a Stored Procedure on the "After Insert" event of the form. The stored procedure is an Insert SQL Statement with two variables.
What is the best way to pass the variables to the stored procedure and run it.

Should I run a SQL Statement like

srtSQL="EXEC SP_Insert (@.Var1=Var1,@.Var2=Var2)

Or there is a better way to do it with ADO objects?

Thanks
==============================================

I found two solutions. Which one do you think is the better one?
The stored procedure on the Server:

CREATE PROCEDURE SP_Insert_Into_CompanyAdrsContact
@.CompAdrsID int,
@.ContactID int
AS
Insert into tblCompanyAdrsContact (CompAdrsID,ContactID) Values (@.CompAdrsID,@.ContactID)
GO

On the Access form:

1) Using a SQL stament dynamically to pass parameters from a form:

strSQL = "Exec SP_Insert_Into_CompanyAdrsContact " & Me!CompAdrsID
strSQL = strSQL & "," & Me!ContactID
DoCmd.RunSQL (strSQL)

**********************************************
2) Using ADO objects:
Dim cmd As ADODB.Command
Dim prmContactID As ADODB.Parameter
Dim prmCompAdrsID As ADODB.Parameter

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "SP_Insert_Into_CompanyAdrsContact"

Set prmCompAdrsID = cmd.CreateParameter("@.CompAdrsID", adInteger, adParamInput)
Set prmContactID = cmd.CreateParameter("@.ContactID", adInteger, adParamInput)

cmd.Parameters.Append prmCompAdrsID
prmCompAdrsID.Value = Me![CompAdrsID]
cmd.Parameters.Append prmContactID
prmContactID.Value = Me![ContactID]

cmd.ExecuteIf you are going to call this proc many times, it's best to use command parameter. This will allow you to gain some performance because of cache. If it's just a one time thing, just execute the string is fine.

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 job dyanamically

Hi All,
I have stored procedure which accepts an input
parameter.
I want to execute this stored procedure using a job.
This job will be executed based on an alert. How do I
pass the input parameter dynamically.
I am elaborating by giving an example.
a) procTest accepts a parameter @.n INT
b) There is a job called jobTest which has a step
stpTest which executes procTest @.n
c) If alertA fires I want to execute procTest 1
If alertB fires I want to execute procTest 2
If alertC fires I want to execute procTest 3
I hope I am clear with my question. Is it possible to do
this, or do I need to write seperate jobs for each alert
and hard code the parameter for the stored procedure.
Thanks in advance
AnandGuess the SP could look in sysalerts to see the last one that fired -
but would probably need to hold info for each alert in case multple
alerts fired at the same time (just save the last count for each alert
and check it to see what to process).
A lot easier to just define multiple jobs.
Posted via http://dbforums.com

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

Running a DTS package from a stored procedure?

Is it possible to run a DTS package from a stored procedure?
If yes how would I go about doing this?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!You can use dtsrun and xp_cmdshell (probbly the easiest) or you can use the sp_oa SPs - example at
http://www.nigelrivett.net/sp_oacreateLoadDTSpackage.html
"Rob NA" wrote:
>
>
> Is it possible to run a DTS package from a stored procedure?
> If yes how would I go about doing this?
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
>