0

I need to migrate some tables from SQL Server to database on another server, but those 2 servers are not in available in the same network.

I decided to use SSMS's Export Wizard to export data from source server to MS Access, and then, after switching networks, to import data from this Access file to destination server.

I had some problems with tables definitions so I scripted all the tables as CREATE script and ran it on the destination server, this way tables are exactly the same on both source & destination server.

The problem is that, when exporting to MS Access file, SSMS converts any nvarchar to LongText, even if it's limited nvarchar e.g. nvarchar(200).

This alone probably isn't problematic, but when you try to import data to destination server, I'm getting errors (even though I set it to ignore errors) and the process stops. The problem is that the columns in Access is LongText and in destination's server it's nvarchar(200). When I change the column type in Access to ShortText, everything works correctly.

I would probably go table after table and change all LongText to ShortText (where source type is a length limited nvarchar), but I checked and there are around 100 of such columns. I checked LongText vs ShortText on MS docs and it reads ShortText is below 255 characters and LongText is everything above, so I'm puzzled why SSMS converts every nvarchar (even those below 255) to LongText. You would think those technologies should talk well to each other as they both are Microsoft's but no no. Any idea for a workaround?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
robs23
  • 135
  • 1
  • 13
  • 1
    I'd rather stay in SQL Server, using this: https://stackoverflow.com/questions/20542819/how-to-export-all-data-from-table-to-an-insertable-sql-format – Andre Aug 16 '21 at 18:25
  • You could also use bcp. It will give you more control over formatting if you really need it. – Robert Sievers Aug 16 '21 at 20:10
  • 1
    @Andre Great tip, haven't realised there is a way to script data insert! It took a whlile to create scripts for all the data but it worked. Thank you! – robs23 Aug 17 '21 at 12:11
  • @RobertSievers As to backup, the source server is extremly locked, meaning it's difficult to move the backup file from source server, unless you physically put a flashdrive in – robs23 Aug 17 '21 at 13:10
  • 1
    @robs23 If you can access the server via SSMS, you can do so with bcp. https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15 – Robert Sievers Aug 17 '21 at 17:48
  • @RobertSievers I didn't know this tool at all. Sounds interesting, need to read up on it a bit. Thanks! – robs23 Aug 18 '21 at 17:14

0 Answers0