I have a windows 2003 server with sql server 2000
I run daily jobs that delete contents of sql tables an insert data from an
oracle9 database using
SELECT
*
INTO
DBname
FROM
OPENROWSET
(
'MSDASQL',
'DSN=ODBCOracleAccount;UID=oracleuser;PWD=password ;',
'SELECT
*
FROM
OracleDBName’
)
This worked until the oracle9 database was upgraded to oracle10. The new
oracle10 server is running windows 2003 server.
On the sql server I created a new service name in oracle client and tested
the connection it works but when I try to run the jobs–stored procedures this
is the following error
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Driver Manager] Data source name not found and no default
driver specified".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "(null)".
The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
MSORCL32.dll dated 3/24/2005.
Appreciate any help I can get.
"HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
>I have a windows 2003 server with sql server 2000
> I run daily jobs that delete contents of sql tables an insert data from an
> oracle9 database using
> SELECT
> *
> INTO
> DBname
> FROM
> OPENROWSET
> (
> 'MSDASQL',
> 'DSN=ODBCOracleAccount;UID=oracleuser;PWD=password ;',
> 'SELECT
> *
> FROM
> OracleDBName'
> )
> This worked until the oracle9 database was upgraded to oracle10. The new
> oracle10 server is running windows 2003 server.
> On the sql server I created a new service name in oracle client and tested
> the connection it works but when I try to run the jobs-stored procedures
> this
> is the following error
> OLE DB provider "MSDASQL" for linked server "(null)" returned message
> "[Microsoft][ODBC Driver Manager] Data source name not found and no
> default
> driver specified".
> Msg 7303, Level 16, State 1, Line 2
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "(null)".
> The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
> MSORCL32.dll dated 3/24/2005.
> Appreciate any help I can get.
>
Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Can you test it and connect? Is 10g perhaps in a different home than 9i
was?
- Arnie
|||"Arnie" wrote:
> "HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
> news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
> Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Yes
I can not connect using the ODBC drivers I set up a different serivce name
and tested the oracle client made a connection but no connection using ODBC
> Can you test it and connect? Is 10g perhaps in a different home than 9i
> was?
home meaning
the old oracle9i server was renamed and the IP address changed
the new oracle 10g has the same DNS name and same IP address as the old 9i
> - Arnie
>
>
Showing posts with label jobs. Show all posts
Showing posts with label jobs. Show all posts
Monday, March 19, 2012
Oracle10 ODBC connection
I have a windows 2003 server with sql server 2000
I run daily jobs that delete contents of sql tables an insert data from an
oracle9 database using
SELECT
*
INTO
DBname
FROM
OPENROWSET
(
'MSDASQL',
'DSN=ODBCOracleAccount;UID=oracleuser;PW
D=password;',
'SELECT
*
FROM
OracleDBName’
)
This worked until the oracle9 database was upgraded to oracle10. The new
oracle10 server is running windows 2003 server.
On the sql server I created a new service name in oracle client and tested
the connection it works but when I try to run the jobs–stored procedures t
his
is the following error
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Driver Manager] Data source name not found and no
default
driver specified".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "(null)".
The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
MSORCL32.dll dated 3/24/2005.
Appreciate any help I can get."HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
>I have a windows 2003 server with sql server 2000
> I run daily jobs that delete contents of sql tables an insert data from an
> oracle9 database using
> SELECT
> *
> INTO
> DBname
> FROM
> OPENROWSET
> (
> 'MSDASQL',
> 'DSN=ODBCOracleAccount;UID=oracleuser;PW
D=password;',
> 'SELECT
> *
> FROM
> OracleDBName'
> )
> This worked until the oracle9 database was upgraded to oracle10. The new
> oracle10 server is running windows 2003 server.
> On the sql server I created a new service name in oracle client and tested
> the connection it works but when I try to run the jobs-stored procedures
> this
> is the following error
> OLE DB provider "MSDASQL" for linked server "(null)" returned message
> "[Microsoft][ODBC Driver Manager] Data source name not found and n
o
> default
> driver specified".
> Msg 7303, Level 16, State 1, Line 2
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "(null)".
> The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
> MSORCL32.dll dated 3/24/2005.
> Appreciate any help I can get.
>
Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Can you test it and connect? Is 10g perhaps in a different home than 9i
was?
- Arnie|||"Arnie" wrote:
> "HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
> news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
> Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Yes
I can not connect using the ODBC drivers I set up a different serivce name
and tested the oracle client made a connection but no connection using ODBC
> Can you test it and connect? Is 10g perhaps in a different home than 9i
> was?
home meaning
the old oracle9i server was renamed and the IP address changed
the new oracle 10g has the same DNS name and same IP address as the old 9i
> - Arnie
>
>
I run daily jobs that delete contents of sql tables an insert data from an
oracle9 database using
SELECT
*
INTO
DBname
FROM
OPENROWSET
(
'MSDASQL',
'DSN=ODBCOracleAccount;UID=oracleuser;PW
D=password;',
'SELECT
*
FROM
OracleDBName’
)
This worked until the oracle9 database was upgraded to oracle10. The new
oracle10 server is running windows 2003 server.
On the sql server I created a new service name in oracle client and tested
the connection it works but when I try to run the jobs–stored procedures t
his
is the following error
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Driver Manager] Data source name not found and no
default
driver specified".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "(null)".
The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
MSORCL32.dll dated 3/24/2005.
Appreciate any help I can get."HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
>I have a windows 2003 server with sql server 2000
> I run daily jobs that delete contents of sql tables an insert data from an
> oracle9 database using
> SELECT
> *
> INTO
> DBname
> FROM
> OPENROWSET
> (
> 'MSDASQL',
> 'DSN=ODBCOracleAccount;UID=oracleuser;PW
D=password;',
> 'SELECT
> *
> FROM
> OracleDBName'
> )
> This worked until the oracle9 database was upgraded to oracle10. The new
> oracle10 server is running windows 2003 server.
> On the sql server I created a new service name in oracle client and tested
> the connection it works but when I try to run the jobs-stored procedures
> this
> is the following error
> OLE DB provider "MSDASQL" for linked server "(null)" returned message
> "[Microsoft][ODBC Driver Manager] Data source name not found and n
o
> default
> driver specified".
> Msg 7303, Level 16, State 1, Line 2
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "(null)".
> The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
> MSORCL32.dll dated 3/24/2005.
> Appreciate any help I can get.
>
Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Can you test it and connect? Is 10g perhaps in a different home than 9i
was?
- Arnie|||"Arnie" wrote:
> "HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
> news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
> Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Yes
I can not connect using the ODBC drivers I set up a different serivce name
and tested the oracle client made a connection but no connection using ODBC
> Can you test it and connect? Is 10g perhaps in a different home than 9i
> was?
home meaning
the old oracle9i server was renamed and the IP address changed
the new oracle 10g has the same DNS name and same IP address as the old 9i
> - Arnie
>
>
Monday, March 12, 2012
Oracle replication failed
Some replication jobs failed and it shows 'data conversion failed' error MSSQL_REPL20037. Any idea on it ? I can't find the error in online book. THanks in advance!
Where did you encounter this error? Command line interface or GUI?Can you provide more information such as the repro steps, environment setting, and the table schema?
Regards,
Gary Chen|||I found the error 'The process could not bulk copy into table '"dbo"."CCB_CLINM2"'.' in view sysnchronization status. and i checked the history in job agent that it shows
"Message
2005-12-14 03:35:33.805 Connecting to Subscriber 'CCBHK08D'
2005-12-14 03:35:33.977 Skipping file 'CCB_CLINM2_2.sch' because it has already been delivered for a previous article or by a previously interrupted snapshot.
2005-12-14 03:35:33.993 Bulk copying data into table 'CCB_CLINM2'
2005-12-14 03:35:34.259 Agent message code 20037. The process could not bulk copy into table '"dbo"."CCB_CLINM2"'.
2005-12-14 03:35:34.321 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
2005-12-14 03:35:34.337 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed"
A few small tables are replicated to the MSSQL2005 but some tables got the same error. The machine is running in 64-bits. And the replication testing in 32-bits is fine. Also, I found that some ODBC drivers are missing in MSSQL 2005 even installed with MDAC2.8. Is it support 32-bits only? Thanks in advance|||also, I found the error in replication monitor
"Error messages:
The process could not bulk copy into table '"dbo"."SYSTWODB_CABL"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
Data conversion failed"|||
Below is the ddl of table (in both mssql and oracle) which failed in replication
USE [dev]
GO
/****** Object: Table [dbo].[SYSTWODB_EXCHR] Script Date: 12/14/2005 14:22:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SYSTWODB_EXCHR](
[GLB_DTIME] [numeric](16, 0) NOT NULL,
[CCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[XCCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[EXCH_GRP] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[XEXCH_GRP] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[REL_DAY] [numeric](5, 0) NOT NULL,
[XREL_DAY] [numeric](5, 0) NOT NULL,
[BASEX_CCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[BSE_RATEID] [varchar](7) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[INP_DATE] [numeric](5, 0) NOT NULL,
[INP_TIME] [numeric](6, 0) NOT NULL,
[MID_MKT_LN] [numeric](14, 10) NOT NULL,
[MID_MKT_TY] [numeric](14, 10) NOT NULL,
[MKT_BUY] [numeric](14, 10) NOT NULL,
[MKT_RATEID] [varchar](7) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[MKT_SELL] [numeric](14, 10) NOT NULL,
[RATE_USAGE] [varchar](1) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[SOFTLOCK] [numeric](2, 0) NOT NULL,
[WIDTH] [varchar](1) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
CONSTRAINT [MSHREPL_49_PK] PRIMARY KEY CLUSTERED
(
[GLB_DTIME] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
*******************************************
CREATE TABLE "LINC"."SYSTWODB_EXCHR"("GLB_DTIME" NUMBER(16) NOT
NULL, "CCY" VARCHAR2(4) NOT NULL, "XCCY" VARCHAR2(4) NOT NULL,
"EXCH_GRP" VARCHAR2(4) NOT NULL, "XEXCH_GRP" VARCHAR2(4) NOT
NULL, "REL_DAY" NUMBER(5) NOT NULL, "XREL_DAY" NUMBER(5) NOT
NULL, "BASEX_CCY" VARCHAR2(4) NOT NULL, "BSE_RATEID" VARCHAR2(7)
NOT NULL, "INP_DATE" NUMBER(5) NOT NULL, "INP_TIME" NUMBER(6)
NOT NULL, "MID_MKT_LN" NUMBER(14, 10) NOT NULL, "MID_MKT_TY"
NUMBER(14, 10) NOT NULL, "MKT_BUY" NUMBER(14, 10) NOT NULL,
"MKT_RATEID" VARCHAR2(7) NOT NULL, "MKT_SELL" NUMBER(14, 10) NOT
NULL, "RATE_USAGE" VARCHAR2(1) NOT NULL, "SOFTLOCK" NUMBER(2)
NOT NULL, "WIDTH" VARCHAR2(1) NOT NULL,
CONSTRAINT "SYSTWODB_EXCHR_PK" PRIMARY KEY("GLB_DTIME"),
CONSTRAINT "SYS_C0022019" CHECK("GLB_DTIME" IS NOT NULL),
CONSTRAINT "SYS_C0022020" CHECK("CCY" IS NOT NULL),
CONSTRAINT "SYS_C0022021" CHECK("XCCY" IS NOT NULL),
CONSTRAINT "SYS_C0022022" CHECK("EXCH_GRP" IS NOT NULL),
CONSTRAINT "SYS_C0022023" CHECK("XEXCH_GRP" IS NOT NULL),
CONSTRAINT "SYS_C0022024" CHECK("REL_DAY" IS NOT NULL),
CONSTRAINT "SYS_C0022025" CHECK("XREL_DAY" IS NOT NULL),
CONSTRAINT "SYS_C0022026" CHECK("BASEX_CCY" IS NOT NULL),
CONSTRAINT "SYS_C0022027" CHECK("BSE_RATEID" IS NOT NULL),
CONSTRAINT "SYS_C0022028" CHECK("INP_DATE" IS NOT NULL),
CONSTRAINT "SYS_C0022029" CHECK("INP_TIME" IS NOT NULL),
CONSTRAINT "SYS_C0022030" CHECK("MID_MKT_LN" IS NOT NULL),
CONSTRAINT "SYS_C0022031" CHECK("MID_MKT_TY" IS NOT NULL),
CONSTRAINT "SYS_C0022032" CHECK("MKT_BUY" IS NOT NULL),
CONSTRAINT "SYS_C0022033" CHECK("MKT_RATEID" IS NOT NULL),
CONSTRAINT "SYS_C0022034" CHECK("MKT_SELL" IS NOT NULL),
CONSTRAINT "SYS_C0022035" CHECK("RATE_USAGE" IS NOT NULL),
CONSTRAINT "SYS_C0022036" CHECK("SOFTLOCK" IS NOT NULL),
CONSTRAINT "SYS_C0022037" CHECK("WIDTH" IS NOT NULL))
TABLESPACE "URBIS" PCTFREE 10 PCTUSED 70 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 2604K NEXT 352K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING|||
Besides, I found a message regarding to the failed replication in error log
'Error: 14151, Severity: 18, State: 1'
Any Hints on it? Thanks in advance
|||Let's try to narrow it down a bit further.
So, the replication works fine on 32bit server and all tables copied fine, right? Only 64bit SQL server is having problem? Has the replication ever work in your 64bit environment at all? Can you tell me what your environment, OS, topology, and platform?
A minimum series of steps that can help to reproduce the error.
What type of Oracle publisher do you use? Gateway or Complete?
I also notice that you uses "Chinese_Hong_Kong_Stroke_90_CI_AS" when you create your table. What is the character set that you have install on your SQL server, OS, and Oracle server?
Regards,
Gary
|||1. same set of tables and same source DB (with vchar and number as data type)
2. source: oracle 9.2 running in AIX platform.
3, publisher is gateway
4. SQL collation is Chinese _hong_kong_stroke_90_CI_AS, OS is 'Chinese (Hong Kong SAR)'
5. transactional publication with push subscription
6. Destination: Win2003 standard x64 edition sp1 with MSSQL2005 EVAL edition (x64) while another machine is Win2003 standard edition sp1 MSSQL2005 eval edition
7. All tables can be replicated to MSSQL2005(32bits) but some of them failed in the 64 bits one.
points 1-5 are the same in both 32 and 64 bits machine
many many Thanks for your help
|||
Hi,
This may or may not help you but I came accross this "data conversion" error when i was trying to reinitialize a subscriber when we migrated the publisher from 2000 to 2005. Our distributor also migrated to a SQL 2005 instance and it was a push subscription.
The issue in this instance was that the underlying data type length for a UDF created at the subscriber originally by SQL 2000 was smaller than what SQL 2005 needed. During the initialization the UDF was not being created at the subscriber by the SQL 2005 as it was already there so the solution was to drop the UDF and let the initialization process create it. Luckily the UDF was not being used anywhere else so we were able to drop it.
Cheers,
Priyanga
Oracle replication failed
We are using MSSQL2005 (64-bits) and replicating data from oracle to
mssql2005 DB. Some replication jobs failed and it shows 'data conversion
failed' error MSSQL_REPL20037. Any idea on it ? I can't find the error in
online book. THanks in advance!
Which agent is this failing on? Can you enable logging to see the problem
statement?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:25858670-C644-4B8F-8B2A-288E1460AC3F@.microsoft.com...
> We are using MSSQL2005 (64-bits) and replicating data from oracle to
> mssql2005 DB. Some replication jobs failed and it shows 'data conversion
> failed' error MSSQL_REPL20037. Any idea on it ? I can't find the error
> in
> online book. THanks in advance!
>
|||Below is the trace info on the failed replication. Thanks a lot!
EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id =
0x0564E4D45972EC48A051BBA5B6CB9F5D, @.step_id = 2, @.sql_message_id = 0,
@.sql_severity = 0, @.run_status = 4, @.run_date = 20051216, @.run_time = 93014,
@.run_duration = 2220,
@.operator_id_emailed = 0, @.operator_id_netsent = 0, @.operator_id_paged = 0,
@.retries_attempted = 0, @.session_id = 5, @.message = N'
2005-12-16 01:52:05.319 Bulk copying data into table ''SYSTWODB_CNCOM''
2005-12-16 01:52:29.413 Bulk copied data into table ''SYSTWODB_CNCOM''
(90546 rows)
2005-12-16 01:52:29.648 Applied script ''SYSTWODB_CNEOD_4.sch''
2005-12-16 01:52:29.663 Bulk copying data into table ''SYSTWODB_CNEOD''
2005-12-16 01:52:34.038 Agent message code 20037. The process could not bulk
copy into table ''"dbo"."SYSTWODB_CNEOD"''.
2005-12-16 01:52:34.101 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
2005-12-16 01:52:34.116 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
2005-12-16 01:52:34.116 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
2005-12-16 01:52:34.132 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
2005-12-16 01:52:34.132 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
'
"Hilary Cotter" wrote:
> Which agent is this failing on? Can you enable logging to see the problem
> statement?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:25858670-C644-4B8F-8B2A-288E1460AC3F@.microsoft.com...
>
>
|||can you post the schema of SYSTWODB_CNEOD
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:6181D00E-B82E-4EA2-AE13-9EBE37337F2F@.microsoft.com...[vbcol=seagreen]
> Below is the trace info on the failed replication. Thanks a lot!
> EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id =
> 0x0564E4D45972EC48A051BBA5B6CB9F5D, @.step_id = 2, @.sql_message_id = 0,
> @.sql_severity = 0, @.run_status = 4, @.run_date = 20051216, @.run_time =
> 93014,
> @.run_duration = 2220,
> @.operator_id_emailed = 0, @.operator_id_netsent = 0, @.operator_id_paged =
> 0,
> @.retries_attempted = 0, @.session_id = 5, @.message = N'
> 2005-12-16 01:52:05.319 Bulk copying data into table ''SYSTWODB_CNCOM''
> 2005-12-16 01:52:29.413 Bulk copied data into table ''SYSTWODB_CNCOM''
> (90546 rows)
> 2005-12-16 01:52:29.648 Applied script ''SYSTWODB_CNEOD_4.sch''
> 2005-12-16 01:52:29.663 Bulk copying data into table ''SYSTWODB_CNEOD''
> 2005-12-16 01:52:34.038 Agent message code 20037. The process could not
> bulk
> copy into table ''"dbo"."SYSTWODB_CNEOD"''.
> 2005-12-16 01:52:34.101 Category:NULL
> Source: Microsoft SQL Native Client
> Number:
> Message: Data conversion failed
> 2005-12-16 01:52:34.116 Category:NULL
> Source: Microsoft SQL Native Client
> Number:
> Message: Data conversion failed
> 2005-12-16 01:52:34.116 Category:NULL
> Source: Microsoft SQL Native Client
> Number:
> Message: Data conversion failed
> 2005-12-16 01:52:34.132 Category:NULL
> Source: Microsoft SQL Native Client
> Number:
> Message: Data conversion failed
> 2005-12-16 01:52:34.132 Category:NULL
> Source: Microsoft SQL Native Client
> Number:
> Message: Data conversion failed
> '
> "Hilary Cotter" wrote:
|||Below is the ddl of table (in both mssql and oracle) which failed in
replication
USE [dev]
GO
/****** Object: Table [dbo].[SYSTWODB_EXCHR] Script Date: 12/14/2005
14:22:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SYSTWODB_EXCHR](
[GLB_DTIME] [numeric](16, 0) NOT NULL,
[CCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[XCCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[EXCH_GRP] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[XEXCH_GRP] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[REL_DAY] [numeric](5, 0) NOT NULL,
[XREL_DAY] [numeric](5, 0) NOT NULL,
[BASEX_CCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[BSE_RATEID] [varchar](7) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[INP_DATE] [numeric](5, 0) NOT NULL,
[INP_TIME] [numeric](6, 0) NOT NULL,
[MID_MKT_LN] [numeric](14, 10) NOT NULL,
[MID_MKT_TY] [numeric](14, 10) NOT NULL,
[MKT_BUY] [numeric](14, 10) NOT NULL,
[MKT_RATEID] [varchar](7) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[MKT_SELL] [numeric](14, 10) NOT NULL,
[RATE_USAGE] [varchar](1) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[SOFTLOCK] [numeric](2, 0) NOT NULL,
[WIDTH] [varchar](1) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
CONSTRAINT [MSHREPL_49_PK] PRIMARY KEY CLUSTERED
(
[GLB_DTIME] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
*******************************************
CREATE TABLE "LINC"."SYSTWODB_EXCHR"("GLB_DTIME" NUMBER(16) NOT
NULL, "CCY" VARCHAR2(4) NOT NULL, "XCCY" VARCHAR2(4) NOT NULL,
"EXCH_GRP" VARCHAR2(4) NOT NULL, "XEXCH_GRP" VARCHAR2(4) NOT
NULL, "REL_DAY" NUMBER(5) NOT NULL, "XREL_DAY" NUMBER(5) NOT
NULL, "BASEX_CCY" VARCHAR2(4) NOT NULL, "BSE_RATEID" VARCHAR2(7)
NOT NULL, "INP_DATE" NUMBER(5) NOT NULL, "INP_TIME" NUMBER(6)
NOT NULL, "MID_MKT_LN" NUMBER(14, 10) NOT NULL, "MID_MKT_TY"
NUMBER(14, 10) NOT NULL, "MKT_BUY" NUMBER(14, 10) NOT NULL,
"MKT_RATEID" VARCHAR2(7) NOT NULL, "MKT_SELL" NUMBER(14, 10) NOT
NULL, "RATE_USAGE" VARCHAR2(1) NOT NULL, "SOFTLOCK" NUMBER(2)
NOT NULL, "WIDTH" VARCHAR2(1) NOT NULL,
CONSTRAINT "SYSTWODB_EXCHR_PK" PRIMARY KEY("GLB_DTIME"),
CONSTRAINT "SYS_C0022019" CHECK("GLB_DTIME" IS NOT NULL),
CONSTRAINT "SYS_C0022020" CHECK("CCY" IS NOT NULL),
CONSTRAINT "SYS_C0022021" CHECK("XCCY" IS NOT NULL),
CONSTRAINT "SYS_C0022022" CHECK("EXCH_GRP" IS NOT NULL),
CONSTRAINT "SYS_C0022023" CHECK("XEXCH_GRP" IS NOT NULL),
CONSTRAINT "SYS_C0022024" CHECK("REL_DAY" IS NOT NULL),
CONSTRAINT "SYS_C0022025" CHECK("XREL_DAY" IS NOT NULL),
CONSTRAINT "SYS_C0022026" CHECK("BASEX_CCY" IS NOT NULL),
CONSTRAINT "SYS_C0022027" CHECK("BSE_RATEID" IS NOT NULL),
CONSTRAINT "SYS_C0022028" CHECK("INP_DATE" IS NOT NULL),
CONSTRAINT "SYS_C0022029" CHECK("INP_TIME" IS NOT NULL),
CONSTRAINT "SYS_C0022030" CHECK("MID_MKT_LN" IS NOT NULL),
CONSTRAINT "SYS_C0022031" CHECK("MID_MKT_TY" IS NOT NULL),
CONSTRAINT "SYS_C0022032" CHECK("MKT_BUY" IS NOT NULL),
CONSTRAINT "SYS_C0022033" CHECK("MKT_RATEID" IS NOT NULL),
CONSTRAINT "SYS_C0022034" CHECK("MKT_SELL" IS NOT NULL),
CONSTRAINT "SYS_C0022035" CHECK("RATE_USAGE" IS NOT NULL),
CONSTRAINT "SYS_C0022036" CHECK("SOFTLOCK" IS NOT NULL),
CONSTRAINT "SYS_C0022037" CHECK("WIDTH" IS NOT NULL))
TABLESPACE "URBIS" PCTFREE 10 PCTUSED 70 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 2604K NEXT 352K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING
Thanks a lot!
"Hilary Cotter" wrote:
> can you post the schema of SYSTWODB_CNEOD
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:6181D00E-B82E-4EA2-AE13-9EBE37337F2F@.microsoft.com...
>
>
mssql2005 DB. Some replication jobs failed and it shows 'data conversion
failed' error MSSQL_REPL20037. Any idea on it ? I can't find the error in
online book. THanks in advance!
Which agent is this failing on? Can you enable logging to see the problem
statement?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:25858670-C644-4B8F-8B2A-288E1460AC3F@.microsoft.com...
> We are using MSSQL2005 (64-bits) and replicating data from oracle to
> mssql2005 DB. Some replication jobs failed and it shows 'data conversion
> failed' error MSSQL_REPL20037. Any idea on it ? I can't find the error
> in
> online book. THanks in advance!
>
|||Below is the trace info on the failed replication. Thanks a lot!
EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id =
0x0564E4D45972EC48A051BBA5B6CB9F5D, @.step_id = 2, @.sql_message_id = 0,
@.sql_severity = 0, @.run_status = 4, @.run_date = 20051216, @.run_time = 93014,
@.run_duration = 2220,
@.operator_id_emailed = 0, @.operator_id_netsent = 0, @.operator_id_paged = 0,
@.retries_attempted = 0, @.session_id = 5, @.message = N'
2005-12-16 01:52:05.319 Bulk copying data into table ''SYSTWODB_CNCOM''
2005-12-16 01:52:29.413 Bulk copied data into table ''SYSTWODB_CNCOM''
(90546 rows)
2005-12-16 01:52:29.648 Applied script ''SYSTWODB_CNEOD_4.sch''
2005-12-16 01:52:29.663 Bulk copying data into table ''SYSTWODB_CNEOD''
2005-12-16 01:52:34.038 Agent message code 20037. The process could not bulk
copy into table ''"dbo"."SYSTWODB_CNEOD"''.
2005-12-16 01:52:34.101 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
2005-12-16 01:52:34.116 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
2005-12-16 01:52:34.116 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
2005-12-16 01:52:34.132 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
2005-12-16 01:52:34.132 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
'
"Hilary Cotter" wrote:
> Which agent is this failing on? Can you enable logging to see the problem
> statement?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:25858670-C644-4B8F-8B2A-288E1460AC3F@.microsoft.com...
>
>
|||can you post the schema of SYSTWODB_CNEOD
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:6181D00E-B82E-4EA2-AE13-9EBE37337F2F@.microsoft.com...[vbcol=seagreen]
> Below is the trace info on the failed replication. Thanks a lot!
> EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id =
> 0x0564E4D45972EC48A051BBA5B6CB9F5D, @.step_id = 2, @.sql_message_id = 0,
> @.sql_severity = 0, @.run_status = 4, @.run_date = 20051216, @.run_time =
> 93014,
> @.run_duration = 2220,
> @.operator_id_emailed = 0, @.operator_id_netsent = 0, @.operator_id_paged =
> 0,
> @.retries_attempted = 0, @.session_id = 5, @.message = N'
> 2005-12-16 01:52:05.319 Bulk copying data into table ''SYSTWODB_CNCOM''
> 2005-12-16 01:52:29.413 Bulk copied data into table ''SYSTWODB_CNCOM''
> (90546 rows)
> 2005-12-16 01:52:29.648 Applied script ''SYSTWODB_CNEOD_4.sch''
> 2005-12-16 01:52:29.663 Bulk copying data into table ''SYSTWODB_CNEOD''
> 2005-12-16 01:52:34.038 Agent message code 20037. The process could not
> bulk
> copy into table ''"dbo"."SYSTWODB_CNEOD"''.
> 2005-12-16 01:52:34.101 Category:NULL
> Source: Microsoft SQL Native Client
> Number:
> Message: Data conversion failed
> 2005-12-16 01:52:34.116 Category:NULL
> Source: Microsoft SQL Native Client
> Number:
> Message: Data conversion failed
> 2005-12-16 01:52:34.116 Category:NULL
> Source: Microsoft SQL Native Client
> Number:
> Message: Data conversion failed
> 2005-12-16 01:52:34.132 Category:NULL
> Source: Microsoft SQL Native Client
> Number:
> Message: Data conversion failed
> 2005-12-16 01:52:34.132 Category:NULL
> Source: Microsoft SQL Native Client
> Number:
> Message: Data conversion failed
> '
> "Hilary Cotter" wrote:
|||Below is the ddl of table (in both mssql and oracle) which failed in
replication
USE [dev]
GO
/****** Object: Table [dbo].[SYSTWODB_EXCHR] Script Date: 12/14/2005
14:22:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SYSTWODB_EXCHR](
[GLB_DTIME] [numeric](16, 0) NOT NULL,
[CCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[XCCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[EXCH_GRP] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[XEXCH_GRP] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[REL_DAY] [numeric](5, 0) NOT NULL,
[XREL_DAY] [numeric](5, 0) NOT NULL,
[BASEX_CCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[BSE_RATEID] [varchar](7) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[INP_DATE] [numeric](5, 0) NOT NULL,
[INP_TIME] [numeric](6, 0) NOT NULL,
[MID_MKT_LN] [numeric](14, 10) NOT NULL,
[MID_MKT_TY] [numeric](14, 10) NOT NULL,
[MKT_BUY] [numeric](14, 10) NOT NULL,
[MKT_RATEID] [varchar](7) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[MKT_SELL] [numeric](14, 10) NOT NULL,
[RATE_USAGE] [varchar](1) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[SOFTLOCK] [numeric](2, 0) NOT NULL,
[WIDTH] [varchar](1) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
CONSTRAINT [MSHREPL_49_PK] PRIMARY KEY CLUSTERED
(
[GLB_DTIME] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
*******************************************
CREATE TABLE "LINC"."SYSTWODB_EXCHR"("GLB_DTIME" NUMBER(16) NOT
NULL, "CCY" VARCHAR2(4) NOT NULL, "XCCY" VARCHAR2(4) NOT NULL,
"EXCH_GRP" VARCHAR2(4) NOT NULL, "XEXCH_GRP" VARCHAR2(4) NOT
NULL, "REL_DAY" NUMBER(5) NOT NULL, "XREL_DAY" NUMBER(5) NOT
NULL, "BASEX_CCY" VARCHAR2(4) NOT NULL, "BSE_RATEID" VARCHAR2(7)
NOT NULL, "INP_DATE" NUMBER(5) NOT NULL, "INP_TIME" NUMBER(6)
NOT NULL, "MID_MKT_LN" NUMBER(14, 10) NOT NULL, "MID_MKT_TY"
NUMBER(14, 10) NOT NULL, "MKT_BUY" NUMBER(14, 10) NOT NULL,
"MKT_RATEID" VARCHAR2(7) NOT NULL, "MKT_SELL" NUMBER(14, 10) NOT
NULL, "RATE_USAGE" VARCHAR2(1) NOT NULL, "SOFTLOCK" NUMBER(2)
NOT NULL, "WIDTH" VARCHAR2(1) NOT NULL,
CONSTRAINT "SYSTWODB_EXCHR_PK" PRIMARY KEY("GLB_DTIME"),
CONSTRAINT "SYS_C0022019" CHECK("GLB_DTIME" IS NOT NULL),
CONSTRAINT "SYS_C0022020" CHECK("CCY" IS NOT NULL),
CONSTRAINT "SYS_C0022021" CHECK("XCCY" IS NOT NULL),
CONSTRAINT "SYS_C0022022" CHECK("EXCH_GRP" IS NOT NULL),
CONSTRAINT "SYS_C0022023" CHECK("XEXCH_GRP" IS NOT NULL),
CONSTRAINT "SYS_C0022024" CHECK("REL_DAY" IS NOT NULL),
CONSTRAINT "SYS_C0022025" CHECK("XREL_DAY" IS NOT NULL),
CONSTRAINT "SYS_C0022026" CHECK("BASEX_CCY" IS NOT NULL),
CONSTRAINT "SYS_C0022027" CHECK("BSE_RATEID" IS NOT NULL),
CONSTRAINT "SYS_C0022028" CHECK("INP_DATE" IS NOT NULL),
CONSTRAINT "SYS_C0022029" CHECK("INP_TIME" IS NOT NULL),
CONSTRAINT "SYS_C0022030" CHECK("MID_MKT_LN" IS NOT NULL),
CONSTRAINT "SYS_C0022031" CHECK("MID_MKT_TY" IS NOT NULL),
CONSTRAINT "SYS_C0022032" CHECK("MKT_BUY" IS NOT NULL),
CONSTRAINT "SYS_C0022033" CHECK("MKT_RATEID" IS NOT NULL),
CONSTRAINT "SYS_C0022034" CHECK("MKT_SELL" IS NOT NULL),
CONSTRAINT "SYS_C0022035" CHECK("RATE_USAGE" IS NOT NULL),
CONSTRAINT "SYS_C0022036" CHECK("SOFTLOCK" IS NOT NULL),
CONSTRAINT "SYS_C0022037" CHECK("WIDTH" IS NOT NULL))
TABLESPACE "URBIS" PCTFREE 10 PCTUSED 70 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 2604K NEXT 352K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING
Thanks a lot!
"Hilary Cotter" wrote:
> can you post the schema of SYSTWODB_CNEOD
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:6181D00E-B82E-4EA2-AE13-9EBE37337F2F@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)