I'm hoping that someone can help me. I have a SQL 2005 SSIS package that will run Friday mornings to empty/load a table with data from another database. On Friday evenings I'll need to run another package, but want to make sure the table load completed prior to launch. For this I planned to use a file watcher task, however I cannot for the life of me figure out how to output a 'done' semaphore, from the morning job, to a networked drive.
A file system task will not work because there is not a 'create file' option. I do not have an existing file that I can rename either.
I tried an execute process task running cmd.exe with the following argument:
This fails because UNC paths are not recognized. (The package executes from another server so I cannot use a local path, nor am I allowed to set-up a local share.)
Can someone offer an alternative suggestion? I'm really hoping this is easier than I'm making it.
I am able to run SSIS packages as SQL Server Agent jobs with a Control Flow items "File system task", if I move a file (test.txt) from a drive (c on the server (where SQL Agent jobs run) to a subdirectory on the same drive. But, if I try to move a file on a network drive, the package fail.
I know , it is not going to work , just wondering if anyone could give any reasons for that. Whether it was intentional constraint or just internally compact edition was designed in particular way which makes such a usage not possible. It is a pity that it doesn't work that way as it would be much easier transitional path for many Visual Foxpro , MS Access applications. In my case I just want READ-ONLY database either for multi-user access via shared drive or stand-alone on local drive.
I am having an Access database on a shared network drive which has read/write access rights on the that shared network drive. When I try to Access data through the linked server it gives me gives me a message box saying you do not have permissions to view the data. Also if i try to use xp_cmdshell to copy over the mdb file to my local drive it say 'Access denied'
But when I copy (through command prompt) the same file to another network drive or my local drive where I have full control the linked server can connect sucessfully.
The problem is the i cannot have 'full control' permissions on shared drive where my database resides.
I recently created a program that connects to a Microsoft SQL database that was stored on my computer and it worked fine. As soon as I tried to connect to the same database via a network drive I got an error stating that "The file Y:Filename.mdf is on a network path that is not supported for database files.". I can't seem to get it to work, if anybody has any ideas what I'm doing wrong I would appreciate your help.
I have an SSIS package which accesses a map UNC path drive. It works fine when I run SSIS from Visual Studio. However, when I run this from a SQL Server job, it gives an error.
I am not allowed to use xp_cmdshell due to security reasons. If someone can point me to right direction, that would be greatly appricated.
Hi,When i use to work on Windows 2000 advance server and Sql 2000I was able to do network restoration by using a mapped drive where mybackup use to be lying.What i did was I assigned administrator rightsto my login and in Services (mssqlserver)i added my login.Then I was able to access any mapped drive from my network in Sqlserver 2000.Now i have switched my OS to Windows Server 2003.What I want to know is , is their any other setting that has to donefor network restoration , because after following above steps then to Iam not able to access mapped drive in SQL SERVER 2000.So can anyone help me to know what setting should i do in Windows 2003server or in SQL Server 2000 to do a network restoration of database.Thanks in advanceTV
Hi I have a simple ftp task that downloads some files from an ftp to a local directory. That package run as a sheduled job from the sql 2005 server without a problem so far.
Now I whant to have the same task but to download the files to different mapped network drives , so I mapped the drives then I changed the paths to the config file but since then the job fails...
is it possible to do that or I did something wrong?
Hello, I need help with the setup of my sql server express 2005 and more specifically, with the location of the database files. Normally, database files are located on the c: drive. In my situation, being in a high school environnement, I need to have the database files on the network drive ... lets call it g:drive. The reason why it cannot be on c:drive is because this drive is protected using DeepFreeze. Now, I was able to change the location of the new drive as I went into the properties window of the sql server instance but when I try to create this new database, sql gives me grief ;-| I am from the group of people that thinks that to anything, there is a solution and am hoping that to this problem, there is hope of a solution. Can anyone help? Thank you
I am using SQL2K server. I have a scheduled job which invokes a DTS package. One of the tasks in the DTS package invokes an OS command (from a .bat file) to map a network drive to b made available (dynamically, referring to a mapped drive letter, say "Y" or "Z") in successive tasks to refer to a source or destination TEXT file(s). Basically, the DTS package must be able to refer to the TEXT files (source/destination, depending on the task I have) located somewhere in the network. [ I use the NET USE command in the .bat file to map the network drive.)
The DTS package works stand-alone from SQL Enterprise Manager. But, when the package is invoked from a scheduled job, the step containing the DTS package fails - the task which maps the network drive fails - access violation /session error... How do I handle the batch job to overcome this problem? I appreciate tips/solutions from anyone.... Thanks. K.K.Rengan
I have creted a backup device on network drive and getting the following error when I run the backup command as a job.SQL server and the agent are running on a domain account which has full permission on the network drive.Any help is greatly appreciated. Thanks!
"Cannot open backup device 'mccdiffbkpond2build'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed"
I am setting up a backup on one of my DB's and the network drive does not appear as an location that I can backup too. Only the C drive is present. However, on the server I do have a drive mapped to the network drive, so it should be available. Has anyone encountered this issue before?
HI i am trying to take a backup on Network drive but i am getting following error message. althought when i try to type the address in window explorer. i can access the specific folder. so that there shouldn't be network security issue.... and even thought the backup device name is correct. when i try to take backup on local disk, it work's fine....
Executed as user: NT AUTHORITYSYSTEM. Cannot open backup device 'FullBackup(\0.0.0.0SQL_BackupsFullBackupsFullBackup.bak)'. Operating system error 5(error not found). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
In 6.5 , I was NOT able to use mapped network drive for new database device if the MSSQLSERVER service is running under local system account, but if I changed it to using a domain a/c which has the authority to create files on mapped drive, I was able to do it.
But if I tried it in 7.0, it never work and got msg: Server: Msg 5105, Level 16, State 2, Line 1 Device activation error. The physical file name 'e:est2.mdf' may be incorrect. Server: Msg 1802, Level 16, State 1, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.
Is it something that's normal for 6.5 and 7.0 ???? i.e. we can only create databases on LOCAL drives ? I am just thinking of in case we run of space locally, we can temporarily make use of remote mapped network drive. (say at time of upgrading)
We have a package that is using a ForEach loop container to access files on a network drive. For some reason I am getting a message that the ForEach enumerator is empty and did not find any files that matched the pattern. For the pattern I left the default *.* for testing purposes. I have specified the file folder as \remoteserverfilesharesubfolder and also as \remoteserverc$filesharesubfolder and have gotten the same message. However when I map a network drive and set the file folder to the network drive it finds the files. Is this a permissions issue?
After I finish processing the file I want to move it to a new directory. Once this is deployed in production, the package will not be running under a domain account and probably won't have access to the network folder. Is there any way to specifiy in the connection manager itself that it should use a specific account to access the folder?
I have a network drive that is mapped as Z: on my local machine. When creating a new database, that drive does not show up in the list of available paths. If I try to hardcode the drive/path info, I get an error message: "The system cannot find the path specified."
I am using SQL Server 2005 Express Edition. I am planning on upgrading to either Workgroup or Standard edition. While working with Express Edition I did find out that this version does not support backup to a network drive. My question is:
Is it possible to backup directly to a network drive using a Workgroup/ Standard edition of SQL Server rather than backing up to a local drive and then 'copy and paste' to a network drive like the Express Edition.My other question is :
Given a database limit of 50GB, which one of the editions, Workgroup or Standard, will be appropriate for use.Thank you for your time and patience. Regards Berly Sam
Hi, I have created an sql server 2005 maintenance plan for a daily backup. The plan has two 'Back up database task' i.e. one backup on the local drive while the second on a network drive. When the plan is executed, a backup is created on the local drive but not on the network drive. If i check the log, it says "Access Denied" whereas i have full access to the network drive with complete permissions to read, write and delete. Can anyone help me understand how to take a backup on both a local and network drive at the same time using a maintenance plan ? I shall be obliged... Regards...
Hi! What is the fastest whay to take the resultset from an temporary table (# table) and write it to an remote destination (mapped network drive) initiated from stored proc?
Similar question if I take the resultset and convert it to xml (using FOR XML clause) and want it written to disk.
when my snapshot folder is on a network drive, on which "simple filesharing" is enabled, i can access that folder without typing a user and password and configuring login security settings.
but in fact the network drive on which the snapshot folder will be located will do have a login and password. (when i go to it with windows explorer, i have to enter my login and password).
won't this cause problems for the replication? as i don't see where to enter net network login when i configure the replication via the wizard.
I am trying to move a log file from one drive to another.
What I have done is add another file to my file group. So now my log has a file on the 'e' drive and one on the 'f' drive. I now want to remove the file on the 'e' drive. I have emptied the file on the 'e' drive. When doing the command:
ALTER DATABASE Uniprodruntime REMOVE FILE m_rk_runtime_log
I get the following error message..
Server: Msg 5020, Level 16, State 1, Line 1 The primary data or log file cannot be removed from a database.
I have also gone into enterprise manager and tried to delete the file and it does nothing.
I have 2 different servers that run nightly backup jobs to a network drive using the UNC format. I am noticing the larger databases encountering the following errors on a regular basis
Error: 18210, Severity: 16, State: 1. '\fnfssql3SQLBackupsMNSQL05<database>.BAK'. Operating system error 64(The specified network name is no longer available.). BACKUP failed to complete the command BACKUP DATABASE <database>. Check the backup application log for detailed messages.
There are 5 databases on this server all backed up by the same job and 3 of them wortk fine and 2 of them fail so it is not a permissions problem. Have there been any problems with backing up to a network drive in 2005?
I get the following error in a log file created in the osql command:
Msg 3201, Level 16, State 1, Server KAC2KGS2, Procedure usp_Kaman_Full_SqlDB_Backup, Line 150 Cannot open backup device 'x:Servername_master_sqlbu_200703101930.bkf'. Device error or device off-line. See the SQL Server error log for more details. Msg 3013, Level 16, State 1, Server KAC2KGS2, Procedure usp_Kaman_Full_SqlDB_Backup, Line 150 BACKUP DATABASE is terminating abnormally.
The device is established in a CMD file right before the osql command is started that starts my stored procedure. The CMD in this file is:
for /f "tokens=15 delims=." %%i in ('ipconfig^|find "IP Address"^|find "192.168"') do set SUBNET=%%i
:loop if exist x: net use x: /del net use x: \192.168.%SUBNET%.1ackup if not ERRORLEVEL 1 ( goto continue ) else ( echo FAILED TO CONNECT TO BACKUP SERVER >> "%SystemDrive%LogFiles\%Computername%.log" sleep 60 goto loop )
:continue
echo IP ADDRESS OBTAINED
Echo delete old log file if it exists
if exist %3\%computername%_kaman_full_sqldb_backup_old.log del /Q %3\%computername%_kaman_full_sqldb_backup_old.log
In the stored procedure I try to use the x: drive and that does not work. I have tried obtaining the \192.168.x.x address and that only works on some of my servers. All are running SQL 2000, some are using Win2K and Win2003. It does not seem to matter. One of them that is using Win2003 only fails occationally.
I notice when I do a
exec master..xp_cmdshell 'x:'
The system cannot find the drive specified.
Yet, when I go to the server there is an x drive.
I am using the sqlserv user to run the job and that use is an administrator on the local machine. This is a virtual machine. We add the X before the osql and drop it after the command finishes.
I created the db with the attached script and I am able to access ituntil I reboot the server. I've tried enabling flag 1807 via the SQLserver service and the startup parameters of the instance. In allcases the database always come up suspect after a reboot. There wasone instance where I was able to recover, but I am not sure how thathappened.Does anyone have an idea of how I can reboot the server without thedatabase becomming suspect?USE MASTERGODBCC TRACEON(1807)GO--DBCC TRACEOFF(1807)--DBCC TRACESTATUS(1807)GOCREATE DATABASE ReadyNAS ON( NAME = ReadyNAS_Data,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Data.mdf',SIZE = 100MB,MAXSIZE = 20GB,FILEGROWTH = 20MB)LOG ON ( NAME = ReadyNAS_Log,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Log.ldf',SIZE = 20MB,MAXSIZE = 100MB,FILEGROWTH = 10MB)
I have VC++ express and MSSQL SMS express and have an application working nicely locally. The Data explorer and data connections part works really easily.
Now, I want to make the application available to my home network.
I mapped the drive where the database is and called it Z: so I could put my "release" on my other network PC and assumed it would find Z: if I mapped the shared network drive on that machine and called it Z:
But: I can't even add the mapped connection on the local machine, I get:
The file "Z:databasescalorie.mdf" is on a network path not supported for database files. An attempt to attach.....etc"
It works fine on the original F drive.......
Am I approaching this the wrong way. How should I distribute to network PCs?
Is this possible? I seem to remember doing it with SQL Server 7 a long time ago. The Microsoft Knowledgebase says it's not possible with 4.2 through 6.5, but nothing about 7.0 and up.
