T-SQL – Inserting 1002-01-01 resulting in “the conversion of a varchar data type to a datetime data type resulted in an out-of-range value” error.

I am having issue with setting a date to 1002-01-01 when null in SQL Server because it’s out of range.

To get around it, use the DATEFROMPARTS function (available from SQL Server 2012) and it must use datetime2 data type (datetime won’t work). An example is shown below.

CAST(DATEFROMPARTS(1002, 01, 01) AS DATETIME2)

 

Read Full Post »

SSAS tabular model reprocessing failed with connection errors

I reprocessed an existing tabular model in order to refresh data but it came up with connection related errors as shown below.

I do have permission to the views, cube and everything else but I still can’t reprocess the model. How odd!

The fix is to change the connection manager provider from SQL Server Native Client 11.0 to Microsoft OLE DB Provider for SQL Server.

 

 

 

 

 

Read Full Post »