Dependent Resultsets In A Stored Procedure

Feb 18, 2003

Hi there,

I'd need to generate multiple recordsets within a MS SQL stored procedure. The thing is these recordsets depend on each previous one (the first one obviously has parameters). How can I make this work? I can't create views with SPs. Thank you very, very much.

Martin

View 4 Replies


ADVERTISEMENT

How Can I Use Several Resultsets From Stored Procedure?

Jan 11, 2007

I have create stored procedure that returns 2 resultsets. When I configure OLE DB Source to use this procedure I can not add second output for he source. I get following error:

TITLE: Microsoft Visual Studio
------------------------------

Error at Data Flow Task [OLE DB Source [1]]: An output cannot be added to the outputs collection.



------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020800F (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
BUTTONS:

OK
------------------------------


How to add second output?

View 1 Replies View Related

Return Multiple Resultsets From Stored Procedure

Mar 11, 2008

I have read a lot in favor and recommendation of returning multiple resultsets.
But now I have to implement it with a scenario.
I have a Parent Table named "Books" and a Child one named "Volumes".
A book can have multiple volumes.
Now I want to display the list of Books with their Volumes pagewise.
How can I implement that procdure.

View 3 Replies View Related

Transact SQL :: Store Resultsets Of Stored Procedure Returning More Than One Resultset In Two Different Table?

Apr 20, 2015

I have on stored procedure which returns mote than one resultset i want that to store in two different temp table how can achieve this in SQL server.

Following is the stored procedure and table that i need to create.

create procedure GetData as begin select * from Empselect * from Deptend 
create table #tmp1 (Ddeptid int, deptname varchar(500),Location varchar(100))
Insert into #tmp1 (Ddeptid , deptname ,Location )
exec GetData

create table #tmp (empid int , ename varchar(500),DeptId int , salary int)
Insert into #tmp (empId,ename,deptId,salary)
exec GetData

View 9 Replies View Related

How To Find Dependent/Referenced Objects Of A Stored Procedure?

Jan 31, 2007

Hi Frens,

Could anybody tell me how can I find all the database objects that are used in /referenced by/called by/dependent on a given stored procedure? In other words, I am looking for something like a stored procedure or a function that takes as input the name of a stored procedure and outputs all the names of the tables, functions, procedures, cursors and etc. database objects that are used in that procedure. Could you please give me suggestions or possible answers for this?

Thanks a lot for your time.
Regards,
-Ram.

View 11 Replies View Related

Stored Procedure:calculating Minus Dependent On Type With Group By

Aug 6, 2004

Hi i am trying to get the following result:
CALCULATE THE CURRENT NUMBER OF SHARES on base of number of shares bought and number of shares sold

so the logic is
int total
if transactiontype is "buy", total = total + amount bought
if transactiontype is sell, total = total-amount sold
plus must be grouped by share

base data are stored in 1 table as
share name transaction type share amount
tesco buy 200
tesco sell 50

sounds rather easy does it not?
well sql syntax must be very foreign to me as i do not manage it
I tried a few options:
1 to write a database function as above and then call it from sproc
but i need to use group by share name or id so it calculates for each share and it will not let me to use function in select with group by
2 i tried to use if within sproc but i get complain incorrect syntax (I use MSDE database accessed via Visual) - I think it suppose to be possible but it throws me out with the most simple if or = is there some special syntax?
3 I created 2 views: 1 view: all sold shares, 2nd view: all bought shares and their bought/sold quantities
as in
select share name, sum(sharequantity)
where transaction = sell
from table
group by share name
and the same for purchases
then I did union of these 2 views with boughtquantity - soldquantity what worked but it only showed me shares which were sold, if share was never sold it was not shown I guess due to union
so I am still stuck!

View 7 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

SQL Server - Multiple ResultSets

Nov 8, 2003

Hi,

How can i return multiple resultsets from SQLServer. For ex, if we pass EmpNo to my storedprocedure it should return his salary details(from Sal table) and Attendance details(attendance table).



Ramesh

View 1 Replies View Related

Comapre Two Values In Same Resultsets

Aug 29, 2006

Hi

I want to compare values of two fields in same resultset. Like I have resultset whrre there are two fields Frequency and New_Frequency I want to compare this two like if

Frequency = New_Frequency then do some logic.

I tried with derived columun using Frequency == New_NewFrequency but it doesn't work.

Let me know is there any way to compare?

Dnyandeo

View 1 Replies View Related

Mapping Variables To Resultsets

May 25, 2005

Is there a problem mapping variables to resultsets with a bigint as datatype?

View 6 Replies View Related

Strongly Typed Datatables (out Of Resultsets)

Feb 18, 2008

I dragged two tables into the dataset designer. And I have a query with a resultset over both of the tables (joined).Is there ANY way I can have a strongly typed datatable out of the resultset?The automatically created dataset adapters won't allow me to create a tableadapter or datatable for the joined resultset but for one single table at a time only.Any solution to that? Theoretically it should be possible since I don't "lose" any type information with a join.. why can't I have a strongly typed datatable over the resultset?The join is not very sophisticated. In fact it couldn't be any more simple.(I'm refering to my previously posted question at http://forums.asp.net/t/1220481.aspx) 

View 4 Replies View Related

Derived Tables From Multiple Resultsets

Oct 19, 2005

Hi!I want to return a derived table along with 4 simple tables in a storedprocedure as follows:Input parameter: @FtNum (==Order Number, selects one Order and allassociated data)Table 1: OrdersTable 2: ItemsTable 3: InstancesTable 4: StockDetailsDerived Table: for each Item that requires stock items, 1st columnshould receive the ItemNo (from Items), subsequent columns should receive thedetails from StockDetails via the common key field 'StockCode'.I have so far used a 'Fetch' cursor to find all occurrences of a StockCodewithin the Items table, but have been unable to figure out how to first addthe ItemNo into the temporary table.Code is as follows:... build #tmp_StockDECLARE stock_cursor CURSOR FORSELECT StockCode, ItemNoFROM ItemsWHERE FtNum = @FtNumORDER BY ItemNoOPEN stock_cursorFETCH NEXT FROM stock_cursorINTO @StockCode, @ItemNoWHILE @@FETCH_STATUS = 0BEGININSERT INTO #tmp_Stock-- wish to insert ItemNo = @ItemNo here --SELECT *FROM ControlledStockWHERE StockCode = @StockCodeFETCH NEXT FROM stock_cursorINTO @Stockcode, @ItemNoENDOf course there may be a much simpler way to do this!Your help would be greatly appreciated either way.--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200510/1

View 2 Replies View Related

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




View 11 Replies View Related

Reporting Sevices Multiple Resultsets In A Dataset

Aug 11, 2004

Hi!

I have a question about SQL Reporting Services. I have a stored procedure which returns multiple resultsets (multiple select). In my reporting services project I have a dataset that connects to this procedure. But, when I execute it in the Data tab, I only get the first resultset. So can't I have more resultsets?
I can't use more datasets (that have the selects in the procedure) because the procedure is complicated and has many calculation. I've managed to get all the selects in only one and the reporting services in working in this way. How about many resultsets in a dataset?

Thank you!

Irina Stanca

View 5 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?

Jan 29, 2015

I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?

CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],

[Code] ....

View 9 Replies View Related

Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.

Sep 19, 2006

I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.

How do I do that? Articles, code samples, etc???

View 1 Replies View Related

Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page

Dec 28, 2005

I have a sub that passes values from my form to my stored procedure.  The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page.  Here's where I'm stuck:    Public Sub InsertOrder()        Conn.Open()        cmd = New SqlCommand("Add_NewOrder", Conn)        cmd.CommandType = CommandType.StoredProcedure        ' pass customer info to stored proc        cmd.Parameters.Add("@FirstName", txtFName.Text)        cmd.Parameters.Add("@LastName", txtLName.Text)        cmd.Parameters.Add("@AddressLine1", txtStreet.Text)        cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue)        cmd.Parameters.Add("@Zip", intZip.Text)        cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text)        cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text)        cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text)        cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text)        cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text)        ' pass order info to stored proc        cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue)        cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue)        cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue)        'Session.Add("FirstName", txtFName.Text)        cmd.ExecuteNonQuery()        cmd = New SqlCommand("Add_EntreeItems", Conn)        cmd.CommandType = CommandType.StoredProcedure        cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc)   <-------------------------        Dim li As ListItem        Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar)        For Each li In chbxl_entrees.Items            If li.Selected Then                p.Value = li.Value                cmd.ExecuteNonQuery()            End If        Next        Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder)  and pass that to my second stored procedure (Add_EntreeItems)

