Thursday, June 08, 2006 9:01 PM
In case anyone is interested, as part of updating my blog, I had a need to copy my Access database to SQL Server. Since GoDaddy doesn't allow remote access using Enterprise Manager (well, at all...) I had to perform all operations from their own web-based interface or from code. Their interface supported queries, but didn't support linked servers or OPENROWSET so I was forced to resort to code. My simple solution was to create a table data copier. Specify the host and destination datasource (using OLE DB connection strings), the table name, and optionally, a sort by column name (probably not needed in retrospect). Next, it turns on IDENTITY_INSERT for the table and copies the rows one by one. It was fast enough for my needs, though there are no optimizations. Don't expect bulk copy performance!
Anyway, if this seems at all useful, download the code
here. If there is any interest, I can include the pre-built assembly as well. It's simple, but it can do the job. Even more useful would be to select tables programmatically and do more than one in a batch. Oh well, I'm ready to move onto the next project! Use it if you can.