Hi, i have below query that i want to use in the Report. However when i copy paste the below query in data tab where Queries are written i am getting error messages. The same query executes and displays results without errors in SSMS. Please advice what i can do to make the below work for reports -------------- USE [ISS_BF_PORTAL] GO DECLARE @SDOHHQAPP27_BFPORTAL_User_Count_ILM_Validated_6charID INT DECLARE @SDOHHQAPP27_BFPORTAL_Distinct_User_Count_Last_30_Days INT DECLARE @SDOHHQAPP27_BFPORTAL_Distinct_Trends_Viewed_Last_30_Days INT DECLARE @SDOHHQAPP27_BFPORTAL_Distinct_Pages_Viewed_Last_30_Days INT DECLARE @SDOHHQAPP27_ISS_SECURITY_Verify_User_Count_ILM_Validated_6charID INT DECLARE @SDOHHQAPP27_ISS_SECURITY_Report_User_Count_ILM_Validated_6charID INT DECLARE @SDOHHQAPP27_ISS_SECURITY_WTI_User_Count_ILM_Validated_6charID INT DECLARE @chrOutputText CHAR(160) SELECT @SDOHHQAPP27_BFPORTAL_User_Count_ILM_Validated_6charID = COUNT(*) FROM [ISS_BF_PORTAL].[dbo].[EP_SEC_USER] [BF] left outer join [SDOHHQINF01].[MicrosoftIdentityIntegrationServer].[dbo].[ILM_UserDataForApplicationVerification] [ILM] on [ILM].[sAMAccountName] collate database_default = substring([BF].[USER_NAME], 5,6) where [ILM].[moqPersonAdEnabled] = 'TRUE' and (len([BF].[DISABLED])=0 or [BF].[DISABLED] is null) and len([BF].[USER_NAME])=10 and substring([BF].[USER_NAME], 1,4) = 'mog\' and substring([BF].[USER_NAME], 5,1) between 'a' and 'z' and substring([BF].[USER_NAME], 6,1) between 'a' and 'z' and substring([BF].[USER_NAME], 7,1) between 'a' and 'z' and substring([BF].[USER_NAME], 8,1) between '0' and '9' and substring([BF].[USER_NAME], 9,1) between '0' and '9' and substring([BF].[USER_NAME],10,1) between '0' and '9' ; SELECT @SDOHHQAPP27_BFPORTAL_Distinct_User_Count_Last_30_Days = COUNT(*) FROM [dbo].[EP_SEC_USER] where [LAST_LOGIN] > (GETDATE())-30; --SELECT @SDOHHQAPP27_BFPORTAL_Distinct_Trends_Viewed_Last_30_Days = COUNT(*) FROM [dbo].[EP_PAGE] WHERE [LAST_VIEWED]> (GETDATE())-30; --SELECT @SDOHHQAPP27_BFPORTAL_Distinct_Pages_Viewed_Last_30_Days = COUNT(*) FROM [dbo].[EP_TREND] WHERE [LAST_VIEWED]> (GETDATE())-30; --COUNT VERIFY USERS: SELECT @SDOHHQAPP27_ISS_SECURITY_Verify_User_Count_ILM_Validated_6charID = COUNT(*) FROM [ISS_SECURITY].[dbo].[MOQ_USER_VERIFY] [BF] left outer join [SDOHHQINF01].[MicrosoftIdentityIntegrationServer].[dbo].[ILM_UserDataForApplicationVerification] [ILM] on [ILM].[sAMAccountName] collate database_default = substring([BF].[NAME], 5,6) where [ILM].[moqPersonAdEnabled] = 'TRUE' and (len([BF].[DISABLED])=0 or [BF].[DISABLED] is null) and len([BF].[NAME])=10 and substring([BF].[NAME], 1,4) = 'mog\' and substring([BF].[NAME], 5,1) between 'a' and 'z' and substring([BF].[NAME], 6,1) between 'a' and 'z' and substring([BF].[NAME], 7,1) between 'a' and 'z' and substring([BF].[NAME], 8,1) between '0' and '9' and substring([BF].[NAME], 9,1) between '0' and '9' and substring([BF].[NAME],10,1) between '0' and '9'; --COUNT REPORT USERS: SELECT @SDOHHQAPP27_ISS_SECURITY_Report_User_Count_ILM_Validated_6charID = COUNT(*) FROM [ISS_SECURITY].[dbo].[MOQ_USER_REPORT] [BF] left outer join [SDOHHQINF01].[MicrosoftIdentityIntegrationServer].[dbo].[ILM_UserDataForApplicationVerification] [ILM] on [ILM].[sAMAccountName] collate database_default = substring([BF].[NAME], 5,6) where [ILM].[moqPersonAdEnabled] = 'TRUE' and (len([BF].[DISABLED])=0 or [BF].[DISABLED] is null) and len([BF].[NAME])=10 and substring([BF].[NAME], 1,4) = 'mog\' and substring([BF].[NAME], 5,1) between 'a' and 'z' and substring([BF].[NAME], 6,1) between 'a' and 'z' and substring([BF].[NAME], 7,1) between 'a' and 'z' and substring([BF].[NAME], 8,1) between '0' and '9' and substring([BF].[NAME], 9,1) between '0' and '9' and substring([BF].[NAME],10,1) between '0' and '9'; --COUNT WellTestInterface USERS: SELECT @SDOHHQAPP27_ISS_SECURITY_WTI_User_Count_ILM_Validated_6charID = COUNT(*) FROM [ISS_SECURITY].[dbo].[MOQ_USER_WellTestInterface] [BF] left outer join [SDOHHQINF01].[MicrosoftIdentityIntegrationServer].[dbo].[ILM_UserDataForApplicationVerification] [ILM] on [ILM].[sAMAccountName] collate database_default = substring([BF].[NAME], 5,6) where [ILM].[moqPersonAdEnabled] = 'TRUE' and (len([BF].[DISABLED])=0 or [BF].[DISABLED] is null) and len([BF].[NAME])=10 and substring([BF].[NAME], 1,4) = 'mog\' and substring([BF].[NAME], 5,1) between 'a' and 'z' and substring([BF].[NAME], 6,1) between 'a' and 'z' and substring([BF].[NAME], 7,1) between 'a' and 'z' and substring([BF].[NAME], 8,1) between '0' and '9' and substring([BF].[NAME], 9,1) between '0' and '9' and substring([BF].[NAME],10,1) between '0' and '9'; SELECT @chrOutputText = ' SDOHHQAPP27 ' + ' ' + CONVERT(VARCHAR,@SDOHHQAPP27_BFPORTAL_User_Count_ILM_Validated_6charID) + ' ' + CONVERT(VARCHAR,@SDOHHQAPP27_BFPORTAL_Distinct_User_Count_Last_30_Days) + ' ' + CONVERT(VARCHAR,@SDOHHQAPP27_ISS_SECURITY_Verify_User_Count_ILM_Validated_6charID) + ' ' + CONVERT(VARCHAR,@SDOHHQAPP27_ISS_SECURITY_Report_User_Count_ILM_Validated_6charID) + ' ' + CONVERT(VARCHAR,@SDOHHQAPP27_ISS_SECURITY_WTI_User_Count_ILM_Validated_6charID) PRINT 'BabelFish Onshore - User Statistics ' PRINT ' ' PRINT ' SERVER Portal Users (all) last 30 days Verify Users Report Users WellTestInterface' PRINT ' ----------- ------------------ ------------------ ------------------ ------------------ ------------------' PRINT @chrOutputText PRINT ' ' PRINT '* All users ILM validated. Excludes admin users ' GO ------ERROR MESSAGE in BIDS---------- An error occured while executing the query. Incorrect Syntax near 'GO' Incorrect Syntax near 'GO'

Hi RubSay, Please check / clarify the following: - You shouldn't set the database context - USE [ISS_BF_PORTAL] - not needed; you are already setting the database context while defining your data source. - You shouldn't use the GO keyword from SSRS. As per the microsoft documentation - "Applications based on the ODBC or OLE DB APIs receive a syntax error if they try to execute a GO command. The SQL Server utilities never send a GO command to the server." - You shouldn't PRINT your results for debugging within your Query editor. You should use the SSMS query editor for all debugging purposes. Also, just a suggestion: you should encapsulate your lengthy code in stored procedures. It would be better from a performance, maintainability viewpoint. This should work for you if you fix the above listed. The following link would also be a good read: http://www.sqlservercentral.com/Forums/Topic548617-1063-1.aspx HTH Please mark correct answers :)

Hi RubSay, Please check / clarify the following: - You shouldn't set the database context - USE [ISS_BF_PORTAL] - not needed; you are already setting the database context while defining your data source. - You shouldn't use the GO keyword from SSRS. As per the microsoft documentation - "Applications based on the ODBC or OLE DB APIs receive a syntax error if they try to execute a GO command. The SQL Server utilities never send a GO command to the server." - You shouldn't PRINT your results for debugging within your Query editor. You should use the SSMS query editor for all debugging purposes. Also, just a suggestion: you should encapsulate your lengthy code in stored procedures. It would be better from a performance, maintainability viewpoint. This should work for you if you fix the above listed. The following link would also be a good read: http://www.sqlservercentral.com/Forums/Topic548617-1063-1.aspx HTH Please mark correct answers :)