I have written a script that needs to run as dbo and is triggered by a 9002 alert and it runs fine on about 500 databases, however I can not get it to run on databases that have a dash in their name (Nor if they have a space).
I am running 8.00.534 SQL Server 2000 SP2 November 30th, 2001 Standard Edition.
To reproduce the problem:
#1 Create a database named "test-dash"
#2 Create a job "testjob" with owner sa
#3 in enterpise manager under job steps click "new"
#4 General tab:
Step Name: testing
Type: TSQL
Database: test-dash
Comand: select 'hello world'
#5 Advance tab:
Run as user: dbo
#6 click ok and click ok
It returns:
Error 170: Line 3: Incorrect syntax near '-', the job was not saved,
Apparently it is dying on line 148 of msdb.dbo.sp_verify_jobstep
EXECUTE(N'DECLARE @.ret INT
SELECT @.ret = COUNT(*)
FROM ' + @.database_name_temp + N'.dbo.sysusers
WHERE (name = N''' + @.database_user_name_temp + N''')
HAVING (COUNT(*) > 0)')
Which should generate:
SELECT COUNT(*)
FROM [test-dash].dbo.sysusers
WHERE (name = N'dbo')
HAVING (COUNT(*) > 0)
But is missing the brackets
Has anyone seen a microsoft patch to fix this?I was able to replicate your problem. I haven't seen a patch for this but I am curious why you set the Run As = dbo when the scripts is running under the sa login?|||Like Paul, I did the test and came down with the same error. However as Paul stated, if the job is owned by sa why set RunAs to dbo. When you remove that option you can save the job.|||Thank you for the replies.
The directions were a "simplified" example to try to find the problem.
I was writing a script to automate the install of the maintenance jobs on 500+ databases.
I am trying to move away from "Mixed Mode" authentication, so actually I was planning to have the jobs owned by the same user that SQL Server is running as which may not have the needed privileges. Can a job be owned by and run as SA when you are using NT Authentication?
The user that a certain application is running as owns many of the databases. I was hoping that running as dbo would simplify the install.
I know I can look at the owner of each database and set that as the owner of the job, but the job might break when the owner of a database is changed.
The script does currently work if I have the jobs all owned and run by my personal account, but I did not think that was a good solution. Occasionally servers get set to deny access to all domain administrators, so I was hoping the scripts would still continue to run when that happens.
Bob S.|||You are headed in the right direction, and yes a job can be owned by and run as SA.
If the NT account used by your SQL Server Agent is alias to SA your life will be much easier. I personnaly have worked for only one company that had a problem with this and we resolved it by switching to a server side authenticated id.|||I got the error on line no 248 (rather than 148) when doing the same in T-SQL, see below.
WHy does it say that "dbo" doesn't exist, when I know it does?
use msdb
EXEC sp_add_job @.job_name = 'testingtsql',
@.owner_login_name = 'sa'
EXEC sp_add_jobstep @.job_name = 'testingtsql',
@.step_name = 'selecthello',
@.subsystem = 'TSQL',
@.command = 'select hello-world',
@.database_name = 'test-dash',
@.database_user_name = 'dbo'
Server: Msg 14261, Level 16, State 1, Procedure sp_verify_job, Line 67
The specified @.name ('testingtsql') already exists.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '-'.
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_jobstep, Line 248
The specified @.database_user_name ('dbo') does not exist.|||Okay, I bet you USD100.00 that you DON'T have a user named 'dbo', but do have a role called db_owner commenly refered to as dbo.
In your job deffinition you do not need to specify @.database_user_name when the job owner is sa, by default the job will run as dbo.|||You're probably right.
Anyway, this bug about the dash-name occurs regardless of user, I guess?
