I have a simple question: Can I have two or more stored procedures that begin transactions but the last stored procedure will commit all transactions if there was no error?
In other words, I have a stored procedure that performs some legacy stuff. I do not want to rewrite that logice, execpt I am putting it into a Stored Procedure since it currently is embedded SQL.
To support newer functionality, I am writing data to additional new tables. However, I do not want the *new* things to occur if there is an error. This is easy enough, I dont call the Stored Procedure for the new functionality if there was an error. However, if there was not an error and the newer stored procedure is called AND there is an error in the new stored procedure, I want to ROLLBACK the changes from the proceeding stored procedures.
To my understanding, I can name transactions but that is only to indicate in the logs what transactions have failed.
I thought about not using transactions for any of the individual stored procedures and calling them from a main stored procedure. The main stored procedure will have a BEGIN TRY and an END TRY (I am using SQL Server 2005) and at the top (right after the try) I will have a BEGIN TRANSACTION. In the BEGIN CATCH I will have a ROLLBACK and at the end I will have a COMMIT. If any of the stored procedures fail at any point it will cause the catch to occur thus rolling back all of my transactions. This would be the easiest way but I still need to deal with the question of, "What happens if any single stored procedure is called?" I guess I could have try and catches in each individual stored procedure for that.
I just want to make sure I am not overlooking something simple. I think I have the right idea by doing it this way I mentioned above. Because this is critical billing processing I just want to have some reassurance that this would work.
My company has decided that all CRUD operations will be done via stored procedures. I am in the process of coming up with a strategy for handling transactions. Before I begin my design, I was wondering if I will be better off keeping my transaction logic completely in my stored procedures or would I be best served by surrounding calls to my stored procedures using ADO.NET transactions to handle the transaction logic?Thanks
I have been having a bit of trouble creating a sql schema creation script (using sql server 2005) with generic stored procedures. I am generating the script from an xslt transform, so it is all in one document. The intent is that either everything works (schema creation, tables, constraints, procedures, and triggers) or the entire thing will be rolled back.
The problem I am having is that I can not find a way to put more than one stored procedure inside of a transaction statement, much less having all of the create statements needed within the same transaction. As far as I can tell the GO statement is needed between stored procs, but this statement appears to commit the preceding create <object> statement - breaking my atomic transaction.
Does anyone know a way around this or a way to put multiple procedures/triggers in the same transaction?
My stored procedure looks like this: ALTER PROCEDURE dbo.Calculate AS BEGIN SET NOCOUNT ON declare @lastUpdate varchar(15) declare @sql nvarchar(4000) declare LastUpdate cursor for select updateID from currentUpdate OPEN lastUpdate FETCH NEXT FROM LastUpdate INTO @lastUpdate SELECT @sql = 'INSERT INTO A ...WHERE ..' + @lastUpdate EXEC(@sql) SELECT @sql = 'INSERT INTO B ...WHERE...' + @lastUpdate EXEC(@sql) SELECT @sql = 'UPDATE ...WHERE...' + @lastUpdate EXEC(@sql) CLOSE LastUpdate DEALLOCATE LastUpdate END
When I run this stored procedure from my web page, only the first sql is executed and then I get a timeout message. When I execute the stored procedure manually on the server, all the sqls are executed correctly. How can I solve this problem? Thank You.
Am i the only one who is forced to open multiple instances of SQL Server Enterprise Manager in order to edit more than one stored procedure at a time? A lot of times i'll be comparing two sprocs or writing one based off of a "template" i wrote earlier. But i have to open two instances and alt-tab between them. Is there an easier way? (aside from pasting it into a notepad and using that)
Hi, I am hoping someone can help me with creation of a stored procedure. What i would basically like to do is have a SELECT stored Procedure that gets values from one table and then uses these values to retrieve data from another table. i.e. I would like to retrieve 2 dates from another table (the table has a single record that holds the 2 dates) then I would like to use those two dates to retrieve all records of another table that fall between those 2 dates. As I know nothing about stored procedures I'm not even sure how to go about this?? Regards. Peter.
I am creating a database application that is accessed through a .NET front end. What I want to do is run a SQL script that will create my DB, create my indexes and enforce my constraints (all of which I have done) but I also want to create my stored procedures in the same script also.
When I merge all my stored procedures (about 16) into one file and run it in SQL Query Analyzer I get multiple errors but the one that is coursing me the most bother is
‘Server: Msg 156, Level 15, State 1, Procedure procedureName, Line 134 Incorrect syntax near the keyword 'procedure'.’
What does this mean and why can’t I run more than once Stored Procedure at once?
I am using multiple stored procedures which are using same set of tables at a time . As stored procedures dont have any DMLs. they are just select statement copied into a temporary table for further processing. My issue is ,I dont want to wait one stored procedure until the other stored procedure is completed.
as one stored procedure is taking 43 secs and another sp is taking one min .they are conmbinely taking 1:43 mins where i want to take just 1 min which is the time took by second sp
I want this because i am calling all the stored procedures more than 5 in my reporting services to show in one report which is taking huge time
Please suggest me how to proceed here.i am stuck
what should i do with the tables or stored procedures?
I have a Report which has 8 stored procedures to get 8 resultant data sets . the stored procedures are almost similar such that they have only difference is the where clause and column getting returned.
So ,every stored access same set of tables and temporary tables getting created to store some set of active data derived from big table.
what happening is ,when i run the stored procedures individually in query analyser ,it is taking the time which is accepatable individually,but when i keep them in the same report. it is taking the time which is equal to sum of all the times taken by the stored procedures ran individually in query analyser which is some what not acceptable
can anybody through an idea,what can be done here. i already thought of locks and kept set transaction isolation level read uncomitted for all the stored procedures.but the time taking is same.
I am creating a database application that is accessed through a .NET front end. What I want to do is run a SQL script that will create my DB, create my indexes and enforce my constraints (all of which I have done) but I also want to create my stored procedures in the same script also.
When I merge all my stored procedures (about 16) into one file and run it in SQL Query Analyser I get multiple errors but the one that is coursing me the most bother is
€˜Server: Msg 156, Level 15, State 1, Procedure procedureName, Line 134 Incorrect syntax near the keyword 'procedure'.€™
What does this mean and why can€™t I run more than once Stored Procedure at once?
Hello All,I can not figure out how to accomplish the task that I want. So, please help.I have two tables, Parent and Child tables. For both Tables I have stored procedures for inserting records. I simply pass parameters for field values to stored proecdures and Stored procedures simply insert record with those values. I am first inserting record in Parent table and then in child table via calling their stored procedures one by one.Now, what I want to do is, after inserting record in parent table successfully, If record can not be inserted in child table successfully then I want to rollback, I also want to undo the insertion operation done in previous parent table's stored procedure call. The problem is, I can not span a Transaction across multiple stored procedure. If I begin a Transaction in a stored procedure for a Parent table, then I have to either roll back or commit that transaction in the same stored procedure. How can I span a transaction across multiple stored procedures, so that I can rollback in the Child Table's stored procedure in such a way that the Record Insertion of the Parent table can be cancelled. What to do...? Please help, it's urgent. Thanks to all.
I have my database and i generated the script that creates the entire database. My database has some stored procedures.
When i create the script without the stored procedures, everything works fine when i try to reinstall the database. But if i create the script with the stored procedures, i get the following error message when i try to reinstall the database:
Code:
System.Data.SqlClient.SqlException: 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Incorrect syntax near the keyword 'PROCEDURE'. Must declare the scalar variable "@time1".
as i understand, i must have 1 storedprocedure per script. I tried separating one stored procedure per script and everything works fine, the problem here is that i have many many stored procedures. Now, is there a way to have multiple stored procedures in one single sqlscript?
Do i really need to separate my stored procedures in one script per stored procedure or is there any way to put them all in one single script?
I have a Report which has 8 stored procedures to get 8 resultant data sets . the stored procedures are almost similar such that they have only difference is the where clause and column getting returned.
So ,every stored access same set of tables and temporary tables getting created to store some set of active data derived from big table.
what happening is ,when i run the stored procedures individually in query analyser ,it is taking the time which is accepatable individually,but when i keep them in the same report. it is taking the time which is equal to sum of all the times taken by the stored procedures ran individually in query analyser which is some what not acceptable
can anybody through an idea,what can be done here. i already thought of locks and kept set transaction isolation level read uncomitted for all the stored procedures.but the time taking is same.
I'm running this legacy DTS package under SQL Server 2005, and need to make some changes.
I have some (say, 3) stored procedures, and they all have one same parameter. Each SP returns a dataset. I need to import each dataset to a table using DTS. Both the SPs and the destination tables are in the same database.
So, I created an "Execute SQL Task" and typed in "SQL Statement" something like this:
INSERT INTO TABLE TABLE1 EXEC SP1 ? GO INSERT INTO TABLE TABLE2 EXEC SP2 ? GO INSERT INTO TABLE TABLE3 EXEC SP3 ? GO
And the execution returns an error: No value given for one or more required parameters.
Is it possible to assign a parameter to all three SPs? What is the work out if I don't want to change the SPs, and I don't want to create 3 Execute SQL Tasks?
Hi i have always used views in my code to group common functionality in my sql expressions and then i can simply call these views in my data access layer by saing: SqlCommand cmd = new SqlCommand("SELECT * FROM vw_Documents WHERE CategoryID = @CategoryID", cn); However my view has become so complicated that i had to convert it to a stored procedure called sp_Documents. The problem now though is that is that i wish to do queries against the data returned but i can't simply say: SqlCommand cmd = new SqlCommand("SELECT * FROM sp_Documents WHERE CategoryID = @CategoryID", cn); The only way i can see to do it is to create a stored procedure for every single senario i have passing in the appropriate values as parameters. This seems a pretty messy solution to me because i would have repeated logic in all my stored procedures. Therefore i was wondering if there's a simpler way for me to do this or am i just being lazy :). Appreciate if someone could help,
I am trying to insert each record coming from my DataTable object to sql server database. The problem that I have is that I have my stored procedures within the loop and it work only for one record, because it complaing that there are too many parameters. Is there a way i can add up my parameters before the loop and avoid this issue?
Here is the code I am using:Public Sub WriteToDB(ByVal strDBConnection As String, ByVal strFileName As String, ByVal strTable As String) 'Fill in DataTable from AccessDim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName) Dim adapter As New OleDbDataAdapter()Dim command As New OleDbCommand Dim DataTable As New DataTableDim sqlCommand = "SELECT * FROM " & strTableDim objDataTable As New DataTable objConn.Open() command.CommandType = CommandType.Text command.Connection = objConn command.CommandText = sqlCommandadapter = New OleDbDataAdapter(command)DataTable = New DataTable("NFS") adapter.Fill(DataTable) 'Sql DB vars 'Dim dtToDBComm = "INSERT INTO NFS_Raw(Time, Exch, Status) VALUES ('test', 'test', 'test')"Dim sqlServerConn As New SqlConnection(strDBConnection)Dim sqlServerCommand As New SqlCommand sqlServerCommand = New SqlCommand("InsertFromAccess", sqlServerConn) sqlServerCommand.CommandType = CommandType.StoredProcedure sqlServerConn.Open()For Each dr As DataRow In DataTable.Rows sqlServerCommand.Parameters.Add(New SqlParameter("@Time", dr.ItemArray(0).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Exch", dr.ItemArray(1).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@Status", dr.ItemArray(2).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Msg", dr.ItemArray(3).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@Action", dr.ItemArray(4).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@BS", dr.ItemArray(5).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@OC", dr.ItemArray(6).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CP", dr.ItemArray(7).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@Qty", dr.ItemArray(8).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Product", dr.ItemArray(9).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@MMMYY", dr.ItemArray(10).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Strike", dr.ItemArray(11).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@Limit", dr.ItemArray(12).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@StopPrc", dr.ItemArray(13).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@Type", dr.ItemArray(14).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Rstr", dr.ItemArray(15).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@TIF", dr.ItemArray(16).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@RstrQty", dr.ItemArray(17).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@ExecQty", dr.ItemArray(18).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@WorkQty", dr.ItemArray(19).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@CxlQty", dr.ItemArray(20).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@AccountNum", dr.ItemArray(21).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@ExchMbrID", dr.ItemArray(22).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchGrpID", dr.ItemArray(23).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTrdID", dr.ItemArray(24).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@MemberID", dr.ItemArray(25).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@GroupID", dr.ItemArray(26).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@NTrdID", dr.ItemArray(27).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@Acct", dr.ItemArray(28).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTime", dr.ItemArray(29).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@ExchDate", dr.ItemArray(30).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeSent", dr.ItemArray(31).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@TimeProcessed", dr.ItemArray(32).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@PA", dr.ItemArray(33).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@OrderNo", dr.ItemArray(34).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TTOrderKey", dr.ItemArray(35).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@IP", dr.ItemArray(36).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@FFT3", dr.ItemArray(37).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@FFT2", dr.ItemArray(38).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitTime", dr.ItemArray(39).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitDate", dr.ItemArray(40).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TransID", dr.ItemArray(41).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@SessionID", dr.ItemArray(42).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SeriesKey", dr.ItemArray(43).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@ExchangeOrderID", dr.ItemArray(44).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Destination", dr.ItemArray(45).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@FlowDeliveryUnit", (dr.ItemArray(46))))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeReceived", dr.ItemArray(47).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CallbackReceived", dr.ItemArray(48).ToString()))
I am trying to insert each record coming from my DataTable object to sql server database. The problem that I have is that I have my stored procedures within the loop and it work only for one record, because it complaing that there are too many parameters. Is there a way i can add up my parameters before the loop and avoid this issue?
Here is the code I am using:Public Sub WriteToDB(ByVal strDBConnection As String, ByVal strFileName As String, ByVal strTable As String) 'Fill in DataTable from AccessDim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName) Dim adapter As New OleDbDataAdapter()Dim command As New OleDbCommand Dim DataTable As New DataTableDim sqlCommand = "SELECT * FROM " & strTableDim objDataTable As New DataTable objConn.Open() command.CommandType = CommandType.Text command.Connection = objConn command.CommandText = sqlCommandadapter = New OleDbDataAdapter(command)DataTable = New DataTable("NFS") adapter.Fill(DataTable) 'Sql DB vars 'Dim dtToDBComm = "INSERT INTO NFS_Raw(Time, Exch, Status) VALUES ('test', 'test', 'test')"Dim sqlServerConn As New SqlConnection(strDBConnection)Dim sqlServerCommand As New SqlCommand sqlServerCommand = New SqlCommand("InsertFromAccess", sqlServerConn) sqlServerCommand.CommandType = CommandType.StoredProcedure sqlServerConn.Open()For Each dr As DataRow In DataTable.Rows sqlServerCommand.Parameters.Add(New SqlParameter("@Time", dr.ItemArray(0).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Exch", dr.ItemArray(1).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@Status", dr.ItemArray(2).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Msg", dr.ItemArray(3).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@Action", dr.ItemArray(4).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@BS", dr.ItemArray(5).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@OC", dr.ItemArray(6).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CP", dr.ItemArray(7).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@Qty", dr.ItemArray(8).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Product", dr.ItemArray(9).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@MMMYY", dr.ItemArray(10).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Strike", dr.ItemArray(11).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@Limit", dr.ItemArray(12).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@StopPrc", dr.ItemArray(13).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@Type", dr.ItemArray(14).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Rstr", dr.ItemArray(15).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@TIF", dr.ItemArray(16).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@RstrQty", dr.ItemArray(17).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@ExecQty", dr.ItemArray(18).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@WorkQty", dr.ItemArray(19).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@CxlQty", dr.ItemArray(20).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@AccountNum", dr.ItemArray(21).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@ExchMbrID", dr.ItemArray(22).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchGrpID", dr.ItemArray(23).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTrdID", dr.ItemArray(24).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@MemberID", dr.ItemArray(25).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@GroupID", dr.ItemArray(26).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@NTrdID", dr.ItemArray(27).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@Acct", dr.ItemArray(28).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTime", dr.ItemArray(29).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@ExchDate", dr.ItemArray(30).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeSent", dr.ItemArray(31).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@TimeProcessed", dr.ItemArray(32).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@PA", dr.ItemArray(33).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@OrderNo", dr.ItemArray(34).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TTOrderKey", dr.ItemArray(35).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@IP", dr.ItemArray(36).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@FFT3", dr.ItemArray(37).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@FFT2", dr.ItemArray(38).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitTime", dr.ItemArray(39).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitDate", dr.ItemArray(40).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TransID", dr.ItemArray(41).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@SessionID", dr.ItemArray(42).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SeriesKey", dr.ItemArray(43).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@ExchangeOrderID", dr.ItemArray(44).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Destination", dr.ItemArray(45).ToString())) sqlServerCommand.Parameters.Add(New SqlParameter("@FlowDeliveryUnit", (dr.ItemArray(46))))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeReceived", dr.ItemArray(47).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CallbackReceived", dr.ItemArray(48).ToString()))
here's my code:my $sth = $dbhSQL->prepare('{call proc(?,?,?)}');$sth->bind_param(1,"asd");$sth->bind_param(2,"klm");$sth->bind_param_inout(3,$no_go, 1000);$sth->execute;print "no go = $no_go";while(my @row=$sth->fetchrow_array){print "@row";}$sth->finish;Here's my stored procedure:CREATE PROCEDURE proc@id varchar(50),@iyt varchar(20),@no_go int OUTPUTASSET NOCOUNT ONDECLARE @id_err int,@ans_glue_err intBEGIN TRANSACTIONSELECT user_id FROM myTableWHERE user_id=@id AND iyt=@iytSET @id_err = @@ERRORIF @@ROWCOUNT <> 0BEGINSELECT date,date_mod FROM ans_glueWHERE user_id=@idSET @no_go = 0SET @ans_glue_err=@@ERRORENDELSEBEGINSET @no_go = 1ENDIF @id_err = 0 AND @ans_glue_err = 0BEGINCOMMIT TRANSACTIONENDELSEBEGINROLLBACK TRANSACTIONENDthe procedure runs perfectly in Cold Fusion, returning both recordsetsand output param, but in perl, it won't print the output param and Idon't know how to access the second recordsetHELP!
I want to use 2 stored procedures in one report, but I could not find what is the way of doing this. I searched this subject on the intrernet, and I found some solutions. However, they used stored procedures in one stored procedure and used in one report. This is not the thing which I want to do, I want only do using 2 stored procedures in one report at the same time.
I'm trying to replace a table name in 250 stored procedures. I found this script below which does a good job but it also finds tables with similar names. How can I limit the replacement to the exact table name? If my original table name is MyTable001, I do not want to find MyTable001_ID.
-- set "Result to Text" mode by pressing Ctrl+T
SET NOCOUNT ON DECLARE @sqlToRun VARCHAR(1000), @searchFor VARCHAR(100), @replaceWith VARCHAR(100) -- text to search for SET @searchFor = 'MyTable001' -- text to replace with SET @replaceWith = '[MyTable002]'
I have a stored procedure that creates a temporary table, and then populates it using an INSERT and then a series of UPDATE statements. The procedure then returns the temporary table which will contain data in all of its columns.
When I call this procedure from SSRS 2005, the rowset returned contains data in only those columns that are populated by the INSERT statement. The columns that are set via the UPDATE statements are all empty. I read (in the Hitchhikers Guide to Reporting Services) that SSRS will only process the first rowset in a stored procedure that generates multiple rowsets. Is this true? Is this why SSRS does not retrieve data for the columns that are populated by the UPDATE statements?
-- File: sp_GetProgramsWatchedByDateRange.sql -- Desc: Returns EDP program and related channel (i.e., provider) information from the IPTV Data warehouse. -- Note that some of that data used by this procedure are obtained from the RMS_EPG database -- which is created by an application (loadEPG) that loads the EPG data from a GLF format XML file. -- Auth: H Hunsaker -- Date: 11/07/2006
-- Example invocation -- EXEC dbo.sp_GetProgramsWatchedByDateRange ...
-- Arguments/Parameters:
-- Parameter Name Type Description -- 3. StartDate datetime First date of reporting period -- 4. EndDate datetime Last date of reporting period -- TerseMode bit Return all columns? (1 = no, 0 = yes) -- 5. AsXML bit Resultset format (0 = standard, 1 = XML) -- 6. Debug bit Debug mode (0 = off, 1 = on). Currently disabled
IF OBJECT_ID (N'dbo.sp_GetProgramsWatchedByDateRange') IS NOT NULL DROP PROCEDURE dbo.sp_GetProgramsWatchedByDateRange GO
CREATE PROCEDURE dbo.sp_GetProgramsWatchedByDateRange @StartDate datetime = NULL, @EndDate datetime = NULL, @TerseMode bit = 0, @AsXML bit = 0, @Debug bit = 0 AS -- Notes: Much of the program content (roles, flags, etc.) that we want is not stored in the IPTV data warehouse. -- So I am going to the RMS_EPG database to obtain that information. -- We will have to ensure that the 2 databases are generated at the same or a matching time -- in order to to ensure that all programID values in the data warehouse can be located in the RMS_EPG database.
-- Debug code for testing -- DECLARE @StartDate datetime -- DECLARE @EndDate datetime -- DECLARE @TerseMode bit
-- Basic program information tprogram int NULL, -- programID from EPG XML, needed to access program data in the RMS_EPG db. tprogramId uniqueidentifier NULL, -- programID generated by IPTV tprogramTitle varchar(150) NULL, tprogramEpisodeTitle varchar(100) NULL, tprogramDescription varchar(500) NULL,
-- Flags ClosedCaption bit NULL, InStereo bit NULL, Repeats bit NULL, New bit NULL, Live bit NULL, Taped bit NULL, Subtitled bit NULL, SAP bit NULL, ThreeD bit NULL, Letterbox bit NULL, HDTV bit NULL, Dolby bit NULL, DVS bit NULL,
-- I store the program watching data in a temp table because -- data from the VIL and the Sandbox that were used to test this procedure were either incomplete or invalid. -- Use of a temp table with a series of updates allow me more control over the result set.
IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL INSERT INTO #programWatched ( tdeviceId, tprogramId, --tprogramTitle, --tprogramEpisodeTitle, toriginDateTime, tduration, --tprogramType, --tchannelCallName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.
FROM DW_EventClientProgramWatched pw WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system AND originTime BETWEEN @StartDate AND @EndDate ELSE INSERT INTO #programWatched ( tdeviceId, tprogramId, --tprogramTitle, --tprogramEpisodeTitle, toriginDateTime, tduration, --tprogramType, --tchannelCallName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.
FROM DW_EventClientProgramWatched pw WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system
-- AccountId/SubscriberId UPDATE #programWatched SET taccountId = (SELECT accountId FROM DW_BRDB_bm_device d WHERE d.deviceId = tdeviceId)
-- program (this is the integer program ID stored in the EPG XML, not to be confused with the IPTV programId) -- a program can occur on multiple channels, so we filter channels where scheduleTime <= originTime <= scheculeTime + durationSecs UPDATE #programWatched SET tchannelCallName = (SELECT TOP 1 channelCallName FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND toriginDateTime BETWEEN scheduleTime AND DATEADD(s, epg.durationSecs, epg.scheduleTime))
UPDATE #programWatched SET tprogram = (SELECT TOP 1 program FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName), tprogramTitle = (SELECT TOP 1 programTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName), tprogramEpisodeTitle = (SELECT TOP 1 programEpisodeTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName), tprogramType = (SELECT TOP 1 programType FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName)
-- Rating (otained from programValues, can also be obtained from programFlags) UPDATE #programWatched SET programMPAARating = (SELECT TOP 1 programValue FROM RMS_EPG..programValue pv WHERE tprogram = pv.programID AND pv.programValueTypeId = 9)
UPDATE #programWatched SET programMPAARatingVal = CASE programMPAARating WHEN 'G' THEN 10 WHEN 'PG' THEN 25 WHEN 'PG-13' THEN 30 WHEN 'R' THEN 35 WHEN 'NC-17' THEN 50 WHEN 'NRAO' THEN 60 WHEN 'NR' THEN 0 ELSE 0 END
UPDATE #programWatched SET programVChipRating = (SELECT TOP 1 programValue FROM RMS_EPG..programValue pv WHERE tprogram = pv.programID AND pv.programValueTypeId = 8)
UPDATE #programWatched SET programVChipRatingVal = CASE programVChipRating WHEN 'TV-Y' THEN 10 WHEN 'TV-Y7' THEN 20 WHEN 'TV-G' THEN 35 WHEN 'TV-PG' THEN 40 WHEN 'TV-14' THEN 45 WHEN 'TV-MA' THEN 60 ELSE 0 END
-- Genre UPDATE #programWatched SET programGenre = (SELECT TOP 1 programCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType psct ON psct.programSubCategoryTypeId = pc.programCategoryId INNER JOIN RMS_EPG..programCategoryType pct ON pct.programCategoryTypeId = psct.programCategoryTypeId WHERE tprogram = pc.programID)
-- Categories UPDATE #programWatched SET programCategory1 = (SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId WHERE tprogram = pc.programID)
UPDATE #programWatched SET programCategory2 = (SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1))
UPDATE #programWatched SET programCategory3 = (SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2))
UPDATE #programWatched SET programCategory4 = (SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2, programCategory3))
-- Roles UPDATE #programWatched SET programDirectorFirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)
UPDATE #programWatched SET programDirectorLastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)
UPDATE #programWatched SET programWriterFirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)
UPDATE #programWatched SET programWriterLastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)
UPDATE #programWatched SET programProducerFirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)
UPDATE #programWatched SET programProducerLastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)
UPDATE #programWatched SET programActor1FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1) UPDATE #programWatched SET programActor1LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1)
UPDATE #programWatched SET programActor2FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName))
UPDATE #programWatched SET programActor2LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName))
UPDATE #programWatched SET programActor3FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName))
UPDATE #programWatched SET programActor3LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleLastName NOT IN (programActor2LastName))
UPDATE #programWatched SET programActor4FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))
UPDATE #programWatched SET programActor4LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))
UPDATE #programWatched SET programActor5FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))
UPDATE #programWatched SET programActor5LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))
UPDATE #programWatched SET programActor6FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))
UPDATE #programWatched SET programActor6LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))
UPDATE #programWatched SET programActor7FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName) AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))
UPDATE #programWatched SET programActor7LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName) AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))
UPDATE #programWatched SET programActor8FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName) AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName) AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))
UPDATE #programWatched SET programActor8LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName) AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName) AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))
-- Channel (provider) Call Letters, Display Name and Type -- Is this correct? Should we get the channelId from the schedule table? -- Is this efficient? View execution plan
UPDATE #programWatched SET tchannelId = (SELECT TOP 1 c.channelId FROM RMS_EPG..channel c INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram)
UPDATE #programWatched SET callLetters = (SELECT TOP 1 c.channelCallLetters FROM RMS_EPG..channel c INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched SET displayName = (SELECT TOP 1 c.channelDisplayName FROM RMS_EPG..channel c JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched SET type = (SELECT TOP 1 c.channelType FROM RMS_EPG..channel c INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched SET networkAffiliation = (SELECT TOP 1 c.channelNetworkAffiliation FROM RMS_EPG..channel c INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram and s.channelId = tchannelId)
IF @TerseMode = 0 SELECT * FROM #programWatched ORDER BY toriginDateTime ELSE -- Get only Genre, title, show date/time, rating, call letters SELECT tDeviceId, tprogramTitle, tprogramEpisodeTitle, programGenre, toriginDateTime, programMPAARating, programVCHIPRating, tchannelCallName FROM #programWatched ORDER BY toriginDateTime
I also tried a query that populates some of its columns via subqueries. The query works fine when executed by the SQL Sevrer Query Analyzer, meaning that all columns contain values, but when executed from SSRS, the columns that are poulated by the subqueries are empty, and only the columns that are not set by subqueries contain values:
SELECT PW.DeviceID, PW.originTime AS 'When Watched', PW.programID, PW.Duration AS 'Duration Seconds',
(SELECT TOP 1 programTitle FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime)) AS Title,
(SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime)) As program,
(SELECT TOP 1 programCategoryTypeValue FROM RMS_EPG..programCategory PC INNER JOIN RMS_EPG..programSubCategoryType PSCT ON psct.programSubCategoryTypeId = PC.programCategoryId INNER JOIN RMS_EPG..programCategoryType PCT ON PCT.programCategoryTypeId = PSCT.programCategoryTypeId WHERE PC.programID = (SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime))) AS Genre,
(SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory PC INNER JOIN RMS_EPG..programSubCategoryType PSCT ON psct.programSubCategoryTypeId = PC.programCategoryId INNER JOIN RMS_EPG..programCategoryType PCT ON PCT.programCategoryTypeId = PSCT.programCategoryTypeId WHERE PC.programID = (SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime))) AS Category
FROM DW_EventClientProgramWatched PW ORDER BY DeviceId, programId, originTime
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
I am writing a fairly complex data automation procedure that basically uses about 5 stored procedures and some additional sql from C# to populate a database. i would like to utilize transactions in C# and sql to help me if something goes wrong. My question is, do i have to maintain the same connection in order to have one transaction for all my Commands. right now i do things like connection.Open();Command.ExecuteNonQuery(); connection.Close(); connection.Open();Command2.ExecuteNonQuery(); connection.Close(); i think i have to keep the same connection open for the duration of all my commands in order to utilize transactions, is this correct? thanks, mcm
I have a package with two sequence containers, each containing two SQL tasks and a data flow task, executed in that order. I want to encapsulate the data flow task in a transaction but not the SQL tasks. I have the TransactionOption property set to 'required' on the data flow tasks and 'supported' on the SQL tasks and the sequence containers. When I run the package I get a distributed transaction error on the first SQL task of the second sequence container:
"[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE DistTransTbl2" failed with the following error: "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
The only way I can get the package to succeed is to set the TransactionOption = 'required' on the sequence containers and 'supported' on all subordinate tasks. This is not what I want, however. Any ideas?
