Sql Script Error - Urgent Please

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sql Script Error - Urgent Please

Author

Topic

sriksdave
Starting Member

5 Posts

Posted - 2007-03-22 : 01:43:48

[DBNETLIB] Specified SQL Server Not Found

I am trying to execute sql scripts on the sql server 2000 on a remote machine in the network

I get this error when I try to Disable the old sql agent jobs and when I try to Grant Permissions to stored procs for a specific user.

Can any one of you guys check the syntax

Here are the scripts

DISABLE JOB SCRIPT

USE DATABASENAME
GO

DECLARE @JobID BINARY(16)
SELECT @JobID = job_id from msdb.dbo.sysjobs
where (name = 'JOBNAME')
IF (@JobID IS NOT NULL)
BEGIN
EXECUTE msdb.dbo.sp_update_job @job_name = 'JOBNAME', @enabled = 0
END

SELECT @JobID = job_id from msdb.dbo.sysjobs
where (name = 'JOBNAME')
IF (@JobID IS NOT NULL)
BEGIN
EXECUTE msdb.dbo.sp_update_job @job_name = 'JOBNAME', @enabled = 0
END

 GRANT PERMISSIONS SCRIPT 

USE DATABASENAME
GO

GRANT CREATE TABLE TO TABLENAME
GO
GRANT EXECUTE ON STOREDPROC1 TO ROLENAME
GO
GRANT EXECUTE ON STOREDPROC2 TO ROLENAME
GO
GRANT EXECUTE ON STOREDPROC3 TO ROLENAME
GO

Can anyone tell me if there are any errors in the syntax

Thanks

krishnarajeesh
Yak Posting Veteran

67 Posts

Posted - 2007-03-22 : 02:42:59

Hi

Pls see the code below. May be this will solve your problem.

USE DATABASENAME
GO

DECLARE @JobID BINARY(16)
SELECT @JobID = job_id from msdb.dbo.sysjobs
where (name = 'JOBNAME')
IF (@JobID IS NOT NULL)
BEGIN
execute sp_addlinkedserver "10.201.80.222" --(Server IP)
EXECUTE "10.201.80.222".msdb.dbo.sp_update_job @job_name = 'JOBNAME', @enabled = 0
END

Thanks & Best Regards,
Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources