SQL Query with Variables

Build
, ,
#1

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 :frowning:

1 Like
#2

@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 +”’"

#3

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

1 Like
#4

“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 +”’"

image
image1381×458 30.9 KB

#5

@Jersey_Practical_Sho My Bad :sweat_smile:
“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

#6

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

#7

@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

#8

Thanks Guys!

2 Likes
#9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.