Problem with Jobs

Hello all,

I am having a problem with the sql

DB on the company that I work for.

I got message under job history

on Enterprise manager is :

"The job failed.The Job was invoked by Schedule 15 (Replication

agent schedule.).The last step to run

was step 1 (Run agent.)"

and in the event log is:

Event ID: 208

Source: SQLAgent$(company DB)

"sql server Schedule (company DB)

status:Failed"

Please advice.

Thanks in advance

Kind regards

This relates to replication issues, which can be quite complex depending on what you're trying to do. You should review your replication scheme to ensure that all the data is going where it should. If the replication isn't happening, your files will grow on the distributor until it runs out of room.

If you're new to replication, check here:

http://msdn2.microsoft.com/en-us/library/ms151198.aspx

Problem with job that run ssis pckg

Hi,

i have a job that run ssis pckgs and it failed when the server is log off with this error msg:

Executed as user: xxxxx\dwhadmin. ...0.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:15:00 AM Error: 2007-07-11 09:15:01.71 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSassword" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-07-11 09:15:26.77 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "PackagePassword" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-07-11 09:15:26.77 Code: 0xC0016016 Source: Description: Faile... The package execution fa... The step failed.

what can be the problem?

thanks.

Hi Steam5,

There are already answers aviled in this forums. I got it resolved in my case. There are several ways.

One way to deploy an SSIS package into a new machine where you actually created this package is to Use "Save copy of <Your SSIS Pkg> as" on the File menu in BIDS. Use EncryptAllSensitivewithPassword option with a password. Now this package can be executed on another Box using dtexec with /De option to decrypt the password.

Thanks

Subhash Subramanyam

|||

Hi Subhash,

thanks for the reply but it's not working.

