Sql-server – Exporting from SQL Server 2005 for import into ER Studio 9.0

erdsql serversql-server-2005

I am new to ER Studio and need to create an ERD for a remote system. That system can only be accessed by LogMeIn remote control and by FTP. The remote system is running SQL Server 2005. The local system has ER Studio 9.0 installed.

How can I export a file from SQL that will allow me to create a diagram in ER Studio? I can see the import function, but I keep getting errors when I attempt to import a SQL file that I've created from the remote system.

I think I'm simply choosing the wrong options on export. Here's what I'm doing:
SQL Server 2005, Object Explorer
1. right-click on the database I want to diagram
2. Choose Tasks > Generate Scripts
3. Select the desired database, next
4. Leave the default options (I've tried a few things, none worked), next
5. Select Tables only (I don't need anything except the tables, their fields, keys, and relationships)
6. Select all tables (or 4 tables, it doesn't matter)
7. Choose "Script to file", enter a file name, next
8. Finish.

The output script, with a .sql extension, contains commands for creating the tables with all the correct fields. It's lovely.

In ER Studio, New Data Model dialog box
1. Select "Import Model From:" and choose SQL File
2. Click Import
3. Select the appropriate SQL file
4. Select target database platfrom (SQL Server 2005)
5. Don't tick any boxes about inferring keys or domains)
6. Hierarchical layout is the default. Cool with me.
7. Relational model
8. Leave "Use Physical Parser" unchecked. No idea what it does, but checking it doesn't help.
9. OK
10. Invalid token, Line 1, Offset 4.

If I manually delete the line that is giving ER Studio trouble, I still get an error. I googled the heck out of this and found nothing of use. Absolutely any feedback would be appreciated.

Best Answer

I can't believe I didn't actually google the exact error message. There's a lesson to be learned there. Upon doing so, I learned that similar errors occur in other applications. The cause is non-ASCII codes. I was exporting from SQL in Unicode.

Here is the full solution: In SQL:

  1. Right-click on the desired db, choose Tasks > Generate Scripts
  2. Select the desired database, next
  3. Leave the default options (I've tried a few things, none worked), next
  4. Select Tables only (I don't need anything except the tables, their fields, keys, and relationships)
  5. Select all tables (or 4 tables, it doesn't matter)
  6. Choose "Script to file", enter a file name
  7. Under Save as: choose ANSI text (Unicode breaks the crap out of ER/Studio)
  8. Finish

Then import into ER/Studio. My import has been running for 2 hours so far.

Next up, trying to figure out how to make an ordered list on this site. I'm so full of fail. :)

Related Question