June 7, 2007 at 5:22 am
Hello,
I have a package with two connection: SQL Server - destination and file - source. The file is on another server in the folder c:\Program files\Logs\Log.txt. I'm running this package via sp that is scheduled in a job "jobImport".
The owner of the job is "sa" and the "Run As User" is set to the user that is an administrator on the second server - "UserSMS".
In the sp I'm changing the connection to the file (because every day I have a file that contains the date in the name). The main code of the procedure is:
CREATE procedure test
as
DECLARE
...
...
...
set @path = '\\Server2\c$\Program Files\Foder No. 1\Logs\Attempts File ' + convert(varchar(10), @FileDate, 120) + '.csv'
...
...
...
--create a package object
EXEC sp_OACreate 'DTS.Package', @object OUTPUT
EXEC sp_OAMethod @object, 'LoadFromSQLServer("Server1", "","",256,,,, "TESTP")', null
EXEC @hr = sp_OASetProperty @object, 'Connections.Item("file").DataSource', @path
EXEC @hr = sp_OAMethod @object, 'Execute'
...
...
...
GO
When I'm executing this job I don't have any error (Result in job history is "Successful") but data are not imported in the table.
Please help me,
Thank you,
June 7, 2007 at 5:31 am
SQL server fails to access resources in network with local users. Instead use a domain id to access the network resource and check if that succeds.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 8, 2007 at 1:23 am
I tried but it doesn't work. The situation is the same, the job is finishing with status successful but no data are imported.
Any other suggestions?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply