How To Script Jobs In SQL Server ?

How to create SQL script out of job in SQL Server ?
I know there is a way thru EM, but is there any stored procedure or any function that will return the SQL script of any job.
I want to script all jobs on my server just as a part backing up mechanism.
Thanks
Decasto.After starting Profiler, going into EM and scripting a job I noticed two procs being called...

msdb..sp_help_jobstep @.job_id = XXX
and
msdb..sp_help_jobschedule @.job_id = XXX

soooo I suppose you could step through msdb..sysjobs and process each job you find with one or both of the above procs.

How to scheule job to run last day of the month

What are the settings to get a sql server job to always run the last day of the month? This should be quite easy!

Thanks!

SQL2000?

if so,

on the schedule tab of the job, edit the schedule, or create a new one... on the schedule type, select recurring, then click the change button on the lower right hand side.

In the Occurs section, change to monthly. Then in the monthly section that you will see to the right select the lower radio button and change the first drop down list box to "Last", the second to "Day" of every 1 month.

Set the time, and away you go...

|||Thanks! This would make a nice addition to EM Help!

How to schedule to run Integration Packages using SQL Agent/Job?

Hi Folks,

I deploying a couple of integration packages which needs to be run sequentially. Currently, I quite puzzled how come i cannot run them as a SQL Agent job. It always fail with "Executed as user: Servername\Administrator. The package execution failed. " But if I had put import these integration packages into the SQL Server 2005 Integration Services FileSystem, it does run but i need promptly as there is a dialog requesting whether to execute the packages. Is there a way to automate the running of these packages through SQL agent. ? Pls help need to get it up and running asap. Thanks first.

Yes there is. Most problems experienced when using SQL Agent to run packages are down to the package being run as a different user. Is that what you are doing?

-Jamie

|||Hi Garynkill23,

This could be due to the ProtectionLevel setting for the individual packages - that's my guess.

By default, these are set to EncryptSensitiveWithUserKey. This means as long as you personally execute the packages, your credentials are picked up and the packages execute in your security context. This is true even if you're connected to a remote machine, so long as you're using the same AD credentials you used when you built the packages. Does this make sense?

When the job you created executes, it runs under the SQL Agent Service logon credentials.

My understanding of the "Sensitive" in EncryptSensitiveWithUserKey is most likely flawed, but I cannot find a way to tell my SSIS package "hey, this isn't sensitive so don't encrypt it." Although this sometimes gets in the way I like this feature because it keeps me from doing something I would likely later regret. Anyway, my point is the Sensitive label is applied to connection strings and I cannot find a way to un-apply it (and I'm cool with that).

One of the first things an SSIS package tries to do (after validation) is load up configuration file data. This uses a connection, which (you guessed it) uses your first connection string. Since yours are encrypted with your own personal SID on the domain and this is different from the SID on the account running the SQL Agent Service, the job-executed packages cannot connect to the configuration files to decrypt them.

There are a couple proper long-term solutions but the one that makes the most sense is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well, and this should allow the package to run without needing your security credentials.

Note: You will also need this password to open the packages in BIDS (or Visual Studio) from now on... there's no free lunch.

Hope this helps,

Andy

|||

Andy Leonard wrote:

Hi Garynkill23,

There are a couple proper long-term solutions but the one that makes the most sense is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well, and this should allow the package to run without needing your security credentials.

Andy,

Why do you think that makes most sense? To me, it makes more sense to set ProtectionLevel=DontSaveSensitive and then store all your connection strings in configurations. Personally I think this should be considered best practice - its a matter of opinion of course.

-Jamie

|||

Hi Jamie,

I think the method you advocate (DontSaveSensitive) is great, and perhaps best.

My experience with this question drives my copy-and-paste answer about using a password. Here's what I've seen: Someone starts using SSIS and works through some issues as they scale the learning curve (reading good books and a great blog over at Conchango for help as they go).

Then they move it to a test or integration server, experiment with the job step type until they get a job to run when they right-click the job and click Start Job at Step... So they schedule it to run and wait. The SQL Agent scheduler fires the job and it fails with a "cannot acquire connection" (or similar) error. We know why but it seems completely baffling to them. (It certainly did to me when it first happened!)

What's worse is there's little or nothing in the log (the package cannot make that connection either).

While EncryptWithPassword method works, it also requires folks to keep track of a(nother) password.

I start here mainly because it's the quickest way I can think of to get that package up and running. Everyone understands passwords. I don't claim to be right or even advocating a best practice (at least not yet) - I'm just trying to help SSIS developers get that package running.

I welcome any feedback - from anyone. I'm still learning too! :)

Thanks,

Andy

|||

That all sounds fair enough to me Andy. I'll hold my hand up and say that I always try and drive people towards best practice - and to me that means configurations. As I aluded to, its all subjective.

Good discussion.

-Jamie

|||

Jamie Thomson wrote:

I'll hold my hand up and say that I always try and drive people towards best practice - and to me that means configurations.

I cannot argue with you. I advocate 2-pass (minimum) configurations to consulting clients. But I'm also right there to either implement it myself or show them how to implement it.

Jamie Thomson wrote:

Good discussion.

I couldn't agree more!

Thanks,

Andy

|||

As regards to your first message, I am not sure which account i was using Windows Authentication to SQL Server 2005 to run the packages which i kept under the SQL Server 2005 Integration Services - File System section. And I had to run it manually.

Then what should i do so that i can run it under SQL Agent as from what i read it requires a username/password but the packages had been saved under this option "Don't Save any sensitive data". One more question, is there any impact if i save it using this option and put it under maintenance under SQL Server 2005 Integration Services.

|||

garynkill23 wrote:

As regards to your first message, I am not sure which account i was using Windows Authentication to SQL Server 2005 to run the packages which i kept under the SQL Server 2005 Integration Services - File System section. And I had to run it manually.

Then what should i do so that i can run it under SQL Agent as from what i read it requires a username/password but the packages had been saved under this option "Don't Save any sensitive data". One more question, is there any impact if i save it using this option and put it under maintenance under SQL Server 2005 Integration Services.

As discussed above, if you have Protectionlevel=DontSaveSensitive then you will more than likely have to use configurations.

What do you mean by "put it under maintenance"?

-Jamie

|||

Hi garynkill23,

This is complicated. It touches a lot of moving parts in your SSIS package, SQL Servers, and enterprise domain. There's just no simple and easy explanation. But it is this way for good reason and that reason is to provide security.

I wrote a blog entry that talks about connections between SSIS and SQL Server - if you use a SQL Login to connect to SQL Server. This should also apply to storing credentials for any provider that requires a username and password.

Windows Authentication is simpler and safer provided your SQL Servers use one or more domain accounts for the SQL Agent service. (It also offloads SQL Server connectivity account maintenance to the Help Desk - at no extra charge.) If you use a domain account for the SQL Agent service, your SSIS connection managers can all be configured to use Windows Authentication to connect, and you simply grant the SQL Agent service domain account the access it needs in SQL Server. Does this make sense?

For example: I have a domain account named MyDomain\Andy. I log in as MyDomain\Andy and write an SSIS package that connects to a Dev SQL Server. I use the default ProtectionLevel: EncryptSensitiveWithUserKey. MyDomain\Andy has sufficient privileges on the Dev SQL Server. When I'm done, I can manually execute the package and it succeeds in connecting to the Dev SQL Server.

Next, I deploy the SSIS package to a Prod SQL Server. I create a SQL Agent job with an SSIS step that calls the SSIS package. I schedule the job to run at 2:00 AM. Again, MyDomain\Andy has sufficient privileges in the Prod SQL Server. When I right-click the SQL Agent job I just created, I can select "Start Job at Step..." and the job executes, calls the SSIS package, and both succeed.

What just happened? From the security context point-of-view, MyDomain\Andy just executed this job. This is important. The SQL Agent scheduler did not execute the job. In this scenario, that won't happen until 2:00 AM. Although this was a good test, it was incomplete.

To continue the example, let's assume the SQL Agent service on the Prod SQL Server runs under a domain account named MyDomain\SQLAgentService. When the scheduler fires the job at 2:00 AM, MyDomain\SQLAgentService will try to log into the Prod SQL Server and perform the operations specified in your SSIS package. If it lacks permissions to connect, the SSIS package will fail, causing the SQL Agent job to fail. You will get an error message similar to "failed to acquire connection". The error varies because the permissions granted MyDomain\SQLAgentService vary along with the configuration of the SSIS package:

If Logging is enabled in the SSIS package, it will probably attempt to connect to the logging provider before attempting to connect to a SQL Server - unless you're using a SQL Server logging provider. If it cannot connect to the Logging provider, it cannot log the fact that it encountered an error connecting to the Logging provider.

how to schedule sql profiler trace job

I would like to know how to schedule a sql profiler trace
job in batch mode. From what I can find, sql profiler
can only be started manually. Any clue will be
appreciated.Wen,
See if this helps..
--SQL 2000:
INF: Job to Monitor SQL Server 2000 Performance and Activity
http://www.support.microsoft.com/?id=283696
--SQL 7.0:
INF: Job to Monitor SQL Server 7.0 Performance and Activity
http://www.support.microsoft.com/?id=286191
--SQL 6.5:
INF: How to Automate SQL Trace by Means of Scheduled Tasks
http://www.support.microsoft.com/?id=194860
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Wen Chang" <wchang@.siac.com> wrote in message
news:034c01c3465c$ebc30cf0$a501280a@.phx.gbl...
> I would like to know how to schedule a sql profiler trace
> job in batch mode. From what I can find, sql profiler
> can only be started manually. Any clue will be
> appreciated.|||Dinesh,
Thank you very much for the info.
I will try it out.
Wen Chang
>--Original Message--
>Wen,
>See if this helps..
> --SQL 2000:
> INF: Job to Monitor SQL Server 2000 Performance and
Activity
> http://www.support.microsoft.com/?id=283696
>--SQL 7.0:
> INF: Job to Monitor SQL Server 7.0 Performance and
Activity
> http://www.support.microsoft.com/?id=286191
>
>--SQL 6.5:
> INF: How to Automate SQL Trace by Means of Scheduled
Tasks
> http://www.support.microsoft.com/?id=194860
>
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"Wen Chang" <wchang@.siac.com> wrote in message
>news:034c01c3465c$ebc30cf0$a501280a@.phx.gbl...
>> I would like to know how to schedule a sql profiler
trace
>> job in batch mode. From what I can find, sql profiler
>> can only be started manually. Any clue will be
>> appreciated.
>
>.
>

how to schedule job to run vbscript file

Hi All,
I wrote a vbscript file which copies one table records to another database table. I wrote error handling to send an email if error occurs when copying records from one table to another. I want to schedule a sql job and give this file to execute. Could anyone plz tell me how to schedule sql job which runs vbscript file. i selected ActiveXScript and choose vbscript and gave path. I'm not understanding what the command should be given to run vbscript file. The result of giving path is job is failing.
Any help would be appreciated...plzzzzzzz
thanks
carolThe way I've used the ActiveXScript option in the past is to physciallypaste the VBScript code into the Command textbox. AFAIK you can'ttype in a path.

how to schedule DTS package in sql 2005

Hello,

I am trying to to schedule DTS Package but this message appear .

Message
The job failed. The Job was invoked by User sa. The last step to run was step 1 (1). and this

Message
Executed as user: Computer name\SYSTEM. The package execution failed. The step failed

so how I can schedule DTS Package in sql 2005 .

Do you have logging enabled for this package? if enabled check the log file it should have more detailed information on the error.

The error you have posted is a very generic error from the job agent, and it does not give any information.

Thanks

|||

Sounds like you might have some issues with the account settings. See the following thread for tips on setting up proxies / credentials / jobs etc.

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

|||

Thanks, all

But still the same problem even I did all thing to solve but still can't schedule DTS, but when I see th proparites of the

SQL Server Agent-->Connection and found the sql server connection grayed out and can't select or change it . so may be this is the problem but how to run the agent under sa account .

|||

Assuming you are talking about the connection infromation on the left hand side of the job properties screen, that connection information is how you are currently connected to the sql server. You would need to look at the steps tab and click edit on the step associated with the package you are trying to run. At the top of this screen there will be a place for the step name (i.e. run package x), the type (sql server integration services package) and the run as. You would select the correct proxy name from the run as drop down that ties to your sa account...

|||

Thanks very much for you reply but I still can't schedule the DTS even though when I execute the dts it's work fine and I did all thing what said here http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp

|||

I am trying to schedule working DTS in sql server 2005 sp 2 . but I it failed and got this message . I searched at Internet and found this

http://support.microsoft.com/kb/904796

but I can’t understand how to solve it, any one can help please .

Message

Executed as user: ComputrName\Administrator. ...00.3042.00 for 32-bitCopyright (C) Microsoft Corp 1984-2005. All rights reserved.Started:10:16:11 ?Error: 2007-08-17 22:16:12.95Code: 0x00000000Source: Copy Data from ROOM toDBNamedboER_ROOMTaskDescription: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:14.75Code: 0x00000000Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:15.14Code: 0x00000000The package execution fa...The step failed.

|||

how to Reinstall the SQL Server 2000 tools? where to find it .

Still no solution can solve this issue .

how to schedule DTS package in sql 2005

Hello,

I am trying to to schedule DTS Package but this message appear .

Message
The job failed. The Job was invoked by User sa. The last step to run was step 1 (1). and this

Message
Executed as user: Computer name\SYSTEM. The package execution failed. The step failed

so how I can schedule DTS Package in sql 2005 .

Do you have logging enabled for this package? if enabled check the log file it should have more detailed information on the error.

The error you have posted is a very generic error from the job agent, and it does not give any information.

Thanks

|||

Sounds like you might have some issues with the account settings. See the following thread for tips on setting up proxies / credentials / jobs etc.

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

|||

Thanks, all

But still the same problem even I did all thing to solve but still can't schedule DTS, but when I see th proparites of the

SQL Server Agent-->Connection and found the sql server connection grayed out and can't select or change it . so may be this is the problem but how to run the agent under sa account .

|||

Assuming you are talking about the connection infromation on the left hand side of the job properties screen, that connection information is how you are currently connected to the sql server. You would need to look at the steps tab and click edit on the step associated with the package you are trying to run. At the top of this screen there will be a place for the step name (i.e. run package x), the type (sql server integration services package) and the run as. You would select the correct proxy name from the run as drop down that ties to your sa account...

|||

Thanks very much for you reply but I still can't schedule the DTS even though when I execute the dts it's work fine and I did all thing what said here http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp

|||

I am trying to schedule working DTS in sql server 2005 sp 2 . but I it failed and got this message . I searched at Internet and found this

http://support.microsoft.com/kb/904796

but I can’t understand how to solve it, any one can help please .

Message

Executed as user: ComputrName\Administrator. ...00.3042.00 for 32-bitCopyright (C) Microsoft Corp 1984-2005. All rights reserved.Started:10:16:11 ?Error: 2007-08-17 22:16:12.95Code: 0x00000000Source: Copy Data from ROOM toDBNamedboER_ROOMTaskDescription: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:14.75Code: 0x00000000Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:15.14Code: 0x00000000The package execution fa...The step failed.

|||

how to Reinstall the SQL Server 2000 tools? where to find it .

Still no solution can solve this issue .

How to schedule a SQL job from .net 2005

Can we still schedule a sql job from .net 2005 using sqldmo object?

Any pointers will be appreciated...

If you're developing a new application for SQL Server 2005, it is recommend that you use SQL SMO instead of DMO. DMO has been marked for deprecation and SQL SMO is taking its place.

Here is a link to the BOL docs for SMO and SQL Server Agent:
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.agent.aspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

Appreciate your response.

I have a sql job which will run this SSIS package. If I were to write a SP which will do Exec sp_start_job and give the job name shouldnt this be lot easier...
i just want an Asyn process to start when the package is running so that user is completly oblivious abt the back end processing.

So I was wondering to call the sp_start_job in a sp and call this stored proc once the file upload is done...

But as expected I am running into permission starting a sp from msdb db...

Any idea how can I sue sp_start_job and execute this package?

Regards,

sql

How to schedule a profile to capture trace

Hello all,
Id like to schedule a profile to capture trace.
How can I set up a batch job for that?
I need a trace file instead of table, filter data by database id, size will
be 50MB, and start time will be 9:00 am and stop time will be 3:00pm.
Thanks in advance,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200508/1
Hi,
Setup the server side trace. Vyas has got a really good article to autmate
serverside tracing.
See the below URL:-
http://vyaskn.tripod.com/server_side...sql_server.htm
Thanks
Hari
SQL Server MVP
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:528E00B69080E@.droptable.com...
> Hello all,
> I'd like to schedule a profile to capture trace.
> How can I set up a batch job for that?
> I need a trace file instead of table, filter data by database id, size
> will
> be 50MB, and start time will be 9:00 am and stop time will be 3:00pm.
> Thanks in advance,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200508/1

How to schedule a profile to capture trace

Hello all,
I?d like to schedule a profile to capture trace.
How can I set up a batch job for that?
I need a trace file instead of table, filter data by database id, size will
be 50MB, and start time will be 9:00 am and stop time will be 3:00pm.
Thanks in advance,
Do.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1Hi,
Setup the server side trace. Vyas has got a really good article to autmate
serverside tracing.
See the below URL:-
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
Thanks
Hari
SQL Server MVP
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:528E00B69080E@.SQLMonster.com...
> Hello all,
> I'd like to schedule a profile to capture trace.
> How can I set up a batch job for that?
> I need a trace file instead of table, filter data by database id, size
> will
> be 50MB, and start time will be 9:00 am and stop time will be 3:00pm.
> Thanks in advance,
> Do.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1

How to schedule a profile to capture trace

Hello all,
Id like to schedule a profile to capture trace.
How can I set up a batch job for that?
I need a trace file instead of table, filter data by database id, size will
be 50MB, and start time will be 9:00 am and stop time will be 3:00pm.
Thanks in advance,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200508/1Hi,
Setup the server side trace. Vyas has got a really good article to autmate
serverside tracing.
See the below URL:-
http://vyaskn.tripod.com/server_sid..._sql_server.htm
Thanks
Hari
SQL Server MVP
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:528E00B69080E@.droptable.com...
> Hello all,
> I'd like to schedule a profile to capture trace.
> How can I set up a batch job for that?
> I need a trace file instead of table, filter data by database id, size
> will
> be 50MB, and start time will be 9:00 am and stop time will be 3:00pm.
> Thanks in advance,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200508/1

How to run SSIS package as a SQL Scheduled Job?

Hello:
I have a SSIS package that will import data from one DB to another. I would
like to set it up as a nightly job. When I did, it faied within couple
seconds & the error saying that Login failed for user (TRSNT\dev02-SQL01) -
that user is the SQL service account that SQL Agent uses.
Message
Executed as user: TRSNT\dev02-sql01. ... 9.00.3042.00 for 64-bit Copyright
(C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:01:42 AM
Error: 2007-10-26 11:01:48.23 Code: 0xC0202009 Source: Copy Jbq Opr
Sec Tables from TRSDev to TRSDev0203 Connection manager
"SourceConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is
available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D
Description: "Login failed for user 'TRSNT\dev02-sql01'.". End Error
Error: 2007-10-26 11:01:48.23 Code: 0xC020801C Source: Data Flow
Task Source - GenTRSEmailAddr [1] Description: SSIS Error Code
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection
method call to the connection manager "SourceConnectionOLEDB" failed with
error code 0xC0202009. There may be error messages posted before this with
more information on why the ... The package execution fa... The step
failed.
Am I missing something here?
Thank you in advavnce for your responses.
Chai> When I did, it faied within couple
> seconds & the error saying that Login failed for user (TRSNT\dev02-SQL01) -
> that user is the SQL service account that SQL Agent uses.
Did you add that Windows account to the SQL Server you try to access?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Chai" <chai@.trs.state.il.us> wrote in message news:efV8Dq%23FIHA.1324@.TK2MSFTNGP06.phx.gbl...
> Hello:
> I have a SSIS package that will import data from one DB to another. I would
> like to set it up as a nightly job. When I did, it faied within couple
> seconds & the error saying that Login failed for user (TRSNT\dev02-SQL01) -
> that user is the SQL service account that SQL Agent uses.
> Message
> Executed as user: TRSNT\dev02-sql01. ... 9.00.3042.00 for 64-bit Copyright
> (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:01:42 AM
> Error: 2007-10-26 11:01:48.23 Code: 0xC0202009 Source: Copy Jbq Opr
> Sec Tables from TRSDev to TRSDev0203 Connection manager
> "SourceConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR.
> An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is
> available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D
> Description: "Login failed for user 'TRSNT\dev02-sql01'.". End Error
> Error: 2007-10-26 11:01:48.23 Code: 0xC020801C Source: Data Flow
> Task Source - GenTRSEmailAddr [1] Description: SSIS Error Code
> DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection
> method call to the connection manager "SourceConnectionOLEDB" failed with
> error code 0xC0202009. There may be error messages posted before this with
> more information on why the ... The package execution fa... The step
> failed.
>
> Am I missing something here?
> Thank you in advavnce for your responses.
>
> Chai
>|||Tibor:
Yes, I tried what you suggested and still received the same error.
Chai
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:A4D3631B-13AE-475C-907C-3A24D1AAF71D@.microsoft.com...
>> When I did, it faied within couple seconds & the error saying that Login
>> failed for user (TRSNT\dev02-SQL01) - that user is the SQL service
>> account that SQL Agent uses.
> Did you add that Windows account to the SQL Server you try to access?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Chai" <chai@.trs.state.il.us> wrote in message
> news:efV8Dq%23FIHA.1324@.TK2MSFTNGP06.phx.gbl...
>> Hello:
>> I have a SSIS package that will import data from one DB to another. I
>> would like to set it up as a nightly job. When I did, it faied within
>> couple seconds & the error saying that Login failed for user
>> (TRSNT\dev02-SQL01) - that user is the SQL service account that SQL Agent
>> uses.
>> Message
>> Executed as user: TRSNT\dev02-sql01. ... 9.00.3042.00 for 64-bit
>> Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started:
>> 11:01:42 AM Error: 2007-10-26 11:01:48.23 Code: 0xC0202009
>> Source: Copy Jbq Opr Sec Tables from TRSDev to TRSDev0203 Connection
>> manager "SourceConnectionOLEDB" Description: SSIS Error Code
>> DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
>> An OLE DB record is available. Source: "Microsoft SQL Native Client"
>> Hresult: 0x80040E4D Description: "Login failed for user
>> 'TRSNT\dev02-sql01'.". End Error Error: 2007-10-26 11:01:48.23 Code:
>> 0xC020801C Source: Data Flow Task Source - GenTRSEmailAddr [1]
>> Description: SSIS Error Code
>> DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
>> AcquireConnection method call to the connection manager
>> "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be
>> error messages posted before this with more information on why the ...
>> The package execution fa... The step failed.
>>
>> Am I missing something here?
>> Thank you in advavnce for your responses.
>>
>> Chai|||Can you log in interactively using the TRSNT\dev02-sql01 account and execute your package?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Chai" <chai@.trs.state.il.us> wrote in message news:%23JcE06%23FIHA.1164@.TK2MSFTNGP02.phx.gbl...
> Tibor:
> Yes, I tried what you suggested and still received the same error.
>
> Chai
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:A4D3631B-13AE-475C-907C-3A24D1AAF71D@.microsoft.com...
>> When I did, it faied within couple seconds & the error saying that Login
>> failed for user (TRSNT\dev02-SQL01) - that user is the SQL service
>> account that SQL Agent uses.
>> Did you add that Windows account to the SQL Server you try to access?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Chai" <chai@.trs.state.il.us> wrote in message
>> news:efV8Dq%23FIHA.1324@.TK2MSFTNGP06.phx.gbl...
>> Hello:
>> I have a SSIS package that will import data from one DB to another. I
>> would like to set it up as a nightly job. When I did, it faied within
>> couple seconds & the error saying that Login failed for user
>> (TRSNT\dev02-SQL01) - that user is the SQL service account that SQL Agent
>> uses.
>> Message
>> Executed as user: TRSNT\dev02-sql01. ... 9.00.3042.00 for 64-bit
>> Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started:
>> 11:01:42 AM Error: 2007-10-26 11:01:48.23 Code: 0xC0202009
>> Source: Copy Jbq Opr Sec Tables from TRSDev to TRSDev0203 Connection
>> manager "SourceConnectionOLEDB" Description: SSIS Error Code
>> DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
>> An OLE DB record is available. Source: "Microsoft SQL Native Client"
>> Hresult: 0x80040E4D Description: "Login failed for user
>> 'TRSNT\dev02-sql01'.". End Error Error: 2007-10-26 11:01:48.23 Code:
>> 0xC020801C Source: Data Flow Task Source - GenTRSEmailAddr [1]
>> Description: SSIS Error Code
>> DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
>> AcquireConnection method call to the connection manager
>> "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be
>> error messages posted before this with more information on why the ...
>> The package execution fa... The step failed.
>>
>> Am I missing something here?
>> Thank you in advavnce for your responses.
>>
>> Chai
>|||Is the SQL server agent start-up account a network account?
Especially when jobs are running across the network, use a network account
to start the SQL Server agent services.
"Tibor Karaszi" wrote:
> Can you log in interactively using the TRSNT\dev02-sql01 account and execute your package?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Chai" <chai@.trs.state.il.us> wrote in message news:%23JcE06%23FIHA.1164@.TK2MSFTNGP02.phx.gbl...
> > Tibor:
> >
> > Yes, I tried what you suggested and still received the same error.
> >
> >
> > Chai
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> > message news:A4D3631B-13AE-475C-907C-3A24D1AAF71D@.microsoft.com...
> >> When I did, it faied within couple seconds & the error saying that Login
> >> failed for user (TRSNT\dev02-SQL01) - that user is the SQL service
> >> account that SQL Agent uses.
> >>
> >> Did you add that Windows account to the SQL Server you try to access?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Chai" <chai@.trs.state.il.us> wrote in message
> >> news:efV8Dq%23FIHA.1324@.TK2MSFTNGP06.phx.gbl...
> >> Hello:
> >>
> >> I have a SSIS package that will import data from one DB to another. I
> >> would like to set it up as a nightly job. When I did, it faied within
> >> couple seconds & the error saying that Login failed for user
> >> (TRSNT\dev02-SQL01) - that user is the SQL service account that SQL Agent
> >> uses.
> >>
> >> Message
> >> Executed as user: TRSNT\dev02-sql01. ... 9.00.3042.00 for 64-bit
> >> Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started:
> >> 11:01:42 AM Error: 2007-10-26 11:01:48.23 Code: 0xC0202009
> >> Source: Copy Jbq Opr Sec Tables from TRSDev to TRSDev0203 Connection
> >> manager "SourceConnectionOLEDB" Description: SSIS Error Code
> >> DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
> >> An OLE DB record is available. Source: "Microsoft SQL Native Client"
> >> Hresult: 0x80040E4D Description: "Login failed for user
> >> 'TRSNT\dev02-sql01'.". End Error Error: 2007-10-26 11:01:48.23 Code:
> >> 0xC020801C Source: Data Flow Task Source - GenTRSEmailAddr [1]
> >> Description: SSIS Error Code
> >> DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
> >> AcquireConnection method call to the connection manager
> >> "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be
> >> error messages posted before this with more information on why the ...
> >> The package execution fa... The step failed.
> >>
> >>
> >> Am I missing something here?
> >>
> >> Thank you in advavnce for your responses.
> >>
> >>
> >> Chai
> >
> >sql

How to run SSIS package

How can I:

1) Create a Job in 2005 to run my SSIS project? Specifically syntax and where I would place the all in the job's properties

2) Can I run an SSIS package from the command prompt? what would be the syntax

You should read BOL as it has extensive information on these topics.

1) ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/541ee5ac-2c9f-4b74-b4f0-13b7bd5920b0.htm

This is general info on agent and you would use the SSIS subsystem for running SSIS packages.

2) ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/89edab2d-fb38-4e86-a61e-38621a214154.htm

Matt

|||this is a dumb question but how do I run those paths to get to your URLs above?|||Open BOL and put the URL into the URL box.

How to run solution from Visual Studio 2005

Hi.

Is it possible to run solution i have created by MS Visual Studio (filling table from file). I was thinking about schedueling job and launching the solution periodically from stored procedure.
Anyone has idea how these things work?

PKV

VS is used for designing a package. If you want to run your package on a schedule you should use SQL Agent to chedule a SSIS job to run the package.

How to run multiple dependent SQL Jobs with OSQL

I want to execute multiple SQL Server scheduled jobs using OSQL, but I only want to execute a job if the previous job completed. Is this possible? So far I am unable to prevent all jobs from starting before any have completed.

OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber1'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR

OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber2'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR

OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber3'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR
GOTO EXIT

:ERROR
ECHO *** ERROR *** Check Log File

:EXIT
*** JOB COMPLETED ***

DaveCreate a new job that uses separate steps consisting of an sp_start_job to launch one of the other jobs. Each step will execute in turn, launching the appropriate job.

-PatP|||I already tried that with no success. The logic looks as follows:

sp_start_job 'DBA Test' -- Inserts one record into a table
go
sp_start_job 'DBA Test2' -- Waits 20 seconds and inserts a record
go
sp_start_job 'DBA Test3' -- Insert a record
go

