Ability to schedule job across Domains

Situation: I have to copy data from a host SQL Server and place it onto my
SQL Server. We have an intermediary box with Console installed with both SQL
Servers registered. I have setup a DTS that performs the transfer.
Problem: I can run the DTS manually but when I schedule the job to run
automatically I get an error message that the job cannot be run because the
user is a nonSysAdmin.
That is because the context of the package is different when you run it as a
job, instead of you running it. See if this helps:
http://support.microsoft.com/default...269074&sd=tech
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
news:D1FAB676-20A5-4DE1-A20E-90C5114A10BB@.microsoft.com...
Situation: I have to copy data from a host SQL Server and place it onto my
SQL Server. We have an intermediary box with Console installed with both
SQL
Servers registered. I have setup a DTS that performs the transfer.
Problem: I can run the DTS manually but when I schedule the job to run
automatically I get an error message that the job cannot be run because the
user is a nonSysAdmin.
|||Since the 1st box is accessed thru VPN, we have a 2nd box that acts as the
connecting device with console and is outside our domain, and the 3rd box is
inside our domain with SQL installed. I made the login, for test purposes a
member of sysadmin. This allowed me to schedule the job and it would run as
scheduled but now I get a message that it cannot find the server referenced
(a simply sql statement is my test to the 1st box).
Here is what the job history tells me:
Executed as user: TEST1\SYSTEM. DTSRun: Loading... DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError:
DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005) Error
string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0 Error Detail Records: Error:
-2147467259 (80004005); Provider Error: 17 (11) Error string:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Thanks a bunch for you help on this.
"Narayana Vyas Kondreddi" wrote:

> That is because the context of the package is different when you run it as a
> job, instead of you running it. See if this helps:
> http://support.microsoft.com/default...269074&sd=tech
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
> news:D1FAB676-20A5-4DE1-A20E-90C5114A10BB@.microsoft.com...
> Situation: I have to copy data from a host SQL Server and place it onto my
> SQL Server. We have an intermediary box with Console installed with both
> SQL
> Servers registered. I have setup a DTS that performs the transfer.
> Problem: I can run the DTS manually but when I schedule the job to run
> automatically I get an error message that the job cannot be run because the
> user is a nonSysAdmin.
>
>

Ability to schedule job across Domains

Situation: I have to copy data from a host SQL Server and place it onto my
SQL Server. We have an intermediary box with Console installed with both SQ
L
Servers registered. I have setup a DTS that performs the transfer.
Problem: I can run the DTS manually but when I schedule the job to run
automatically I get an error message that the job cannot be run because the
user is a nonSysAdmin.That is because the context of the package is different when you run it as a
job, instead of you running it. See if this helps:
http://support.microsoft.com/defaul...;269074&sd=tech
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
news:D1FAB676-20A5-4DE1-A20E-90C5114A10BB@.microsoft.com...
Situation: I have to copy data from a host SQL Server and place it onto my
SQL Server. We have an intermediary box with Console installed with both
SQL
Servers registered. I have setup a DTS that performs the transfer.
Problem: I can run the DTS manually but when I schedule the job to run
automatically I get an error message that the job cannot be run because the
user is a nonSysAdmin.|||Since the 1st box is accessed thru VPN, we have a 2nd box that acts as the
connecting device with console and is outside our domain, and the 3rd box is
inside our domain with SQL installed. I made the login, for test purposes a
member of sysadmin. This allowed me to schedule the job and it would run as
scheduled but now I get a message that it cannot find the server referenced
(a simply sql statement is my test to the 1st box).
Here is what the job history tells me:
Executed as user: TEST1\SYSTEM. DTSRun: Loading... DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError:
DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005) Error
string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not
exist or
access denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0 Error Detail Records: Error:
-2147467259 (80004005); Provider Error: 17 (11) Error string:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access
denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Thanks a bunch for you help on this.
"Narayana Vyas Kondreddi" wrote:

> That is because the context of the package is different when you run it as
a
> job, instead of you running it. See if this helps:
> http://support.microsoft.com/defaul...;269074&sd=tech
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
> news:D1FAB676-20A5-4DE1-A20E-90C5114A10BB@.microsoft.com...
> Situation: I have to copy data from a host SQL Server and place it onto my
> SQL Server. We have an intermediary box with Console installed with both
> SQL
> Servers registered. I have setup a DTS that performs the transfer.
> Problem: I can run the DTS manually but when I schedule the job to run
> automatically I get an error message that the job cannot be run because th
e
> user is a nonSysAdmin.
>
>

