when I run a package from a command window using dtexec, the job immediately says success. DTExec: The package execution returned DTSER_SUCCESS (0). Started: 3:37:41 PM Finished: 3:37:43 PM Elapsed: 2.719 seconds
However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.
I am getting an error while running the XP_CMDSHELL 'NET USER /DOMAIN'. But when I run the same command for sp_cmdshell 'net user' it works fine. It giving me the local users that are available on the my local pc. But now i want the domain users on my network which is not working with the xp_cmdshell..
I am getting the following error when running a command in QueryAnalyzer.Msg 50001, Level 1, State 50001xpsql.cpp: Error 5 from CreateProcess on line 675Here is the command that I am running that generates this errormessage:xp_cmdshell "@ECHO test message > c: empewtemp.txt"The command inside the xp_cmdshell command runs successfully from aWindows command prompt. Simpler commands also fail with the same errormessage. For example:xp_cmdshell 'dir'Can anyone suggest a solution to this problem? I assume this problem isdue to a permissions/security issue. I have given Everyone Full Controlover c: emp.Windows 2000SQL Server 2000Thanks for any help.
I executed xp_cmdshell command. More than 24 hours this process still running. I tried killing this process with enterprise manager and query analyser, both gave me a message saying its successfully killed. But when i do a sp_who, the process still their executing.
how can I kill this process that's running xp_cmdshell
Our system is MS SQL Server v7 and NT 4. We have a stored procedure that exec's xp_cmdshell to run an external program located on the server. When a user who has 'sa' rights runs this stored procedure it works fine. When a 'non-sa' user (via the "BuiltinUsers" NT account) runs it, xp_cmdshell produces the following error:
Msg 50001, Level 1, State 50001 xpsql.c: Error 1385 from LogonUser on line 476
Is there an NT security or SQL Server setting I've overlooked that can be changed to allow non-sa users to xp_cmdshell programs?
n.b. The BuiltinUsers account does already have execute permission on the xp_cmdshell procedure.
Hello everyone, I recently ran across with a request to check if the SQL Server Agent is running on all the servers, I immediately thought about implementing that with SSIS, since I've accomplished such repetitive task before with using SSIS. However, I'm unsure on how to do it, my first approach was creating a script that would create a table with all services running on the machine using the DOS "SC" command, now, I don't think that's going to do what I need, since it'll only give me a list with all the services installed on the machine, but there isn't a way to throw a where clause since I get a list of services and its attributes on a single field (See below).
I was wondering if anyone has ran across such request or if anyone has any ideas or suggestions.
This is what I get on my table in a single field when I run the SC command into a # table.
It is SQL server 7. Scheduled jobs were running fine until the hard drive crasked. Replace the hard drive and restored the dabases. However, all the scheduled jobs cannot run. I created new jobs using database maintenance plan and it still doesn't work. the same error occurs when viewing the Job history.
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
However, if I create a job by right click on the database and select backup database. This job runs fine.
What should I do to make jobs created by the database maintenance plan running again?
Hello, I am having a problem with my sql server agent service not working. I have done some research from earlier postings and still having no luck with getting it up and running.
Problem: "Could not start the SQLSEVERAGENT service on local computer. could be an internal Windows errow or service error.
2006-05-09 14:23:55 - ! [298] SQLServer Error: 6, Specified SQL server not found. [SQLSTATE 08001] 2006-05-09 14:23:55 - ! [298] SQLServer Error: 11001, ConnectionOpen (Connect()). [SQLSTATE 01000] 2006-05-09 14:23:55 - ! [000] Unable to connect to server '\PROBLEMTRACK'; SQLServerAgent cannot start 2006-05-09 14:23:55 - ? [098] SQLServerAgent terminated (normally)
I have reset the password for the 'sa' account and running it and got error: Error 18456: login failed for user Specify a different account to be used. But when I try it only provides me with the 'sa' account.
My primary goal is to run some DMP ion the databases that I have on the server.
I'm testing my IS package in my local PC which extracts data from one SQL Server table to another SQL Server database. In BIDS, it's running without error. I also tried it in cmd with DTExec /f "c:package.dts", it also ran ok. I tried assigning it in SQL Agent as job. with same command DTExec. This time, it ran successfully but got 0 rows. I dunno what went wrong, why suddenly it cannot get data from the source.
Hi everybody. Need help with secuity 1. SQLAgent servive = domainMy_local_admin 2. Job created Ownner: domainSQLDBA step1 exec sp_Who2 step2 Run DTS a)Connect to ANOTHER_SQL_SERVER USING windows authentication b) truncate table xxx
3. Run daily every 1 hr
1. Who will run job, domainMy_local_admin or domainSQLDBA ? 2. What account will be used to connect to ANOTHER_SQL_SERVER in step2
We have a DTS which loads files using FTP to another server. This will be used by app groups and will be run via click of button using sp_start_job,
Somehow the userid running the job gets "not sysadmin to run cmdshell.." Top fix that, we granted it to execute the xp_cmdshell and also created a proxy account for SQL Agent which is an admin on server and sql. However, still we get permission errors like "...The needed permission is missing to run command shell.." Please help . The DTS is being called from SQl Agent job and we don't want an admin ID to be used .
If a job is currently running and I want to disable it so that it does not run as scheduled for the following night, will it disrupt the current process?
I'm new here and hope you will be able to help me.
I have created several SSIS packages with Visual Studio 2005. They all work fine in debug mode. I have been able to make them work with a ODBC connection by using a ADO.NET connection.
Then I exported them to the file system in my SQL Server 2005 database and created a task in SQLAgent to run them.
All the packages using the ODBC connection fail with the following error :
Login failed for user XXX Error : 18456; Severity : 14 , State : 8
This error is a password mismatch.
I tried several database users and checked the passwords multiple times.
It looks like SQL Agent is not able to retrieve the password although it is stocked in both the ODBC connection and the SSIS connection.
I’m trying to write a script that will detect long running agent jobs.
Having looked at this article: http://www.databasejournal.com/features/mssql/article.php/3500276
It appears that agent job job id’s don’t necessarily get stored in the programname of the sysprocesses table. This is true if the agent executes an os command. It also appears that job steps do not get stored in the sysjobhistory until the step is complete so that cannot be used accurately.
Does anyone know of an effective way to find if there are long running jobs other than these methods?
I have a problem when i Want to run the Snapshot Agent in SQL Server 2005. I have done all the tutorial called Replication Data with Mobile Clients and i done the previous tutorial called : Preparing the Server for Replication. But in the Lesson One i can't run the Snapshot Agent but the publicationis good. Can you help me ? This is the error's code :
Date 25/04/2007 10:44:27 Log Job History (NTI-PC-118-AdventureWorks-AdvWorksSalesOrdersMerge-2)
Step ID 2 Server NTI-PC-118 Job Name NTI-PC-118-AdventureWorks-AdvWorksSalesOrdersMerge-2 Step Name Run agent. Duration 00:00:01 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0
Message 2007-04-25 09:44:28.29 -Publisher NTI-PC-118 2007-04-25 09:44:28.29 -PublisherDB AdventureWorks 2007-04-25 09:44:28.29 -Publication AdvWorksSalesOrdersMerge 2007-04-25 09:44:28.29 -ReplicationType 2 2007-04-25 09:44:28.29 -Distributor NTI-PC-118 2007-04-25 09:44:28.29 -DistributorSecurityMode 1 2007-04-25 09:44:28.29 -XJOBID 0x893D7BB9E72044489F0A1C1878663E1D 2007-04-25 09:44:28.29 -------------------------------------- 2007-04-25 09:44:28.29 Connecting to Distributor 'NTI-PC-118' 2007-04-25 09:44:28.43 The replication agent had encountered an exception. 2007-04-25 09:44:28.43 Source: Replication 2007-04-25 09:44:28.43 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException 2007-04-25 09:44:28.43 Exception Message: The remote server "NTI-PC-118" does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers. 2007-04-25 09:44:28.43 Message Code: 14080 2007-04-25 09:44:28.43
I'm trying to configure mirroring with High Availability, Automatic Failover.
I know that all the jobs and maintenance plans need to be copied to the mirror server, and enabled if a mirror database takes over the principal role.
I wonder if it is a good solution to have all agent jobs on the Witness server (no jobs on principal and mirror). And all the jobs select the server where they should run (depending on current role).
One of the advantages of this approach would be that the jobs have to be created only once on the witness.
Will this solution work? What are the downsides of it?
I have a meaty job that currently runs as a set of sql scripts under sql agent and I'm transferring this job to run as a SSIS package. I have managed to set it up so that it runs as a sql job (cheers guys!), and I'm watching the job run now, maxing out the CPU usage as it tries to run 10 different tasks at the same time . great stuff!
However, I've been thinking about how to view the progress of this job/package (the job only has one step; "run SSIS package"). With the current job as single scripts, in SQL agent you can view which step of the job you are at.... is it possible to somehow view which package task you're at (ideally with nice shiny yellow green and red updates like in debugging), or do you have to output each step result into a log file?
Oh, and another question! If i have a checkpoint in a package, and the package falls over, when the job is rerun through SQL agent, will it start at the checkpoint, or run from the start?
if I have some packages (.dtsx) that are depolyed to Server A and I have SQL Agent installed on Server B - if I run a run a package from SQL Agent, does it run on Server A or Server B?
I€™m trying to write a script that will detect long running agent jobs.
Having looked at this article: http://www.databasejournal.com/features/mssql/article.php/3500276
It appears that agent job job id€™s don€™t necessarily get stored in the programname of the sysprocesses table. This is true if the agent executes an os command. It also appears that job steps do not get stored in the sysjobhistory until the step is complete so that cannot be used accurately.
Does anyone know of an effective way to find if there are long running jobs other than these methods?
I've tried finding an answer to this on this website but I don't see any. I'm running SQL Enterprise Manager v8.0 and have a job in the SQL server agent folder that won't run as scheduled nor will it run manually if I Right-Click and choose Start Job.
It's designed to run a DTS. I can run the DTS manually without errors but the job won't run it for me. I will try to provide as many details in this post as I can in hopes that someone who is more experienced at this can help me.
DTS DETAILS The DTS is designed to transfer data from a table on one server and transfer it through a firewall to another server into a table on that server. Then delete the entries from the originating table. This works very well if I run it manually. The purpose of deleting entries from the web server is for security. Once the data in on the other side of the firewall it's safe.
The job however will not run either manually or from it's schedule. I've tried creating a new job and setting up a very simple schedule that runs daily every 2 minutes for example. I've played around with different times but no luck. So I will try and provide you with job details below:
JOB DETAILS Name: TransferData Enabled, Target = Local Server Category: Uncategorized Local Description: Execute package: TransferData
STEPS ID: 1 Step Name: TransferData Type: Operating System Command On Success: Quit with Success On Failure: Quit with Failure
PROPERTIES OF STEP Step Name: TransferData Type: Operating System Command (CmdExec) Command: ( I have no idea what this translates to as I did not write it) DTSRun/~Z0xF6EE76ED6259A60AC370F465DE7F3095F4B37F7176C05B8E72E396DEBE60135E53ACFEDB00E1F216DD8EC8206782439BA435771E3C1E8A7A5D0DAE3F3AAA9F48BF08A9A9FA23C010A85573546F6B6B900323F76DC8A27EACAFD63904464DD5A9B28137
Nothing special in the advanced tab
SCHEDULES TAB ID: 20 Name: TransferData Enabled: No Description: Occurs every 1 day(s), every 2 minute(s) between 12:00:00 AM and 11:59:59 PM and is setup as Recurring Nothing in Notifications Tab
I only have two SQL books to reference. This is a production fire to put out and I really need the help here as I am not a certified SQL guru.
I hope I have provided enough information here for someone to help. I'm happy to provide an email address for easier contact should you request it.
Hi, I ahve attached the bmp doc that will show my screen snapshot. My SQL server Agent service indicator is not showing green as like other server. I have started the serice and running fine. I don't have any problem running the job. My curious is why that indicator is not showing up. Thanks, Ravi
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "'I:MyFolderMyRptsMyApplicationMyDatabase.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
The above path "I:...." is mapped on the server that SQL Server is running on.
This package runs with NO problems in MS Visual Studio.
I have created a stored procedure for a routine task to be performed periodically in my application. Say, i want to execute my stored procedure at 12:00 AM daily.
How can I add my stored procedure to the SQL server agent jobs??
Hi. I was wondering if anyone has ever had a problem where nothing seems to be wrong with the server, and SQL Server Agent is up and running, but jobs fail. We had a job run at midnight, and it was the last successful run. Every job failed after that, with an error stating "Unable to retrieve steps for job..." There doesn't seem to be any reason for the problem. The jobs were kicked off manually and they all ran.
The only other error messages we could find were in the Application Event Viewer on the server. But the first error happened days ago and said this: "The description for Event ID ( 0 ) in Source ( .NET Runtime ) 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: Unable to open shim database version registry key - v2.0.50727.00000."
Since then, every few seconds this error occurred: "Windows cannot load extensible counter DLL MSSQLServerOLAPService, the first DWORD in data section is the Windows error code."
We turned off the performance monitor counters and that error stopped. But we still have no idea if that was the cause of the problem.
This isn't the first time we've had an issue with jobs failing because the steps couldn't be 'found', while SQL Server Agent was up and running.
I was working on some reports on SSRS. Now I cant view those reports coz I can not open report manager. I noticed that SQLSERVER and SQL Server agent has stopped running in my local machince. But Other services are running properly (SSIS, SSRS, SSAS). When I go to SQL Configuration manager and try to run them, I get the following error massege.
" The request failled or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details. "
So now I can not connect database engine in my local machine through management studio. When I try to do that, I get the following error massage.
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)"
Also I can not access reportserver data base and reportservertemDB.
If I reinstall SQL Server again, what would happen? Would I still be able to use and view my old reports? Please can anyone help me to get this worked again correctly?
I have a set of packages, which I need to run through Sql Agent to automate it. This package runs Ok when I use DtexecUI but when I run it through SQl Agent with the same options it fails.
THe error it fails on is the old 0xC0202009, acquire connection failed error. The component on which it fails is an access database with the location and query fed by variables that pick up this from a table in a sql server database. THe access database is on a shared drive on a different machine
Does anyone have any idea as to why this should be happening
I have a number of packages that I have moved from an old server. Each package was scheduled with a SQL Agent job. On the old server everything ran fine. All of the packages run fine from VS, from DTEXECUI and I have tried one from the command line with DTEXEC and it worked.
When I run from the SQL Agent job, I don't get a failure, the package just hangs. I let one of the agent jobs sit for an hour with no progress. The package typically takes about 15 minutes to complete.
Below is the output from my package log up to the point that it hangs:
I get 2 errors when I run a job in sql server agent 2005 0xc0202009 and 0x80040E2. I cannot see why I am getting these errors, when I run the job with dtexec it is successful. Also when I run it in the debugger or run the executeable it is successful. But when I try to run it with the agent it fails. Any Ideas would be greatly appriciated.
thanks for the valuable information all the time!!! saved me a lot of time...
our team developes a system for text data improvement using ssis .
we have a few heavy packages that we want to execute on two separate "SSIS servers" that will be dedicated to runnung these packages only, and repeatedly. the main sql server will be placed on a different server machine .
my question is:
what is the best way to do this?
if we schedule these packages as a job of the Sql Ajent- does that mean that the packages will be executed on the sql server machine (which is not what we want) ? or could we define a remote machine to run the package on, and specify our "SSIS servers"?
or- should we use a simple scedualer on the "SSIS servers" using a dtexec command? but then i loose the benefits in using the sql agent- such as logging, notifications, etc.
do we need to install sqlserver on these "ssis servers"?
Okay, i've got an ETL package which works flawless, we've created a scheduled job for it which also worked. This ETL accesses a flat file located on a server on a different location in the same domain.
Last week we start a migration to a new network, the location where the flat file is stored has already moved to the new domain but the SQL server agent is still on the old domain. There is a trust between the 2 domains, the network user from the old domain has been added on the server in the new domain where the flatfile is located.
If i run the etl package manually it works flawless, now if i run the etl using the SQL server agent it won't run anymore.
I get the following error:
Executed as user: OIdDomaindwh. ...n 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 21:15:00 Error: 2007-10-02 21:15:02.10 Code: 0xC001401E Source: Rolmer_Product Connection manager "Flatfile Product" Description: The file name "\fs-cha-fs01dwhproduit.csv" specified in the connection was not valid.
Don't tell me its a problem related to the user that the SQL Server Agent is set to because its set to OldDomaindwh which is the same user i use to login on the server where i can manually run it without problems. The problem only occures when i run it using the SQL server agent which uses the same user to execute packages.
This is a very difficult problem i doubt anyone here will be able to crack this nut, but if they do i'll be very impressed because it would require a real smart network admin with SQL experiance to figure this one out...
