Sql-server – SQL Server – TIMESTAMP caused exception

postgresqlsql serversymmetric-ds

I am syncing from PostgreSql to MSSQL using SymmetricDS. Some tables of the source database have timestamp field set too '0001-01-01 00:00:00.0' and it could not be able to insert to destination(SQL Server) database.

Is there anyway to work around this ?

AcknowledgeService - The outgoing batch 001-987 failed: Parameter arg '0001-01-01 00:00:00.0' type: TIMESTAMP caused exception: Only dates between January 1, 1753 and December 31, 9999 are accepted.

Best Answer

Instead of using the (old) datetime datatype in SQL Server, you can use the (newer and better) datetime2. Among other things, it accepts earlier dates, back to year 1.

A tip is to specify how many digits you was for the second fractions. If you don't do it, you get 7 (as in 12:23:45.2314534). If you can get by with less, then specify for instance datetime2(0) and you get zero decimals. Or whatever you want/need.

Related Solutions

Sql-server – SQL Server Import Export Wizard Error – Server throw Exception

Since you are getting the error when saving and not executing the package, you are using SQL 2014 you are probably being bitten by the new security features introduced in 2012.

Your user's login most likely doesn't have access to the SSISDB catalog (even though he might have access to SQL Server).
You need to map the login to an SSISDB user and configure access to the SSISDB folders/projects in Integration Services.

Please have a look at this MSDN blog post SSIS Catalog Access Control Tips and SQL 2012 SSIS Catalog Permissions

SQL Server – No Search Results Due to Noise Words

Simply deactivating the stop list fixed the problems. There may be better solutions but this worked for me.

Use database; 
ALTER FULLTEXT INDEX ON table
   SET STOPLIST OFF;
GO

Best Answer

Related Solutions

Sql-server – SQL Server Import Export Wizard Error – Server throw Exception

SQL Server – No Search Results Due to Noise Words

Related Question