View 9 Replies View Related

SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?

Sep 26, 2014

I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure

at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT

I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT

View 3 Replies View Related

System Stored Procedure Call From Within My Database Stored Procedure

Mar 28, 2007

I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.

How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?

Thanks in advance

View 9 Replies View Related

Ad Hoc Query Vs Stored Procedure Performance Vs DTS Execution Of Stored Procedure

Jan 23, 2008



Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio

What could explain this?

Obviously,

All three scenarios are executed against the same database and hit the exact same tables and indices.

Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).

The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes

View 1 Replies View Related

Failed To Generate ResultSet Code. Typed ResultSets Are Only Valid For Microsoft SQL Server Compact 3.5 Databases

Feb 27, 2008



I get the error above when I do the following.

Using VS 2008 I got a Windows Smart Device project targeting WM6 Standard and using .NET CF 3.5.
I add a new database file = creating an empty Compact 3.5 database (creates an sdf-file in my project).
Then create an empty dataset in the wizard (creates an xsd-file in my project).
Then I add a couple of tables in the database.
After that I want to use my earlier created dataset.
I mark the xsd-file and looks in Poperties.
I get the error when I try to change Custom Tool value from 'MSDataSetGenerator' to 'MSResultSetGenerator'.
Why????

View 1 Replies View Related

