I have a windows service that connects to a regular sql server 2005 database and basically bulk copies data into a SQL express database. Afterwards, I just want to copy the MDF file into a different directory. I keep getting the "Process cannot access file because it is being used by another process" error. I've tried changing the connection strings but nothing seems to work. I'm closing the connections in the code as well. Here is the code. Any thoughts or help would be appreciated. RefreshDB calls Sync 3 times and if all three calls are successful, it attempts to copy the database file to the specified location. This is where I get the error.
Public Function RefreshDB() As Boolean
Dim success As Boolean = True
Dim tables() As String = {"Job", "Equipment", "PMScheduled"}
For Each tableName As String In tables
If SyncTable(tableName) = False Then
success = False
Exit For
End If
Next
If success Then
'copy the new database to the target directory
Try
File.Copy(My.Settings.DBFilePath, My.Settings.TargetDirectory + "EMField.mdf")
Catch ex As Exception
My.Application.Log.WriteEntry(ex.Message + "(RefreshDB)", TraceEventType.Critical)
Return False
End Try
End If
Return success
End Function
Private Function SyncTable(ByVal tableName As String) As Boolean
Dim reader As SqlDataReader
Dim sourceViewName As String = String.Format("EMField{0}View", tableName)
Dim connectionString As String = My.Settings.EMFieldConnectionString
'insert the path to the database into the connectionstring
connectionString = connectionString.Replace("[DBFilePath]", My.Settings.DBFilePath)
Try
'clear the target table first
Using targetConnection As New SqlConnection(connectionString)
Using truncateCommand As New SqlCommand(String.Format("TRUNCATE TABLE {0}", tableName), targetConnection)
targetConnection.Open()
truncateCommand.ExecuteNonQuery()
targetConnection.Close()
End Using
End Using
'create a datareader from the source database
Using sourceConnection As New SqlConnection(My.Settings.EMLiteConnectionString)
Using readCommand As New SqlCommand(String.Format("SELECT * FROM {0}", sourceViewName), sourceConnection)
sourceConnection.Open()
reader = readCommand.ExecuteReader
BulkCopy(tableName, reader, connectionString)
reader.Close()
sourceConnection.Close()
End Using
End Using
Return True
Catch ex As SqlException
My.Application.Log.WriteEntry(ex.Message + "(sync)", TraceEventType.Critical)
Return False
End Try
End Function
Public Sub BulkCopy(ByVal tableName As String, ByRef reader As SqlDataReader, ByVal connectionString As String)
Try
'bulk copy from source to target database
Using bulkCopy As New SqlBulkCopy(connectionString)
bulkCopy.DestinationTableName = tableName
bulkCopy.WriteToServer(reader)
bulkCopy.Close()
End Using
Catch ex As SqlException
'throw exception back to calling sub
Throw ex
End Try
End Sub
Can anyone give me info on how the report processing page works in reporting services.
My application makes some pretty heavy queries to the database and I would like to have a message appear on the page whilst the request is being processed.
Something identical to the way reporting services deals with this would be absolutely perfect!
Here's a really annoying problem. Let's say you have a text file with 2 million rows.Delimiters all look good and rows are previewed well but the file has a missing row at say lin 1234567 - way deep in the file. When SSIS encounters the blank row, an error is raised and processing on the file STOPS! I verified this in by checking the SSIS log and have even developed an error routine to notify me via email when the error occurs (really cool if I do say so myself ). The main problem still remains - how to resume processing from the point of failure in the file? Any help is appreciated. Thanks.
if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.
I need to extract a particular file from our AS 400 system on a daily basis and do some processing on it. Also I want to do the daily processing only on those records that have been added/updated since the initial load. Here is the approach and possible implementation.
Approach I have the DB2 source data containing 10 columns (Col1 to Col5 together form the key) and the rest are attributes. I am interested only in the key and two attributes. So I load my table with only Col1 to Col7 ( 5 for key and the two attributes). I then do my processing on this table.
Here is the implementation given by a member of dbforums -
You'll then have to deal with 3 potential actions,
INSERT: New records on the file. DELETES: Records that don't exists. UPDATES: Records that are on the file, but attributes have changed.
INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5) SELECT '1','1','a','b','c' UNION ALL SELECT '1','2','d','e','f' UNION ALL SELECT '1','3','g','h','i' UNION ALL SELECT '1','4','j','k','l' --DELETED
INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5) SELECT '1','1','a','b','c' UNION ALL--NO CHANGE SELECT '1','2','x','y','z' UNION ALL-- UPDATE (My comment - Instead of an update I want to insert a new record) SELECT '1','3','g','h','i' UNION ALL--NO CHANGE SELECT '2','3','a','b','c'--INSERT GO
SELECT * FROM myTable99 SELECT * FROM myTable00 GO
--DO DELETES FIRST (My comment - Before deleting, I want to copy the rows that I am going to delete on a separate table to maintain history. Then I want to delete from a). I don't get the logic. If the rows of the old extract are not in new extract then delete them. So shouldn't it be <> instead of =. why the where clause condition) DELETE FROM a FROM myTable99 a LEFT JOIN myTable00 b ON a.Col1 = b.Col1 AND a.Col2 = b.Col2 WHERE b.Col1 IS NULL AND b.Col2 IS NULL
-- INSERT (My comment - I don't get the logic of the where. If the rows of the old extract are not in new extract then delete them. So shouldn't it be <> instead of =)
INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5) SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5 FROM myTable00 a LEFT JOIN myTable99 b ON a.Col1 = b.Col1 AND a.Col2 = b.Col2 WHERE b.Col1 IS NULL AND b.Col2 IS NULL
-- UPDATE
UPDATE a SET Col3 = b.Col3 , Col4 = b.Col4 , Col5 = b.Col5 FROM myTable99 a INNER JOIN myTable00 b ON a.Col1 = b.Col1 AND a.Col2 = b.Col2 AND ( a.Col3 <> b.Col3 OR a.Col4 <> b.Col4 OR a.Col5 <> b.Col5) GO ------------
Can anybody look at My comments and answer them or revise this code template if need be?
Brett Kaiser - I sent you an e-mail on this. Can you respond to it when time permits.
Hello, How do I import data from a text file into columns (DB_table) that I have created in SQL server 2005? Data in text file does not have any commas, semicolons, or spaces in between them. Each instance in the text file consists of 109 characters (real numbers). I also know that first 4 numbers go in particular field, next 4 go in another field, and next 6 are particular field, and so on. I also have created columns in a table respectively.
Hello, I have a DTS package that copies data from a fixed width file and inserts it into a SQL Server 2000 table. These files are generated daily, and usually contain about 200k records.
Sometimes, the format of the file between two days can differ slightly. I can't tell if the problem lies with the length of the record string, or the string terminator. The record string is supposed to begin with 'D1'. One file (as viewed in Notepad) will contain the records each in its own row, and each row will start with 'D1'. Another file will show that the first record starts with 'D1', but then the next record does not begin on a new line. Instead the next record continues at the end of the previous record. In this case, 'D1' is preceded with an unrecognized character as if it to indicate a carriage return.
Anyway, in SSIS, when I configure the row width of the fixed width file in the flat file connection manager to 386, and the beginning of all the records are all on their own row, the data processes without a problem. When the records do not all begin on their own row, the data will process fine if I change the row width to 385.
I don't have to compensate for this in DTS. Between SSIS and DTS, both flat file connections are configured with a row delimiter of LF, and no text qualifier. Why will SSIS and DTS process these files differently?
I cannot seem to get my flat file to write columns in error when inserting into a SQL table. I have tried a few examples from MS and did not get anything written to my flat file output. I have set the Source Error Output on this flat file and it uses a script task to created the error description and then write it to a Flat File Destination.
I am new to SSIS and have not had any formal training on it. However, I am very familiar with VS.Net/c# and SQL 2000 DTS - I need to get this working ASAP as there are 45 total flat files that need to be processed. Once I have this solved for one, the rest will follow suit.
Historically I've always written a VB script to copy a file from a sharepoint library. I don't like this method because I have to input a username & password in the script and maintain a config file.
Yesterday I was playing around with using a file system task. The sharepoint file has a UNC path so why not? I created a simple test package with a single file system task that copies the sharepoint file (addressed via UNC) to another network location. Package runs fine locally.
I try running on our utility server but am getting a "The file name [SHAREPOINT UNC PATH] specified in the connection was not valid" error. Package is running with a proxy on the server and the proxy account has the same permissions to the sharepoint site (so far as I can tell) as me.
I am new to Forum. So not sure if i am posting my problem uner the right topic.
We have a sql server 2005 enterprise edition 4 way cluster on windows 2003 advance server.
I am logshipping these database to a different server at a different location. My logshipping went fine until one the cluster server failed and the server instance failed over to another node. The backup that happened around that time got copied over to the secondary by the copy job. The log file that got copied to the secondary server tried restoring and i think it failed int he middle of restoring it. (You would think that the sql would knoe if the backup is in complete and will move on to the next file. Not sure what happened there.) There is no indication of the *.TUF file in the directory where i have the log files.
I tried restoring it manually and i got the following error
Msg 4319, Level 16, State 3, Line 1
A previous restore operation was interrupted and did not complete processing on file 'sessionlog1'. Either restore the backup set that was interrupted or restart the restore sequence.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I looked in the msdb..log_shipping_secondary_databases and looked for the last file that it restored and tried restoring it again with the following restore command by removing and adding some of the keywords that you see after the "WITH" clause. MSFT do not recommand to use continne_after_error unless its absolutley necessary. I stilll get the above error.
restore log sessiondata
from disk = 'I:sql13qasmlogssessiondatasessiondata_20070901124516.trn'
with restart, CONTINUE_AFTER_ERROR, norecovery
When i add the restart int he with clause,
The restart-checkpoint file 'J:Microsoft SQL ServerMSSQL.5MSSQLBackupsessiondata.CKP' was not found. The RESTORE command will continue from the beginning as if RESTART had not been specified.
Msg 4319, Level 16, State 1, Line 1
A previous restore operation was interrupted and did not complete processing on file 'sessionlog1'. Either restore the backup set that was interrupted or restart the restore sequence.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I checked it the backup directory and i can't locate the .CKP file.
Does anyone ever come accross this issue?
Is there anyother way i could recover this DB in a standby or norecovery mode.
Any kind of help to resolve this issue (beside copy the full backup and redo the whole log-shipping process again) would be appreciated. sicne my primary and secondary server are totally ina different location, i need to ship a tape, if i need a full backup. This is the 3rd time its happening on that cluster. its frustrating to ship a tape everytime this happens.
I have one task in which i have to collect lots of .txt file having ## delimiter my requirement is to convert the delimiter from ## to comma and save the new file with .dat extension in different folder.
I have done all required process and run the application which should flow like collect source .txt file do Script component processing and create new .dat file with processed data in Data Flow task, but in my Task the Source and Destination start on same time and process start after words which cause empty file or some time a.txt file data stored in b.dat file where as a.dat file is completely empty.
The process should flow in sequence but behavior is totally against the process, i am using Foreach Loop Container for pick up each file.
I need to set up create a package so that I could check the date of the files posted in a folder, e.g. H:source. If there is no file created later than one day exists, then continue to check again one hour later. If files do exists, then copy then to c:dest and then upzip the files. Once this is done, sent an notification email to user@mydomain.com.
According to the help for SSIS, one method of deploying an SSIS package to a SQL Server, http://msdn2.microsoft.com/en-us/library/ms137565.aspx, is to use the File...Save a Copy of <package file> as... menu option.
I don't have that menu option at all. And yes, the package is in focus. My save menu options are simply; Save Selected, Save <package file> As... and Save All.
I am using Version 9.00.1399.00 of the SSIS Designer.
At one time I did have the Management Studio's CTP installed. However it was uninstalled before installing the tools from the Standard Edition. (it would seem like not completely however)
Your help would be greatly appreciated. Thanx much.
p.s. Almost forgot to mention... I am already aware of using the DTSInstall utility as a workaround. It should be noted, however, that despite enabling the "CreateDeploymentUtility" property, the DTSInstall.exe is not copied to the binDeployment directory.
I need to transfer my database from one computer to another. I am using SQL SERVER 2000. So I made the script of the database and transfered to other computer. Now when I try to run the script it gives me error that (mydatabase name).MDF file missing. So I tried to copy the .MDF file from my computer. but it says that access is denied and sharing volilation. Can anyone please help me that how can I copy the .MDF file and remove the sharing volilation.
I need to copy files from one machine to another machine. It should be done automatically. Could you please give me a suggestion. I have no idea about this. It should write a script or program. But I have no idea yet,
hi i am using isp_backup store procedure to get the daily backup of database and this is work fine but i have copy the database file in network sharing folder to use following cmd EXEC master..xp_cmdshell 'D:filename M:foldernam' M: is network folder to window2003 but their no copy this folder
What is the syntax for using xp_smdshell to copy a file from 1 server to another? Our Report server is restored from our production server and I want to copy the .dat file from the production server to a folder on the report server.
But I am running this command with sa user..... Wich kind of permission is missing to execute this copy? When I execute the same command to copy the backup from the server to itself, it works fine!!!! Does someone have an idea to solve this problem?????
I'm looking to use ActiveX in a DTS to copy a file from on elocation to another. I have the code for this (attached below kindly supplied by a colleague), so that's cool.
dim filesys set filesys=CreateObject("Scripting.FileSystemObject")
If filesys.FileExists("D:CreditsCredits_MTD.mdb") Then filesys.CopyFile "D:CreditsCredits_MTD.mdb", _ "C:CreditsCredits_200602.mdb" End If
But what I'm looking to do is go one step further. As you can see, it renames the file to yyyymm, but this is hard coded, and I'm looking for a coded solution so I can rename the file with yesterday's date.
i would like to copy the SQL Server Express database .mdf and .ldf files for backup. Is this ok? Autoclose = true and recovery model = simple.
Must i detach the database before copy the 2 files or can i copy the 2 files without detach at any time? When connections are open (also remote connections). Can i copy at any time even when transactions are active?
I would like to write a copy programm which copies the 2 files every 30 minuutes. Only 30 minutes of work could be lost.
This would be enough for me and i don't have to care for the the BACKUP and RESTORE stuff. In the past i used BACKUP and when i needed this BACKUP it did not run. Returns some error message..
Is copy ok? When is it possible? At any time or must all transactions be comitted? Must all connections (remotes too) be closed? Must the database be detached?
Is this enough to have a valid backup? Backup would be an attach of the .mdf file.
Or must i use the BACKUP and RESTORE stuff? Why? If so, for what reason is the AUTO CLOSE property there?
This is the start of the procedure that creates the test_new.txt file. I want to copy the test_new.txt file and name it test_new1.txt. Any help is appreciated. thank you