Error Handling In Stored Proc

Jan 16, 2003

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

CLOSE cr_ues_tier
DEALLOCATE cr_ues_tier
GO

Any help is appreciated.
Thanks.

View 1 Replies


ADVERTISEMENT

Stored Proc Error Handling

Jul 20, 2005

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

View 1 Replies View Related

Stored Proc And Deadlock Error Handling

Jun 4, 2004

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.

Thank you,
DMW

View 8 Replies View Related

Help: Prob Handling SQL Error In Stored Proc

Jul 20, 2005

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

View 1 Replies View Related

Error Handling In MSSQL - If Error During Call Remote Stored Prcedure I Need SQL Code To Continue...

Jul 20, 2005

Hi All,I want to catch the next MSSQL error in my SQL code with following continuecalculationsServer: Msg 17, Level 16, State 1, Line 1SQL Server does not exist or access denied.If REMOTE_SERVER_1 is inaccessible (as in (a) below) the executing of SQLwill not continue with (b) - I need the code in (b) to run despite whetherthe previous exec was successful or not - Any ideas?begin transaction(a) exec REMOTE_SERVER_1...bankinsert '1' , '1' , 1 , 0 , 0(b) print @@errorcommit transactionwhere REMOTE_SERVER_1 is link to server created byEXEC sp_addlinkedserver @server = 'REMOTE_SERVER_1', @srvproduct = '',@provider = 'SQLOLEDB', @datasrc = 'MYCOMP1', @catalog = 'mirror2'EXEC sp_addlinkedsrvlogin @rmtsrvname = 'REMOTE_SERVER_1', .....Exec sp_serveroption 'REMOTE_SERVER_1', 'data access', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'rpc', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'rpc out', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'collation compatible', 'true'Any help will be greatly appreciated

View 1 Replies View Related

Error Handling In A Stored Procedure

Dec 19, 2003

I have an application where I am bcping a file into a holder table and then looping through each of the rows in that table to add it into the main table for the app. If the data is improperly formatted (ie someone accidently enters 39.Y6 instead of 39.66), we still want to keep it in the system, so we can update the bad fields manually and then import it into the system and keep going through the loop so that if there are 50 rows to import and only one of them is bad, the other 49 will still get imported fine.

I am putting each field from the holder table into a variable and passing them into an existing stored procedure that updates the main table. I had originally checked each one of those variables that had to be numeric or a date to make sure that it was correct before passing them into the procedure but there are about 30 fields so this made the application run unacceptably slow. I then just checked the @@Error value after passing them into the stored procedure (tried after the sp and after the INSERT statement inside the sp) to get that this row didn't insert correctly and move onto the next one. The problem is that the "Error converting data type varchar to numeric" doesn't seem to be handled by the error handling and just bombs the whole thing, so none of the subsequent rows or processing is done.

Is there any way to handle this error and continue the processing without the whole stored procedure crashing? The data entry is being outsourced to India (grrr...), so I don't have any control over checking the data when they enter it and have to do it from within my application on the database side.

View 1 Replies View Related

Error Handling/ Stored Procs

Jul 8, 2005

Hi,     I'm doing some fairly basic updates with stored procedures. 99% of them affect one row. I've jsut discovered that I can't get the value of @@rowcount and @@error to return as output parameters (if I check one, the other one gets reset!). My theory is then to return the rowcount and if it's not = 1, then I know I've had a problem. If I begin a transaction in vb.net and call each proc in the required order and check each step that rowcount = 1, is this a reliable method of ensuring no errors have occurred?Thanks.

View 2 Replies View Related

Error Handling In SQL Stored Procedure

Mar 12, 2008

Hi all,

I have a huge stored procedure. part of the query is, the cursor is open and it consist of the implementation for subquery, I want to do an implementation if this subquery return more than one value, catch the error, but continue the cursor operation.

my SQL procedure part looks like:

