Re: Problems launching DTS package from Stored Procedure

From: ATJaguarX (mrjboner_at_yahoo.com)
Date: 12/15/04 

Date: Wed, 15 Dec 2004 12:11:56 -0600

Here is an example of a SUCCESSFUL sql profiler log:

----------------------------------------------------------

Audit Login

exec sp_oledb_ro_usrname
select collationname(0x0904D00034)
drop table [Users1]
CREATE TABLE [Users1]
exec sp_bcp_dbcmptlevel "UsersDatabase" SET FMTONLY ON Select * from Users1
SET FMTONLY OFF exec "mpfloor_UsersDatabase"..sp_tablecollations
'"mpfloor_UsersDatabase"..Users1'

Audit Logout

----------------------------------------------------------

Here is an example of an UNSUCCESSFUL sql profiler log:

----------------------------------------------------------

Audit Login

exec sp_oledb_ro_usrname
select collationname(0x0904D00034)
drop table [Users2]
CREATE TABLE [Users2]

Audit Logout

----------------------------------------------------------

You can notice after it creates the table, it hits "Audit Logout" and never
continues on with the batch input

"ATJaguarX" <mrjboner@yahoo.com> wrote in message
news:%233AKy1s4EHA.3124@TK2MSFTNGP11.phx.gbl...
> The SQL Service is running under the Domain Admin account which has access
> to both directories and files.
>
> I cannot run enterprise manager on our server because it is not installed.
> Can enterprise manager be installed on the server?
>
> "Darren Green" <darren.green@reply-to-newsgroup-sqldts.com> wrote in
> message news:N4NA4OBi42vBFwks@sqldts.com...
>> In message <uIbX$pi4EHA.3420@TK2MSFTNGP10.phx.gbl>, ATJaguarX
>> <mrjboner@yahoo.com> writes
>>>I have a stored procedure create for the sole purpose of launching a DTS
>>>package. I can post the SQL later if necessary.
>>>
>>>The problem is that I have multiple DTS packages that I would like to
>>>execute in a SP. The sole purpose for this is that we have data stored
>>>and
>>>being used in a foxpro database and I am writing Reports and would like
>>>to
>>>have the data in SQL Server. So I created a SP that will accept a DTS
>>>package name as a parameter, executes the package and continues on.
>>>Well...
>>>my DTS package consists of a table drop, a table creation, a connection
>>>to
>>>Foxpro, a query to retrieve the data and a connection to SQL Server.
>>>
>>>If I run the DTS package directly from Enterprise manager, it executes
>>>correctly. It drops the table (if it exists), creates it, queries the
>>>data
>>>and drops it into the table. Pretty simple considering it was all done
>>>from
>>>the "Import Wizard". The problem arises when I use my stored procedure
>>>to
>>>execute the DTS package. It gets as far as dropping and recreating the
>>>table, but never retrieves the data or populates the table. The stored
>>>procedure does not error out or give me any signs of problems. In
>>>actuality, it works perfectly fine for a VERY similiar DTS package for a
>>>different table (which was also made from the "Import Wizard"). Both
>>>packages do the same thing (drop, create, import) except they hit
>>>different
>>>tables.
>>>
>>>Does anyone know why this would happen? Any help would be GREATLY
>>>appreciated.
>>>
>>
>> I would not expect the store procedure to fail if there was an error
>> during package execution, instead you need to check the log or the step
>> error information post execution to find this out.
>>
>> I suspect the error is around security and location contexts. When
>> running via the sp the package is executed under the security context of
>> the SQL Server service account. So what is this account and does it have
>> access to the Foxpro file? The package is also being executed on the SQL
>> Server machine itself.
>>
>> The typical development scenario means, that when you "test" through
>> Enterprise Manager it executes the package under the context of you, as
>> the logged on user, and on your local workstation, not the server.
>> So taking that into account it is not normally a very valid test. To do a
>> more realistic test you need to log into the server console as the SQL
>> Server service account and use Enterprise Manager there.
>>
>>
>> --
>> Darren Green (SQL Server MVP)
>> DTS - http://www.sqldts.com
>>
>> PASS - the definitive, global community for SQL Server professionals
>> http://www.sqlpass.org
>>
>
>

Relevant Pages

  • SIMPLE and wierd SQL Server 2000 login problem
    ... I have setup the following server: ... The SQL Server service ... Added a new local windows group called "ServerA\SQLAdminGroup" ... Run EXEC sp_grantlogin 'ServerA\SQLAdminGroup' [in SQL under SA ...
    (microsoft.public.sqlserver.setup)
  • MailMerge API Trouble
    ... word doc set up using the mailmerge wizard that creates an SQL ... Server .odc file to use as a datasource. ... there is an exec sp_cursoropen ...
    (microsoft.public.word.vba.general)
  • Re: Error in Distribution Clean Up Agent
    ... exec master.dbo.sp_configure 'xp_cmdshell' ... Server 1 SQL 2000 Distributor, ... Miscellenaous agent...except the Distribution Clean Up Agent ...
    (microsoft.public.sqlserver.replication)
  • Re: Cluster setup failing
    ... Connecting to Server ... ... EXEC sp_grantlogin N'BUILTIN\Administrators' ... SQL Server has been successfully configured! ...
    (microsoft.public.sqlserver.setup)
  • Re: Obtain Info
    ... How to Enable the Audit for Logins:- ... SQL 2000 - Open enterprise manager -- Right click on server and select ... you need to use Profiler for that. ...
    (microsoft.public.sqlserver.security)