Write cursor to track error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Write cursor to track error

Author

Topic

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-08-15 : 12:26:25

Hi Guys,
I wanna built one store procedure which will insert data from access to sql server.both are on different instances.
This procedure will insert record row by row which check all if it find any error it will genrate a log and will move to next.

I have 12 constraints on this table

please help.

Yogesh V. Desai. | SQLDBA|

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-15 : 13:41:08

you can use if @@error > raise error and then rollback if you want.
check for Cursors and RAISEERROR in books online. your post does not tells in detail what you need to do. if you need help with syntax books online has good examples.

Ashley Rhodes

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-08-15 : 14:43:03

hi thanks for reply

I have this query

insert into trade(cancel,tnum,confirmed,id,tt,q,xref,fund,strat,
strat2,clr,clr2,td,settles,tp,tai,tc,tax,moneyspot,tccy,rate,
d360365,fwddate,moneyfwd,fr,divrate,clr_ref,xmit_date,openq,close_date,
rpl,cpty,instructions,trader,notes,ctc,tnumb,created,inputby,input_date,acctrpl,acctfx,src,sdfactor,lnkq,prevfactor,
user1,user2,user3,splitref,tradeyld,confirmedby,tc2,tc3,tc4,fund2,rpl2,user4,ExecutionTime,ordertime,Decisiontime,
Reason)
select TradeNew.cancel,TradeNew.tnum,TradeNew.confirmed,TradeNew.id,TradeNew.tt,TradeNew.q,TradeNew.xref,TradeNew.fund,
TradeNew.strat,TradeNew.strat2,TradeNew.clr,TradeNew.clr2,TradeNew.td,TradeNew.settles,TradeNew.tp,TradeNew.tai,TradeNew.tc,
TradeNew.tax,TradeNew.moneyspot,TradeNew.tccy,TradeNew.rate,TradeNew.d360365,TradeNew.fwddate,TradeNew.moneyfwd,TradeNew.fr,
TradeNew.divrate,TradeNew.clr_ref,TradeNew.xmit_date,TradeNew.openq,TradeNew.close_date,TradeNew.rpl,TradeNew.cpty,TradeNew.instructions,
TradeNew.trader,TradeNew.notes,TradeNew.ctc,TradeNew.tnumb,TradeNew.created,TradeNew.inputby,TradeNew.input_date,
TradeNew.acctrpl,TradeNew.acctfx,TradeNew.src,TradeNew.sdfactor,TradeNew.lnkq,prevfactor,TradeNew.user1,TradeNew.user2,
TradeNew.user3,TradeNew.splitref,TradeNew.tradeyld,TradeNew.confirmedby,TradeNew.tc2,TradeNew.tc3,TradeNew.tc4,
TradeNew.fund2,TradeNew.rpl2,TradeNew.user4,TradeNew.ExecutionTime,TradeNew.ordertime,TradeNew.Decisiontime,TradeNew.Reason
from tradenew
where((tradenew.fund)notin(select excludefunds from excludeuploadfunds))and ((tradenew.action)="accept"))or(((tradenew.fund2)not in
(select excludefunds from excludeduploadfunds)) and ((tradenew.action)="accept"));

Now the scenario is that, It will take data from access table and it will store to SQl table..while inserting i have to check for the data if I found any invalis data then it will generate a log and will move on next row.I don't want to rollback the transaction.
Could you help me to build a proc.Please let me know if you need more details.

Thanks for your help

Yogesh V. Desai. | SQLDBA|

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-15 : 14:52:53

what is the criteria for invalid data.
can you post some sample data along with scripts for tables.
please include the script for inserts into the tables as well

Ashley Rhodes

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-08-15 : 15:01:49

I don't have sample data now,

There are some constraints already defind

