how to treat truncations as a warning

I'm using SSIS to migrate data from one system to another. This is a usual extract, transform, cleanse and load type task.

The error handling is critical to get right. E.g. truncation of data on one column should stop that row being loaded but for other columns I might be happy to carry on loading the row but record a warning.

I'm finding the error disposition a bit limiting. I really feel the need for an 'Issue Warning' disposition which will act the same way as 'Ignore Error' in that the row continues being processed but will in addition copy a row to a warning output so that I can write a message to a log file for someone to manually investigate and correct that item of data post the conversion. Alternatively it would be useful to specify a severity (at a column level) when redirecting error output. This way I can put logic into a downstream component which would treat the error row differently depending on the severity of the error.

Am I missing a trick?

There's no built-in switch for enabling this behavior, but you can accomplish it with creative use of error redirection. I use error redirection or a conditional split to identify rows that either cause warnings or errors, flag them appropriately, then send the errors to a logging table. I send the warnings to a multicast that outputs the rows to both a logging table and to a Union All to put them back into the main flow.|||this will work for me, though it's a shame there isn't a built-in feature...|||

Nick Corrie wrote:

this will work for me, though it's a shame there isn't a built-in feature...

Nick,

That could be a good suggestion to make; you can post it at the connect site: http://connect.microsoft.com/VisualStudio/Feedback

How to trap an error from insert ?

When inserting rows from a staging table to a production one, I need to
convert a column of type varchar to type int. Often there're rows that
have junk data in this column and that makes convert() fail. Is it
possible to know that such junk rows exist without getting an error
message ? In other words, is there a way to prevent the insert query
from throwing an error msg, but I still know that it fails ?
thanks,
TamYou can use the ISNUMERIC() to determine whether an
expression is a valid numeric type.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1124509954.485547.268620@.o13g2000cwo.googlegroups.com...
> When inserting rows from a staging table to a production one, I need to
> convert a column of type varchar to type int. Often there're rows that
> have junk data in this column and that makes convert() fail. Is it
> possible to know that such junk rows exist without getting an error
> message ? In other words, is there a way to prevent the insert query
> from throwing an error msg, but I still know that it fails ?
> thanks,
> Tam
>|||I've tried isnumeric() - indeed this was the first thing I did. Yet
strings that have character like 'd', 'e', '.' also pass isnumeric()
but are not convertible to int, and these characters happen quite
commonly in the junk rows in my DB. Any other suggestions ?|||You can check for all digits with:
CASE WHEN
REPLACE(
REPLACE(
.
REPLACE (num '0',''),
1, ''),
.
9, '') = '' THEN CAST(num AS INTEGER) ELSE NULL END|||On 20 Aug 2005 18:30:21 -0700, --CELKO-- wrote:

>You can check for all digits with:
>CASE WHEN
>REPLACE(
> REPLACE(
> ..
> REPLACE (num '0',''),
> 1, ''),
> ..
> 9, '') = '' THEN CAST(num AS INTEGER) ELSE NULL END
Hi Joe,
Never rely on implicit conversion if you don't have to. Use quotes
around 1, 2, ..., 9 as well to prevent conversions.
And of course, this is lots more complicated then encessary:
CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||these are interesting solutions. btw, is there any "try-catch"
structure in sql server as I'm concerned there're cases that exceptions
are not known before hand ?
thanks,
Tam|||>> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
Should be
> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CAST(num AS INTEGER) END
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:kgrgg11r74k425gau3958b7dgjjtcsu9vu@.
4ax.com...
> On 20 Aug 2005 18:30:21 -0700, --CELKO-- wrote:
>
> Hi Joe,
> Never rely on implicit conversion if you don't have to. Use quotes
> around 1, 2, ..., 9 as well to prevent conversions.
> And of course, this is lots more complicated then encessary:
> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Yep. But in SQL Server 2005
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1124668962.565306.81090@.g43g2000cwa.googlegroups.com...
> these are interesting solutions. btw, is there any "try-catch"
> structure in sql server as I'm concerned there're cases that exceptions
> are not known before hand ?
> thanks,
> Tam
>|||On Mon, 22 Aug 2005 11:06:30 +0530, Roji. P. Thomas wrote:
>Should be
Hi Roji,
You are correct that I made a mistake. But your correction is wrong too
(since it's an exact same copy - you obviously forgot to correct the
mistake before posting).
For others reading this discussion: the correct statement is
(Note how the caret has sneaked one position to the right)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> You are correct that I made a mistake. But your correction is wrong too
> (since it's an exact same copy - you obviously forgot to correct the
> mistake before posting).
:)
In a hurry to correct you asap, I refuse to look at the finer details :)
Infact I have'nt noticed the misplaced caret, but only the mispelled CAST.
Lets keep correcting each other :p
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:m1mkg19d2vmgq7vvus5ncer3di83gdvhut@.
4ax.com...
> On Mon, 22 Aug 2005 11:06:30 +0530, Roji. P. Thomas wrote:
>
> Hi Roji,
>
> You are correct that I made a mistake. But your correction is wrong too
> (since it's an exact same copy - you obviously forgot to correct the
> mistake before posting).
> For others reading this discussion: the correct statement is
>
> (Note how the caret has sneaked one position to the right)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

how to transfer gdb format data to SQL server

My problem is aquisiton data with gdb extension.There is not driver of that type in DTS of SQL Server 2000.
Can anybody help??Assuming that you have an ODBC or OLEDB driver that allows you to use the GDB file, you could use that.

-PatP|||I feel this .GDB is from mainframe or unix if so as suggested use the compatible odbc driver.|||Thank you everybody
I found ODBC driver and everythink's working well (for the time being at least:))

how to tell type of replication

Hi admins,
If there's a system with running replication on it then how to tell which
type of replication is that? Where and what should I look at?
Thanks you a lot in advance for your inputsp_helpreplicationoption
See BOL for details
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Alex" <alex_remove_this_@.telus.net> wrote in message
news:Y9YAd.35482$dv1.4877@.edtnps89...
> Hi admins,
> If there's a system with running replication on it then how to tell which
> type of replication is that? Where and what should I look at?
> Thanks you a lot in advance for your input
>

how to tell type of replication

Hi admins,
If there's a system with running replication on it then how to tell which
type of replication is that? Where and what should I look at?
Thanks you a lot in advance for your input
sp_helpreplicationoption
See BOL for details
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Alex" <alex_remove_this_@.telus.net> wrote in message
news:Y9YAd.35482$dv1.4877@.edtnps89...
> Hi admins,
> If there's a system with running replication on it then how to tell which
> type of replication is that? Where and what should I look at?
> Thanks you a lot in advance for your input
>
sql

how to tell type of replication

Hi admins,
If there's a system with running replication on it then how to tell which
type of replication is that? Where and what should I look at?
Thanks you a lot in advance for your inputsp_helpreplicationoption
See BOL for details
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Alex" <alex_remove_this_@.telus.net> wrote in message
news:Y9YAd.35482$dv1.4877@.edtnps89...
> Hi admins,
> If there's a system with running replication on it then how to tell which
> type of replication is that? Where and what should I look at?
> Thanks you a lot in advance for your input
>

How to store large chunks for binary data into the DB?

From what I can see, the 'varbinary(max)' data type is not supported, and the 'image' data type is supposed to go away. Is there some other way to store large chunks (10MB to 100MB) of data into an SSEv DB?

If I have to use the 'image' data type to so this, does anyone have a code sample that would let me push an array() of numbers into an 'image' field, and unload an 'image' field into an array()?

TIA

Pat

That hardly makes sense to store 100M of binary data in the database even without 4GB database size limit. Consider storing file name instead.

|||ok but say i need to store an arbitrary large amount of data in a database and it needs to be only one file, what would you suggest|||

OK, say you have huge files in the DB. Now, how you going to work with them? Run DataReader and load them into memory? How you going to update them? Say, you need to change one bit in a huge file. With file system you open a file, seek to position and change this bit. With database you have to load entire file into memory, change one bit and save it back. Don’t you think it will be a bit slow if file is huge?

Why not simply keep big files where they belong – on a file system so you can work with them easily?

As to one file equipment, I don't quite understand it. Are you working on some special file system which only allows for one file?

|||Ilya: That is what "structured storage" is supposed to be for. I.e. ILockBytes|||

That needs support from the database and I don't believe it's supported by SQL Ev. No problems with files on the other hand. I also don't believe it's available on devices at all (SQL Ev used to be SQL CE), but memory mapped files API is available.

|||

SQL Server CE/Mobile 3.0 supports ILockBytes in OLEDB Provider. I dont know if ILockBytes exists in managed code!

Thanks,

Laxmi

How to store large chunks for binary data into the DB?

From what I can see, the 'varbinary(max)' data type is not supported, and the 'image' data type is supposed to go away. Is there some other way to store large chunks (10MB to 100MB) of data into an SSEv DB?

If I have to use the 'image' data type to so this, does anyone have a code sample that would let me push an array() of numbers into an 'image' field, and unload an 'image' field into an array()?

TIA

Pat

That hardly makes sense to store 100M of binary data in the database even without 4GB database size limit. Consider storing file name instead.

|||ok but say i need to store an arbitrary large amount of data in a database and it needs to be only one file, what would you suggest|||

OK, say you have huge files in the DB. Now, how you going to work with them? Run DataReader and load them into memory? How you going to update them? Say, you need to change one bit in a huge file. With file system you open a file, seek to position and change this bit. With database you have to load entire file into memory, change one bit and save it back. Don’t you think it will be a bit slow if file is huge?

Why not simply keep big files where they belong – on a file system so you can work with them easily?

As to one file equipment, I don't quite understand it. Are you working on some special file system which only allows for one file?

|||Ilya: That is what "structured storage" is supposed to be for. I.e. ILockBytes|||

That needs support from the database and I don't believe it's supported by SQL Ev. No problems with files on the other hand. I also don't believe it's available on devices at all (SQL Ev used to be SQL CE), but memory mapped files API is available.

|||

SQL Server CE/Mobile 3.0 supports ILockBytes in OLEDB Provider. I dont know if ILockBytes exists in managed code!

Thanks,

Laxmi

How to store large chunks for binary data into the DB?

From what I can see, the 'varbinary(max)' data type is not supported, and the 'image' data type is supposed to go away. Is there some other way to store large chunks (10MB to 100MB) of data into an SSEv DB?

If I have to use the 'image' data type to so this, does anyone have a code sample that would let me push an array() of numbers into an 'image' field, and unload an 'image' field into an array()?

TIA

Pat

That hardly makes sense to store 100M of binary data in the database even without 4GB database size limit. Consider storing file name instead.

|||ok but say i need to store an arbitrary large amount of data in a database and it needs to be only one file, what would you suggest|||

OK, say you have huge files in the DB. Now, how you going to work with them? Run DataReader and load them into memory? How you going to update them? Say, you need to change one bit in a huge file. With file system you open a file, seek to position and change this bit. With database you have to load entire file into memory, change one bit and save it back. Don’t you think it will be a bit slow if file is huge?

Why not simply keep big files where they belong – on a file system so you can work with them easily?

As to one file equipment, I don't quite understand it. Are you working on some special file system which only allows for one file?

|||Ilya: That is what "structured storage" is supposed to be for. I.e. ILockBytes|||

That needs support from the database and I don't believe it's supported by SQL Ev. No problems with files on the other hand. I also don't believe it's available on devices at all (SQL Ev used to be SQL CE), but memory mapped files API is available.

|||

SQL Server CE/Mobile 3.0 supports ILockBytes in OLEDB Provider. I dont know if ILockBytes exists in managed code!

Thanks,

Laxmi

How to store jpg's in an image column

Hi
I want to store an image (not just the pointer to the
image, but the entire image) in an image data type column
in my database.
How do I do this? The images will be small (7kb) so I'd
like to do this rather than just store the pointer to an
image on the file system.
Thanks in advance !!
-H
Some ideas here: http://vyaskn.tripod.com/programming_faq.htm#q5
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:98c001c433c2$5da25c10$a501280a@.phx.gbl...
Hi
I want to store an image (not just the pointer to the
image, but the entire image) in an image data type column
in my database.
How do I do this? The images will be small (7kb) so I'd
like to do this rather than just store the pointer to an
image on the file system.
Thanks in advance !!
-H

How to store jpg's in an image column

Hi
I want to store an image (not just the pointer to the
image, but the entire image) in an image data type column
in my database.
How do I do this? The images will be small (7kb) so I'd
like to do this rather than just store the pointer to an
image on the file system.
Thanks in advance !!
-HSome ideas here: http://vyaskn.tripod.com/programming_faq.htm#q5
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:98c001c433c2$5da25c10$a501280a@.phx.gbl...
Hi
I want to store an image (not just the pointer to the
image, but the entire image) in an image data type column
in my database.
How do I do this? The images will be small (7kb) so I'd
like to do this rather than just store the pointer to an
image on the file system.
Thanks in advance !!
-H

How to store jpg's in an image column

Hi
I want to store an image (not just the pointer to the
image, but the entire image) in an image data type column
in my database.
How do I do this? The images will be small (7kb) so I'd
like to do this rather than just store the pointer to an
image on the file system.
Thanks in advance !!
-HSome ideas here: http://vyaskn.tripod.com/programming_faq.htm#q5
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:98c001c433c2$5da25c10$a501280a@.phx.gbl...
Hi
I want to store an image (not just the pointer to the
image, but the entire image) in an image data type column
in my database.
How do I do this? The images will be small (7kb) so I'd
like to do this rather than just store the pointer to an
image on the file system.
Thanks in advance !!
-H

How to store image datatype value to a variable?

I have image type col.
I'm trying to do the following,
DECLARE @.Data varbinary(16)
SET @.Data = (select imageCol from Table1 where id=3)
As image datatype returns varbinary value, so I want to store image col value to a varbinary variable(or any other type variable, eg., varchar). But getting following error,
========================================
Server: Msg 279, Level 16, State 3, Line 2
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
========================================
Is there anyway to store image datatype value to a variable?
Cheers.

Try this:
select @.Data=imageCol from Table1 where id=3

|||

mbanavige wrote:

Try this:
select @.Data=imageCol from Table1 where id=3

Thanks a lot.

How to store HTML data in SQL

Hi friends

I want to add HTML to the SQL server as data please can anyone help me. which data type . & how to store the data.

Thanks for all your support

Thanks & regards

ASPFreak

You can use a TEXT type or BLOB. I would personally use gzip to compress the string before storing it and uncompressed when retrieving.

Hope this helps

|||

Do you know or expect the amount of HTML you will have? If yes, then user varchar or nvarchar. If not, use text or ntext.

How to store them? Treat them as notmal text.

Example:

1Insert into MyTable (PageNumber, PageName , HTML)2 Values (1 ,'Index.html','<p>This is a sample</p>')3

Good luck.

|||

What approach did you take?

How to store GPS Coordinates in database, Which Data Type?

I am working on a program in VB 2005 in which i want to store and retrieve GPS coordinates. I am not sure which data type is the best to use to enter Latitude & Longitude numbers and maintain their proper integrity.

Like LAT ( N38 28.025' ) and LONG (W105 52.098' )

The numbers will be entered by the user and that format can be maintained, but how to re-enter & or insert them into the database using the same format is my real question.

I hope I have explained this right. The numbers in BOLD are what I need to maintain.

Thanks for any help in advance.

Steve

Hi Steve,

are you using SQL Server 2005 ? YOu could create your own data type for that ! I am not quite sure if since now anybody did already one for that reason but it would be worth a try searching in google for CLR +data type +sql server 2005 (and then the appropiate buzz words you probably know for your problem, like special names for the coordinates etc.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

You could look at a SQL CLR User defined data type.The other option is to store as integers (or numerics) and then have a function that converts from the raw numeric value to the format above.

With a UDT you implement a parse method that allows you to interpret text into a value that you can store.

Be careful with UDTs though as changing them once in place is very difficult, as it is in use and so you can't just upgrade the base assembly.

How to store an email on SQL server 2000?

Is it possible to store an email in SQL server 2000? Do we need to define our own data type for that, if yes then how? or do we have to store it as an object, again how? I want to save email after sending it by my ASP .NET application and then retrieve it at a later stage......Anyone please help?

You could create a new email table and just log to it every time you send. That is what we do. Below is our table schema.

Email_Log
-----
ID int Not Null
From varchar(100) Not Null
To Text Not Null
CC Text
BCC Text
Subject varchar(250)
Body Text
CreatedDate DateTime

|||

Email_Log
-----
ID int Not Null
From varchar(100) Not Null
To Text Not Null
CC Text
BCC Text
Subject varchar(250)
Body Text
CreatedDate DateTime

We can only use this schema when the email body is plain text without pictures or formatting information. I would suggest storing the email body using binary files. Or we can store the email body on a file server, while keeping a link in the table which points to the location where the email is stored.

Hope my suggestion helps

How To Start using Query Analyzer

I'm new to sql what do you type in at the top of the Query Analyzer then you
start on the Query
Hi,
1. Choose query analyzer from SQL Server Program groups
2. In the security option, choose either Windows or SQL Authentication.
3. If it is SQL authentication give user name and password and click OK. For
Windows authentication, just click OK.
4. Once you login you could give all your TSQL commands inside that.
6. For Eg: Select * from sysobjects
7. For Execution just press F5 button in keyboard
For TSQL command usage have a look into SQL Server Books online
Thanks
Hari
SQL Server MVP
"TYE" <TYE@.discussions.microsoft.com> wrote in message
news:CCD0D16A-33DA-4802-B506-63CDB4EACA28@.microsoft.com...
>
> I'm new to sql what do you type in at the top of the Query Analyzer then
> you
> start on the Query

How To Start using Query Analyzer

I'm new to sql what do you type in at the top of the Query Analyzer then you
start on the QueryHi,
1. Choose query analyzer from SQL Server Program groups
2. In the security option, choose either Windows or SQL Authentication.
3. If it is SQL authentication give user name and password and click OK. For
Windows authentication, just click OK.
4. Once you login you could give all your TSQL commands inside that.
6. For Eg: Select * from sysobjects
7. For Execution just press F5 button in keyboard
For TSQL command usage have a look into SQL Server Books online
Thanks
Hari
SQL Server MVP
"TYE" <TYE@.discussions.microsoft.com> wrote in message
news:CCD0D16A-33DA-4802-B506-63CDB4EACA28@.microsoft.com...
>
> I'm new to sql what do you type in at the top of the Query Analyzer then
> you
> start on the Query