Migrated Packages from DTS to SSIS...
Tried running the jobs calling these DTS packages ....but getting error ......
the step in job is :
DTSRUN /S<servername> /U<username> /P<password> /N"ATL_Get_Down_Dates"
giving error :
Executed as user: USATLBNHSYSTEM. DTSRun: Loading... Error: -2147220438 (8004042A); Provider Error: 0 (0) Error string: The specified Storage File contains multiple Packages; Loading requires a Name or Guid. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713. Process Exit Code 1. The step failed.
Who should own job in SQL Server 2005? Currently, I have set up a couple new jobs, the owner is a windows login with administrative privileges on the server. So the job runs fine.
When I tried to change the owner to a non-admin user, the job failed, stating that I needed to set up proxy permissions or something to that effect. So I left the owner as the admin user.
Is that ok, or even the preferred owner of jobs?
Also - One of my job steps is calling an SSIS package. On the "general" tab, it shows that it will run the package as the SQL Server Agent, and then there is a section "Log on to Server" where you select windows or sql server login info. In this case, I the SQL Server agent is a different windows account than the job owner... so who is logging in at the package level - the job owner or the SQL Server agent account?
Hello I am using SQL Server 2005 I want to delete the data from my table after specific number of days.This should happen automatically. After the days I specified the data from the table should automatically get deleted. Can anyone let me know how to achieve this.If anyone can give an example for this it will be great.Thanks in advance.
Will prbly write a blog about this pretty soon. Until then, here is a quick llist of steps:
Create an IS package from Visual Studio. In the Toolbox, under the "Control Flow Items" there is a "Transfer Jobs Task". Use this to transfer jobs to your local machine or any other server.
I still have to figure out how to make it write to a flat file. The only destination it allows at this point seems to be another SQL server.
Dinakar Nethi ************************ Life is short. Enjoy it. ************************ http://weblogs.sqlteam.com/dinakar/
The application that I'm working is has a job that runs 24/7 which checks a table for rows with specific status, if it finds it, then I has to execute a job for each row that it finds. I don't know wheather if SQL Server jobs are asynchronous or not, since I need the execution of jobs to be in parallel instead of in a pipeline mode.
Hi there,, I've a problem when I make a a full backup for my db using maintenance plan the job that is associated with the maintenance plan is not working and gives the followning error: The job falied. The job was invoked by user machine nameuser name. The step to run was step1(subplan). The user I'm using is administrator and the jobs was working normaly and suddenly it stoped.
I have two tables t_DTM_DATA_STAGING around 2 million records t_DTM_DATA around 251 million records
The below SQL statement looks for records in the t_DTM_DATA_STAGING table that are not in the t_DTM_DATA table and adds them to a 3rd table. (t_DTM_DATA_STAGING2)
This statement has been running fine for weeks, but now it seems to get hung every day. I ran sp_Who2 and it says the status is runnable. I let it run for around 5 or 6 hours the other day to see if it will finish but it didn't. This SQL job is step 3 in a 6 step SQLAgent job that usually finishes in 30 to 45 minutes.
I'm not sure how to troubleshoot this problem. No other jobs are running at the time this job runs.
Could this SQL statement be written a better way?
Thanks for any help anyone can provide.
Jerid
SET QUOTED_IDENTIFIER ON
INSERT INTO [DTM].[dbo].[t_DTM_DATA_STAGING2] ([CP],,[MAJ],[MINR],[LOCN],[DPT],[YEAR],[PD],[WK],[TRDT],[SYSTEM],[AMOUNT],[DESCRIPTION],[GROUP],[VENDOR] ,[INVOICE],[IDAT],[PO_NUMBER],[DDAT],[RCV#],[RDAT],[RSP],[EXPLANATION],[UPLOAD_DATE],[UPLOAD_USER],[UPLOAD_NAME] ,[RELEASE_DATE],[RELEASE_USER],[RELEASE_NAME],[TRTM]) SELECT t_DTM_DATA_STAGING.CP, t_DTM_DATA_STAGING.CO, t_DTM_DATA_STAGING.MAJ, t_DTM_DATA_STAGING.MINR, t_DTM_DATA_STAGING.LOCN, t_DTM_DATA_STAGING.DPT, t_DTM_DATA_STAGING.YEAR, t_DTM_DATA_STAGING.PD, t_DTM_DATA_STAGING.WK, t_DTM_DATA_STAGING.TRDT, t_DTM_DATA_STAGING.SYSTEM, t_DTM_DATA_STAGING.AMOUNT, t_DTM_DATA_STAGING.DESCRIPTION, t_DTM_DATA_STAGING.[GROUP], t_DTM_DATA_STAGING.VENDOR, t_DTM_DATA_STAGING.INVOICE, t_DTM_DATA_STAGING.IDAT, t_DTM_DATA_STAGING.PO_NUMBER, t_DTM_DATA_STAGING.DDAT, t_DTM_DATA_STAGING.RCV#, t_DTM_DATA_STAGING.RDAT, t_DTM_DATA_STAGING.RSP, t_DTM_DATA_STAGING.EXPLANATION, t_DTM_DATA_STAGING.UPLOAD_DATE, t_DTM_DATA_STAGING.UPLOAD_USER, t_DTM_DATA_STAGING.UPLOAD_NAME, t_DTM_DATA_STAGING.RELEASE_DATE, t_DTM_DATA_STAGING.RELEASE_USER, t_DTM_DATA_STAGING.RELEASE_NAME, t_DTM_DATA_STAGING.TRTM FROM t_DTM_DATA_STAGING LEFT OUTER JOIN t_DTM_DATA AS t_DTM_DATA_1 ON t_DTM_DATA_STAGING.TRTM = t_DTM_DATA_1.TRTM AND t_DTM_DATA_STAGING.TRDT = t_DTM_DATA_1.TRDT AND t_DTM_DATA_STAGING.PD = t_DTM_DATA_1.PD AND t_DTM_DATA_STAGING.YEAR = t_DTM_DATA_1.YEAR AND t_DTM_DATA_STAGING.DPT = t_DTM_DATA_1.DPT AND t_DTM_DATA_STAGING.LOCN = t_DTM_DATA_1.LOCN AND t_DTM_DATA_STAGING.MINR = t_DTM_DATA_1.MINR AND t_DTM_DATA_STAGING.MAJ = t_DTM_DATA_1.MAJ AND t_DTM_DATA_STAGING.CO = t_DTM_DATA_1.CO AND t_DTM_DATA_STAGING.CP = t_DTM_DATA_1.CP WHERE (t_DTM_DATA_1.CP IS NULL)
We have our SQL Server 2005 hosted at a datacenter and have only SS Management Studio access (no term serv, no event log, etc.). Also, our hosting company has disabled the Database Mail feature. We have over 60 jobs running on a minute/hourly/daily basis that are critical for our business. It is super important for us to know if and when a job failed.
Given this scenario, how do you suggest we implement monitoring/notification of failed jobs?
We have development and user acceptance (UA) servers. When I start a job on the development server, on the management studio, Start Jobs window and Job activity windows indicate "Executing" until the end of the job and finish with "success" or "failure"
But on the UA server, second after I start a job, Start Jobs window comes up with "success" or failure" and Job activity monitor says "idle" but Job continues to log without any error message and updates the tables. So these two windows are not reliable at all. I have to add that I have only job operator rights on the UA server.
I need to copy (not convert) a number of existing DTS jobs and the related DTS packages from a 2k machine to a 2k5 machine. I'm trying to avoid having to re-create the Jobs and type in all the Jobs steps in 2005.
(Yes, we will be converting the jobs, etc. but that's not an option right now)
I've had good results using the nifty technique from SQLDTS.com (http://www.sqldts.com/default.aspx?204) for the packages, but this doesn't quite work for the Job steps due to the new 'Step_UID' column in the sysjobsteps table in 2005.
I've tried a number of variations in DTS to populate this new column, which is of type uniqueidentifier, but still no joy.
Does anyone know of a workaround or a better method to get the Job copied over from 2000?
Hello All I am currently reviewing MS documentation regarding an upgrade to SQL 05 (from 2000). Given the risks invloved we have opted for a side-by-side upgrade. The "Upgrade Technical Resource Guide" indicates that system databases cannot be upgraded. This leaves the question "What to do with Jobs, Operators and Alerts?" Do these have to be recreated manually? Also is there no easy way to migrate DB Maintenance plans ? I will use MS's sp_help_revlogin stored proc to re-create users but what about the msdb stuff ? I am presuming the structure is different hence cannot script (in 2000) and apply (to 2005) the Jobs. The Copy Databases wizard can copy Jobs etc but my past experience with this has not been brilliant so am reluctant to use it. We plan to use the "backup and restore" method to migrate the user databases. Thanks in advance.
I am trying to migrate few of my SQL jobs to SQL2K5. Can I simply script and execute them on the new environment. I hope the script is forward compatible.
------------------------ I think, therefore I am - Rene Descartes
I would like to create an SSIS package in 2005 and run it in 2000. Is there anyway to do this? Or does SQL Server 2000 have a precursor to SSIS? I am trying to create a job to automatically catch and kill orphaned processes. -Kyle
I was wondering if there was a SQL query I could run that would list all the current jobs in a SQL 2005 database? I would like to check to ensure a job exists before I call it, but was having trouble finding a way to determine if it existed.
I have moved my database recently from a NT server to a Windows 2000 server, both running SQL 7.0 I have a couple of jobs that sometimes, work, sometimes not, the problem it tells me is that it cannot find the file specified, I run the job from enterprise manager and it runs just fine, it'll run here and there but it still fails frequently. Any suggestions on what to check? Thanks.
I have a very annoying problem with my SQL Server 7.0
Here is the story: In our compagny we have three SQL servers, SQL1, SQL2 and SQL3. The SQL1 server is our development server where we build our databases. When they are ready to go live I make an DTS package that copies the database to SQL2 and put it into a schedule.
Is it somehow possible to execute a job on SQL2 when the job on SQL1 succesfully has finished??
I'm new to SQL. I have a scenario, Where customer want to move all the jobs from original SQL server to some remote SQL server and want to trigger jobs on remote server to do its work on original server.
Hi All, I am not able to see any of the jobs in the enterprise manager that are scheduled on one of our production servers. Have any body seen this type of behaviour of SQL server.. The jobs are there in the SQL as I can query sysjobs table to see there entry.. The jobs are being executed properly when they are supposed to.. Thanks Ajay
Hi I need to copy all the jobs from one server to another server. How i have to do. I tried to take script of MSDB database, but it is not scripting all the jobs. PLs anyone suggest me what i have to do with scheduled jobs in 65. it is very urgent pls. thank u --Rk
I have created DTS packages to export data to text files....these packages are running...the jobs that are created to run these packages are failing ... both the SQL SERVER and the SQL SERVER AGENT are running administrator accounts ...if the job step is run on the command line it works..so basically some permission that invokes the the command shell is missing...jobs that run just T-SQL steps work fine too....what am i missing out?
Hi, I have to move my SQL server data from win95 to NT. When it comes to databases I would execute attach option. When it comes to backup devices I think I could simply copy the folder where backup devices are being located? IS it true?? How about moving jobs; DTS and Database Maintenance Plans??? Any Ideas?? Thanks, Andy
Hello - I was curious if there is any way to "copy" a SQL Job, alert etc. from one server to another. I want to test jobs on a non production server before putting them out on our live Server. I understand you can do the whole Master server thing, but this temp server is often rebuilt for whatever we need. Any insights would be greatly appreciated... Thanks in Advance. Joe
I have installed SQL server 2000 (Enterprise) on W2K advanced server using my local "Administrator" account. Using the same account for login, I have created database backup jobs on the server. After this I rename the Windows administrator account to "admin". Now I see that the jobs started failing. The error message indicates that "could not determine whether the 'server/administrator' account has access to SQL server."
Further, when I tried to troubleshoot, I discovered that the job owner name still displayed (through EM) is "server/administrator" account (this account is now renamed). I tried to change the owner name to "server/admin" but it is not getting changed. Hence, I just created a Windows user named "Administrator" and see that the Job is executing successfully.
Please note that the backup is taken on the server itself and SQL server & Agent services are running under security context of a domain account having administrator priveleges on the server.
What I am interested in knowing is what are the reasons for such behaviour? And how to rectify this problem since i really don't want the "administrator" account to be created.
Hi. i'm new to sql server jobs as i have never used the feature manually before. here is what i want to do but i don't know if it's possible.
we have a report we use that's accessed from an asp page. currently, the page uses a stored procedure to generate the data. well, as time has progressed, we've added many items to the report. it now takes a good 15-20 seconds to pull the report. what i want to do is run a job that will compile the data for me and just dump that information into a new table. does that make sense?
Hello all, we just moved our databases over from a SQL 2000 server onto a new SQL 2005 server. We had no problem migrating them over, but was wondering if there was a way to migrate the jobs over as well. We have about 20 of them and would prefer not to hav to create them all again on the new server.
I am creating a job for integration services in SQL server 2005 64-bit edition. I am getting error :The specified module could not be found. (Exception from HRESULT: 0x8007007E) (SqlManagerUI). Please help
