Incorrect syntax near '-'
-
I am receiving this following error on step 1 on a job that I am trying to run.
LoadAllDatabaseInfoAccrossServer
Duration00:00:00
Sql Severity15
Sql Message ID102
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed 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 code I used
EXEC master.dbo.sp_addlinkedsrvlogin
--@rmtsrvname = N'ServerB',
--@locallogin = NULL ,
--@useself = N'False',
--@rmtuser = N'UserB',
--@rmtpassword = N'password'
--Go
The server I'm running the job on is SQL 2008 R2 and ServerB is SQL Server 2005.
-
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; Theyll drag you down to their level and beat you with experience
-
ServerA and ServerB were just place holders I put in for this post.
Call me paranoid
Here is the SP without my place holders.
USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[usp_LoadAllDatabaseInfoAccrossServers]
as
DECLARE @server varchar(100)
DECLARE @sql Varchar(5000)
DELETE FROM DBA.dbo.DB_InfoALL
DECLARE c1 CURSOR READ_ONLY
FOR
select ServerName from MaintMonitoring WHERE Enabled = 1
OPEN c1
FETCH NEXT FROM c1
INTO @server
WHILE @@FETCH_STATUS = 0
BEGIN
Set @sql = ' INSERT INTO MadSQL08.DBA.dbo.DB_InfoALL ' +
' SELECT * from [ ' + @server + ' ].DBA.dbo.DB_Info '
EXEC (@SQL)
FETCH NEXT FROM c1
INTO @server
END
CLOSE c1
DEALLOCATE c1
-
forceman29 (4/24/2013)
EXEC master.dbo.sp_addlinkedsrvlogin--@rmtsrvname = N'ServerB',
--@locallogin = NULL ,
--@useself = N'False',
--@rmtuser = N'UserB',
--@rmtpassword = N'password'
--Go
Just uncomment it and run it again :hehe: ?
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'ServerB',
@locallogin = NULL ,
@useself = N'False',
@rmtuser = N'UserB',
@rmtpassword = N'password'
Go
Hope I'm not missing the point...
Greetz
Query Shepherd -
Sorry about the comment lines. I already ran that before (and it succeeded), I just forgot to take them out when i posted to the forums.
I can pull data from ServerB to my Server from within a query without any problems.
What I'm trying to figure out is why i'm getting this error:
Message
Executed as user: Domain\AccountA. Incorrect syntax near '-'. [SQLSTATE 42000] (Error 102). The step failed.
-
Instead of EXEC (@SQL) , please DO a PRINT and then post the results so we can see the output
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
I took out the EXEC and inserted a PRINT.
The SP within the job ran just fine. I then put the exec back and the 1st step for the job run successfully.
...my level of T-SQL programing is limited so not sure what exactly happened here....
-
Are you able to print the results of the print here?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply