| Author |
Topic |
|
william_lee78
Yak Posting Veteran
53 Posts |
 Posted - 2004-09-27 : 21:27:43
|
| Hi,Can someone help me on this issue? I create a DTS package to archive some of the data in the database. The DTS package will transfer my data into MS Access .mdb file to a remote share folder. When I run the package without previously login to the remote share folder, I got the following error message:The Microsoft Jet database engine cannot open the file "\\PC_Name\Share_Folder\Export_20040927.mdb"It is already opened exclusively by another user or you need permission to view its data.If I login to the remote share folder, I can successfully run the Package without any error. May I know is there any way for me to specify the login ID and password for the share folder in the DTS package? I plan to automate the DTS package by using schedule. Therefore, I don't intend to login the share folder manually everytime I need to execute the DTS package.Pls provide me with some helps.....Thank you! |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-28 : 00:36:11
|
| you can create a job to logon to the shared folder then call the dts package if logging is successfull. |
 |
|
|
william_lee78
Yak Posting Veteran
53 Posts |
Posted - 2004-09-28 : 05:46:07
|
| Hi Jen,Thank you for your information. Can you further explain on your solution because I'm new to DTS?Thank you. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-01 : 03:48:58
|
| create a jobstep 1 (cmdexec): establish connection by issuing,net use \\PC_Name\Share_Folder /user:username password--if there's no error then goto step2step 2: calling your dts package (search for dtsrun) |
|
|
|
william_lee78
Yak Posting Veteran
53 Posts |
Posted - 2004-10-01 : 05:00:42
|
| Hi Jen,Thank you for the information. Is the job refer to Window schedule job or database schedule job?I'm still uncertain about the solution. Sorry about this. Can you pls further explain it.Thank you very much. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-01 : 11:49:46
|
it's a sql job. if you're not familiar, search this forum, i think i posted something similar to your concern...no problem, i know how it feels to want to do something but unsure of what to do... |
|
|
|
william_lee78
Yak Posting Veteran
53 Posts |
Posted - 2004-10-07 : 03:11:08
|
Hi Jen,I tried to create a job under SQL Server Agent and add a new Step. I enter the following code under the Command field of Step tab.net use \\PC_Name\Share_Folder /user:username passwordWhen I press on the Parse button. I got the following error:The following syntax error(s) were detected in the Command:Error 170: Line 1 Incorrect syntax near "\".I can't get the thing work? How should I do it? Basically, I want to follow the suggestion you provided to login to remote share folder. But, once again I failed to do so. Can you help me again? Thank you so much. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-07 : 04:30:03
|
you should not be able to parse it cause on the job step, type should be operating system command (cmdexec)  |
|
|
|
william_lee78
Yak Posting Veteran
53 Posts |
Posted - 2004-10-07 : 05:14:27
|
| Hi Jen,Thank you for your guidance. I managed to create the Job on my SQL Server. But, my SQL server still failed to login into the remote share folder. I got a failed message from Windows Application log stating that the job fail.Basically, I enter the following command on the Command field of the Step:net use \\William\Temp /user: William abc123Is it correct? Furthermore, I try this command from the Window Command Prompt (cmd), I can succesfully run the command. Do you know what wrong with my Job setting? Pls help me. Thank you |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-07 : 05:28:20
|
| explore why the job failed, right click on the job and view job history,or try this, run it in your server's query analyzer, then post the error if you encountered one:BEGIN TRANSACTION DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance' -- Delete the job with the same name (if it exists) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'connect') IF (@JobID IS NOT NULL) BEGIN -- Check if the job is a multi-server job IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN -- There is, so abort the script RAISERROR (N'Unable to import job ''connect'' since there is already a multi-server job with this name.', 16, 1) GOTO QuitWithRollback END ELSE -- Delete the [local] job EXECUTE msdb.dbo.sp_delete_job @job_name = N'connect' SELECT @JobID = NULL END BEGIN -- Add the job EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'connect', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job steps EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'connect', @command = N'net use \\William\Temp /user:William abc123', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job schedules EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Start Automatic', @enabled = 1, @freq_type = 64 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the Target Servers EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback ENDCOMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: |
|
|
|
william_lee78
Yak Posting Veteran
53 Posts |
Posted - 2004-10-07 : 06:05:33
|
| Hi Jen,Thank you so much for the fast respond. Basically, I got the following error message from the Job history:Executed as user: SINGDEV05\SYSTEM. The syntax of this command is: NET USE [devicename | *] [\\computername\sharename[\volume] [password | *]] [/USER:[domainname\]username] [/USER:[dotted domain name\]username] [/USER:[username@dotted domain name] [[/DELETE] | [/PERSISTENT:{YES | NO}]] NET USE {devicename | *} [password | *] /HOME NET USE [/PERSISTENT:{YES | NO}]. Process Exit Code 1. The step failed.Any idea from this error message? |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-07 : 10:04:01
|
quote:
Originally posted by william_lee78
Hi Jen,Thank you so much for the fast respond. Basically, I got the following error message from the Job history:Executed as user: SINGDEV05\SYSTEM. The syntax of this command is: NET USE [devicename | *] [\\computername\sharename[\volume] [password | *]] [/USER:[domainname\]username] [/USER:[dotted domain name\]username] [/USER:[username@dotted domain name] [[/DELETE] | [/PERSISTENT:{YES | NO}]] NET USE {devicename | *} [password | *] /HOME NET USE [/PERSISTENT:{YES | NO}]. Process Exit Code 1. The step failed.Any idea from this error message?
specify the username with domain? /user:domain\username password, then what's the user SINGDEV05\SYSTEM? is this a login?, just change the owner of the job to sa. |
|
|
|
william_lee78
Yak Posting Veteran
53 Posts |
Posted - 2004-10-07 : 23:33:50
|
| Hi Jen,Thank you for your help. The SINGDEV05\SYSTEM is the SQLSERVERAGENT logon account. After so many help from you, I finally get the thing work. Basically, it is the problem of my SQLSERVERAGENT logon account. I change the logon account from Local System Account option to This Account option, where I specify the username and password with admin right. Thank you so much for the help. |
|
|
|
|
Login Share Folder