Constraints detail
Account Transfers must have different receiving account
Book Transfers must have a different receiving strategy and the same receiving account
For StockLoan Transactions, xref cannot be null
Only Allocation Trade Types can allocate across Funds
Only Allocation Trade Types or Book Transfers can allocate across Strategies
Only Repo transactions can have a fwddate or moneyspot
Settle date must be >= Trade date
Check_Trade_acctfx
Check_Trade_acctrpl
Check_Trade_q
Check_Trade_src
CK_Trade_d360365
CK_Trade_tccy
DF__Trade__acctfx__6319B466
DF__Trade__acctrpl__61316BF4
DF__Trade__cancel__5006DFF2
DF__Trade__confirmed__50FB042B
DF__Trade__created__5F492382
DF__Trade__d360365__5A846E65
DF__Trade__divrate__5C6CB6D7
DF__Trade__LongTermF__5E54FF49
DF__Trade__moneyspot__589C25F3
DF__Trade__openq__5D60DB10
DF__Trade__prevfacto__67DE6983
DF__Trade__rate__59904A2C
DF__Trade__rpl__5E54FF49
DF__Trade__rpl2__6CA31EA0
DF__Trade__sdfactor__66EA454A
DF__Trade__Section12__5D60DB10
DF__Trade__settles__53D770D6
DF__Trade__src__6501FCD8
DF__Trade__tai__55BFB948
DF__Trade__tax__57A801BA
DF__Trade__tc__56B3DD81
DF__Trade__tc2__69C6B1F5
DF__Trade__tc3__6ABAD62E
DF__Trade__tc4__6BAEFA67
DF__Trade__td__52E34C9D
DF__Trade__tp__54CB950F
DF__Trade__tradeyld__68D28DBC
DF__Trade__WorkOutPr__5F492382
DF_Trade_Decisiontime
DF_Trade_ExecutionTime
DF_Trade_input_date
DF_Trade_ordertime
FK_Sec_Trade

FK_Trade_Country

FK_Trade_Sec

FK_Trade_TradeClr

FK_Trade_TradeClr2

FK_Trade_TradeFund

FK_Trade_TradeFund2

FK_Trade_Trader

FK_Trade_TradeStrat

FK_Trade_TradeStrat2

FK_TradeCpty_Trade36

FK_TradeT_Trade41

PK_Trade
TDate_Rule
TDate_Rule

Yogesh V. Desai. | SQLDBA|

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2007-08-15 : 15:18:36

If you're going to do it row by row (ie a loop or cursor) then just check @@ERROR after every insert and insert errors into your log table.

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-08-16 : 05:25:30

hi Jonek,

Can you build a sample procdure for this.

Yogesh V. Desai. | SQLDBA|

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-08-16 : 07:04:13

Hi Jonek

I have written the following code
will you check it please
-- i want to inser errors in log table

--USE 'database name'
GO
CREATE PROCEDURE spInsertRowbyRow_usingCursor_TradeNew
AS
DECLARE @MyTable_tradenew VARCHAR(255)
DECLARE myCursor_tradenew
CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
--open the cursor
open myCursor_tradenew
---start fetching rows
fetch next from myCursor_tradenew into @MyTable_tradenew
WHILE @@FETCH_STATUS = 0
BEGIN
-- Insert query/ update statment
IF @@ERROR = 547
PRINT 'A CHECK CONSTRAINT violation occurred'
IF @@ROWCOUNT = 0
PRINT 'No rows updated.'
ELSE
PRINT STR(@@ROWCOUNT) + ' rows updated.'
COMMIT -- Commits rows successfully updated.
FETCH NEXT FROM myCursor INTO @MyTable_tradenew
END
CLOSE myCursor_tradenew
DEALLOCATE myCursor_tradenew
Go

Will it work ?
Coditions for proc
it should have to check constraint, if it is correct,then it will insert the row else it will log error into logtable and move to the next row.

Yogesh V. Desai. | SQLDBA|

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2007-08-16 : 09:12:16

A couple of items (below underlined)

