DTS Fails as JOB

Hi Freinds,
SQL 2000
I have a DTS where reads a file from other server:
\\server2\uploads\tree.csv
When I'm inside the DTS and running it, then everything is fine. Even if I
right click on DTS and run , works fine.
The problem comes when I schedule the DTS as a job. JOB Fails : INVALID PATH
for \\server\.....
Is this a security issue? How can I solve it
Thanks in advance,
Patrick
Patrick
2 things. First make sure that the scheduled task is running either as SA or the Domain ID that the SQL Server is running under.
2 make sure that the ID that SQL is running as has access to the directory in question.
Jeff
MCDBA, MCSE+I
|||Also make sure that the SQL Agent Windows Service is configured to run under
the context of a domain user account. If it's configured to run under
LocalSystem context, the service can't see any network addresses.
Regards,
Greg Linwood
SQL Server MVP
"Patrick" <pashour@.mail.all-in-1.com> wrote in message
news:OYEQ4DxIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi Freinds,
> SQL 2000
> I have a DTS where reads a file from other server:
> \\server2\uploads\tree.csv
> When I'm inside the DTS and running it, then everything is fine. Even if I
> right click on DTS and run , works fine.
> The problem comes when I schedule the DTS as a job. JOB Fails : INVALID
PATH
> for \\server\.....
> Is this a security issue? How can I solve it
> Thanks in advance,
> Patrick
>

DTS Fails as JOB

Hi Freinds,
SQL 2000
I have a DTS where reads a file from other server:
\\server2\uploads\tree.csv
When I'm inside the DTS and running it, then everything is fine. Even if I
right click on DTS and run , works fine.
The problem comes when I schedule the DTS as a job. JOB Fails : INVALID PATH
for \\server\.....
Is this a security issue? How can I solve it
Thanks in advance,
PatrickPatrick
2 things. First make sure that the scheduled task is running either as SA o
r the Domain ID that the SQL Server is running under.
2 make sure that the ID that SQL is running as has access to the directory i
n question.
Jeff
MCDBA, MCSE+I|||Also make sure that the SQL Agent Windows Service is configured to run under
the context of a domain user account. If it's configured to run under
LocalSystem context, the service can't see any network addresses.
Regards,
Greg Linwood
SQL Server MVP
"Patrick" <pashour@.mail.all-in-1.com> wrote in message
news:OYEQ4DxIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi Freinds,
> SQL 2000
> I have a DTS where reads a file from other server:
> \\server2\uploads\tree.csv
> When I'm inside the DTS and running it, then everything is fine. Even if I
> right click on DTS and run , works fine.
> The problem comes when I schedule the DTS as a job. JOB Fails : INVALID
PATH
> for \\server\.....
> Is this a security issue? How can I solve it
> Thanks in advance,
> Patrick
>sql

DTS / Stored Proceedure

How can you run a DST package from a stored proceedure.
I am using sql server 2000
i cant find the syntax anywhere
it is a DTS that takes a file and imports it into a table in the dbi was looking around and someone said to try to run this at the sql prompt

EXEC xp_cmdshell 'dtsrun /S /U /P /N
exportEmail'

where exportEmail is the dts
when i do this though it says xp_cmdshell is a stored procedure that cant be found
is this something i have to make or is it something that should be there

http://www.dbforums.com/t688385.html|||i tried this method

http://p2p.wrox.com/topic.asp?TOPIC_ID=9741

and then it came back with "you dont have permission for xp_cmdShell" in short
where is this function located and where can i change permissions|||xp_cmdshell is found in the Master database. Seexp_cmdshell for more information.

Terri|||i set the permissions and i used the command

CREATE PROCEDURE MassImport AS
exec master..xp_cmdshell 'dtsrun /S /N /E'
GO

and i got the error

".Net SqlClient Data ProviderA severe error occurred on the current command. The results, if any, should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on link 472"

Is my stored proceedure correct
did I miss something does it not like the permissions
i am going to try to dig deeper into the link you gave me. But if someone knows a quick fix please tell me|||The problem I see with the stored procedure is that you did not supply the package name (exportEmail) with the /N switch, and you used the /S switch to indicate which server you were running against but did not supply the server name.

Note that you can run DTSRUN /? from the command prompt to see an explanation of each of the switches.

Terri|||Looking at the help in the command prompt this should be correct
my package name is "Mass Import" and this is a local sql server
Does this require no spaces in the package name or do i need quotes somewhere in the statement

here is my new stored procedure
and i get the same error message

exec master..xp_cmdshell 'dtsrun /S(local) /N(Mass Import) /E'

when i looked at the help /s and /n are both optional and /e is needed
/e seems to be the only one required.

i also tried it at a command prompt and this is the error i got.

