I was wondering if there is a way to copy files from a network to an ip site where you would need to log in at. What I am trying to do is make a SP that would take all .csv files from the network drive and place them in the other ip address folder however you would need to use the login information.
View 5 RepliesHi,
I am very new to SQL Server and am just starting to look into replication as a possible solution to a problem I have. I have a local database which will be running on either MSDE or SQL Express 2005. I have a database on a webserver running SQL Server 2005 Standard which I wish to keep as an exact copy of the MSDE/Express one. If data is changed in the local db then I want the webserver db to be updated with this data, preferably as the change occurs, but it could be on an hourly schedule if required.
If data in the webserver db is altered then I do not wish for it to update the local db, but I would like it to be assumed that the change is erroneous and reset the item to whatever exists in the local db. In other words, should a table be erroneously dropped or something from the webserver db then the table would be recreated when it syncs with the local db (rather than propogating the change back to the local db and thus losing local data).
Basically I want the web db to be an exact mirror of the local db as often as possible. Given that the webserver is running SQL Server 2005 Standard and the local machine will be running either MSDE or SQL EXPRESS, is this achievable and what would be the best way of achieving it? Should I use merge replication or database mirroring? I have succeeded in getting 1-directional merge replication working by setting the webserver up as a publisher and distributor and setting up a push merge subscription on the local machine with an ExchangeType of 1. However, this won't correct the data in the web db if it is compromised until it is altered locally.
It crossed my mind that I could write a small application to utilise tablediff to compare each table in the DBs and get it to correct the web db if required on a regular basis. Would this be a silly approach?
I would appreciate expert advice!
Max
Hello:
I have setup a Flat File connection manager and set the location of the file as such:
ftp://10.1.1.1/FileName.txt
When I enter this URL into Start | Run on my machine it opens the txt file fine. Working in BIDS, when I create the flat file connection manager, it doesn't give me a message saying this file does not exist, indicating it is correct. When I create a flat file Source that uses the connection manager, the preview comes up blank. When I try to execute the step it gives an error saying it "cannot open datafile ftp://10.1.1.1/FileName.txt"
Any idea what could be going on?
Thanks,
Kayda
Hi!
I did:
alter database mydb set single_user with rollback immediate;
exec sp_detach_db @dbname='mydb', @keepfulltextindexfile='true';
then I tried to copy files to new location on other drives, same server but got
>>Cannot copy <myfile>: Access is denied
Make sure the disk is not full or write-protected and that the file is not currently in use<<
I also tried rename of file without success.
I also tried with db service stoppet (not preferred) without success.
How to find out, which process locks the files?
Best regards
Hi Experts,
I am looking for sql code/approach to export a text file and 2 excel works sheets(from an excel workbook) in a folder, to 3 different tables in sql server, perform data scrubbing and then export the data from all 3 tables to 3 different excel sheets and post them in 3 different web parts in a share point site.
Mainly sql code(or any code that i can integrate with SQL Server) to post files to different web parts in a share point site is what i am looking for.
Any sort of pointers/suggestions would be really helpful.
Thanks & Regards,
I am looking for sql code/approach to export a text file and 2 excel works sheets(from an excel workbook) in a folder, to 3 different tables in sql server, perform data scrubbing and then export the data from all 3 tables to 3 different excel sheets and post them in 3 different web parts in a share point site.
Mainly sql code(or any code that i can integrate with SQL Server) to post files to different web parts in a share point site is what i am looking for.
Any sort of pointers/suggestions would be really helpful.
Thanks & Regards,
I need to only receive files via ftp when the file does not exist on my local machine. FIles are being added to the remote location on a weekly basis and they are being downloaded locally. I do not want to download all the files each time. Instead, just download what was not already downloaded. Is there a way to do this? I want to do this using SSIS / ftp task.
Thanks in advance.
I work with databases and some of my backup files are 30-100 gigs insize. I currently use the FastCopy utility to copy files from oneserver to another. (This is needed when I need to transfer a DB to adifferent server). FastCopy says its 30 to 50% faster than Windowsfile copy. I read somewhere that the windows kernel defaults to4mb/sec throughput for copying.Is this true? Is there a faster way or some utility to copy files?I'm aware of RoboCopy but the documentation didn't mention anythingabout faster performance. All my servers are windows 2000 NTFS.Thanks
View 3 Replies View RelatedHi,
We have SAN for our SQL server and all of DB backup copy pointing to one the SAN volume(ex. T). We are moving the bkp copy from this SAN volume into remoteserver for restoring the backup. We are getting a lot of Time out during this time.
Is that copy process affect production time out?.
Thanks,
Any specific risks in placing database files on network shares on a windows server (2008 R2). I have read its a bad design, but unsure why, or what problems such a setup may cause?
View 4 Replies View RelatedWe 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?
TIA,
Sabrina
I have two, probably related, problems:I have a very simple site. It will be used on the local intranet only. I want to use the windows authentication so users do not have to log on. The aspnetdb has been created using the configuration tool. If I use Server Explorer in vs to work on the site on my local machine, I can open aspnetdb, look at its tables, etc. If I use Server Explorer in vs to work on the site on the server, aspnetdb will not open and gives this error message: ..."network path that is not supported for database files"...Other sites on the same server give the same message, but the sites work. I think this is because they can read the file but not write it; but that is a guess. We have tried everything; my head is ready to explode. We are using SQLServerExpress. It resides on the server's C drive (as does our data database). The web site, and hence the aspnetdb are on the F: drive of the same machine. Does SQLServerExpress treat the F: drive as a UNC drive? If so, are we in deep doo doo? I'm at a loss.As far as logging in: If I debug from vs, the login works beautifully both on my local machine and running from the server using vs. Login does not work when accessing the site from iexplorer. Mystified by that one, too. I tried to implement profiles and they crash, too; that led me to consider a write problem.Don't know what to do; any help is appreciated.
View 1 Replies View RelatedHello,
Completely new to this stuff, so sorry if I'm asking
something that is painfully simple to resolve!!! Also, not sure whether
there are standard formats for code, errors etc.
OK. When I create a Login.aspx file in VWD Express on my local Hard Disk Drive, the file works and runs correctly, allowing
me to log in etc.
However, when I do exactly the same thing using a remote Network Drive connection to an Intranet server, when you enter the
Username and Password, and Click the Log In Button, the following error message appears:-
The file "[Network Drive]AuthorsApp_Dataaspnetdb.mdf" is on a network path that is not supported for database files.An
attempt to attach an auto-named database for file
S:AuthorsApp_Dataaspnetdb.mdf failed. A database with the same name
exists, or specified file cannot be opened, or it is located on UNC
share.
Then I get the following information - which I'm sure is supposed to help, but doesn't!!!
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException:
The file "S:AuthorsApp_Dataaspnetdb.mdf" is on a network path that
is not supported for database files.An attempt to attach an
auto-named database for file S:AuthorsApp_Dataaspnetdb.mdf failed. A
database with the same name exists, or specified file cannot be opened,
or it is located on UNC share.
Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): The file "S:AuthorsApp_Dataaspnetdb.mdf" is on a network path that is not supported for database files.An attempt to attach an auto-named database for file S:AuthorsApp_Dataaspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +115 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +56 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +1083 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +272 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +687 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +82 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +558 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +126 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +651 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +160 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +122 System.Data.SqlClient.SqlConnection.Open() +229 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +114 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +225 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +157 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +68 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +100 System.Web.UI.WebControls.Login.AuthenticateUsingMembershipProvider(AuthenticateEventArgs e) +100 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +113 System.Web.UI.WebControls.Login.AttemptLogin() +178 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +134 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +107 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +178 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +72 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3838
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
All assistance greatly appreciated!!!
Hello,
I am experiencing an issue with SQL Server 2000 Maintenance Plan.
DB Backup job fails to delete old backup files from the file server (I am backing up to the network share - actually, a DFS).
Backup part of the maintenance plan/job succeeds, but then cleanup part fails.
I made sure that service account under which SQL Server Agent is running, has sufficient privileges over the network share by logging in and successfully deleting files in question.
I was not able to locate any log entries either on the SQL Server machine or on the file server machine that would indicate the root of the problem. Even though I turned on auditing for Delete operations for the destination folder, its subfolders and files, I could not find anything in the Security event log.
I would appreciate any ideas on how to troubleshoot and correct this problem.
Thanks in advance,
I am trying to attach to aMDF and LDF file that are on the remote location. When you try to do this though getting an error..
"File filename.mdf is on a network device not supported for database files."
How to do this.
Could you please help me to sort out this problem.
Thanks in advance
WillSmith
mail2willsmith@yahoo.com
We have some to-disk backups scheduled on our 2000 Enterprise machine - nightly fulls, hourly logs - that go to a network share located on another machine. They were originally stored directly on the same machine as SQL Server, but we changed them to a remote destination within the past few weeks. This works okay, but despite having the maintenance plan set to remove files older than 2 days, old files don't seem to be removed. Understandably, this gets to be a problem when the backup disk becomes filled.
Is there any obvious reason why this option wouldn't work against a network share? I've checked the directory permissions, and the SQL Agent domain account should have no trouble deleting the files.
I copied a database using backup and restore from Sql server 2000 to Sql server 2005. I noticed the data(mdf) and log(ldf) files did not copy. when I try to do a copy and past it gives me an error: cannot copy lof: it is being used by another person or program. Close all programs and try again. Does this mean I have to stop both servers to copy between them? Please help.
View 1 Replies View RelatedHello, everyone:
I want setup a scheduled job to copy txt files from FTP to local hard drive. How to open FTP and copy files from SQL Server Query Analyzer?
Thanks
ZYT
I have been looking for a way to copy only new flat files from an FTP server. I can find how to copy all of the files into another directory, but I only want to copy in files that are not already in my target directory.
So, is there a way to compare the files in the FTP directory to a list of files in a SQL table and use this to control the transfer, and how do I get those FTP file names into a SQL table? Or is there some other way?
Thanks in advance for any help.
Graham
Howdy, hope someone can help me out with this.I want to run a job each night that copies files from one server to another.I cant even get a simple copy one file from one directory to another,statement to work.When I try :@cmd 'copy c: empfile1.txt c:ackupsfile1.txt'master.dbo.xp_cmdshell @cmdI get the msg: 'c' is not recognised as an internal command, program orbatch file, Null.What I ideally want to do is pass xp_cmdshell variables for the 2 file'spath+names. I have tried numerous variations of single and double quoteswithout success, which is why I decided to work up form the simple 'copyc: empfile1.txt c:ackupsfile1.txt', but can't even get that to work.Any advice much appreciated.
View 3 Replies View RelatedHi,I'm having some trouble finding any information on this topic - can MS SQLserver 2000 copy/delete files on the host computers disk.Any info on this would be great.Thanks,Eirik
View 2 Replies View Relatedhi , I am trying to copy files dynamically from a remot folder by using File System task ,
the files are going to be generated daily with f1yyyy-mm-dd.txt
I created 3 file connections :
- Existing remote folder (RemotFolder) and set that to var v_remotefolder
- Existing destination folder (DestFolder)
- existing file (filename) and set the expression to var (string ) =v_fname
in the File System task I set the the
Destination connection = DestFolder
Operation =Copy file
Source = filename
expression >>Property (source) = @[User::v_remotefolder] + @[User::v_fname]
I know I need the date too ,but I 'm getting error on this step.
Can you please tell me if you know how to resolve this without using For each loop since I don't have control on the remote folder and I don't want to copy unneeded files (the files are 10 GB daily) and get delete it once every month.
-I know this not uncommon task but I can't find any example.
Thanks
I copied a database using backup and restore from Sql server 2000 to Sql server 2005. I noticed the data(mdf) and log(ldf) files did not copy. when I try to do a copy and past it gives me an error: cannot copy lof: it is being used by another person or program. Close all programs and try again. Does this mean I have to stop both servers to copy between them? Please help.
View 5 Replies View RelatedI have a simple SSIS package that reads a flat file and copies it into a SQL Server table.
When the flat fiel is on the C drive I have no problem runnign this package from SQL Server Agent, but as soon as I update the path to a network location the package only works when I run it manually, but fails when is executed via the SQL Server agent job.
The error says "cannot open the datafile", while the datafile location is valid.
Is this a kind of limitation of a SQL Server Agent that only local files are allowed to be processed?
I was trying to copy backup files from production domain to test domain nightly using xp_cmdshell. I
View 3 Replies View RelatedI connect to remote servers using PPTP connection. There are 4 web servers and 1 production server on the other side of the firewall. From my staging server, I am supposed to archive the NT event logs(Application and system) for each of the servers.
To solve this problem, I have mapped the C drives of each of the remote servers to my local staging server. I then created a batch file, webevnt.bat containing code,
G:
Cd winnt
Cd system32
Cd config
Copy sysevent.evt c:eventarcweb01sysevent.evt
Copy appevent.evt c:eventarcweb01appevent.evt
Then I used SQL Agent to create a job of type, Operating system command(cmdexec) and typed the following command :
C:atch_~1eventl~1webevnt.bat
When I run the command, C:atch_~1eventl~1webevnt.bat through the command prompt, the files are copied with the correct modified date.
The output is,
1 file(s) copied
1 file(s) copied
But, when I use SQL Agent and start the job containing the same command, the job runs successfully but, the files have a wrong modified date. Moreover, when I view the job history for this job, it says, specified drive not found. 1 file(s) copied. 1 file(s) copied.
Please let me know why this happens.
Thank you.
Praveena
hi.
how can I copy any mdf file to another folder without stopping sql server?
Hi,
I'm fairly new to SQL, waiting on a course on how to use it, but wanting to get stuck it.
I was wondering how I can get SQL to copy a file, say in Access, from one location to another.
This would be part of my job with delivering data extracts users.
Many thanks in advance
I have a situation from where I need to loop through different folders and files in these folders. After processing these files, I need to archive these folders to different location.
e.g., C:MainFolderMar01 ==> Multiple files in Mar01 folder
C:MainFolderMar02 ==> Multiple files in Mar02 folder
Does any one know the best way to do this in SSIS?
Thanks in advance.
BC
Hi,
How do I copy files from several subdirectories into the same directory? I have about 80 subdirectories of a parent directory from which I want to copy the files and place the copies into the same, flat destination directory. I tried using the copy file and copy directory file system tasks, but this always seems to recreate the subdirectory structure under the destination directory.
How can I get around this in SSIS?
Thanks in advance,
Bruce.
In SSIS, I was to:
take files from c:directory1
copy them to cirectory2 with a different name (concatenated date on the end)
delete them from c:directory1
Should I be using the Script Task for this? I am wondering if I should be using the File System Task which copies directories or files, but I don't know if I could rename the files during the copy.
Also, I want the directory names to be in the configuration file. I am not sure how to do this. If I set up a flat file source connection, I need to specify a file name and I just want the directory names. How do I get them in the config file and how to I read them into my script from the config file?
Linda
This is a question of whether or not to use SSIS to solve a problem.
I need to copy SQL Server database backup files from a server in the DMZ to a fileserver inside the firewall. The SQL Server is not allowed to write it's backup files directly to the fileserver, so they are written to local disk. A connection can be made from inside the firewall to the SQL Server to copy the files off.
So, I'm considering SSIS for the job. Is it possible to use SSIS to perform the file copies from one remote server to another? If so, is the FTP task required, or can File System tasks be used?
An alternative would be Windows scripting, xcopy, robocopy, etc. but I like the features of SSIS and would like to take advantage of it's flow control, error handling, database scheduling, etc.
Any tips, sample code, etc. would be appreciated.
Thanks,
david
I am looking to implement a system whereby a given user can 'authorise' changes to the system itself and promote from test to live.
For instance a user might be running an application which has a SQL 2005 database DataA opena nd runnign stored procedures from that. I'd like a Stored procedure in that able to copy over a given stored procedure from a different database - DataB and copy into DataA. Is that possible?
Also is it possible from T-SQL to copy files on the servers underlying filesystem? Ie to copy some .aspx files from one location to another.
Regards
Clive