Derived Column

How do i change the data type of a column using the derived column feature?
I have two columns that need changing
(a) needs to be changed to an integer
(b) needs to be changed to a date
Both columns contain balnks and spaces!
Realise i could do this using a view but is it best practice to change the
data types before they get into the Database
--
Milk Bottle> Realise i could do this using a view but is it best practice to change the
> data types before they get into the Database
Good idea. One of the main purposes of SSIS is to scrub data before it is
loaded.
> I have two columns that need changing
> (a) needs to be changed to an integer
> (b) needs to be changed to a date
One method is with 4 Derived Column transformations, 2 for the integer and 2
for the datetime. The first transformation of each pair attempts to convert
the input to the appropriate datatype and the second transformation assigns
a default value if a conversion error occurs. To do this:
Create 2 new derived column transforms for the integer field and specify
expression as (DT_I4)[YourIntegerData] in the first transformation. Connect
that derived column output path to a new Union All and the derived column
error path (redirect row) to the second transformation that specifies your
desired default value (e.g. (DT_I4)0). Connect that derived column output
path to the same union all as the first transformation so that all records
are processed and contain valid integer values.
Repeat the above process for the datetime field with the union all output of
the integer transforms connected to a derived column with expression
(DT_DBTIMESTAMP)[YourDateTimeData]. The output of the second union all will
then contain only valid integer and datetime values.
Hope this helps.
Dan Guzman
SQL Server MVP
"MilkBottle" <MilkBottle@.discussions.microsoft.com> wrote in message
news:488C7D26-B9A6-43F8-BB9D-596EF19B2517@.microsoft.com...
> How do i change the data type of a column using the derived column
> feature?
> I have two columns that need changing
> (a) needs to be changed to an integer
> (b) needs to be changed to a date
> Both columns contain balnks and spaces!
> Realise i could do this using a view but is it best practice to change the
> data types before they get into the Database
>
>
> --
> Milk Bottle|||Dan
Thank you very very much...it worked a treat.
Thanks for taking the time to post a reply.
--
Milk Bottle
"Dan Guzman" wrote:
> > Realise i could do this using a view but is it best practice to change the
> > data types before they get into the Database
> Good idea. One of the main purposes of SSIS is to scrub data before it is
> loaded.
> > I have two columns that need changing
> > (a) needs to be changed to an integer
> > (b) needs to be changed to a date
> One method is with 4 Derived Column transformations, 2 for the integer and 2
> for the datetime. The first transformation of each pair attempts to convert
> the input to the appropriate datatype and the second transformation assigns
> a default value if a conversion error occurs. To do this:
> Create 2 new derived column transforms for the integer field and specify
> expression as (DT_I4)[YourIntegerData] in the first transformation. Connect
> that derived column output path to a new Union All and the derived column
> error path (redirect row) to the second transformation that specifies your
> desired default value (e.g. (DT_I4)0). Connect that derived column output
> path to the same union all as the first transformation so that all records
> are processed and contain valid integer values.
> Repeat the above process for the datetime field with the union all output of
> the integer transforms connected to a derived column with expression
> (DT_DBTIMESTAMP)[YourDateTimeData]. The output of the second union all will
> then contain only valid integer and datetime values.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MilkBottle" <MilkBottle@.discussions.microsoft.com> wrote in message
> news:488C7D26-B9A6-43F8-BB9D-596EF19B2517@.microsoft.com...
> >
> > How do i change the data type of a column using the derived column
> > feature?
> > I have two columns that need changing
> > (a) needs to be changed to an integer
> > (b) needs to be changed to a date
> > Both columns contain balnks and spaces!
> >
> > Realise i could do this using a view but is it best practice to change the
> > data types before they get into the Database
> >
> >
> >
> >
> >
> > --
> > Milk Bottle
>|||I'm glad I was able to help you out.
--
Dan Guzman
SQL Server MVP
"MilkBottle" <MilkBottle@.discussions.microsoft.com> wrote in message
news:3FAD3449-D418-46FB-83CC-1ED98F1A6F1C@.microsoft.com...
> Dan
> Thank you very very much...it worked a treat.
> Thanks for taking the time to post a reply.
> --
> Milk Bottle
>
> "Dan Guzman" wrote:
>> > Realise i could do this using a view but is it best practice to change
>> > the
>> > data types before they get into the Database
>> Good idea. One of the main purposes of SSIS is to scrub data before it
>> is
>> loaded.
>> > I have two columns that need changing
>> > (a) needs to be changed to an integer
>> > (b) needs to be changed to a date
>> One method is with 4 Derived Column transformations, 2 for the integer
>> and 2
>> for the datetime. The first transformation of each pair attempts to
>> convert
>> the input to the appropriate datatype and the second transformation
>> assigns
>> a default value if a conversion error occurs. To do this:
>> Create 2 new derived column transforms for the integer field and specify
>> expression as (DT_I4)[YourIntegerData] in the first transformation.
>> Connect
>> that derived column output path to a new Union All and the derived column
>> error path (redirect row) to the second transformation that specifies
>> your
>> desired default value (e.g. (DT_I4)0). Connect that derived column
>> output
>> path to the same union all as the first transformation so that all
>> records
>> are processed and contain valid integer values.
>> Repeat the above process for the datetime field with the union all output
>> of
>> the integer transforms connected to a derived column with expression
>> (DT_DBTIMESTAMP)[YourDateTimeData]. The output of the second union all
>> will
>> then contain only valid integer and datetime values.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "MilkBottle" <MilkBottle@.discussions.microsoft.com> wrote in message
>> news:488C7D26-B9A6-43F8-BB9D-596EF19B2517@.microsoft.com...
>> >
>> > How do i change the data type of a column using the derived column
>> > feature?
>> > I have two columns that need changing
>> > (a) needs to be changed to an integer
>> > (b) needs to be changed to a date
>> > Both columns contain balnks and spaces!
>> >
>> > Realise i could do this using a view but is it best practice to change
>> > the
>> > data types before they get into the Database
>> >
>> >
>> >
>> >
>> >
>> > --
>> > Milk Bottlesql

Deployment on load balanced servers.

Hi, I'm trying to solve the problem with deploying sql code on load-balanced application server.
Normally when we put new feature\fix live we take one server out of the loop, than we put new app code, we test it and then we bring it back to the loop. But we've started to have a problem when we had to change stored procedures at the same time to run the new app code, becouse either 'test' server won't work either production application won't work. I wonder is there any good solution for this problem.

Regards
Piotr.
P.S. Sorry for the forum topic but I didn't know where to put it.

what I understand is that your stored procedure signature is still the same but its implementation is changed in such a way that only the new application code can work with it. Your configuration is mulitple application servers talking to 1 SQL Server. In that case, let us say you have 10 application servers. I will take 5 (let us call them group-A) of those out of the loop, install new app code. Note, your app is still up and running though at 1/2 the capacity. you can test your group-A with another SQL Server (say the test SQL Server that has new stored procs). Once it is done, you can take other group of app servers offline (this will cause short term unavailability of your application), change the stored proc on the production SQL Server, and then bring group-A online. Now you can do the same with other set of application servers.

|||That was very helpful, but I'm more after stored procedure versioning solution. Sometimes we need old and new code running (part of requests are hitting old and other new code). Thanks in advance.|||http://weblogs.asp.net/fmarguerie/archive/2003/02/24/2880.aspx fyi.sql