Insert date value into SQL Server datetime columm

[chulett]

Which means that even though you're using the DRS stage, you are probably still talking to SQL Server through an ODBC interface.

That depends entirely on the 'Database type' setting, it supports both ODBC and MSSQL Server. The OP hasn't specified which they are using.

[chulett]

Could the Sybase stages be configured to access SQL Server?

No.

[chulett]

After a bit of hacking, I've deduced that TO_DATE must be an internal DataStage function that converts the string to the proper format for the target database. TO_DATE isn't a SQL Server function, although I've used it before on Oracle projects.

No, TO_DATE() is not anything internal to DataStage, if so you'd only be able to use it in a derivation and not directly in your sql. That is, unless someone there created that function in the target database, then you actually could. I found plenty of instances on the 'net where people wanted to recreate Oracle's TO_DATE() function in SQL Server, so it seemed fairly common.

In my mind, the fact that it doesn't generate a TO_DATE() for a Date column isn't an issue since it's not valid syntax, the fact that it *does* when you declare a Timestamp seems like the bug to me. Unfortunately, I've had very little exposure to SQL Server, my DRS experience with it has been mostly selecting from it (where both a Date and a Timestamp get wrapped in a TO_CHAR) and the only timestamp field we updated was treated as a varchar that we sent a full ISO timestamp to. Unfortunately, that source no longer exists so I can't verify what the actual data type was in the table itself.

This makes me wonder, can you update yours if you declare it as a Varchar and pass in an ISO date or timestamp without anything special in the sql?

[Mike]

Craig,

Any idea what the difference between selecting ODBC vs. MSSQL Server on a UNIX platform might be? I haven't experimented with it myself. I believe both would require an ODBC DSN in the connection property. Would MSQL Server possibly use the wire protocol driver while ODBC might use the legacy ODBC driver? Though I guess that might entirely depend on the ODBC DSN. I guess I don't see how the 'Database type" could matter for SQL Server on UNIX.

Mike

[chulett]

As Ray noted, DataStage ships with a Data Direct wire driver for SQL Server which is what I used when I needed to interface with that source. Sure, that's still ODBC and you still need to do all the normal configuring like you would for any other ODBC source, but it's a 'source specific' driver and not the same as the generic ODBC driver.

[Kryt0n]

Could the Sybase stages be configured to access SQL Server?

No. ...

Either not totally true or the documentation is not up to date...

BCPLoad Stages
This topic describes BCPLoad stages, which are used to bulk load data into a single table in a Microsoft SQL Server 2000 or Sybase (System 11.5 or 12.5) database.

How useful it is for current requirements I have no idea though, never having used it

[ray.wurlod]

Once upon a time, in an IT environment not so far away, SQL Server was based on Sybase, and the bulk copy program (BCP) was suitable for either.

Whether or not that is still true I do not know, but I doubt it.

[Kryt0n]

Yesterday was the first time I got to use Sybase and my first impression was "Access" so wasn't overly surprised when I found a driver designed for both Sybase and MS SQL Server (hence my original question)

Never had the (mis?) fortune of using SQL Server but always gives me the feeling it will be just like Access

[chulett]

I've used the Sybase stage (singular) and, since it requires the Sybase OC to work, I made the assumption it couldn't somehow be finageled into also working for SQL Server. Could be wrong. [shrug]

I didn't pick up on your use of the plural, so didn't take any other stages into consideration and will be the first to admit I have no experience with the BCPLoad stage.

[Mike]

Scott,

Sorry to veer off wondering about the internal workings of DRS MSSQL Server.

Coming back to your issue...

I think the suggestions made by Ray and Craig both sound like plausible solutions.

Mike

[jdmiceli]

In my own project, I have SQL Server 2000 source and targets with DS on AIX Unix. I have mangled the Sybase Stage every which way I could think of to get it to work with SS2K since they are 2nd cousins about three times removed now. I never could get anything to work to any usable degree.

As to dates being inserted into SS2K using RDS (which I do), if you are using 'User Defined Query' in your stage, then you cannot use the TO_DATE function (at least not in my experience). What you do have to know is what collation the target database is using (if anything other than default) to see if it would affect what acceptable date formats can come in. Once you know that, then you can get creative to transform the incoming data to a format the target db can accept. If I remember right (I'm trying to find the job so I can back it up), I stored the value in a VarChar field in DS and then used CONVERT against that field during the insert/update. I'll see if I can find the code to make sure I am not lying to you.

[sbass1]

Which means that even though you're using the DRS stage, you are probably still talking to SQL Server through an ODBC interface.

That depends entirely on the 'Database type' setting, it supports both ODBC and MSSQL Server. The OP hasn't specified which they are using.

DRS Stage with MSSQL Server database type setting.

[sbass1]

After a bit of hacking, I've deduced that TO_DATE must be an internal DataStage function that converts the string to the proper format for the target database. TO_DATE isn't a SQL Server function, although I've used it before on Oracle projects.

No, TO_DATE() is not anything internal to DataStage, if so you'd only be able to use it in a derivation and not directly in your sql.

I made my deduction that, at least in this instance, TO_DATE was internal to DS based on the fact that:

INSERT INTO dbo.ScottDateTesting (EffectiveDate) VALUES (TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'))

works when submitted from within DS, whereas

INSERT INTO dbo.ScottDateTesting (EffectiveDate) VALUES (TO_DATE('2009-03-03 18:00:00', 'YYYY-MM-DD HH24:MI:SS'))

results in this error when submitted from the SQL Server client:

Msg 195, Level 15, State 10, Line 1
'TO_DATE' is not a recognized built-in function name.

I'm just guessing here, but I thought TO_DATE might be a function within DS to overcome the different epoch dates between databases, i.e. 2009-03-03 = 12345 in SQL Server, while 2009-03-03 = 45678 in Oracle (made up values of course).

That is, unless someone there created that function in the target database, then you actually could. I found plenty of instances on the 'net where people wanted to recreate Oracle's TO_DATE() function in SQL Server, so it seemed fairly common.

But doesn't that confirm that TO_DATE must be internal to DS if it's not available in SQL Server?

This makes me wonder, can you update yours if you declare it as a Varchar and pass in an ISO date or timestamp without anything special in the sql?

Not sure what you mean here, but I haven't been able to get it to update if the metadata datatype is date or varchar. The error messages are something like:

ScottTest4..Transformer_1: At row 1, link "DSLink2", while processing column "Date"
Value treated as NULL
Attempt to convert String value "2009-09-25" to Date type unsuccessful

So the error message is definitely coming from DS, not SQL Server.

[chulett]

But doesn't that confirm that TO_DATE must be internal to DS if it's not available in SQL Server?

Hmmm... I get what you are saying and I really wish I could explain the behaviour you are seeing but that still doesn't make it internal to DS. All DataStage should be doing is passing that sql to the database engine for parsing/processing just like any other tool would. Seems like the only good answer to that conundrum would have to come from your official support provider, I'd be very curious what is going on behind the scenes in the plugin.

I haven't been able to get it to update if the metadata datatype is date or varchar. The error messages are something like:

ScottTest4..Transformer_1: At row 1, link "DSLink2", while processing column "Date"
Value treated as NULL
Attempt to convert String value "2009-09-25" to Date type unsuccessful

So the error message is definitely coming from DS, not SQL Server.

Yes, that's definitely a DataStage message and it comes from it trying to enforce the metadata before it even sends it off to the database. However, that should only happen with a 'Date' datatype... can you confirm please that you truly get that same message for a Varchar?

[sbass1]

(Scroll to the end for a summary...)

Hi Craig,

OK, I've got a DS server job as follows:

SeqFile --> Extract (link) --> xfm --> Load (link) --> DRS Stage (MS SQL Server)

The SeqFile contains two rows:

2009-09-30
2009-09-30 12:34:56

The column is defined varchar (doesn't really matter for a seq file anyway)

The xfm is a straight mapping to the SQL Server table.

The SQL Server table has a single column "Date" of type datetime.

The DRS update action is "Clear table then insert rows".

Scenarios:

1) Load link has metadata type timestamp:

Generated SQL:
INSERT INTO dbo.ScottDateTesting (Date) VALUES (TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'))

Results:
2 rows loaded
1st row fails, 2nd row succeeds

Log (Warnings only):
ScottTest4..Transformer_1: At row 1, link "Load", while processing column "Date"
Value treated as NULL
Attempt to convert String value "2009-09-30" to Timestamp type unsuccessful

2) Load link has metadata type date:

Generated SQL:
INSERT INTO dbo.ScottDateTesting (Date) VALUES (?)

Results:
2 rows loaded
Both rows fail

Log (Warnings only):
ScottTest4..Transformer_1: At row 1, link "Load", while processing column "Date"
Value treated as NULL
Attempt to convert String value "2009-09-30" to Date type unsuccessful
ScottTest4..Transformer_1: At row 2, link "Load", while processing column "Date"
Value treated as NULL
Attempt to convert String value "2009-09-30 12:34:56" to Date type unsuccessful

3) Load link has metadata type varchar:

Generated SQL:
INSERT INTO dbo.ScottDateTesting (Date) VALUES (?)

Results:
0 rows loaded
Obviously both rows fail

(Funnily enough, "View data" shows two blank rows. If the table was cleared first, then zero rows loaded, I would think it would be empty?)

Log (Warnings only):
ScottTest4..Transformer_1: [DataDirect][ODBC SQL Server Driver][SQL Server]Arithmetic overflow occurred.
ScottTest4..Transformer_1: Date = 2009-09-30
ScottTest4..Transformer_1: At row 1, link "Load"
Row rejected.
ScottTest4..Transformer_1: [DataDirect][ODBC SQL Server Driver][SQL Server]Arithmetic overflow occurred.
ScottTest4..Transformer_1: Date = 2009-09-30 12:34:56
ScottTest4..Transformer_1: At row 2, link "Load"
Row rejected.


***** Now, change seq file data from YYYY-MM-DD to YYYY-DD-MM:

2009-30-09
2009-30-09 12:34:56

The auto-generated TO_DATE function has the string 'YYYY-MM-DD...', but I've confirmed that this does not work in the SQL client. See original post, but to recap:

DELETE FROM dbo.ScottDateTesting
INSERT INTO dbo.ScottDateTesting (Num,Date) VALUES (1,'2009-30-09') /* works, even without the hhmmss */
INSERT INTO dbo.ScottDateTesting (Num,Date) VALUES (2,'2009-09-30') /* fails */
SELECT * FROM dbo.ScottDateTesting

SQL Server client log:
Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

The Num=1 row was inserted, but not Num=2 row...

*****

4) Load link has metadata type timestamp:

Generated SQL:
INSERT INTO dbo.ScottDateTesting (Date) VALUES (TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'))

Results:
1 row loaded
However, "View data" returns Empty data source, so go figure on the performance statistics returned to the job!

Log (Warnings only):
ScottTest4..Transformer_1: At row 1, link "Load", while processing column "Date"
Value treated as NULL
Attempt to convert String value "2009-30-09" to Timestamp type unsuccessful
ScottTest4..Transformer_1: [DataDirect][ODBC SQL Server Driver]Invalid date format
ScottTest4..Transformer_1: Date = 2009-30-9 12:34:56.0
ScottTest4..Transformer_1: At row 2, link "Load"
Row rejected.

5) Load link has metadata type date:

Generated SQL:
INSERT INTO dbo.ScottDateTesting (Date) VALUES (?)

Results:
2 rows loaded
Both rows fail

Log:
ScottTest4..Transformer_1: At row 1, link "Load", while processing column "Date"
Value treated as NULL
Attempt to convert String value "2009-30-09" to Date type unsuccessful
ScottTest4..Transformer_1: At row 2, link "Load", while processing column "Date"
Value treated as NULL
Attempt to convert String value "2009-30-09 12:34:56" to Date type unsuccessful

6) Load link has metadata type varchar:

Generated SQL:
INSERT INTO dbo.ScottDateTesting (Date) VALUES (?)

Results:
2 rows loaded
Both rows succeeded!!!

Yay!

So, after two hours of hacking around , what works in writing date values via the DRS stage to SQL Server datetime column:

1) Make your metadata timestamp, use generated SQL code TO_DATE(?,'YYYY-MM-DD HH24:MM:SS'), and ensure your data is formatted in YYYY-MM-DD format (must include HH:MM:SS - could always just whack on 00:00:00)

2) Make your metadata varchar, generated SQL code will not include the TO_DATE function, and ensure your data is formatted in YYYY-DD-MM format (actually any format which works in native SQL Server will do). You DON'T have to include the time portion - SQL Server will 00:00:00 if it's not supplied - convenient.

3) Finally, I also tried YYYY-MON-DD format, which also works natively in SQL Server (again, see original post):

2009-SEP-30
2009-SEP-30 12:34:56

This also worked (also worked without the dashes)

Sorry for the long post, in a long thread. Perhaps someone will find this useful in a future search, although the chance that they'll scroll to the end of this thread is minimal

Regards,
Scott