---code to open the cursor here
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @attachmentCount=count(metaDataID)
FROM View_1
WHERE parentMetaDataID='' + ( SELECT metaDataID
FROM View_1
WHERE metaDataStorageID = @metaStorageID AND parentMetaDataID='0') + '' AND
metaDataContentTypeID=@metaDataContentTypeID

--error handling
DECLARE @err int
SELECT @err = @@error
IF @err <> 0
BEGIN
FETCH NEXT FROM CursorDataStorageID INTO @metaStorageID
CONTINUE
END

FETCH NEXT FROM CursorDataStorageID INTO @metaStorageID
END

-----------------

when I execute this query in SQL management studio, it does return me the no of rows with the msg:

Msg 512, Level 16, State 1, Procedure sp_AdvanceSearchHugeExecution, Line 522

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


But when the stored procedure is called from the within the code ASP.Net, to fill the dataset, I get an exception.

my C# code is simple to fill the dataset using data adapter in try catch block.

Can anyone please suggest how will I able to fill dataset successfully overcoming this mesg?

Thanks a lot.

View 2 Replies View Related

Stored Procedure Error Handling

Aug 25, 2005

OK, i'm trying to do some error checking on stored procedures and amfollowing the advise in Erland Sommarskog's 'Implementing ErrorHandling with Stored Procedures' document.Can anybody help with my stored procedures and why it keeps erroring atthe '-- Create new Address Detail stage'? The errorCode value that isbeing return in my web app is 0, so i'm not even sure why it's evenraising the error!!Rather than executing the INSERT INTO AddressDetail in myCreateSupplier procedure and checking for errors, i'd like to be ableexecute a CreateAddressDetail SP, so that i can reuse it throughout myweb app.New suppliers must have a contact address associated with it, so ifthere's an error creating the suppliers address, i need myCreateSupplier stored procedure to ROLLBACK and not create the newsupplier. That's why i'm not doing two separate calls to the proceduresfrom my app code.Any suggestions are most appreciated.Many thanksDan Williams.CREATE PROCEDURE CreateSupplier@supplierName varchar(50),@userId bigint,@address varchar(50),@town varchar(50),@county varchar(50),@postCode varchar(15),@contactName varchar(50)ASBEGINDECLARE @newSupplierId as bigintDECLARE @newAddressDetailId as bigintDECLARE @errorCode as bigintSET NOCOUNT ONBEGIN TRANINSERT INTO Supplier(supplierName, accOpenedBy, accOpenedDate)VALUES (@supplierName, @userId, getDate())SET @newSupplierId = SCOPE_IDENTITY()-- Check for an error creating new supplierSELECT @errorCode = @@ERRORIF (@errorCode <> 0) BEGIN ROLLBACK TRAN RAISERROR ('Error creatingsupplier',16,1) RETURN @errorCode END-- Create new Address DetailEXEC @errorCode = CreateAddressDetail @address, @town, @county,@postCode, @contactName, @newAddressDetailId OUTPUTSELECT @errorCode = coalesce(nullif(@errorCode, 0), @@error)if @errorCode <> 0 BEGIN ROLLBACK TRAN RAISERROR ('Error creatingaddress. ErrorCode = %d',16, @errorCode) RETURN @errorCode ENDCOMMIT TRANSET NOCOUNT OFFRETURN @newSupplierIdENDGOCREATE PROCEDURE CreateAddressDetail@address varchar(50),@town varchar(50),@county varchar(50),@postCode varchar(15),@contactName varchar(50),@newAddressDetailId bigint OUTPUTASBEGIN-- Create new AddressDetailDECLARE @errorCode as bigintSET NOCOUNT ONBEGIN TRANINSERT INTO AddressDetail(address, town, county, postCode, contactName)VALUES (@address, @town, @county, @postCode, @contactName)SET @newAddressDetailId = SCOPE_IDENTITY()-- Check for an error creating new addressSELECT @errorCode = @@ERRORIF (@errorCode <> 0)BEGINRAISERROR ('Error creating new address detail',16,1)ROLLBACK TRANENDELSECOMMIT TRANSET NOCOUNT OFFRETURN @newAddressDetailIdENDGO

View 9 Replies View Related

Confused About Stored Procedure Error Handling

Apr 2, 2002

I am a little confused about something and I am hoping that someone out there has some insight.

Within a stored procedure, I have error handling code just after an INSERT statement. the error handling code basically checks @@ERROR, and if non-zero, logs a message to the NT Event Log. One of the things I want to be able to handle is if the transaction log on the database is full, and the INSERT fails for this reason. However, it appears that for this particular situation (and others I've seen in the past), the stored procedure aborts at the INSERT statement. Never makes it to the error handling code. Is there any way to control this behaviour?

I really need to put in place a failure scenario, if the insert fails, but i can't do that if the stored proc. just aborts on certain types of insert failures.

View 1 Replies View Related

Error Handling In Stored Procedure AND Checking

Jan 3, 2008

I am stumped on the error reporting with sql server. I was told ineed to return @SQLCode(code showing if successful or not) and@ErrMsg(and the message returned). I am clueless on this.I wrote this procedure:
Code: ( text )

View 4 Replies View Related

Stored Procs, Error Handling And Return Values

Sep 24, 2007

Can anyone let me know the prefered method for handling stored procedure errors. I usually trap the error in the stored proc and then return a value using an output parameter
e.g stored proc
if @@error <> 0 beginset @returnValue = -1returnend
c#com.Parameters.Add("@returnValue", SqlDbType.Int).Direction = ParameterDirection.Output;con.Open();com.ExecuteNonQuery();
int result = (int)com.Parameters[0].Value;
if (result == -1){//throw exception}else{//do whatever}

View 2 Replies View Related

Linked Server Error Handling In Stored Procs.

Jun 29, 2004

I would like to capture any errors thrown by linked server in my stored procedure, How do I do this? eg: Select statement in sp to remote server fails if login credentials were changed for some reason on remote server. @@error is not able to capture this error and stored procedure is terminated when this type of error occurs.

View 1 Replies View Related

SQL Server 2008 :: Error Handling In Called Stored Procedures?

Apr 14, 2015

I have one main stored procedure. It calls other 10 stored procedures by giving input parameters.

Do I want to implement Begin Try/Begin catch in each sub procedures or in main procedure only.

View 2 Replies View Related

Stored Proc Error: Error Converting Character String To Smalldatetime Data Type

Feb 12, 2005

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.

Stored Procedure:
---
CREATE PROCEDURE dbo.AdminAddUser( @username varchar(100),
@password varchar(100),
@email varchar(100),
@acct_type varchar(100),
@realname varchar(100),
@billname varchar(100),
@addr1 varchar(100),
@addr2 varchar(100),
@city varchar(100),
@state varchar(100),
@country varchar(100),
@zip varchar(100),
@memo varchar(100) )
AS
BEGIN TRAN

--
-- Returns 1 if successful
-- 2 if username already exists
-- 0 if there was an error adding the user
--

SET NOCOUNT ON
DECLARE @error int, @rowcount int

--
-- Make sure that there isn't already a user with this username
--

SELECT userID
FROM users
WHERE username=@username

SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT

IF @error <> 0 OR @rowcount > 0 BEGIN
ROLLBACK TRAN
RETURN 2
END

--
-- Set expiration date
--

DECLARE @expDate AS smalldatetime

IF @acct_type = "new_1yr" BEGIN
SET @expDate = DATEADD( yyyy, 1, GETDATE() )
END

IF @acct_type = "new_life" BEGIN
SET @expDate = DATEADD( yyyy, 40, GETDATE() )
END

DECLARE @paidCopies AS decimal
SET @paidCopies = 5


--
-- Add this user to the database
--

IF @acct_type <> "new" BEGIN

INSERT INTO users (userName, userPassword, userEmail, userJoinDate,
userPaidCopies, userExpirationDate, userLastPaidDate,
userBname, userRealName, userAddr1, userAddr2, userCity,
userState, userCountry, userZip, userMemo )
VALUES (@username, @password, @email, GETDATE(), @realname, @billname,
@paidCopies, @expDate, GETDATE(), @addr1, @addr2, @city, @state, @country, @zip, @memo )

SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT

IF @error <> 0 OR @rowcount < 1 BEGIN
ROLLBACK TRAN
RETURN 0
END

END

IF @acct_type = "new" BEGIN

INSERT INTO users (userName, userPassword, userEmail, userJoinDate,
userBname, userRealName, userAddr1, userAddr2, userCity,
userState, userCountry, userZip, userMemo )
VALUES (@username, @password, @email, GETDATE(), @realname, @billname,
@addr1, @addr2, @city, @state, @country, @zip, @memo )

SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT

IF @error <> 0 OR @rowcount < 1 BEGIN
ROLLBACK TRAN
RETURN 0
END

END



COMMIT TRAN
RETURN 1
GO
---


Page Code:
---
Sub AddUser(Sender as Object, e as EventArgs)

Dim db_conn_str As String
Dim db_conn As OleDbConnection
Dim resultAs Int32
Dim cmdAs OleDbCommand


db_conn_str = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=xxxxx; User ID=xxxxx; PWD=xxxxx;"
db_conn = New OleDbConnection( db_conn_str )
db_conn.Open()

cmd = new OleDbCommand( "AdminAddUser", db_conn )
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add( "result", OleDbType.Integer ).Direction = ParameterDirection.ReturnValue

cmd.Parameters.Add( "@username", OleDbType.VarChar, 100 ).Value = Request.Form("userName")
cmd.Parameters.Add( "@password", OleDbType.VarChar, 100 ).Value = Request.Form("userPass")
cmd.Parameters.Add( "@email", OleDbType.VarChar, 100 ).Value = Request.Form("userEmail")
cmd.Parameters.Add( "@acct_type", OleDbType.VarChar, 100 ).Value = Request.Form("userEmail")
cmd.Parameters.Add( "@realname", OleDbType.VarChar, 100 ).Value = Request.Form("userRealName")
cmd.Parameters.Add( "@billname", OleDbType.VarChar, 100 ).Value = Request.Form("userBname")
cmd.Parameters.Add( "@addr1", OleDbType.VarChar, 100 ).Value = Request.Form("userAddr1")
cmd.Parameters.Add( "@addr2", OleDbType.VarChar, 100 ).Value = Request.Form("userAddr2")
cmd.Parameters.Add( "@city", OleDbType.VarChar, 100 ).Value = Request.Form("userCity")
cmd.Parameters.Add( "@state", OleDbType.VarChar, 100 ).Value = Request.Form("userState")
cmd.Parameters.Add( "@country", OleDbType.VarChar, 100 ).Value = Request.Form("userCountry")
cmd.Parameters.Add( "@memo", OleDbType.VarChar, 100 ).Value = Request.Form("userMemo")
cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip")

cmd.ExecuteNonQuery()

(...)

---

View 1 Replies View Related

Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.

Feb 13, 2008

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 
 

View 3 Replies View Related

SQL Error When Using EXEC In Stored Proc

Jul 10, 2006

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

View 12 Replies View Related

Stored Proc Syntax Error?

Mar 14, 2006

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()

View 3 Replies View Related

Stored Proc Error And Exit

Sep 15, 2000

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?

View 3 Replies View Related

Constraint Error In Stored Proc

Dec 18, 2007

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

View 3 Replies View Related

Can A Stored Proc Swallow An Error

Jul 23, 2005

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?

View 5 Replies View Related

Error Deploying Managed Stored Proc

Jul 24, 2006

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?
 

View 1 Replies View Related

Stored Proc Error By Northwind Database

Jan 14, 2008

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 

View 2 Replies View Related

Error: Identifier 'stored Proc Name' Is Out Of Scope

Mar 10, 2005

Does anyone know what this error message is telling me.

Thanks in advance everyone.

RB

View 2 Replies View Related

Raising Error From Trigger To Stored Proc

Jul 23, 2005

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)BEGINROLLBACK TRANSACTION;RAISERROR('An error occurred in the stored proc.', 16, 1);RETURN(1);END--FORCING AN ERROR AT THE END FOR TESTING PURPOSESRAISERROR('Proc Successful',16,1)On MyTableA, there is a trigger that loops through the inserted data andstops the insert in certain circumstances, returning an error:IF (some criteria)BEGINROLLBACKRAISERROR('An error occurred in the trigger.',16,1)RETURNENDWhen I call the stored procedure from VB (connecting via RDO) witherror-causing data, the trigger successfully stops the insert, and adds thetrigger-error-msg to the errors collection, but it does NOT seem to createan error situation back in the stored procedure. The procedure finishes upwith the "Proc Successful" message, so that when I iterate through theerrors collection back in VB, I have "Proc Successful" followed by "An erroroccurred in the trigger."Is there some way I'm not finding to have the calling procedure recognizethat a raiserror occurred in the trigger and behave appropriately for anerror situation?Jen

View 1 Replies View Related

Error Adding Stored Proc To Subscription

Dec 14, 2007

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.

Thanks,
Stuart Fox

View 1 Replies View Related

Getting An Error While Activating Stored Proc On Queue

Apr 20, 2007

Hi,

I am having an SQL CLR stored proc which looks like this in SQL Svr



CREATE PROCEDURE [dbo].[callspGetMessage]

@strMsg [nvarchar](4000) OUTPUT

WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [GetSSBMSG].[StoredProcs].[callspGetMessage]

GO

EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'PROCEDURE', @level1name=N'callspGetMessage'

GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'StoredProcs.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'PROCEDURE', @level1name=N'callspGetMessage'

GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=14 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'PROCEDURE', @level1name=N'callspGetMessage'







When I am trying to alter the queue to attach to this procedure using following code



alter queue q1 with activation (

status = on,

max_queue_readers = 1,

procedure_name = [callspGetMessage],

execute as owner);



I am getting following error.

The signature of activation stored procedure 'callspGetMessage' is invalid. Parameters are not allowed.



Parameters to the stored proc are not allowed or am I doing anything wrong.



Any help is greatly appreciated.



Thanks,

Don

View 2 Replies View Related

SQL Server Error 7405 While Compiling A Stored Proc

Jul 18, 2000

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.

Thanks.

View 2 Replies View Related

Stored Proc Crashes At Select Into With Error 18456 When Run From Another Machine

Mar 27, 2007

Hi,

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.

Please please help.



Ahrvie

View 1 Replies View Related

Trigger Error When Inserting Stored Proc Output Into Temp Table.

Feb 18, 2008





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



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

)

INSERT #Scorecard_Returns(

NAME_COL ,

ACCT_ID

ACCT_NUMBER ,

ENTITY_ID,

NAME,

ID,

NUM_ACCOUNT,

A_OFFICER,

I_OFFICER,

B_CODE,

I_OBJ ,

LAST_MONTH

LAST_3MONTHS,

IS

)

EXEC ISI_WEB_DATA.dbo.wbGetxxxxxcardUserReturns

@nId = 1,

@sSUser = 'SELECTED USER',

@sUName = 'VALID USER'

View 4 Replies View Related

Stored Proc - Calling A Remote Stored Proc

Aug 24, 2006

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':

Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'

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?

Thanks - Amos.

View 3 Replies View Related

Stored Proc Question : Why If Exisits...Drop...Create Proc?

Jun 15, 2006

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

View 3 Replies View Related

Enable Error Handling When Writing Custom Source Component /custom Error Handling Component.

Apr 21, 2006

1) We are writing a custome Source component for Oracle with OCI calls, Could some one please let me know how to Enable Error Handling for the Same,

2) Is it possible to write Custome Error Handeling Component for SSIS? if yes could you please help me on how to write it.

Thanks in advance.

View 1 Replies View Related







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