SQL Query with Variables

[Jersey_Practical_Sho (Jersey Practical Shooter “X X Sonic Loss Xx”)]

I have used the below, and it returns results, obviously I have it between quotes

SELECT TOP (10000) [LogID]
,[ParentLogID]
,[Description]
,[PackageName]
,[PackageGuid]
,[MachineName]
,[ExecutionGuid]
,[SystemIdentifier]
,[LogicalDate]
,[Operator]
,[StartTime]
,[EndTime]
,[Status]
,[FailureTask]
FROM [ssisLogger].[logging].[executionLog] where PackageName in (‘RGLRec_ETL_Master’ , ‘RGLWalker_ETL_Master’) AND StartTime >= ‘2020-06-02’

Now all I want to do is change the date for a variable containing the date.

"SELECT TOP (10000) [LogID]
,[ParentLogID]
,[Description]
,[PackageName]
,[PackageGuid]
,[MachineName]
,[ExecutionGuid]
,[SystemIdentifier]
,[LogicalDate]
,[Operator]
,[StartTime]
,[EndTime]
,[Status]
,[FailureTask]
FROM [ssisLogger].[logging].[executionLog] where PackageName in (‘RGLRec_ETL_Master’ , ‘RGLWalker_ETL_Master’) AND StartTime >= " + “'” + Today.Tostring + “'”

but keeps telling me it needs to end in double quotes, I have done this before but I can’t find my previous work

[supermanPunch (Arpan)]

@Jersey_Practical_Sho Try This :
“SELECT TOP (10000) [LogID]
,[ParentLogID]
,[Description]
,[PackageName]
,[PackageGuid]
,[MachineName]
,[ExecutionGuid]
,[SystemIdentifier]
,[LogicalDate]
,[Operator]
,[StartTime]
,[EndTime]
,[Status]
,[FailureTask]
FROM [ssisLogger].[logging].[executionLog] where PackageName in (‘RGLRec_ETL_Master’ , ‘RGLWalker_ETL_Master’) AND StartTime >= '”+Today.Tostring +”'"

[PrasadGupta]

Hi @Jersey_Practical_Sho,

Instead of placing entire query in the double quotes , place it in txt file and update some unique text where you want to pass the dates. Then read it with read text file in to a string variable and replace the unique text with required date, In future maintenance also easy. Then execute the query.

Thanks

[Jersey_Practical_Sho (Jersey Practical Shooter “X X Sonic Loss Xx”)]

“SELECT TOP (10000) [LogID]
,[ParentLogID]
,[Description]
,[PackageName]
,[PackageGuid]
,[MachineName]
,[ExecutionGuid]
,[SystemIdentifier]
,[LogicalDate]
,[Operator]
,[StartTime]
,[EndTime]
,[Status]
,[FailureTask]
FROM [ssisLogger].[logging].[executionLog] where PackageName in (‘RGLRec_ETL_Master’ , ‘RGLWalker_ETL_Master’) AND StartTime >= '”+Today.Tostring +”’"

[supermanPunch (Arpan)]

@Jersey_Practical_Sho My Bad
“SELECT TOP (10000) [LogID], [ParentLogID], [Description], [PackageName],[PackageGuid], [MachineName], [ExecutionGuid], [SystemIdentifier], [LogicalDate], [Operator], [StartTime], [EndTime], [Status], [FailureTask] FROM [ssisLogger].[logging].[executionLog] where PackageName in (‘RGLRec_ETL_Master’ , ‘RGLWalker_ETL_Master’) AND StartTime >= '”+Today.Tostring +”’"

Try this

[Jersey_Practical_Sho (Jersey Practical Shooter “X X Sonic Loss Xx”)]

@supermanPunch

“SELECT TOP (10000) [LogID], [ParentLogID], [Description], [PackageName],[PackageGuid], [MachineName], [ExecutionGuid], [SystemIdentifier], [LogicalDate], [Operator], [StartTime], [EndTime], [Status], [FailureTask] FROM [ssisLogger].[logging].[executionLog] where PackageName in (‘RGLRec_ETL_Master’ , ‘RGLWalker_ETL_Master’) AND StartTime >= '”+Today.Tostring +”’"

Execute Query: Incorrect syntax near ‘‘’.
Unclosed quotation mark after the character string ‘2020-06-02’’.

[supermanPunch (Arpan)]

@Jersey_Practical_Sho Try This :
“SELECT TOP (10000) [LogID], [ParentLogID], [Description], [PackageName],[PackageGuid], [MachineName], [ExecutionGuid], [SystemIdentifier], [LogicalDate], [Operator], [StartTime], [EndTime], [Status], [FailureTask] FROM [ssisLogger].[logging].[executionLog] where PackageName in (‘RGLRec_ETL_Master’ , ‘RGLWalker_ETL_Master’) AND StartTime >="+Today.Tostring

[Jersey_Practical_Sho (Jersey Practical Shooter “X X Sonic Loss Xx”)]

Thanks Guys!