July 06, 2012I'm trying to use the tool but always get the message "Incorrect syntax near the keyword from". Does anyone have any ideas about what I'm doing wrong? ... Thanks
December 06, 2011since the September version there is no "sql" row anymore for each table to copy which i can edit to remove certain columns that need no copy :(
May 20, 2011Experienced the same issue with the column ordinate positions. Column orders are alphabetical when they should not be.
September 15, 2010I have discovered an apparent glitch: From an error that I got copying the info of one specific table it seems that the copying is done assuming that the columns have the same order in both the source and destination tables, so the match is positional and not done by column name. Fortunately the column types were different, so it showed an error, but if the column types were the same than the contents of the columns would be switched on the destination, and the problem might go unnoticed.
September 15, 2010What a very fine app you have here. If you ever wonder how to make it absolutely perfect, please consider adding the option of sorting by object name and filtering by object type (i.e. only tables). Great work!
fred.sobel •May 09, 2010Rick, it will take any valid connection string. I use sql auth all the time. The format is someting like this: data source=184.108.40.206,1234;Initial Catalog=my_db;User ID=login;pwd=password; Where 1234 is the port #.
fred.sobel •May 09, 2010Rick, it will take any valid connection string. I use sql auth all the time. The format is someting like this: data source=220.127.116.11,1234;Initial Catalog=my_db;User ID=login;pwd=password;
May 09, 2010Hey Rick, it will take any valis connection string. I use sql auth all the time. The format is someting like this: data source=18.104.22.168,1234;Initial Catalog=my_db;User ID=login;pwd=password;
May 04, 2010Hi Fred, how to configure the connection string if i going to use SQL authentication instead of windows. This is due to servers are place in different region.
October 29, 2009VERY nice work guys!! Just saved our butts trying to move off a hosted DB with no "real" access to perform backups, etc.
June 12, 2009There is SQL Server specific sql used to read the list of tables. You could grab the source and change the SQL to work with MySql. The BulkCopy class should be able to handle mySql? Im doing more work with oracle lately. If I have time I can try Oracle to Sql Server or MySql to Sql Server...etc.
June 12, 2009Does the source datasource have to be MS SQL Server? Could it be used to copy data from MySQL to MS SQL Server ?
MMALAFRONTE •June 08, 2009I am executing a BCP command from within a VB6 application. When the length of the Sql record I am copying out is increased to 131 characters the last character is not copied out. Whhen I set theh record length to 130 or less all is well. Is there some sort of setting I can change? Any help would be appreciated. Michael
fred.sobel •April 19, 2009Exe for the branch is here: http://projects.c3o.com/install/sqlcopy/simplesqlcopy2.exe
fred.sobel •April 19, 2009Ahmed, there is a branch based on PockyMaster's code which may fix your problem. You can pull it down here: http://sqlcopy.googlecode.com/svn/branches/22.214.171.124/
fred.sobel •April 19, 2009There is a branch based on PockyMaster's code which may your problem. You can pull it down here: http://sqlcopy.googlecode.com/svn/branches/126.96.36.199/
February 05, 2009I got error "Invalid object name 'information_schema.tables'". I guess this is collation (binary or case-sensitive) related error, because 'INFORMATION_SCHEMA.TABLES' must be in upper case.
February 04, 2009create/drop destination tables would not be that easy. What about relationships, indexes etc? Maybe adding a mapping functionality would be cool though.
mxmissile •February 03, 2009I have modified the source to include an option that creates destination tables automatically. A couple caveats:
- It requires the Microsoft.SqlServer.Management.* namespace, which gets installed when you install the latest Sql Studio.
- Requires VS 2008, .NET 3.5 to compile.
Anyway, if you or anyone is interested in the source, let me know.
February 03, 2009You should implement the an option to create or drop destination tables. Create would create the destination tables if they dont exist based on the source, and drop would drop the destination tables and re-create them before copying the data.
January 26, 2009I'm trying to move between two SQL Server 2005 instances. I keep getting errors if the source table has a datetime field. It keeps saying it is out of range, even though it most definitely isn't. Any ideas?
January 20, 2009Found some issues with computed columns. I changed the code a bit to filter them. I made some other improvements as well: Selection of nr. of simultaneous copy threads, added extra column to show if the object is a table or a view, added quick selection for selection only tables or only views, added progress notification (every 1000 rows or so the status is updated) + showing when all is copied. Further I fixed the crash when the copy button is pressed twice. If you want the updated code, let me know.
January 15, 2009Eric, sounds like you have a whole new version. Send an email to email@example.com and I'll set you up to admin the code.
January 15, 2009Found some issues with computed columns. I changed the code a bit to filter them. I made some other improvements as well: Selection of nr. of simultaneous copy threads, added extra column to show if the object is a table or a view, added quick selection for selection only tables or only views, added progress notification (every 1000 rows or so the status is updated) + showing when all is copied. Further I fixed the crash when the copy button is pressed twice. If you want the updated code, let me know.
December 11, 2008Thank you!! You have no idea how many hours I spent wrestling with the DTSWizard before finding the page. A model of a program - simple, effective and it just plain works.
wiredone •October 17, 2008i use this nearly every day and it is my favourite little utility/tool over subsonic
however i have one gripe...
there is no progress indicator; if you have a table that is huge you have no way of knowing if something is still happening of the thing has hung (which it never has but hey 'd like to know how long i have to wait - even if just a percentage
September 30, 2008Seems to have a problem with calculated columns (i.e. needs to exclude from the copy I guess). I.e. threw up the following error: The column "FullQuotationCode" cannot be modified because it is either a computed column or is the result of a UNION operator.
alacom •September 17, 2008
Let me first start by saying thanks for providing such a helpful utility.
How does it handles uniqueidentifier data?
I tried to copy an aspnet_user membership table whose primary key column (userid) is a uniqueidentifier, along with tables that used that column (userid) as its foreign key reference, and those tables failed to copy the data.
Below was the error message
"The given value of type Int32 from the data source cannot be converted to type uniqueidentifier of the specified target column."
September 04, 2008Thanks a pile!! I was dreading going through the list of tables (again) and enabling identity insert on every one. We don't have backend access and can't just take a simple backup You're a legend.
July 31, 2008Are you sure the destination table is created? The tool copies data. It's up to you to create the tables but that part is easy using "Generate Script...".
July 30, 2008try to copy data to another database in the same remote server but cannot error is - Cannot access destination table '[dbo].[name_list]'. Please help
fred.sobel •July 21, 2008Roach; the copy is handled internally by the SqlBulkCopy class. You could use Profiler to see what gets passed to Sql Server.
fred.sobel •July 17, 2008The copy is done using the SqlBulkCopy class. I create a datareader using "select * from table", set destination, options and the call the WriteToServer method passing in the reader.
July 17, 2008The copy is done by the SqlBulkCopy class. All I do is create a datareader using "select * from table", set destination, options and the call the WriteToServer method passing in the reader.
July 17, 2008This is awesome. Thanks a ton. Is there a way I can view the sql script that is executed that performs the copy.
fred.sobel •May 03, 2008Thanks Mat, Added "with check" and edit window so you can modify the pre and post sql.
fred.sobel •May 03, 2008Thanks Mat, Added with check and edit window so you can modify the pre and post sql.
May 02, 2008Great App. Thanks so much. I spend hours banging my head against the wall with the SQL Server data import wizard. One this though. I don't think the constraints are correctly enabled after the data load. You might need to use: exec sp_MSforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' see http://www.eggheadcafe.com/software/aspnet/31300619/disabling-and-enabling-al.aspx Thanks again
April 28, 2008OMG finally I can export data!! Thanks for doing what microsoft couldn't. You're a legend!!
fred.sobel •April 25, 2008Looks like the SQLBulkCopy class does not like copying data between different locale ids. (1033=US, 1030=Danish).
April 25, 2008Sounds like it does not like copying data between different locale ids. 1033 = US 1030 = Danish Can you set both source and destination to same locale?
April 24, 2008When I try to copy data I get this error for 8-10 tables: The locale id '1030' of the source column 'alias' and the locale id '1033' of the destination column 'alias' do not match.
April 14, 2008Nice, Simple App! Thanks! Would be nice to have support for case-sensitive databases and have a checkbox for excluding views.
April 08, 2008I found it. Persist Security Info=True;User ID=sa;Password=password" added that instead of Integrated Security. Thanks sooo much.
April 08, 2008How input the username/password. I am working remotely and do not have console access. It might have to do with the "Integrated Security=True" how do i input username/password. Thanks
March 19, 2008Thanks Mike:) See the shiny new donate button at the top or just click on my name here.
March 19, 2008Thanks Mike:) Added a shiny donate button at the top of the page or click on my name.
March 19, 2008Thanks Mike:) I added a shiny paypal donate button at the top. or just click on my name.
March 13, 2008good point, I thought the too does create the original table with all keys and indexes then it copies the data, but I created it manually and it worked, thanks~!
fred.sobel •March 10, 2008Are you sure the destination table is created? The tool copies data. It's up to you to create the tables but that part is easy using "Generate Script...".
March 10, 2008Are you sure the destination table is created? The tool copies data. It's up to you to create the tables but that part is easy using "Generate Script..."
March 10, 2008thanks for putting the efforts to put this together. But I get a message saying "Cannot access destination table '[dbo].[MyTableHere]'" Is there a special permissions for bulk copy? I'm using my built-in admin account, I also tried with sa account and still does not let go through... or is there something bad in my table naming? thanks.
March 07, 2008Great!!!! It's midnight and finally I found the way to do the old work in a smart way! Thanks you I can go sleep! :D Just one suggestion: could you insert a checkbox to skip views from the list? Thanks again :D
fred.sobel •March 03, 2008TomP, Mark: Please try this version. http://projects.c3o.com/install/sqlcopy/SimpleSqlCopy.exe I added some error handling on connect. Let me know what kind of error you are getting. You can always get me at firstname.lastname@example.org
March 03, 2008Please try this version. I added some error handling on connect. Let me know what kind of error you are getting. You can always get me at email@example.com http://projects.c3o.com/install/sqlcopy/SimpleSqlCopy.exe
March 03, 2008Ran into the identity issue trying to copy database contents from a test to a new production 2005 server. I have the same problem as Mark, unhandled exception when connecting to a SQL 2005 as source. Even found the CONFIG.EXE and changed the source information there, and it still points to the original source (sqlexpress). Suggestions?
February 21, 2008I get an unhandled exception error when I try and connect to my 2005 database - it works when connecting to the 2000 database. Any help would be appreciated.
February 18, 2008Turn off "optimise for multiple tables" and turn on "Enable identity Insert". That works for me! :)
markworsnop •February 17, 2008This little program has saved the day! thanks! any chance I could get a copy of the source so I understand how this works?
February 14, 2008Hey Fred, I have tried to download and run from the site but unsuccessfully. Really need your tool to work around SQL 2005, can you repost it on the web. That would be very appreciated. CPP
fred.sobel •February 09, 2008
I've been testing a new version of simple bulk copy for a few weeks now and thought I would make it available for click once install. So I published the project to my file system and uploaded the new version to the ftp site. Fired up the app, pulled down the new version and wham ran into this nasty error: "Configuration system failed to initialize"! Ouch.
Turns out I had decided to do some cleanup and change my namespace from Test.... to c3o.... which should be okay but this also changed the App.Config which causes all the configurations to be under
c3o.SqlCopy.Properties.Settings instead of Test.SqlCopy.Properties.Settings
So after some quick changes I reverted back to the old "Test" namespace and all should be well. Let me know if you run into any problems!
February 01, 2008I have to echo the sentiments of the others here. Thank you for putting in the time to do this. Not exactly happy with Microsoft's response on this issue. Thank you!!!
fred.sobel •January 08, 2008The source is pretty rough right now so I did not make it available for download. The key to the code can be found here: http://davidhayden.com/blog/dave/archive/2006/01/13/2692.aspx
Tarwn •December 26, 2007I am in the process of trying to copy data from approximately 10000 tables (don't ask) from server A to server B. Unfortunatly when you type in the connection string from the first database and click the "Refresh" button, the interface pulls in all of the Tables and Views. Would it be possible to have a checkbox to only display tables, or somethign of that nature?
Tarwn •December 26, 2007This is a result of me being impatient with the tool, but if you click the "Copy Data" button, then click it again before the copy has completed, you will receive a .Net exception (System.InvalidOperationException) from the BackgroundWorkerProcess.
- Disable the Copy button until the executed copy process is complete
- Add some type of indicator to show that the application is in the process of a copy (a label or progress indicator somewhere perhaps)
December 18, 2007great tool, helped me out on copying 200+ tables from sql 2k5 to 2k. Time-outs on large tables though. Perhaps add a partial transaction feature (like 50000 rows per commit)
December 17, 2007Great app. One request, is to have a setting for the timeout on the bulk object. I am getting a timeout error in the middle of a large table. Thanks
December 11, 2007Excellent app fred.sobel. Neat and Simple. I'm sure a lot of DB Admins/Developers will find it very useful
fred.sobel •December 04, 2007Guci, Changed it to [table_schema].[table_name] using information_schema.tables
December 04, 2007Thanks Fred, it works great. One minor suggestion would be to allow the user to view the full stack trace (maybe by clicking on the error or something), when a table does not successfully copy. Mine was simply because the schema was slightly different, but there may be cases where it isn't so obvious why a copy failed. Otherwise, it works absolutely great, thank you very much!
fred.sobel •December 03, 2007Matt: You can pull the exe down from here: http://projects.c3o.com/install/sqlcopy/SimpleSqlCopy.exe
December 03, 2007Guci: thanks, I'll give it a try. Matt: You can pull the exe down from here: http://projects.c3o.com/install/sqlcopy/SimpleSqlCopy.exe http://projects.c3o.com/install/sqlcopy/SimpleSqlCopy.exe
December 03, 2007This looks like a solution I would love to try, but I get the error "Cannot download the application. The application is missing required files. contact application vendor for assistance." I get the error no matter if I download it in IE or FF.
December 03, 2007Nice app, better than the original MS Wizard :) But it won't work with tables with a schema other than dbo. You should use [schema].[tablename] in the query.
November 29, 2007Am I being thick - when I open the download (SimpleSqlCopy.application - 6k) I get "Cannot start application. The application is missing required files". Contact application vendor for assistance"
fred.sobel •November 24, 2007
Changed checkbox list to DataGrid to display errors. Also changed call to asynchronous for better feedback. Added user settings to remember source and destination.
Keep Identity & Nulls
Sql Server, Oracle & MySql
Edit Select SQL
Command line version