Incorrect syntax near '-'

Question

Incorrect syntax near '-'

forceman29

SSC Eights!

Points: 942
More actions
April 24, 2013 at 7:42 am

#394663

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MyDoggieJessie SSC-Forever Points: 44484 More actions · Answer 1

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

forceman29 SSC Eights! Points: 942 More actions · Answer 2

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

Query Shepherd SSCrazy Points: 2988 More actions · Answer 3

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

forceman29 SSC Eights! Points: 942 More actions · Answer 4

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.

MyDoggieJessie SSC-Forever Points: 44484 More actions · Answer 5

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

forceman29 SSC Eights! Points: 942 More actions · Answer 6

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....

MyDoggieJessie SSC-Forever Points: 44484 More actions · Answer 7

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