especial SUM

I wonderin if I can make this type of sum in SQL Reporting Services.

sum( iif(Fields!ESTADO_TRANSAC_POS.Value = "V",Fields!TOTAL_GENERAL.Value,cdbl("0.00")),"source")

i get an error field : (

i dont want to sum all the rows just the ones that where Estado_Transac = "V"

can you help me : )

i found the answer in this thread Conditional Sum/Runnining Total

sql

escaping single quotes

Hi,

I need to have an varchar value with single quotes. For eg: the below code throws compilation error.

Declare @.val VARCHAR(20)
SELECT @.val = ''+name+''
print @.val

Error: Invalid column 'name'

I want to print name enclosed with single quotes. Please guide me.

Regards,
SamDeclare @.val VARCHAR(20)
SELECT @.val = 'Kaiowas'
PRINT @.VAL

EDIT (added): SET @.VAL = @.VAL + '''Kaiowas'''
EDIT (added): PRINT @.VAL

SET @.VAL = @.VAL + '''' + @.VAL + ''''
PRINT @.VAL

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
I'm not using any fancy ado.net objects:
string sql= [whatever the user passes in]
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionString].ToString());
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
int result = command.ExecuteNonQuery();
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
connection.Close();On 6 4 , 8 48 , eggie5 <egg...@.gmail.com> wrote:
> I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql=3D [whatever the user passes in]
> SqlConnection connection =3D new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionS=tr=ADing].ToString());
> connection.Open();
> SqlCommand command =3D connection.CreateCommand();
> command.CommandType =3D CommandType.Text;
> command.CommandText =3D sql;
> try
> {
> int result =3D command.ExecuteNonQuery();
> if (result !=3D 1)
> {
> Response.StatusCode =3D 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode =3D 500;
> Response.End();
> }
> connection.Close();
You can string.replace() method to escape
charater ' by ''(double single quotes).|||If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.
> Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
The Best Practice is to use parameters rather than build a SQL statement
string. Not only does this eliminate the need to escape quotes, it's much
more secure because it's not vulnerable to SQL injection. Simple example
below.
command.CommandText = "INSERT INTO dbo.MyTable VALUES(@.UserParameter)";
SqlParameter param = new SqlParameter("@.UserParameter",
userSuppliedValue);
command.Parameters.Add(param);
command.ExecuteNonQuery();
--
Hope this helps.
Dan Guzman
SQL Server MVP
"eggie5" <eggie5@.gmail.com> wrote in message
news:1180918088.976008.41270@.q75g2000hsh.googlegroups.com...
>I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionString].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
>
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
>

Escape characters

What should one pass as a field value into a table in the insert
statement if the value contained a percentage symbol (%) or the
asterisk symbol (*), since both of these have special wildcard
meanings. What if I want to pass the special meanings and pass them as
literals? Is there any escape character that I must use? I am using
ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
SQL Server 2000.
Hi
In as search condition there is the ESCAPE clause that allows you to specify
the escape character, you can also specify a literal character by using
square brackets.
See the topic "Pattern Matching in Search Conditions" in Books online.
When you are inserting the characters there should not be a need to escape
them e.g
CREATE TABLE #fred ( col1 varchar(10) )
INSERT INTO #fred values ( '%_*' )
INSERT INTO #fred select '%_*'
select * from #fred
col1
%_*
%_*
John
"Sathyaish" wrote:

> What should one pass as a field value into a table in the insert
> statement if the value contained a percentage symbol (%) or the
> asterisk symbol (*), since both of these have special wildcard
> meanings. What if I want to pass the special meanings and pass them as
> literals? Is there any escape character that I must use? I am using
> ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
> SQL Server 2000.
>
|||While INSERTing data, you can simply pass data containig % inside single
quotes. % has a meaning in the context of LIKE, not in INSERT.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sathyaish" <sathyaish@.gmail.com> wrote in message
news:1125988873.864860.108830@.g44g2000cwa.googlegr oups.com...
What should one pass as a field value into a table in the insert
statement if the value contained a percentage symbol (%) or the
asterisk symbol (*), since both of these have special wildcard
meanings. What if I want to pass the special meanings and pass them as
literals? Is there any escape character that I must use? I am using
ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
SQL Server 2000.

Escape characters

What should one pass as a field value into a table in the insert
statement if the value contained a percentage symbol (%) or the
asterisk symbol (*), since both of these have special wildcard
meanings. What if I want to pass the special meanings and pass them as
literals? Is there any escape character that I must use? I am using
ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
SQL Server 2000.Hi
In as search condition there is the ESCAPE clause that allows you to specify
the escape character, you can also specify a literal character by using
square brackets.
See the topic "Pattern Matching in Search Conditions" in Books online.
When you are inserting the characters there should not be a need to escape
them e.g
CREATE TABLE #fred ( col1 varchar(10) )
INSERT INTO #fred values ( '%_*' )
INSERT INTO #fred select '%_*'
select * from #fred
col1
--
%_*
%_*
John
"Sathyaish" wrote:
> What should one pass as a field value into a table in the insert
> statement if the value contained a percentage symbol (%) or the
> asterisk symbol (*), since both of these have special wildcard
> meanings. What if I want to pass the special meanings and pass them as
> literals? Is there any escape character that I must use? I am using
> ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
> SQL Server 2000.
>|||While INSERTing data, you can simply pass data containig % inside single
quotes. % has a meaning in the context of LIKE, not in INSERT.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sathyaish" <sathyaish@.gmail.com> wrote in message
news:1125988873.864860.108830@.g44g2000cwa.googlegroups.com...
What should one pass as a field value into a table in the insert
statement if the value contained a percentage symbol (%) or the
asterisk symbol (*), since both of these have special wildcard
meanings. What if I want to pass the special meanings and pass them as
literals? Is there any escape character that I must use? I am using
ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
SQL Server 2000.

Escape characters

What should one pass as a field value into a table in the insert
statement if the value contained a percentage symbol (%) or the
asterisk symbol (*), since both of these have special wildcard
meanings. What if I want to pass the special meanings and pass them as
literals? Is there any escape character that I must use? I am using
ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
SQL Server 2000.Hi
In as search condition there is the ESCAPE clause that allows you to specify
the escape character, you can also specify a literal character by using
square brackets.
See the topic "Pattern Matching in Search Conditions" in Books online.
When you are inserting the characters there should not be a need to escape
them e.g
CREATE TABLE #fred ( col1 varchar(10) )
INSERT INTO #fred values ( '%_*' )
INSERT INTO #fred select '%_*'
select * from #fred
col1
--
%_*
%_*
John
"Sathyaish" wrote:

> What should one pass as a field value into a table in the insert
> statement if the value contained a percentage symbol (%) or the
> asterisk symbol (*), since both of these have special wildcard
> meanings. What if I want to pass the special meanings and pass them as
> literals? Is there any escape character that I must use? I am using
> ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
> SQL Server 2000.
>|||While INSERTing data, you can simply pass data containig % inside single
quotes. % has a meaning in the context of LIKE, not in INSERT.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sathyaish" <sathyaish@.gmail.com> wrote in message
news:1125988873.864860.108830@.g44g2000cwa.googlegroups.com...
What should one pass as a field value into a table in the insert
statement if the value contained a percentage symbol (%) or the
asterisk symbol (*), since both of these have special wildcard
meanings. What if I want to pass the special meanings and pass them as
literals? Is there any escape character that I must use? I am using
ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
SQL Server 2000.sql

Escape characters

What should one pass as a field value into a table in the insert
statement if the value contained a percentage symbol (%) or the
asterisk symbol (*), since both of these have special wildcard
meanings. What if I want to pass the special meanings and pass them as
literals? Is there any escape character that I must use? I am using
ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
SQL Server 2000.Replied in Micorsoft.public.sqlserver.server please do not multi-post.

http://www.aspfaq.com/etiquette.asp?id=5003
http://www.aspfaq.com/show.asp?id=2081
http://www.aspfaq.com/etiquette.asp?id=5006

John
"Sathyaish" <sathyaish@.gmail.com> wrote in message
news:1125988847.238366.259770@.g47g2000cwa.googlegr oups.com...
> What should one pass as a field value into a table in the insert
> statement if the value contained a percentage symbol (%) or the
> asterisk symbol (*), since both of these have special wildcard
> meanings. What if I want to pass the special meanings and pass them as
> literals? Is there any escape character that I must use? I am using
> ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
> SQL Server 2000.

Escape character for /SET option of dtexec

I have a problem setting some variables in a package using the /SET option of dtexec. Specifically when the value I want to set contains a semi-colon. I get an error like:

Argument ""\Package.Variables[User::Delim].Properties[Value];^;"" for option "set" is not valid.

I am guessing that I will have to escape the semi-colons somehow, but with what?

Regards,
Lars

You don't show the command but from the error it looks like

/set "\Package.Variables[User::Delim].Properties[Value];^;"

instead try

/set "\Package.Variables[User::Delim].Properties[Value]";"^;"

This puts the semicolon separator outside the quotes and may allow DTExec to process the argument.

HTH,

Matt

|||

Matt,

I am afraid that didn't work either. Any other ideas of how to pass a string containing semi-colons to dtexec using the command line?

This is the output:
H:\>dtexec /SET "\Package.Variables\[User::TheVariable].Properties[Value]";"^;"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Argument ""\Package.Variables\[User::TheVariable].Properties[Value];^;"" for opt
ion "set" is not valid.

H:\>

Regards,
Lars

|||

Sorry I should have been more explicit in my posting. You should try:

dtexec /SET "\"\Package.Variables\[User::TheVariable].Properties[Value]\";\"^;\""

The command interpreter strips quotes so the command I showed previously was what DTExec would work with but you have to escape the quotes so that they get to the DTExec parser. The above shows the actual command line with all the escaping.

HTH,

Matt

|||

The following turned out to be the proper syntax:

dtexec /SET \Package.Variables[User::TheVariable].Properties[Value];\"^;\"

Regards,
Lars

|||

The above did not work when the string contained spaces. The following has yet not failed though:

dtexec /SET \Package.Variables[User::TheVariable].Properties[Value];\""; space"\"

Regards,
Lars

Escape character for /SET option of dtexec

I have a problem setting some variables in a package using the /SET option of dtexec. Specifically when the value I want to set contains a semi-colon. I get an error like:

Argument ""\Package.Variables[User::Delim].Properties[Value];^;"" for option "set" is not valid.

I am guessing that I will have to escape the semi-colons somehow, but with what?

Regards,
Lars

You don't show the command but from the error it looks like

/set "\Package.Variables[User::Delim].Properties[Value];^;"

instead try

/set "\Package.Variables[User::Delim].Properties[Value]";"^;"

This puts the semicolon separator outside the quotes and may allow DTExec to process the argument.

HTH,

Matt

|||

Matt,

I am afraid that didn't work either. Any other ideas of how to pass a string containing semi-colons to dtexec using the command line?

This is the output:
H:\>dtexec /SET "\Package.Variables\[User::TheVariable].Properties[Value]";"^;"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Argument ""\Package.Variables\[User::TheVariable].Properties[Value];^;"" for opt
ion "set" is not valid.

H:\>

Regards,
Lars

|||

Sorry I should have been more explicit in my posting. You should try:

dtexec /SET "\"\Package.Variables\[User::TheVariable].Properties[Value]\";\"^;\""

The command interpreter strips quotes so the command I showed previously was what DTExec would work with but you have to escape the quotes so that they get to the DTExec parser. The above shows the actual command line with all the escaping.

HTH,

Matt

|||

The following turned out to be the proper syntax:

dtexec /SET \Package.Variables[User::TheVariable].Properties[Value];\"^;\"

Regards,
Lars

|||

The above did not work when the string contained spaces. The following has yet not failed though:

dtexec /SET \Package.Variables[User::TheVariable].Properties[Value];\""; space"\"

Regards,
Lars

sql

Errors on importing a text file

When a DTS fails on a Text Source input with an error like "DTS_Transformation encountered an invalid data value for 'Column1' destination"

Is there a way to get the line number of the textfile where the import failed? It is hard to determine where in my 40,000-line file it found the invalid value for my column.

Thanks,
AndrewSpecify the LOG file in Package Properties/Logging.|||WHere does it store the logs on the server?|||Wherever you would like it to be.|||I set the error log file to a text file on my esktop - it seems to repeat the same error message (without the line number) - am i missing something?

Error: The value was too large to fit in the output column "colName" (60).

Hi All,

I'm trying to transfer data from DB2 Database to SQL Server 2005.

Well, i used the OLE DB Source, the Data Conversion Component and the OLE DB Destination component.

I have five Data flows with this configuration above. But I am receiving an error message from one of them.

Please check below the error message:

"[Source Table TARTRATE [1]] Error: The value was too large to fit in the output column "ADJ_RATE_PCT" (60). "

"[Source Table TARTRATE [1]] Error: The "component "Source Table TARTRATE" (1)" failed because error code 0xC02090F8 occurred, and the error row disposition on "output column "ADJ_RATE_PCT" (60)" specifies failure on error. An error occurred on the specified object of the specified component."

Could you please help me with this issue?

Thanks in advance.

Thiago

The ADJ_RATE_PCT column in your data flow has a defined maximum width. You'll be able to find out what it is by double clicking the data-path leading from the source component.

What has happened is that a value has come in that is bigger than that defined maximum. So, you'll have to increase that maximum in the source component.

-Jamie

|||

Hi Jamie,

Thanks for your answer, but i still dont have a solution for this.

I tried to change the field width but i still have the same error message.

I think it is related with the field type. I received the same error message in another package and the field type is the same ( numeric , Precision 4 and Scale 2 ) as the first one.

I will continuing trying to figure out what is happening.

Thanks in advance.

Thiago

|||My guess is that, like Jamie said, a value is coming in that is bigger than the type. Based on the error message, and the type, it sounds like the value probably has a precision larger than 6. Have you tried increasing the precision (and possibly scale) of the output column in the advanced editor (inputs and outputs tab) of the source component?|||

Hi All,

Guys, thanks for your answer but I just don't know what to do now.

I changed the Precison (to 5) and Scale (to 3) in the Adavnced Editor following this sequence: Advanced Editor --> Input and Output Properties --> DataReader Output --> External Columns --> Select the column then change these two Data Types Properities: Precision and Scale. I tried to re-run the package and i still have the error message.

I saw that there is another "option" in the DataReader output menu called "Output Columns". I can't change any property in this menu. I guess these columns ( in the Ouptut Columns menu) are the return types from the source table ( im using a "select" command in a DB2 table ).

Another detail: in the "Output Columns" menu, the field that is returning error, has the precision 4 and Scale 2 ( remember: i cant change these values, they are automatically assigned by the component )). I think these values are comming from the source table. Am i right?

Thanks in advance for your help and atention.

Regards.
Thiago

|||

Hi Thiago,

Yes, in the DataReaderSrc you cannot change the properties of the output columns -- they are fixed at what the provider said the metadata was for the source column. Changing it in the External Columns will have no effect...

Your original post said you used the OLE DB Src -- if that is correct, you CAN change these properties there... Can you give that a try?

Thanks,
Mark

|||

Hi All,

Finally I find out what were happening in my application.

It was not related with the SQL2005 but with the DB2 ODBC Driver.

Follow below the instrunction how to fix this error in your DB2 ODBC Driver:

When you reconfigure your connection ( in the ODBC ), you have an Advanced button. So, you should click there and look for a Service Tab. In this service tab you have some "know workarounds".
There are two workarounds in this tab ( called PATCH 1 and PATCH2). In the second patch you have an option with this description: "Always use period for decimal separator in character output". You MUST select this option then type "15" in the value text box.

After that, just click ok and your DB2 ODBC driver will be configurated.

Thank you all for your answers.

See ya.

Thiago