Re: DTS bug (?): Data Driven Query Task removes milliseconds from DATETIME?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: John Peterson (j0hnp_at_comcast.net)
Date: 06/22/04 

Date: Tue, 22 Jun 2004 10:13:02 -0700

Ooo! What's a Global Variable Rowset? I'm not familiar with that?

Since I was able to get a viable workaround, I think I'll stick with that for now. But, I
appreciate that there appear to be other ways in the event that this didn't work. My goal
is to try and use the DDQ Task for SP invocations, since it's so simple/straightforward
and doesn't require any code writing at the DTS package level. :-)

Thanks again for your help!

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:uJEWqiHWEHA.384@TK2MSFTNGP10.phx.gbl...
> No but you could;
> 1. do this in ADO inside an Active Script task using a Global Variable
> Rowset
> 2. Do this in a loop inside an executeSQL task
>
> Are you sure there isn't a way that you can make this into a statement by
> itself?
>
>
> --
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
>
> "John Peterson" <j0hnp@comcast.net> wrote in message
> news:ODpXjbHWEHA.1152@TK2MSFTNGP09.phx.gbl...
> > I'm using the DDQ versus the DataPump because it was a convenient
> mechanism with which to
> > call a stored procedure with the contents of my source query. (That is, I
> wanted to
> > process a row-at-a-time, invoking a SP for every row with the contents of
> the source query
> > as the parameter values.)
> >
> > Is there a way to do that with the DataPump?
> >
> > Thanks again!
> >
> >
> >
> > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> > news:%23z0LxbFWEHA.716@TK2MSFTNGP11.phx.gbl...
> > > I haven't tried up but on initial thoughts yes that may work.
> > > Another idea is, the DDQ is slow. It processes rows row*row.
> > > We know there won't be a problem moving DateTime to DateTime using the
> > > DataPump task so I would try to rethink my package not to use the DDQ.
> > >
> > > If you want to explain your reasoning for using the DDQ then maybe we
> can
> > > work out a better alternative if one exists.
> > >
> > > --
> > >
> > > ----------------------------
> > >
> > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > www.SQLDTS.com - The site for all your DTS needs.
> > > I support PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> > >
> > >
> > > "John Peterson" <j0hnp@comcast.net> wrote in message
> > > news:eKyg6WFWEHA.2844@TK2MSFTNGP12.phx.gbl...
> > > > Thanks Allan!
> > > >
> > > > Do you think there's any way around this? I appreciate that some
> > > precision is being lost
> > > > by virtue of the fact that it's going through ActiveX script.
> However, I
> > > have a need to
> > > > keep the times *exactly* as they are.
> > > >
> > > > I was going to toy with the idea of making my Binding table be a
> string
> > > instead of a
> > > > datetime, but still have my stored procedure parameter be a datetime
> and
> > > rely on implicit
> > > > conversion. My hopes was that, as a string in the Binding table,
> perhaps
> > > I wouldn't lose
> > > > the millisecond component. Do you think this would work?
> > > >
> > > > Thanks again!
> > > >
> > > > John Peterson
> > > >
> > > >
> > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> > > > news:O0LhlLDWEHA.4048@TK2MSFTNGP12.phx.gbl...
> > > > > I do not think this is a bug rather by design i.e. it is working how
> it
> > > is
> > > > > being told.
> > > > >
> > > > > Here is my test repro
> > > > >
> > > > > CREATE TABLE DDQSource(col1 int Primary Key, colDateTime DATETIME)
> > > > > GO
> > > > > CREATE TABLE DDQDest(col1 int , colDateTime DATETIME)
> > > > > GO
> > > > > CREATE PROCEDURE SimplyInsertValues_i @i int, @dt DATETIME
> > > > > AS
> > > > > INSERT DDQDest(Col1, colDateTime) VALUES(@i,@dt)
> > > > > GO
> > > > > INSERT DDQSource(col1, colDateTime) VALUES(1,Getdate())
> > > > > GO
> > > > > SELECT * FROM DDQSource
> > > > > GO
> > > > > --Result
> > > > > --col1 colDateTime
> > > > > ----------- -----------------------
> > > > > --1 22/06/04 08:36:39.867
> > > > >
> > > > > --This is what DTS does to the data in the DDQ
> > > > >
> > > > > --exec sp_prepexec 1, 'Jun 22 2004 8:36:40:000AM'
> > > > >
> > > > > --
> > > > >
> > > > > What I think happens is this. To get from the Source to the
> Destination
> > > we
> > > > > have to go through an Active Script. This is where I believe the
> > > fractions
> > > > > are getting dropped because I believe they are being converted by
> the
> > > > > VBScript. VBScript uses the FormatDateTime() function and none of
> the
> > > > > options to the function allow milliseconds.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > ----------------------------
> > > > >
> > > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > > > > www.SQLDTS.com - The site for all your DTS needs.
> > > > > I support PASS - the definitive, global community
> > > > > for SQL Server professionals - http://www.sqlpass.org
> > > > >
> > > > >
> > > > > "John Peterson" <j0hnp@comcast.net> wrote in message
> > > > > news:OZ0V9qAWEHA.2444@TK2MSFTNGP11.phx.gbl...
> > > > > > (SQL Server 2000, SP3a)
> > > > > >
> > > > > > Hello all!
> > > > > >
> > > > > > I have a DTS package that uses the Data Driven Query Task (DDQT)
> to
> > > invoke
> > > > > a stored
> > > > > > procedure on the Insert action (that's the only action that's
> > > defined).
> > > > > My source table
> > > > > > has a DATETIME column that I'm using as the value to one of the
> > > parameters
> > > > > to the stored
> > > > > > procedure. During the invocation of the stored procedure, it
> appears
> > > as
> > > > > if the DATETIME
> > > > > > value is getting rounded to the nearest second and I'm losing the
> > > > > millisecond component,
> > > > > > making the DATETIME values between the source and destination
> > > > > non-equivalent.
> > > > > >
> > > > > > Is there any way to prevent this from happening?
> > > > > >
> > > > > > Thanks!
> > > > > >
> > > > > > John Peterson
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages