Code below is our stored procedure that is trying to populate log data into our data warehouse. For the life of me I can't figure out why I'm getting this conversion error. All timestamp columns are datetime. I've tried a myriad of different amounts of quotes, conversions, etc. I believe it is due to the ImportDateTime column but Im not sure. Heavily edited the stored procedure for conciseness.
DECLARE
@SQL NVARCHAR(MAX)
,@SQL1 NVARCHAR(MAX)
,@SQL2 NVARCHAR(MAX)
,@ErrorCode INT
,@checkcount BIGINT
,@rowcounter BIGINT
,@maxrowcount BIGINT
,@minTimeStamp DATETIME2
,@DateCollected DATETIME2
,@ImportDateTime DATETIME
,@AssessorDeploymentTimestamp DATETIME2
,@OrderSystemDeploymentTimestamp DATETIME2
SELECT
@checkcount = 0
,@rowcounter = 1
,@ImportDateTime = GETDATE();
SELECT @SQL = N'
INSERT INTO ' + @ToDatabase + '.dbo.Log
(
ImportDateTime
,ServerSource
,DatabaseSource
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
)
(
SELECT
''' + @ImportDateTime + '''
,''' + @ServerSource + '''
,''' + @DatabaseSource + '''
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM (
select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
from openquery([' + @ServerSource + '],
''select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters] = CONVERT(NVARCHAR(MAX),[Parameters])
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM ' + @FromDatabase + '.dbo.[Log] WITH (NOLOCK)
WHERE URL LIKE ''http://online%'' AND TimeStamp > ''' + @AssessorDeploymentTimestamp + ''' AND TimeStamp > ''' + @DateCollected + ''' '') o
WHERE NOT EXISTS
(SELECT 1
FROM ' + @ToDatabase + '.dbo.Log b
WHERE b.id = o.id
AND CONVERT(DATETIME2, b.TimeStamp) > ''' + @DateCollected + '''
AND b.ServerSource = ''' + @ServerSource + '''
)
) a
)'
Here is the error message output -
Msg 241, Sev 16, State 1, Line 197 : Conversion failed when converting date and/or time from character string. [SQLSTATE 22007]
Msg 0, Sev 16, State 1, Line 94 : SELECT MAX(CONVERT(DATETIME2,TimeStamp)) , NULL , NULL FROM AssessorLogDW.dbo.[Log] WHERE ServerSource = 'AOR-AOSQL01' AND DatabaseSource = '2018Q402' ; [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 111 : 2018-11-08 00:48:12.8830000 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 136 : SELECT min(CONVERT(DATE,TimeStamp)),count(a.id),count(a.id) FROM (SELECT id, TimeStamp = CONVERT(DATE,TimeStamp) FROM openquery([AOR-AOSQL01], 'select Id, TimeStamp = CONVERT(DATE,TimeStamp) FROM [AOR-AOSQL01].[AssessorLog2018Q402].[dbo].[Log] WITH (NOLOCK) WHERE CONVERT(DATE,TimeStamp) > ''2018-11-04 00:48:12.8830000''') ) a [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 167 : @checkcount [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 168 : 1161246 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 169 : @maxrowcount [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 170 : 1161246 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 171 : @minTimeStamp [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 172 : 2018-11-05 00:00:00.0000000 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 178 : 2018-10-26 15:05:43.5400000 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 179 : 2018-10-26 14:37:43.8300000 [SQLSTATE 01000]
Any help is greatly appreciated.