H:\>dtsrun /S(local) /N(Mass Import) /E
DTSRun: Loading...
Error: -2147217900 (80040E14); Provider Error: 14262 (37B6)
 Error string: The specified DTS Package ('Name = '(Mass Import) '; ID.Versio
nID = {[not specified]}.{[not specified]}') does not exist.
 Error source: Microsoft OLE DB Provider for SQL Server
 Help file:
 Help context: 0

|||Did you try enclosing your package name in double quotes?

H:\>dtsrun /S "(local)" /N "Mass Import" /E

Terri|||gives me the same error

H:\>dtsrun /S "(local)" /N "Mass Import" /E
DTSRun: Loading...
Error: -2147217900 (80040E14); Provider Error: 14262 (37B6)
 Error string: The specified DTS Package ('Name = '"Mass Import" '; ID.Versio
nID = {[not specified]}.{[not specified]}') does not exist.
 Error source: Microsoft OLE DB Provider for SQL Server
 Help file:
 Help context: 0

|||I've tried that exact syntax and it works for me. So I would guess that either the Server is wrong or the Package Name is wrong.

You might try the dtsrunui.exe tool. This will enable you to visually set up your dtsrun statement.

From the command prompt, run this tool, select your Server from the drop-down (or type it in if it does not appear), choose the Authentication method, then choose the Package Name from the dropdown. When you have all of this set, click on the "Advanced..." button, and on this popup, click the "Generate..." button towards the bottom. This will drop in the generated dtsrun command in the box to the left. You should be able to use this generated command to run your package (by copying and pasting it into your stored procedure).

Terri|||that tool is cool thank you :)

here is the new command it made me run

DTSRun /S "(local)" /N "Mass Import" /G "{73D07FB3-E2D4-4A92-B69F-20C746F00DC1}" /W "0" /E

|||alright now my stored proceedure runs correctly in the command prompt and in the sql query analyzer
but for some reason it isnt working in my code

here is my code

 Dim cmdMassImport As New SqlCommand("MassImport", C.ndConnection)
 cmdMassImport.CommandType = CommandType.StoredProcedure
 C.ndConnection.Open()
 cmdMassImport.ExecuteNonQuery()
 C.ndConnection.Close()

Here is the error

A severe error occurred on the current command. The results, if any, should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on line 472
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: A severe error occurred on the current command. The results, if any, should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on line 472
Source Error:
Line 5760:
Line 5761: C.ndConnection.Open()
Line 5762: cmdMassImport.ExecuteNonQuery() <-- Says error is occuring here
Line 5763: C.ndConnection.Close()
Line 5764: End Sub
Source File: e:\Inetpub\wwwroot\Objectmanipulation\panelMaker.aspx.vb Line: 5762
Stack Trace:
[SqlException: A severe error occurred on the current command. The results, if any, should be discarded.
xpsql.cpp: Error 997 from GetProxyAccount on line 472
]
 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
 Objectmanipulation.panelMaker.btn_Click(Object sender, EventArgs e) in e:\Inetpub\wwwroot\Objectmanipulation\panelMaker.aspx.vb:5762
 System.Web.UI.WebControls.Button.OnClick(EventArgs e)
 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
 System.Web.UI.Page.ProcessRequestMain()

Am I missing something stupid that I need to initialize to make the stored proceedure work?
or is there something deep that needs to be fixed|||Please??
does this seem to be a code thing
i cant seem to see that i missed anything with the stored proceedure call|||Ack, sorry, I didn't mean to leave you hanging. :-(

I have to try this out myself and get back to you -- I'll try not to make it later than tonight.

And if anyone has a suggestion -- please chime in!

Terri|||After playing around with this for a while and looking at Google, it's a permissions problem with xp_cmdshell. I am not going to be able to talk you through it because I don't thoroughly understand it -- the DBA at my place of employment set this up for me when I needed it.

Terri

DTS - Excel file path from a table

SQL 2000
I got a table with file names and path for the Excel files to be loaded into
a sql table.
My plan is to use DTS and global variable to assign the path and file name
and use that as the source for the data-pump.
Any thoughts or sample code to achieve this ?
Thanks In Advance
Jeff
You would probably want to have a look at these two articles then.
Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"MS User" <sqlman@.sql.com> wrote in message
news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
> SQL 2000
> I got a table with file names and path for the Excel files to be loaded
> into a sql table.
> My plan is to use DTS and global variable to assign the path and file name
> and use that as the source for the data-pump.
> Any thoughts or sample code to achieve this ?
> Thanks In Advance
> Jeff
>
|||Thanks Allan
My Excel sheet contains different sheets and I just want to read one sheet
to load into table. Thoughts/Ideas ?
Thanks
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eAuNYX74EHA.828@.TK2MSFTNGP14.phx.gbl...
> You would probably want to have a look at these two articles then.
> Looping, Importing and Archiving
> (http://www.sqldts.com/default.aspx?246)
> How to loop through a global variable Rowset
> (http://www.sqldts.com/default.aspx?298)
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
> "MS User" <sqlman@.sql.com> wrote in message
> news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
>
|||The worksheets/named ranges within a workbook will be exposed to DTS as
table objects.
So this will give you the ability to change the DataSource property of the
Excel workbook and the SourceObjectName of the DataPump task.

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"MS User" <sqlman@.sql.com> wrote in message
news:O%23LVGP84EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Thanks Allan
> My Excel sheet contains different sheets and I just want to read one sheet
> to load into table. Thoughts/Ideas ?
> Thanks
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eAuNYX74EHA.828@.TK2MSFTNGP14.phx.gbl...
>
|||Thanks Allan. That did it.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:%23B%23e8JA5EHA.828@.TK2MSFTNGP14.phx.gbl...
> The worksheets/named ranges within a workbook will be exposed to DTS as
> table objects.
> So this will give you the ability to change the DataSource property of the
> Excel workbook and the SourceObjectName of the DataPump task.
>
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
> "MS User" <sqlman@.sql.com> wrote in message
> news:O%23LVGP84EHA.2540@.TK2MSFTNGP09.phx.gbl...
>