Revit DB Link with SQL Server

[Anonymous]

I have been trying the RevitDBLink plugin for Revit 2013 downloaded from the subscription resources. It doesn't appear to include any guidance nor can I find any online. These are my observations:

After installing it and starting Revit, The Add-ins menu now includes the item Revit DB Link greyed until a project is loaded. 

Click on this and it offers a dialog to select a new or recent database connection. There are tabs for Access 2000-2003, Access 2007 and ODBC.

I selected ODBC tab (intending to use SQL Server), and [Select a new Connection]

There are Export and Edit and Import buttons. I clicked Export and the standard Windows Select Data Source dialog appeared.

Because I wasn't logged on to WIndows 7 as Administrator, I couldn't create a Machine Data Source, so I chose a File Data Source and saved it as RDBLink.dsn

I selected the appropriate SQL Server, Integrated Security and didn't alter the default database.

When the the DB Link exported the project data to SQL two things happened:

1) Tables and foreign keys were created in the Master database - not intended

I had to clean up my master database using scripts generated like so:

SELECT 'ALTER TABLE ' + t.name + ' DROP CONSTRAINT ' + k.name FROM sys.foreign_keys k

INNER JOIN sys.tables t ON k.parent_object_id = t.object_id WHERE t.is_ms_shipped = 0

SELECT 'DROP TABLE ' + [Name] FROM sys.tables WHERE is_ms_shipped = 0

2) Truncation errors occurred where the exported data exceeded the size of the destination fields.

IMHO this is a bug in the Revit DB Link which may make it unsuitable for safe round trip (edit in DB and reimport to Revit) operations.

I only wanted to export from Revit so I tried again having

a) created an empty database RDBLink and selected it as default 

b) unticked Use ANSI Nulls Paddings and Warnings

The import stilll produced errors; some tens of entries like this:

< DBG_WARN: for MEP topologies key, the optionId should be notInOptionId: line 96 of Elem\PlanTopologyIdInfo.cpp.

Again, since I wasn't interested in MEP I wasn't bothered anthough, again, this looks like a bug with the RDB Link.

I did get 251 tables of data from my Revit Project, including a table of Views with the following fields:

CREATE TABLE [dbo].[Views](
[Id] [int] NOT NULL,
[Family] [nvarchar](255) NULL,
[FamilyandType] [nvarchar](255) NULL,
[PhaseFilter] [int] NULL,
[PartsVisibility] [int] NULL,
[DetailLevel] [int] NULL,
[SunPath] [int] NULL,
[DisplayName] [nvarchar](255) NULL,
[SheetName] [nvarchar](255) NULL,
[SheetNumber] [nvarchar](255) NULL,
[Orientation] [int] NULL,
[AssociatedLevel] [nvarchar](255) NULL,
[Discipline] [int] NULL,
[DisplayModel] [int] NULL,
[ViewScale] [int] NULL,
[ScaleValue1:] [int] NULL,
[TitleonSheet] [nvarchar](255) NULL,
[ViewName] [nvarchar](255) NULL,
[Phase] [int] NULL,
[Sub-discapline] [nvarchar](255) NULL, --Mis spelling not mine!

From this I created a Query of Views:

SELECT TOP ( 100 ) PERCENT

    Id

   ,SheetName

   ,SheetNumber

   ,TitleonSheet

   ,ViewName

   ,'1:' + CAST([ScaleValue1:] AS NVARCHAR(5)) AS Scale

FROM

    dbo.Views

WHERE 

   SheetNumber IS NOT NULL 

ORDER BY

    SheetName

   ,SheetNumber

   ,ViewName

Quite a performance just to get this information. I'm going to try it doing it by using code and the api instead of the RDB Link but I hope the steps I have outlined help others to get Revit data into SQLServer.

Steve

[jeremytammik]

Dear Steve,

Thank you very much for this helpful information. I am sure it will be much appreciated. To ensure you get full visibility for this, I would be happy to create a blog post on The Building Coder from it, if you like. Thank you!

Best regards,

Jeremy

[Anonymous]

Hi, I know this is one and a half years later, but I just did an ODBC export to SQL Server and encountered the same thing (tables created in master) that you did.  I used the queries you have below (copied and pasted into a new query, both statements) and the query executes correctly, but the tables dont dissappear.  What am I missing?  Did you use a command line interface for that?  I am using the new query in the Microsoft SQL Server Management Studio, and executing within there, so perhaps there is a step missing, but the tables stay there (screen shot below).  Thanks for any help you can provide!

Paul

[Anonymous]

I will get back to you tomorrow. If. I don't then call my mobile 07747718389
Steve

Sent from my Sony Xperia™ smartphone


This message has been scanned for malware by Websense. www.websense.com

[Anonymous]

Hey, I did a quick reply, but didn't see it post.  Basically I figured out that your queries just generate a list of SQL commands, so I redirected the results to a file and then used that file as a new query.  It worked, but the management studio tree view does not update correctly (even when refreshed) and so I could not tell it worked unitl I closed the tree and re-opened.  Thanks for your great post . . by the way, did you ever figure out how to get the export to create a new database, or to use one that you created for that purpose?