All jobs execute immediately.

Dave|||That looks suspiciously like a SQL script instead of a job with a set of job steps to me.

-PatP|||Lightbulb.

We had a DR test this week and apparently I'm still experiencing the affects. Thanks for the help. That should do the trick.

Dave|||I spoke too soon. I added sp_start_job 'job name' to three different job steps and the same problem occurs. All three jobs are executed before any job completes. I believe the reason is due to sp_start_job. SQL Server successfully executes the command, which simply starts the job. It doesn't care whether or not the job it started has completed.

Any suggestions.

Dave

How to run MDX query as scheduled job?

Hi,

Is there any way to run MDX

query as scheduled job on SQL Server 2005?

We need create local cubes by

weekly or monthly. How can we put “Create global cube” MDX query into scheduled

job?

Any info would be greatly

appreciated.

Thanks.

Yes. Create a new SQL Server Agent job and add a step with the step type of SQL Server Analysis Services Query. For this type of step, you need to specify the SSAS server to connect to, the database to run the query within, and the text for the query itself.

Should do exactly what you want...

HTH,

Dave Fackler

|||

Hi Dave,

Thanks for the quick replay.

Could you recommend any article or books that I can take look?

Thanks.

|||

I'm assuming you are asking about references for SSIS? If that is the case, check out Microsoft SQL Server 2005 Integration Services (SQL Server Series) by Kirk Haselden, Professional SQL Server 2005 Integration Services (Programmer to Programmer) by Brian Knight et al, and The Rational Guide to Extending SSIS 2005 with Script (Rational Guides) (Rational Guides) by Donald Farmer. All great books related to SSIS that should get you started...

HTH,

Dave Fackler

|||Thank you very much Dave for answered my question. It's helpful.

How to Run Job without Sysadmin Role

Hi All,
I know this question have already posted frequently, and I've tried all
the solution that they're suggested, but still doesn't work. I've
changed job owner and also setup the SQL Server Proxy account and
password into that Non-SyAdmin user, but still failed when run the job.
Is there any other solution? Please help me...
ThanksWhat error are you getting when the job fails?
-Sue
On 9 Nov 2005 18:13:06 -0800, resant_v@.yahoo.com wrote:

>Hi All,
>I know this question have already posted frequently, and I've tried all
>the solution that they're suggested, but still doesn't work. I've
>changed job owner and also setup the SQL Server Proxy account and
>password into that Non-SyAdmin user, but still failed when run the job.
>Is there any other solution? Please help me...
>
>Thanks|||"Non-SysAdmins have been denied permission to run CmdExec job steps"
But never mind, i've solved now but i'm getting new error :
"The specified @.job_name (Name of Job) doesn't exist".
This happen when I changed job owner into sysadmin user not that
Non-Sysadmin user.
Oh yeah, I use sysadmin user for proxy account, so when a Non-Sysadmin
user run the job, they will use the proxy account, right?
So, what should I do now?
Thanks

How to run an agent job in low priority

