How can I implement error handling within a SQL script that us

Hi Hugo,
I was thinking in similar lines as the solution you gave (not the one
with severity 20.. I would have never come up with such a solution :)
But I think there is a problem in the other one. We are talking about
different batches. and these batches will be creating database objects.
Can you give an example on how will you implement the error handling with
temp tables for this scenario?
1. Create a view on some existing table first (select *)
2. If the view creation is successful,then create another view on this view.On Thu, 1 Jun 2006 22:35:01 -0700, Omnibuzz wrote:
(snip)
>Can you give an example on how will you implement the error handling with
>temp tables for this scenario?
>1. Create a view on some existing table first (select *)
>2. If the view creation is successful,then create another view on this view.[/color
]
Hi Omnibuzz,
There's one thing I hadn't thought about - CREATE VIEW must be the only
in a batch, so you'll have to use dynamic SQL to make it conditional.
-- Preparational steps
CREATE TABLE #Status (Status varchar(30) NOT NULL)
INSERT INTO #Status (Status) VALUES ('Okay')
go
-- Start of batch #1
EXEC ('CREATE VIEW v1
AS
SELECT 1 AS a')
IF @.@.error > 0
BEGIN
UPDATE #Status
SET Status = 'Error in batch #1'
END
go
-- Start of batch #2
IF (SELECT Status FROM #Status) = 'Okay'
BEGIN
EXEC ('CREATE VIEW v2
AS
SELECT a
FROM v1')
IF @.@.error > 0
BEGIN
UPDATE #Status
SET Status = 'Error in batch #2'
END
END
go
-- Repeat for batches #3, #4, ...
-- After last batch:
IF (SELECT Status FROM #Status) <> 'Okay'
BEGIN
PRINT 'Error'
SELECT Status FROM #Status
END
DROP TABLE #Status
go
Hugo Kornelis, SQL Server MVP

how can I get the object_name

Hi,
how can I get the object_name from a given dbid?
I think the only solution is to use dynamic sql with cursors because I can
not run dynamic sql inside a UDF...
can you help me, please?
create table dbo.objects (
dbid smallint
, objectid int
)
go
insert into dbo.objects select 1, 1
insert into dbo.objects select 1, 2
insert into dbo.objects select 1, 6
insert into dbo.objects select 1, 8
insert into dbo.objects select 1, 9
insert into dbo.objects select 4, 1
insert into dbo.objects select 4, 2
insert into dbo.objects select 4, 3
go
select
dbid, objectid,
db_name(dbid) as dbname
-- , object_name (dbid, objectid) as object_name
from dbo.objects
Thank you!
ejrejr
What does that mean OBJECT_NAME of dbid? Have you looked in BOL?
"ejr" <namrek1@.hotmail.com> wrote in message
news:%23QI7mdmGFHA.616@.TK2MSFTNGP10.phx.gbl...
> Hi,
> how can I get the object_name from a given dbid?
> I think the only solution is to use dynamic sql with cursors because I can
> not run dynamic sql inside a UDF...
> can you help me, please?
> create table dbo.objects (
> dbid smallint
> , objectid int
> )
> go
> insert into dbo.objects select 1, 1
> insert into dbo.objects select 1, 2
> insert into dbo.objects select 1, 6
> insert into dbo.objects select 1, 8
> insert into dbo.objects select 1, 9
> insert into dbo.objects select 4, 1
> insert into dbo.objects select 4, 2
> insert into dbo.objects select 4, 3
> go
> select
> dbid, objectid,
> db_name(dbid) as dbname
> -- , object_name (dbid, objectid) as object_name
> from dbo.objects
> Thank you!
> --
> ejr
>|||object_name has only one parameter.
So you should use
object_name(objectid)
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"ejr" <namrek1@.hotmail.com> wrote in message
news:%23QI7mdmGFHA.616@.TK2MSFTNGP10.phx.gbl...
> Hi,
> how can I get the object_name from a given dbid?
> I think the only solution is to use dynamic sql with cursors because I can
> not run dynamic sql inside a UDF...
> can you help me, please?
> create table dbo.objects (
> dbid smallint
> , objectid int
> )
> go
> insert into dbo.objects select 1, 1
> insert into dbo.objects select 1, 2
> insert into dbo.objects select 1, 6
> insert into dbo.objects select 1, 8
> insert into dbo.objects select 1, 9
> insert into dbo.objects select 4, 1
> insert into dbo.objects select 4, 2
> insert into dbo.objects select 4, 3
> go
> select
> dbid, objectid,
> db_name(dbid) as dbname
> -- , object_name (dbid, objectid) as object_name
> from dbo.objects
> Thank you!
> --
> ejr
>|||Use the Information Schema to retrieve metadata wherever possible
rather than create your own catalogue.
If you need to store metadata yourself for any reason then use the
object name. Never store the object ID. The ID is fragile and may
change if the schema changes.
David Portas
SQL Server MVP
--|||yes,
if object_name accept dbid there is no problem at all; I commented the line
object_name (dbid, objectid) to have an idea what I wanted to do
I wanted to know if is possible to get an object name of any database from
an user table with a query like that...
Thank you,
--
ejr
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:Om2v#kmGFHA.3088@.tk2msftngp13.phx.gbl...
> object_name has only one parameter.
> So you should use
> object_name(objectid)
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "ejr" <namrek1@.hotmail.com> wrote in message
> news:%23QI7mdmGFHA.616@.TK2MSFTNGP10.phx.gbl...
can
>|||ejr
I thint it is something like this
Declare @.Sql varchar(100)
while @.@.rowcount=0
begin
select @.sql='Use '+object_name(objectid) from objects
exec @.sql
select
dbid, objectid,
db_name(dbid) as dbname
, object_name (objectid) as object_name
from dbo.objects
end
Madhivanan|||Sorry It should be
Declare @.Sql varchar(100)
while @.@.rowcount=0
begin
select @.sql='Use '+db_name(dbid) from objects
exec @.sql
end
select
dbid, objectid,
db_name(dbid) as dbname
, object_name (objectid) as object_name
from dbo.objects
end
Madhivanan|||Madhavan,
Have you tested that?
Use <DbName> has no effect without Go.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<madhivanan2001@.gmail.com> wrote in message
news:1109255172.695465.89630@.g14g2000cwa.googlegroups.com...
> Sorry It should be
>
> Declare @.Sql varchar(100)
> while @.@.rowcount=0
> begin
> select @.sql='Use '+db_name(dbid) from objects
> exec @.sql
> end
> select
> dbid, objectid,
> db_name(dbid) as dbname
> , object_name (objectid) as object_name
> from dbo.objects
> end
> Madhivanan
>

How can I get the actual line number from inside flatfile sources for each record?

hi all,

maybe this is not a serious problem, but I tried for days to come toi a solution without success.

My Problem:

I have have several flatfile sources I need to import into a sql-server 2005 DB.

It is very important for me to have the original line number from inside the source file for each record. The rowcount transform doesn't fit in for this task, because it accumulates all rows until the end of the dataflow.

I tried script components and it works fine if i assume there are no errors in my source. then I simply could declare a local variable and count it up and add a custom collumn to my output. But for errors in my source this won't work, because a second script component won't know the actual value of a package level variable, which i use to store the value, because i am only allowed to access this variable in the post execujte method of the script.

How can I achieve my goal? Please help me...

Thanks in advance .. Bernd

Can I clarify something?

Do you want the number of rows in the file or the row number adding for each row? Your post above is a little ambiguous about this because you say "original line number from inside the source" but then mention the rowcount component which is nothing to do with line numbers.

-Jamie

|||

I want the line number of the Flat File, which holds the Record.

I don't have them in the source. you may think of it as an Row ID.

Thanks Bernd

|||

OK, gotcha. Well the rowcount component is completely irrelevant here.

SSIS does not give row numbers to rows in the pipeline. I've previously asked for this to be changed which you can vote for and add a comment on here if you like: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=131335

In the meantime, the alternative is to artifically create your own row numbers as explained here: http://www.sqlis.com/default.aspx?37 however this may not work for you as the row numbers cannot be added until after the rows have been extracted into the pipeline. It depends on your requirement.

If you need row numbers added as the file is parsed then you're in script source component or custom component territory I'm afraid.

-Jamie

|||

Thank you very, even if my problem isn't solved.

Personally I think it is a major flaw, because we are not able to track direcly back into the source, which sometimes is extremely important. I support your request for adding this information into the Pipeline without question.

Thank you again

Bernd

|||

berndS wrote:

Thank you very, even if my problem isn't solved.

Personally I think it is a major flaw, because we are not able to track direcly back into the source, which sometimes is extremely important. I support your request for adding this information into the Pipeline without question.

Thank you again

Bernd

Thanks Bernd,

In that case, click the first link in my last post and vote for it. I forgot to add the link in before (sorry) but I have now edited it!

I also think this is a really important feature!

-Jamie

|||

Hmm, hope it's OK to play devil's advocate here. How would this work in a data flow merge, conditional split, etc? Seems like a lot of the processing that is needed in ETL and supported in SSIS would confound the meaning of source row number?

|||

kenambrose wrote:

Hmm, hope it's OK to play devil's advocate here. How would this work in a data flow merge, conditional split, etc? Seems like a lot of the processing that is needed in ETL and supported in SSIS would confound the meaning of source row number?

Ken

Yeah of course it would, absolutely - in which case it has no meaning and you don't use it. Ideally you'd simply have a boolean property on every source adapter saying whether or not to add this "rownumber" column.

If its there and you don't want it - ignore it!

I'd still like to have this feature.

-Jamie

|||

I was just wondering if this might be one feature that sounds good on paper but has limited practical value. Since I insist on data models and domains that make use of natural keys (and always design solutions based on this paradigm), I can't see how it would be of much use in my work. If I ever need to find a row, I always can do so by using the key...

|||

kenambrose wrote:

I was just wondering if this might be one feature that sounds good on paper but has limited practical value. Since I insist on data models and domains that make use of natural keys (and always design solutions based on this paradigm), I can't see how it would be of much use in my work. If I ever need to find a row, I always can do so by using the key...

True enough. Where I think it would be useful would be when importing a file. If a row goes down the error flow it'd be nice to know which row it was.

-Jamie

|||

Jamie,

is there any way to get the error details from a source connector when you set it to ignore errors? Otherwise it would be easy to add the row number by a counter (there are some ways to do that, you know)... The only problem are the error lines. SSIS simply changes the value of a non-readable field to NULL. You don't get any error information (at least that's what I see). If there would be any way to find out that a record has an error (and probably what kind of error in which field) then we could just ignore all errors first, add the counter and do some manual error handling later...

Thanks,

|||

Thomas Pagel wrote:

Jamie,

is there any way to get the error details from a source connector when you set it to ignore errors?

I don't think so Thomas, no. Do these errors trigger the OnError eventhandler? I'm 99% sure they don't but don't have a SSIS instance to hand to check.

-Jamie

How can I get the actual line number from inside flatfile sources for each record?

hi all,

maybe this is not a serious problem, but I tried for days to come toi a solution without success.

My Problem:

I have have several flatfile sources I need to import into a sql-server 2005 DB.

It is very important for me to have the original line number from inside the source file for each record. The rowcount transform doesn't fit in for this task, because it accumulates all rows until the end of the dataflow.

I tried script components and it works fine if i assume there are no errors in my source. then I simply could declare a local variable and count it up and add a custom collumn to my output. But for errors in my source this won't work, because a second script component won't know the actual value of a package level variable, which i use to store the value, because i am only allowed to access this variable in the post execujte method of the script.

How can I achieve my goal? Please help me...

Thanks in advance .. Bernd

Can I clarify something?

Do you want the number of rows in the file or the row number adding for each row? Your post above is a little ambiguous about this because you say "original line number from inside the source" but then mention the rowcount component which is nothing to do with line numbers.

-Jamie

|||

I want the line number of the Flat File, which holds the Record.

I don't have them in the source. you may think of it as an Row ID.

Thanks Bernd

|||

OK, gotcha. Well the rowcount component is completely irrelevant here.

SSIS does not give row numbers to rows in the pipeline. I've previously asked for this to be changed which you can vote for and add a comment on here if you like: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=131335

In the meantime, the alternative is to artifically create your own row numbers as explained here: http://www.sqlis.com/default.aspx?37 however this may not work for you as the row numbers cannot be added until after the rows have been extracted into the pipeline. It depends on your requirement.

If you need row numbers added as the file is parsed then you're in script source component or custom component territory I'm afraid.

-Jamie

|||

Thank you very, even if my problem isn't solved.

Personally I think it is a major flaw, because we are not able to track direcly back into the source, which sometimes is extremely important. I support your request for adding this information into the Pipeline without question.

Thank you again

Bernd

|||

berndS wrote:

Thank you very, even if my problem isn't solved.

Personally I think it is a major flaw, because we are not able to track direcly back into the source, which sometimes is extremely important. I support your request for adding this information into the Pipeline without question.

Thank you again

Bernd

Thanks Bernd,

In that case, click the first link in my last post and vote for it. I forgot to add the link in before (sorry) but I have now edited it!

I also think this is a really important feature!

-Jamie

|||

Hmm, hope it's OK to play devil's advocate here. How would this work in a data flow merge, conditional split, etc? Seems like a lot of the processing that is needed in ETL and supported in SSIS would confound the meaning of source row number?

|||

kenambrose wrote:

Hmm, hope it's OK to play devil's advocate here. How would this work in a data flow merge, conditional split, etc? Seems like a lot of the processing that is needed in ETL and supported in SSIS would confound the meaning of source row number?

Ken

Yeah of course it would, absolutely - in which case it has no meaning and you don't use it. Ideally you'd simply have a boolean property on every source adapter saying whether or not to add this "rownumber" column.

If its there and you don't want it - ignore it!

I'd still like to have this feature.

-Jamie

|||

I was just wondering if this might be one feature that sounds good on paper but has limited practical value. Since I insist on data models and domains that make use of natural keys (and always design solutions based on this paradigm), I can't see how it would be of much use in my work. If I ever need to find a row, I always can do so by using the key...

|||

kenambrose wrote:

I was just wondering if this might be one feature that sounds good on paper but has limited practical value. Since I insist on data models and domains that make use of natural keys (and always design solutions based on this paradigm), I can't see how it would be of much use in my work. If I ever need to find a row, I always can do so by using the key...

True enough. Where I think it would be useful would be when importing a file. If a row goes down the error flow it'd be nice to know which row it was.

-Jamie

|||

Jamie,

is there any way to get the error details from a source connector when you set it to ignore errors? Otherwise it would be easy to add the row number by a counter (there are some ways to do that, you know)... The only problem are the error lines. SSIS simply changes the value of a non-readable field to NULL. You don't get any error information (at least that's what I see). If there would be any way to find out that a record has an error (and probably what kind of error in which field) then we could just ignore all errors first, add the counter and do some manual error handling later...

Thanks,

|||

Thomas Pagel wrote:

Jamie,

is there any way to get the error details from a source connector when you set it to ignore errors?

I don't think so Thomas, no. Do these errors trigger the OnError eventhandler? I'm 99% sure they don't but don't have a SSIS instance to hand to check.

-Jamie