I am stumped on the error reporting with sql server. I was told i
need 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 )
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.
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.
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 RelatedI 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.
I have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values
-------------------------------------------
PoId Podate Approved
22008-07-07 No
42007-05-05 No
52005-08-06 Yes
62006-07-07 Yes
Table B contains the following columns and values
-------------------------------------------------
TaskId TableName Fromdate Approved_Status
1 A 7/7/2007 No
3 B 2/4/2006 Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in the Table which is specified in the table name column in Table B. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C.
Pls provide me with full stored procedure code.
It is very urgent.
C.R.P RAJAN
ok, ill lay it out like this, and try not to be too confusing.
I need to check my trasaction table to see that the user with a UserID bought, keeping track(adding up) of how many times they bought the Product with a productID, , then i need to selcect items from product table with the productID for the items the user bought,
i know how to return the product information, and how to check the tranaction table and i think how to add up the amount of tranastions for that item
so bacially i need to select data from a product table, for the products that the user bought tahts stored in the transaction table.
well i tried not to be confusing, but i think i failed
I have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values-------------------------------------------PoId Podate Approved
2 2008-07-07 No 4 2007-05-05 No 5 2005-08-06 Yes 6 2006-07-07 Yes
Table B contains the following columns and values-------------------------------------------------TaskId TableName Fromdate Approved_Status
1 A 7/7/2007 No3 B 2/4/2006 Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C.
Pls provide me with full stored procedure code.
It is very urgent.
Hi i have a stored procedure which deletes a row from a table.
Sometimes the proc will break the constraints for the table.
How can i check if the constraint is going to be broken before I try to delete so that I can avoid a nasty error message coming onto my asp.net page?
Thanks in advance.
Gary
I'm trying to put scripts to create our stored procedures under version control.
However I don't want these scripts to be run if the stored procedure already exists (we'll be using update scripts to alter existing stored procedure I think).
Anyway I tried :
Code:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.getEnglandHotelsByATOPResort Script Date: 26/10/2005 10:40:01 ******/
if NOT EXISTS (SELECT object_id('procedureName','p'))
CREATE PROCEDURE [dbo].[procedureName]
@location char(2)
AS
...Procedure...
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I know that the if NOT EXISTS ... part works as doing the following returns the expected result :
Code:
IF NOT EXISTS (SELECT object_id('procedureName','p'))
SELECT 'FALSE'
ELSE
SELECT 'TRUE'
However when trying to do this before the CREATE PROCEDURE I get :
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'PROCEDURE'.
Server: Msg 137, Level 15, State 1, Line 12
Must declare the variable '@location'.
Any ideas how to do this?
-D
Hi,Say I have a stored procedure which does two INSERT operation.How can I check if the first INSERT succeeded in order to know if Ishould carry on and do the second one ?Regards
View 1 Replies View RelatedHi all,
I’m returning two values from a stored procedure, one is a basic string confirming that an email has been sent and the other is the normal value returned from running an INSERT statement. So in my code I’m using the ExecuteNonQuery() method. I’m not sure how to handle both returned values in my code in my data layer. This is what I have:
ExecuteNonQuery(cmd);
return Convert.ToString(cmd.Parameters["@ReturnedValue"].Value).ToLower();
Obviously I’d need to return the value returned by the ExecuteNonQuery method as well, normally I’d simply convert the value to an int and precede this with the return keyword like so:
return (int)ExecuteNonQuery(cmd);
Obviously I can’t do this as I need to return two values, the normal value returned by the ExecuteNonQuery() method and my own output parameter value. Any ideas how I can do both? My current method containing the code further above returns a string but clearly this doesn’t help. I’m guessing that maybe I should return an object array so I can return both values? I haven’t encountered this problem before so I’m just guessing. Please help.
Thanks
I have a select query that soemtimes errors out. Hence I need to get an email , if this query errors out. I have my email etc setup...
However, how can i detect in a stored proc, that this qry has an error?
I used this, but if it had an error, it does not return any value for @@error.
BEGIN TRANSACTION
select * from vw_dailydel
IF @@ERROR != 0
BEGIN
print @@ERROR
RAISERROR('There was an error here.', 11, 1)
RETURN
END
Pl assit.
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 RelatedHi, 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 RelatedI 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.
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 RelatedHi There,
If an update in a stored procedure fails/errors (as in (a) below) the procedure will not continue with (b) - I need the code in (b) to run despite whether the previous update was successful or not - Any ideas?
(a) if(@Data2 = 6)
begin
update
SCHEDULE
set
Start_CallBack = getdate()
where
(Block = @Block)
end
(b) WHILE @Block_Count > 0
BEGIN
UPDATE BLOCK SET Status = @Block_Status
END
Any help will be greatly appreciated :)
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
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 RelatedCan 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}
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 RelatedI 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.
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.
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
View 9 Replies View RelatedI am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType int = 0,
@PositionType int = 0,
@BeginAvailableDate DATETIME = NULL,
@EndAvailableDate DATETIME = NULL,
@EmployerLatitude DECIMAL(10, 6),
@EmployerLongitude DECIMAL(10, 6),
@Radius INT
AS
SET NOCOUNT ON
DECLARE @v_SQL NVARCHAR(2000)
DECLARE @v_RadiusMath NVARCHAR(1000)
DECLARE @earthRadius DECIMAL(10, 6)
SET @earthRadius = 3963.191
-- SET @EmployerLatitude = 29.346675
-- SET @EmployerLongitude = -89.42251
-- SET @radius = 50
SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius
SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +
'Round(' + @v_RadiusMath + ', 0) AS Distance ' +
'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +
'WHERE ' + @v_RadiusMath + ' <= ' + @Radius
IF @LicenseType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType
END
IF @PositionType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType
END
IF LEN(@BeginAvailableDate) > 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate
END
--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'
PRINT(@v_SQL)
EXEC(@v_SQL)
-----------------------------------------------------------------------------------------------------------------
Hi All,
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
Thanks in advance,
Anand
hello friends
i m stuck up with a problem...actually i dont have much experience in database line....i m new to this line....i have recently joined the job & this problem is like a test of me....if i will be able to give the solution then everything is fine otherwise i will be fired & im not in a condition to leave this job as this is my first job in software development....i have got this chance with lots of difficulty....so please help me if u can...
the problem is....>> i m using a procedure to check the duplicatye records by using string comparison against address of persons..allover the country....
i m using SQL server 7.0
i have a single table(name of table is DATA) which contains 350000 records( i mean address entries) there are about 35 columns but i have to check duplicate records only against address field...for that first of all i remove special characters from the address field.....then i compare first 20 characters for duplicate entries...
for this i m generating another table(name of another table is RESULT)...
how the logic works...initially the data table contains the records but the result table is totally blank....first of all i pick first entry of address from DATA table then...check it with the entry in RESULT table if the entry exists... it compares the address if the record is same then it generates a refference of this address and make an entry....means a refference of that entry....(as far as very first record is concerned there will be no entry in the RESULT table so it will enter the address over there...then it picks up the second record...checks it in the RESULT table...now this record will be compared with the one & only entry in the RESULT table....if the entry is same then the refference will be entered... otherwise it will be entered as second record in the RESULT table....)
now where lies the problem.....initially the procedure is very fast.... but it gradually slows down .....because(when it checks the 10th record for duplication it compares the entry in RESULT table for 9 times only
*** similarly when it checks the 100th record it compares it for 99 times
*** similarly when it checks the 10000th record it compares it for 9999 times
so here lies the problem....
when it checks the 100000th record it gets dammm slow...
what i have get till now is that i have checked.....>>>>>
5000 records in 4 mins....
25000 records in 22 mins....
and
100000 records in 20 hours....(means initially its faster but it gradually slows down)
************************************************** ************************
here i m giving the code for the procedure......
************************************************** *************************
CREATE PROCEDURE pro1 as
SET NOCOUNT ON
Declare @IvgId as numeric(15)
Declare @Address as nvarchar(250)
Declare @AddressClean as nvarchar(250)
Declare @MaxLen as INT
Declare @Add as nvarchar(250)
Declare @Ic as int
Declare @FoundIvgId as numeric(15)
Declare @NewIvgId as numeric(15)
/* here 'N' is for keeping track for some system failures etc */
Declare CurData CURSOR forward_only FOR Select IvgId, Address From Data Where ProcessClean = 'N'
OPEN CurData
FETCH NEXT FROM CurData INTO @IvgId, @Address
WHILE @@FETCH_STATUS = 0
Begin
/*here i m doing string cleaning by removing special characcters */
Select @MaxLen = len(LTRIM(RTRIM(@Address)))
Select @Address = LOWER(@Address)
Select @Ic = 1
Select @AddressClean = ' '
While @Ic <= @MaxLen
/* here @MaxLen is the maximum length of the address field but i have to compare only first 20 characters */
Begin
Select @Add = Substring(@Address, @Ic, 1)
If ascii(@Add) > 47 AND ascii(@Add) <= 64 AND @Add <> ' '
Begin
Select @AddressClean = @AddressClean + @Add
End
If ascii(@Add) > 90 AND @Add <> ' '
Begin
Select @AddressClean = @AddressClean + @Add
End
Select @Ic = @Ic + 1
End
/* now we have removed special characters , for failure checking i m using this 'Y' */
Update Data Set AddressClean = @AddressClean, ProcessClean = 'Y'
Where IvgId = @IvgId
FETCH NEXT FROM CurData INTO @IvgId, @Address
End
PRINT 'Cleaning Done.............................'
Close CurData
Deallocate CurData
/* till now procedure doesnt take too much time & cleans all the 3 lack records in abt 40 mins but next part is giving trouble */
Declare CurData CURSOR FOR Select IvgId, Address, AddressClean From Data Where ProcessDup = 'N'
OPEN CurData
FETCH NEXT FROM CurData INTO @IvgId, @Address, @AddressClean
Select @NewIvgId = 100
WHILE @@FETCH_STATUS = 0
Begin
If EXISTS (Select IvgId From Result Where SubString(RTRIM(LTRIM(AdressClean)),1,20) = SubString(RTRIM(LTRIM(@AddressClean)),1,20))
Begin
Update Result Set DupIvgId = @IvgId Where SubString(RTRIM(LTRIM(AdressClean)),1,20) = SubString(RTRIM(LTRIM(@AddressClean)),1,20)
End
ELSE
Begin
Insert Into Result Values (@NewIvgId, @Address, @AddressClean,0)
Select @NewIvgId = @NewIvgId + 1
End
Update Data set ProcessDup = 'Y' Where IvgId = @IvgId
FETCH NEXT FROM CurData INTO @IvgId, @Address, @AddressClean
End
Close CurData
Deallocate CurData
SET NOCOUNT OFF
Print 'Done................................'
************************************************** **************************
now the procedure is over....now i m writing the SQL script of DATA & RESULT table
************************************************** ************************
CREATE TABLE [dbo].[DATA] (
[IVGID] [numeric](18, 0) NOT NULL ,
[Title] [varchar] (10) NULL ,
[FirstName] [varchar] (50) NULL ,
[MiddleName] [varchar] (10) NULL ,
[LastName] [varchar] (30) NULL ,
[Add1] [varchar] (150) NULL ,
[Add2] [varchar] (50) NULL ,
[Add3] [varchar] (50) NULL ,
[City] [varchar] (30) NULL ,
[State] [varchar] (30) NULL ,
[Country] [varchar] (20) NULL ,
[Pincode] [varchar] (10) NULL ,
[OffPhone] [varchar] (20) NULL ,
[OffFax] [varchar] (20) NULL ,
[ResPhone] [varchar] (20) NULL ,
[ResFax] [varchar] (20) NULL ,
[EMail] [varchar] (50) NULL ,
[Source] [varchar] (20) NULL ,
[MODEL] [varchar] (20) NULL ,
[PNCD] [varchar] (6) NULL ,
[DupKey] [decimal](18, 0) NULL ,
[Duplicate] [int] NULL ,
[HouseHoldID] [varchar] (50) NULL ,
[YearSlab] [varchar] (10) NULL ,
[CleanStatus] [int] NULL ,
[AddStatus] [int] NULL ,
[BatchNo] [varchar] (20) NULL ,
[ModelStatus] [int] NULL ,
[Month] [int] NULL ,
[Year] [int] NULL ,
[SapStatus] [int] NULL ,
[ErrCase] [int] NULL ,
[cmpCity] [varchar] (50) NULL ,
[Product] [varchar] (1) NULL ,
[cmpPinCode] [varchar] (6) NULL ,
[Address] [nvarchar] (250) NULL ,
[AddressClean] [nvarchar] (250) NULL ,
[DupIvgId] [numeric](18, 0) NULL ,
[ProcessClean] [nvarchar] (1) NULL ,
[ProcessDup] [nvarchar] (1) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[DATA_TEST] Script Date: 15/06/2001 8:36:21 PM ******/
CREATE TABLE [dbo].[DATA_TEST] (
[IVGID] [numeric](18, 0) NOT NULL ,
[Address] [nvarchar] (50) NULL ,
[AddressClean] [nvarchar] (50) NULL ,
[DupIvgId] [numeric](18, 0) NULL ,
[ProcessClean] [nvarchar] (1) NULL ,
[ProcessDup] [nvarchar] (1) NULL
) ON [PRIMARY]
GO
so now i have given the whole description of my problem....i m eagerly waiting for reply......
if anybody can help....i will be very thankful.....
bye for now
Bhupinder singh
Hey everyone,
I'm getting a rather bizarre (but probably simple) error when trying to check for the existence of a procedure before creating it. I know I could just drop it, but I'm really curious as to why this code doesn't work right.
Code SnippetIF OBJECT_ID(N'dbo.spCoverageLog') IS NULL
CREATE PROCEDURE [dbo].[spCoverageLog] ( @spName VARCHAR(100) )
AS
BEGIN
UPDATE dbo.CodeCoverage
SET CreationDate = GETDATE(),ProcedureName = @spName;
END
GO
It's generating a syntax error at PROCEDURE.
Anyone know why this might happen?
The solution was to run the CREATE PROCEDURE statement with EXEC, but it shouldn't be.
Thanks in advance for any suggestions!
Hi Guys,
SQL Server 2000
I've run out of ideas on what to do with a DTS process that has suddenly started giving an error when one company is processed on it.
Here's the deal:
DTS package launches a complex set of stored procedures (built entirely without error handling) as a step in importing data from various customers. I have isolated the error to one segment where I have to cursor through records that have been identified as having changes. Unfortunately, this cursor is updating about 15 system tables and uses modular stored procedures, about 12 of them, to do the job.
Error: The classic Subquery returned more than one value.
Oddity: If I run the code in Query Analyzer I never get an error, the code finishes without complaints. If I run the exact same code via the DTS package I always get the error. Note that the cursor appears to process every row assigned to it and then the sproc throws the error.
What I've Tried:
1) The obvious, reviewing the code for any unprotected subqueries. Haven't found it.
2) Checking for @@Error = 1 at key points in the code to try to isolate where this is happening. Can't catch it.
Note that this same process is used for several other company's data import process and they do not error even when running the DTS package. Therefore I'm assuming it is a data issue with the one company that errors that is causing the problem.
Any suggestions on what to try next would be greatly appreciated!
Thanks!
Hi,
Just a brief question. I have a script which does a number of insert statements. What I would like to do is determine if the insert statements were all successful. Aside from checking @@ERROR after every insert, is there a way to check if all the insert statements completed successfully?
Thanks,
Jim
I have built a procedure to send mail using OLE Automation and want to be able to trap error information when it doesn't work. So in an attempt to do that I have the following stored procedure that will return informaiton if the return value is <> 0. Here is how it is being used:
IF @return <> 0
BEGIN
EXECUTE sp_displayoaerrorinfo @handle, @return
END
ELSE
PRINT 'Success'
This works fine, but I would like to write the error message to a table and so I thought I could just alter to to be:
IF @return <> 0
BEGIN
EXECUTE sp_displayoaerrorinfo @handle, @return = @failure
END
ELSE
PRINT 'Success'
Where @failure is a variable I declared earlier. Then I could insert the value of this variable along with some other infomration into a table that would track the errors. However, when I do this I receive the following:
Error: Procedure or Function 'sp_DisplayOAErrorInfo' expects parameter '@HResult', which was not supplied. Number:201 Severity:16 State:4
So it isn't seeing that I am passing two variables into the stored procedure. I know I must be missing something simple but I've tried a bunch of different itterations and can't seem to get it right. Any help would be great. Thanks.
Hi, somebody can tell me how to create a correct Stored procedure (with commit and rollback) that return errors to my code for save it in a log file .... I would like to know the right method for a SP with parameters and return error value Thanks
View 5 Replies View Related