DTS Through ASP Page permissions

Russell,

Thanks for the response and sorry for the late response. My priorities
got shifted for awhile and now I'm back on this.

I am using SQL Server 2000. I currently have a stored procedure that
is using xp_cmdshell to run DTSRUN.

I was trying to get the procedure to start a SQL Agent job but was
unsuccessful that way.

Also, do you know of a way to return an error if the DTS fails? I have
no way of knowing except to compare the number of rows in the table
before and after the job runs.

Thanks,

Tim



On Dec 9 2008, 9:41 am, "Russell Fields"
wrote:
> Tim,
>
> What version of SQL Server are you running?  When you say "running a DTS
> through a stored procedure", do you mean that you are starting a SQL Agent
> job, using xp_cmdshell to run DTSRUN.exe, or something else?
>
> If it is a SQL Agent job running as a sysadmin account, then the DTS would
> be running as the SQL Server service account.  If a SQL Agent job is running
> as a non-sysadmin, then the job will run as a proxy account.  (SQL Server
> 2000 only has one, but 2005 allows you to define many SQL Agent proxy
> accounts.)
>
> If you are running it via xp_cmdshell, the SQL Agent Proxy Account (2000) or
> (2005) the ##xp_cmdshell_proxy_account## credential's domain login identity.
>
> RLF
>
> "Tim" wrote in message
>
>
>
>
>
> > I'm running a DTS through a stored procedure called from an ASP page
> > on Server B.
>
> > Server B is also the SQL Server.
>
> > The DTS package is supposed to access a file on Server A.
>
> > I'm having permission issues in running this through the web. I've
> > tried giving IUSER_ServerB and IWAM_ServerB full permission on the
> > file but that didn't work.
>
> > What account needs permissions on the file? Do I need to add it
> > differently that I would the IUser/IWAM accounts?
>
> > Tim- Hide quoted text -
>
> - Show quoted text -
 >> Stay informed about: DTS Through ASP Page permissions 

Thanks for the ideas.

I will give them a try this week and let you know how it turns out.

I am running an asp page calling the stored proc. I was hoping there
was a way to have the asp page receive the error if it happened. I may
have to capture the log file and display it or a portion somehow on
the page.

Thanks,

Tim

On Jan 27, 12:00 pm, "tbradshaw via SQLMonster.com"
wrote:
> Hello Tim,
>
> Russell has good advice.  You also asked about returning an error.  To where?
>
> I have never tried to execute a package from within a stored procedure, but
> when I execute a package from a SQL Agent job using DTSRUN, the job will
> report failure when there is a failure within DTS.  So, perhaps on the next
> line following your DTSRUN, you could check @@ERROR for success/failure.
> Likewise you could throw an exception up the call chain back to your ASP page.
>
> In addition, you can capture your DTS execution in a log file.  In the
> designer window, right-click on an empty space & choose "package properties".
> In the logging tab, you can log to either SQL server or to an error file.
> For simplicity, I log to Error File by providing a path to a text file.
>
> Fair Warning:  Logging appends data to the file for each run, so you'll need
> some outside process to empty it from time to time.
>
> Let us know how you make out.
>
> Regards, Tom
>
> Thomas Bradshaw
> Data Integration Services
> MyWebGrocer
>
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-dts/200901/1
 >> Stay informed about: DTS Through ASP Page permissions 

Hello Tim,

Russell has good advice. You also asked about returning an error. To where?

I have never tried to execute a package from within a stored procedure, but
when I execute a package from a SQL Agent job using DTSRUN, the job will
report failure when there is a failure within DTS. So, perhaps on the next
line following your DTSRUN, you could check @@ERROR for success/failure.
Likewise you could throw an exception up the call chain back to your ASP page.


In addition, you can capture your DTS execution in a log file. In the
designer window, right-click on an empty space & choose "package properties".
In the logging tab, you can log to either SQL server or to an error file.
For simplicity, I log to Error File by providing a path to a text file.

Fair Warning: Logging appends data to the file for each run, so you'll need
some outside process to empty it from time to time.

Let us know how you make out.

Regards, Tom

Thomas Bradshaw
Data Integration Services
MyWebGrocer

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-dts/200901/1
 >> Stay informed about: DTS Through ASP Page permissions