I just had to rebuild master and msdb in Sql 2000. Now any DTS package accessing that machine fails with the message 'Server __ is not configured for DATA ACCESS.' The message seems to be coming from OLE. Everything else, including applications, scheduled jobs, etc. seems to work fine.
How can I restore data access?You may need to allow other SQL servers to connect. In enterprise manager, right-click on the server and select properties. On the Connections tab, check the box in the Remote Servers frame "Allow other SQL Servers to connect remotely to this SQL Server using RPC".|||No good. Properties showed remote connection OK. I unchecked, rechecked and rebooted to make sure. same problem.|||Can you create a connection to the server in a new DTS package?
It may be that you have to recreate the Server Connections in the existing package(s).|||No. Fails for both new and existing packages. The rebuild was apparently caused by converting databases from another machine from Sql 6.5. After the conversion we saw some odd things showing in msdb and lost alerts, operators etc. I also saw some table structure changes. Even though we didn't include msdb it looked like stuff from 6.5's was showing up. This looks to be a repercussion related to that.
Waht I can't seem to find anywhere is any discussion of the error. DTS's error window says the source is OLE but gives no eror number|||Just to add to the fun, I ignored the error message and built the queries without the point & click junk. Package ran fine. So it looks like all I've lost the visual references to that server.sql
Showing posts with label msdb. Show all posts
Showing posts with label msdb. Show all posts
Sunday, March 25, 2012
Sunday, February 26, 2012
DTS
Hi,
What is good way of executing .dtsx jobs in sql 2005, when stored in file format, or msdb database. Can my sql agent read jobs from file format as well as msdb database ?
Thanks &Regards
Nitu
Yes SQLAgent can execute file system or msdb packages. I'm surprised you could not find that information in Books Online. The online version is here ...
http://msdn2.microsoft.com/en-us/library/ms141701(SQL.90).aspx
At the bottom of every Books Online page there is an option to vote for the usefulness of the content. You can do that to help us improve the content - I'm concerned that you are not finding this information there, so any help you can give us would be appreciated.
Donald Farmer
DTS
Hi,
When i save a DTS package within msdb i must provide a
valid login to save it. If i schedule the package with the
DTS schedule options (right click on the DTS object), it
will create a job with a dtsrun step with the login and
password, that i provided when it was saved, encrypted for
load the DTS. I'm i right '
Suppose i save the package with dtsadmin user, where can i
found the information about the user that saved the
package ' i need this info ' suppose i change the user's
password, my job will not run any more until i rebuild the
DTSrun encrytion with the new password.
Thanks
PhilNot exactly. The package is saved with an owner who is the
logged in user who created the package. You do not have to
provide any information to save a package.
If you schedule it through Enterprise Manager, it is
scheduled with the currently logged in users credentials.
The owner and the credentials used when scheduling the
package can be different. Additionally, if it's all done
through Windows authentication, you wouldn't have a user id
and password.
The user and password used in the encrypted string for
dtsrun is not stored anywhere.
In terms of changing passwords, that's part of the reason
that Windows authentication is preferred. The biggest reason
is that it's more secure but it also often eliminates
maintenance issues such as this.
-Sue
On Fri, 2 Apr 2004 09:37:34 -0800, "Phil"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>When i save a DTS package within msdb i must provide a
>valid login to save it. If i schedule the package with the
>DTS schedule options (right click on the DTS object), it
>will create a job with a dtsrun step with the login and
>password, that i provided when it was saved, encrypted for
>load the DTS. I'm i right '
>Suppose i save the package with dtsadmin user, where can i
>found the information about the user that saved the
>package ' i need this info ' suppose i change the user's
>password, my job will not run any more until i rebuild the
>DTSrun encrytion with the new password.
>Thanks
>Phil
When i save a DTS package within msdb i must provide a
valid login to save it. If i schedule the package with the
DTS schedule options (right click on the DTS object), it
will create a job with a dtsrun step with the login and
password, that i provided when it was saved, encrypted for
load the DTS. I'm i right '
Suppose i save the package with dtsadmin user, where can i
found the information about the user that saved the
package ' i need this info ' suppose i change the user's
password, my job will not run any more until i rebuild the
DTSrun encrytion with the new password.
Thanks
PhilNot exactly. The package is saved with an owner who is the
logged in user who created the package. You do not have to
provide any information to save a package.
If you schedule it through Enterprise Manager, it is
scheduled with the currently logged in users credentials.
The owner and the credentials used when scheduling the
package can be different. Additionally, if it's all done
through Windows authentication, you wouldn't have a user id
and password.
The user and password used in the encrypted string for
dtsrun is not stored anywhere.
In terms of changing passwords, that's part of the reason
that Windows authentication is preferred. The biggest reason
is that it's more secure but it also often eliminates
maintenance issues such as this.
-Sue
On Fri, 2 Apr 2004 09:37:34 -0800, "Phil"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>When i save a DTS package within msdb i must provide a
>valid login to save it. If i schedule the package with the
>DTS schedule options (right click on the DTS object), it
>will create a job with a dtsrun step with the login and
>password, that i provided when it was saved, encrypted for
>load the DTS. I'm i right '
>Suppose i save the package with dtsadmin user, where can i
>found the information about the user that saved the
>package ' i need this info ' suppose i change the user's
>password, my job will not run any more until i rebuild the
>DTSrun encrytion with the new password.
>Thanks
>Phil
Subscribe to:
Posts (Atom)