Trapping Stored Procedure Errors
Jun 23, 2000In Stored Procedures that have a group of statements wrapped in a Begin and End Statement What is the best way of trapping errors ?
View 1 Replies
ADVERTISEMENT
Trapping Errors In Stored Procedure
Jul 20, 2005Hi there,I am converting a large PL/SQL project into Transact-SQL and have hitan issue as follows:I have a PL/SQL procedure that converts a string to a date. Theprocedure does not know the format of the date in the string so ittries loads of formats in converting the string to a date until itsucceeds.After trying each potential format it uses the Oracle 'EXCEPTION WHENOTHERS' construct to trap the failure so it can try another format.Is it possible to do this with SQLServer ? If I do a CONVERT and it isnot one of the standard formats it fails. This is part of a backgroundscheduled process and I cannot afford the procedure to bomb out.I suspect the answer is I cannot do this and will need to impose somecontrol over the string being received (from various externalsystems!!) to ensure it is a specific known format. Even if I know itwill be one of the known SQLServer formats this will not be enoughsince if the first one I try is not correct the process will crash.Any ideas ?Thanks
View 2 Replies View RelatedTrapping Errors In SPs
Dec 28, 1999Hi,
Here is what I am trying to do:
CREATE PROCEDURE usp_deleteSomething
@theThing_i char(11)
AS
BEGIN
SET NOCOUNT ON
delete from myTable where thing=@theThing_i
return(@@ERROR)
END
I thought this SP would return 547(foreign key constraint voilation) when column 'thing' was being referenced in another table. Instead, when the front-end application code calls this SP it gets a 1 from the delete statement itself. In other words, my return statement never seems to get executed. Is there any way of achieving this? In other words, I want to trap the error 547 and return that to the front-end.
Any replies would be greatly appreciated.
Thanks in advance,
Nishi
Trapping Errors
Jul 20, 2005Hi,I have a stored proc StoredProc1 ={INSERT INTO Table1SELECT *FROM View1Return @@ERROR}StoredProc1 is used in another sp StoredProcMain ={(some code before)...EXEC @iResult = StoredProc1If @iResult <> 0BEGINROLLBACK TRANSACTIONReturn @iResultEND.... (continue)}So I want to rollback if StoredProc1 is not successful.Then I ran into a problem. I added a column to Table1 but forgot toupdate View1 to add the equivalent column. When I executedStoredProc1, I got the "Insert Error: Column name or number ofsupplied values does not match table definition." But the error isNOT trapped. It seems the instruction "Return @@ERROR" returns 0 andStoredProcMain goes on as if there wasn't an error.How can I trap this error?ThanksWalter
View 2 Replies View RelatedDTS - Trapping SQL Errors
Mar 23, 2006
I am panning to write a DTS package whcih alter the table and output any error messages if the alter statement fails.
I have created Execute SQL Task in which I have wrote the following command.
Alter table Employee ADD EmpStatus char(4) DEFAULT A not null;
I have created a work flow to write the error message to a text file. But I am having trouble to trap the error message prduced by the Alter statement (like "column names in each table must be unique. Column name specified int the table more than once").
Any help will be appreciated.
Thanks
Sankar
Trapping SQLDataSource Errors
Oct 24, 2006I have read some ideas on this, but nothing is working for me.I have an SQLDataSource bound to a FormView. I need to use the FormView to Insert new rows. When I type new values, all is well. When I type a duplicate, a get a runtime primary key error. That's fine, but how do I trap that? Overriding Page_Error doesn't work for me.Anyone please?
View 1 Replies View RelatedTrapping Cmdexec Errors
Nov 11, 1998I would like to trap a return value from a cmdexec that is scheduled. The cmdexec returns 0 if it is a success and something other than 0 if it doesn't.
Can I raise an error from a command file. The command file calls a console application ( i.e. no interface ).
Any help is appreciated.
Trapping Sqlcmd Errors When Executed From A Job
Jul 17, 2007Hi I am running some scripts in files using sqlcmd via a SQL Server Agent job. If sqlcmd generates an error (for example if it is unable to connect) then the job fails. However, if the T-SQL within the script is invalid (syntax, name resolution etc etc) the job completes reporting success. If sqlcmd is invoked directly via the query window then no error is raised however there is a result set returned reporting the error. Anyone know why and whether is it possible to get the error to be recognised by the job? invalid_sql.sql--The below is not actually valid SQL.do SOME stuff, innit! sqlcmdEXEC master.dbo.xp_cmdshell "sqlcmd -S my_server -i C:invalid_sql.sql" Cheers
View 2 Replies View RelatedSQL Errors, Trapping In Server Agent
Nov 28, 2005Hello to all,I've fallow problem. I've a sp called as a job of SA each minute. Thisruns pretty nice, but from time to time, the job is aborted, and Idon't know why.Considering my logging, which is implemented in DB, I know, in whichpoint it is happening, but I don't know the exact error.This one is for sure any SQL server exception.I wanted to track this error, but reading all news, and help, andperforming some tests, I've find out, that this is almost likeimpossible, to catch the error in t-sql code (for example in this sp),and wirte it to any table for futher review.Reading great documentation from Erland Sommarskog, I know, there isno way to catch this error in t-sql, because, usualy the sql terminatesexecution of the code immieadetly (so I found it also by my tests).Now, my question is: sice I'm calling this sp continously in ServerAgent as a job scheduled to be called each one minute, is it any way,to trap this error on this level? In SA? and THEN save it somewhere inmy db?I'm calling the sp as a 'command' in job step as 'execsp_name_of_procedure'.If I'll try like this:declare @err intset @err = 0exec sp_name_of_procedureset @err = @@errorif @err <> 0begininsert into tbl_logger (sql_error, msg) values (@err, 'SQL raised anerror')endwill it work, or the sql will assume the whole code as a one batch, andwill terminate after call of sp?Thank you in advance for reply.GreatingsMateusz
View 2 Replies View RelatedTrapping Errors In The Control Flow
Mar 1, 2007SSIS GURUS:
I have read several posting about various modes of trapping errors, but none seem to directly address what I am looking for (SQLIS.com.MSDN, etc)
Coming from a Java/C# background, I am looking for a way to trap errors that arise within the ssis control flow much like the said languges:
try {
do something
} catch(AnExceptionType myException) {
handle my exception
}
/ ** my code at this point is unperterbed by the exception unless I explicitly raise the exception out of the scope of the exception handler. */
To make the analogy in SSIS, I want to be able to handle an error within a "container" and not have to handle the same error in surrounding containers.
Example:
I have a "Foreach" container (call it container FEC) that contains several other containers. One of the subordinate containers is a "For Loop" (call it FLC). The FLC in turn has some nested tasks, some of which are expected to fail and therefore I want to handle in a graceful manner. The tasks that are expected to fail have a "fail" constraint that links them to a task that I want to occur when the failure occurs, and that works, but the failure is not trapped as it percolates out of the container to the FEC. I also tried to trap it with event handler, but that is also an incorrrect trail to follow.
I don't want the failure to percolate up to the FEC. I have set the max errors to a reasonable value for FLC and my "program" is not exceeding that value; however, the FEC still sees that error so it fails. How do I keep FEC from seeing the error (without upping the max errors for the FEC)?
BTW, I am using the script task to set a variable value to indicate successes or fails for those tasks where I can set the max errors to a high enough level (allow the error to occur, then let the fail/success precedent constraint pass control to the script task so that the variable can be set). This is only a partial solution.
I am new to SSIS, in fact to the MS world having been a code slinger for Java and Oracle. So far I have been very impressed with SSIS. Analogous structures that I expect to find in modern development environments have been within easy reach. This is my first serious challenge. Please help.
SCott
Trapping Validation Errors And Sending Email
Jul 20, 2006I have a DTS package that I'm moving over to SSIS. In place of migrating this package, I've choosen to recreate it. This package moves data from an Informix database to a SQL database.
In the old package the first task was to make a simple connection to the Informix database and if the task failed, it would send an email and stop the package.
The biggest reason for this is because the Unix server that I'm getting the Informix data from forces the user passwords to be reset ever 90 days. So in my old package, if I forgot to change the password and the connection started to fail it would send me an email.
In my new package, SSIS performs a validation before starting. There are a number of task that uses the connection to the Informix database. Under testing, if I put in a bad password, the validation process generates a validation error. I've tried catching this validation error using the Error Handling events but I've had no luck. I can send out errors PreValidation and PostValidation but OnError appears not to fire under a validation error.
Might anyone have any suggestions on a proper way to validate and be able to send out email notification if a connection fails? Any assistance would be appreciated.
Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?
Mar 3, 2008Hi 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.
Controlling Errors In Stored Procedure
Jul 14, 2006Hi everyone:
I need to use the "SET ROWCOUNT" statement to limit the amount of data returned to the application in a query, I know that if "SET ROWCOUNT = 0" is not specified at the end of this stored proc all the next queries will return only the amount of records specified in the initial "SET ROWCOUNT" call, so I would like to know if a I can have something like the TRY-CATCH-FINALLY statement (in SQL-92 for SQL Server 2000, not in SQL 2005) to make sure the "SET ROWCOUNT = 0" is sent at the end even if an error is raised.
Can it be done?
Thanks for any help.
How Can I Trap Errors In A Stored Procedure?
May 26, 2004My simple question:
Is there any way to prevent unimportant errors in a stored procedure from causing exceptions in my C# code? This is preventing the SqlAdapter from filling the query results into my DataSet.
The Setup:
I have a Stored Procedure in Sql Server 2000 which has a text parameter called @Xml. I send in an Xml document to process. This document contains several "records" to process. The format of the xml really isn't important.
I create a temporary table called #Results to hold the results of processing each record in the xml.
To process the xml I have a Cursor which loops over a SELECT from the xml.
For each record, the sproc attempts to make a series of INSERTs and UPDATEs inside of a transaction. Any one of these commands may fail because of constraint violations or attempts to insert NULL into non-null columns, or such. After each command I check @@ERROR. If it is not zero, I stop processing the record and rollback the transaction. The cursor loops around and tries the next record. Each time the success or failure of the transaction is recorded into the #Results table.
When the cursor is done looping I 'SELECT * FROM #Results'.
I've tested this many times in the Query Analyzer and each time, regardless of any errors, I can see the result set from the SELECT of the #Results table in the Grids tab. The Messages tab shows each of the errors that occurred.
I try to call this stored procedure using the following code:
int c = 0;
try
{
sqlAdapter.Fill( sqlDS );
}
catch( System.Data.SqlClient.SqlException )
{
c = sqlDS.Tables.Count;
}
The value of c will always be zero, if there were any errors during the execution of the stored procedure. The DataSet does not get filled, even though the stored procedure is returning a result set. This is a problem for me because I expect errors to occur, and I need to know which records from the Xml caused those errors.
Is there any way to clear the errors in my stored procedure so that they don't turn into exceptions in my code? Or, is there anyway to get the Adapter to fill the DataSet regardless of any errors that were encountered?
I've also tried this with a SqlDataReader. The reader never gets assigned to because SqlCommand.ExecuteReader() throws an exception.
Dynamic Stored Procedure Errors
Aug 17, 2004Hi
I am getting the following error
Syntax error converting the varchar value 'Select * from Residential WHERE Price BETWEEN ' to a column of data type int.
when running the following SP.
CREATE PROCEDURE testing
(
@Locationnvarchar(100)=NULL,
@TypeHomenvarchar(50)=NULL,
@MinPriceint=0,
@MaxPriceint=9999999999,
@Bedroomsnvarchar(2)=NULL,
@BathsSearchnvarchar(2)=NULL
)
AS
Declare @strSql char(255)
Set @strSql="Select * from Residential WHERE "
Set @strSql=@strSql + "Price BETWEEN " + @MinPrice + " AND " + @MaxPrice
If @Location is NOT NULL
Set @strSql=@strSql + ' AND city = ' + @Location
If @TypeHome is NOT NULL
Set @strSql=@strSql + ' AND Type = ' + @TypeHome
Set @strSql=@strSql + ' AND BDRM >= ' + @Bedrooms
Set @strSql=@strSql + ' AND BATHS <= ' + @BathsSearch
Set @strSql=@strSql + ' AND IDX = Y'
Exec(@strSql)
What is causing this error?
Thanks in advance
How To Handle Errors In Stored Procedure
Feb 23, 2004How to handle errors in stored procedure ?
View 1 Replies View RelatedStored Procedure T -sql Syntax Errors
Apr 22, 2008
Hi, i'm writing this stored procedure -
Code Snippet
USE [TheHub]
GO
create proc dbo.sp_GetInvitationsHistoryDetails(@ExecID int, @OrgID int)
as
IF (@OrgID = 0)
BEGIN
select E.EventID,E.Description as Event,E.EventDate as Date
I.Attending as [Att'g],NotAttending as [Not att'g],I.Bootcamp as [Maybe] I.Attended
FROM Invitations I INNER JOIN Events E on I.EventID=E.EventID
WHERE I.MemberID=@ExecID and NotForStats=0
ORDER BY E.EventDate DESC
END
ELSE
BEGIN
select E.EventID,E.Description as Event,E.EventDate as Date
count(*) as Invited,SUM(CONVERT(smallint,I.Attended)) AS Attended
FROM Invitations I INNER JOIN Events E on I.EventID=E.EventID
WHERE I.MemberID IN (select ID FROM Executives WHERE OrganisationID=OrgID
GROUP BY E.EventID,E.Description,E.EventDate
ORDER BY E.EventDate DESC
END
and i'm getting the following syntax errors when i check it -
Msg 102, Level 15, State 1, Procedure sp_GetInvitationsHistoryDetails, Line 10
Incorrect syntax near 'I'.
Msg 102, Level 15, State 1, Procedure sp_GetInvitationsHistoryDetails, Line 19
Incorrect syntax near 'count'.
Msg 156, Level 15, State 1, Procedure sp_GetInvitationsHistoryDetails, Line 23
Incorrect syntax near the keyword 'ORDER'.
Originally i just ran this sql from C# and it worked, obviously added the @ to the variables but it's basically the same.
Any ideas??
Getting Permission Errors On Using Webclient In CLR Stored Procedure.
Jan 11, 2008 Hello,I have a CLR stored procedure which send some values to an external URL by using the webclient, but for some reason I am getting this error.A .NET Framework error occurred during execution of user-defined routine or
aggregate "sp_LeadSend": System.Security.SecurityException: Request for
the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException: at System.Net.WebClient.UploadValues can anyone please advice how to resolve this one...I am really having a hard luck....... thanks.
Stored Procedure Not Updating Database (no Errors Appearing)
Dec 1, 2003Hi All,
I have a stored procedure (works form the SQL side). It is supposed to update a table, however it is not working, please help. What is supposed to happen is I have a delete statement deleting a payment from the payment table. When the delete button is pushed a trigger deletes the payment from the payment table and transfers it to the PaymentDeleted table. The stored procedure is supposed to update the PaymentDeleted table with the empID and reason for deleting, the delete and transfer work fine, however these 2 fields are not updated. Below is the sp and below that is the vb code. Thanks, Karen
ALTER PROCEDURE dbo.PaymentDeletedInfoTrail (@EmpID_WhoDeleted varchar(10), @Reason_Deleted varchar(255), @PmtDeletedID int)
AS
BEGIN
UPDATE dbo.PaymentDeleted
SET EmpID_WhoDeleted = @EmpID_WhoDeleted
WHERE PmtDeletedID = @PmtDeletedID
UPDATE dbo.PaymentDeleted
SET Reason_Deleted = @Reason_Deleted
WHERE PmtDeletedID = @PmtDeletedID
END
Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
Me.Validate()
If Me.IsValid Then
Dim DR As SqlClient.SqlDataReader
Dim strPmtID As String
strPmtID = lblPmtIDDel.Text
Dim MySQL As String
MySQL = "DELETE From Payment WHERE PmtID = '" & strPmtID & "'"
Dim MyCmd As New SqlClient.SqlCommand(MySQL, SqlConnection1)
SqlConnection1.Open()
DR = MyCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
SqlConnection1.Close()
Dim strDeletePmt As String
strDeletePmt = lblPmtIDDel.Text
Dim cmd As New SqlClient.SqlCommand("PaymentDeletedInfoTrail", SqlConnection1)
cmd.CommandType = CommandType.StoredProcedure
Dim myParam As SqlClient.SqlParameter
myParam = cmd.Parameters.Add(New SqlClient.SqlParameter("@PmtDeletedID", SqlDbType.Int))
myParam.Direction = ParameterDirection.Input
myParam.Value = lblPmtIDDel.Text
myParam = cmd.Parameters.Add(New SqlClient.SqlParameter("@EmpID_WhoDeleted", SqlDbType.VarChar))
myParam.Value = txtEmpIDDelete.Text
myParam = cmd.Parameters.Add(New SqlClient.SqlParameter("@Reason_Deleted", SqlDbType.VarChar))
myParam.Value = txtDeleteComments.Text
SqlConnection1.Open()
cmd.ExecuteNonQuery()
SqlConnection1.Close()
End If
Response.Redirect("PaymentVerification.aspx")
End Sub
Is There A Way To Capture All Bulk Insert Errors From Within A Stored Procedure?
Sep 28, 2007Hi all!!
I have a stored procedure that dynamically bulk loads several tables from several text files. If I encounter an error bulk loading a table in the stored procedure, all I get is the last error code produced, but if I run the actual bulk load commands through SQL Management Studio, it gives much more usable errors, which can include the column that failed to load. We have tables that exceed 150 columns (don't ask), and having this information cuts troubleshooting load errors from hours down to minutes. Onto my question..., is there any way to capture all of the errors produced by the bulk load from within a stored procedure (see examples below)?
Running this...
BULK INSERT Customers
FROM 'c: estcustomers.txt'
WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')
Produces this (notice column name at the end of the first error)...
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (CustId).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Running this (similar to code in my stored procedure)...
BEGIN TRY
BULK INSERT Customers
FROM 'c: estcustomers.txt'
WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
Produces something similar to this (which is useless)...
...Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Variable Type Errors When Calling Stored Procedure
May 12, 2008I currently have a stored procedure that is defined as follows:
CREATE PROCEDURE UpdateSyncLog
@TableName char(100),
@LastSyncDateTime datetime,
@ErrorState int OUTPUT
I am using an execute sql task to call this procedure. The connectiontype is ADO .NET and the SQLSourceType is DirectInput. The IsQueryStoredProcedure setting is false, and the following is my SQL Statement I have entered:
exec UpdateSyncLog 'myTestTable', @LastSyncDateTime, @ErrorState
Result set is set to None, as this query returns NO results (i.e. has no select statements in it that returns results).
I have two variables in this SSIS package. CurrentDateTime, and ErrorStateVal. CurrentDateTime is of Data type DateTime, the ErrorStateVal is of type Int32
The parameter mappings are as follows:
Varialbe Name=User::CurrentDateTime, Direction=Input, DateType=DateTime, Parameter Name=@LastSynDateTime, Parameter Size=-1
Variable Name=User::ErrorStateVal, Direction=Output, DateType=Int32, Parameter Name=@ErrorState, Parameter Size=-1
The error I am getting when running this execute sql task is as follows:
Error: 0xC001F009 at AS400 to SQL Full Repopulation Sync: The type of the value being assigned to variable "User::ErrorStateVal" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec UpdateSyncLog 'myTestTable', @LastSyncDateTime, @ErrorState" failed with the following error: "The type of the value being assigned to variable "User::ErrorStateVal" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
This makes no sense to me, both the SSIS variable ErrorStateVal is Int32, as well as the parameter declaration in the Execute SQL task is Int32 with direction of OUTPUT, and my stored procedure definition has @ErrorState as an integer as well.
What gives?
Calling CLR Stored Procedure From Within A CLR Table-valued Function Giving Errors
Apr 6, 2007We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table.
Code Snippet
[SqlFunction ( FillRowMethodName = "FillRow",
TableDefinition = "CustomerID nvarchar(MAX)",
SystemDataAccess = SystemDataAccessKind.Read,
DataAccess = DataAccessKind.Read,
IsDeterministic=false)]
public static IEnumerable GetWishlist () {
using (SqlConnection conn = new SqlConnection ( "Context Connection=true" )) {
List<string> myList = new List<string> ();
conn.Open ();
SqlCommand command = conn.CreateCommand ();
command.CommandText = "GetObject";
command.Parameters.AddWithValue ( "@map", "Item" );
command.CommandType = System.Data.CommandType.StoredProcedure;
using ( SqlDataReader reader = command.ExecuteReader ( System.Data.CommandBehavior.SingleRow )) {
if (reader.Read ()) {
myList.Add ( reader[0] as string );
}
}
return (IEnumerable)myList;
}
}
When command.ExecuteReader is called, I am getting an "Object not defined" error. However, the stored procedure can be used in SQL Management Studio just fine.
Code SnippetEXEC GetObject 'Item'
Is there some sorf of trick I am missing?
Thank you!
Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)
Nov 1, 2007Hi 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?
T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure
Nov 14, 2014I 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.
SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?
Jan 29, 2015I 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] ....
Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.
Sep 19, 2006I 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???
Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page
Dec 28, 2005I 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 RelatedSQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?
Sep 26, 2014I 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
System Stored Procedure Call From Within My Database Stored Procedure
Mar 28, 2007I 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
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
Errors And Stored Procs
Jul 28, 2004Ok, I've read somewhere(which I'm looking for again : ) that said that there are errors like DeadLock that kills the execution of a stored proc and there are other errors that do not necessarily kill the rest of the execution of the stored proc. Is that true? If so does anyone have any links I can read. What I'm seeing is a bad id in the foreign key and I think what is happening is that there was a unique constraint error on the first insert but the stored proc continued executing and used the bad id later on in the stored proc.
I do know I can use the @@error and will start using it but I need more proof to agree or not agree with my theory.
Thanks ahead of time for any information you can give me either way.
DMW
Trapping BCP Error From SP
Jul 20, 2005HII am using the following code in an SP, it seems like an ugly hack Ihave done to check if the BCP was working or not, I check the table itshold have filled instead of checking the error from BCP itself.Does anyone know how I can check the BCP errors directly?this is the code I am using.--------------------------------------create procedure q_spr_autoinventeringAScreate table ##q_tbl_autoinventering (ean13 varchar(13),antal decimal,signatur varchar(10),lagstalle int)exec master..xp_cmdshell"bcp ##q_tbl_autoinventering inc:outpathhd1invent.txt -t ; -Usa -P13hla -c -C "declare@invjournal int,@lagstalle int,@invdatum datetime,@ean13 varchar(15),@antal decimal,@artnr varchar(50),@lagplats varchar(20),@lagsaldo decimal,@mysubject nvarchar(4000)IF EXISTS (select * from ##q_tbl_autoinventering)begin-----------------------------------------------it seems so ugly to check the table instead of the BCP error itself soany pointers would be gladly appreciated, I tried to check @@error butthat did not seem to worked how I needed it.in short what I want is thisif bcp did not work break out of the SP and wait until called nexttime by sql server agent. and by not working I only mean that therewas no file to fetch, if there is a file to fetch and the table iscreated I have lots of checks in the SP to make sure the values arecorrectrgdsMatt
View 1 Replies View RelatedTimeout Errors When Execution Long Running Procedure
Apr 21, 2007When I execute a long running procedure, I get timeout errors when other users try to execute other procedures with UPDATE or INSERT statements.
I suspect that the other procedures are trying to execute DML statements on tables that are locked by the long running procedure.
I have a sharred trigger on all my tables that creates and updates records in tables AuditLogDetails and AuditLogParent for keeping a log of modifications. I suspect that tables AuditoLogDetails and AuditLogParent are locked by the long running procedure.
How can I change the LOCKING behavior of the long running procedure to fix the time out errors that I get?
The long running procedure is displayed below.
ALTER PROCEDURE [dbo].[spPostPresenceToHistory2]
@PostDate DateTime,
@Department Int,
@Division Int,
@Testing Bit = 0,
@XDoc xml OUTPUT,
@XDoc2 xml OUTPUT,
@ModifierID varchar(20),
@Comment varchar(200)
AS
BEGIN
BEGIN TRANSACTION
DECLARE @PostCount Int,@PreCount Int,@DiffCount Int
IF @Testing=1
BEGIN
PRINT 'DELETE FROM History2_Presence'
EXEC sp_SetPostingProperties 'History2_Presence',@ModifierID,@Comment
SELECT @PreCount=COUNT(*) FROM History2_Presence
IF EXISTS(SELECT E.ID FROM History2_Personel E WHERE E.PostDate=@PostDate)
BEGIN
DELETE FROM History2_Presence FROM History2_Presence H
INNER JOIN History2_Personel Ps ON H.Personel_ID=Ps.ID AND Ps.PostDate=@PostDate
WHERE Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division AND H.Date_de_Presence=@PostDate
AND EXISTS (SELECT P.ID FROM Presence P
WHERE (P.Date_de_Presence=@PostDate AND P.Personel_ID=H.Personel_ID AND P.Travaille_de_Jour=H.Travaille_de_Jour) OR (P.ID=H.ID))
END
ELSE
BEGIN
DELETE FROM History2_Presence FROM History2_Presence H
INNER JOIN Personel As Ps ON H.Personel_ID=Ps.ID
WHERE Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division AND H.Date_de_Presence=@PostDate
AND EXISTS (SELECT P.ID FROM Presence P
WHERE (P.Date_de_Presence=@PostDate AND P.Personel_ID=H.Personel_ID AND P.Travaille_de_Jour=H.Travaille_de_Jour) OR (P.ID=H.ID))
END
SELECT @PostCount=COUNT(*) FROM History2_Presence
IF @PreCount<>@PostCount
BEGIN
SET @DiffCount = @PreCount-@PostCount
SET @XDoc2.modify('
insert <Table Name="History2_Presence" RecordDeleted="{ sql:variable("@DiffCount") }"/> as last into /Deleted_Records[1]
')
END
END
PRINT 'INSERT INTO History2_Presence'
EXEC sp_SetPostingProperties 'History2_Presence',@ModifierID,@Comment
SELECT @PreCount=COUNT(*) FROM History2_Presence
INSERT INTO [dbo].[History2_Presence]
([ID]
,[User_ID]
,[Personel_ID]
,[Date_de_Presence]
,[Category_Motif_ID]
,[DateEntre]
,[Category_TypeDePresence_ID]
,[Travaille_de_Jour]
,[Heur_Supplementaire_Travaille]
,[prime_transport]
,[Tarif]
,[Jour_Travaille]
,[Montant_Supplementaire_Par_Heur]
,[Salair_par_Jour]
,[Salair_Minimum]
,[IsAutomaticRec])
SELECT [P].[ID]
,[P].[User_ID]
,[P].[Personel_ID]
,[P].[Date_de_Presence]
,[P].[Category_Motif_ID]
,[P].[DateEntre]
,[P].[Category_TypeDePresence_ID]
,[P].[Travaille_de_Jour]
,[P].[Heur_Supplementaire_Travaille]
,[P].[prime_transport]
,[P].[Tarif]
,[P].[Jour_Travaille]
,[P].[Montant_Supplementaire_Par_Heur]
,[P].[Salair_par_Jour]
,[P].[Salair_Minimum]
,[P].[IsAutomaticRec]
FROM [dbo].[Presence] AS P
INNER JOIN Personel As Ps ON P.Personel_ID=Ps.ID
WHERE P.Date_de_Presence=@PostDate AND Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division
AND NOT EXISTS
(SELECT HP.ID FROM History2_Presence HP
WHERE (HP.Date_de_Presence=@PostDate AND HP.Personel_ID=P.Personel_ID AND HP.Travaille_de_Jour=P.Travaille_de_Jour) OR (HP.ID=P.ID))
SELECT @PostCount=COUNT(*) FROM History2_Presence
IF @PreCount<>@PostCount
BEGIN
SET @DiffCount = @PostCount-@PreCount
SET @xdoc.modify('
insert <Table Name="History2_Presence" RecordAdded="{ sql:variable("@DiffCount") }"/> as last into /Inserted_Records[1]
')
END
IF @Testing=0
BEGIN
PRINT 'DELETE FROM Presence'
EXEC sp_SetPostingProperties 'Presence',@ModifierID,@Comment
SELECT @PreCount=COUNT(*) FROM Presence
DELETE FROM Presence FROM Presence P
INNER JOIN Personel As Ps ON P.Personel_ID=Ps.ID
WHERE P.Date_de_Presence=@PostDate AND Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division
AND EXISTS
(SELECT HP.ID FROM History2_Presence HP
WHERE (HP.Date_de_Presence=@PostDate AND HP.Personel_ID=P.Personel_ID AND HP.Travaille_de_Jour=P.Travaille_de_Jour) OR (HP.ID=P.ID))
SELECT @PostCount=COUNT(*) FROM Presence
IF @PreCount<>@PostCount
BEGIN
SET @DiffCount = @PreCount-@PostCount
SET @XDoc2.modify('
insert <Table Name="Presence" RecordDeleted="{ sql:variable("@DiffCount") }"/> as last into /Deleted_Records[1]
')
END
END
COMMIT TRANSACTION
END