In Data Transformation Service, Local Packages,
I need to pull data from a SAGE Line 50 v9 database into SQL . I have
created a simple DTS Package to pull one of the tables and prove the concept.
It is set
up so it drops the existing table, creates it again and then copies
the data across. It runs well when I run it as a package, however it
fails when I run it as a scheduled job.
It seems to drop the table, it then creates the table (this somehow tells me
that I
don't have a permissions problem because the table structure has to be
read from the SAGE database), but finally fails to copy the data.
Error
Step Error Description:The driver returned invalid (or failed to return)
SQL_DRIVER_ODBC_VER: 2.00 (Microsoft OLE DB Provider for ODBC Drivers
(80004005): Cannot find all files in data path)When you run a package interactively, it runs under YOUR permissions. When
you schedule a package it runs under the permissions for the login that SQL
Agent uses... Make sure the SQL Agent login has the appropriate permissions
everywhere
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"GEmsley" <GEmsley@.discussions.microsoft.com> wrote in message
news:56B83EDA-653F-4E3F-83FA-179A645F4824@.microsoft.com...
> In Data Transformation Service, Local Packages,
> I need to pull data from a SAGE Line 50 v9 database into SQL . I have
> created a simple DTS Package to pull one of the tables and prove the
concept.
> It is set
> up so it drops the existing table, creates it again and then copies
> the data across. It runs well when I run it as a package, however it
> fails when I run it as a scheduled job.
> It seems to drop the table, it then creates the table (this somehow tells
me
> that I
> don't have a permissions problem because the table structure has to be
> read from the SAGE database), but finally fails to copy the data.
> Error
> Step Error Description:The driver returned invalid (or failed to return)
> SQL_DRIVER_ODBC_VER: 2.00 (Microsoft OLE DB Provider for ODBC Drivers
> (80004005): Cannot find all files in data path)|||Thanks Wayne Spot on
"Wayne Snyder" wrote:
> When you run a package interactively, it runs under YOUR permissions. When
> you schedule a package it runs under the permissions for the login that SQL
> Agent uses... Make sure the SQL Agent login has the appropriate permissions
> everywhere
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "GEmsley" <GEmsley@.discussions.microsoft.com> wrote in message
> news:56B83EDA-653F-4E3F-83FA-179A645F4824@.microsoft.com...
> > In Data Transformation Service, Local Packages,
> > I need to pull data from a SAGE Line 50 v9 database into SQL . I have
> > created a simple DTS Package to pull one of the tables and prove the
> concept.
> > It is set
> > up so it drops the existing table, creates it again and then copies
> > the data across. It runs well when I run it as a package, however it
> > fails when I run it as a scheduled job.
> > It seems to drop the table, it then creates the table (this somehow tells
> me
> > that I
> > don't have a permissions problem because the table structure has to be
> > read from the SAGE database), but finally fails to copy the data.
> >
> > Error
> > Step Error Description:The driver returned invalid (or failed to return)
> > SQL_DRIVER_ODBC_VER: 2.00 (Microsoft OLE DB Provider for ODBC Drivers
> > (80004005): Cannot find all files in data path)
>
>
Showing posts with label created. Show all posts
Showing posts with label created. Show all posts
Friday, March 30, 2012
scheduling ssis package
Hi there!!
I have created a ssis package and now i want to schedule it on SQL Server Agent.
How do we create new job for SSIS package?
Rahul Kumar
hi
gotta answer at:
http://msdn2.microsoft.com/en-us/library/ms139805.aspx
thanks and regards
Rahul Kumar
sqlScheduling SQL 2005 SSIS packages
I have created and run SSIS packages from my workstation that are successful. When I Build and then Deploy the same package to the server and schedule it, it fails. I am using SQL authentication connecting from a SQL 2000 database to a SQL 2005 database via OLE DB connections.
The following is the exported result of the package...
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
05/09/2007 15:46:05,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by User JDEREP. The last step to run was step 1 (step1).,00:00:01,0,0,,,,0
05/09/2007 15:46:05,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOIL\vector. Error: 2007-05-09 15:46:06.38 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:46:06.38 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:46:06.38 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:46:05 PM Finished: 3:46:06 PM Elapsed: 0.75 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
05/09/2007 15:30:00,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by Schedule 29 (AMS_EnterpriseWarehouse). The last step to run was step 1 (step1).,00:00:01,0,0,,,,0
05/09/2007 15:30:00,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOIL\vector. Error: 2007-05-09 15:30:01.15 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:30:01.15 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:30:01.15 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:30:00 PM Finished: 3:30:01 PM Elapsed: 0.734 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
05/09/2007 15:20:00,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by Schedule 29 (AMS_EnterpriseWarehouse). The last step to run was step 1 (step1).,00:00:01,0,0,,,,0
05/09/2007 15:20:00,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOIL\vector. Error: 2007-05-09 15:20:01.55 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:20:01.55 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:20:01.55 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:20:00 PM Finished: 3:20:01 PM Elapsed: 0.703 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
05/09/2007 15:18:00,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by Schedule 29 (AMS_EnterpriseWarehouse). The last step to run was step 1 (step1).,00:00:01,0,0,,,,0
05/09/2007 15:18:00,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOIL\vector. Error: 2007-05-09 15:18:01.53 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:18:01.53 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:18:01.53 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:18:00 PM Finished: 3:18:01 PM Elapsed: 0.766 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
Any help would be greatly appreciated...
Jim
Additionally, when I run this from the command line, I get the following
DTSER_FAILURE (1)
Jim
How are you setting the database connections in your package?|||The package was created in Visual Studio 2005, the database connections were created within the package under Data Sources by using the Data Source Wizard. Then in the editor for the Execute SQL Task and the Data Flow task, the connection is selected.
The Execute SQL Task is a SQL statement to trucate the destination table.
Then on the Data Flow I select a source table and a destination table for the ETL - actually just copying the data from one table in one db to another table in a different db.
|||Take a look at the topic "Setting the Protection Level of Packages" in Books Online. Depending on the ProtectionLevel setting, the package may not be saving the password for the database, or it may require it to be run under your user account to decrypt the password.|||Hello,
To run packages as sql agent jobs, I have deployed the package in the ssis server in sql server MSDB with the option 'Rely on server storage for encryption'
I have also added packages that use a package configuration file, the packages refer to the config file via an environment variable. the environment variable is set on the server. The package is also deployed in the ssis server in sql server MSDB with the option rely on server storage for encryption. I have not yet tested it a job that runs this 2nd type of packages , realy uses this package configuration file, or if it is using whatever happend to be in the connection string of the package when I deployed it.
|||does it work?|||Yes,
the packages definitely use the config file.
Scheduling SQL 2005 SSIS packages
I have created and run SSIS packages from my workstation that are successful. When I Build and then Deploy the same package to the server and schedule it, it fails. I am using SQL authentication connecting from a SQL 2000 database to a SQL 2005 database via OLE DB connections.
The following is the exported result of the package...
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
05/09/2007 15:46:05,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by User JDEREP. The last step to run was step 1 (step1).,00:00:01,0,0,,,,0
05/09/2007 15:46:05,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOIL\vector. Error: 2007-05-09 15:46:06.38 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:46:06.38 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:46:06.38 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:46:05 PM Finished: 3:46:06 PM Elapsed: 0.75 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
05/09/2007 15:30:00,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by Schedule 29 (AMS_EnterpriseWarehouse). The last step to run was step 1 (step1).,00:00:01,0,0,,,,0
05/09/2007 15:30:00,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOIL\vector. Error: 2007-05-09 15:30:01.15 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:30:01.15 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:30:01.15 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:30:00 PM Finished: 3:30:01 PM Elapsed: 0.734 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
05/09/2007 15:20:00,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by Schedule 29 (AMS_EnterpriseWarehouse). The last step to run was step 1 (step1).,00:00:01,0,0,,,,0
05/09/2007 15:20:00,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOIL\vector. Error: 2007-05-09 15:20:01.55 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:20:01.55 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:20:01.55 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:20:00 PM Finished: 3:20:01 PM Elapsed: 0.703 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
05/09/2007 15:18:00,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by Schedule 29 (AMS_EnterpriseWarehouse). The last step to run was step 1 (step1).,00:00:01,0,0,,,,0
05/09/2007 15:18:00,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOIL\vector. Error: 2007-05-09 15:18:01.53 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:18:01.53 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:18:01.53 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:18:00 PM Finished: 3:18:01 PM Elapsed: 0.766 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
Any help would be greatly appreciated...
Jim
Additionally, when I run this from the command line, I get the following
DTSER_FAILURE (1)
Jim
How are you setting the database connections in your package?|||The package was created in Visual Studio 2005, the database connections were created within the package under Data Sources by using the Data Source Wizard. Then in the editor for the Execute SQL Task and the Data Flow task, the connection is selected.
The Execute SQL Task is a SQL statement to trucate the destination table.
Then on the Data Flow I select a source table and a destination table for the ETL - actually just copying the data from one table in one db to another table in a different db.
|||Take a look at the topic "Setting the Protection Level of Packages" in Books Online. Depending on the ProtectionLevel setting, the package may not be saving the password for the database, or it may require it to be run under your user account to decrypt the password.|||Hello,
To run packages as sql agent jobs, I have deployed the package in the ssis server in sql server MSDB with the option 'Rely on server storage for encryption'
I have also added packages that use a package configuration file, the packages refer to the config file via an environment variable. the environment variable is set on the server. The package is also deployed in the ssis server in sql server MSDB with the option rely on server storage for encryption. I have not yet tested it a job that runs this 2nd type of packages , realy uses this package configuration file, or if it is using whatever happend to be in the connection string of the package when I deployed it.
|||does it work?|||Yes,
the packages definitely use the config file.
Wednesday, March 28, 2012
Scheduling not working
Hello.
Does RS have to be installed on the same machine as SQL Server in order to
get scheduling to work?
I created a schedule to run every minute (for testing). The time has come
and gone, and the Next Run time on the Shared Schedules screen isn't
changing, and the Last Run column shows Never. So scheduling isn't working.
Any ideas?
Thanks in advance,
MikeNo, I don't think so. However, scheduling is dependent on SQL Agent running
so make sure it is running on the database server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mike" <milop@.slomins.com> wrote in message
news:OCyTWKvuFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hello.
> Does RS have to be installed on the same machine as SQL Server in order to
> get scheduling to work?
> I created a schedule to run every minute (for testing). The time has come
> and gone, and the Next Run time on the Shared Schedules screen isn't
> changing, and the Last Run column shows Never. So scheduling isn't
> working.
> Any ideas?
> Thanks in advance,
> Mike
>|||Hi, Mike.
1) RS CAN be on different machine.
2)Make sure that the SQL Agent is running.
Regards, ProJester,
MSN Programmer.
"mike" wrote:
> Hello.
> Does RS have to be installed on the same machine as SQL Server in order to
> get scheduling to work?
> I created a schedule to run every minute (for testing). The time has come
> and gone, and the Next Run time on the Shared Schedules screen isn't
> changing, and the Last Run column shows Never. So scheduling isn't working.
> Any ideas?
> Thanks in advance,
> Mike
>
>
Does RS have to be installed on the same machine as SQL Server in order to
get scheduling to work?
I created a schedule to run every minute (for testing). The time has come
and gone, and the Next Run time on the Shared Schedules screen isn't
changing, and the Last Run column shows Never. So scheduling isn't working.
Any ideas?
Thanks in advance,
MikeNo, I don't think so. However, scheduling is dependent on SQL Agent running
so make sure it is running on the database server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mike" <milop@.slomins.com> wrote in message
news:OCyTWKvuFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hello.
> Does RS have to be installed on the same machine as SQL Server in order to
> get scheduling to work?
> I created a schedule to run every minute (for testing). The time has come
> and gone, and the Next Run time on the Shared Schedules screen isn't
> changing, and the Last Run column shows Never. So scheduling isn't
> working.
> Any ideas?
> Thanks in advance,
> Mike
>|||Hi, Mike.
1) RS CAN be on different machine.
2)Make sure that the SQL Agent is running.
Regards, ProJester,
MSN Programmer.
"mike" wrote:
> Hello.
> Does RS have to be installed on the same machine as SQL Server in order to
> get scheduling to work?
> I created a schedule to run every minute (for testing). The time has come
> and gone, and the Next Run time on the Shared Schedules screen isn't
> changing, and the Last Run column shows Never. So scheduling isn't working.
> Any ideas?
> Thanks in advance,
> Mike
>
>
scheduling jobs that query linked servers
I have some SQL Servers that are linked and have created
a stored procedure on my primary SQL Server that queries
the linked servers. I also created a new login ID for
this.
I can run the stored proc anytime and it works great.
However, when I attempt to execute the stored proc from a
scheduled job I get the message: "Remote access not
allowed for Windows NT user activated by SETUSER."
I don't see how to get around this and still use the job
scheduler. Do I really have to use isql and put it in
scheduler software running outside MSSQL?
Can I run this T-SQL inside the scheduler with a
configuration switch somewhere? Or is this a "feature"
of MSSQL?
thanksIf you go to step properties, there is a drop down
box "run as user", from that list select the login that
has access to the linked server, that login also need to
have some access privileges to the database from which the
step is run.
hth.
>--Original Message--
>I have some SQL Servers that are linked and have created
>a stored procedure on my primary SQL Server that queries
>the linked servers. I also created a new login ID for
>this.
>I can run the stored proc anytime and it works great.
>However, when I attempt to execute the stored proc from a
>scheduled job I get the message: "Remote access not
>allowed for Windows NT user activated by SETUSER."
>I don't see how to get around this and still use the job
>scheduler. Do I really have to use isql and put it in
>scheduler software running outside MSSQL?
>Can I run this T-SQL inside the scheduler with a
>configuration switch somewhere? Or is this a "feature"
>of MSSQL?
>thanks
>
>.
>|||I tried that before and it did not work. I have not been
able to run this via any user in that drop down.
I have a situation where I can login via isql (sql server
authentication - not Windows auth.) and I am able to
execute the stored proc just fine. The stored proc
queries linked servers and works fine.
However when I try to use the Job Scheduler, I get the
error: "Remote access not allowed for Windows NT user
activated by SETUSER." When I attempt to run the job as
[self] I get: "Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'."
Unless there is a hidden config option somewhere it
appears that the job scheduler cannot be used for remote
access to linked servers unless via a windows auth. login.
Is that true? If this is supposed to be a "security
feature" then it is pointless as far as I can tell.
We're on 2000 sp 3a
Jeff
>--Original Message--
>If you go to step properties, there is a drop down
>box "run as user", from that list select the login that
>has access to the linked server, that login also need to
>have some access privileges to the database from which
the
>step is run.
>hth.
>>--Original Message--
>>I have some SQL Servers that are linked and have
created
>>a stored procedure on my primary SQL Server that
queries
>>the linked servers. I also created a new login ID for
>>this.
>>I can run the stored proc anytime and it works great.
>>However, when I attempt to execute the stored proc from
a
>>scheduled job I get the message: "Remote access not
>>allowed for Windows NT user activated by SETUSER."
>>I don't see how to get around this and still use the
job
>>scheduler. Do I really have to use isql and put it in
>>scheduler software running outside MSSQL?
>>Can I run this T-SQL inside the scheduler with a
>>configuration switch somewhere? Or is this a "feature"
>>of MSSQL?
>>thanks
>>
>>.
>.
>|||I tried it myself and came to same conclusion as you for
now that you have to use windows auth. to access linked
servers via a jobschedule.
And looks like the problem is with the setuser command,
because when using nt auth. sqlagent is not using setuser
command, and if you use sql auth. then sqlagent is using
the setuser command. I tried this in QA also using setuser
for an nt auth. user who has sysadmin rights on both
servers and came back with the same error message "emote
access not allowed for Windows NT user activated by
SETUSER."
I will post more if I find something.
>--Original Message--
>I tried that before and it did not work. I have not been
>able to run this via any user in that drop down.
>I have a situation where I can login via isql (sql server
>authentication - not Windows auth.) and I am able to
>execute the stored proc just fine. The stored proc
>queries linked servers and works fine.
>However when I try to use the Job Scheduler, I get the
>error: "Remote access not allowed for Windows NT user
>activated by SETUSER." When I attempt to run the job as
>[self] I get: "Login failed for user 'NT
>AUTHORITY\ANONYMOUS LOGON'."
>Unless there is a hidden config option somewhere it
>appears that the job scheduler cannot be used for remote
>access to linked servers unless via a windows auth. login.
>Is that true? If this is supposed to be a "security
>feature" then it is pointless as far as I can tell.
>We're on 2000 sp 3a
>Jeff
>>--Original Message--
>>If you go to step properties, there is a drop down
>>box "run as user", from that list select the login that
>>has access to the linked server, that login also need to
>>have some access privileges to the database from which
>the
>>step is run.
>>hth.
>>--Original Message--
>>I have some SQL Servers that are linked and have
>created
>>a stored procedure on my primary SQL Server that
>queries
>>the linked servers. I also created a new login ID for
>>this.
>>I can run the stored proc anytime and it works great.
>>However, when I attempt to execute the stored proc from
>a
>>scheduled job I get the message: "Remote access not
>>allowed for Windows NT user activated by SETUSER."
>>I don't see how to get around this and still use the
>job
>>scheduler. Do I really have to use isql and put it in
>>scheduler software running outside MSSQL?
>>Can I run this T-SQL inside the scheduler with a
>>configuration switch somewhere? Or is this a "feature"
>>of MSSQL?
>>thanks
>>
>>.
>>.
>.
>|||This just gets stranger and stranger. On a hunch I set
up execution of my stored proc by a DTS package. I
schedule it and voila! it works!!?! It's not my
preferred way of doing this but if this is the kludge I
need to use to employ our of these inconsistent
behaviors, I'm going to use it.
Jeff
>--Original Message--
>I tried it myself and came to same conclusion as you for
>now that you have to use windows auth. to access linked
>servers via a jobschedule.
>And looks like the problem is with the setuser command,
>because when using nt auth. sqlagent is not using
setuser
>command, and if you use sql auth. then sqlagent is using
>the setuser command. I tried this in QA also using
setuser
>for an nt auth. user who has sysadmin rights on both
>servers and came back with the same error
message "emote
>access not allowed for Windows NT user activated by
>SETUSER."
>I will post more if I find something.
>
>>--Original Message--
>>I tried that before and it did not work. I have not
been
>>able to run this via any user in that drop down.
>>I have a situation where I can login via isql (sql
server
>>authentication - not Windows auth.) and I am able to
>>execute the stored proc just fine. The stored proc
>>queries linked servers and works fine.
>>However when I try to use the Job Scheduler, I get the
>>error: "Remote access not allowed for Windows NT user
>>activated by SETUSER." When I attempt to run the job
as
>>[self] I get: "Login failed for user 'NT
>>AUTHORITY\ANONYMOUS LOGON'."
>>Unless there is a hidden config option somewhere it
>>appears that the job scheduler cannot be used for
remote
>>access to linked servers unless via a windows auth.
login.
>>Is that true? If this is supposed to be a "security
>>feature" then it is pointless as far as I can tell.
>>We're on 2000 sp 3a
>>Jeff
>>--Original Message--
>>If you go to step properties, there is a drop down
>>box "run as user", from that list select the login
that
>>has access to the linked server, that login also need
to
>>have some access privileges to the database from which
>>the
>>step is run.
>>hth.
>>--Original Message--
>>I have some SQL Servers that are linked and have
>>created
>>a stored procedure on my primary SQL Server that
>>queries
>>the linked servers. I also created a new login ID
for
>>this.
>>I can run the stored proc anytime and it works
great.
>>However, when I attempt to execute the stored proc
from
>>a
>>scheduled job I get the message: "Remote access not
>>allowed for Windows NT user activated by SETUSER."
>>I don't see how to get around this and still use the
>>job
>>scheduler. Do I really have to use isql and put it
in
>>scheduler software running outside MSSQL?
>>Can I run this T-SQL inside the scheduler with a
>>configuration switch somewhere? Or is this
a "feature"
>>of MSSQL?
>>thanks
>>
>>.
>>.
>>.
>.
>
a stored procedure on my primary SQL Server that queries
the linked servers. I also created a new login ID for
this.
I can run the stored proc anytime and it works great.
However, when I attempt to execute the stored proc from a
scheduled job I get the message: "Remote access not
allowed for Windows NT user activated by SETUSER."
I don't see how to get around this and still use the job
scheduler. Do I really have to use isql and put it in
scheduler software running outside MSSQL?
Can I run this T-SQL inside the scheduler with a
configuration switch somewhere? Or is this a "feature"
of MSSQL?
thanksIf you go to step properties, there is a drop down
box "run as user", from that list select the login that
has access to the linked server, that login also need to
have some access privileges to the database from which the
step is run.
hth.
>--Original Message--
>I have some SQL Servers that are linked and have created
>a stored procedure on my primary SQL Server that queries
>the linked servers. I also created a new login ID for
>this.
>I can run the stored proc anytime and it works great.
>However, when I attempt to execute the stored proc from a
>scheduled job I get the message: "Remote access not
>allowed for Windows NT user activated by SETUSER."
>I don't see how to get around this and still use the job
>scheduler. Do I really have to use isql and put it in
>scheduler software running outside MSSQL?
>Can I run this T-SQL inside the scheduler with a
>configuration switch somewhere? Or is this a "feature"
>of MSSQL?
>thanks
>
>.
>|||I tried that before and it did not work. I have not been
able to run this via any user in that drop down.
I have a situation where I can login via isql (sql server
authentication - not Windows auth.) and I am able to
execute the stored proc just fine. The stored proc
queries linked servers and works fine.
However when I try to use the Job Scheduler, I get the
error: "Remote access not allowed for Windows NT user
activated by SETUSER." When I attempt to run the job as
[self] I get: "Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'."
Unless there is a hidden config option somewhere it
appears that the job scheduler cannot be used for remote
access to linked servers unless via a windows auth. login.
Is that true? If this is supposed to be a "security
feature" then it is pointless as far as I can tell.
We're on 2000 sp 3a
Jeff
>--Original Message--
>If you go to step properties, there is a drop down
>box "run as user", from that list select the login that
>has access to the linked server, that login also need to
>have some access privileges to the database from which
the
>step is run.
>hth.
>>--Original Message--
>>I have some SQL Servers that are linked and have
created
>>a stored procedure on my primary SQL Server that
queries
>>the linked servers. I also created a new login ID for
>>this.
>>I can run the stored proc anytime and it works great.
>>However, when I attempt to execute the stored proc from
a
>>scheduled job I get the message: "Remote access not
>>allowed for Windows NT user activated by SETUSER."
>>I don't see how to get around this and still use the
job
>>scheduler. Do I really have to use isql and put it in
>>scheduler software running outside MSSQL?
>>Can I run this T-SQL inside the scheduler with a
>>configuration switch somewhere? Or is this a "feature"
>>of MSSQL?
>>thanks
>>
>>.
>.
>|||I tried it myself and came to same conclusion as you for
now that you have to use windows auth. to access linked
servers via a jobschedule.
And looks like the problem is with the setuser command,
because when using nt auth. sqlagent is not using setuser
command, and if you use sql auth. then sqlagent is using
the setuser command. I tried this in QA also using setuser
for an nt auth. user who has sysadmin rights on both
servers and came back with the same error message "emote
access not allowed for Windows NT user activated by
SETUSER."
I will post more if I find something.
>--Original Message--
>I tried that before and it did not work. I have not been
>able to run this via any user in that drop down.
>I have a situation where I can login via isql (sql server
>authentication - not Windows auth.) and I am able to
>execute the stored proc just fine. The stored proc
>queries linked servers and works fine.
>However when I try to use the Job Scheduler, I get the
>error: "Remote access not allowed for Windows NT user
>activated by SETUSER." When I attempt to run the job as
>[self] I get: "Login failed for user 'NT
>AUTHORITY\ANONYMOUS LOGON'."
>Unless there is a hidden config option somewhere it
>appears that the job scheduler cannot be used for remote
>access to linked servers unless via a windows auth. login.
>Is that true? If this is supposed to be a "security
>feature" then it is pointless as far as I can tell.
>We're on 2000 sp 3a
>Jeff
>>--Original Message--
>>If you go to step properties, there is a drop down
>>box "run as user", from that list select the login that
>>has access to the linked server, that login also need to
>>have some access privileges to the database from which
>the
>>step is run.
>>hth.
>>--Original Message--
>>I have some SQL Servers that are linked and have
>created
>>a stored procedure on my primary SQL Server that
>queries
>>the linked servers. I also created a new login ID for
>>this.
>>I can run the stored proc anytime and it works great.
>>However, when I attempt to execute the stored proc from
>a
>>scheduled job I get the message: "Remote access not
>>allowed for Windows NT user activated by SETUSER."
>>I don't see how to get around this and still use the
>job
>>scheduler. Do I really have to use isql and put it in
>>scheduler software running outside MSSQL?
>>Can I run this T-SQL inside the scheduler with a
>>configuration switch somewhere? Or is this a "feature"
>>of MSSQL?
>>thanks
>>
>>.
>>.
>.
>|||This just gets stranger and stranger. On a hunch I set
up execution of my stored proc by a DTS package. I
schedule it and voila! it works!!?! It's not my
preferred way of doing this but if this is the kludge I
need to use to employ our of these inconsistent
behaviors, I'm going to use it.
Jeff
>--Original Message--
>I tried it myself and came to same conclusion as you for
>now that you have to use windows auth. to access linked
>servers via a jobschedule.
>And looks like the problem is with the setuser command,
>because when using nt auth. sqlagent is not using
setuser
>command, and if you use sql auth. then sqlagent is using
>the setuser command. I tried this in QA also using
setuser
>for an nt auth. user who has sysadmin rights on both
>servers and came back with the same error
message "emote
>access not allowed for Windows NT user activated by
>SETUSER."
>I will post more if I find something.
>
>>--Original Message--
>>I tried that before and it did not work. I have not
been
>>able to run this via any user in that drop down.
>>I have a situation where I can login via isql (sql
server
>>authentication - not Windows auth.) and I am able to
>>execute the stored proc just fine. The stored proc
>>queries linked servers and works fine.
>>However when I try to use the Job Scheduler, I get the
>>error: "Remote access not allowed for Windows NT user
>>activated by SETUSER." When I attempt to run the job
as
>>[self] I get: "Login failed for user 'NT
>>AUTHORITY\ANONYMOUS LOGON'."
>>Unless there is a hidden config option somewhere it
>>appears that the job scheduler cannot be used for
remote
>>access to linked servers unless via a windows auth.
login.
>>Is that true? If this is supposed to be a "security
>>feature" then it is pointless as far as I can tell.
>>We're on 2000 sp 3a
>>Jeff
>>--Original Message--
>>If you go to step properties, there is a drop down
>>box "run as user", from that list select the login
that
>>has access to the linked server, that login also need
to
>>have some access privileges to the database from which
>>the
>>step is run.
>>hth.
>>--Original Message--
>>I have some SQL Servers that are linked and have
>>created
>>a stored procedure on my primary SQL Server that
>>queries
>>the linked servers. I also created a new login ID
for
>>this.
>>I can run the stored proc anytime and it works
great.
>>However, when I attempt to execute the stored proc
from
>>a
>>scheduled job I get the message: "Remote access not
>>allowed for Windows NT user activated by SETUSER."
>>I don't see how to get around this and still use the
>>job
>>scheduler. Do I really have to use isql and put it
in
>>scheduler software running outside MSSQL?
>>Can I run this T-SQL inside the scheduler with a
>>configuration switch somewhere? Or is this
a "feature"
>>of MSSQL?
>>thanks
>>
>>.
>>.
>>.
>.
>
scheduling job in sql server 2005
Hi,
I created a scheduled job in sql server 2005 which takes a daily backup of database.
My problem is this job does not run automatically.but when i execute it manually it runs on that instance.
do i need to activate any servrice to have scheduling of jobs running perfectly on my machine ?
Please guide me on this.
Thanks,
Prashant
The SQL Agent Server must be running (Check in Services) and have OS permission to the backup file location.
The Job must be 'Enabled'.
The Schedule must be 'Enabled'
Scheduling DTS Package in SQL 2005
We have an issue scheduling a DTS package we have created... We can run the
package manually in SSIS with NO PROBLEMS... but when we attempt to
schedule the package we receive the following error:
Date 5/9/2007 6:12:57 PM
Log SQL Agent (Current - 5/9/2007 6:12:00 PM)
Message
[LOG] Unable to read local eventlog (reason: The parameter is incorrect)
What configuration or settings are we missing to cause this to happen when
scheduled... If we execute it manually it runs fine.
Job is created
Credentials are setup
Proxies is setup under SSIS Package Execution
--
Using ASP, SQL2005 and DW8Hi Daniel
"Daniel" wrote:
> We have an issue scheduling a DTS package we have created... We can run the
> package manually in SSIS with NO PROBLEMS... but when we attempt to
> schedule the package we receive the following error:
> Date 5/9/2007 6:12:57 PM
> Log SQL Agent (Current - 5/9/2007 6:12:00 PM)
> Message
> [LOG] Unable to read local eventlog (reason: The parameter is incorrect)
> What configuration or settings are we missing to cause this to happen when
> scheduled... If we execute it manually it runs fine.
> Job is created
> Credentials are setup
> Proxies is setup under SSIS Package Execution
>
> --
> Using ASP, SQL2005 and DW8
>
What version of SQL 2005 are you using? According to
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=137778&SiteID=1 SP1
fixed something that caused this!
John
package manually in SSIS with NO PROBLEMS... but when we attempt to
schedule the package we receive the following error:
Date 5/9/2007 6:12:57 PM
Log SQL Agent (Current - 5/9/2007 6:12:00 PM)
Message
[LOG] Unable to read local eventlog (reason: The parameter is incorrect)
What configuration or settings are we missing to cause this to happen when
scheduled... If we execute it manually it runs fine.
Job is created
Credentials are setup
Proxies is setup under SSIS Package Execution
--
Using ASP, SQL2005 and DW8Hi Daniel
"Daniel" wrote:
> We have an issue scheduling a DTS package we have created... We can run the
> package manually in SSIS with NO PROBLEMS... but when we attempt to
> schedule the package we receive the following error:
> Date 5/9/2007 6:12:57 PM
> Log SQL Agent (Current - 5/9/2007 6:12:00 PM)
> Message
> [LOG] Unable to read local eventlog (reason: The parameter is incorrect)
> What configuration or settings are we missing to cause this to happen when
> scheduled... If we execute it manually it runs fine.
> Job is created
> Credentials are setup
> Proxies is setup under SSIS Package Execution
>
> --
> Using ASP, SQL2005 and DW8
>
What version of SQL 2005 are you using? According to
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=137778&SiteID=1 SP1
fixed something that caused this!
John
Scheduling DTS Package in SQL 2005
We have an issue scheduling a DTS package we have created... We can run the
package manually in SSIS with NO PROBLEMS... but when we attempt to
schedule the package we receive the following error:
Date 5/9/2007 6:12:57 PM
Log SQL Agent (Current - 5/9/2007 6:12:00 PM)
Message
[LOG] Unable to read local eventlog (reason: The parameter is incorrect)
What configuration or settings are we missing to cause this to happen when
scheduled... If we execute it manually it runs fine.
Job is created
Credentials are setup
Proxies is setup under SSIS Package Execution
Using ASP, SQL2005 and DW8Hi Daniel
"Daniel" wrote:
> We have an issue scheduling a DTS package we have created... We can run th
e
> package manually in SSIS with NO PROBLEMS... but when we attempt to
> schedule the package we receive the following error:
> Date 5/9/2007 6:12:57 PM
> Log SQL Agent (Current - 5/9/2007 6:12:00 PM)
> Message
> [LOG] Unable to read local eventlog (reason: The parameter is incorrec
t)
> What configuration or settings are we missing to cause this to happen when
> scheduled... If we execute it manually it runs fine.
> Job is created
> Credentials are setup
> Proxies is setup under SSIS Package Execution
>
> --
> Using ASP, SQL2005 and DW8
>
What version of SQL 2005 are you using? According to
http://forums.microsoft.com/MSDN/Sh...137778&SiteID=1 SP1
fixed something that caused this!
John
package manually in SSIS with NO PROBLEMS... but when we attempt to
schedule the package we receive the following error:
Date 5/9/2007 6:12:57 PM
Log SQL Agent (Current - 5/9/2007 6:12:00 PM)
Message
[LOG] Unable to read local eventlog (reason: The parameter is incorrect)
What configuration or settings are we missing to cause this to happen when
scheduled... If we execute it manually it runs fine.
Job is created
Credentials are setup
Proxies is setup under SSIS Package Execution
Using ASP, SQL2005 and DW8Hi Daniel
"Daniel" wrote:
> We have an issue scheduling a DTS package we have created... We can run th
e
> package manually in SSIS with NO PROBLEMS... but when we attempt to
> schedule the package we receive the following error:
> Date 5/9/2007 6:12:57 PM
> Log SQL Agent (Current - 5/9/2007 6:12:00 PM)
> Message
> [LOG] Unable to read local eventlog (reason: The parameter is incorrec
t)
> What configuration or settings are we missing to cause this to happen when
> scheduled... If we execute it manually it runs fine.
> Job is created
> Credentials are setup
> Proxies is setup under SSIS Package Execution
>
> --
> Using ASP, SQL2005 and DW8
>
What version of SQL 2005 are you using? According to
http://forums.microsoft.com/MSDN/Sh...137778&SiteID=1 SP1
fixed something that caused this!
John
Scheduling DTS Package in SQL 2005
We have an issue scheduling a DTS package we have created... We can run the
package manually in SSIS with NO PROBLEMS... but when we attempt to
schedule the package we receive the following error:
Date 5/9/2007 6:12:57 PM
Log SQL Agent (Current - 5/9/2007 6:12:00 PM)
Message
[LOG] Unable to read local eventlog (reason: The parameter is incorrect)
What configuration or settings are we missing to cause this to happen when
scheduled... If we execute it manually it runs fine.
Job is created
Credentials are setup
Proxies is setup under SSIS Package Execution
Using ASP, SQL2005 and DW8
Hi Daniel
"Daniel" wrote:
> We have an issue scheduling a DTS package we have created... We can run the
> package manually in SSIS with NO PROBLEMS... but when we attempt to
> schedule the package we receive the following error:
> Date 5/9/2007 6:12:57 PM
> Log SQL Agent (Current - 5/9/2007 6:12:00 PM)
> Message
> [LOG] Unable to read local eventlog (reason: The parameter is incorrect)
> What configuration or settings are we missing to cause this to happen when
> scheduled... If we execute it manually it runs fine.
> Job is created
> Credentials are setup
> Proxies is setup under SSIS Package Execution
>
> --
> Using ASP, SQL2005 and DW8
>
What version of SQL 2005 are you using? According to
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=137778&SiteID=1 SP1
fixed something that caused this!
John
sql
package manually in SSIS with NO PROBLEMS... but when we attempt to
schedule the package we receive the following error:
Date 5/9/2007 6:12:57 PM
Log SQL Agent (Current - 5/9/2007 6:12:00 PM)
Message
[LOG] Unable to read local eventlog (reason: The parameter is incorrect)
What configuration or settings are we missing to cause this to happen when
scheduled... If we execute it manually it runs fine.
Job is created
Credentials are setup
Proxies is setup under SSIS Package Execution
Using ASP, SQL2005 and DW8
Hi Daniel
"Daniel" wrote:
> We have an issue scheduling a DTS package we have created... We can run the
> package manually in SSIS with NO PROBLEMS... but when we attempt to
> schedule the package we receive the following error:
> Date 5/9/2007 6:12:57 PM
> Log SQL Agent (Current - 5/9/2007 6:12:00 PM)
> Message
> [LOG] Unable to read local eventlog (reason: The parameter is incorrect)
> What configuration or settings are we missing to cause this to happen when
> scheduled... If we execute it manually it runs fine.
> Job is created
> Credentials are setup
> Proxies is setup under SSIS Package Execution
>
> --
> Using ASP, SQL2005 and DW8
>
What version of SQL 2005 are you using? According to
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=137778&SiteID=1 SP1
fixed something that caused this!
John
sql
scheduling dts jobs fails
Hello,
Could somebody tell me what i'm doing wrong. I have created a dts job from
the client console on my computer. Next i want to schedule the dts job, but
whenever it starts it is always failing.
Is it because i've created the dts job on my computer? I can see the owner
of the job is the name of my computer/username.
Greetings,
Zeke
I guess it has to do with security. When you schedule a job, it is executed
under different user account, not under yours. if you are a member of the
sysadmin role, then it is executed using SQL Server Agent's account.
Otherwise you can use so-called proxy account (check more in Books OnLine).
And the account used might not have permissions (maybe on file system as
well, not just SQL Server, depends on whatever the package is doing). Or it
does not see the drives you mapped using your account, etc.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Ezekil" <ezekiel@.lycos.nl> wrote in message
news:%23EwqWe1MEHA.2540@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Could somebody tell me what i'm doing wrong. I have created a dts job from
> the client console on my computer. Next i want to schedule the dts job,
but
> whenever it starts it is always failing.
> Is it because i've created the dts job on my computer? I can see the owner
> of the job is the name of my computer/username.
> Greetings,
> Zeke
>
Could somebody tell me what i'm doing wrong. I have created a dts job from
the client console on my computer. Next i want to schedule the dts job, but
whenever it starts it is always failing.
Is it because i've created the dts job on my computer? I can see the owner
of the job is the name of my computer/username.
Greetings,
Zeke
I guess it has to do with security. When you schedule a job, it is executed
under different user account, not under yours. if you are a member of the
sysadmin role, then it is executed using SQL Server Agent's account.
Otherwise you can use so-called proxy account (check more in Books OnLine).
And the account used might not have permissions (maybe on file system as
well, not just SQL Server, depends on whatever the package is doing). Or it
does not see the drives you mapped using your account, etc.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Ezekil" <ezekiel@.lycos.nl> wrote in message
news:%23EwqWe1MEHA.2540@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Could somebody tell me what i'm doing wrong. I have created a dts job from
> the client console on my computer. Next i want to schedule the dts job,
but
> whenever it starts it is always failing.
> Is it because i've created the dts job on my computer? I can see the owner
> of the job is the name of my computer/username.
> Greetings,
> Zeke
>
scheduling dts jobs fails
Hello,
Could somebody tell me what i'm doing wrong. I have created a dts job from
the client console on my computer. Next i want to schedule the dts job, but
whenever it starts it is always failing.
Is it because i've created the dts job on my computer? I can see the owner
of the job is the name of my computer/username.
Greetings,
ZekeI guess it has to do with security. When you schedule a job, it is executed
under different user account, not under yours. if you are a member of the
sysadmin role, then it is executed using SQL Server Agent's account.
Otherwise you can use so-called proxy account (check more in Books OnLine).
And the account used might not have permissions (maybe on file system as
well, not just SQL Server, depends on whatever the package is doing). Or it
does not see the drives you mapped using your account, etc.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Ezekil" <ezekiel@.lycos.nl> wrote in message
news:%23EwqWe1MEHA.2540@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Could somebody tell me what i'm doing wrong. I have created a dts job from
> the client console on my computer. Next i want to schedule the dts job,
but
> whenever it starts it is always failing.
> Is it because i've created the dts job on my computer? I can see the owner
> of the job is the name of my computer/username.
> Greetings,
> Zeke
>
Could somebody tell me what i'm doing wrong. I have created a dts job from
the client console on my computer. Next i want to schedule the dts job, but
whenever it starts it is always failing.
Is it because i've created the dts job on my computer? I can see the owner
of the job is the name of my computer/username.
Greetings,
ZekeI guess it has to do with security. When you schedule a job, it is executed
under different user account, not under yours. if you are a member of the
sysadmin role, then it is executed using SQL Server Agent's account.
Otherwise you can use so-called proxy account (check more in Books OnLine).
And the account used might not have permissions (maybe on file system as
well, not just SQL Server, depends on whatever the package is doing). Or it
does not see the drives you mapped using your account, etc.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Ezekil" <ezekiel@.lycos.nl> wrote in message
news:%23EwqWe1MEHA.2540@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Could somebody tell me what i'm doing wrong. I have created a dts job from
> the client console on my computer. Next i want to schedule the dts job,
but
> whenever it starts it is always failing.
> Is it because i've created the dts job on my computer? I can see the owner
> of the job is the name of my computer/username.
> Greetings,
> Zeke
>
Monday, March 26, 2012
scheduling dts jobs fails
Hello,
Could somebody tell me what i'm doing wrong. I have created a dts job from
the client console on my computer. Next i want to schedule the dts job, but
whenever it starts it is always failing.
Is it because i've created the dts job on my computer? I can see the owner
of the job is the name of my computer/username.
Greetings,
ZekeI guess it has to do with security. When you schedule a job, it is executed
under different user account, not under yours. if you are a member of the
sysadmin role, then it is executed using SQL Server Agent's account.
Otherwise you can use so-called proxy account (check more in Books OnLine).
And the account used might not have permissions (maybe on file system as
well, not just SQL Server, depends on whatever the package is doing). Or it
does not see the drives you mapped using your account, etc.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Ezekiël" <ezekiel@.lycos.nl> wrote in message
news:%23EwqWe1MEHA.2540@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Could somebody tell me what i'm doing wrong. I have created a dts job from
> the client console on my computer. Next i want to schedule the dts job,
but
> whenever it starts it is always failing.
> Is it because i've created the dts job on my computer? I can see the owner
> of the job is the name of my computer/username.
> Greetings,
> Zeke
>
Could somebody tell me what i'm doing wrong. I have created a dts job from
the client console on my computer. Next i want to schedule the dts job, but
whenever it starts it is always failing.
Is it because i've created the dts job on my computer? I can see the owner
of the job is the name of my computer/username.
Greetings,
ZekeI guess it has to do with security. When you schedule a job, it is executed
under different user account, not under yours. if you are a member of the
sysadmin role, then it is executed using SQL Server Agent's account.
Otherwise you can use so-called proxy account (check more in Books OnLine).
And the account used might not have permissions (maybe on file system as
well, not just SQL Server, depends on whatever the package is doing). Or it
does not see the drives you mapped using your account, etc.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Ezekiël" <ezekiel@.lycos.nl> wrote in message
news:%23EwqWe1MEHA.2540@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Could somebody tell me what i'm doing wrong. I have created a dts job from
> the client console on my computer. Next i want to schedule the dts job,
but
> whenever it starts it is always failing.
> Is it because i've created the dts job on my computer? I can see the owner
> of the job is the name of my computer/username.
> Greetings,
> Zeke
>
Scheduling does not work!
Hi Guys
On one of my RS Servers the scheduling does not work. That is the "schedule"
is created. It even exists as a job in SQL Server that runs however the
actual report is not run. In the log file I have the following entries:
ReportingServicesService!library!10ac!11/08/2004-18:04:22:: e ERROR:
Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
ion: The report server cannot decrypt the symmetric key used to access
sensitive or encrypted data in a report server database. You must either
restore a backup key or delete all encrypted content and then restart the
service. Check the documentation for more information., ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
ion: The report server cannot decrypt the symmetric key used to access
sensitive or encrypted data in a report server database. You must either
restore a backup key or delete all encrypted content and then restart the
service. Check the documentation for more information. -->
System.Runtime.InteropServices.COMException (0x80090005): Bad Data.
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
errorCode, IntPtr errorInfo)
at RSManagedCrypto.RSCrypto.ImportSymmetricKey(Byte[] pSymKeyBlob)
at
Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
Does anyone have any ideas as how to fix this problem?
(I have deleted all schedules and having the server rebooted tonight but
dont know if that will fix the problem).
Thanks.
Regards
JonasDid you recently change the user that the reportserver service runs under?
Did you back up your symmetric key?
If you changed the user but did not back up the symmetric key, then change
the user back to the original and use rskeymgmt to back up the symmetric
key. Change the user again to what you want and user rskeymgmt to insert
the symmetric key.
If you have already saved your symmetric key then just insert it using
rskeymgmt.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:ezvNqMBgEHA.2536@.TK2MSFTNGP09.phx.gbl...
> Hi Guys
> On one of my RS Servers the scheduling does not work. That is the
"schedule"
> is created. It even exists as a job in SQL Server that runs however the
> actual report is not run. In the log file I have the following entries:
> ReportingServicesService!library!10ac!11/08/2004-18:04:22:: e ERROR:
> Throwing
>
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
> ion: The report server cannot decrypt the symmetric key used to access
> sensitive or encrypted data in a report server database. You must either
> restore a backup key or delete all encrypted content and then restart the
> service. Check the documentation for more information., ;
> Info:
>
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
> ion: The report server cannot decrypt the symmetric key used to access
> sensitive or encrypted data in a report server database. You must either
> restore a backup key or delete all encrypted content and then restart the
> service. Check the documentation for more information. -->
> System.Runtime.InteropServices.COMException (0x80090005): Bad Data.
> at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> errorCode, IntPtr errorInfo)
> at RSManagedCrypto.RSCrypto.ImportSymmetricKey(Byte[] pSymKeyBlob)
> at
> Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
> Does anyone have any ideas as how to fix this problem?
> (I have deleted all schedules and having the server rebooted tonight but
> dont know if that will fix the problem).
> Thanks.
> Regards
> Jonas
>|||Thanks. Problem solved.
Next problem. Due to this issue I now have 2 jobs on my SQLServer created by
Reporting Services that does not show in RS. Can I just delete these jobs
or?
Regards
Jonas
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:%23fN5hFJgEHA.536@.TK2MSFTNGP11.phx.gbl...
> Did you recently change the user that the reportserver service runs under?
> Did you back up your symmetric key?
> If you changed the user but did not back up the symmetric key, then change
> the user back to the original and use rskeymgmt to back up the symmetric
> key. Change the user again to what you want and user rskeymgmt to insert
> the symmetric key.
> If you have already saved your symmetric key then just insert it using
> rskeymgmt.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> news:ezvNqMBgEHA.2536@.TK2MSFTNGP09.phx.gbl...
> > Hi Guys
> >
> > On one of my RS Servers the scheduling does not work. That is the
> "schedule"
> > is created. It even exists as a job in SQL Server that runs however the
> > actual report is not run. In the log file I have the following entries:
> >
> > ReportingServicesService!library!10ac!11/08/2004-18:04:22:: e ERROR:
> > Throwing
> >
>
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
> > ion: The report server cannot decrypt the symmetric key used to access
> > sensitive or encrypted data in a report server database. You must either
> > restore a backup key or delete all encrypted content and then restart
the
> > service. Check the documentation for more information., ;
> > Info:
> >
>
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
> > ion: The report server cannot decrypt the symmetric key used to access
> > sensitive or encrypted data in a report server database. You must either
> > restore a backup key or delete all encrypted content and then restart
the
> > service. Check the documentation for more information. -->
> > System.Runtime.InteropServices.COMException (0x80090005): Bad Data.
> > at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> > errorCode, IntPtr errorInfo)
> > at RSManagedCrypto.RSCrypto.ImportSymmetricKey(Byte[] pSymKeyBlob)
> > at
> > Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
> >
> > Does anyone have any ideas as how to fix this problem?
> > (I have deleted all schedules and having the server rebooted tonight but
> > dont know if that will fix the problem).
> >
> > Thanks.
> >
> > Regards
> > Jonas
> >
> >
>|||Yes, RS will recreate jobs if they are deleted from SQL Agent and RS still
needs them. By default it will check this every 12 hours. You can force
this to happen by recycling the ReportServer service.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:OG$#KRMgEHA.4024@.TK2MSFTNGP10.phx.gbl...
> Thanks. Problem solved.
> Next problem. Due to this issue I now have 2 jobs on my SQLServer created
by
> Reporting Services that does not show in RS. Can I just delete these jobs
> or?
> Regards
> Jonas
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:%23fN5hFJgEHA.536@.TK2MSFTNGP11.phx.gbl...
> > Did you recently change the user that the reportserver service runs
under?
> > Did you back up your symmetric key?
> >
> > If you changed the user but did not back up the symmetric key, then
change
> > the user back to the original and use rskeymgmt to back up the symmetric
> > key. Change the user again to what you want and user rskeymgmt to
insert
> > the symmetric key.
> >
> > If you have already saved your symmetric key then just insert it using
> > rskeymgmt.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> > news:ezvNqMBgEHA.2536@.TK2MSFTNGP09.phx.gbl...
> > > Hi Guys
> > >
> > > On one of my RS Servers the scheduling does not work. That is the
> > "schedule"
> > > is created. It even exists as a job in SQL Server that runs however
the
> > > actual report is not run. In the log file I have the following
entries:
> > >
> > > ReportingServicesService!library!10ac!11/08/2004-18:04:22:: e ERROR:
> > > Throwing
> > >
> >
>
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
> > > ion: The report server cannot decrypt the symmetric key used to access
> > > sensitive or encrypted data in a report server database. You must
either
> > > restore a backup key or delete all encrypted content and then restart
> the
> > > service. Check the documentation for more information., ;
> > > Info:
> > >
> >
>
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
> > > ion: The report server cannot decrypt the symmetric key used to access
> > > sensitive or encrypted data in a report server database. You must
either
> > > restore a backup key or delete all encrypted content and then restart
> the
> > > service. Check the documentation for more information. -->
> > > System.Runtime.InteropServices.COMException (0x80090005): Bad Data.
> > > at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> > > errorCode, IntPtr errorInfo)
> > > at RSManagedCrypto.RSCrypto.ImportSymmetricKey(Byte[] pSymKeyBlob)
> > > at
> > >
Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
> > >
> > > Does anyone have any ideas as how to fix this problem?
> > > (I have deleted all schedules and having the server rebooted tonight
but
> > > dont know if that will fix the problem).
> > >
> > > Thanks.
> > >
> > > Regards
> > > Jonas
> > >
> > >
> >
> >
>
On one of my RS Servers the scheduling does not work. That is the "schedule"
is created. It even exists as a job in SQL Server that runs however the
actual report is not run. In the log file I have the following entries:
ReportingServicesService!library!10ac!11/08/2004-18:04:22:: e ERROR:
Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
ion: The report server cannot decrypt the symmetric key used to access
sensitive or encrypted data in a report server database. You must either
restore a backup key or delete all encrypted content and then restart the
service. Check the documentation for more information., ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
ion: The report server cannot decrypt the symmetric key used to access
sensitive or encrypted data in a report server database. You must either
restore a backup key or delete all encrypted content and then restart the
service. Check the documentation for more information. -->
System.Runtime.InteropServices.COMException (0x80090005): Bad Data.
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
errorCode, IntPtr errorInfo)
at RSManagedCrypto.RSCrypto.ImportSymmetricKey(Byte[] pSymKeyBlob)
at
Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
Does anyone have any ideas as how to fix this problem?
(I have deleted all schedules and having the server rebooted tonight but
dont know if that will fix the problem).
Thanks.
Regards
JonasDid you recently change the user that the reportserver service runs under?
Did you back up your symmetric key?
If you changed the user but did not back up the symmetric key, then change
the user back to the original and use rskeymgmt to back up the symmetric
key. Change the user again to what you want and user rskeymgmt to insert
the symmetric key.
If you have already saved your symmetric key then just insert it using
rskeymgmt.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:ezvNqMBgEHA.2536@.TK2MSFTNGP09.phx.gbl...
> Hi Guys
> On one of my RS Servers the scheduling does not work. That is the
"schedule"
> is created. It even exists as a job in SQL Server that runs however the
> actual report is not run. In the log file I have the following entries:
> ReportingServicesService!library!10ac!11/08/2004-18:04:22:: e ERROR:
> Throwing
>
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
> ion: The report server cannot decrypt the symmetric key used to access
> sensitive or encrypted data in a report server database. You must either
> restore a backup key or delete all encrypted content and then restart the
> service. Check the documentation for more information., ;
> Info:
>
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
> ion: The report server cannot decrypt the symmetric key used to access
> sensitive or encrypted data in a report server database. You must either
> restore a backup key or delete all encrypted content and then restart the
> service. Check the documentation for more information. -->
> System.Runtime.InteropServices.COMException (0x80090005): Bad Data.
> at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> errorCode, IntPtr errorInfo)
> at RSManagedCrypto.RSCrypto.ImportSymmetricKey(Byte[] pSymKeyBlob)
> at
> Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
> Does anyone have any ideas as how to fix this problem?
> (I have deleted all schedules and having the server rebooted tonight but
> dont know if that will fix the problem).
> Thanks.
> Regards
> Jonas
>|||Thanks. Problem solved.
Next problem. Due to this issue I now have 2 jobs on my SQLServer created by
Reporting Services that does not show in RS. Can I just delete these jobs
or?
Regards
Jonas
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:%23fN5hFJgEHA.536@.TK2MSFTNGP11.phx.gbl...
> Did you recently change the user that the reportserver service runs under?
> Did you back up your symmetric key?
> If you changed the user but did not back up the symmetric key, then change
> the user back to the original and use rskeymgmt to back up the symmetric
> key. Change the user again to what you want and user rskeymgmt to insert
> the symmetric key.
> If you have already saved your symmetric key then just insert it using
> rskeymgmt.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> news:ezvNqMBgEHA.2536@.TK2MSFTNGP09.phx.gbl...
> > Hi Guys
> >
> > On one of my RS Servers the scheduling does not work. That is the
> "schedule"
> > is created. It even exists as a job in SQL Server that runs however the
> > actual report is not run. In the log file I have the following entries:
> >
> > ReportingServicesService!library!10ac!11/08/2004-18:04:22:: e ERROR:
> > Throwing
> >
>
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
> > ion: The report server cannot decrypt the symmetric key used to access
> > sensitive or encrypted data in a report server database. You must either
> > restore a backup key or delete all encrypted content and then restart
the
> > service. Check the documentation for more information., ;
> > Info:
> >
>
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
> > ion: The report server cannot decrypt the symmetric key used to access
> > sensitive or encrypted data in a report server database. You must either
> > restore a backup key or delete all encrypted content and then restart
the
> > service. Check the documentation for more information. -->
> > System.Runtime.InteropServices.COMException (0x80090005): Bad Data.
> > at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> > errorCode, IntPtr errorInfo)
> > at RSManagedCrypto.RSCrypto.ImportSymmetricKey(Byte[] pSymKeyBlob)
> > at
> > Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
> >
> > Does anyone have any ideas as how to fix this problem?
> > (I have deleted all schedules and having the server rebooted tonight but
> > dont know if that will fix the problem).
> >
> > Thanks.
> >
> > Regards
> > Jonas
> >
> >
>|||Yes, RS will recreate jobs if they are deleted from SQL Agent and RS still
needs them. By default it will check this every 12 hours. You can force
this to happen by recycling the ReportServer service.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:OG$#KRMgEHA.4024@.TK2MSFTNGP10.phx.gbl...
> Thanks. Problem solved.
> Next problem. Due to this issue I now have 2 jobs on my SQLServer created
by
> Reporting Services that does not show in RS. Can I just delete these jobs
> or?
> Regards
> Jonas
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:%23fN5hFJgEHA.536@.TK2MSFTNGP11.phx.gbl...
> > Did you recently change the user that the reportserver service runs
under?
> > Did you back up your symmetric key?
> >
> > If you changed the user but did not back up the symmetric key, then
change
> > the user back to the original and use rskeymgmt to back up the symmetric
> > key. Change the user again to what you want and user rskeymgmt to
insert
> > the symmetric key.
> >
> > If you have already saved your symmetric key then just insert it using
> > rskeymgmt.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> > news:ezvNqMBgEHA.2536@.TK2MSFTNGP09.phx.gbl...
> > > Hi Guys
> > >
> > > On one of my RS Servers the scheduling does not work. That is the
> > "schedule"
> > > is created. It even exists as a job in SQL Server that runs however
the
> > > actual report is not run. In the log file I have the following
entries:
> > >
> > > ReportingServicesService!library!10ac!11/08/2004-18:04:22:: e ERROR:
> > > Throwing
> > >
> >
>
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
> > > ion: The report server cannot decrypt the symmetric key used to access
> > > sensitive or encrypted data in a report server database. You must
either
> > > restore a backup key or delete all encrypted content and then restart
> the
> > > service. Check the documentation for more information., ;
> > > Info:
> > >
> >
>
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
> > > ion: The report server cannot decrypt the symmetric key used to access
> > > sensitive or encrypted data in a report server database. You must
either
> > > restore a backup key or delete all encrypted content and then restart
> the
> > > service. Check the documentation for more information. -->
> > > System.Runtime.InteropServices.COMException (0x80090005): Bad Data.
> > > at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> > > errorCode, IntPtr errorInfo)
> > > at RSManagedCrypto.RSCrypto.ImportSymmetricKey(Byte[] pSymKeyBlob)
> > > at
> > >
Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
> > >
> > > Does anyone have any ideas as how to fix this problem?
> > > (I have deleted all schedules and having the server rebooted tonight
but
> > > dont know if that will fix the problem).
> > >
> > > Thanks.
> > >
> > > Regards
> > > Jonas
> > >
> > >
> >
> >
>
Scheduling Data Transformation Services?
Hi,
Our vendor created a series of dts local packages and I'd like to have them
run each night. When I right-click one of the packages and select Schedule
Package it comes up with a default schedule. So it looks like it is already
schedule, which is fine but how do I know if the package was run? Is this
activity tracked in a log somewhere? When I look under Package Log I see
it's empty. Any suggestions?
Thanks in advance,
LinnIn enterprize manager, go to
Management / SQL Server Agent / Jobs
From there you can view job history and edit the job itself.. as well as the
schedule.
"Linn Kubler" <lkubler@.chartwellwisc2.com> wrote in message
news:uBL6RelMGHA.3432@.tk2msftngp13.phx.gbl...
> Hi,
> Our vendor created a series of dts local packages and I'd like to have
> them run each night. When I right-click one of the packages and select
> Schedule Package it comes up with a default schedule. So it looks like it
> is already schedule, which is fine but how do I know if the package was
> run? Is this activity tracked in a log somewhere? When I look under
> Package Log I see it's empty. Any suggestions?
> Thanks in advance,
> Linn
>|||First..I assume you are using ss2k.
If you open up the DTS package, and click Package, Proprties..and the
Logging tab, you will see in the bottom how to create an error file.
You could also set up a task to email yourself when the task completes
successfully(of course for this to work your server must be configured
to use Exchange.)
MJKulangara
http://sqladventures.blogspot.com
Our vendor created a series of dts local packages and I'd like to have them
run each night. When I right-click one of the packages and select Schedule
Package it comes up with a default schedule. So it looks like it is already
schedule, which is fine but how do I know if the package was run? Is this
activity tracked in a log somewhere? When I look under Package Log I see
it's empty. Any suggestions?
Thanks in advance,
LinnIn enterprize manager, go to
Management / SQL Server Agent / Jobs
From there you can view job history and edit the job itself.. as well as the
schedule.
"Linn Kubler" <lkubler@.chartwellwisc2.com> wrote in message
news:uBL6RelMGHA.3432@.tk2msftngp13.phx.gbl...
> Hi,
> Our vendor created a series of dts local packages and I'd like to have
> them run each night. When I right-click one of the packages and select
> Schedule Package it comes up with a default schedule. So it looks like it
> is already schedule, which is fine but how do I know if the package was
> run? Is this activity tracked in a log somewhere? When I look under
> Package Log I see it's empty. Any suggestions?
> Thanks in advance,
> Linn
>|||First..I assume you are using ss2k.
If you open up the DTS package, and click Package, Proprties..and the
Logging tab, you will see in the bottom how to create an error file.
You could also set up a task to email yourself when the task completes
successfully(of course for this to work your server must be configured
to use Exchange.)
MJKulangara
http://sqladventures.blogspot.com
Scheduling a SSIS Package using SQL Agent (passing credentials)
Hi:
I created a fairly simple SSIS package to move data from a remote server which uses SQL Server Authentication to a local server that uses Windows Authentication. Everything works fine when executing the package within BI studio and if I execute the package by connecting to SSIS. If I schedule it as a SQL Server Agent Job, however, the non-integrated security fails unless I manually edit the connection to include the password. Obviously this is insecure because it is in plain text. What can I do?
Thanks.
You might need to change the encryption protection of your package (i.e. encrypt sensitive w/ user key, encrypt sensitive w/ password, etc) as well as setting up your proxy / credentials correctly.
Check out these MS article...
http://support.microsoft.com/kb/912911
http://support.microsoft.com/?kbid=918760
There are also many threads discussing these topics, including:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1322146&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=703968&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2068280&SiteID=1
|||Thanks for your response.
First of all, I did try the encrypt sensitive w / password (the default is w / user key) and I put a password in, saved the package to SQL Server and re-setup the job. It prompted me for passwords as I was setting the job up, but it didn't help at all with the remote connection. Do I need to put my connection information into the command line (user and password) AND encrypt with the password to keep others from seeing the username and password. So far I've just done one or the other.
I also tried the proxy credentials thing, but I am not so sure I understood exactly how it works. Specifically, it was prompting for an AD account, which doesn't help me in the non-integrated (SQL) login environment.
|||RM,
You have multiple choices, but before choosing one you have to understand the pros and cons of each one. Using Package protection level equal to DonSaveSensitive plus using package configurations is a popular method that works well. You have more details in the method 4 described in one of the KB articles that EWisdahl pointed out( http://support.microsoft.com/?kbid=918760)
Search by package configurations and you will find plenty of options and examples
|||Interestingly enough,
When I went back into the SQL Agent Job and clicked on command line (where I had added the credentials before) it defaulted back to the original command line. If this is the case, no problem, because the password I typed is hidden, right? Of course, now when I click on the data sources tab I get an error that states my connection string is invalid and it shows no data sources, either? Is the behavior somehow interrelated? The job seems to be running fine, though!?!
Friday, March 23, 2012
Scheduling a DTS package without admin privileges.
I have created a dts package that I can execute from within sql server
enterprise manager. However, I don’t have admin privileges so when I
schedule a job within sql server it fails to execute because I don’t have
the
necessary privileges.
Since I have the privileges to run the DTS package I have created I am
interested in another means of scheduling my DTS package. I was thinking of
making use of the windows task scheduler or if that is not possible making a
windows service.
Right now I am using the dtsrun.exe but I can only use this if sql
server is installed on the machine that requests the DTS package to be
executed. I was hoping to find a way to request the DTS Package to be run
from any machine of my choosing. I got excited when I found the DTS com dl
l
but it appears only to provide a wrapper that communicates with the locally
installed sqlserver. Is there another way to do this other than have admin
privileges and keep it on the actually server? For now I have created a dts
substitute…Basically I have every thing I need to do in a series of stored
procedures and I call that from a windows task scheduler. (However I am
missing email and other capabilities.) Do I really need admin privileges to
be able to schedule a DTS package?Hello,
The security context in which the DTS job is run is determined by the owner
of the job. If the job is owned by a login that is not a member of the
Sy
min server role, then the package is run under the context of the
account setup as the SQL Agent Proxy Account, and has the rights and
permissions of that account.
For SQL Agent Proxy to be able to run jobs that connect to SQL Server, the
SQL Agent Proxy account must have proper Windows/NT permissions and be
granted login access to SQL Server with appropriate database permissions.
For the jobs that execute a DTS package, the SQL Agent Proxy Account must
have read and write permissions to the temp directory of the Account the
SQL Server Agent is running under. For example,
c:\Documents and Settings\<Account>\Local Settings\Temp
You may want to look at the following article:
http://support.microsoft.com/defaul...kb;en-us;269074
also the security discussions at:
http://support.microsoft.com/defaul...kb;en-us;252987
http://support.microsoft.com/defaul...kb;en-us;322746
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Scheduling a DTS package without admin privileges.
>thread-index: AcZD0ZYb2apHTm+GQoGmib/7CppZqQ==
>X-WBNR-Posting-Host: 66.162.65.194
>From: examnotes <JeffDotNet@.newsgroups.nospam>
>Subject: Scheduling a DTS package without admin privileges.
>Date: Thu, 9 Mar 2006 15:31:27 -0800
>Lines: 20
>Message-ID: <2AB4DFE2-6F42-4DD5-8AAF-339819986F39@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 8bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>Newsgroups: microsoft.public.sqlserver.programming
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.programming:586021
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.programming
>I have created a dts package that I can execute from within sql server
>enterprise manager. However, I don’t have admin privileges so when I
>schedule a job within sql server it fails to execute because I don’t
have the
>necessary privileges.
> Since I have the privileges to run the DTS package I have created I am
>interested in another means of scheduling my DTS package. I was thinking
of
>making use of the windows task scheduler or if that is not possible making
a
>windows service.
> Right now I am using the dtsrun.exe but I can only use this if sql
>server is installed on the machine that requests the DTS package to be
>executed. I was hoping to find a way to request the DTS Package to be run
>from any machine of my choosing. I got excited when I found the DTS com
dll
>but it appears only to provide a wrapper that communicates with the
locally
>installed sqlserver. Is there another way to do this other than have
admin
>privileges and keep it on the actually server? For now I have created a
dts
>substitute…Basically I have every thing I need to do in a series of
stored
>procedures and I call that from a windows task scheduler. (However I am
>missing email and other capabilities.) Do I really need admin privileges
to
>be able to schedule a DTS package?
>
enterprise manager. However, I don’t have admin privileges so when I
schedule a job within sql server it fails to execute because I don’t have
the
necessary privileges.
Since I have the privileges to run the DTS package I have created I am
interested in another means of scheduling my DTS package. I was thinking of
making use of the windows task scheduler or if that is not possible making a
windows service.
Right now I am using the dtsrun.exe but I can only use this if sql
server is installed on the machine that requests the DTS package to be
executed. I was hoping to find a way to request the DTS Package to be run
from any machine of my choosing. I got excited when I found the DTS com dl
l
but it appears only to provide a wrapper that communicates with the locally
installed sqlserver. Is there another way to do this other than have admin
privileges and keep it on the actually server? For now I have created a dts
substitute…Basically I have every thing I need to do in a series of stored
procedures and I call that from a windows task scheduler. (However I am
missing email and other capabilities.) Do I really need admin privileges to
be able to schedule a DTS package?Hello,
The security context in which the DTS job is run is determined by the owner
of the job. If the job is owned by a login that is not a member of the
Sy
min server role, then the package is run under the context of theaccount setup as the SQL Agent Proxy Account, and has the rights and
permissions of that account.
For SQL Agent Proxy to be able to run jobs that connect to SQL Server, the
SQL Agent Proxy account must have proper Windows/NT permissions and be
granted login access to SQL Server with appropriate database permissions.
For the jobs that execute a DTS package, the SQL Agent Proxy Account must
have read and write permissions to the temp directory of the Account the
SQL Server Agent is running under. For example,
c:\Documents and Settings\<Account>\Local Settings\Temp
You may want to look at the following article:
http://support.microsoft.com/defaul...kb;en-us;269074
also the security discussions at:
http://support.microsoft.com/defaul...kb;en-us;252987
http://support.microsoft.com/defaul...kb;en-us;322746
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Scheduling a DTS package without admin privileges.
>thread-index: AcZD0ZYb2apHTm+GQoGmib/7CppZqQ==
>X-WBNR-Posting-Host: 66.162.65.194
>From: examnotes <JeffDotNet@.newsgroups.nospam>
>Subject: Scheduling a DTS package without admin privileges.
>Date: Thu, 9 Mar 2006 15:31:27 -0800
>Lines: 20
>Message-ID: <2AB4DFE2-6F42-4DD5-8AAF-339819986F39@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 8bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>Newsgroups: microsoft.public.sqlserver.programming
>Path: TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.programming:586021
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.programming
>I have created a dts package that I can execute from within sql server
>enterprise manager. However, I don’t have admin privileges so when I
>schedule a job within sql server it fails to execute because I don’t
have the
>necessary privileges.
> Since I have the privileges to run the DTS package I have created I am
>interested in another means of scheduling my DTS package. I was thinking
of
>making use of the windows task scheduler or if that is not possible making
a
>windows service.
> Right now I am using the dtsrun.exe but I can only use this if sql
>server is installed on the machine that requests the DTS package to be
>executed. I was hoping to find a way to request the DTS Package to be run
>from any machine of my choosing. I got excited when I found the DTS com
dll
>but it appears only to provide a wrapper that communicates with the
locally
>installed sqlserver. Is there another way to do this other than have
admin
>privileges and keep it on the actually server? For now I have created a
dts
>substitute…Basically I have every thing I need to do in a series of
stored
>procedures and I call that from a windows task scheduler. (However I am
>missing email and other capabilities.) Do I really need admin privileges
to
>be able to schedule a DTS package?
>
Scheduling a DTS package
I am using Enterprise manager with an MSDE engine. I have created a DTS package that updates a table in one of the databases. If I right click and choose "Execute Package" it runs...No sweat. When I try to use the scheduler to run the job every hour, it always fails between 1 and 10 seconds into the job...It only returns the error "Failed During Step 1."
I'm wondering if this feature won't work with MSDE? Does anyone have any ideas?Under Management>Jobs, right click on the job in question, and select "Job History". There should be a checkbox that says "Show step details". Clicking that will expand the information. Check to see if there's more information in there.|||Probably a security issue. Wen you run the package interactively it uses the logged in user, when running from the job queue it uses the configured user account of the DTS Package.
Open your package, click Package->Properties->Logging|||Thanks for tips, the error is:
Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error opening datafile: The system cannot find the path specified.
Step Error code: 80004005
Step Error Help File:DTSFFile.hlp
Step Error Help Context ID:0
I checked the path and it's correct, and it finds the file when I run it manually.|||Check the server to see if the file exists in the exact same filepath as on your machine. I believe that when you run a DTS package interactively, it resolves file names locally, but when executed from the server via SQLAgent it will resolve the filepaths from the server.|||i concurr
i suck at typing in filepaths so i always set the windows explorer to display the full path in the title bar and i just copy it from there.
I'm wondering if this feature won't work with MSDE? Does anyone have any ideas?Under Management>Jobs, right click on the job in question, and select "Job History". There should be a checkbox that says "Show step details". Clicking that will expand the information. Check to see if there's more information in there.|||Probably a security issue. Wen you run the package interactively it uses the logged in user, when running from the job queue it uses the configured user account of the DTS Package.
Open your package, click Package->Properties->Logging|||Thanks for tips, the error is:
Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error opening datafile: The system cannot find the path specified.
Step Error code: 80004005
Step Error Help File:DTSFFile.hlp
Step Error Help Context ID:0
I checked the path and it's correct, and it finds the file when I run it manually.|||Check the server to see if the file exists in the exact same filepath as on your machine. I believe that when you run a DTS package interactively, it resolves file names locally, but when executed from the server via SQLAgent it will resolve the filepaths from the server.|||i concurr
i suck at typing in filepaths so i always set the windows explorer to display the full path in the title bar and i just copy it from there.
Scheduling a DTS
I have created a DTS Import routine and scheduled it to run every hour.
I started the agent but despite that, the schedule never seems to work.
I am forced to run it manually.
Any Ideas how I can get it to work ?Could it be it's still running, or has it run but failed.
In EnterPrise Manager :- Management
SQL Server Agent
Jobs
If you right click your job and 'Show History' has it ever ran..?
Also ensure Enabled = 'Yes' and Schedule = 'Yes'
--
HTH
Ryan Waight, MCDBA, MCSE
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> I have created a DTS Import routine and scheduled it to run every hour.
> I started the agent but despite that, the schedule never seems to work.
> I am forced to run it manually.
> Any Ideas how I can get it to work ?
>
>|||" Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
Where do I access the Enabled and the Shedule ?
Sorry for being ignorant but I have never done this before.
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> Could it be it's still running, or has it run but failed.
> In EnterPrise Manager :- Management
> SQL Server Agent
> Jobs
> If you right click your job and 'Show History' has it ever ran..?
> Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > I have created a DTS Import routine and scheduled it to run every hour.
> > I started the agent but despite that, the schedule never seems to work.
> > I am forced to run it manually.
> >
> > Any Ideas how I can get it to work ?
> >
> >
> >
>|||Whilst viewing the Jobs window, 2 of the columns are entitled Enabled,
Scheduled.
--
HTH
Ryan Waight, MCDBA, MCSE
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> Where do I access the Enabled and the Shedule ?
> Sorry for being ignorant but I have never done this before.
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > Could it be it's still running, or has it run but failed.
> >
> > In EnterPrise Manager :- Management
> > SQL Server Agent
> > Jobs
> >
> > If you right click your job and 'Show History' has it ever ran..?
> >
> > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > I have created a DTS Import routine and scheduled it to run every
hour.
> > > I started the agent but despite that, the schedule never seems to
work.
> > > I am forced to run it manually.
> > >
> > > Any Ideas how I can get it to work ?
> > >
> > >
> > >
> >
> >
>|||Sorry.
I can see it now.
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> Where do I access the Enabled and the Shedule ?
> Sorry for being ignorant but I have never done this before.
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > Could it be it's still running, or has it run but failed.
> >
> > In EnterPrise Manager :- Management
> > SQL Server Agent
> > Jobs
> >
> > If you right click your job and 'Show History' has it ever ran..?
> >
> > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > I have created a DTS Import routine and scheduled it to run every
hour.
> > > I started the agent but despite that, the schedule never seems to
work.
> > > I am forced to run it manually.
> > >
> > > Any Ideas how I can get it to work ?
> > >
> > >
> > >
> >
> >
>|||No problem, if your new to the SQL Server Agent this may be of use :-
http://www.sqlpass.org/news/02feb/index.cfm?passnews=DBA1
--
HTH
Ryan Waight, MCDBA, MCSE
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:ua218MUpDHA.1488@.TK2MSFTNGP12.phx.gbl...
> Sorry.
> I can see it now.
>
> "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> > " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> >
> > Where do I access the Enabled and the Shedule ?
> > Sorry for being ignorant but I have never done this before.
> >
> >
> > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > > Could it be it's still running, or has it run but failed.
> > >
> > > In EnterPrise Manager :- Management
> > > SQL Server Agent
> > > Jobs
> > >
> > > If you right click your job and 'Show History' has it ever ran..?
> > >
> > > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> > >
> > > --
> > > HTH
> > > Ryan Waight, MCDBA, MCSE
> > >
> > > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > > I have created a DTS Import routine and scheduled it to run every
> hour.
> > > > I started the agent but despite that, the schedule never seems to
> work.
> > > > I am forced to run it manually.
> > > >
> > > > Any Ideas how I can get it to work ?
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||None of my DTS services appear in jobs.
What am I doing wrong.
I thought that after creating a schedule event for a dts it would appear as
a job ?
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:Oz4SPMUpDHA.1672@.TK2MSFTNGP09.phx.gbl...
> Whilst viewing the Jobs window, 2 of the columns are entitled Enabled,
> Scheduled.
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> > " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> >
> > Where do I access the Enabled and the Shedule ?
> > Sorry for being ignorant but I have never done this before.
> >
> >
> > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > > Could it be it's still running, or has it run but failed.
> > >
> > > In EnterPrise Manager :- Management
> > > SQL Server Agent
> > > Jobs
> > >
> > > If you right click your job and 'Show History' has it ever ran..?
> > >
> > > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> > >
> > > --
> > > HTH
> > > Ryan Waight, MCDBA, MCSE
> > >
> > > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > > I have created a DTS Import routine and scheduled it to run every
> hour.
> > > > I started the agent but despite that, the schedule never seems to
> work.
> > > > I am forced to run it manually.
> > > >
> > > > Any Ideas how I can get it to work ?
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Right click on Jobs and Choose Refresh
--
HTH
Ryan Waight, MCDBA, MCSE
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:e2BLOQUpDHA.3320@.tk2msftngp13.phx.gbl...
> None of my DTS services appear in jobs.
> What am I doing wrong.
> I thought that after creating a schedule event for a dts it would appear
as
> a job ?
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:Oz4SPMUpDHA.1672@.TK2MSFTNGP09.phx.gbl...
> > Whilst viewing the Jobs window, 2 of the columns are entitled Enabled,
> > Scheduled.
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> > > " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> > >
> > > Where do I access the Enabled and the Shedule ?
> > > Sorry for being ignorant but I have never done this before.
> > >
> > >
> > > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > > news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > > > Could it be it's still running, or has it run but failed.
> > > >
> > > > In EnterPrise Manager :- Management
> > > > SQL Server Agent
> > > > Jobs
> > > >
> > > > If you right click your job and 'Show History' has it ever ran..?
> > > >
> > > > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> > > >
> > > > --
> > > > HTH
> > > > Ryan Waight, MCDBA, MCSE
> > > >
> > > > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > > > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > > > I have created a DTS Import routine and scheduled it to run every
> > hour.
> > > > > I started the agent but despite that, the schedule never seems to
> > work.
> > > > > I am forced to run it manually.
> > > > >
> > > > > Any Ideas how I can get it to work ?
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks Ryan.
I might have learned a little but I still canr figure out how to run a DTS
from a job.
Its driving me nuts.
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eL8KFPUpDHA.2488@.TK2MSFTNGP12.phx.gbl...
> No problem, if your new to the SQL Server Agent this may be of use :-
> http://www.sqlpass.org/news/02feb/index.cfm?passnews=DBA1
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> news:ua218MUpDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > Sorry.
> > I can see it now.
> >
> >
> > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> > > " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> > >
> > > Where do I access the Enabled and the Shedule ?
> > > Sorry for being ignorant but I have never done this before.
> > >
> > >
> > > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > > news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > > > Could it be it's still running, or has it run but failed.
> > > >
> > > > In EnterPrise Manager :- Management
> > > > SQL Server Agent
> > > > Jobs
> > > >
> > > > If you right click your job and 'Show History' has it ever ran..?
> > > >
> > > > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> > > >
> > > > --
> > > > HTH
> > > > Ryan Waight, MCDBA, MCSE
> > > >
> > > > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > > > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > > > I have created a DTS Import routine and scheduled it to run every
> > hour.
> > > > > I started the agent but despite that, the schedule never seems to
> > work.
> > > > > I am forced to run it manually.
> > > > >
> > > > > Any Ideas how I can get it to work ?
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Another good resource at :- www.sqldts.com
--
HTH
Ryan Waight, MCDBA, MCSE
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:OI84bhUpDHA.2000@.TK2MSFTNGP12.phx.gbl...
> Thanks Ryan.
> I might have learned a little but I still canr figure out how to run a DTS
> from a job.
> Its driving me nuts.
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:eL8KFPUpDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > No problem, if your new to the SQL Server Agent this may be of use :-
> >
> > http://www.sqlpass.org/news/02feb/index.cfm?passnews=DBA1
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > news:ua218MUpDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > > Sorry.
> > > I can see it now.
> > >
> > >
> > > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > > news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> > > > " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> > > >
> > > > Where do I access the Enabled and the Shedule ?
> > > > Sorry for being ignorant but I have never done this before.
> > > >
> > > >
> > > > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > > > news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > > > > Could it be it's still running, or has it run but failed.
> > > > >
> > > > > In EnterPrise Manager :- Management
> > > > > SQL Server Agent
> > > > > Jobs
> > > > >
> > > > > If you right click your job and 'Show History' has it ever ran..?
> > > > >
> > > > > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> > > > >
> > > > > --
> > > > > HTH
> > > > > Ryan Waight, MCDBA, MCSE
> > > > >
> > > > > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > > > > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > > > > I have created a DTS Import routine and scheduled it to run
every
> > > hour.
> > > > > > I started the agent but despite that, the schedule never seems
to
> > > work.
> > > > > > I am forced to run it manually.
> > > > > >
> > > > > > Any Ideas how I can get it to work ?
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>sql
I started the agent but despite that, the schedule never seems to work.
I am forced to run it manually.
Any Ideas how I can get it to work ?Could it be it's still running, or has it run but failed.
In EnterPrise Manager :- Management
SQL Server Agent
Jobs
If you right click your job and 'Show History' has it ever ran..?
Also ensure Enabled = 'Yes' and Schedule = 'Yes'
--
HTH
Ryan Waight, MCDBA, MCSE
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> I have created a DTS Import routine and scheduled it to run every hour.
> I started the agent but despite that, the schedule never seems to work.
> I am forced to run it manually.
> Any Ideas how I can get it to work ?
>
>|||" Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
Where do I access the Enabled and the Shedule ?
Sorry for being ignorant but I have never done this before.
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> Could it be it's still running, or has it run but failed.
> In EnterPrise Manager :- Management
> SQL Server Agent
> Jobs
> If you right click your job and 'Show History' has it ever ran..?
> Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > I have created a DTS Import routine and scheduled it to run every hour.
> > I started the agent but despite that, the schedule never seems to work.
> > I am forced to run it manually.
> >
> > Any Ideas how I can get it to work ?
> >
> >
> >
>|||Whilst viewing the Jobs window, 2 of the columns are entitled Enabled,
Scheduled.
--
HTH
Ryan Waight, MCDBA, MCSE
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> Where do I access the Enabled and the Shedule ?
> Sorry for being ignorant but I have never done this before.
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > Could it be it's still running, or has it run but failed.
> >
> > In EnterPrise Manager :- Management
> > SQL Server Agent
> > Jobs
> >
> > If you right click your job and 'Show History' has it ever ran..?
> >
> > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > I have created a DTS Import routine and scheduled it to run every
hour.
> > > I started the agent but despite that, the schedule never seems to
work.
> > > I am forced to run it manually.
> > >
> > > Any Ideas how I can get it to work ?
> > >
> > >
> > >
> >
> >
>|||Sorry.
I can see it now.
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> Where do I access the Enabled and the Shedule ?
> Sorry for being ignorant but I have never done this before.
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > Could it be it's still running, or has it run but failed.
> >
> > In EnterPrise Manager :- Management
> > SQL Server Agent
> > Jobs
> >
> > If you right click your job and 'Show History' has it ever ran..?
> >
> > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > I have created a DTS Import routine and scheduled it to run every
hour.
> > > I started the agent but despite that, the schedule never seems to
work.
> > > I am forced to run it manually.
> > >
> > > Any Ideas how I can get it to work ?
> > >
> > >
> > >
> >
> >
>|||No problem, if your new to the SQL Server Agent this may be of use :-
http://www.sqlpass.org/news/02feb/index.cfm?passnews=DBA1
--
HTH
Ryan Waight, MCDBA, MCSE
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:ua218MUpDHA.1488@.TK2MSFTNGP12.phx.gbl...
> Sorry.
> I can see it now.
>
> "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> > " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> >
> > Where do I access the Enabled and the Shedule ?
> > Sorry for being ignorant but I have never done this before.
> >
> >
> > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > > Could it be it's still running, or has it run but failed.
> > >
> > > In EnterPrise Manager :- Management
> > > SQL Server Agent
> > > Jobs
> > >
> > > If you right click your job and 'Show History' has it ever ran..?
> > >
> > > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> > >
> > > --
> > > HTH
> > > Ryan Waight, MCDBA, MCSE
> > >
> > > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > > I have created a DTS Import routine and scheduled it to run every
> hour.
> > > > I started the agent but despite that, the schedule never seems to
> work.
> > > > I am forced to run it manually.
> > > >
> > > > Any Ideas how I can get it to work ?
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||None of my DTS services appear in jobs.
What am I doing wrong.
I thought that after creating a schedule event for a dts it would appear as
a job ?
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:Oz4SPMUpDHA.1672@.TK2MSFTNGP09.phx.gbl...
> Whilst viewing the Jobs window, 2 of the columns are entitled Enabled,
> Scheduled.
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> > " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> >
> > Where do I access the Enabled and the Shedule ?
> > Sorry for being ignorant but I have never done this before.
> >
> >
> > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > > Could it be it's still running, or has it run but failed.
> > >
> > > In EnterPrise Manager :- Management
> > > SQL Server Agent
> > > Jobs
> > >
> > > If you right click your job and 'Show History' has it ever ran..?
> > >
> > > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> > >
> > > --
> > > HTH
> > > Ryan Waight, MCDBA, MCSE
> > >
> > > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > > I have created a DTS Import routine and scheduled it to run every
> hour.
> > > > I started the agent but despite that, the schedule never seems to
> work.
> > > > I am forced to run it manually.
> > > >
> > > > Any Ideas how I can get it to work ?
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Right click on Jobs and Choose Refresh
--
HTH
Ryan Waight, MCDBA, MCSE
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:e2BLOQUpDHA.3320@.tk2msftngp13.phx.gbl...
> None of my DTS services appear in jobs.
> What am I doing wrong.
> I thought that after creating a schedule event for a dts it would appear
as
> a job ?
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:Oz4SPMUpDHA.1672@.TK2MSFTNGP09.phx.gbl...
> > Whilst viewing the Jobs window, 2 of the columns are entitled Enabled,
> > Scheduled.
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> > > " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> > >
> > > Where do I access the Enabled and the Shedule ?
> > > Sorry for being ignorant but I have never done this before.
> > >
> > >
> > > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > > news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > > > Could it be it's still running, or has it run but failed.
> > > >
> > > > In EnterPrise Manager :- Management
> > > > SQL Server Agent
> > > > Jobs
> > > >
> > > > If you right click your job and 'Show History' has it ever ran..?
> > > >
> > > > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> > > >
> > > > --
> > > > HTH
> > > > Ryan Waight, MCDBA, MCSE
> > > >
> > > > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > > > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > > > I have created a DTS Import routine and scheduled it to run every
> > hour.
> > > > > I started the agent but despite that, the schedule never seems to
> > work.
> > > > > I am forced to run it manually.
> > > > >
> > > > > Any Ideas how I can get it to work ?
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks Ryan.
I might have learned a little but I still canr figure out how to run a DTS
from a job.
Its driving me nuts.
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eL8KFPUpDHA.2488@.TK2MSFTNGP12.phx.gbl...
> No problem, if your new to the SQL Server Agent this may be of use :-
> http://www.sqlpass.org/news/02feb/index.cfm?passnews=DBA1
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> news:ua218MUpDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > Sorry.
> > I can see it now.
> >
> >
> > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> > > " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> > >
> > > Where do I access the Enabled and the Shedule ?
> > > Sorry for being ignorant but I have never done this before.
> > >
> > >
> > > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > > news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > > > Could it be it's still running, or has it run but failed.
> > > >
> > > > In EnterPrise Manager :- Management
> > > > SQL Server Agent
> > > > Jobs
> > > >
> > > > If you right click your job and 'Show History' has it ever ran..?
> > > >
> > > > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> > > >
> > > > --
> > > > HTH
> > > > Ryan Waight, MCDBA, MCSE
> > > >
> > > > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > > > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > > > I have created a DTS Import routine and scheduled it to run every
> > hour.
> > > > > I started the agent but despite that, the schedule never seems to
> > work.
> > > > > I am forced to run it manually.
> > > > >
> > > > > Any Ideas how I can get it to work ?
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Another good resource at :- www.sqldts.com
--
HTH
Ryan Waight, MCDBA, MCSE
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:OI84bhUpDHA.2000@.TK2MSFTNGP12.phx.gbl...
> Thanks Ryan.
> I might have learned a little but I still canr figure out how to run a DTS
> from a job.
> Its driving me nuts.
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:eL8KFPUpDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > No problem, if your new to the SQL Server Agent this may be of use :-
> >
> > http://www.sqlpass.org/news/02feb/index.cfm?passnews=DBA1
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > news:ua218MUpDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > > Sorry.
> > > I can see it now.
> > >
> > >
> > > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > > news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> > > > " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> > > >
> > > > Where do I access the Enabled and the Shedule ?
> > > > Sorry for being ignorant but I have never done this before.
> > > >
> > > >
> > > > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > > > news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > > > > Could it be it's still running, or has it run but failed.
> > > > >
> > > > > In EnterPrise Manager :- Management
> > > > > SQL Server Agent
> > > > > Jobs
> > > > >
> > > > > If you right click your job and 'Show History' has it ever ran..?
> > > > >
> > > > > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> > > > >
> > > > > --
> > > > > HTH
> > > > > Ryan Waight, MCDBA, MCSE
> > > > >
> > > > > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > > > > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > > > > I have created a DTS Import routine and scheduled it to run
every
> > > hour.
> > > > > > I started the agent but despite that, the schedule never seems
to
> > > work.
> > > > > > I am forced to run it manually.
> > > > > >
> > > > > > Any Ideas how I can get it to work ?
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>sql
scheduled TRN backups failing (sql2000)
I have created a backup that does once weekly BAK and every night it does TRN backup. This is in the same maintaince plan, the jobs appear as seprate jobs as they should. I have noticed my TRN is always failing, after looking into the event logs i see this.
please note this is lastest time to early:
4:22pm
this is the warning that the event log gives:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'xxxxxxxx TRN'' (0xB87B2FA07E4FB74CA01A1D38134C9C4F) - Status: Failed - Invoked on: 2007-03-09 16:19:40 - Message: The job failed. The Job was invoked by User domain\Administrator. The last step to run was step 1 (Step 1).
the next is
4:22pm
18265 :
Log backed up: Database: Staging, creation date(time): 2007/01/30(09:35:34), first LSN: 24624:14008:1, last LSN: 24624:14021:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'f:\program files\microsoft sql server\MSSQL\BACKUP\Staging\Staging_tlog_200703091622.TRN'}).
last is 4:21pm
18264 :
Database backed up: Database: Staging, creation date(time): 2007/01/30(09:35:34), pages dumped: 93982, first LSN: 24624:14008:2, last LSN: 24624:14018:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'f:\program files\microsoft sql server\MSSQL\BACKUP\Staging\Staging_db_200703091618.BAK'}).
So for some very strange reason its running a full backup then a Transactional, for this reason i would say its failing but why is it even doing this backup? the job is only for TRN?
A manual TRN backup works like a treat
Thanks
BRAD
CHECK what is the recovery model of your database... make sure it is not Simple... If it is simple it is normal to have this behaviour
Madhu
|||ran sp_helpdb all of my databases are at full recovery apart from the tmpdb and another system DB but these are not in the backup plan only user databases are.
any other ideas?
|||never mind you were right there was the northwind database still active (by default set to simple) this is classed as a user DB.
Thanks
Brad
Subscribe to:
Posts (Atom)