Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Incorrect syntax near '-' Expand / Collapse
Author
Message
Posted Wednesday, April 24, 2013 7:42 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:30 PM
Points: 18, Visits: 144
I am receiving this following error on step 1 on a job that I am trying to run.

LoadAllDatabaseInfoAccrossServer
Duration 00:00:00
Sql Severity 15
Sql Message ID 102
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

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.
Post #1445972
Posted Wednesday, April 24, 2013 7:47 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:18 PM
Points: 3,961, Visits: 7,170
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
Posted Wednesday, April 24, 2013 8:07 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:30 PM
Points: 18, Visits: 144
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]
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
Post #1446000
Posted Wednesday, April 24, 2013 8:34 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 1:52 AM
Points: 120, Visits: 368
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 ?

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
SQL Pizza



sometimes you dont see the pizza for the toppings...
seek and ya shall find...
Post #1446018
Posted Wednesday, April 24, 2013 8:45 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:30 PM
Points: 18, Visits: 144
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.
Post #1446030
Posted Wednesday, April 24, 2013 9:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:18 PM
Points: 3,961, Visits: 7,170
Instead of EXEC (@SQL) , please DO a PRINT and then post the results so we can see the output

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1446053
Posted Wednesday, April 24, 2013 10:08 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:30 PM
Points: 18, Visits: 144
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....
Post #1446097
Posted Wednesday, April 24, 2013 10:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:18 PM
Points: 3,961, Visits: 7,170
Are you able to print the results of the print here?

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1446102
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse