Error report: Interactive sorting loses the image reference

I have a table using the interactive sort feature and an image on each row.
On the report server when I sort the column the images are lost and a broken
link is displayed. In the Designer preview window the images are not lost.
Anyone else share my experience, and does anyone know how to work around
this issue?
Thanks,
Steve MunLeeuwIf I switch the image to embedded then it works as expected.
Thanks,
Steve MunLeeuw
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:O$osPQxtGHA.3392@.TK2MSFTNGP04.phx.gbl...
>I have a table using the interactive sort feature and an image on each row.
>On the report server when I sort the column the images are lost and a
>broken link is displayed. In the Designer preview window the images are
>not lost. Anyone else share my experience, and does anyone know how to work
>around this issue?
> Thanks,
> Steve MunLeeuw
>

Error Redirected to Script Component : Package Doesnt fail.

I have created a data flow task. In that, in a 'data conversion', if a column fails validation then that row is redirected to a script component, which in turn writes the error to a variable.

But though the error is generated and script component receives the error, package doesnt fail.

Is there any way to set the package result to failure inside the script component? I tried set 'FailPackageOnFailure' to true but doesnt work.

Any help is greatly appreciated.

Thanks,

Don

donMahya wrote:

I have created a data flow task. In that, in a 'data conversion', if a column fails validation then that row is redirected to a script component, which in turn writes the error to a variable.

But though the error is generated and script component receives the error, package doesnt fail.

Is there any way to set the package result to failure inside the script component? I tried set 'FailPackageOnFailure' to true but doesnt work.

Any help is greatly appreciated.

Thanks,

Don

You could raise an error within the script component using:

Me.Events.FireError(...) (something like that anyway)

-Jamie

|||

Sorry for the delay. Wasnt working on this part for some time.

I tried

Me.ComponentMetaData.FireError(0, "", strErrDesc, "", 0, True) - Where strErrDesc is error decription.

but still no luck

I tried setting the maxerrorcount to 0 but No Luck. Package still completes without any failure.

Any help or ideas are greatly appreciated.

Thanks,

Don

|||

You should return the failure value inside of your script main method. I do not remember the exact enumeration value but the intellisence will give it to you.

Thanks.

Error Redirected to Script Component : Package Doesnt fail.

I have created a data flow task. In that, in a 'data conversion', if a column fails validation then that row is redirected to a script component, which in turn writes the error to a variable.

But though the error is generated and script component receives the error, package doesnt fail.

Is there any way to set the package result to failure inside the script component? I tried set 'FailPackageOnFailure' to true but doesnt work.

Any help is greatly appreciated.

Thanks,

Don

donMahya wrote:

I have created a data flow task. In that, in a 'data conversion', if a column fails validation then that row is redirected to a script component, which in turn writes the error to a variable.

But though the error is generated and script component receives the error, package doesnt fail.

Is there any way to set the package result to failure inside the script component? I tried set 'FailPackageOnFailure' to true but doesnt work.

Any help is greatly appreciated.

Thanks,

Don

You could raise an error within the script component using:

Me.Events.FireError(...) (something like that anyway)

-Jamie

|||

Sorry for the delay. Wasnt working on this part for some time.

I tried

Me.ComponentMetaData.FireError(0, "", strErrDesc, "", 0, True) - Where strErrDesc is error decription.

but still no luck

I tried setting the maxerrorcount to 0 but No Luck. Package still completes without any failure.

Any help or ideas are greatly appreciated.

Thanks,

Don

|||

You should return the failure value inside of your script main method. I do not remember the exact enumeration value but the intellisence will give it to you.

Thanks.

error output - ignore just one column?

When configuring error output, I want everything that is good in the row to make it to the destination, and then the offending column that is causing an error to be set to NULL, and then sent to the destination as well. In addition, I want to take the offending column's data, and route it over to an error holding table. I know about the ability to redirect the whole row, but I just sort of want to redirect just that column. For example....

Have a table with 5 columns

col1 int null,

col2 int null,

col3 char(3) null,

col4 bit null,

col5 int null

My data flow loads data from a flat file and has a record that looks like this

1 5 ABC R 3

I want the row to make it to the destination as follows....

1 5 ABC NULL 3

Then the offending data needs to go over to my error table

err# errcolumn errdata errdesc

1 col4 R Could not convert "R" to bit data type

Any way to do this?

You could create a custom component, or a script component. Otherwise, you'd need to redirect the row, and use a multicast to send it to the error destination and put the row back into the main flow, after correcting the data.|||And even though you've redirected the row, you can still just operate on one column. The problem is knowing which column is in error.|||

Yes - this is the problem - the users who review the etl loads want to see: "You had invalid data in the BirthDate column". They do not want to see - "You had invalid data somewhere on record # 26".

I really want to stay inside SSIS to do these data type checks, but I'm leaning towards shoving everything into the engine & using isdate(), isnumeric(), etc. to get the flagrant data conversion stuff cleaned up. Then bring it back into SSIS to do lookups & "softer" validations. My real table (unlike my example) has 50+ columns to do checks on, and I don't want 50 branches or more in my SSIS package, all with error flows, and unions everywhere.

Is there any way of keeping this in SSI S, using the Data conversion/Derived Column transforms, and capturing column specific errors, instead of generic row errors?

|||Take a look at this post - it may have an answer for you - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1509836&SiteID=1|||I saw that yesterday - it looked promising, but I'm relatively sure that the folks who run the servers here won't allow anything other than the Microsoft released stuff onto the servers. I'm toying with a workaround right now that hopefully will work........I'll check back in.sql

error output - ignore just one column?

When configuring error output, I want everything that is good in the row to make it to the destination, and then the offending column that is causing an error to be set to NULL, and then sent to the destination as well. In addition, I want to take the offending column's data, and route it over to an error holding table. I know about the ability to redirect the whole row, but I just sort of want to redirect just that column. For example....

Have a table with 5 columns

col1 int null,

col2 int null,

col3 char(3) null,

col4 bit null,

col5 int null

My data flow loads data from a flat file and has a record that looks like this

1 5 ABC R 3

I want the row to make it to the destination as follows....

1 5 ABC NULL 3

Then the offending data needs to go over to my error table

err# errcolumn errdata errdesc

1 col4 R Could not convert "R" to bit data type

Any way to do this?

You could create a custom component, or a script component. Otherwise, you'd need to redirect the row, and use a multicast to send it to the error destination and put the row back into the main flow, after correcting the data.|||And even though you've redirected the row, you can still just operate on one column. The problem is knowing which column is in error.|||

Yes - this is the problem - the users who review the etl loads want to see: "You had invalid data in the BirthDate column". They do not want to see - "You had invalid data somewhere on record # 26".

I really want to stay inside SSIS to do these data type checks, but I'm leaning towards shoving everything into the engine & using isdate(), isnumeric(), etc. to get the flagrant data conversion stuff cleaned up. Then bring it back into SSIS to do lookups & "softer" validations. My real table (unlike my example) has 50+ columns to do checks on, and I don't want 50 branches or more in my SSIS package, all with error flows, and unions everywhere.

Is there any way of keeping this in SSI S, using the Data conversion/Derived Column transforms, and capturing column specific errors, instead of generic row errors?

|||Take a look at this post - it may have an answer for you - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1509836&SiteID=1|||I saw that yesterday - it looked promising, but I'm relatively sure that the folks who run the servers here won't allow anything other than the Microsoft released stuff onto the servers. I'm toying with a workaround right now that hopefully will work........I'll check back in.

Error on insert to

Cannot insert explicit value for identity column in
table 'INCOMING_TEMP' when IDENTITY_INSERT is set to OFf
hi garry,
you will have to SET option identity_insert on the table. also while
inserting you will have to specify column list.
ex:
set identity_insert <table> on
insert into <table> (col1, col2) values(1,1)
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Error on INSERT Query

I want to insert "489d68bf-baa1-415d-94c7-a8e56b-609c61" in a column in sql
server . The data type of this column is varchar (100). i am getting the
folloing error - incorrect syntax near '68bf'. I think it is not accepting
the '-'. How can i get a work around for the '-' so that the above values
gets inserted successfully.
Vijay,
can you post some sample DDL for the table, as well as the insert statement
that's throwing the error? that value should be legal in the column.
David
"Vijay" <Vijay@.discussions.microsoft.com> wrote in message
news:233A3D0E-1A73-424A-BB00-608D9676247B@.microsoft.com...
>I want to insert "489d68bf-baa1-415d-94c7-a8e56b-609c61" in a column in sql
> server . The data type of this column is varchar (100). i am getting the
> folloing error - incorrect syntax near '68bf'. I think it is not accepting
> the '-'. How can i get a work around for the '-' so that the above values
> gets inserted successfully.
|||Are you trying to insert via a stored procedure, or a simple insert
statement?
Try delimiting the string with single quotes. As in:
INSERT INTO YourTable (YourColumn) VALUES
('489d68bf-baa1-415d-94c7-a8e56b-609c61')
Replace YourTable and YourColumn with whatever is appropriate
Keith Kratochvil
"Vijay" <Vijay@.discussions.microsoft.com> wrote in message
news:233A3D0E-1A73-424A-BB00-608D9676247B@.microsoft.com...
>I want to insert "489d68bf-baa1-415d-94c7-a8e56b-609c61" in a column in sql
> server . The data type of this column is varchar (100). i am getting the
> folloing error - incorrect syntax near '68bf'. I think it is not accepting
> the '-'. How can i get a work around for the '-' so that the above values
> gets inserted successfully.
sql

Error on bcp

Hi again

Sometimes, in some files .dat, thee following error occurs:

#@. Row 1, Column 8: Invalid character value for cast specification @.#
3877 597 2.0 Manual 1C15H4 <NULL> 24590.0000 1 2 G 2.0 ATIVO US$ 2006 2006 2 <NULL>

But the columns are OK.

Somebody can help me?The error indicates that some value in the line could not be converted to the data type of the destination column. It can be due to the format of the specified value. If you post a repro script then it might be easier to tell what the problem might be.|||Hi,

Sorry.. Only today I saw your response.

So, the dat file that I use is something like this:

1;ABADIA;12*
2;ABADIA DOS DOURADOS;18*
3;ABADIANIA;16*
4;ABAETE;18*
5;ABAETETUBA;21*
6;ABAIARA;13*
7;ABAIBA;18*
8;ABAIRA;12*
9;ABARE;12*
10;ABATIA;25*
11;ABELARDO LUZ;31*
12;ABOBORA;12*
13;ABRANTES;12*
14;ABRE CAMPO;18*
15;ABREU E LIMA;23*
16;ABUNA;28*
17;ACAIACA;18*
18;ACAILANDIA;17*
19;ACAJUTIBA;12*
20;ACARA;21*

and the command line that I use is:

bcp cdiretodev5.dbo.cidade in "./loader/dat/cidade.dat" -U sa -P pwd123 -S BART\SQLEXPRESS -c -t; -r * -E -e d:\cidade.log

The table is:

table name: cidade

columns:

id_cidade numeric(10)
no_cidade varchar(50)
id_estado numeric(4)

Thanks!!!!|||Your row terminator seems incorrect. Do you have CR/LF after *? If so, you need to change the -r option to -r "*\n" or -r "*\r\n". See the Books Online topic on BCP utility for details on the options, format files etc.|||Hi,

thank you for your help.. Only now I could see.. sorry...
well, I put the option with -r "*\n", but and some lines, The bcp doesn′t generate the *, like follows:

155;237;SLX;;;R$;;1;5;;1;;;;;;;2;;;2 ou 4;0;007001-7;;*
156;237;SDX;;;R$;;1;5;;1;;;;;;;2;;;2 ou 4;0;007001-7;;*
1317;511;3.0 E;;;R$;;1;5;;1;;;;;;;2;;;2 ou 4;0;021069-2;;*
1318;511;3.0 SL;;;R$;;1;5;;1;;;;;;;2;;;2 ou 4;0;021070-6;;*
1988;46;Sedan Wind 1.0;5C19Z1;SIMP;R$;18486.0000;1;5;;1;Powertech 1.0L M.P.F.I. 4cil.;4056 mm;46 l;8,3 mkgf a 3.000 rpm;14.8 km/l;11.7 km/l;1;2000;2001;4;0;

Is there a limit for number of columns to line?

The new error now is #@. Row 1, Column 27: String data, right truncation @.#

thank you very much.|||The error message indicates that the data in column 27 or row 1 exceeds length of the destination column. So check the length of data against your table's column definition. There is a limit to the number of columns in a table in SQL Server but there is really none in the data file. As long as you have to terminators defined properly BCP should be able to parse the file correctly assuming it is formatted correctly.

Error on bcp

Hi again

Sometimes, in some files .dat, thee following error occurs:

#@. Row 1, Column 8: Invalid character value for cast specification @.#
3877 597 2.0 Manual 1C15H4 <NULL> 24590.0000 1 2 G 2.0 ATIVO US$ 2006 2006 2 <NULL>

But the columns are OK.

Somebody can help me?The error indicates that some value in the line could not be converted to the data type of the destination column. It can be due to the format of the specified value. If you post a repro script then it might be easier to tell what the problem might be.|||Hi,

Sorry.. Only today I saw your response.

So, the dat file that I use is something like this:

1;ABADIA;12*
2;ABADIA DOS DOURADOS;18*
3;ABADIANIA;16*
4;ABAETE;18*
5;ABAETETUBA;21*
6;ABAIARA;13*
7;ABAIBA;18*
8;ABAIRA;12*
9;ABARE;12*
10;ABATIA;25*
11;ABELARDO LUZ;31*
12;ABOBORA;12*
13;ABRANTES;12*
14;ABRE CAMPO;18*
15;ABREU E LIMA;23*
16;ABUNA;28*
17;ACAIACA;18*
18;ACAILANDIA;17*
19;ACAJUTIBA;12*
20;ACARA;21*

and the command line that I use is:

bcp cdiretodev5.dbo.cidade in "./loader/dat/cidade.dat" -U sa -P pwd123 -S BART\SQLEXPRESS -c -t; -r * -E -e d:\cidade.log

The table is:

table name: cidade

columns:

id_cidade numeric(10)
no_cidade varchar(50)
id_estado numeric(4)

Thanks!!!!|||Your row terminator seems incorrect. Do you have CR/LF after *? If so, you need to change the -r option to -r "*\n" or -r "*\r\n". See the Books Online topic on BCP utility for details on the options, format files etc.|||Hi,

thank you for your help.. Only now I could see.. sorry...
well, I put the option with -r "*\n", but and some lines, The bcp doesn′t generate the *, like follows:

155;237;SLX;;;R$;;1;5;;1;;;;;;;2;;;2 ou 4;0;007001-7;;*
156;237;SDX;;;R$;;1;5;;1;;;;;;;2;;;2 ou 4;0;007001-7;;*
1317;511;3.0 E;;;R$;;1;5;;1;;;;;;;2;;;2 ou 4;0;021069-2;;*
1318;511;3.0 SL;;;R$;;1;5;;1;;;;;;;2;;;2 ou 4;0;021070-6;;*
1988;46;Sedan Wind 1.0;5C19Z1;SIMP;R$;18486.0000;1;5;;1;Powertech 1.0L M.P.F.I. 4cil.;4056 mm;46 l;8,3 mkgf a 3.000 rpm;14.8 km/l;11.7 km/l;1;2000;2001;4;0;

Is there a limit for number of columns to line?

The new error now is #@. Row 1, Column 27: String data, right truncation @.#

thank you very much.|||The error message indicates that the data in column 27 or row 1 exceeds length of the destination column. So check the length of data against your table's column definition. There is a limit to the number of columns in a table in SQL Server but there is really none in the data file. As long as you have to terminators defined properly BCP should be able to parse the file correctly assuming it is formatted correctly.

Error number for duplicate column value during an insert for a unique column ?

I have a table in which a non-primary key column has a unique index on it.

If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?

Open a query analyzer and try to manually insert a value into the table and see what SQL complains..

|||

The error number for inserting duplicate value in a column of unique index is : 2601.

Error number 207 : invalid column name "***"

i got this error when i add a table as atricle
but i am pretty sure the column exist in this table,
anyone experences thos error before?
thanks for any comments
cheers
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:31DD5B76-93C8-4D1F-B7EF-6CCF11745352@.microsoft.com...
> i got this error when i add a table as atricle
> but i am pretty sure the column exist in this table,
> anyone experences thos error before?
> thanks for any comments
>
> cheers
Nick,
This looks like a SQL server bug, see this thread from a week or so back -
sp_repladdcolumn failure/Merge Replication Fails with Error 207 While
Generating a Snapshot/KBArticle 821535
Kestutis Adomavicius provides some good feedback.
TT
|||also can you script out the publication, and the schema of the
tables/objects you are replicating and post them here.
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:31DD5B76-93C8-4D1F-B7EF-6CCF11745352@.microsoft.com...
>i got this error when i add a table as atricle
> but i am pretty sure the column exist in this table,
> anyone experences thos error before?
> thanks for any comments
>
> cheers