Hi Sql server experts,
Is there a way that I can tell the agent to run a job in the lowest
priority(resource wise)? like Unix NICE command.
I need to solve this problem we are having ASAP. I appreciate any prompt
response.
Thanks in advance!
Pingx
Not really. There is a feature in the next version of SQL Server called the
Resource Governor that will do this but not currently.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Pingx" <Pingx@.discussions.microsoft.com> wrote in message
news:309207E0-7B8A-4439-A2C1-4A244A9778D6@.microsoft.com...
> Hi Sql server experts,
> Is there a way that I can tell the agent to run a job in the lowest
> priority(resource wise)? like Unix NICE command.
> I need to solve this problem we are having ASAP. I appreciate any prompt
> response.
> Thanks in advance!
> Pingx
>
|||Andrew, thanks for your help.
Pingx
"Andrew J. Kelly" wrote:

> Not really. There is a feature in the next version of SQL Server called the
> Resource Governor that will do this but not currently.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Pingx" <Pingx@.discussions.microsoft.com> wrote in message
> news:309207E0-7B8A-4439-A2C1-4A244A9778D6@.microsoft.com...
>
|||"Pingx" <Pingx@.discussions.microsoft.com> wrote in message
news:14131D4A-9D05-4067-AEF8-3661133F8727@.microsoft.com...
> Andrew, thanks for your help.
>
What's your underlying problem and ultiamte problem?
There may be other solutions we can propose.
[vbcol=seagreen]
> Pingx
> "Andrew J. Kelly" wrote:
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
sql

How to run an Access file from a SQL job?

I have an Access file that I'm using to move some data around. I want to run it from a SQL job. How do I do that? The Access file runs a command using AutoExec, so it doesn't have any interaction or anything. It just runs and closes itself. Thanks.

pse don't duplicate.

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

Madhu

How to run Access file from SQL job?

I have an Access file that I'm using to move some data around. I want to run it from a SQL job. How do I do that? The Access file runs a command using AutoExec, so it doesn't have any interaction or anything. It just runs and closes itself. Thanks.

use XP_cmdshell

Executes a given command string as an operating-system command shell and returns any output as rows of text. Grants nonadministrative users permissions to execute xp_cmdshell.

check in BOL. IF its sql server 2005 you have to enable it using SQL Server 2005 features.

Madhu