Author |
Message |
lorellan
Joined: 13 Jun 2011 Posts: 124 Country: Ecuador |
|
AlertCenter-Conversion varchar to datetime was out of range |
|
I have a problem with Central Repository, I get the following message:
ERROR: [Microsoft][SQL Server Native Client 10.0][SQL Server]La conversion del tipo de datos varchar en datetime produjo un valor fuera de intervalo.
STAGE_100001 and STAGE_100002:
db_audit.REP_STAGE:
My version of SQL Server, in where is installed the Central Repository is:
Microsoft SQL Server 2008 (SP1) - 10.0.2573.0 (Intel X86) Feb 4 2011 11:51:02 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2) (VM)
Please, help me, can I fix?
|
|
Wed Sep 28, 2011 7:19 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
Which audit data replication method are you using for this database? Data pump, bulk insert, or linked server? If you haven't tried yet, try linked server.
|
|
Wed Sep 28, 2011 11:23 pm |
|
![](templates/GreenTech/images/spacer.gif) |
lorellan
Joined: 13 Jun 2011 Posts: 124 Country: Ecuador |
|
|
|
data pump, because is Sybase.
The Central Repository is SQL Server 2008
|
|
Thu Sep 29, 2011 8:59 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
You should be able to use linked server if you get Sybase ADO.NET driver installed on the repository server. I'm asking to try linked server because it involves less data conversions, less middlewhere and allows the repository database to pull data directly from the audited database. IT doesn't always work, but it is worse a try.
|
|
Thu Sep 29, 2011 9:15 am |
|
![](templates/GreenTech/images/spacer.gif) |
lorellan
Joined: 13 Jun 2011 Posts: 124 Country: Ecuador |
|
|
|
I tried, but I get another error:
ERROR: [Microsoft][SQL Server Native Client 10.0][SQL Server]No se ha registrado el proveedor OLE DB "MSDASSQL".
I don't know what's the meaning of this error.
![](http://s3.postimage.org/cy37xrt0/No_se_ha_registrado_el_proveedor_OLE_DB_MSDASSQL.jpg)
|
|
Thu Sep 29, 2011 2:26 pm |
|
![](templates/GreenTech/images/spacer.gif) |
lorellan
Joined: 13 Jun 2011 Posts: 124 Country: Ecuador |
|
|
|
To avoid the conversion, I changed the type of data both tables:
db_audit.STAGE$100001
db_audit.STAGE$100002
Replica some data, but then back out the same error.
![](http://s3.postimage.org/dme38d1g/STAGE_100002_cambie_tipo_de_dato.jpg)
|
|
Thu Sep 29, 2011 2:37 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
What kind of values do you get in eventtime column in db_audit.STAGE$100001 table. Can you post some sample values?
|
|
Fri Sep 30, 2011 10:33 am |
|
![](templates/GreenTech/images/spacer.gif) |
lorellan
Joined: 13 Jun 2011 Posts: 124 Country: Ecuador |
|
|
|
None.
|
|
Fri Sep 30, 2011 10:34 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
Since you are suing ODBC for the connections, you can enabled ODBC tracing feature in the ODBC Administrator program . Run the job and check what's get recorded in the log. We need to see the format of date-time values.
|
|
Fri Sep 30, 2011 8:02 pm |
|
![](templates/GreenTech/images/spacer.gif) |
lorellan
Joined: 13 Jun 2011 Posts: 124 Country: Ecuador |
|
|
|
How can I enable ODBC tracing feature in the ODBC Administrator program?
|
|
Sat Oct 01, 2011 12:13 am |
|
![](templates/GreenTech/images/spacer.gif) |
lorellan
Joined: 13 Jun 2011 Posts: 124 Country: Ecuador |
|
|
|
Is this?
...
1000 records copied
1000 records copied
Total audit data records copied: 50952
process time: 932.432 seconds
performance: 54.6442 records per second
Validating table [sybsecurity.dbo.sysaudits_02]...
System audit trail table detected.
Transforming audit data to common format...
Transformation query execution time: 4.136 seconds.
Post-processing: Truncating staging table db_audit.STAGE$100002...
Post-processing: Truncate query execution time: 0.0 seconds.
Saving last run-time in the catalog...
Update query execution time: 0.0 seconds.
Updating job log...
Insert log query execution time: 0.01 seconds.
-----------------------------------------------
Table sybsecurity.dbo.sysaudits_02 processing time: 936.629 seconds.
Audit data archiving complete.
Disconnecting from database...
Disconnecting from repository database...
Closing Alert Center connection and terminating...
DATABASE: xxx - audit data archiving complete. Tables processed: 2. Tables failed: 0. Audit records archived: 50952. Processing time: 15 minutes and 38 seconds.
Total processing time: 15 minutes and 38 seconds.
I don't know why, after replication, data disappears. The purpose of having a central repository is that data is stored.
|
|
Sat Oct 01, 2011 12:39 am |
|
![](templates/GreenTech/images/spacer.gif) |
lorellan
Joined: 13 Jun 2011 Posts: 124 Country: Ecuador |
|
|
Sat Oct 01, 2011 12:49 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
On your Windows system, click the Start button,
then go to Control Panel
and then double-click Administrative Tools.
Double-click Data Sources (ODBC) icon.
Click Tracing tab
Note value of Log file Path field - this value points to the trace file.
Click Start Tracing Now button
Wait for the audit data replication job to run, or kick it in the alert Center scheduler using Run Now menu.
Review the log file after job run. We need to see the formatting of date-time values.
|
|
Tue Oct 04, 2011 1:00 am |
|
![](templates/GreenTech/images/spacer.gif) |
|