April 24, 2013 at 7:42 am
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.
April 24, 2013 at 7:47 am
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
April 24, 2013 at 8:07 am
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
April 24, 2013 at 8:34 am
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
April 24, 2013 at 8:45 am
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.
April 24, 2013 at 9:07 am
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
April 24, 2013 at 10:08 am
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....
April 24, 2013 at 10:12 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy