(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

Sounds as though something is gettng converted to a SmallDateTime.

I'll have a look

--

----------------------------

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 (AT) comcast (DOT) net> wrote

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 (AT) comcast (DOT) net> wrote

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 (AT) no-spam (DOT) sqldts.com> wrote

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 (AT) comcast (DOT) net> wrote

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 (AT) no-spam (DOT) sqldts.com> wrote

Okay -- I think I finally got this to work around this issue. I basically had to have the
source query *and* the binding table convert the DATETIME to a string. Further, I *had*
to use a VARCHAR(23) specification for the string, because when I used a VARCHAR(255)
originally, sometimes the DATETIME would inexplicably convert to something like
'2003-07-25 17:11:07.72000000000' (note the extra zeroes at the end). Then, when this was
inexplicably converted for the DATETIME parameter in my stored procedure, it would
generate an error. Weird. But, by "truncating" it with the VARCHAR(23), that seems to
have fixed the problem.


"John Peterson" <j0hnp (AT) comcast (DOT) net> wrote

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 (AT) comcast (DOT) net> wrote

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 (AT) no-spam (DOT) sqldts.com> wrote

My motto is "Whatever works and is acceptable to the business" It would
seem you have that.

Have a look at this though for the future

How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)

--
--

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 (AT) comcast (DOT) net> wrote