Making A Batch File That Will Start A Active Script In SQL Server Agent Through SQL Enterprise Manag
Jun 29, 2000
Hi again!
I would like to thank the 2 persons which I had an answer from my previous question first. Thanks a lot !
But, it didn't quite answer my question so I'll give a bit more info.
I would like to know if I can make a batch file, that excutes or starts an active script in SQL Server Agent (that is in SQL Enterprise Manager).
If I can, can you give me more info on how to do it (command line, parameters), a web site, anything that can help me to that.
If I cannot do that, let me know please.
Thanks a lot!
Hi! I would first like to thank the person (Ray) who answered my question and also those who read it and tried to find an answer to it.
But, it didn't quite help me much, so I'll ask again; reformulate my question. Here it goes: I would like to know if I can make a batch file, that excutes or starts an active script (in enterprise manager go to SQL Server Group/Management/SQL Server Agent/jobs/Dbl-click on a job/then go to the steps tab and you will see that the type of the job is an active script. Or, try to see if you have an active script job). P.S. It's not a SQL script (*.sql). What I want to do is to start that job from a batch file. If I can, can you give me more info on how to do it (command line, parameters), a web site, anything that can help me do that.
If I cannot do that, let me know please. Thanks a lot!
Hi everyone! I'm new using SQL server (7.0) and I would like to know if it is possible to create a batch file that will automaticly start a job... If so, let me know and give me some piece of information (command, parameters) or a web site, anything that will help me do so... I'll be very grateful. If not, let me know anyways Thanks a lot in advance...
I am try to do a query of dates. I have an access adp front end with sql2000 backend I am trying to query vacation dates. the start date month and day is based on month(hiredate) & day(hiredate) i need to find the number of days taken during a specific period i have to use entering start date and end date and days.
i am trying a query that returns sum of days before the end date changes.
it works in sql analyzer but doesn't when i paste it in EM doesn't want to save it. it says error by if..
heres the code.
SE EMPLOYEE
IF { fn CURDATE() } > (SELECT distinct cast(Year(dbo.[Employment Information].[Hire Date]) as varchar(30)) + '-' + CAST(DATEPART([month], dbo.[Employment Information].[Hire Date]) AS VARCHAR(30)) + '-' + CAST(DATEPART([day], dbo.[Employment Information].[Hire Date]) AS VARCHAR(30)) FROM dbo.[Employment Information] INNER JOIN dbo.[Employee Vacation] ON dbo.[Employment Information].[Employee ID] = dbo.[Employee Vacation].[Employee ID])
BEGIN
SELECT dbo.[Employee Vacation].[Employee ID], SUM(dbo.[Employee Vacation].[Number of Days]) AS [Days Taken] FROM dbo.[Employee Vacation] INNER JOIN dbo.[Employment Information] ON dbo.[Employee Vacation].[Employee ID] = dbo.[Employment Information].[Employee ID] WHERE (dbo.[Employee Vacation].[Start Date] BETWEEN CAST(DATEPART([month], dbo.[Employment Information].[Hire Date]) AS VARCHAR(30)) + '-' + CAST(DATEPART([day], dbo.[Employment Information].[Hire Date]) AS VARCHAR(30)) + '-' + CAST(YEAR({ fn CURDATE() }) AS VARCHAR(30)) AND CAST(DATEPART([month], dbo.[Employment Information].[Hire Date]) AS VARCHAR(30)) + '-' + CAST(DATEPART([day], dbo.[Employment Information].[Hire Date]) AS VARCHAR(30)) + '-' + CAST(YEAR({ fn CURDATE() })+ 1 AS VARCHAR(30))) GROUP BY dbo.[Employee Vacation].[Employee ID] END
ELSE BEGIN
SELECT dbo.[Employee Vacation].[Employee ID], SUM(dbo.[Employee Vacation].[Number of Days]) AS [Days Taken] FROM dbo.[Employee Vacation] INNER JOIN dbo.[Employment Information] ON dbo.[Employee Vacation].[Employee ID] = dbo.[Employment Information].[Employee ID] WHERE (dbo.[Employee Vacation].[Start Date] BETWEEN CAST(DATEPART([month], dbo.[Employment Information].[Hire Date]) AS VARCHAR(30)) + '-' + CAST(DATEPART([day], dbo.[Employment Information].[Hire Date]) AS VARCHAR(30)) + '-' + CAST(YEAR({ fn CURDATE() }) - 1 AS VARCHAR(30)) AND CAST(DATEPART([month], dbo.[Employment Information].[Hire Date]) AS VARCHAR(30)) + '-' + CAST(DATEPART([day], dbo.[Employment Information].[Hire Date])- 1 AS VARCHAR(30)) + '-' + CAST(YEAR({ fn CURDATE() }) AS VARCHAR(30))) GROUP BY dbo.[Employee Vacation].[Employee ID] END
I'm setting up a Maintenance Plan to do a backup for me in the night of my SQL Databases but before the backup starts I have created a SQL Server Agent Job that runs a DOS batch (.bat) file using CmdExec. The problem is I need this process to finish processing before the rest of the tasks run in the Maintenance Plan. It seems like SQL Server Agent Job Task tells the batch file to start running (and it runs fine) but it just continues to the next task and does not wait for it to finish.
Is there any way I can configure this to wait before proceeding?
I've set up transactional replication with queued updating. If I stop the queue reader agent through EM, I can't start it again. Agent's history says "Queue Reader aborting. The step failed."
[I originally posted this in the General Tools forum, but perhaps I'll get better response here...]
I am wondering if this is a bug or a (strange) feature: when a SQL Server Agent job step is a CmdExec job (properly configured for owner/proxy, etc), the step is invoked with the proper user credentials (of the 'Run As' account); HOWEVER, the job step doesn't ever seem to be logged in and thus the step does not pick up the critical local group membership(s) such as BATCH (my preference) or SERVICE, etc. Provided that the command consists solely of built-ins or specifically-permissioned executable invocations (permissioned to the specific 'Run as' account), all is well. However, if the command attempts to invoke general OS exe's (cmd.exe as a sub-shell, whoami, ftp, etc -- anything that is generally permissioned via a local group such as BATCH or SERVICE), such invocations fail with access denied. Specifying a CmdExec step with 'whoami /all' as the command demonstrates the missing group memberships.
(The only workaround seems to be to either crawl through the OS exes and assign permissions, or to grant administrator membership to the proxy credentials account.)
So, design feature or major annoyance?
[Additional info I left out: SS05 (same behavior on SP2 and SP1); on WS03R2-x64; in an Active Directory domain; 'Run as' account granted log on as service, log on as batch, etc.]
I need to take a variable from a tabel in SQL Server pass to a Batch file and execute the batch file. Right now I can exec the batch file with XP_CMDSHELL but how can I pass the variable to the batch file and loop through all the variables.
We are having problems w/ our agent. When we try to start it in computer management, it will say the agent started then stopped because it has nothing to do. In surface area config, when we try to start it it times out. Does anyone have experience with this? We are desperate. Thanks!
I cannot run SQl Server Agent service. Each time I try I get these 2 errors
Event Type:Error Event Source:SQLAgent$MY_INSTANCE Event Category:(2) Event ID:17310 Date:18/06/2007 Time:09:27:49 User:N/A Computer:MY SERVER Description: A user request from the session with SPID 53 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Data:
Event Type:Error Event Source:SQLAgent$MY_INSTANCE Event Category:Service Control Event ID:103 Date:18/06/2007 Time:09:27:49 User:N/A Computer:MY SERVER Description:
SQLServerAgent could not be started (reason: SQLServerAgent cannot start because the msdb database is not set to a compatibility level of 70 or higher).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
I am on SQL Server 2005. Do you have any ideas what might cause it and how to fix it?
I cannot run SQl Server Agent service. Each time I try I get these 2 errors
Event Type: Error Event Source: SQLAgent$MY_INSTANCE Event Category: (2) Event ID: 17310 Date: 18/06/2007 Time: 09:27:49 User: N/A Computer: MY SERVER Description: A user request from the session with SPID 53 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Data:
Event Type: Error Event Source: SQLAgent$MY_INSTANCE Event Category: Service Control Event ID: 103 Date: 18/06/2007 Time: 09:27:49 User: N/A Computer: MY SERVER Description:
SQLServerAgent could not be started (reason: SQLServerAgent cannot start because the msdb database is not set to a compatibility level of 70 or higher).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
I am on SQL Server 2005. Do you have any ideas what might cause it and how to fix it?
I need to start SQL Server Agent so that I can run scheduled jobs. I am running SQL Server 7.0 with windows NT 4.0 service pack 4. When I try to start the agent I get the following error
Error 22003: StartService() returned error 5,'Access is denied'
Does anyone have an idea as to why this is happening? Any help with this problem would be appreciated. Thanks
hi guys i keep getting this error when trying to start the sql server agent service.
"The SQLSERVERAGENT service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service".
the SQL server service starts fine and i tryied starting the agent with the same user but it fails still.
Hello everyone! We had an issue this week where SQL Server Agent did not restart automatically. Weekly maintenance runs on the server each Sunday in which a reboot of the server occurs. Both SQL Server and SQL Server Agent are set to auto-start when OS starts. SQL started fine, but SQL Server Agent didn't.
Anyone know of a reason why SQL Server Agent wouldn't have restarted? Also, does anyone know of a way to set something up that would alert me (through email) if this service hasn't restarted?
I have 2 servers that are members of the same AD Domain.
I need an account that can login to either one, but needs to be able to start a service, which my network admin says a local domain administrator cannot do.
So, I just decided to create an account with the same name, properties and password on both machines.
This I did. The account is a member of local Windows Administrator group on each server. Additionally, it is an SQL account on the SQL Server local instance, and a member of the SysAdmin group.
I can assign this account to SQL Server as the startup account (Log in with this account). That works fine. However, when I assign this account to SQL Server, then SQL Server Agent quits running. So I try to assign this same account to this service and I get an error that the account 'Unknown' cannot login and needs to be a member of the SysAdmin group!??
This is a completely confusing error message since the account is a Windows Admin, SQL Server SysAdmin account and can start SQL Server fine without a hitch.
I am not successful in getting SQL Server Agent to startup when SQL server starts up.
I believe the problem has to do with a lack of permissions or rights. I will list the steps below which seem to me to be all that are needed to install SQL Server Agent. I used Enterprise Manager to get this information.
If anyone can shoot any holes in the process, please do, that may be where my problem lies.
a. Created NT accounts called MSSQLSERVER for the server and MSSQLSERVERAGENT for SQL Agent.
b. Each of these accounts has the "right" to logon as a service.
c. Each of these accounts has "admin" rights.
d. Each of these accounts has been added to SQL SERVER under Security and Logins.
e. The properties assigned to each of the accounts are: (1) on the general tab -- Windows NT authentication is in effect, database is master and language is English (2) on the server roles tab -- System Administrators is checked (3) on the database access tab -- the master database is checked
f. Under SQL Server Agent, properties, selecting the General Tab, in the frame titled "service startup account": (1) the circle labelled "this account" is clicked (2) the account is filled in using domain namemssqlserveragent (3) the correct password is filled in taking case into consideration
g. Under SQL Server Agent, properties, selecting the Connection Tab, in the frame titled "sql server connection": (1) the circle labelled "use windows NT authentication" is clicked
h. Right clicking the server and selecting properties show a multi-tab window (1) the general tab shows that all three autostart boxes are checked (2) the security tabs shows SQL Server and Windows authentication are in effect (3) the startup service account shows "this account" selected and the account is filled in with domain namemssqlserver and the password is filled in (taking case into consideration)
In all cases above, the domain name is not the domain in which the server resides, but is in a "trusted" domain.
Two NT accounts have been created on the server. MSSQLSERVER MSSQLSERVERAGENT
These accounts have been given: a. logon rights b. unchangeable passwords c. passwords which never expire d. NT admin rights
Questions:
a. Do I have to create a login account within SQL Server 7 itself for these accounts? b. Must the properties for "this account' box on the general tab include the domain name? c. If the answer to (b) is yes, then can I use ".MSSQLSERVERAGENT" in the "this account" box on the general tab? d. Does the SQL Server Agent function require the "BUILTINadministrators" login in order to work?
Any help in figuring out why I can't get my SQL Server Agent to startup will be appreciated!!
I have a job in SQL Server agent with 199 SSIS steps and growing. In the job log only 99 steps are shown, which is a problem if a step higher than 99 fails to run. I need the steps to run in sequence and preferable would like to split the job into several jobs each containing no more than 99 steps. The only thing I don't know is if it is possible to have the next job in sequence start only when the previous job has finished.
Does anyone know if this is possible, and if so how it is done?
SQL Server Enterprise Manager could not start the service 'MSDTC' on server'NTAS21'.1722 - (The RPC server is unavailable)I Setup A publication on a server called NTAS4D, then Pushed TheSubscription to another server called NTAS21. If I configure so that thechanges at the subscriber are not replicated to the publisher, all works ok,I make a change at the publisher, it is replicated to the subscriber.If I configure for updating at the subscriber, I am presented with a screenstating that the following services must be started for replication to work:SQL Server Agent on NTAS4D - RunningMSDTC on NTAS4D - RunningMSDTC on NTAS21 - UnknownWhen I complete the wizard, I receive the message listed above.The Servers are in two separate locations, each behind a NAT enabled routerusing the internet to transfer data between the publisher and subscriber.Any Suggestions will be greatly appreciated.ThanksSteve
I am new to SQL server. I just installed SQL Server 2005 enterprise edition .
I have not more experience in SQL but I need some tutorial about how to use SQL Server 2005 and create tables and make queries and relate tables . I can't find any exact location in Microsoft website about SQL Server enterprise edition and documentation where i can find beginner's resources. which feature we use for creating the tables and other features for making the database.
I am new to SQL server. I just installed SQL Server 2005 enterprise edition .
I have not more experience in SQL but I need some tutorial about how to use SQL Server 2005 and create tables and make queries and relate tables . I can't find any exact location in Microsoft website about SQL Server enterprise edition and documentation where i can find beginner's resources. which feature we use for creating the tables and other features for making the database.
I would appreciate any help here at all. I am pulling my hair out!
I am unable to start the snapshot agent or log reader agent from within SQL management studio. it fails on executing step 2 with unknown username or bad password.
I have checked all account details and they are correctly using my domain admin account. I can connect to SQL using teh same account and it also has sysadmin permissions.
If i copy the step 2 paramters and start from the cmd prompt (again logged in using the same domain account) they both start fine.
I am testing peer to peer replication in our environment. I simulated a three node peer to peer topology and a local distributor.
For some wierd reason I cannot get the Log Reader Agent and snapshot agent to start. The domain account under which SQL Server Agent runs has administrator previlage on the box. I also use a domain account for SQL Server Service. (none of the passwords changed).
This is the error I am getting - "Executed as user: abc. A required privilege is not held by the client. The step failed"
Hello, I've configured SQL Server 2000 for merge replication and am trying to start the snapshot agent for the publication and after about 20 minutes of it trying to start, it gives up with this error.
Server execution failed
There is no more information in the event viewer. I've tried it with a bunch of different subscriptions. SQL Server and SQL Agent are running under a local account which has access to the replcation data directory. SQL Agent is running.
I am using the following batch file to execute a script that creates a db and all its objects in the local sql express:
sqlcmd -S (local)SQLExpress -i C:CreateDB.sql
This works fine, but I'm wondering if there's an easy way to put the script in the batch file, so users don't have to worry about putting the script in the C drive. I tried getting rid of the i parameter and pasting the script from the sql file into the batch file, but it didn't work.
When I am trying to install SQL Enterprise edition, in Server 2003 OS. while installing only it shows an error.
SQL Server Failed to start. For more information, see the SQL server books Online topics, "How to view SQL Server 2005 setup Log files" and "starting SQL server manually".
Had some big problems with my SQL Server 2008 R2 SP1 during the last maintenance running on Windows Server 2008 R2 Enterprise ( upgraded from Standard). I'm getting the following error,
"SQL Server blocked access to procedure 'dbo.sp_sqlagent_has_server_access' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online."
I did a google search and found others with the problem but the resolution isn't working for me. I ran the following commands, heck I ran them multiple times. No errors come back running these commands. But the same error keeps happening, I even restart the service and then the server again.
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Agent XPs', 1; GO RECONFIGURE GO
The msdb got too big for the disk it was on. I had maintenance plans to clear out the all of histories but I didn't think of the mail items. When I finally saw this problem I tried clearing them out using the stored procedures only with it finally failing because the transaction log got full. Since we didn't have a lot of jobs scheduled I decided to just recreate the msdb. So I scheduled a maintenance window, download SQL Server Service Pack 3.
my steps are as follows: ->diff backup of msdb ->stopped the service and started it again with: NET START MSSQLSERVER /T3608 ->detached msdb with: SQLCMD -E -SP-SRVR-SQL-01 -dmaster -Q"EXEC sp_detach_db msdb" ->moved it off the drive ->restarted the service without any startup flags ->recreated msdb with: SQLCMD -E -SP-SRVR-SQL-01 -i"D:Microsoft SQL ->ServerMSSQL10_50.MSSQLSERVERMSSQLInstallinstmsdb.sql" -o"D:Microsoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLInstallinstmsdb62715.out"
I looked at the log file and there were no actual error messages so I applied service pack 3 and restarted server after it installed, was actually prompted to restart. In my tests I was never prompted to restart the server.Now I never started the agent service after recreating the msdb database since I knew it wasn't on the same version. I just went directly to the latest SP. Our applications are running fine but now I cant do scheduled backups.
Hi all, I have the "Northwind" database in my Sql Server Management Studio Express.
In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files: (1) ListColumnValues (MS-DOS Batch File) sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName = "Shippers" -i c:prosseappschapter02ListListColumnVales.sql -o c:prosseappschapter02ColumnValuesOut.rpt (2) ListColumnValues (Microsoft SQL Server Query File) USE $(Northwind) GO SELECT $(CompanyName) FROM $(Shippers) GO When I ran the following SQLcmd: C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat I got the following "ColumnValuesOut.rpt" with error messages:
'Northwind' scripting variable not defined. Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1 Incorrect syntax near '$'. 'CompanyName' scripting variable not defined. 'Shippers' scripting variable not defined. Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1 Incorrect syntax near 'CompanyName'.
I copied these T-SQL statements from a book and I do not know how to correct them. Please help and tell me how to correct these errors.
I work on a product installed on a CD ROM. I would like, if the costumer installs our product for a SQL Server database, to know how, with a batch, starts the necessary SQL Services if they aren't started yet when we run our Java application.
