EXEC sp_start_job in ASP page
-
Is it possible to run a Job using an asp page? I have tried the following and its is not working
created a job to perform an incremental full-text index on a catalog named "MyCatalog"
Next I assigned a sql user who has both read and write privileges the ability to execute sp_start_job within the msdb on the same server.
I then created an asp page to actually execute the job like so:
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=user1;Password=password1;Initial Catalog=msdb;Data Source=MyServer"
conn.execute("EXEC sp_start_job @job_name = 'INCREMENTAL MyCatalog'")
Even though the catalog appears underneath a separate database in enterprise manager, and not the MSDB, when I run EXEC sp_start_job @job_name = 'INCREMENTAL MyCatalog' in QA it runs just fine
When I run it through the asp page i get:
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
The specified @job_name ('INCREMENTAL-FULL-TEXT-GRIPANET') does not exist.
/Administrators/CareCoordination/Pharmacy/PharmacyLocator/runFullText.asp, line 7
When I change the connection string to point to a different database
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=user1;Password=password1;Initial Catalog=MyDataBase;Data Source=MyServer"
and then change....
conn.execute("USE [msdb] EXEC sp_start_job @job_name = 'INCREMENTAL MyCatalog'")
I get no errors however the Job never runs. The reason I know this, is becase in QA I run a simple statement to test
SELECT * FROM table
WHERE FREETEXT(column, 'value' )
No records are returned, and I also check Enterprise Manager at the last run date for that particular job.
Can anyone help me?
-
Who owns the job? Users cannot see/execute jobs that they don't own.
--------------------
Colt 45 - the original point and click interface
-
In addition to who ever owns the job, the owner needs higher permissions (DBO) than just read and write privileges the ability to execute sp_start_job within the msdb on the same server. They need to be "members of the sysadmin fixed server role and the db_owner (or higher) fixed database roles can execute sp_fulltext_catalog. " (from SQL 2000 BOL title "sp_fulltext_catalog" as your Incremental Population job is actually executing this system stored proc.
Furthermore, and if you're using SQL Server 2000, I'd recommend that you implement "Change Tracking" with "Update Index in Background" as these options elimate the need to have a scheduled Incremental Population, therefore you don't need to worry about creating a job to do this!
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
John T. Kane -
John
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply