Executing Multiple Procs At One Go

Jan 12, 2004

Hi,

I would like to execute multiple stored procs by calling a single script say A. I want to A to execute on a nightly basis. The multiple stored procs are bulk inserts into separate tables. I want to be notified of the error if one among the many stored procs fail when I call A. But if one fails, I don't want A to fail. The remaining stored procs should execute. Does anyone have a script wriiten for A? I guess A is just not about -

EXEC SP1
EXEC SP2
EXEC SP3
EXEC SP4
EXEC SP5

Thanks

View 4 Replies


ADVERTISEMENT

Executing Extended Stored Procs???

Dec 3, 1999

I've written a very simple extended stored proc--little more than what VC++
6.0's ext stored proc wizard generates--set up the xp in the master database,
and eventually want to execute it from a trigger defined on a table in another database...

I've tested the xp in Query Analyzer. If the DB is set to 'master', the xp
runs (exec xp_InputTrigger), no problem. If I change databases and try to run the xp, I get:

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_InputTrigger'.

Keying "exec master.xp_InputTrigger", also fails: Could not find stored procedure 'master.xp_InputTrigger'.

I've checked permissions--all seem to be set right. Anyone have any insight
for me here? Many thanks,
mjo

View 1 Replies View Related

Problems In Executing SQL Or Stored Procs With ASP

Jan 30, 2007

Hi experts,

I m working with MS SQL Server 2000 with ASP for my application. There're some Stored Procedures created for the new functions but seems I can't run these new SPs with my ASP pages. When I load that ASP page, it shows error message that can't find my SP. I've execute the SP alone in SQL Enterprise Manager and it works.

When I work with the SPs, I can connect with the DB with Enterprise Manager only without administrator right. As my SPs I suppose they should work with ASP but I just worry would it be the problem with my right granted in SQL server?

There are some other SPs running fine created by dbo but for my SPs not by dbo. Would there be any differences? Does it mean my SPs need to be granted by dbo instead of my current role? I m sure if I use the same ASP page then running another existing SPs, it works really smooth.

I also tried to make a SQL statement in my ASP page (e.g. an insert statement) but it seems nothing can be inserted. I got really screwed up!!!

Thanks in advance!!
Manfred

View 3 Replies View Related

Wait Display While Executing Stored Procs

Aug 10, 2007

Hello! I am new poster and I hope I am posting this question in the right forum.