:-( .

any other idea?

|||

Hi

We get slightly different errors here with SSIS, but the step succeeds.

Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 09:55:33 Finished: 09:55:34 Elapsed: 1.15 seconds. The package executed successfully. The step succeeded.

Have you tried hardcoding the password into the connection string in the job step? Sometimes depending on the type of connection (oracle OLDBD) the password is not deployed with the package and you need to override the connection string with the one in the job, including the password.

Hope it helps, bit of a long shot.

Cheers

Matt

Problem with job permissions

SO here's the deal - I have a developer that needs to be able to schedule jobs, but that I do NOT want to give System admin privelges to. She has DBO privelges on the DBs she needs, and is a member of Bulk Insert and Process ADmin server roles. She has the abiltiy to schedule jobs but if she creates one that uses CmdExe steps they fail with the message:

Executed as user: MCC_GENESIS\SQLSrv. The process could not be created for step 1 of job 0xB793D66AE8016647AC64FFEB3EE2E202 (reason: A required privilege is not held by the client). The step failed.

I have configured the SQL Agent to use a proxy account to run scripts if the user is not SysAdmin, and I am using the same domian account that starts the MSSQL service as the proxy. The account is a DOMAIN admin (I know bad idea, but that's what they gave me to work with.)

So here is the question - why do jobs fail when they are owned by the debveloper, but run when owned by SA - both are using the same account, (SA using account that starts server, develped using it thorugh proxy)

Any advie woudl be greatly appreciatedDid you reset the SQLAgentCmdExec permissions ?|||Originally posted by rnealejr
Did you reset the SQLAgentCmdExec permissions ?

I actually found the answer on anotehr forum, I had to grant the user permisson to replace a process level token. Once I did that, everythign worked fine.

Thanks for the reply :)|||Would you mind posting the solution - for future reference ?|||The solution was just that - granting the NTUser ID permission to replace proces level tokens - this can be done with policies or local system permissions. I think by default this is granted already, but the organization I am consulting for had it locked down. Once that was done, non- SA users could use CmdExe steps in their jobs.|||I was asked by another user to post the solution:

You first have to set up a Proxy account for running scripts as a non-sysadmin,

1. Open Enterprise manager.
2. Click on the server name -> Management
3. Right Click on SQL Server Agent
4. Choose the Job System tab
5. Uncheck the box that says "Only users with sysadmin privleges can execute CMdExec and ActiveScripting job steps.
6. You will be prompted to enter log in information - enter the system account info you want to use (Domain or Local) to run scripts - it should be at least a local admin account for the box, I normally use the same logon as that used by the MSSQL service.

then Verify/Change permissions.

1. Open Administrative tools (You will need admin rights on the server to do this)
2. Go to Local Security Policy ( this can also be done at the domain level, speak to your LAN admin about how they would like this setup)
3. Click on "user rights Assignment" in the left hand pane,
4. In the right hand pane, scroll down until you find " replace a process level token".
5. Double click on "Replace a Process level token", and add the proxy user account (Step 6 above).
6. Apply the change. You may have to restart the MSSQL and SQL Server Agent services for the change to take place, I am not sure.|||Originally posted by cdols
These are the exact steps I'm using to setup the Proxy account however, when I attempt to enter the login information for the Proxy I get the following error: "Unable to set the SQL Agent proxy account because of the reason listed below. 'Error executing extended stored procedure: Specified user can not login' "

What's strange is that this is the same account that the Server & Server Agent services use (and they are working fine). This domain account has also been added to SQL as a login with system admin privileges. The account has also been made a local administrator on the server.

In terms of testing the account, I can connect successfully to SQL with Query Analyzer using this domain account.

I've actually reloaded the OS & reinstalled SQL2000 and still have the problem (OS is 2000 Server and SQL2K is SP3).

What I have found for solutions so far assume that you actually CAN set the Proxy account.

Anyone have an idea what's going on?

Doug|||Hi,

Was you able to find the solution?

Thanks,
Leosql

Problem with job history. sql server 2005

HI

I found some issue with job history. My server name length is 32 charecters. and when we view the job history in sql management studio, some back ground script is running, there microsoft has defined servername as 30, due to this smaller definition for server server variable, i am getting error when i tried to view the job history.

what wil be the solution for this issue?

thanks

kiran

rename the server. hahaha. joke

|||

Hi Kiran,

Thanks for reporting this problem. Please file a bug using the following link https://connect.microsoft.com/SQLServer/Feedback.

Workaround:

Use the following stored procedure to get the job history

exec msdb.dbo.sp_help_jobhistory @.mode='FULL'

Link for more help on the above stored procedure:

http://msdn2.microsoft.com/en-us/library/ms188025.aspx

Regards

Rajesh Basa

Problem with job history. sql server 2005

HI

I found some issue with job history. My server name length is 32 charecters. and when we view the job history in sql management studio, some back ground script is running, there microsoft has defined servername as 30, due to this smaller definition for server server variable, i am getting error when i tried to view the job history.

what wil be the solution for this issue?

thanks

kiran

rename the server. hahaha. joke

|||

Hi Kiran,

Thanks for reporting this problem. Please file a bug using the following linkhttps://connect.microsoft.com/SQLServer/Feedback.

Workaround:

Use the following stored procedure to get the job history

exec msdb.dbo.sp_help_jobhistory @.mode='FULL'

Link for more help on the above stored procedure:

http://msdn2.microsoft.com/en-us/library/ms188025.aspx

Regards

Rajesh Basa

problem with job

hy,

i have made a package that fills a sql server 2005 database. when i run it locally it runs all the way, no errors.

Now that i have deployed the package en work with a job that executes it every day i get these errors.

The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.

and

component "Source - BU" (1) failed validation and returned error code 0xC020801C.

the strange thing is i don't get an error on the first dft which uses the same connection. i have pressed the test connection button and it states connection succesfull. any help would be appreciated.

Greetz,
Jens

It sounds like youre dynamically loading a connection that doesnt exist. Are you using a config file to load the connection info?

Have you tried logging on to the server machine (remote desktop), opening a command window and running the package through DTEXEC?

It will give you a more verbose error message which hopefully will flush out the issue.

(To run it with DTEXEC, open the job, select the SSIS step, click on the command line tab and copy and paste that text after DTEXEC)

|||

Are you by chance running the package on a 64bit server and is the source an Excel file or otherwise using the Jet engine for accessing the source?

Kirk Haselden
Author "SQL Server Integration Services"

|||

thanks for your reply's

i can run it with the dtexec utility without error

i'm using a access database to get the info. is this a problem?

|||

Hi, I am using SQL 2005 Integration Services on a 64bit server and trying to import an Access 2000 database and get the above error when i debug the package. What is my best workaround? Should i use a SQL 2000 server as an intermediate place for the import?

Many thanks

Jonathan

|||

FatherJack wrote:

Hi, I am using SQL 2005 Integration Services on a 64bit server and trying to import an Access 2000 database and get the above error when i debug the package. What is my best workaround?

Please check out this thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=430630&SiteID=1

Basically, you need to run the package in 32-bit mode.

problem with job

hy,

i have made a package that fills a sql server 2005 database. when i run it locally it runs all the way, no errors.

Now that i have deployed the package en work with a job that executes it every day i get these errors.

The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.

and

component "Source - BU" (1) failed validation and returned error code 0xC020801C.

the strange thing is i don't get an error on the first dft which uses the same connection. i have pressed the test connection button and it states connection succesfull. any help would be appreciated.

Greetz,
Jens

It sounds like youre dynamically loading a connection that doesnt exist. Are you using a config file to load the connection info?

Have you tried logging on to the server machine (remote desktop), opening a command window and running the package through DTEXEC?

It will give you a more verbose error message which hopefully will flush out the issue.

(To run it with DTEXEC, open the job, select the SSIS step, click on the command line tab and copy and paste that text after DTEXEC)

|||

Are you by chance running the package on a 64bit server and is the source an Excel file or otherwise using the Jet engine for accessing the source?

Kirk Haselden
Author "SQL Server Integration Services"

|||

thanks for your reply's

i can run it with the dtexec utility without error

i'm using a access database to get the info. is this a problem?

|||

Hi, I am using SQL 2005 Integration Services on a 64bit server and trying to import an Access 2000 database and get the above error when i debug the package. What is my best workaround? Should i use a SQL 2000 server as an intermediate place for the import?

Many thanks

Jonathan

|||

FatherJack wrote:

Hi, I am using SQL 2005 Integration Services on a 64bit server and trying to import an Access 2000 database and get the above error when i debug the package. What is my best workaround?

Please check out this thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=430630&SiteID=1

Basically, you need to run the package in 32-bit mode.

Problem with job

Hi there,

Actually I'm trying to replicate a production database to a development server using merge publication. (I'm using Microsoft SQL Server 2005 - 9.00.2047.00).

When I created the publication job (automatically when creating the publication) I specified to get executed every 15 minutes, and later I created a subscription (push subscription) I specified it to execute continously. So I made some chenges on the data on the production server, and they get replicated every minute aprox. , and not in 15 min intervals as I defined before.... When I go to the server agent I can see the job for publication and It is supposed to be executing the publication every 15 min as I defined but this doesn't happen !!!

Is this a Bug ? Is there any fix ?

Thanks !

The subscription job, which you set to run continuously, is what's moving changes between publisher and subscriber. This runs/polls every 60 sec, which is why you're seeing changes replicated every minute. THis behavior you're seeing is by design and expected. If you want it to replicate every 15 minutes, then change the schedule for this subscription job.

I'm not sure what a "publication" job is, maybe you're referring to the snapshot agent job? For merge replication, its typical to schedule the snapshot agent to run maybe once a day, but not every 15 minutes. You may want to change it.

So no it's not a bug

|||

I've got my problem solved !!! Thank you so much !

Greg Y wrote:

The subscription job, which you set to run continuously, is what's moving changes between publisher and subscriber. This runs/polls every 60 sec, which is why you're seeing changes replicated every minute. THis behavior you're seeing is by design and expected. If you want it to replicate every 15 minutes, then change the schedule for this subscription job.

I'm not sure what a "publication" job is, maybe you're referring to the snapshot agent job? For merge replication, its typical to schedule the snapshot agent to run maybe once a day, but not every 15 minutes. You may want to change it.

So no it's not a bug

sql

problem with insert into in scheduled job pleas help ASAP

Hello MSDN

I am using SQL 2005 and trying to INSTERT data in to a table

When I am using my command from SQL query windows it works fine,

INSERT INTO "tbl.FTPuploads" ("FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME")

SELECT "FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME"

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="G:\DATA\EDItoDB";Extended properties=Text')...uploaded#txt

But when I am trying to put that command in to a scheduled job I get this error

Executed as user: GW\fmnlasa. Incorrect syntax near 'tbl.FTPuploads'. [SQLSTATE 42000] (Error 102).The step failed.

I have changed the command to this, I have removed the quotes from the table name.

INSERT INTO tbl.FTPuploads ("FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME")

SELECT "FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME"

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="G:\DATA\EDItoDB";Extended properties=Text')...uploaded#txt

And now I get this error

Executed as user: GW\fmnlasa. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274).The step failed.

When you execute from the query, it will use your credential to connect the remote server. But the scheduled query might use the ServiceAccount. Check which service account used in the current context & give the required permission on the remote server. Or change the service account context to NT Credential.|||

Bascially the issues is of Security Context under which the Job run. Check these links

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1012759&SiteID=1

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/44111/Access-to-the-remote-server-is-denied-because-the-current-security

Madhu

|||

Thank you, for such a quick reply

I was using the same account in Job to as I was using in Query window

I am not sure but I think that the problem was in my table and column names

I have created new table with the name tblFTPuploads the old one was tbl.FTPuploads

After that I got erros for may column names so I have changed the first column name to FTPFileNames the old one was FTPfile_name

And the last step I did was to remove the quotes from the table and column names.

So the end script is like this

INSERT INTO tblFTPuploads (FTPFileNames, FTPGS, FTPST, FTPJOB, FTPDN, FTPSTATUS, FTPDATE, FTPTIME)

SELECT FTPFileNames, FTPGS, FTPST, FTPJOB, FTPDN, FTPSTATUS, FTPDATE, FTPTIME

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="G:\DATA\EDItoDB";Extended properties=Text')...uploaded#txt

And it is working now no problems.

Thanks again for a quick reply

Best regards,

Artavazd ASLANYAN

Network Administrator

Problem with displaying workflow?

Hi,

I just recognized a strange displaying problem in one of my SSIS jobs.

I created job which contains a sequence container. Within the sequence container there is one "Execute SQL Task" and one "Foreach Loop Container". Within the "Foreach Loop Container" there are 4 task which are connected with precedence constraints.

Now my problem is that if I load the SSIS job and open the "sequence container" while the "Foreach Loop Container" is already open then the precedence constraint won't be displayed. If I close and open the "Foreach loop container" again then the precedence constraints will be displayed again.

My first assumption was that it might be a problem with the display drivers of the computer ....however the problem appears also on any other computer.

Does anyone know how to solve this display problem without closeing/reopening the container?

Thanks,

StSt

StSt,

You just have to lump it I'm afraid. It happens from time to time.

-Jamie