Ability to schedule job across Domains

Situation: I have to copy data from a host SQL Server and place it onto my
SQL Server. We have an intermediary box with Console installed with both SQL
Servers registered. I have setup a DTS that performs the transfer.
Problem: I can run the DTS manually but when I schedule the job to run
automatically I get an error message that the job cannot be run because the
user is a nonSysAdmin.That is because the context of the package is different when you run it as a
job, instead of you running it. See if this helps:
http://support.microsoft.com/default.aspx?scid=kb;en-us;269074&sd=tech
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
news:D1FAB676-20A5-4DE1-A20E-90C5114A10BB@.microsoft.com...
Situation: I have to copy data from a host SQL Server and place it onto my
SQL Server. We have an intermediary box with Console installed with both
SQL
Servers registered. I have setup a DTS that performs the transfer.
Problem: I can run the DTS manually but when I schedule the job to run
automatically I get an error message that the job cannot be run because the
user is a nonSysAdmin.|||Since the 1st box is accessed thru VPN, we have a 2nd box that acts as the
connecting device with console and is outside our domain, and the 3rd box is
inside our domain with SQL installed. I made the login, for test purposes a
member of sysadmin. This allowed me to schedule the job and it would run as
scheduled but now I get a message that it cannot find the server referenced
(a simply sql statement is my test to the 1st box).
Here is what the job history tells me:
Executed as user: TEST1\SYSTEM. DTSRun: Loading... DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError:
DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005) Error
string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0 Error Detail Records: Error:
-2147467259 (80004005); Provider Error: 17 (11) Error string:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Thanks a bunch for you help on this.
"Narayana Vyas Kondreddi" wrote:
> That is because the context of the package is different when you run it as a
> job, instead of you running it. See if this helps:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;269074&sd=tech
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
> news:D1FAB676-20A5-4DE1-A20E-90C5114A10BB@.microsoft.com...
> Situation: I have to copy data from a host SQL Server and place it onto my
> SQL Server. We have an intermediary box with Console installed with both
> SQL
> Servers registered. I have setup a DTS that performs the transfer.
> Problem: I can run the DTS manually but when I schedule the job to run
> automatically I get an error message that the job cannot be run because the
> user is a nonSysAdmin.
>
>

A way to recursively look up hierarchal data using a lookup table

I have found the Common Table Expressions described in SQL 2005 and I
am not sure if it applies to this situation.

Here are the tables

<PRE>
<B>ManagedServer Table</B>
--IdManagedServer (PK, int, not Null)
--Name (nvarchar(256), not null)

<B>ManagedServerToManagedServer Table</B>
--IdParentManagedServer (PK, int, not null)
--IdChildManagedServer (PK, int, not null)
</PRE
The following will give you the parent

-- Get Managed Server Group Names
LEFT OUTER JOIN ManagedServerToManagedServer mstms ON
ms.IdManagedServer = mstms.IdChildManagedServer
LEFT OUTER JOIN ManagedServer msg ON mstms.IdParentManagedServer =
msg.IdManagedServer

How would you go about getting all of the "parents" in the tree?
Can this be done with CTEs? Unfortuately all of the examples found are
joining on itself.(patuww@.yahoo.com) writes:
> I have found the Common Table Expressions described in SQL 2005 and I
> am not sure if it applies to this situation.
> Here are the tables
><PRE>
><B>ManagedServer Table</B>
> --IdManagedServer (PK, int, not Null)
> --Name (nvarchar(256), not null)
><B>ManagedServerToManagedServer Table</B>
> --IdParentManagedServer (PK, int, not null)
> --IdChildManagedServer (PK, int, not null)
></PRE>
> The following will give you the parent
> -- Get Managed Server Group Names
> LEFT OUTER JOIN ManagedServerToManagedServer mstms ON
> ms.IdManagedServer = mstms.IdChildManagedServer
> LEFT OUTER JOIN ManagedServer msg ON mstms.IdParentManagedServer =
> msg.IdManagedServer
> How would you go about getting all of the "parents" in the tree?
> Can this be done with CTEs? Unfortuately all of the examples found are
> joining on itself.

For this type of query, it is also a good idea to post:

o CREATE TABLE statement(s) for the involved table(s).
o INSERT statement with sample data.
o The desired output given the sample.

This makes it easy to copy and paste and post a tested solution.

Judging from the table design, it appears that a child can have many
parents, which makes sort of interesting for the output.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp