Incorrect Syntax Near Sqlstate 42000 Error 102
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might sql server error code 102 have Meta Discuss the workings and policies of this site About
Sql Error 102 Incorrect Syntax Near
Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting
Sql State S0001 Error Code 102
ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7
Sql Error 42000 Incorrect Syntax Near
million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up SQL Job running a few select statements Incorrect syntax near '\'. [SQLSTATE 42000] (Error 102) up vote 0 down vote favorite I have a SQL job that contains a few select statements (4 views and 1 text). EXEC db error code 102 msdb.dbo.sp_send_dbmail @profile_name = 'Database Email', @recipients = 'dbcheck@xxx.com', @subject = 'SQL Replication Deletion Check', @query = 'SELECT '\\server1\folder1\SQL\check.sql' AS [Script] SELECT * FROM [repserv].[dbo].[1repdupecheck] SELECT * FROM [repserv].[dbo].[2repdupecheck] SELECT * FROM [repserv].[dbo].[3repdupecheck] SELECT * FROM [repserv].[dbo].[4repdupecheck]', @attach_query_result_as_file = 1; I currently get the following error when running it. Message Executed as user: SERVER1\sqlusr. Incorrect syntax near '\'. [SQLSTATE 42000] (Error 102). The step failed. Now I have also tried taking out the line with the '\' in and it works so I can only assume a bracket or something needs to be put around the back slashes in the select text. sql syntax-error sql-job share|improve this question asked Apr 3 '14 at 13:34 Matt 7,549154482 add a comment| 2 Answers 2 active oldest votes up vote 4 down vote accepted You need to escape the backslashes and quotes 'SELECT ''\\\\server1\\folder1\\SQL\\check.sql'' AS [Script] share|improve this answer answered Apr 3 '14 at 13:35 juergen d 126k21132197 Thanks man! Pretty much got it, didn
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about incorrect syntax near '@p0' hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges incorrect syntax near '@errno' Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. incorrect syntax near ' '. in sql server 2012 Join them; it only takes a minute: Sign up T-SQL Job fail with Incorrect syntax near ''. [SQLSTATE 42000] (Error 102) up vote 1 down vote favorite Just wondering if some can help me figure out why this http://stackoverflow.com/questions/22839410/sql-job-running-a-few-select-statements-incorrect-syntax-near-sqlstate-420 T-SQL script doesn't work. My experience in SQL is novice at best. DELETE FROM ActiveUsers WHERE HostName + CAST(HostId AS CHAR) IN (SELECT HostName + CAST(HostId AS CHAR) FROM ActiveUsers ACTUSR WHERE NOT EXISTS (SELECT NULL) FROM master.dbo.sysprocesses SYSPRC WHERE SYSPRC.HostName = ACTUSR.HostName COLLATE database_default AND SYSPRC.HostProcess = ACTUSR.HostId GROUP BY SYSPRC.HostName, SYSPRC.HostProcess)) The error message is: Executed as user: . Incorrect syntax near ' '. [SQLSTATE 42000] (Error 102) Incorrect syntax near http://stackoverflow.com/questions/31088530/t-sql-job-fail-with-incorrect-syntax-near-sqlstate-42000-error-102 ' '. [SQLSTATE 42000] (Error 102). The step failed. sql sql-server share|improve this question edited Jun 27 '15 at 12:10 Rüdiger Herrmann 8,539102137 asked Jun 27 '15 at 12:03 Sasanka .S 113 I have updated my answer, please have a look. –Padhraic Jun 27 '15 at 14:12 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote It looks like you have an extra closing bracket in the line WHERE NOT EXITS (SELECT NULL) Update: Ok, you have said that you removed the bracket after the select null in that case the following 2 points also apply The GROUP BY clause in the subquery is redundant and can be safely removed since you have no aggregate function and you are just checking if results NOT EXISTS. Also one of the two sub queries can be removed which leaves you with the query below. This will delete records from ActiveUsers table where the HostName and HostId are not found in the master.dbo.sysprocesses table. Try this, I have tested it and it worked fine for me. DELETE FROM ActiveUsers WHERE NOT EXISTS (SELECT NULL FROM master.dbo.sysprocesses SYSPRC WHERE SYSPRC.HostName = ActiveUsers.HostName COLLATE database_default AND SYSPRC.HostProcess = ActiveUsers.HostId) share|improve this answer edited Jul 12 at 0:27 answered Jun 27 '15 at 12:30 Padhraic 653415 Thanks, Just tried with out it but still give the same error –Sasanka
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 2008 » SQL Server 2008 Administration » Incorrect syntax near http://www.sqlservercentral.com/Forums/Topic1445972-1550-1.aspx '-' Incorrect syntax near '-' Rate Topic Display Mode Topic Options Author Message forceman29forceman29 https://social.msdn.microsoft.com/Forums/sqlserver/en-US/377d021c-33c4-4040-ad15-58ab9ef9a4c8/issue-when-running-a-sql-statement-in-a-job-setup-through-sql-server-2005-management-studio?forum=sqltools Posted Wednesday, April 24, 2013 7:42 AM Grasshopper Group: General Forum Members Last Login: Thursday, February 18, 2016 10:27 AM Points: 20, Visits: 162 I am receiving this following error on step 1 on a job that I am trying to run. LoadAllDatabaseInfoAccrossServerDuration 00:00:00Sql Severity 15Sql Message ID 102Operator Emailed Operator Net sent Operator Paged Retries incorrect syntax Attempted 0MessageExecuted as user: Domain\AccountA. Incorrect syntax near '-'. [SQLSTATE 42000] (Error 102). The step failed.The sql server service's are setup with SQL domain accounts and I have a secure link created between ServerA and ServerB. When I run each SP manually they run without any error but when ran from a sql job I receive the above error. Is there something i'm missing in the linked server? Here is the incorrect syntax near code I usedEXEC master.dbo.sp_addlinkedsrvlogin --@rmtsrvname = N'ServerB', --@locallogin = NULL , --@useself = N'False', --@rmtuser = N'UserB', --@rmtpassword = N'password'--GoThe server I'm running the job on is SQL 2008 R2 and ServerB is SQL Server 2005. Post #1445972 MyDoggieJessieMyDoggieJessie Posted Wednesday, April 24, 2013 7:47 AM SSCarpal Tunnel Group: General Forum Members Last Login: Yesterday @ 8:25 PM Points: 4,754, Visits: 7,338 Is ServerA and ServerB that actual names of the servers?Linked servers cannot be referenced with special characters in them unless they are enclosed in [brackets] - Ex: SELECT * FROM OPENQUERY([SERVER-A], 'SELECT TOP 1 * FROM ThatTable WHERE This = That') ______________________________________________________________________________"Never argue with an idiot; They'll drag you down to their level and beat you with experience" Post #1445979 forceman29forceman29 Posted Wednesday, April 24, 2013 8:07 AM Grasshopper Group: General Forum Members Last Login: Thursday, February 18, 2016 10:27 AM Points: 20, Visits: 162 ServerA and ServerB were just place holders I put in for this post. Call me paranoid :P Here is the SP without my place holders. USE [DBA]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc [dbo].[usp_LoadAllDatabaseInfoAccrossServers]asDECLARE @Server varchar(100)DECLARE @SQL Varchar(5000)DELETE FROM DBA.dbo.DB_InfoALL DECLARE c1 CURSOR READ_ONLYFORselect ServerName from MaintMonitoring WHERE Enabled = 1OPEN c1FETCH NEXT FROM c1INTO @ServerWHILE @@FETCH_STATUS = 0 BEGIN Set @SQL = '
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: Issue when running a sql statement in a 'job' setup through SQL Server 2005 Management Studio SQL Server > SQL Server Tools Question 0 Sign in to vote I am attempting to do a simple table copy from one DB to another on the same server using a job in Server Management Studio on SQL 2005. The command works when run under the Query window, however, when I input it into a job I receive the following error message in the job history: Date 9/30/2008 11:39:19 AMLog Job History (a) Step ID 1Server LONGHORNJob Name aStep Name fDuration 00:00:00Sql Severity 15Sql Message ID 102Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 MessageExecuted as user: DENVERARTMUSEUM\spdb_service. Incorrect syntax near 'Serenic_DAM Holdings Test, LLC$G_L Entry'. [SQLSTATE 42000] (Error 102) Incorrect syntax near 'Serenic_DAM Holdings Test, LLC$G_L Entry'. [SQLSTATE 42000] (Error 102) Incorrect syntax near 'Serenic_H Street Test, LLC$G_L Entry'. [SQLSTATE 42000] (Error 102) Incorrect syntax near 'Serenic_DAM Live Test$G_L Entry'. [SQLSTATE 42000] (Error 102) Incorrect syntax near 'Serenic_DAM Holdings Test, LLC$G_L Account'. [SQLSTATE 42000] (Error 102) Incorrect syntax near 'Serenic_H Street Test, LLC$G_L Account'. [SQLSTATE 42000] (Error 102) Incorrect syntax near 'Serenic_DAM Live Test$G_L Account'. [SQLSTATE 42000] (Error 102). The step failed. The sql statement I am using is below: Select * into Operational_Data_Store.dbo."Serenic_DAM Holdings Test,