Has anyone been able to edit a job that they created without having SQL Server Admin access?
I have created a credential that has SQL Server Admin access along with a proxy account. My ID doesn't have admin access but I have Ownership rights to my database. I am able to create a job and execute a job but I cant edit the job once its created.
We are on SQL Server 2005 SP2.
Any Ideas?
Thanks in advance.
I have created a logging test package that I am attempting to execute in SQLAgent.
It uses an environment variable in Package Configurations to set a variable which is the expression value for the ConnectionManager's 'ole_src_Admin' ConnectionString.
The next three Package Configurations use that connection to retrieve SQL Server configuration types/
Using the same account I can execute the package succesfully from cmd line
The execution string is
dtexec /SQL "Parent Package" /SERVER "BLAHSQL-DEV" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
Attempting to execute this via SQLAgent (type CmdExec) I receive the following error:
Date 9/26/2007 9:47:45 AM
Log Job History (Logging Test)
Step ID 3
Server QRISQL-DEV
Job Name Logging Test
Step Name Execute 2005 Package
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 9:47:45 AM
Info: 2007-09-26 09:47:46.28
Code: 0x40016038
Source: Parent Package
Description: The package is attempting to configure from the environment variable "SQL_Parameter_Connect_String".
End Info
Info: 2007-09-26 09:47:46.28
Code: 0x40016040
Source: Parent Package
Description: The package is attempting to configure from SQL Server using the configuration string ""ole_src_Admin";"[dbo].[Parameter]";"sv_ssis_package_store_connectionstring";".
End Info
Error: 2007-09-26 09:47:46.30
Code: 0xC0202009
Source: Parent Package Connection manager "ole_src_Admin"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
End Error
Info: 2007-09-26 09:47:46.30
Code: 0x40016040
Source: Parent Package
Description: The package is attempting to configure from SQL
I have also succeffuly executed this package in the same manner locally using both the cmd line and SQLAgent.
Any clues would be appreciated.
Developers need to view and modify jobs on some servers without being sysadmin. We've added the developers to the following MSDB roles:
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole
Technically they should be able to view and modify jobs through Management Studio when being added only to SQLAgentOperatorRole. However they receive the following error when clicking on the Jobs folder regardless of the msdb role.
Execute Permission Denied on Object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Our original two SQL 2005 servers do not produce this message, but all other instances do. All servers, but one, are SP2 so the service pack is not an issue. Any thoughts?
Thanks, Dave
I have a database with tables that are used for various front-ends, Access 97 and ASP. All of a sudden, my "Stores" table will not let me add, edit, delete, insert records. I checked security permissions everywhere and cannot figure out why this is happening. Is there something linked to this that might be running a process that might be causing this?
TIA,
Bruce
When I change some data in a row in my Results Pane and hit enter, I receive a message that "Data has changed since the Results pane was last retrieved." I click the yes button to "commit your changes to the database anyway," but then I get an error message telling me that my row was not updated. The message says "Error Message: The updated row has changed or been deleted since data was last retrieved. Correct the errors and retry or press ESC to cancel the change(s)." This time, the only option I have is to click the OK button. Can anyone give me some idea as to what I need to do to be able to update the row data in this way? Thanks.
View 11 Replies View RelatedWhen using the 2005 SQL Server Management Studio Express to create a new query, most of the necesary keyboard functions do not work (Return/Enter, Backspace, Delete, etc). The only way to edit in the New Query tab is to select the undesired text and re-type it. The 'Enter' key is non-functioning so all text appears on one line.
A second issue arises when trying to execute a query. I always get an error message stating that the designated database cannot be found in the System Databases group. I have right-clicked on the database (Northwnd) I am querying to create the new query and I have also used the 'USE' statement but I still get the same error message.
Hi,
I'm working with few examples from a 70-315 exam prep book.
I'm trying to view the Table design... edit/ create views from VS. NET environment.
But when i right click on the View node under Data Connection tree i get only "Refresh" & "Properties". According to this book.. i should be able to create new views from here.
I'm working with a SQL Server 2000 local installation. VS .NET is connected to the DB, as i tried other examples and i'm able to read from the DB.
My SQL server installation is a 120 days eval installation... has this got anything to do with the behavior ??
I also reied to establish a connection with SQL server 'sa' login... i could connect, but again i wasnt able to create 'views' access tables design.
Any help would be appreciated.
Thanks.
Dear Listers,
We're using AS sp2.
Just about every action within AM generates the following message:
"DSOInfo: Cannot create temp directory c:program filesOLAP
ServicesBin..Help80metadatametatemp100. Please verify that you have enough disk space, and
they try again."
Has anybody been able to source the reason & subsequently remove the message?
Also, for a particular user, while attempting to edit a cube, who is defined as an OLAP Admin, receives the following message:
"Unable to connect to the 'Data source name' data source.
Does anyone have any ideaes?
TIA
John
Hi!I am able to add records to my SQL 2005 database using bound forms in myAccess application, but if I try to edit any records (not only the newrecords I create) using the same bound forms, I receive a write conflict.For the record, I am currently the only user of this application and my SQLServer database has only one userID. I used this userID to create all ofthe objects in the database and to connect to the database from Access. Thedata sources for the bound forms are linked tables, the record source typeis set to "Dynaset" and the record locks are set to "No Locks". "Opendatabases using record-level locking" is checked.The write error says that another user has changed the current record sinceit was opened (since I'm currently the only user this isn't possible) andasks me to copy my changes to the clipboard or drop the changes.Can anyone suggest what might be causing this? Thanks!
View 1 Replies View RelatedHi,
I am running SQL 2005 Enterprise with SP2. In the past I have been able to successfully create SSIS packages using the BIDS and also Maintenance Plans from within Management Studio.
Today I tried to edit a maintenance plan and got the following error message:-
TITLE: Microsoft SQL Server Management Studio
------------------------------
Retrieving the COM class factory for component with CLSID {0BE35203-8F91-11CE-9DE3-00AA004BB851} failed due to the following error: 80040154. (Microsoft.DataWarehouse)
------------------------------
BUTTONS:
OK
------------------------------
This error also occurs if I try to create a new Maintenance Plan.
I then tried opening an existing SSIS package in BIDS and got the following error:-
------------------------------------------------------------------------------------------
Microsoft Visual Studio is unable to load this document
Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
------------------------------------------------------------------------------------------
These errors only occur on my PC so I know that the problem is with my PC. I have totally uninstalled SQL 2005 components from my PC and reinstalled them with no luck.
To my knowledge, I haven't installed any other programs since creating these packages, although there's a possiblilty my PC may have had some patches applied through SMS.
I am running Windows 2000 Professional.
Can anyone help me resolve this problem?
Thanks.
Martin
Hi,
I think my description says it all. I have just installed Studio Express for the first time.
I want to attach a database. When the connect to server dialog is displayed the user name and password text box is greyed out. The Password box is blank and I need to edit this. I switch to SQL authentication they are enabled but if I switch back to Windows authentication they are both greyed out.
I have uninstalled and reinstalled but still no luck,
Any help would be great.
Hi,
I am testing SSIS and have created a Flat File Destination. I defined the Flat File Connection as New for the first time and it worked fine. Now, I would like to go back and modify the Flat File Connection in the Flat File Destination Editor, but it allows only to create a New connection rather allowing me to edit the existing one. For testing, I can go back and create a new connection, but if my connection had 50-100 columns then it would be an issue to re-create it from scratch.
Did someone else faced this issue?
Thanks,
AQ
Hi:
I hope somebody can give me a clue for this problem. Our SQL Server was installed with default option. The SQL Server account is .SQL-Cmdexec. I tried to create two backup jobs to save daily backup files into another server. The SQL Server could not detect that server drive during the database backup set up. I knew that was because the SQL Agent account could not access to that server. I had the following questions:
1. How to grant write right to current SQL Agent account to another backup server?
2. It looks like .SQL-cmdexec is created by SQL Server default installation. If I try to change this account to another account, the Enterprise Manager is the only place to be changed or there is another place keeps SQL Agent account info.
3. If I try to give SQL Agent account access to the other servers, what is the relationship between NT User manager and SQL Agent accounts.
Thanks.
Joan
Hello,I have the following error message in the sqlagent.out file, loopingeach minute.2005-12-19 10:58:54 - ! [298] SQLServer Error: 14262, The specified@job_id ('254D5C3B-CB1F-4B02-AD79-FF5AFE343E3B') does not exist.[SQLSTATE 42000] (ConnExecuteCachableOp)I restarted the sqlagent and ms-sqlserver service, but did not fix theproblem.ThanksDiane Lavoie
View 5 Replies View RelatedI have two questions:(1)After I stopped SQLAgent, I couldn't start it up, even restart theserver.Why?(2) I may need to reinstall the server, if I do, do I need touninstall? or just override the current one?Thanks!Saiyou
View 1 Replies View RelatedSQL Server name: BOProd
Domain: BODomain
I have an NT User 'SQLAgent' with domain Admin previleges.
SQL server has 'SQLAgent' user with NT authentication and system admin previledges.
I am in process of setting up mail services for sqlmail and sql agent mail.
As a part of this process, when I log on to the server computer as SQLAgent, it doesn't let me access Enterprise manager saying
'Login could not be established to BOProd-Login failed for user 'BODomainSQLAgent'. It does allow me to connect to query analyser by providing 'user name/password'.
Any idea why connection to enterprise manager fails?
The SQLAgent service was modified to login as a domain account. SQLAgent would not start. I made sure that the account was in the Admin group on the server, I setup the account as a System Administrator in SQL. I made sure that the network protocols in Client Network Utiliity and Server Network Utility where the same as other servers using the domain account. The message I'm getting is...
SQLServerAgent could not be started (reason: Unable to connect to server; SQLServerAgent cannot start).
I modified the properties for SQLAgent back to login as local account and I still get the same problem.
Any suggestions would be appreciated.
Sidney Ives
Database Administrator
Sentara Healthcare.
Hi all, I have some trouble with SQLAgent...when I try to schedule a jobthat invokes a script (vbs, java, cmdshell,...), SQLAgent crashes at themoment of scheduled execution. If I try to execute it manually, all worksfine. This installation runs on a cluster Win2003 and the node initiate afailover if I don't delete the job and then restart Agent (I can't start theservice before deleting that job!!!). Event viewer logs something like:"LOG] Exception 5 caught at line 191 of file ..src
efreshr.c.SQLServerAgent initiating self-termination".I've already tried to debug the service but my dump did not help me.Any suggestion?Thankscamau
Hi,
Can someone explain me this error I encountered, is this an application error or script error? How can i resolve
this one?
"The description for Event ID ( 208 ) in Source ( SQLAgent$TSESMSDBS ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: [0230] GET MA_SMSCODE, 0x523D92CBAACD304E88CF720580B18357, Failed, 2007-11-10 02:30:00, The job failed. The Job was invoked by Schedule 2 (MA_SMSCODE - Every 2.30AM). The last step to run was step 5 (NSCSI - MODEL SMS SHORT CODE).."
Hope to hear from anyone as soon as possible.
Thanks.
Russell
All,
I have a SQLAgent job created using isqlw to run the query and output the results to a file on the C drive. However, the job never completes, which is strange since the database is fairly new and when I run the query manually in a isqlw window, it completes and outputs the file in seconds.
Any ideas where I'm failing here?
Thanks in advance,
JB
Hi
Keep getting this message in the Application Event Viewer.
Source: SQLAgent$SHAREPOINT
Category: Alert Engine
Event ID: 324
Description: SQLAgent is not allowed to run.
Can't find any help online. Anyone out there got any ideas?
TIA
SQLAGENT CAN't START
this log:
SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role).
I'm going to be setting up SQLAgent to automatically send notifications on failure. My question is are there any "gotcha's" I might need to no about when doing this in a clustered environment? If the server were to failover, does the agent peform as it should?
Thanks in advance!
If you are transferring to a new server is there a good way to transfer SQLAgent jobs in bulk. Scripting out and in is one way, is there a better way
Jim
how can i start automatically sqlagent if it doesn't on W98 ?
thanks for answer
2000-08-28 15:03:15.06 ods Starting SQL Mail session...
2000-08-28 15:03:15.18 ods Error: 17903, Severity: 18, State: 1
2000-08-28 15:03:15.18 ods MAPI login failure..
2000-08-28 15:03:15.18 ods Error: 17951, Severity: 18, State: 1
2000-08-28 15:03:15.18 ods Failed to start SQL Mail session
You know where to find the MAPI settings in NT?
On my account we have very separate authority between the NT system administrators and the SQL DBAs. Routinely in SQL 6.5 we would remove sa authority from the NT administrators group and add this authority to a NT database administrators group which we were members of using SQL Security manager.
We have tried to apply this same method to our implementation of SQL 7.0. We removed the sysadmin role from the BUILTINAdministrators group and added sysadmin to the DatabaseAdmin NT group which the DBAs and SQLAGENT account are members of. This appeared to work fine until we tried to run our database maintenance plans in SQLAGENT. When sysadmin role is revoked from BUILTINAdministrators the jobs in SQLAGENT using xp_sqlmaint fail with "select permission denied to sysdbmaintplans in database 'msdb' owner 'dbo'"
The SQLAGENT login is part of an NT group that has been granted sysadmin role in SQL Server. This should grant full access to SQLAGENT. What's up? Is the xp_sqlmaint using hardcoded security of some kind? I am confused. Any suggestions? We really need to revoke sysadmin from the BUILTINAdministrators group.
I have several target servers defined, i have a job that needs to run in a serial fashion (ie.. 1 target at at time) if every target kicks off the job at the same time it's going to bog down the disk drives... can this be serialized? or do i need to write a special routine...
View 3 Replies View RelatedHello,
How can i change the location of the Sql Agent errorlog file, sqlagent.out
When i choose Properties from the Sql Agent page, i can see the location and filename, but i am not able to change it.
Is there some starup parm? And how can i apply it?
Any help is appreciated.
Best regards,
hvdtol
Hi all,
Configuration : MSSQL2005 SP2 on Windows 2003 server
I have a strange issue when scheduling SSIS Job.
When I schedule it, I set the owner as the domain administrator. The scheduled job can be run manually without any problem. The scheduled job run automatically at scheduled time wihtout problem.
But , for an unknow reason, after some cycle, the job failed with the following error :
The job failed. The owner () of <name of the job> does not have server access
As you can see, the owner name is blank.
If I look to the job settings : the owner is still the domain administrator.
If I try to run it manually, the job fails with the same error.
now if I just rename the job name from <name of the job> to <name of the job2> then all problems are solved.
If we let the job failing everyday, sometimes, it starts again to work properly.
We have this issue on 2 jobs.
If anyone has faced this issue : thank you for advise.
After you've created an SSIS package or multi-package solution, the next step is usually to deploy it to a production environment and schedule it to run at regular intervals. We took this approach at our company and scheduled the master package as a step in a SqlAgent Job. I import the dtsx packages into the server by connecting to the Integration Services connection and importing them into Stored Packages > MSDB. Next, I create a SqlAgent job via the Database Engine connection under SQL Server Agent > Jobs. I then add a step and select "SQL Server Integration Services Package" under the step Type menu. This brings up a powerful dialog that lets you set your production runtime environment parameters for the connection strings, SSIS package variables, config file path, and logging parameters.
As part of my deployment process, I wanted to run the job one time at the end of the build and check the status before deeming the build a complete success. The script is long running (several minutes) so I had to deal with the issue of polling. I had been recently using the WHILE loop in T-SQL and I also found the Waitfor Delay command. I discovered how to get to the job activity via the sp_help_jobactivity extended stored proc. The source for this proc indicates that you can get to the most recent execution of the job, including the currently running job. If the job is running, the run_status column of the resultset is null. Once the job completes, this flag is 0 for failure and 1 for success. I wanted to query the underlying tables directly so I extracted the bare minimum queries to get at the fields that I needed. I provided timeout variables so you can set your desired polling interval and timeout.
I am not sure whether this while/waitfor loop approach is very CPU-intensive or can cause locking problems. I'm hoping it does not. But it's a convenient way to use T-SQL to test for a SqlAgent job status if your build tool supports running a query against a database, which most of them do. You can always use WMI or a .Net app to achieve the same result. If someone has done this already, perhaps you can post your code sample or a link to it as a reply to this thread.
I'm assuming that other DBAs and developers responsible for production deployment and monitoring will find this script useful. I also wouldn't mind if I scored a couple MVP points . One confusion I had to deal with was that sp_help_jobactivity returns a return code of 0 or 1 with the opposite meaning as the run_status column that is in the result set. It took me a little while to figure this out since possible values for run_status were not well-documented.
Enjoy!
Norm Katz
www.ipconsulting.com
/************************************************************************************************
**
** Script Name: CheckJobStatus.sql
** Description: Checks the status of the last instance of a SqlAgentJob
** Author: Norm Katz
** Date: 11-15-2007
************************************************************************************************/
USE MSDB
GO
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @job_name sysname
DECLARE @jobStatus int
DECLARE @message varchar(1000)
DECLARE @jobid int
DECLARE @jobEndTime varchar(32)
DECLARE @runtime int
DECLARE @timeout int
DECLARE @delay datetime
DECLARE @delayIncr int
DECLARE @session_id int
DECLARE @jobCheckTimeoutOccurred bit
SET @job_name = 'MySqlAgentJobName'
-- initialize @delay using the datetime string, e.g., "00:00:05" for 5 seconds
-- initialize @timeout to seconds you want to wait, e.g. 300 for 5 minutes.
-- initialize @delayIncr to the integer number of seconds for @delay
SET @runtime = 0
SET @timeout = 300
SET @delay = '00:00:05'
SET @delayIncr = 5
SET @jobCheckTimeoutOccurred = 0
SELECT TOP(1) @session_id = session_id FROM msdb.dbo.syssessions ORDER by agent_start_date DESC
exec msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT
print 'Checking status of ' + @job_name
WHILE @jobStatus is null
BEGIN
SELECT @job_id = ja.job_id,
@message = jh.message,
@jobStatus = jh.run_status,
@jobEndTime = stop_execution_date
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE
ja.job_id = @job_id
AND
ja.session_id = @session_id
IF @jobStatus is null
BEGIN
SET @runtime = @runtime + @delayIncr
IF @runtime > @timeout
BEGIN
SET @jobCheckTimeoutOccurred = 1
SET @jobStatus = -1
BREAK
END
Waitfor Delay @delay
END
ELSE
BREAK
END
IF @jobCheckTimeoutOccurred = 1
print 'Check for status of Job ID ' + cast(@job_id as varchar(64)) + ' for ' + @job_name + ' timed out after ' + cast(@timeout as varchar(5)) + ' seconds'
ELSE
BEGIN
print 'Job ID ' + cast(@job_id as varchar(64)) + ' for ' + @job_name + ' completed on ' + @jobEndTime
IF @jobStatus = 1
print 'Job Succeeded'
ELSE
print 'Job Failed with Message: ' + @message
END
SELECT @jobStatus
-- The final select query will return one of three values:
-- -1: Script timed out
-- 0: Job failed
-- 1: Job Succeeded
Help!
I installed Windows Update KB927891 for Windows Server 2003 today. Following the required reboot my three instances of SQL no longer work! Monitoring, BackUpExec and Sophos have stopped working due to SQLAgent being unable to start. Both SQL Server and DTC start successfully.
Event Viewer reports error id 103, unable to connect to server.
Looking at the logs in the SQL folders they show:
! [298] SQLServer Error: 18, SSL Security error [SQLSTATE 08001]
! [298] SQLServer Error: 772, ConnectionOpen (SECDoClientHandShake()). [SQLSTATE 01000]
! [000] Unable to connect to server 'XXXXBKUPEXEC'; SQLServerAgent cannot start
? [098] SQLServerAgent terminated (normally)
Thanks for your help in advance!
Stephen
This sends a success or fail status and length of execution time can it be extended to send error details when it fails
View 1 Replies View Related