I feel like I'm missing something obvious here, but I'm stumped...
I have a stored procedure with code that looks like:
INSERT INTO MyTableA ( ...fields... ) VALUES (...values...)
IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION;
RAISERROR('An error occurred in the stored proc.', 16, 1);
RETURN(1);
END
--FORCING AN ERROR AT THE END FOR TESTING PURPOSES
RAISERROR('Proc Successful',16,1)
On MyTableA, there is a trigger that loops through the inserted data and
stops the insert in certain circumstances, returning an error:
IF (some criteria)
BEGIN
ROLLBACK
RAISERROR('An error occurred in the trigger.',16,1)
RETURN
END
When I call the stored procedure from VB (connecting via RDO) with
error-causing data, the trigger successfully stops the insert, and adds the
trigger-error-msg to the errors collection, but it does NOT seem to create
an error situation back in the stored procedure. The procedure finishes up
with the "Proc Successful" message, so that when I iterate through the
errors collection back in VB, I have "Proc Successful" followed by "An error
occurred in the trigger."
Is there some way I'm not finding to have the calling procedure recognize
that a raiserror occurred in the trigger and behave appropriately for an
error situation?
I writing a unit test which has one stored proc calling data from another stored proc. Each time I run dbo.ut_wbTestxxxxReturns_EntityTest I get a severe uncatchable error...most common cause is a trigger error. I have checked and rechecked the columns in both of the temp tables created. Any ideas as to why the error is occurring?
--Table being called.
ALTER PROCEDURE dbo.wbGetxxxxxUserReturns
@nxxxxtyId smallint,
@sxxxxxxxxUser varchar(32),
@sxxxxName varchar(32)
AS
SET NOCOUNT ON
CREATE TABLE #Scorecard_Returns
( NAME_COL varchar(64), ACCT_ID int,
ACCT_NUMBER varchar(10),
ENTITY_ID smallint,
NAME varchar(100),
ID int,
NUM_ACCOUNT int,
A_OFFICER varchar(30),
I_OFFICER varchar(30),
B_CODE varchar(30),
I_OBJ varchar(03),
LAST_MONTH real,
LAST_3MONTHS real,
IS int
)
ALTER PROCEDURE dbo.ut_wbTestxxxxReturns_EntityTest
I am fairly new to using triggers and stored procs in SQL Server and could use some help.
Scenario:
I have a table on SQL Server database running on a Windows server (of course) that contains information about articles. A second server in the shop is set up as a LAMP (Linux, Apache, MySQL, PHP) box. What I would like to do is any time the SQL server table is updated or a record is added, tables in MySQL would then be updated to reflect the changes on the first server. My thought is to try and do this with triggers and a stored proc.
Questions: 1) Assuming MySQL can be accessed through ODBC, can an ODBC connection be set up inside a stored proc or trigger, or would this have to be done through the Windows ODBC Data Sources tool in the control panel?
2) Can a Stored Proc be used in a trigger?
3) Can a Stored Proc call an outside function, such as an API call for third party software?
I am trying to create a trigger that fires after a user logon and logoff and does the following:
creates a new user deletes data from old temp table
Then I need to create a stored procedure that executes dynamically to drop old users remove the old user account access
We are running SQL Server 2000. Since I am new to T-SQL programming could anyone help point me in the right direction? Can I write dynamic SQL in a trigger to do these things?
I have a database trigger that is set to call a CLR trigger/stored proc when a certain field in a table is updated. The issue is that if i execute the stored proc manually in enterprise studio, it works perfectly but the same call made through the trigger does not go through. A few more details -
I have CLR integration enabled on the sql server.
The dbo has UNSAFE ASSEMBLY rights
I have the both the assembly and the serialized dll imported in the database.
Here's the definition of the stored proc -
CREATE PROCEDURE [dbo].[WriteXMLNotification]
@TaskID [nvarchar](20)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [DataInterfaceWebServices].[TaskUpdateXMLWriter.WriteXMLNotification].[run]
and the trigger -
CREATE TRIGGER [dbo].[tr_Task_U]
ON [dbo].[_Task]
FOR UPDATE, INSERT AS
IF UPDATE (TaskType_Status) OR UPDATE (TaskType) OR UPDATE (TaskType_SubType1)
BEGIN
SET NOCOUNT ON;
DECLARE @status AS INT
DECLARE @taskType AS INT
DECLARE @taskSubType AS INT
DECLARE @taskID as sysname
DECLARE @cmd as sysname
DECLARE @parentTask as sysname
DECLARE @NotificationXMLTaskID as sysname
SELECT @status = [TaskType_Status] FROM inserted
SELECT @taskType = [TaskType] FROM inserted
SELECT @taskSubType = [TaskType_SubType1] FROM inserted
SELECT @taskID = [TaskID] FROM inserted
SELECT @parentTask = [Parent_TaskID] FROM inserted
SELECT @NotificationXMLTaskID = [MCCTaskID] FROM _TaskNotificationXML WHERE [MCCTaskID] = @parentTask
IF (@status = 2602) AND (@taskType = 2282) AND (@taskSubType = 19500)
BEGIN
exec WriteXMLNotification @taskID;
END
ELSE IF (@taskType = 2285) AND (@parentTask IS NOT NULL) AND (@NotificationXMLTaskID IS NOT NULL)
BEGIN
exec WriteXMLNotification @parentTask;
END
END
I stepped into the trigger and it seems to execute the line " exec WriteXMLNotification @taskID;" but nothing happens, but if I run that same line manually, it works. Could it be that the impersonation by the EXECUTE AS clause is causing it to fail?
First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.
Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.
Hi,I am using sql server 2005 and i am having table with a column which has unique key constraint.So if the user tries to enter the duplicate value for that particular column and try to save it ,it errors out in runtime..how do i raise the user friendly error message in this DB error, when user tries to save it.Thanks
I am trying to create a page that adds users to a MS SQL database. In doing so, I have run into a couple errors that I can't seem to get past. I am hoping that I could get some assistance with them.
Error from SQL Debug: --- Server: Msg 295, Level 16, State 3, Procedure AdminAddUser, Line 65 [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type. ---
Error from page execution: --- Exception Details: System.Data.OleDb.OleDbException: Error converting data type varchar to numeric.
Source Error:
Line 77: cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip") Line 78: Line 79: cmd.ExecuteNonQuery() ---
Below is what I currently have for my stored procedure and the pertinent code from the page itself.
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 the following stored proc that is providing the following error - Server: Msg 156, Level 15, State 1, Line 79[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'ORDER'. The sp works fine if the DateOfBirth elements are removed. I think it is something to do with the way in which the date variable is incorporated into the string that will be executed by the EXEC command but I'm unsure as to what is wrong. CREATE PROCEDURE dbo.GetPersonsByName (@FirstName varchar(50)=NULL, @FamilyName varchar(50)=NULL, @DateOfBirth datetime=NULL) AS EXEC ('SELECT PersonId, Title, FirstName, FamilyName , AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeath, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, Hospital, HospitalNo, AltHospital, AltHospitalNo, EthnicGroup, Citizenship, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated FROM vw_GetPersons WHERE FirstName LIKE ''%' + @FirstName + '%'' AND FamilyName LIKE ''%' + @FamilyName + '%'' AND DateOfBirth = '+ @DateOfBirth +' ORDER BY FamilyName, FirstName') GO
Can anyone see an error here? I am getting a general exception when call this and it is driving me crazy. What am I missing? The error is.....{"Incorrect syntax near 'UpdateStaffDept'." ... Any help is greatly appreciated! Below is the code calling the proc Thank you! set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[UpdateStaffDept] @Co_Id INT, @CardNo VARCHAR(50), @DeptNo VARCHAR(50)
AS SET NOCOUNT ON
DECLARE @Error INT
SELECT @Error = @@Error IF @Error = 0 BEGIN
BEGIN TRANSACTION UPDATE Staff SET Dept_No = @DeptNo WHERE Co_Id = @Co_Id AND PC_Id IN (SELECT PC_ID FROM PC_Number WHERE PC_Number = @CardNo)
SELECT @Error = @@Error IF @Error <> 0 BEGIN ROLLBACK TRANSACTION RETURN @Error END ELSE COMMIT TRANSACTION
END
Dim seldeptno As String seldeptno = ddlEditPosDept.SelectedValue Dim cmdChnDept As SqlCommand cmdChnDept = New SqlCommand("UpdateStaffDept", conEditPos) cmdChnDept.Parameters.Add(New SqlParameter("@Co_Id", SqlDbType.Int 10)) cmdChnDept.Parameters("@Co_Id").Value = compid cmdChnDept.Parameters.Add(New SqlParameter("@CardNo", SqlDbType.VarChar, 50)) cmdChnDept.Parameters("@CardNo").Value = lblEditPosCardNo.Text cmdChnDept.Parameters.Add(New SqlParameter("@DeptNo", SqlDbType.VarChar, 50)) cmdChnDept.Parameters("@DeptNo").Value = seldeptno cmdChnDept.ExecuteNonQuery()
I come from an Access/VB background where in an error trapping routine or testing for an error your statement "exit sub" ended the process. WHen using a stored procedure and you come across an error what is the best method to exit/end the stored proc?
I have a stored proc that loops through records using a cursor. If during the loop an error occurs i need to trap the error and write it to a log file. Error handling needs to be placed in the update and insert sections and the error that occurs needs to get recorded along current row from the cursor.
Here is the code: declare @tier_id int, @tier_desctext varchar(50), @tier_shortdesc varchar(50), @long_name varchar(50), @short_name varchar(50), @rec_count int
--Cursor for UES data DECLARE cr_ues_tier INSENSITIVE CURSOR FOR SELECT DISTINCT tier_id, tier_desctext, tier_shortdesc FROM "dbsourcedayoldprod".ues.dbo.Tiers
OPEN cr_ues_tier
--Select cursor values into local variables FETCH NEXT FROM cr_ues_tier INTO @tier_id, @tier_desctext, @tier_shortdesc WHILE (@@FETCH_STATUS <> -1) BEGIN
--Set Data Mart variables SELECT @long_name = long_name, @short_name = short_name FROM uesrpt..coverage_level WHERE ues_tier_id = @tier_id
--Set the record counter SET @rec_count = (select ues_tier_id from uesrpt..coverage_level where ues_tier_id = @tier_id)
--Are there exsisting records if so go to the update section IF @rec_count <> 0
--Update Data Mart values if they have changed BEGIN IF @long_name <> @tier_desctext OR (@long_name IS NULL AND @tier_desctext IS NOT NULL) OR (@long_name IS NOT NULL AND @tier_desctext IS NULL)
OR @short_name <> @tier_shortdesc OR (@short_name IS NULL AND @tier_shortdesc IS NOT NULL) OR (@short_name IS NOT NULL AND @tier_shortdesc IS NULL)
UPDATE uesrpt..coverage_level SET long_name = @tier_desctext, short_name = @tier_shortdesc WHERE ues_tier_id = @tier_id END
--Rows don't exsist in the Data Mart - Insert new rows ELSE INSERT INTO uesrpt..coverage_level (ues_tier_id, long_name, short_name) SELECT @tier_id, @tier_desctext, @tier_shortdesc
--Get next row from UES cursor FETCH NEXT FROM cr_ues_tier INTO @tier_id, @tier_desctext, @tier_shortdesc END
This query keeps giving me a Primary Key violation error and I am not sure what it is getting at. maybe you can help?
truncate table tbl_rtv_cosmetic_dif go insert tbl_RTV_COSMETIC_DIF select d.fisc_yr ,d.fisc_pd ,d.fisc_wk ,d.fisc_dy ,substring(a.long_sku,1,4) as DeptNo ,substring(a.long_sku,7,3) as VendorNo ,substring(a.long_sku,10,5) as MarkStyleNo ,a.Loc as LocNo ,convert(int,a.RsnCd) as ReasonCode ,min(a.EAN_Nbr) as UPCEANNo ,coalesce(i.own_rtl,0) as OwnedCur ,sum(convert(int,a.units)) as UPCUnits
from november a inner join generalinfo..tbl_fisc_date d on a.date = d.cal_date
left outer join itemdata..tbl_item_import_history i on substring(a.long_sku,1,4) = i.dept and substring(a.long_sku,7,3) = i.vendor and substring(a.long_sku,10,5) = i.mrk_style and d.fisc_yr = i.amc_yr and d.fisc_pd = i.amc_pd and d.fisc_wk = i.amc_wk
group by fisc_yr,fisc_pd,fisc_wk,fisc_dy ,substring(long_sku,1,4),substring(long_sku,7,3),substring(long_sku,10,5) ,loc,rsncd,own_rtl
I have a table I insert a record into to give access to a user. It usesprimary keys so duplicates are not allowed, so trying to add a recordfor a user more than once is not allowed.In my .NET programs, sometimes it's easier to let the user select agroup of people to give access to eben if some of them may already haveit.Of course this throws an exception an an error message. Now I couldcatch and ignore the message in .NET for this operation but then I'mstuck if something is genuinely wrong.So is there a way to do this? :In my stored procedure determine if an error occured because of aduplicate key and somehow not cause an exception to be returned toADO.NET in that case?
Hi all,I have a sproc that uses OpenRowset to an Oracle db. If OpenRowseterrors, it terminates the procedure. I need it to continueprocessing. Is there any workaround for this?ThanksPachydermitis
When I try to deploy this managed stored proc assembly on SQL Server 2005 I get this error: Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x8007000e. You may fix the problem and try again later I looked on the net but found no documentation about it. I know the CLR v2.0 is working as I have some ASP.NET apps running from this server that use it. Any ideas?
i try create this example:http://www.codeproject.com/KB/webforms/ReportViewer.aspxI have Northwind database added in the SQL server management and i select Northwind databse in drop box and I push Execute!ALTER PROCEDURE ShowProductByCategory(@CategoryName nvarchar(15) )ASSELECT Categories.CategoryName, Products.ProductName, Products.UnitPrice, Products.UnitsInStockFROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryIDWHERE CategoryName=@CategoryNameRETURNbut error is:Msg 208, Level 16, State 6, Procedure ShowProductByCategory, Line 11Invalid object name 'ShowProductByCategory'.on web not so clear what is issue pls. help
I have a Stored Proc that is called by a SQL Job in SQL Server 2000. This stored proc deadlocks once every couple of days. I'm looking into using the @@error and try to doing a waitfor .5 sec and try the transaction again. While looking around google I've come across a few articles stating that a deadlock inside a Stored Proc will stop all execution of the stored proc so I will not be able doing any error handling. Is this true? Does anyone have any experience that could help me out?
I know the best solution would be to resolve why I get a deadlock. We are currently looking into that but until we can resolve those issues I would like to get some type of error handling in place if possible.
Hi,I a stored procedure that inserts a record into a table asbelow.The insert works OK, but if the insert violates a uniqueindewx constraint on one of the columns, the proc terminatesimmediately, and does NOT execute the 'if @@ERROR <> 0'statement.Am I doing something wrong, or do I need to set an attributesomewhere?tia,Billbegin traninsert into Users(UserName, UserPWD, Lname, Fname, UserDesc)values (@userName, @userPWD, @lname, @fname, @userDesc)if @@ERROR <> 0beginrollback transet @returnCode = -2set @errMsg = 'SQL error '+ convert(varchar(6), @@ERROR)+ ' occurred adding user '+ @userNameend
I am trying to add a data driven subscription to my report. When I get to the screen to enter the Delivery Query, I enter the stored procedure (GetStatusReportData) in the query box. When i try to validate, I get a "Query is not valid" error. If I replace the stored procedure name with a generic query (select * from jobs), it works fine.
Is there some sort of syntax I need to use to enter a stored procedure here? I have used "exec GetStatusReportData" and that did not work either.
I am attempting to compile a stored proc that contains SQL statements that access databases across different SQL servers. I am getting the following error:
"Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query."
Does this mean that I need to put the necessary statements (Set ANSI_NULLS ON etc.) in my stored proc or that I need to configure my SQL Server differently somehow? I tried the former without success.
Finally found what is causing my .net c# service application to return with Error 18456 (NT Authority/anonymous logon" severity 14 State 11.
It is connecting to a sql server db in another machine and running a stored procedure in that db. I found out that if I remove that two statements that create temp tables (with select INTO), the stored procedure executes without a problem.
Note that this stored procedure updates the tables does not have problems updated tables that are in the DB. I gets upset when creating temp tables though.
I cannot do away with those temp tables as they are needed in the calculation. How can I fix this problem?
Why would creating temp tables remotely cause this error? What am I missing in my set up. My service application connects with the integrated security = true. My service process installer has the account set to "User". What am I missing.
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 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.
I wanted to execute a proc whenever a new login or a login is droped is created on the server. I was trying for CREATE_LOGIN in the following code but not able to capture the loginname and pass it to the proc.
Server object Trigger:- USE [master] GO /****** Object: DdlTrigger [LoginCreateTrigger] Script Date: 7/20/2015 3:41:06 AM ******/ SET ANSI_NULLS ON GO
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
