I’m a little bit conffused.
Can anyone tell me why i get sintaxis error on the next script?
declare @schema1 NVARCHAR(100)
set @schema1 = 'ex.:DATABASENAME'
declare @query NVARCHAR(500)
set @query = 'INSERT UsersSessions (UserSessionId, IpAddress, ChannelId, IsEmployee, UserId, ClientFullName, UserAgent, StartDate, ExpirationDate, SessionDuration)
SELECT us.UserSessionId, us.IpAddress, us.ChannelId, CASE WHEN us.ChannelId = 2 THEN 1 ELSE 0 END AS IsEmployee, us.UserId, (u.Name + u.LastName) as ClientFullName, us.UserAgent, us.StartDate, us.ExpirationDate, (us.ExpirationDate-us.StartDate) AS SessionDuration
FROM ' + @schema1 + '.UsersSessions us INNER JOIN ' + @schema1 + '.Users u ON us.UserId = u.UserId WHERE us.UserSessionId NOT IN (SELECT UserSessionId FROM UsersSessions)'
EXEC(@query)
RESULT:
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'Use'.
Completion time: 2021-11-16T11:10:32.6309920-03:00
If i remove the Insert block, i get the script running
declare @schema1 NVARCHAR(100)
set @schema1 = 'ex.:DATABASENAME'
declare @query NVARCHAR(500)
set @query = '
SELECT us.UserSessionId, us.IpAddress, us.ChannelId, CASE WHEN us.ChannelId = 2 THEN 1 ELSE 0 END AS IsEmployee, us.UserId, (u.Name + u.LastName) as ClientFullName, us.UserAgent, us.StartDate, us.ExpirationDate, (us.ExpirationDate-us.StartDate) AS SessionDuration
FROM ' + @schema1 + '.UsersSessions us INNER JOIN ' + @schema1 + '.Users u ON us.UserId = u.UserId WHERE us.UserSessionId NOT IN (SELECT UserSessionId FROM UsersSessions)'
EXEC(@query)
RESULT:
(1 row affected)
Completion time: 2021-11-16T11:11:07.6741062-03:00
I think some char is missing?
Thanks and regards!
>Solution :
Your query variable length is not sufficient, modify it as below. Also, schema name can accept only certain characters, colon is not allowed I guess.
declare @query NVARCHAR(max)