InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 23:28:26 GMT20http://www.sqlservercentral.com/Forums/Topic1402245-148-1.aspxThanks guys....the single quote works!!!!!!!!! So glad:) Love this forum!:-DBtw guys i am wrapping up my work after this. I am asked to prepare for a rerun scenario. I have thought of what to do but i am not sure how to make use of package variable in SQL statements inside the Execute SQL task. For now my plan would be, having a batch file to pass in a current DATE parameter to my package variable called ExecDate. Then i would like to use this ExecDate inside my Execute SQL task such as: Delete * from Table where CreationDate = @ExecDate.how can this be done in Execute Sql Task? Btw thanks for suggesting stored procedures, i know that's a better approach, i definitely agree but for this project i am kinda restraint from using stored procedure due to some security issue.Thu, 03 Jan 2013 18:23:53 GMT10e5xhttp://www.sqlservercentral.com/Forums/Topic1402245-148-1.aspxFor an even better solution stop using dynamic pass through sql and do this with a stored proc. The advantages are numerous. You can avoid sql injection, debugging is a lot less difficult, changes to the data logic does not require a recompile and deployment of the software, the list goes on and on.Thu, 03 Jan 2013 07:29:26 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1402245-148-1.aspxLook at line 15, you are using two variables to represent your filter dates, but the string representation doesn't include the single quotes ' ' around the date. So you're putting together your SELECT clause without single quotes around the date variables. Try including the single quotes before and after each variable as part of the strings and see if that does the trick.Your error message is simply due to SQL Server not being able to parse the line as 'single quotes' are required around values like these.EDIT: Sorry davidandrews13, didn't spot your post above. Ditto that.Thu, 03 Jan 2013 06:39:10 GMTderek.colleyhttp://www.sqlservercentral.com/Forums/Topic1402245-148-1.aspxdo you have to put quotes round your dates?instead of[code="sql"]where EXIT_DT >= " & startFilterDate & " and[/code]you do[code="sql"]where EXIT_DT >= ' " & startFilterDate & " ' and[/code]Thu, 03 Jan 2013 05:32:17 GMTdavidandrews13http://www.sqlservercentral.com/Forums/Topic1402245-148-1.aspx[quote][b]10e5x (1/3/2013)[/b][hr]Oh i tried using msgbox to see whats wrong before posting here. MsgBox doesnt really helps pointing out where is the problem.[/quote]Why not? If you put this in your code:[code="other"]MsgBox("SELECT Count(*) from RPT_TACS_ACCESS_DTL where EXIT_DT >= " & startFilterDate & " and EXIT_DT <= " & endFilterDate & " and WORKED_HRS >= (select VALUE from TERMACCESS_CONFIG where MODULE = 'SSIS TACSReport' and [KEY]= 'Var_WorkHrs_Limit') +':00'")[/code]how does that not help? If it really doesn't, you'll need to do your own research to find out how to display the code some other way.JohnThu, 03 Jan 2013 03:22:37 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1402245-148-1.aspx[quote][b]John Mitchell-245523 (1/3/2013)[/b][hr]That's more a VB question than a SQL Server question, and outside my area of expertise. You could try using MsgBox.John[/quote]Oh i tried using msgbox to see whats wrong before posting here. MsgBox doesnt really helps pointing out where is the problem.Thu, 03 Jan 2013 03:01:45 GMT10e5xhttp://www.sqlservercentral.com/Forums/Topic1402245-148-1.aspx[quote][b]John Mitchell-245523 (1/3/2013)[/b][hr] What is the "+':00'" at the end for? If you're trying to append ":00" on to the end of a number, you need to do something like this:[code="sql"]SELECT CAST(COUNT(*) AS varchar(6)) + ':00'...[/code]John[/quote]Thanks for helping. The "+':00'" is to add :00 to my variable to make the query works. For example after using sub query if the Var_WorkHrs_Limit is 07, then it will query WORKED_HRS >= 07:00 I have been using this way for another similar method and no problem for the script. I do not think that is the cause.Thu, 03 Jan 2013 02:55:55 GMT10e5xhttp://www.sqlservercentral.com/Forums/Topic1402245-148-1.aspxThat's more a VB question than a SQL Server question, and outside my area of expertise. You could try using MsgBox.JohnThu, 03 Jan 2013 02:54:10 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1402245-148-1.aspx[quote][b]John Mitchell-245523 (1/3/2013)[/b][hr] Perphaps you could put a line in your code to print the command string before it's executed? John[/quote]How do i do this?Thu, 03 Jan 2013 02:51:41 GMT10e5xhttp://www.sqlservercentral.com/Forums/Topic1402245-148-1.aspxLooks like it's to do with the way the command string is being built. Perphaps you could put a line in your code to print the command string before it's executed? What is the "+':00'" at the end for? If you're trying to append ":00" on to the end of a number, you need to do something like this:[code="sql"]SELECT CAST(COUNT(*) AS varchar(6)) + ':00'...[/code]JohnThu, 03 Jan 2013 02:40:45 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1402245-148-1.aspxHi all,I have been trying to resolve this for hours but failed, hence have to post here. I have this vbscript:[code="vb"]Private Function CheckNeedSendMail() As Integer Dim startFilterDate As DateTime Dim endFilterDate As DateTime endFilterDate = CType(Dts.Variables("StgEndDate").Value, DateTime) startFilterDate = endFilterDate.AddHours(-25) Dim strStart As String = startFilterDate.ToString() Dim strEnd As String = endFilterDate.ToString() Dim sqlConnStr As String = "Data Source=" & RdVarStr("DB_Server_Name") & ";Initial Catalog=" & RdVarStr("DB_Init_Cat") & ";Integrated Security=True;" Dim rowCounter As Integer Using sqlConn As New Data.SqlClient.SqlConnection(sqlConnStr) sqlConn.Open() Using sqlCmd As New Data.SqlClient.SqlCommand("SELECT Count(*) from RPT_TACS_ACCESS_DTL where EXIT_DT >= " & startFilterDate & " and EXIT_DT <= " & endFilterDate & " and WORKED_HRS >= (select VALUE from TERMACCESS_CONFIG where MODULE = 'SSIS TACSReport' and [KEY]= 'Var_WorkHrs_Limit') +':00'", sqlConn) Dim sqlDtReader As Data.SqlClient.SqlDataReader sqlDtReader = sqlCmd.ExecuteReader() While sqlDtReader.Read() rowCounter = sqlDtReader.GetInt32(0) End While sqlDtReader.Close() End Using End Using Return rowCounter End Function[/code]I keep hitting incorrect syntax near '7' and '+' when i dont even have this two value.... what went wrong?????Thu, 03 Jan 2013 02:24:12 GMT10e5x