Dependent Columns

Dec 4, 2004

I have three columns (CWOC, C, and PWC). both CWOC and PWC are dependent on the value in C, yet C is dependent on the value in PWC.

Basically:

C = PWC * BR
PWC = MR - CWOC
CWOC = MC - C


In setting up these columns in the database how do I set up columns like these that are so dependent on each other?

This was set up previously in Excel using iteration, but now I'm recreating the application in VB.net and SQL Server.

Thanks for your suggestions.

View 4 Replies View Related

User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net

Sep 13, 2007

Hi all,



I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.



Whenever I tried to right click stored procedure and select step into store procedure> i get following error



"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"



I am not sure what needs to be done on sql server side



We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.

Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?



Please advise..

Thank You

View 3 Replies View Related

Dependent Service On MSSQLSERVER?

Jun 15, 2004

I have a ABC service on a server1 that is dependent on MSSQLSERVER service running on server2. Does anyone know how to setup the service ABC on server1 to have a dependancy on MSSQLSERVER running on server2?

Thanks,
jgs

View 3 Replies View Related

MAX Date Dependent On Quantity

Aug 20, 2013

I am trying to query only the Max date dependant on quantity

Create Table dbo.TestParts
(Part char(30), Desc1 char(50), Desc2 char(50));
Create Table dbo.TestStructure
(Model char(30), Part char(30), EDATE smalldatetime, QtyPer float);
GO
Insert INTO dbo.TestParts Values('101111','Widget A', 'Batteries Not Included'),

[Code] ....

Looking for a return of:

PART Description EDATE QtyPer
101112 Widget B ..... 2012-12-03 3
101113 Widget C ..... 2012-12-03 5
101114 Widget D ..... 2012-12-01 1

Widget A should not show because the last date the qty was changed to Zero

Closest that I have come..........(which is pulling the part 101111 which should be incorrect)

Select ts.Part, RTRIM(tp.Desc1) + ' ' + RTRIM(tp.Desc2) as Description,
ts.EDATE, ts.QtyPer
FROM testing.dbo.TestStructure ts
Inner Join (Select Part,MAX(EDATE) as Date
FROM testing.dbo.TestStructure
WHERE QTYPER <> '0'

[Code] .....

View 3 Replies View Related

Query Dependent On Three Fields?

Oct 13, 2014

I am trying to do a select query like below...

SELECT INVENTORY_ITEM_TAB.ITEM_NO, INVENTORY_ITEM_TAB.DESCR, INVENTORY_ITEM_TAB.STATUS_FLG, INVENTORY_ITEM_TAB.PRICINGUOM,
INVENTORY_ITEM_TAB.PURCHUOM, INVENTORY_ITEM_TAB.ITEM_CATEGORY, INVENTORY_ITEM_TAB.ICINTERNALNOTES, INVENTORY_WHS.QTY_ON_HAND,
INVENTORY_WHS.QTY_ON_ORDER, INVENTORY_WHS.QTY_ALLOCATED
FROM INVENTORY_ITEM_TAB INNER JOIN
INVENTORY_WHS ON INVENTORY_ITEM_TAB.ITEM_NO = INVENTORY_WHS.ICWHSPCODE
WHERE (INVENTORY_ITEM_TAB.ITEM_CATEGORY = 'SS') AND QTY_ALLOCATED, QTY_ON_HAND, Qty_On_ORDER <> 0
ORDER BY INVENTORY_ITEM_TAB.DESCR

But I don't want to select records if all of these fields have a 0 in them - QTY_ALLOCATED and QTY_ON_HAND and QTY_ON_ORDER.

how to do this type of query. If any of those fields doesn't have a 0 then I would want to return it. I just don't want them if all three of those fields have 0.

View 3 Replies View Related

Question About Dependent Queries

Jul 20, 2005

Suppose I have the following table:col1 col2hammet jonesjlo afflectafflect armandwills snoptarmand hammetjones smithIf someone choses armand, then I'd like to returnamand hammet jones smithThe first selection goes over to the second column, gets that valueand locates it back in column one and returns column 2 and so on.One way of doing it is to set up a separate query for each one andthen construct a new query to get them all.I'm thinking there's a more elegant way to do this. Any suggestionswould be appreciated.-David

View 2 Replies View Related

Is It Possible To Use Dependent Combo Boxes?

Nov 15, 2007



Hi,

I have a problem with a report.


in this report 1 use 2 combo boxes. The first with my organisation on different levels.

And in the second a value for every level and all.

Now i want to filter the organisation box with the second combo box.

For the first time it works but if i change after that the box it doesn't work.

I use SQL Reporting Services 2005

View 4 Replies View Related

User Dependent Security

May 10, 2007

I was just analysing the security which can be given to different users to access respective databases. So i tried, on my local server, to deny permission to myself to access Model database. After this i am not able to connect to my local server at all. Error: Permission denied. I am using windows authentication mode.

I have also deleted the local server registration, and re-registered it, but still the condition is same. Do i need to uninstall SQL Server completly to get rid of this prob.? I also registered a new data server, and there every thing is going fine. So now what do i do to get connected to my local server.

View 5 Replies View Related

Defaulting Dependent Parameters

Jul 23, 2007

Hi,



I have a report which uses a parameter called "Interval" with possible string values of "Daily", "Weekly", and "Monthly" and I want these values to set the default values for my StartDate and EndDate parameters. However, the default value remains static. And I have tried all of the solutions found in this forum. Any suggestions?



I am using SSRS 2005. And I have tried using an iif statement for StartDate, also tried Property->Custom Code, and also tried using stored procedure to populate Start and End Dates, but none of these provide dynamic parameter setting like I was hoping for.



And I think this should work because I know that if it is not a date that it works correctly. For example if I have one parameter called country and a subsequent one named state, every time I change the country it will let me re-pick the state. But this is not working correctly here.



Any ideas on what I am doing wrong or how to fix this?

View 1 Replies View Related

Delete Error Of Dependent Row

Jan 9, 2007

I am not able to delete a row due to the presence of its parent table. There is no circular relationship and the child has no dependencies when I verify the Delete trigger. Any advice? See error msg below:

Msg 30010, Level 16, State 1, Procedure tD_My_child_table_name, Line 43

Cannot DELETE last My_child_table_name CI because My_parent_table_name exists.

Msg 3609, Level 16, State 1, Line 1

The transaction ended in the trigger. The batch has been aborted.

View 4 Replies View Related

How To Run Multiple Dependent SQL Jobs With OSQL

Sep 22, 2005

I want to execute multiple SQL Server scheduled jobs using OSQL, but I only want to execute a job if the previous job completed. Is this possible? So far I am unable to prevent all jobs from starting before any have completed.

OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber1'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR

OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber2'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR

OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber3'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR
GOTO EXIT

:ERROR
ECHO *** ERROR *** Check Log File

:EXIT
*** JOB COMPLETED ***

Dave

View 5 Replies View Related







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