I have an application (winform written in c#) that has tons of interaction (lots of triggers and stored procs) with sql server (SQL Express, SQL 2000 & SQL Server 2005).

I want to monitor when a stored procedure starts executing and then display something until the execution ends. I was thinking of displaying a modal form or if not a non-modal form and just play around with the Z-order. I thought of utilizing the Query Notification service but I believe it is only available in SQL Server 2005. My guess is that a CLR will be the best way to go.

Also, is it possible to put this in a dll and then just add the dll to the application?

I would appreciate it if I could get some input/advice on this matter (or some sample codes if someone has done something similar).

Thanks a lot!

View 2 Replies View Related

How To Copy Multiple Stored Procs

May 29, 2008

I would like to copy the following 3 stored procedures from Database1 to database2 on the same server:

USP_UpdateDocRepository

USP_GetUA

USP_GetCN

Is there a copy command available via query window.

Thanks.

View 1 Replies View Related

Granting Permission On Multiple Stored Procs

May 25, 2007

Is there a better way of granting permission to a stored proc for a selected user other than (enterprise manager) select sp then accessing propertys then permissions, then user?

View 4 Replies View Related

Exporting Multiple Stored Procs To Separate Files

Jan 16, 2008

We just upgraded from SQL 2000 to 2005. Under 2000, I could export multiple stored procs to separate windows files.
Is there a way to do this under 2005 without exporting 1 proc at a time?

View 5 Replies View Related

Rolling Back Transactions For Multiple Stored PRocs

Oct 5, 2007

I have a quick question.

I need to execute some stored procedures in certain steps... all performing some inserts/updates

What i need is, a mechanism, by which i can roll back to the previous state if i encounter an exception (either in the app or SP)

so, if the my first two SP execute fine, perform their functionality like insert/update, and the third one fails...

how can i roll back to the initial state in ASP.NET.

would appreciate any info.
or redirection to the the location where i can look up some info on that.

AJ

View 1 Replies View Related

Executing Multiple Lines Of Sql Via C#

Jan 27, 2008

I am generating hundreds of lines of sql with a tool and wish to execute is in runtime. The sql consists of table creation, procedure creation, aswell as inserts and updates. When i try with SqlDataAdapter I get an error. Does anyone have a solution on what I can do?         

View 2 Replies View Related

Executing Multiple Scripts

Oct 22, 2007

Hey guys,
What is the best way to run multiple sql scripts against a database.
Vendor provided a DBupdate utility but it isn't working properly.....
any suggestions....
thanks,
jonathan

View 5 Replies View Related

Executing Multiple SSIS Packages

Apr 24, 2008

I am trying to execute around 3 SSIS packages using Execute package task by having all the 3 in one SSIS package,
I am getting the below error:
Error: Error 0xC001000A while preparing to load the package.
The specified package could not be loaded from the SQL Server database. .

Can I use Execute package task for this purpose??

Thanks!!

View 4 Replies View Related

Difficulty Executing Multiple SQL Cmds

Aug 27, 2007

I am probably missing something simple here but...

I have a CLR Stored Procedure where I am attempting to execute multiple SQL commands. Here is what happens.


Case One
1. Stored Procedure is invoked.
2. Create SQL Connection.
3. Open the connection.
4. Begin Transaction.
5. Create Command from Connection.
6. Set Command objects Transaction property to transaction created above.
7. Set Command Text Property to SQL Query.
8. Execute Query via ExecuteReader and get SQLReader.
9. Read query result set.
10. Close QueryReader.
11. Set Command Text Property to another SQL Query.
12. Execute Query

This is where I get one of two exceptions.
1. An Exception stating that I cannot have parallel transactions.
or
2. An Exception that I have an open SqlReader that must be closed.


Case Two
1. Stored Procedure is invoked.
2. Create SQL Connection.
3. Open the connection.
4. Begin Transaction.
5. Create Command from Connection.
6. Set Command objects Transaction property to transaction created above.
7. Set Command Text Property to SQL INSERT command.
8. Execute Insert command.
9. Set Command Text Property to SQL Query command.
10. Execute Query Command via ExecuteReader.

At this point the stored procedure appears to hang.


Could someone please enlighten me if I have missed something obvious while reading the manual, regarding what you can and cannot do?

Thanks in advance.

View 2 Replies View Related

Executing The Workflow For Multiple DBs At A Time

Aug 22, 2007

HI,
We are currently trying to import the Data from one DB to Multiple DBs.And these multiple DBs connection strings must be configurable. This I can do it by reading the connection strings from dtsconfig file and execute it for each DB.Is it possible to execute for all the DBs in parallel(Multithreaded).


Thanks,
Sabari.

View 6 Replies View Related

Executing Multiple Query At One Time

Feb 27, 2007

hi,

i am making a n application which in between deletes the multiple tables from the sql database.

for that i have written the following code:

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "delete from " + dbConstt.DBSchema + ".PicassoSelectivityLog where QTID=" + qtid;

cmd.ExecuteNonQuery();

cmd.CommandText = "delete from " + dbConstt.DBSchema + ".PicassoSelectivityLog where QTID=" + qtid;

cmd.ExecuteNonQuery();

in this way, many more tables are to be deleted.

is there any need to create the new SQLCommand object again and agin for each and every query. can iot be done like the given above or can there be some better method?



thanz in advance..

divya

View 1 Replies View Related

Executing Multiple Simultaneous Reports

Oct 15, 2007

Is there a way to get the Report Manager to allow a single user to
execute more than 1 report at a time? Currently, each user can only
have a single report being executed at a time. It looks like any
other reports are being queued and executed serially.

View 1 Replies View Related

Executing Multiple Query Returned

Feb 24, 2008

Hi,

I am executing following script which return more than 1 query/row as there are more than 1 log backup :
select @Query = 'RESTORE LOG ' + @dbName + ' FROM DISK=' + '''' +@path+''''+ ' WITH' + ' NORECOVERY, FILE= ' + convert(varchar(10),Position) from #temp where BackupName = 'Log_Backup' and BackupType = 2 and Position> @Diff_Position EXEC(@QUERY)

[Above script restores database with latest Differential backup file and latest log files. ]
Now I want to execute this, but @Query don't work for more than one row returned . Can you please suggest me how to execute such query?



Thanks,
Dipak.

View 1 Replies View Related

Executing Multiple SSIS Packages

Apr 24, 2008

I am trying to execute around 3 SSIS packages using Execute package task by having all the 3 in one SSIS package,
I am getting the below error:
Error: Error 0xC001000A while preparing to load the package.
The specified package could not be loaded from the SQL Server database. .
Then I did the following:
1. Right click on MSDB
2. Import package
Inside Import package window:
Package location: SQL Server
Server: servername
authentication: windows authen
Package path: \serverC$folder1SSIS Packagesfolder2
Import package as:
Package name: package.dtsx
Below is the error I am getting:
TITLE: Import Package
------------------------------
Cannot find folder "\serverC$folder1SSIS Packagesfolder2".
Can I use Execute package task for this purpose??
Thanks!!

View 12 Replies View Related

Executing Multiple Jobs / Local Packeges At Once

Jan 3, 2005

Hi all,

Is there any way so that we can run/ execute multiple jobs or local packeges at once. Can we create any sql script which will execute multiple jobs/packeges at once. please advise

View 3 Replies View Related

Isql -- Executing Multiple Input Scripts

Jul 20, 2005

Hello!We have a set of individual .SQL scripts which we would like toexecute against a MS SQL Server 2000. Is there any way to have ISQLutility (or any other means) to execute all of them without having toestablish a separate database connection for each script:isql -Ux -Py -Ss -i script1.sqlisql -Ux -Py -Ss -i script2.sqlisql -Ux -Py -Ss -i script3.sql.....isql -Ux -Py -Ss -i scriptN.sqlAll scripts are in the same location, which could be made visible toby the SQL Server itself (so either a client-based or a server-basedinvokation is ok)TIA

View 6 Replies View Related

Executing Clustering Models On Multiple Threads

Mar 3, 2008

Hi All

I have been asked by developers if there is any advantage in processing multiple clustering models simultaneously by using AMO and multiple threads as against processing one after another.

I have limited experience with Analysis Services but based on my reading I don't see this method providing any advantage.

Does anyone have any recommendations or advice? The system Enterprise Edition running on an x86 Server with 2 dual core processors and 4GB of RAM. Would the answer alter if the server running x64 version of SQL Server and Windows.

Thanks

Nadreck

View 3 Replies View Related

OLE DB Source Executing SQL Command Multiple Times

Jan 2, 2008



Happy new year everyone!!!


Ok, now for my not so happy new year problem ... here's the scenario:

I have a simple task of exporting data retrieved by a parameterized stored procedure to a delimited text file ... task done in 30 mins max using dataflow task from OLE DB Source with SQL Command to Text File Destination ... thank you very much SSIS!

But the problem is the OLE DB Source executed the SP MULTIPLE TIMES (3 to be exact)
Actually configuration is:

1. OLE DB Source
2. SQL Command access mode
3. The following command written in the command text box:

SET FMTONLY OFF;
exec sp_SampleSP @param1=?, @param2=?
4. Configured parameter mapping.

The stored procedure logs to a database table everytime it is executed to report how many rows were selected (please don't crucify me with this method, i'm re-using procs from old system). The log shows that during execution of this package the SP was executed on 3 times.

Help anyone? Please ...

View 5 Replies View Related

Executing Multiple Instances Of SSIS Package In Parallel

Dec 28, 2007

Hi,

Can we execute multiple instances of the same SSIS package simultaneously??
If yes, how?
If no, what is the work-around to simulate such a functionality?


Thanks in advance.

Regards,
B@ns

View 9 Replies View Related

Best Method For Running/Executing Multiple DTSX Packages?

May 13, 2008

Hello all -

Based on advice from this board, I am setting up our newly-converted DTSX packages (sql server 2000 (DTS) to 2005 (SSIS) package conversion) to utilize package configurations to make it easier to run them in multiple environments (dev, testing, production, etc...). This seems to be working just fine, but I now have another question.

We have approximately 12 DTSX packages which need to be executed in a specific sequential order. I was wondering what the best / most widely accepted method is to allow for running them as an automated process. If you double click on each DTSX file individually, the "Execute Package Utility" pops up, and you can run the package. I'd like to know the best way for us to be able to run each package, one after the other (each one waiting until the previous one completes before it begins), until they have all processed. It would be nice if it did not rely on SQL Server being installed on the machine on which it is being run...

thanks for all the fantastic support on this site

View 6 Replies View Related

Executing Sql Code From Text Field Or From Multiple Varchar(8000) Rows In A Table

Jul 20, 2005

Does anyone know of a way to execute sql code from a dynamically builttext field?Before beginning, let me state that I know this db architecture isbuilt solely for frustration and I hope to make it better soon.Unfortunately, there is never a non-crucial time in which we can do anupgrade, so we are stuck for now.Point 1:There are multiple tables: students, courses, cross-referencestudent/courses, teachers, cross-reference teacher/courses, andothers.Point 2:Yearly archiving is done by appending a 2 digit year suffix to thetable and rebuilding the table structure for the current year. Thatis, each of the tables named above has an archive table for 1999,2000, 2001, etc. This leads to many tables and a big mess whenunioning them.Point 3:A history report is run by building a big query that unions each unionof tables (students, courses, etc) by year. This query has grown toobig for a varchar(8000) field. Actually, it's too big for 2 of them.Point 4:I don't want to write code to maintain any more varchar(8000) fieldsfor this query. It should be much more easily handled with atemporary table holding each bit of yearly archive data. I have builtthis and it works fine. I have also figured out how to pull the rowsfrom that table, concatenate them, and insert the resulting lump intoa text field in another table.Point 5:I haven't figured out how to grab the 5 or so records from that tableand execute them on their own. I figured I could grab them, put theminto a text field that's big enough to hold the whole query and thenselect and execute that text field. I've had no luck with that and Ihaven't had any luck finding any references that might help me withthis problem. I keep thinking of nesting execute() calls, but thatdoesn't work.I'm open to questions, potential solutions, hints about different wayto approach the problem, anything.Many thanks in advance,Rick Caborn

View 7 Replies View Related

SQL Server Admin 2014 :: Logon Trigger Executing Multiple Times For Single Connection?

Jan 30, 2015

I am trying to create a logon trigger. As I am testing this, I discovered that each time I do a connection, I get 19 rows, inserted into my audit table. I ran profiler, and I see it is going through the logon trigger multiple times, for a single connection. So, what am I doing wrong? The code is fairly simplistic, and the profiler doesn't give a clue, as to what is going on. When I look at the output, I see the spid for the first couple of connections are different, then a spid, that is different from those 2 is in the next 17 rows. But, when I do an sp_who2, that spid does not exist.

This issue was noticed on a 2012 version, that I was first testing on, then had the same issue on a 2008 R2. I am currently testing on a 2014 version, that is doing the same thing. Is the logon trigger itself, firing, and causing this?

I also tried using the After Logon option, and got the same issue.

Here is the code:

CREATE TRIGGER LogonAuditTrigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @Body NVARCHAR(2000),

[code]....

View 0 Replies View Related

Stored Procs: Is There A Way To Divide Up (or Namespace) Groups Of Stored Procs

Jan 15, 2008

Is there a way to namespace groups of stored procs to reduce confusion when using them?

For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.

View 1 Replies View Related

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Qu

Jan 24, 2008



Hi,

I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window

Please see the image through this url http://kyxao.net/127/ExecutionProblem.png


Any ideas for this issue?

Thanks a lot

View 1 Replies View Related

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window

Jan 23, 2008

Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack 

View 2 Replies View Related

Different Results When Executing From .NET Component Compare To Executing From SQL Management Studio

Oct 10, 2006

Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys

View 2 Replies View Related

Problem With Using Stored Procs As I/p To Another Stored Procs

May 7, 2004

HI,

CREATE PROCEDURE PROC1
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
ORDER BY B.INTCUSTOMERID

END

IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?

CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC PROC1


ST......1,
ST......2,


END.

BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-


CREATE PROCEDURE MY_PROC
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (23,12,10)
ORDER BY B.INTCUSTOMERID

END

SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 12 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 10 then '1'
else '0' end) AS PREFER
FROM CUSTOMER
GROUP BY A.INTCUSTOMERID
ORDER BY A.INTCUSTOMERID
END

WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .



CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC MY_PROC


ST......1,
ST......2,

END.

BUT, HERE I WANT TO USE FIRST DATASET ONLY , HOW TO USE IT ?

THANKS.

View 4 Replies View Related

Stored Procs Are BAD!! BAD, I Tell You!!!

Feb 5, 2005

almost choked when i read the following recent post on The Daily WTF (http://thedailywtf.com/) the other day --

Logical Tiers? That Makes No Sense! (http://thedailywtf.com/ShowPost.aspx?PostID=28959)

i don't think i can do justice to how utterly stupid that stored procedure is

read the comments and have a laugh

one of the points made was that stored procedures are bad

this twigged something in my memory, so i dug around in my bookmarks, and sure enough, here's another decent discussion about stored procs --

Stored procedures are bad, m'kay? (http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx)

enjoy!

View 6 Replies View Related

.exe From Stored Procs

Jun 28, 2007

Hey guys.

I am wondering if it is possible to execute an .exe from a stored proc. I have an application that calls a stored proc and I am wanting for that stored proc to call up an app to show certain results. I have no way of doing this on the application itself as I dont have the source code. So, since I do have access to the SQL I am wondering if it could be done there.

Thanks
tibor

View 14 Replies View Related

Startup Procs

Jan 13, 2004

Does anyone know how to tell which procedures are called when SQL Server 2000 starts up if scan for startup procs is turned on?

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved