Data Type that accept . for MSSQL

Hi,

I wish to enter some string data with period(.) in a column with char type. It doesn't accept period(.) What data type should I choose for this field ?

I'm using MSSQL Server 2000.

Please advice.varchar, nvarchar..?|||Thanks.

Data type issues

One:

I can't map in the Lookup Transformer editor from my input (a string) that has been modded using the following in the Derived Column Transformer as follows:

Formatted_Date

((DT_DATE)(SUBSTRING([IN-DT-CYMD-TRANS],5,2) + "-" + SUBSTRING([IN-DT-CYMD-TRANS],7,2) + "-" + SUBSTRING([IN-DT-CYMD-TRANS],1,4)))

database_date (DT_DBDATE)

The destination field has the data type "datetime", according to Management Studio.

Two:

I have almost the exact same problem with another mapping, trying to cast a string to an int (INT is not one of the choices in my list...):

Batch_Code_As_Int

((DT_NUMERIC,1,1)([IN-BAT-NBR]))

numeric [DT_NUMERIC]

Any thoughts?

Thanks!

Jim Work

Jim Work wrote:

One:

I can't map in the Lookup Transformer editor from my input (a string) that has been modded using the following in the Derived Column Transformer as follows:

Make your derived column type a DB_TIMESTAMP and see if that works.

Jim Work wrote:

Two:

I have almost the exact same problem with another mapping, trying to cast a string to an int (INT is not one of the choices in my list...):

Batch_Code_As_Int

((DT_NUMERIC,1,1)([IN-BAT-NBR]))

numeric [DT_NUMERIC]

Try DT_I4 (a 32-bit integer)

Data type error

I have created a Foreach Loop container which generates a string variable which is a Select statement that in turn used in my OLE DB source in my Data Flow task. I have a 3 variables that I am using to create the Select statement. Two of them work fine but the 3rd gives me an error "Cannot convert varchar to numeric" after about the 5th or 6th loop which is odd as the variable is the same for each pass.

The SQL Task linked to the Foreach Loop is a query as follows

SELECT DISTINCT

CAST(FYr as varchar(4)) as FYr,

CAST(Acct1 as varchar(13)) as Acct1,

CAST(Acct2 as varchar(13)) as Acct2

FROM GL, AcctTbl

The resulting dataset looks like this (there is only one record in AcctTbl)

FYr Acct1 Acct2

2000 400.00 307.00

2001 400.00 307.00

2002 400.00 307.00

etc, which is exactly what I want.

I've created package scope string variables for sFYr, sAcct1 and sAcct2 as well as another variable qrySQL

The value for qrySQL string variable is something like this

"Select " + @.User :: sFYr + " as FiscalYear, Account, " + @.User :: sAcct2 + "Amount FROM tblGL WHERE Account < " + @.User :: sAcct1

This is then used as my datasourse in my data flow task

When I run the package it goes through a half a dozen iterations ( there are about a dozen total rows to iterate) and successfully writes the results to my data destination table but then fails with the "cannot convert varchar to numeric" message.

It seems to be with the sAcct1 variable because if I use the same string for my qrySQL except I replace the sAcct1 variable with string (as shown below) the package completes successfully

"Select " + @.User :: sFYr + " as FiscalYear, Account, " + @.User :: sAcct2 + "Amount FROM tblGL WHERE Account < '400.00'

Does anyone have any ideas? Can I not use the < to compare string? The Account field that I'm comparing is a varchar(13) field. I've even tried casting the Account and sAcct1 variable as numeric in the qrySQL string and I'm getting the same failure after several iterations.

Any insight would really be appreciated. I've lost a bit of hair over this one.

Thanks in advance

You should be including single quotes around sAcct1 in the WHERE clause, such as "...WHERE Account < '" + @.User :: sAcct1 + "'"

I believe SQL Server is converting the value of your Account column to a numeric to match the datatype you are sending it. The error is occurring because you have data in that column that fails the conversion.
|||

wpwebster wrote:

I have created a Foreach Loop container which generates a string variable which is a Select statement that in turn used in my OLE DB source in my Data Flow task. I have a 3 variables that I am using to create the Select statement. Two of them work fine but the 3rd gives me an error "Cannot convert varchar to numeric" after about the 5th or 6th loop which is odd as the variable is the same for each pass.

The SQL Task linked to the Foreach Loop is a query as follows

SELECT DISTINCT

CAST(FYr as varchar(4)) as FYr,

CAST(Acct1 as varchar(13)) as Acct1,

CAST(Acct2 as varchar(13)) as Acct2

FROM GL, AcctTbl

The resulting dataset looks like this (there is only one record in AcctTbl)

FYr Acct1 Acct2

2000 400.00 307.00

2001 400.00 307.00

2002 400.00 307.00

etc, which is exactly what I want.

I've created package scope string variables for sFYr, sAcct1 and sAcct2 as well as another variable qrySQL

The value for qrySQL string variable is something like this

"Select " + @.User :: sFYr + " as FiscalYear, Account, " + @.User :: sAcct2 + "Amount FROM tblGL WHERE Account < " + @.User :: sAcct1

This is then used as my datasourse in my data flow task

When I run the package it goes through a half a dozen iterations ( there are about a dozen total rows to iterate) and successfully writes the results to my data destination table but then fails with the "cannot convert varchar to numeric" message.

It seems to be with the sAcct1 variable because if I use the same string for my qrySQL except I replace the sAcct1 variable with string (as shown below) the package completes successfully

"Select " + @.User :: sFYr + " as FiscalYear, Account, " + @.User :: sAcct2 + "Amount FROM tblGL WHERE Account < '400.00'

Does anyone have any ideas? Can I not use the < to compare string? The Account field that I'm comparing is a varchar(13) field. I've even tried casting the Account and sAcct1 variable as numeric in the qrySQL string and I'm getting the same failure after several iterations.

Any insight would really be appreciated. I've lost a bit of hair over this one.

Thanks in advance

Is it possible that Account column in tblGL table is numeric? if so, you make sure that you cast accordingly the values of acct1 from Acttbl table. Why are you casting it in the query as varchar and putting it in a string variable? would not be better to to use a data type that is consistent with tblGL.Account?

|||

Thanks for the input. It helped me get to the bottom of it. The Account field is a varchar(13) field although the accounts are in a format of something like 400.00 There was however several records I found where the Accocunt was NA, when I changed those through a derived column data flow control to a "0" it worked fine.

I still find it a bit puzzling that the WHERE clause worked when it was WHERE Account < '400.00' but wouldnt' work when it was WHERE Account < @.UserVariable

Making sure all Accounts looked like numbers did the trick though.

Thanks again for the input.

Regards

Bill

Data Type Conversions

I am trying to convert int to date. first I have converted the int value to string(dt_str), using 'Data Conversion' Transformation, then again I have used another 'Data Conversion' Transformation to convert it into date. but its giving the following error

[Data Conversion 1 [3643]] Error: Data conversion failed while converting column "THEDATE_INTO_STR" (2799) to column "CALENDAR_YEAR1" (3657). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

What are the simple ways to convert from one data type to another? If somebody could give some example of the above...............Try using the Derived Column component to do the casting. Its more flexible than Data Conversion component.

-Jamie|||Right, you might need the flexibility of expressions in derived column.
What is the meaning of the integer as a date?|||int as a date meansI have one int type in source, which has data like20051211....
but my target column is of datetime.
Anyways, it done now...

Thanks

Data Type Conversions

I am trying to convert int to date. first I have converted the int value to string(dt_str), using 'Data Conversion' Transformation, then again I have used another 'Data Conversion' Transformation to convert it into date. but its giving the following error

[Data Conversion 1 [3643]] Error: Data conversion failed while converting column "THEDATE_INTO_STR" (2799) to column "CALENDAR_YEAR1" (3657). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

What are the simple ways to convert from one data type to another? If somebody could give some example of the above...............Try using the Derived Column component to do the casting. Its more flexible than Data Conversion component.

-Jamie|||Right, you might need the flexibility of expressions in derived column.
What is the meaning of the integer as a date?|||int as a date meansI have one int type in source, which has data like20051211....
but my target column is of datetime.
Anyways, it done now...

Thanks

Data type Conversion during update ...

I am trying to update a row in a table. To my SP, I send an XML string.
One of the columns I am trying to update is of type "Bit" and the value I am
getting from my app is either "true" or "false". This is the general
structure of sp:
UPDATE x
SET x.isEmployee = xmlEmp.isEmployee
WHERE ...
I tried the following to convert my xmlEmp.isEmployee to "BIT" ...
SET x.isEmployee = CASE WHEN xmlEmp.isEmployee='false' THEN 0 ELSE 1 END
SET x.isEmployee = CASE WHEN xmlEmp.isEmployee='false' THEN CAST(0 AS BIT)
ELSE CAST(1 AS BIT) END
both of them don't convert the strings 'false' and 'true' to 0 and 1 ... not
sure what I am missing. Appreciate any tips. TIA.
"exBK" <exBK@.discussions.microsoft.com> wrote in message
news:09146AA0-F234-45F6-9B71-D4F90FDED3B2@.microsoft.com...
>I am trying to update a row in a table. To my SP, I send an XML string.
> One of the columns I am trying to update is of type "Bit" and the value I
> am
> getting from my app is either "true" or "false".
That should work...
declare @.x varchar(10), @.y bit
set @.x = 'true'
set @.y = case when @.x = 'false' then 0 else 1 end
select @.y
The above results in 1. Are you getting an error?
Bryant

DATA TYPE (STRING)

HI EVERY ONE,
I want to use long datatype as a string, which has variable length and may
exceed (8000) character. in a stored procedure or function as a variable.
when i tried to use the datatype ( Text, or nText ) i was not able to create
a stored procedure which manipulate where i have the following error message:
"The text, ntext, and image data types are invalid for local variables."
Any suggestions please.
Thanks
Rami,
Hi
Unfortunately for a local variable you are limited. You may want to use
multiple variables or a table as an alternative.
John
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:76BAF46C-4699-45A0-AF1C-AABBA66C8841@.microsoft.com...
> HI EVERY ONE,
> I want to use long datatype as a string, which has variable length and may
> exceed (8000) character. in a stored procedure or function as a variable.
> when i tried to use the datatype ( Text, or nText ) i was not able to
> create
> a stored procedure which manipulate where i have the following error
> message:
> "The text, ntext, and image data types are invalid for local variables."
> Any suggestions please.
> Thanks
> Rami,
|||I Tried to use a table fields variable but u can not manipulate the field the
same way u manipulate usual string,
for example:
Create Table T1
(
value TEXT
)
the following statment will generate an error message:
Update T1 set value = value + 'any additional string'
could u provide me with better techniques.
"John Bell" wrote:

> Hi
> Unfortunately for a local variable you are limited. You may want to use
> multiple variables or a table as an alternative.
> John
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:76BAF46C-4699-45A0-AF1C-AABBA66C8841@.microsoft.com...
>
>
|||Hi
Unfortunately to read/write/update text you require specific functions For
UPDATETEXT see
http://msdn.microsoft.com/library/de...reate_4hk5.asp
Large string handing has improved in SQL Server 2005.
John
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:741BB23B-F83E-41B9-A1DA-A5C78FEE210C@.microsoft.com...[vbcol=seagreen]
>I Tried to use a table fields variable but u can not manipulate the field
>the
> same way u manipulate usual string,
> for example:
> Create Table T1
> (
> value TEXT
> )
> the following statment will generate an error message:
> Update T1 set value = value + 'any additional string'
> could u provide me with better techniques.
> "John Bell" wrote:
|||Thanks John,
Rami
"John Bell" wrote:

> Hi
> Unfortunately to read/write/update text you require specific functions For
> UPDATETEXT see
> http://msdn.microsoft.com/library/de...reate_4hk5.asp
> Large string handing has improved in SQL Server 2005.
> John
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:741BB23B-F83E-41B9-A1DA-A5C78FEE210C@.microsoft.com...
>
>

DATA TYPE (STRING)

HI EVERY ONE,
I want to use long datatype as a string, which has variable length and may
exceed (8000) character. in a stored procedure or function as a variable.
when i tried to use the datatype ( Text, or nText ) i was not able to create
a stored procedure which manipulate where i have the following error message:
"The text, ntext, and image data types are invalid for local variables."
Any suggestions please.
Thanks
Rami,Hi
Unfortunately for a local variable you are limited. You may want to use
multiple variables or a table as an alternative.
John
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:76BAF46C-4699-45A0-AF1C-AABBA66C8841@.microsoft.com...
> HI EVERY ONE,
> I want to use long datatype as a string, which has variable length and may
> exceed (8000) character. in a stored procedure or function as a variable.
> when i tried to use the datatype ( Text, or nText ) i was not able to
> create
> a stored procedure which manipulate where i have the following error
> message:
> "The text, ntext, and image data types are invalid for local variables."
> Any suggestions please.
> Thanks
> Rami,|||I Tried to use a table fields variable but u can not manipulate the field the
same way u manipulate usual string,
for example:
Create Table T1
(
value TEXT
)
the following statment will generate an error message:
Update T1 set value = value + 'any additional string'
could u provide me with better techniques.
"John Bell" wrote:
> Hi
> Unfortunately for a local variable you are limited. You may want to use
> multiple variables or a table as an alternative.
> John
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:76BAF46C-4699-45A0-AF1C-AABBA66C8841@.microsoft.com...
> > HI EVERY ONE,
> >
> > I want to use long datatype as a string, which has variable length and may
> > exceed (8000) character. in a stored procedure or function as a variable.
> >
> > when i tried to use the datatype ( Text, or nText ) i was not able to
> > create
> > a stored procedure which manipulate where i have the following error
> > message:
> >
> > "The text, ntext, and image data types are invalid for local variables."
> >
> > Any suggestions please.
> >
> > Thanks
> > Rami,
>
>|||Hi
Unfortunately to read/write/update text you require specific functions For
UPDATETEXT see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_4hk5.asp
Large string handing has improved in SQL Server 2005.
John
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:741BB23B-F83E-41B9-A1DA-A5C78FEE210C@.microsoft.com...
>I Tried to use a table fields variable but u can not manipulate the field
>the
> same way u manipulate usual string,
> for example:
> Create Table T1
> (
> value TEXT
> )
> the following statment will generate an error message:
> Update T1 set value = value + 'any additional string'
> could u provide me with better techniques.
> "John Bell" wrote:
>> Hi
>> Unfortunately for a local variable you are limited. You may want to use
>> multiple variables or a table as an alternative.
>> John
>> "Rami" <Rami@.discussions.microsoft.com> wrote in message
>> news:76BAF46C-4699-45A0-AF1C-AABBA66C8841@.microsoft.com...
>> > HI EVERY ONE,
>> >
>> > I want to use long datatype as a string, which has variable length and
>> > may
>> > exceed (8000) character. in a stored procedure or function as a
>> > variable.
>> >
>> > when i tried to use the datatype ( Text, or nText ) i was not able to
>> > create
>> > a stored procedure which manipulate where i have the following error
>> > message:
>> >
>> > "The text, ntext, and image data types are invalid for local
>> > variables."
>> >
>> > Any suggestions please.
>> >
>> > Thanks
>> > Rami,
>>|||Thanks John,
Rami
"John Bell" wrote:
> Hi
> Unfortunately to read/write/update text you require specific functions For
> UPDATETEXT see
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_4hk5.asp
> Large string handing has improved in SQL Server 2005.
> John
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:741BB23B-F83E-41B9-A1DA-A5C78FEE210C@.microsoft.com...
> >I Tried to use a table fields variable but u can not manipulate the field
> >the
> > same way u manipulate usual string,
> >
> > for example:
> >
> > Create Table T1
> > (
> > value TEXT
> > )
> >
> > the following statment will generate an error message:
> >
> > Update T1 set value = value + 'any additional string'
> >
> > could u provide me with better techniques.
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> Unfortunately for a local variable you are limited. You may want to use
> >> multiple variables or a table as an alternative.
> >>
> >> John
> >>
> >> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> >> news:76BAF46C-4699-45A0-AF1C-AABBA66C8841@.microsoft.com...
> >> > HI EVERY ONE,
> >> >
> >> > I want to use long datatype as a string, which has variable length and
> >> > may
> >> > exceed (8000) character. in a stored procedure or function as a
> >> > variable.
> >> >
> >> > when i tried to use the datatype ( Text, or nText ) i was not able to
> >> > create
> >> > a stored procedure which manipulate where i have the following error
> >> > message:
> >> >
> >> > "The text, ntext, and image data types are invalid for local
> >> > variables."
> >> >
> >> > Any suggestions please.
> >> >
> >> > Thanks
> >> > Rami,
> >>
> >>
> >>
>
>

Data Type > Save various object types

Hello,

I am working on a .NET project and I would like to save some LOCALIZED content in an SQL 2005 database.

This content might be a string, a number or even a class with its properties values defined so I can retrieve it later.

I will not need search or any complex operation just the following:
1. Add content to the database
2. Remove content from the database by contentId
3. Get content from the database by contentId

Anyway, is this possible?

What SQL 2005 datatype should I use to accomplish this?

Thanks,
Miguel

I suppose the only one which will work for you isvarbinary(max) I suppose

Thanks