June 15, 2011 at 12:03 pm
Now that I've said all that, could you post your DDL for both tables (granted we don't have Sybase, but a SQL version would help), some sample data for both tables, and examples of what the data looks like both before and after it is updated?
EDIT: I just got a test to go through that loaded a table without a Destination component. But I still stand by what I said about the command not being a real destination. So, perhaps that isn't the problem. But the DDL and sample data would help us greatly in determining potential solutions other than what I suggested above.
June 15, 2011 at 2:55 pm
Sample Data (Header's are for ease of viewing here)
id_rsltn,code_rsltn,code_rsltn_cat,dt_rslvd,ind_rslvd
2011-06-09 09:36:01.923000000,SPAY,DISP,2011-09-21 00:00:00,A
Source Table (SQL)
CREATE TABLE [dbo].[smt_upd_rsltn](
[id_rsltn] [datetime] NOT NULL CONSTRAINT [DF__smt_upd_r__id_rs__5CED7072] DEFAULT (getdate()),
[code_rsltn] [varchar](4) NOT NULL CONSTRAINT [DF__smt_upd_r__code___5B052800] DEFAULT (' '),
[code_rsltn_cat] [varchar](4) NOT NULL CONSTRAINT [DF__smt_upd_r__code___5BF94C39] DEFAULT (' '),
[dt_rslvd] [datetime] NULL,
[ind_rslvd] [varchar](1) NOT NULL CONSTRAINT [DF__smt_upd_r__ind_r__5DE194AB] DEFAULT (' ')
) ON [PRIMARY]
Destination Table (Sybase)
CREATE TABLE dbo.rsltn
(
amt_captzn numeric(13,2) NOT NULL,
amt_dflt_int numeric(13,2) NOT NULL,
amt_escrw_adv numeric(13,2) NOT NULL,
amt_escrw_shrt numeric(13,2) NOT NULL,
amt_fees_late numeric(13,2) NOT NULL,
amt_fees_legl numeric(13,2) NOT NULL,
amt_fees_othr numeric(13,2) NOT NULL,
amt_int numeric(13,2) NOT NULL,
amt_new_money numeric(13,2) NOT NULL,
amt_prin numeric(13,2) NOT NULL,
amt_reinstmt numeric(13,2) NOT NULL,
amt_swr numeric(13,2) NOT NULL,
amt_tax numeric(13,2) NOT NULL,
amt_wtr numeric(13,2) NOT NULL,
cnt_svcg_per int NOT NULL,
code_plan char(3) NOT NULL,
code_rsltn char(4) NOT NULL,
code_rsltn_cat char(4) NOT NULL,
code_rsltn_end char(4) NOT NULL,
code_rsn_asgnm char(4) NOT NULL,
code_rsn_dflt char(4) NOT NULL,
code_rsn_svcg char(4) NOT NULL,
desc_mrtg_paid_by varchar(30) NOT NULL,
dt_eff datetime NULL,
dt_eff_arrg datetime NULL,
dt_mrtg_perdc_beg datetime NULL,
dt_mrtg_perdc_end datetime NULL,
dt_othr_plan_due datetime NULL,
dt_plan_rcvd datetime NULL,
dt_rate_apprd datetime NULL,
dt_rsltn_beg datetime NULL,
dt_rsltn_end datetime NULL,
dt_rslvd datetime NULL,
dt_rvw_due datetime NULL,
flag_iop_exmptn char(1) NOT NULL,
flag_othr_needed char(1) NOT NULL,
flag_reorg_pmrtg char(1) NOT NULL,
flag_rsltn_svcng char(1) NOT NULL,
id_asesmt datetime NULL,
id_mrtg datetime NOT NULL,
id_mrtg_ddlpi datetime NULL,
id_mrtg_ddlpi_end datetime NULL,
id_rsltn datetime NOT NULL,
ind_qc_rvw char(1) NOT NULL,
ind_risk char(1) NOT NULL,
ind_rslvd char(1) NOT NULL,
amt_sbrdnt_dbt numeric(13,2) NULL,
amt_ap numeric(13,2) NULL
)
June 16, 2011 at 4:01 am
robin 86662 (6/15/2011)
yayomayn, Your t-sql statement was very close. Could you please tweak it so there is a decimal between Seconds and Milliseconds instead of a colon? Your query results are: '6/15/2011 10:51:40:280 AM' and the destination format is: '6/11/2011 6:07:52.556 PM'Thank you.
This should do it:
SELECT CASE WHEN LEFT(CONVERT(CHAR,GETDATE(),101),2)<10 THEN
STUFF(RTRIM(CONVERT(CHAR, GETDATE(), 101)),1,1,'')
ELSE
RTRIM(CONVERT(CHAR, GETDATE(), 101))
END
+' '+STUFF(STUFF(RIGHT(CONVERT(CHAR, GETDATE(), 109),18),13,0,' '),9,1,'.')
June 16, 2011 at 4:45 am
Ah. This might be part of the problem. Sybase and SQL Server datetime data types aren't exactly the same.
Use the datetime and smalldatetime datatypes to store date and time information from January 1, 1753 through December 31, 9999. .... Dates outside this range must be entered, stored, and manipulated as char or varchar values.datetime columns hold dates between January 1, 1753 and December 31, 9999. datetime values are accurate to 1/300 second on platforms that support this level of granularity. Storage size is 8 bytes: 4 bytes for the number of days since the base date of January 1, 1900 and 4 bytes for the time of day.
Whereas SQL Server Books Online says:
Date range
January 1, 1753, through December 31, 9999
Time range
00:00:00 through 23:59:59.997
...
Accuracy
Rounded to increments of .000, .003, or .007 seconds
Notice the difference in the accuracy.
I think what you're going to have to do is CONVERT both values in your update statement up to the second, minute, or day in order to get this to work. Try anything more granular and you'll kill your match.
Check this link out (http://msdn.microsoft.com/en-us/library/ms187928.aspx) for the details on datetime styles for the CONVERT() function in SQL Server.
June 17, 2011 at 7:53 am
Brandie Tarvin (6/16/2011)
Notice the difference in the accuracy.
They look the same to me--0.000, 0.003 or 0.007 means 3 divisions in 1/100th of a second, which is 1/300th second accuracy. Same accuracy, different way of expressing it. Since DATETIME was, AFAIK, one of the data types carried over from Sybase to SQL Server, I'd be surprised if they were actually different!
June 17, 2011 at 10:26 am
paul.knibbs (6/17/2011)
Brandie Tarvin (6/16/2011)
Notice the difference in the accuracy.
They look the same to me--0.000, 0.003 or 0.007 means 3 divisions in 1/100th of a second, which is 1/300th second accuracy. Same accuracy, different way of expressing it. Since DATETIME was, AFAIK, one of the data types carried over from Sybase to SQL Server, I'd be surprised if they were actually different!
That's what I mean, Paul. If I'm reading that correctly, SQL only uses those three numbers instead of each individual .001, .002, etc. like Sybase uses. So if Sybase records a .015 but SQL Server only records one of the three above seconds, then the dates will never be the same.
Of course, I haven't looked at this or tested this to verify whether or not SQL Server really does only use those values. I'm just looking at the way BOL is describing it.
June 20, 2011 at 2:38 am
But if the Sybase DATETIME is only accurate to 1/300th of a second, it CAN'T be using any old values for the thousandths--0.001 would make no sense because the value isn't accurate to that!
June 20, 2011 at 5:01 am
paul.knibbs (6/20/2011)
But if the Sybase DATETIME is only accurate to 1/300th of a second, it CAN'T be using any old values for the thousandths--0.001 would make no sense because the value isn't accurate to that!
Now I'm confused, because this seems to be exactly the opposite of what you said earlier. Are you talking thousandths or hundredths?
June 20, 2011 at 6:17 am
3 divisions in 1/100th second is what I said earlier--maybe I'm not being clear. My argument was that the accuracy in both cases is 1/300th of a second, and the SQL server documentation is just expressing it differently to the Sybase documentation.
June 20, 2011 at 7:31 am
I appreciate everyone's input; the discussion has given me some directions to research. One approach I tried was to store the WHERE field value as a STRING datatype in the Source table. Then do a conversion to a DB_DATETIME datatype before mapping it to the Destination. In my sample data, I copied the value exactly from the Destination and pasted it into the Source to set up my source sample data. Unfortunately, I am now getting an error saying that it cannot do the conversion due to "a potential loss of data". (My server is down at the moment, so I cannot provide the exact error message.") I tried several variations along the lines of using strings and have not had any success. (Please see the above thread for the details of the issue.)
The approach I started over the weekend is to use the Script Task to manipulate the Command method of the Destination connection object. I'm going to attempt to run a stored procedure on the Sybase server that updates the records based on the PK value passed in. I say "attempt" because I have not yet gotten that far in this approach. I fear the Sybase Driver may be at play here as well.
Thanks,
Robin
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply