Error when trying to execute a job from windows service.

Hi,
I'm trying to execute a DTS package from a .NET service. The service is
running under a domain account that owns the job which it executes using
'sp_start_job'. The call to the sp succeeds however checking the Job history
gives: -
"The job failed. Unable to determine if the owner (DOMAIN\ServiceUser) of
job <JobName> has server access (reason: Could not obtain information about
Windows NT group/user 'DOMAIN\ServiceUser'. [SQLSTATE 42000] (Error 8198
))."
SQL Server 2000 SP4
Both SQLServer + SQLAgent run under a domain account (different to the
service)
ServiceUser has login to sql server not an admin, however I've tried using
the SQL Agent Proxy account still no success.
Any ideas?
thanks Paul> SQL Server 2000 SP4
> Both SQLServer + SQLAgent run under a domain account (different to the
> service)
> ServiceUser has login to sql server not an admin, however I've tried using
> the SQL Agent Proxy account still no success.
The SQL 2000 Books Online <instsql.chm::/in_overview_6k1f.htm> states that
the SQL Agent service account must be a member of the local Administrators
group in order to "Create CmdExec and ActiveScript jobs belonging to someone
other than a SQL Server administrator".
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul Coulson" <nospam@.nospam.net> wrote in message
news:OZUDQ3WOGHA.3144@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm trying to execute a DTS package from a .NET service. The service is
> running under a domain account that owns the job which it executes using
> 'sp_start_job'. The call to the sp succeeds however checking the Job
> history gives: -
> "The job failed. Unable to determine if the owner (DOMAIN\ServiceUser) of
> job <JobName> has server access (reason: Could not obtain information
> about Windows NT group/user 'DOMAIN\ServiceUser'. [SQLSTATE 42000] (Er
ror
> 8198))."
> SQL Server 2000 SP4
> Both SQLServer + SQLAgent run under a domain account (different to the
> service)
> ServiceUser has login to sql server not an admin, however I've tried using
> the SQL Agent Proxy account still no success.
> Any ideas?
> thanks Paul
>|||Hi Dan,
Thanks that's sorted it. Also got it working using the proxy account
again this needed local admin rights.
Cheers Paul
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ueU6PriOGHA.1192@.TK2MSFTNGP11.phx.gbl...
> The SQL 2000 Books Online <instsql.chm::/in_overview_6k1f.htm> states that
> the SQL Agent service account must be a member of the local Administrators
> group in order to "Create CmdExec and ActiveScript jobs belonging to
> someone other than a SQL Server administrator".
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul Coulson" <nospam@.nospam.net> wrote in message
> news:OZUDQ3WOGHA.3144@.TK2MSFTNGP11.phx.gbl...
>|||I'm glad you were able to get it working. However, I'd be leery about
granting admin rights the proxy account since that allows non-sysadmins to
run processes with admin rights. Now that you got the service accounts
sorted out, I would expect your DTS package to run fine as long as the proxy
account has permissions on data and files accessed by the package.
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul Coulson" <nospam@.nospam.net> wrote in message
news:eCdaOR4OGHA.812@.TK2MSFTNGP10.phx.gbl...
> Hi Dan,
> Thanks that's sorted it. Also got it working using the proxy account
> again this needed local admin rights.
> Cheers Paul
>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:ueU6PriOGHA.1192@.TK2MSFTNGP11.phx.gbl...
>

Error when trying to edit a DTS package - please help

SQL2000 sp2 and sp3a
I'm having trouble getting into DTS design view on a
particular pc. I can get in fine on other pcs. This
apparently started happening just after installing sp3a on
my local machine. The pc I am using can't be rebuilt and
I can't use a different pc. I get a couple of error
messages. After clicking design package, I ge the
following error "Error occurred during creation of a DTS
Package". After clicking ok, I get "The selected package
cannot be opened. The DTS Designer has been closed".
This happens on all packages on all servers (and local)
for the pc I am using. I found one bit of information on
the net, but it didn't help. It was at:
http://www.sqldts.com/default.aspx?228 I then uninstalled
sql and reinstalled. That still didn't work. I've
uninstalled and put sp2, sp3a, different combinations a
few times and still no luck. Has anyone experienced this
or does anyone have any suggestions?
Thanks,
Van JonesThis can happen if the MDAC installation gets corrupted. Since you have
installed SQL Server SP3 then you have MDAC 2.7 SP1. The only way to
correct the MDAC installation would be to install MDAC 2.8 on this server.
Unless this is will cause a problem for you, you can possibly fix the
problem by installing MDAC 2.8.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Error when schedule a job in SQL 2005

Hi,

I have a SSIS package that contains an Execute DTS 2000 process. This DTS 2000 is calling a legacy package, which resides on the same server. The legacy package is just to import a flat file into the database. When i right click and execute this package, it runs perfectly without any errors. However, if i try to schedule this package as a job, it keeps failing.

I checked the flat file source for the permission, it does have the account of the executor of the package. In the view history, i didn't see any more information about why it is failing. It keeps saying "The job failed. The Job was invoked by Schedule 10...."

Does anyone have any ideas?

Did you try to turn on logging so you could get better error details?|||

Thanks, I turned on the log and it wrote to the application log events saying. Is there another log event in SQL 2005? I only saw it said "View History" in the scheduled job.

Status: Failed - Invoked on: 2006-11-06 13:56:00 - Message: The job failed. The Job was invoked by Schedule 24 (Test Schedule). The last step to run was step 1 (RUN ImportData)

|||See this KB about turning on logging and general troubleshooting of SSIS packages under Agent:
http://support.microsoft.com/kb/918760

error when running the DTSRUN command

Hi
We're using SqlServer 2000.

I want to run a DTS using the DTSRUN command.
The commmand is inside a ".bat" file which my application is running
from an SQL client.
No matter how I run it from the client it fails giving me the message:

[ConnectionOpen (Connect()).]SQL Server does not exist or access denied

I've tried to run it from the client from the application, from the
command prompt and it fails.
I went into enterprise on the client and tried to run the DTS from
there. The run failed already on the 1st step which is a call to a
stored procedure, also giving me the same message. This really stuck me
as wierd because in enterprise I'm accessing the server successfully so
why the above messgae. It identifies the server and I am accessing it.

Because it failed on the 1st step which is a call to a stored procedure
I tried running the sp from within query analyzer and it succeeded.

Has anyone got any idea what the problem is and ghow to solve it.

Thanks !

David GreenbergDavid Greenberg (davidgr@.iba.org.il) writes:

Quote:

Originally Posted by

I want to run a DTS using the DTSRUN command.
The commmand is inside a ".bat" file which my application is running
from an SQL client.
No matter how I run it from the client it fails giving me the message:
>
[ConnectionOpen (Connect()).]SQL Server does not exist or access denied
>
I've tried to run it from the client from the application, from the
command prompt and it fails.
I went into enterprise on the client and tried to run the DTS from
there. The run failed already on the 1st step which is a call to a
stored procedure, also giving me the same message. This really stuck me
as wierd because in enterprise I'm accessing the server successfully so
why the above messgae. It identifies the server and I am accessing it.
>
Because it failed on the 1st step which is a call to a stored procedure
I tried running the sp from within query analyzer and it succeeded.
>
Has anyone got any idea what the problem is and ghow to solve it.

You can't accuse me to know too much about DTS, but I see that a
DTS package has connection properties. I would assume that you can
have a package on server A, and run it against server B.

So I would check the connection properties of the package (or the step,
as different task surely must be able to connect to different servers).

The error message simply means that you are trying to connect to a
server that is not available. (Not running, misspelled, a firewall
in the way etc.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Error when running DTS package as job

I have a DTS package which deletes some rows in a table and then inserts new ones from a Dbase file.

When I execute the package it works perfectly, but when I shedule it as a job it gives this error message:

Executed as user: LOMMELDOM\Administrator. ...DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: Copy Data from GRES to [ZNA].[dbo].[GRES] Step DTSRun OnError: Copy Data from GRES to [ZNA].[dbo].[GRES] Step, Error = -2147221164 (80040154) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4700 Error: -2147221164 (... Process Exit Code 5. The step failed.

The deleting of the rows works but the import from the Dbase doens't, I thought it was a security issue. So I made sure the JobAgent has all the rights it needs to access the directory of the Dbase file.

Any clues?

You are using a custom class / transform / task that is not registered on the server. You will need to register it through the GAC....|||But how come it works perfectly on the same server when I just execute the package? And on the same server it fails if job agent does it?|||

You are executing it on YOUR machine, even if it is hosted on the server.

Edit: Unless if you are running it from the job by right clicking and hitting execute job starting at step...

|||

Hi Eric,

Thanks for the reply.

Euh, my machine = server so there is no other machine around.

It fails when sheduled but it also fails when I right click the job and say execute. It only works if I right click the package and execute that! Further there are no other steps in the job and the job was created by right clicking on the package and sheduling it.

|||Looking a bit more closely at your error messages, are you running SSIS (SQL 2005)? It kind of looks like you are still using SQL 2000 and DTS...|||Yes I'm using SQL2000 with DTS, that's why I also mentionned this in the title of my question. I didn't find a DTS specific newsgroup.|||Try this one: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

Error when running DTS package as job

I have a DTS package which deletes some rows in a table and then inserts new ones from a Dbase file.

When I execute the package it works perfectly, but when I shedule it as a job it gives this error message:

Executed as user: LOMMELDOM\Administrator. ...DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: Copy Data from GRES to [ZNA].[dbo].[GRES] Step DTSRun OnError: Copy Data from GRES to [ZNA].[dbo].[GRES] Step, Error = -2147221164 (80040154) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4700 Error: -2147221164 (... Process Exit Code 5. The step failed.

The deleting of the rows works but the import from the Dbase doens't, I thought it was a security issue. So I made sure the JobAgent has all the rights it needs to access the directory of the Dbase file.

Any clues?

You are using a custom class / transform / task that is not registered on the server. You will need to register it through the GAC....|||But how come it works perfectly on the same server when I just execute the package? And on the same server it fails if job agent does it?|||

You are executing it on YOUR machine, even if it is hosted on the server.

Edit: Unless if you are running it from the job by right clicking and hitting execute job starting at step...

|||

Hi Eric,

Thanks for the reply.

Euh, my machine = server so there is no other machine around.

It fails when sheduled but it also fails when I right click the job and say execute. It only works if I right click the package and execute that! Further there are no other steps in the job and the job was created by right clicking on the package and sheduling it.

|||Looking a bit more closely at your error messages, are you running SSIS (SQL 2005)? It kind of looks like you are still using SQL 2000 and DTS...|||Yes I'm using SQL2000 with DTS, that's why I also mentionned this in the title of my question. I didn't find a DTS specific newsgroup.|||Try this one: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

Error when opening a Lookup in a Legacy DTS package

Hi all,

the SQL Management Studio keeps crashing out on me everytime i try to open a Lookup in one of my DTS packages i am using in the Legacy section.

I am copying the DTS package across and need to change the server connections (which i do), but then i was getting a permissions based error when the package ran when it tries to access the Lookup.

I tried to open the Lookup and the SQL Management Studio hanged when it tried to display the details for the lookup. It's done this many times and i have tried different files incase one was corrupted to no avail.

Anyone have any ideas of what i can try?

Thanks

Jordan

Hi Jordan,

Want to know the following:

1) Have you installed DTS 2000 Designer utility from the Feature Pack release?

2) Once the above utility is installed and then importing the DTS package into Legacy section of Management Studio, are you able to successfully decrypt the Package with password you have used to encrypt and open it successfully?

3) Assuming that this file is corrupt, another way is to embed your DTS Package in a newly created SSIS Package using DTS 2000 Task.

Hope ensuring the above steps should solve your problem.

Thanks

Subhash Subramanyam

|||

Hi Subhash,

1) How do i determine this? I can already view the DTS package in the Legacy section of 2005, does this mean it is already installed?

2) To install the package i have Saved it out of SQL 2000 as a DTS file and opened the package in SQL 2005 and saved it to the server. Should i be doing something else?

3) i haven't looked at SSIS yet. I'm not sure what that would involve.

Thanks

Jordan

Subhash512525 wrote:

Hi Jordan,

Want to know the following:

1) Have you installed DTS 2000 Designer utility from the Feature Pack release?

2) Once the above utility is installed and then importing the DTS package into Legacy section of Management Studio, are you able to successfully decrypt the Package with password you have used to encrypt and open it successfully?

3) Assuming that this file is corrupt, another way is to embed your DTS Package in a newly created SSIS Package using DTS 2000 Task.

Hope ensuring the above steps should solve your problem.

Thanks

Subhash Subramanyam

Error when executing DTS from sp_OA

What does this error mean? I get this error when i execute a DTS
package using SP_OA from within T-SQL....
I'm stumped.
0x80070008 Microsoft Data Transformation Services (DTS) Package
Not enough storage
is available to process this command.This may indicate the memory size is not big enough to hold distributed
query operations. You need to restart sql server to refresh memory.
"Shaun Farrugia" <far!!!!ugia!!!s@.dte!!!!ener!gy.com> wrote in message
news:e3FQe1E5DHA.2656@.TK2MSFTNGP11.phx.gbl...

quote:

---

> What does this error mean? I get this error when i execute a DTS
> package using SP_OA from within T-SQL....
> I'm stumped.
>
> 0x80070008 Microsoft Data Transformation Services (DTS) Package
>
> Not enough storage
> is available to process this command.
>

---

|||That's what I'm thinking. I just don't want to have to restart all the
time to refresh memory. Is this a bug?
Richard Ding wrote:

quote:

---

> This may indicate the memory size is not big enough to hold distributed
> query operations. You need to restart sql server to refresh memory.
> "Shaun Farrugia" <far!!!!ugia!!!s@.dte!!!!ener!gy.com> wrote in message
> news:e3FQe1E5DHA.2656@.TK2MSFTNGP11.phx.gbl...
>
>
>

---

Error when executing DTS from sp_OA

What does this error mean? I get this error when i execute a DTS
package using SP_OA from within T-SQL....
I'm stumped.
0x80070008 Microsoft Data Transformation Services (DTS) Package
Not enough storage
is available to process this command.This may indicate the memory size is not big enough to hold distributed
query operations. You need to restart sql server to refresh memory.
"Shaun Farrugia" <far!!!!ugia!!!s@.dte!!!!ener!gy.com> wrote in message
news:e3FQe1E5DHA.2656@.TK2MSFTNGP11.phx.gbl...
> What does this error mean? I get this error when i execute a DTS
> package using SP_OA from within T-SQL....
> I'm stumped.
>
> 0x80070008 Microsoft Data Transformation Services (DTS) Package
>
> Not enough storage
> is available to process this command.
>|||That's what I'm thinking. I just don't want to have to restart all the
time to refresh memory. Is this a bug?
Richard Ding wrote:
> This may indicate the memory size is not big enough to hold distributed
> query operations. You need to restart sql server to refresh memory.
> "Shaun Farrugia" <far!!!!ugia!!!s@.dte!!!!ener!gy.com> wrote in message
> news:e3FQe1E5DHA.2656@.TK2MSFTNGP11.phx.gbl...
>>What does this error mean? I get this error when i execute a DTS
>>package using SP_OA from within T-SQL....
>>I'm stumped.
>>
>>0x80070008 Microsoft Data Transformation Services (DTS) Package
>>
>> Not enough storage
>>is available to process this command.
>
>

Error when creating new connection in DTS Designer

64b SQL2005 EE and all clent tools installed on the same server. Im trying to create a new connection in DTS Designer but after succesful test of the connection it errors out with

The new connection manager couldnt be created. Additional info : Exeption from HRESULT 0xC0010014

Any idea whats the problem here?

This might be caused by a problem described here:

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

|||Im logged in with local admin account. Does that count? Still SSIS is running under Network Servise|||Usually the service does the component enumeration and caches the results, so this is performed without admin rights. You can check if this is an issue by temporary disabling the service, and trying this again (restart BIDS first). If this is caused by permission issue, you'll likely succeed because BIDS will do the component enumeration itself (this harms performance, but avoids Network Service restrictions).

Error trying to copy stored procedures from one db to another

Hello,

The export menu in DTS is a bit confusing for me. I have some stored procedures in one SQL Server and want to copy them to another. The username is the same for both SQL Servers. When I try copying the stored procedure with defaul settings, I get a progress bar (24 percent ccompleted, 62 percent completed...), then when its done it says "failed to copy objects from sql server to sql server". When I double click the error for details it says "User or role '[username]' already exists in the current database". Then I try again, unchecking "use default options" for my export. I go in to alter the options, uncheck "copy database users and database groups" (I've also tried unchecking "copy object-level permissions"), run it, get progress bar (like above), and then get another error that says "There is no such user or group '[username]'".

My user name is correct, I am able to access both Sql Servers with this username, and I should have all of the appropriate permissions.

I think maybe there is a checkbox I'm not unchecking or something stupid like that.

Thanks in advance for any feedback.Wild guess here: the user who owns the sp is not dbo ?|||Wild guess here: the user who owns the sp is not dbo ?

Sorry, your msg is a bit cryptic for me. What are "sp" and "dbo"? My guess on the latter is "database owner". I will check with the administrator to find out if I can get a username with more privileges. It is a shared Sql Server with a web hosting company and there are dozens of databases that I can see in there, but do not have access to, so users and permissions are a little hairy, i'm sure.

Error traping in DTS

I am using a DTS for my purpose and I am calling that DTS execution in a stored procedure(using master..xp_cmdshell dtsrun/F).I like to store the error meassage or error description in the Stored Procedure if it fails due to any reason.How can I do that?call the dtsrun also with /L<log file path >|||Will you kindly give me a brief of that please?|||dtsrun gets some arguments the /L <file path > switch "tells" dtsrun where to log it executed steps results
for more info search books online for "dtsrun Utility" there you can find all info about dtsrun.