SQL 2012 :: Use SSIS Or Stored Proc Or Combination To Handle Stopping A Process On Failure
Apr 7, 2015
A project I'm working on consists of a Main stored procedure which then runs about 30 nested procedures. The client wants to know when a certain nested SP fails, but wihtout rollbacks, as they may want to fix a data item manually (such as a missing Patient ID, that they have to call someone about). At this point, we don't want to roll back anything but halt the rest of the nested SP's and send out an email to someone that they have to check out a missing PatientID.
I'm wondering if an SSIS package would handle this better than just using a Stored Procedure. When that SP runs, it will also update a "Process tracking" table in the backend, that would update [Lastprocessran] with a number. I'm thinking that if they run the main SP again, after making a manual correction, that they could re-run the main SP, and it would bypass any step that already ran successfully based upon the [Lastprocessrun] number.
I have a scenario where I need to develop a stored proc to identify invalid input provided.
Following is a sample scenario
Create table product (ProductId varchar(10),SizeId int,ProductColor varchar(10)); insert into Product select 'Prod1',10,'Black' union ALL select 'Prod1',10,'BLue' union ALL select 'Prod2',20,'Green' union ALL select 'Prod2',10,'Black' ;
[Code] ....
In following TSql Code , Color and Size are optional. Both are provided as comma separated input. I have provided "bbc" as wrong color and "MM" as wrong size. I want to identify if color is invalid or size (MM is in valid for Black and Blue) and to set flag accordingly.
I am trying to use SQL Server 2012 for the first time. I am running Win 8.1. I create a local website and sample webpage using Visual Studio 2012 for Web. Running this page on my localhost, when it attempts to create a new SQL database this exception is thrown:
"Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed."
I have a service broker process that's in run-away mode and I cannot figure out how to stop it.
Here's the situation:
The stored procedure named in the activation queue is opening a transaction but not closing it. The receive loop has a timeout of 3 seconds, with MAX_QUEUE_READERS set to 5 so every 3 seconds I'm getting 15 new records in
sys.dm_tran_locks.
The server is at 99% CPU with no users on it.
The problem is I can't stop it. Here's what I have tried:
Reboot the box Clears the lock table and runs away again ALTER QUEUE <Queue Name> WITH STATUS=OFF Just sits there forever Drop the Service, Drop the Queue Just sits there forever Dropped the stored procedure itself (!) It's still running like crazy
So my immediate question is how do I stop this crazy thing? And the follow up question is when a programmer makes a mistake, do we have to reformat our DEV box to recover from it (that's a joke) -- seriously how do you stop a run away service broker process?
I have a SSIS pkg that gets data from SQL and do data conversion and Insert into OLE db AS400 destination, There is a flag column in SQL table , that has to be updated to true, once the records are inserted in AS400 how do i do that in SSIS
SQL oledb ---------> dataConversion ---------------> AS400 OLE db Destination | update SQL table Flag column<---------------------------------|
Now what i have to do is i need to make sure that ID,Name,City,County,Phone is there in flat file. if it is not there then i have to send mail to client saying that file is not valid.
Let me know how i will do it.I need to also calculate the size of flat file.
in a my SSIS 2012 pkg I'm using a Foreach ADO Enumerator container that reads an object variable in order to get an id value.This identifier is passed as an input parameter to an Execute SQL task to update an Oracle table: if this task fails the id is written on a SQL Server table. After the Execute SQL task execution, with success or failure, the flow go to another task in the container.
When an error occurs for the update on Oracle table, each tasks inside the container are executed but the container fails and the loop ends.I'd like to complete the entire loop respect to the identifiers present in the object variable also if the update operation on Oracle table goes in error.
Now I have a different constellation: Integration Services run on one server, in version 2014, the Analysis Services instance to process the cube database on runs on another server, version 2012.I tried several different combinations of SSIS version and Analysis Management Objects version, and got several errors while running the process package (e.g. object reference not set to an instance of an object, cannot find AnalyisServices.dll..)
Is this combination 2014/2012 possible at all?I assume the BIDS version has to be for SQL Server 2014, as I want to run SSIS packages on a 2014 server, is that correct? Does it matter at all, can I also deploy 2012 packages?Which version of Analysis Management Objects do I have to use? I assumed I have to use version 11.0 here, because I want to process a 2012 cube?If it is possible to use the "old" 11.0 version of AMO, do I have to do anything so that it can be found by the SSIS package running on the server (it was built on my local computer, there I have all SQL Server versions from 2005 to 2014 installed in parallel), or do I just have to copy it to the appropriate SQL Server folder?
I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.
Here are my simulated procs...
Main Stored Procedure: This will be called once per each row of some table.
-- All this proc does is, prints out the list of parameters that are passed to it.
CREATE PROCEDURE dbo.MyMainStoredProc ( @IDINT, @NameVARCHAR (200), @SessionIDINT ) AS BEGIN
[Code] ....
Here is a sample call to the out proc...
EXEC dbo.MyOuterStoredProc @SessionID = 123
In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.
I need to use the same SQL source code in more than one stored proc.
Is there a way to -include mysql.sql or something like that ?
Is InsertSnippet the only way to do this ?
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS CoreTotal ,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)!='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreSales ,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreUpsell ,CASE WHEN P.PROD_CTG != 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS NonCoreTotal
I figured out a way to execute an Oracle Stored Procedure from an Execute SQL Task by using
Declare Begin SomeStoredProc(?,?,?); End;
with an OLE DB connection using the Oracle Provider for OLE DB.
The parameters are getting passed in and the procedure executes but if for some reason it fails SSIS is painting the task green and keeps processing. I'm guessing that's because the outer Declare/End statement completed sucessfully.
I couldn't get it to work as a function with a return value. :(
Is there another way to execute an Oracle stored procedure that I missed?
Can you call an Oracle stored procedure from a Script Task and then fail it on parameter value?
Have a situation where I need to check 100+ columns in the dataflow against lookup values to make sure all values are valid, and wanted to take a poll. Would it be better to
1) load the data into a working table and use traditional stored procedure (either NOT IN or LEFT OUTER JOIN where x is null) in order to weed out my bad values against my lookup table.....example
SELECT a.Col1 b.Col2 FROM Table1 a LEFT OUTER JOIN Table2 b ON (a.JoinCol = b.JoinCol) WHERE b.JoinCol IS NULL
This results in poor performance b/c my temp work table is not really optimized for joins over to the lookup tables & I have so many columns that I don't really want to add all these indexes - my thoughts were that the index builds would take longer than the table scans.
OR
2) Use a huge number of lookup transforms in my data flow and keep it all in SSIS.
#1 is easier to maintain (my opinion) for future purposes but slower b/c I don't want to deal with indexes on the work table b/c it will be highly volatile. So - less cumbersome but slower
#2 will be more difficult to maintain b/c of the sheer # of lookups (since I can't change the SQL statement @ run time I have to put them all in separate lookups). Probably will run faster though b/c I won't have to deal with the transfer of the data to the db and also will avoid the table scans from #1. So - more cumbersome but faster.
I have created an SSIS package with protection level - EncryptSensitiveWithUserKey. It is running alright from Visual Studio Environment but i need to call the package from an application, so i created a stored proc to call it, but getting the following error while running the stored proc:
Error: 2007-04-16 17:55:05.78
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
I have a stored proc that is executing in 2 sec on production and test database. It is taking more than a min on dev environment.
I have verified sqlserver version is same on both of the server.Prod is running on 2012Sp1 however dev don't have sp1. I am downloading it.
Both are 64bit, has same collation and compatibility level.I have confirmed that sp on both servers has same execution plan. I have reset and import stats from prod too.
I have an SSIS package that executes a stored procedure. In that stored procedure is a try/catch block. If the try isn't successful, it goes to the catch block which does a rollback. So when I execute the SSIS package, it tells me that the stored procedure was ran successfully because there essentianlly were no errors and everything ran fine, but in reality, everytime it goes into the catch block and does a rollback, I want the SSIS package to fail as well. How would I send back a failure to the SSIS package from the stored procedure?
I would like to build a report with nice functionalities like filter, sorting, drill-down, something like a PowerPivot Table, but with some layout/design/format capabilities. I would also want to publish the report, refresh it let´s say once a week, notify users when a new version is available, etc.
If I use PowerPivot, then I am not able to customized the report or to mix data from different sources in one table.
If I convert the cells of the PowerPivot table to workbook formulas I lose the filter, sorting, etc functionalities.
I still have to try using Reporting Services, but I think that always something is missing.
I need a query to produce permutation combination.
declare @t2 as table (tab varchar(100)) insert into @t2 values ('V') insert into @t2 values ('VL') insert into @t2 values ('1099') insert into @t2 values ('VOI')
declare @t1 as table (tab varchar(100)) insert into @t1 values ('I') insert into @t1 values ('U') from the above I need following output (attached output),
I am wondering if you guys have any experience with failing Stored Procedures running inside a SSIS package with the following error:
===================================================== Message Executed as user: GAALPSVR034FSYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:51:22 AM Error: 2008-05-19 09:06:55.15 Code: 0x00000000 Source: usp_SLIM_Site_PreProcess Description: TCP Provider: The specified network name is no longer available. End Error Error: 2008-05-19 09:06:55.18 Code: 0xC002F210 Source: usp_SLIM_Site_PreProcess Execute SQL Task Description: Executing the query "Exec usp_SLIM_Site_PreProcess" failed with the following error: "Communication link failure". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:51:22 AM Finished: 9:06:55 AM Elapsed: 932.203 seconds. The package execution failed. The step failed. ======================================================
Sometime it fails when gets called through a job and sometimes even when a package is open in a design mode. The actual Stored Procedure NEVER fails if called in SQL Server Management Studio.
Any ideas or suggestions will be very helpful and appreciated Thanks for your help!
I have a multiselect checkbox list in my UI. I pass the list items to my stored proc as comma separated parameter and then I have a function which converts this parameter to a table with separate rows.
E.g. : a,b,c,d
Converted to result table
result a b c d
I want to insert each row of the result table into another table. How to do that.
E.g., the table after the function is :
CREATE TABLE #result ( Subject varchar(100) )
insert into #result values ('a') insert into #result values ('b') insert into #result values ('c') insert into #result values ('d')
DOC_NO // REV_NO // FILE_NAME ABC123 // A // abc123.pdf ABC123 // B // abc123_2.docx ABC124 // A // abc124.xlsx ABC124 // A // - ABC125 // A // abc125.docx ABC125 // C // abc125.jpg ABC125 // C // abc125.docx ABC125 // C // - ABC126 // 0 // - ABC127 // A1 // abc127.xlsx ABC127 // A1 // abc127.pdf
I'm looking to select all rows where the DOC_NO and REV_NO appear only once.(i.e. the combination of the two values together, not any distinct value in a column)
I have written the sub query to filter the correct results;
SELECT DOC_NO, REV_NO FROM [MYTABLE] GROUP BY DOC_NO, REV_NO HAVING COUNT(*) =1
I now need to strip out the records which have no file (represented as "-" in the FILE_NAME field) and select the other fields (same table - for example, lets just say "ADD1", "ADD2" and "ADD3")
I was looking to put together a query like;
SELECT DOC_NO, REV_NO, FILE_NAME, ADD1, ADD2, ADD3 FROM [MYTABLE] WHERE FILE_NAME NOT LIKE '-' AND DOC_NO IN (SELECT DOC_NO, REV_NO FROM [MYTABLE] GROUP BY DOC_NO, REV_NO HAVING COUNT(*) =1)
But of course, DOC_NO alone being in the subquery select is not sufficient, as (ABC125 /A) is a unique combination, but (ABC125 /C) is not, but these results would be pulled in.
I also cannot simply add an additional "AND" clause on its own to make sure the REV_NO value appears in the subquery, because it is highly repetitive and would have to specifically match the DOC_NO)
What is the easiest way of ensuring that I only pull in the records where both the DOC_NO and REV_NO (combination) are unique, or is there a better way of putting this select together altogether?
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
I have a job that runs a dtsx package (containing an infinite loop). What I want to do is set up a schedule to start the job at 8am and stop it at 5pm. The scheduling GUI doesn't give this kind of option (caveat: that I can see).
I have two process steps in a package and one failure step. Both process steps have On Failure workflows to the failure step. The problem I'm having is that it seems if two or more workflows go to the same failure step then neither go to the failure step, but it does go if only one process step is attached to it.
I know I can create duplicate steps for each process step, but I was hoping to be able to do it this way FMI.
I have a set of winforms (.NET 2.0 assemblies) ( 6 total ) that each tries to open a connection to the same Database file using the same connection string. In the connection string, UserInstance is set to true.
If each winform is started manually after opening the session on the computer, the connection to the database performs well for every winform.
If the same winforms are automatically started at the opening of the user session, the the following error is reported by each application when trying to open the connection :
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
The only way to overcome this problem is :
to stop the winforms, to stop the SQLExpress service, to kill the remaining sqlserv.exe running task, then restarting SQLExpress service, and finally manualy start the winforms.
What is the command to stop a stored procedure from continuing to run? I have a procedure that has a problem, and due to a bug it runs wild in some scenarios, so I need to be able to stop it.
I am having trouble executing a stored procedure on a remote server. On my local server, I have a linked server setup as follows: Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
When I attempt to run the above, I get the following error: Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'. No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
I am trying to use the Transact-SQL debugger to step through a stored procedure on a server to determine where it is failing. I right-click the stored procedure I want to debug in the Query Analyzer's Object Browser and choose Debug... In the Debug Procedure dialog I fill in the values for the parameters, choose the Auto roll back checkbox, and press Execute.
The T-SQL Debugger screen comes up with my stored procedure and a message on the bottom of Waiting for user input. But I can input nothing, neither click on the icons on top, set breakpoints, or doing anything else, as the stored procedure runs to the end and gives me the return code.
If I subsequently set some breakpoints in my stored procedure and try the Go button. Once again the stored procedure runs to the end without stopping at any breakpoint.
In both cases, the stored procedure shows the waiting icon as it proceeds to the end, so that I can not click on anything to stop it.
I have been told this is may be a permissions issue between myself as a client on my machine and the stored procedure on the server. If so, does anybody know what I the things that have to be done on the permissions side to be able to debug the stored procedure using the Transact SQL debugger.
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
