SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
AlertCenter-Conversion varchar to datetime was out of range

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
AlertCenter-Conversion varchar to datetime was out of range
Author Message
lorellan



Joined: 13 Jun 2011
Posts: 124
Country: Ecuador

Post AlertCenter-Conversion varchar to datetime was out of range Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6505

Post Reply with quote
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 View user's profile Send private message
lorellan



Joined: 13 Jun 2011
Posts: 124
Country: Ecuador

Post Reply with quote
data pump, because is Sybase.
The Central Repository is SQL Server 2008
Thu Sep 29, 2011 8:59 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6505

Post Reply with quote
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 View user's profile Send private message
lorellan



Joined: 13 Jun 2011
Posts: 124
Country: Ecuador

Post Reply with quote
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.


Thu Sep 29, 2011 2:26 pm View user's profile Send private message
lorellan



Joined: 13 Jun 2011
Posts: 124
Country: Ecuador

Post Reply with quote
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.




Thu Sep 29, 2011 2:37 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6505

Post Reply with quote
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 View user's profile Send private message
lorellan



Joined: 13 Jun 2011
Posts: 124
Country: Ecuador

Post Reply with quote
None.
Fri Sep 30, 2011 10:34 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6505

Post Reply with quote
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 View user's profile Send private message
lorellan



Joined: 13 Jun 2011
Posts: 124
Country: Ecuador

Post Reply with quote
How can I enable ODBC tracing feature in the ODBC Administrator program?
Sat Oct 01, 2011 12:13 am View user's profile Send private message
lorellan



Joined: 13 Jun 2011
Posts: 124
Country: Ecuador

Post Reply with quote
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 View user's profile Send private message
lorellan



Joined: 13 Jun 2011
Posts: 124
Country: Ecuador

Post Reply with quote
It's in blank.


Sat Oct 01, 2011 12:49 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6505

Post Reply with quote
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 View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.