DECLARE @MyTable_tradenew VARCHAR(255)
--new vars
Declare @rows int, @chk int
DECLARE myCursor_tradenew
CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
--open the cursor
open myCursor_tradenew
---start fetching rows
fetch next from myCursor_tradenew into @MyTable_tradenew
WHILE @@FETCH_STATUS = 0
BEGIN
-- Insert query/ update statment
-- To ensure that you get the correct values you
-- should get @@ROWCOUNT and @@ERROR in the same line...
Select @chk = @@Error, @rows = @@RowCount

--I'd be less specific with your error handling
If @chk <> 0
Begin
Print 'message'
--Insert Into <yourLogTable>
End
--IF @@ERROR = 547
--PRINT 'A CHECK CONSTRAINT violation occurred'
--IF @@ROWCOUNT = 0
--PRINT 'No rows updated.'
--ELSE
PRINT STR(@rows) + ' rows updated.'

COMMIT -- Commits rows successfully updated.
FETCH NEXT FROM myCursor INTO @MyTable_tradenew
END
CLOSE myCursor_tradenew
DEALLOCATE myCursor_tradenew

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-08-16 : 10:53:44

Hi Joenak,
Thanks for the help,
I have omplemented the following query need some changes and updates
--DECLARE @MyTable_tradenew VARCHAR(255)
--new vars
Declare @rows int, @chk int
DECLARE myCursor_tradenew
CURSOR FOR
select *
from TradeNew_duplicated
--open the cursor
open myCursor_tradenew
---start fetching rows
fetch next from myCursor_tradenew
WHILE @@FETCH_STATUS = 0
BEGIN
-- Insert query/ update statment
insert into trade(cancel,tnum,confirmed,id,tt,q,xref,fund,strat,
strat2,clr,clr2,td,settles,tp,tai,tc,tax,moneyspot,tccy,rate,
d360365,fwddate,moneyfwd,fr,divrate,clr_ref,xmit_date,openq,close_date,
rpl,cpty,instructions,trader,notes,ctc,tnumb,created,inputby,input_date,acctrpl,acctfx,src,sdfactor,lnkq,prevfactor,
user1,user2,user3,splitref,tradeyld,confirmedby,tc2,tc3,tc4,fund2,rpl2,user4,ExecutionTime,ordertime,Decisiontime,
Reason)
select TradeNew_duplicated.cancel,TradeNew_duplicated.tnum,TradeNew_duplicated.confirmed,TradeNew_duplicated.id,TradeNew_duplicated.tt,TradeNew_duplicated.q,TradeNew_duplicated.xref,TradeNew_duplicated.fund,
TradeNew_duplicated.strat,TradeNew_duplicated.strat2,TradeNew_duplicated.clr,TradeNew_duplicated.clr2,TradeNew_duplicated.td,TradeNew_duplicated.settles,TradeNew_duplicated.tp,TradeNew_duplicated.tai,TradeNew_duplicated.tc,
TradeNew_duplicated.tax,TradeNew_duplicated.moneyspot,TradeNew_duplicated.tccy,TradeNew_duplicated.rate,TradeNew_duplicated.d360365,TradeNew_duplicated.fwddate,TradeNew_duplicated.moneyfwd,TradeNew_duplicated.fr,
TradeNew_duplicated.divrate,TradeNew_duplicated.clr_ref,TradeNew_duplicated.xmit_date,TradeNew_duplicated.openq,TradeNew_duplicated.close_date,TradeNew_duplicated.rpl,TradeNew_duplicated.cpty,TradeNew_duplicated.instructions,
TradeNew_duplicated.trader,TradeNew_duplicated.notes,TradeNew_duplicated.ctc,TradeNew_duplicated.tnumb,TradeNew_duplicated.created,TradeNew_duplicated.inputby,TradeNew_duplicated.input_date,
TradeNew_duplicated.acctrpl,TradeNew_duplicated.acctfx,TradeNew_duplicated.src,TradeNew_duplicated.sdfactor,TradeNew_duplicated.lnkq,prevfactor,TradeNew_duplicated.user1,TradeNew_duplicated.user2,
TradeNew_duplicated.user3,TradeNew_duplicated.splitref,TradeNew_duplicated.tradeyld,TradeNew_duplicated.confirmedby,TradeNew_duplicated.tc2,TradeNew_duplicated.tc3,TradeNew_duplicated.tc4,
TradeNew_duplicated.fund2,TradeNew_duplicated.rpl2,TradeNew_duplicated.user4,TradeNew_duplicated.ExecutionTime,TradeNew_duplicated.ordertime,TradeNew_duplicated.Decisiontime,TradeNew_duplicated.Reason
from TradeNew_duplicated
where(((TradeNew_duplicated.fund)not in(select excludedfunds from excludeduploadfunds_duplicated))and ((TradeNew_duplicated.action)='accept'))or(((TradeNew_duplicated.fund2)not in
(select excludedfunds from excludeduploadfunds_duplicated)) and ((TradeNew_duplicated.action)='accept'));

-- To ensure that you get the correct values you
-- should get @@ROWCOUNT and @@ERROR in the same line...
Select @chk = @@Error, @rows = @@RowCount
-- error handling
If @chk <> 0
Begin
Print 'message'
--Insert Into <yourLogTable>--How t insert into log table
End
IF @@ERROR = 547
PRINT 'A CHECK CONSTRAINT violation occurred'
IF @@ROWCOUNT = 0
PRINT 'No rows updated.'
ELSE
PRINT STR(@rows) + ' rows updated.'
COMMIT -- Commits rows successfully updated.
FETCH NEXT FROM myCursor --INTO @MyTable_tradenew
END
CLOSE myCursor_tradenew
DEALLOCATE myCursor_tradenew

It's giving me the following messages

"(1 row(s) affected)

Server: Msg 515, Level 16, State 2, Line 18
Cannot insert the value NULL into column 'clr', table 'PA_KEYNESQS_LEV.dbo.Trade'; column does not allow nulls. INSERT fails.
The statement has been terminated.
message
No rows updated.
Server: Msg 3902, Level 16, State 1, Line 51
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Server: Msg 16916, Level 16, State 1, Line 53
A cursor with the name 'myCursor' does not exist."

Yogesh V. Desai. | SQLDBA|

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2007-08-16 : 11:24:09

Remove the COMMIT line or add a BEGIN TRAN statement.
End of your cursor loop use the correct name.

I should have asked to see the destination table first. You could wrap all the not nullable fields with IsNull(). You may have to disable the constraints, insert, and enable the constraints again, although this won't help you with the NOT NULL issue.

This is tough to do in TSQL, I was in a .NET frame of mind when I read this the first time.

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-08-16 : 11:47:20

I want to store the error loges in table how could I, Can you give me sample code example

Yogesh V. Desai. | SQLDBA|

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2007-08-16 : 16:09:46

Yeah, sorry. I can't think of a good way to do this in 2000.

In 2005 or .NET you can use a TRY CATCH block to catch the bad record and log it to a table.

You can force the records in by disabling the constraints and checking for nulls, inserting, then re-enabling the constraints. But then you'll have orphaned records and other inconsistencies.

Here's a quick sample....

--Sample PK table
Create Table TestPK
(val2 int not null
, val3 varchar(100) null
, Constraint PK_TestPK Primary Key Clustered (val2))

Insert Into TestPK Values (1, 'Test1')

--Destination table
Create Table Test
(val1 varchar(10) not null
, val2 int null
, val3 varchar(10) null
, val4 datetime null
, Constraint FK_Test_TestPK Foreign Key (val2) References TestPK (val2))

--Source
Create Table Source
(sourceid int identity(1, 1)
, val1 varchar(10)
, val2 int
, val3 varchar(10)
, val4 datetime)

--Sample Data
Insert Into Source Values ('123', 1, 'abc', getdate())
Insert Into Source Values ('456', 2, 'def', getdate() + 1)
Insert Into Source Values (Null, 3, 'ghi', getdate() + 2)

Declare @sql nvarchar(2000)
, @sql2 nvarchar(2000)
, @table varchar(255)
, @table2 varchar(255)
, @field varchar(255)
, @val varchar(255)
, @order int
, @isnull bit
, @SourceID int

Set @table = 'test'
Set @table2 = 'Source'

Set @sql = 'Alter Table ' + @table + ' NoCheck Constraint All'
Exec sp_ExecuteSql @sql

Select Top 1 @SourceID = sourceid
From Source
Order By sourceid

While @@RowCount <> 0
Begin

Select Top 1 @field = column_name
, @order = ordinal_position
, @isnull = Case When is_nullable = 'Yes' Then 1 Else 0 End
From information_schema.columns
Where table_name = @table
Order By ordinal_position

Set @sql = 'insert into ' + @table + ' ('
Set @sql2 = ' select '

While @@RowCount <> 0
Begin
Set @sql = @sql + QuoteName(@field) + ', '
If @isnull = 0
Set @sql2 = @sql2 + 'IsNull(' + QuoteName(@Field) + ', '''') As ' + QuoteName(@Field) + ', '
Else
Set @sql2 = @sql2 + QuoteName(@field) + ', '

Select Top 1 @field = column_name
, @order = ordinal_position
, @isnull = Case When is_nullable = 'Yes' Then 1 Else 0 End
From information_schema.columns
Where table_name = @table
And ordinal_position > @order
Order By ordinal_position

End

Set @sql = left(@sql, len(@sql) - 1) + ')'
Set @sql2 = left(@sql2, len(@sql2) - 1) + ' From ' + @table2 + ' Where sourceid = ' + Convert(varchar, @SourceID)

Set @sql = @sql + @sql2

Exec sp_ExecuteSql @sql

Select Top 1 @SourceID = sourceid
From Source
Where sourceid > @SourceID
Order By sourceid
End

Set @sql = 'Alter Table ' + @table + ' Check Constraint All'
Exec sp_ExecuteSql @sql

Select * From test

Drop Table Test
Drop Table TestPK
Drop Table Source

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2007-08-16 : 16:10:37

Even though this is a 2000 forum question, the 2005 SQL...

--Sample PK table
Create Table TestPK
(val2 int not null
, val3 varchar(100) null
, Constraint PK_TestPK Primary Key Clustered (val2))

Insert Into TestPK Values (1, 'Test1')

--Destination table
Create Table Test
(val1 varchar(10) not null
, val2 int null
, val3 varchar(10) null
, val4 datetime null
, Constraint FK_Test_TestPK Foreign Key (val2) References TestPK (val2))

--Source
Create Table Source
(sourceid int identity(1, 1)
, val1 varchar(10)
, val2 int
, val3 varchar(10)
, val4 datetime)

--Sample Data
Insert Into Source Values ('123', 1, 'abc', getdate())
Insert Into Source Values ('456', 2, 'def', getdate() + 1)
Insert Into Source Values (Null, 3, 'ghi', getdate() + 2)

--Error Log
Create Table ErrorLog
(sourceid int)

Declare @sourceid int

Select Top 1 @sourceid = sourceid
From Source
Order By sourceid

While @@RowCount <> 0
Begin
Begin Try
Insert Into Test
Select val1, val2, val3, val4
From Source
Where sourceid = @sourceid
End Try
Begin Catch
Insert Into ErrorLog (SourceID) Values (@sourceid)
End Catch

Select Top 1 @sourceid = sourceid
From Source
Where sourceid > @sourceid
Order By sourceid
End

Select * From Test
Select * From ErrorLog

Drop Table Test
Drop Table TestPK
Drop Table Source
Drop Table ErrorLog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources