September 28, 2004 at 11:50 am
I have a table like:In my server
[Entry_Date] [smalldatetime] NULL ,
[Usage_Date] [smalldatetime] NULL ,
[Process_Date] [smalldatetime] NULL ,
[Source_System] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Total_Event] [bigint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [PSA20040919] (
[ENTRY_DATE] [smalldatetime] NULL ,
[FILE_NAME] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RECTYPE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[A_NUMBER] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[B_NUMBER] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PSA_DATE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PSA_TIME] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TARIFF_PLAN] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CURRENCY_SIGN] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CHARGED_AMOUNT] [float] NULL ,
[REMAINING_BALANCE] [float] NULL ,
[OPERATION] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATE_USAGE] [smalldatetime] NULL
) ON [PRIMARY]
in the server gpra740
now I would like to do following insertion on my server :
insert into dbo.tbl_C09_SMSC_To_PSA
(entry_date,usage_date,process_date,source_system,total_event)
select '2004-09-01' as a ,date_usage,'20040901' as b ,'PSA' as c,count(*) as d
from gpra740.psadb.dbo. PSA20040919 where charged_amount=-2.3
group by date_usage
But it generates Following Error:
Server: Msg 7341, Level 16, State 2, Line 1
Could not get the current row value of column '(user generated expression).Expr1003' from the OLE DB provider 'SQLOLEDB'. Could not convert the data value due to reasons other than sign mismatch or overflow.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetData returned 0x80040e21: Data status returned from the provider: [COLUMN_NAME=Expr1003 STATUS=DBSTATUS_E_CANTCONVERTVALUE]].
insert into dbo.tbl_C09_SMSC_To_PSA
(entry_date,usage_date,process_date,source_system,total_event)
select '2004-09-01' as a ,date_usage,'20040901' as b ,'PSA' as c,count(*) as d
from dbo. PSA20040919 where charged_amount=-2.3
group by date_usage
then runs smoothly, then what the problem with my previous query?
Then another interesting things ! if I create a dummy function then it also runs
Like:
RETURNs varchar(50)
--AS
BEGIN
RETURN @mstring
END
insert into dbo.tbl_C09_SMSC_To_PSA
(entry_date,usage_date,process_date,source_system,total_event)
select dbo.Fx_dummy('20040901') as a ,date_usage,dbo.Fx_dummy('20040901') as b ,dbo.Fx_dummy('PSA')as c,count(*) as d
from gpra740.psadb.dbo.psa20040831 where charged_amount=-2.3
group by date_usage
Then What is the problem…..?
Can you help me on that ……
Thanks in advance
Muntasir
..Better Than Before...
October 1, 2004 at 8:00 am
This was removed by the editor as SPAM
October 3, 2004 at 5:04 pm
'2004-09-01' - the separators were removed in the 'dummy function' example, that could be the difference ?
Maybe the REMOTE server '2004-09-01' has to convert it to a date (to return the Select resulset), and it fails.
If this fails it will prove it:
Select convert( smalldatetime, '2004-09-01' ),
count(*)
from gpra740.psadb.dbo.PSA20040919
where charged_amount=-2.3
Change the date format ?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply