Freigeben über

Importing Multiple Scripts using DTE

  • Artikel

We have had a lot of requests from folks that would rather not go through the Import Script Wizard when they have multiple T-SQL source scripts. Fortunately Visual Studio provides the ability to execute parameterized commands by using the Design Time Extensibility (DTE) framework. For a typical list of such commands visit this MSDN Library entry. In this blog I’ll lead you through a useful command provided by General Distribution Release (GDR) for Visual Studio Team System 2008 Database Edition which allows you to bypass the Import Script Wizard and import your scripts directly into your project system.

Step 1: Create a script

Create a script as c:\myScript.txt with the following contents:

CREATE TABLE [dbo].[Table1]

(

column_1 int NOT NULL,

column_2 int NULL,

column_3 as (column_1 * 14.3)

)

GO

EXEC sp_addextendedproperty @name='TableWithComputedColumn',

@value ='column_3',

@level0type = 'SCHEMA',

@level0name = 'dbo',

@level1type = 'TABLE',

@level1name = 'Table1',

@level2type = NULL,

@level2name = NULL

GO

CREATE VIEW [dbo].[View1] AS SELECT column_3 FROM [dbo].[Table1]

GO

CREATE USER [User1] WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo;

GO

GRANT UPDATE ON [dbo].[Table1] TO [User1]

Create another script “c:\myScript2.txt” with the following contents:

CREATE PROCEDURE [dbo].[Procedure1]

@param1 int = 0,

@param2 int

AS

SELECT @param1, @param2

RETURN 0

Step 2: Create a Project

Launch Visual Studio and create a SQL Server 2008 project:

image

Step 3: Select the new project in the Solution Explorer

When you execute DTE commands, they execute in the context of the project that is selected in the Solution Explorer. You should back up your project or, better yet, check in your project to version control before you run this DTE command.

image

Step 4: Display the Command Window

By using the Command Window, you can run DTE commands and provide parameters to those commands that allow them. To display the Command Window, open the View menu, point to Other Windows, and click Command Window.

Step 5: Import the Script

The import script DTE command is “Project.ImportScript”. Now you are at the point where you can run the command and have some fun.

First try the command without any parameters (“Project.ImportScript”):

image

You’ll notice that the Import Script wizard appears. This is the command that is executed when you right-click on the project node and click “Import Script”. Not so interesting. Dismiss this dialog box. Next you will parameterize the command.

The parameters for “Project.ImportScript” are as follows.

Project.ImportScript /FileName [filename] [/encoding Unicode|UTF32|UTF8|UTF7] [/Overwrite] [/IgnoreExtendedProperties] [/IgnorePermissions] [/AddImportedPermissionsToModel]

/FileName [filename]

This is the file that you want import. Place quotes around the file name if it has embedded spaces.

/encoding Unicode|UTF32|UTF8|UTF7

The encoding of the file

[/Overwrite]

If duplicate objects are discovered should they be overwritten?

[/IgnoreExtendedProperties]

Suppress importing extended properties

[/IgnorePermissions]

Suppress importing permissions

[/AddImportedPermissionsToModel]

If provided, any imported permissions will not be added to the model. This means your permissions XML file will have a Build Action of “Not In Build”

Now import your myScript.sql file. You can ignore extended properties and permissions. Run the script below (“Project.ImportScript /FileName “C:\myScript.sql” /IgnoreExtendedProperties /IgnorePermissions”):

image

Notice that your project now has the following files:

MyProject\Schema Objects\Schemas\dbo\Tables\Table1.table.sql

CREATE TABLE [dbo].[Table1]

(

column_1 int NOT NULL,

column_2 int NULL,

column_3 as (column_1 * 14.3)

)

MyProject \Schema Objects\Schemas\dbo\Views\View1.view.sql

CREATE VIEW [dbo].[View1] AS SELECT column_3 FROM [dbo].[Table1]

If you now change the command to “Project.ImportScript /FileName "C:\myScript.sql" /Overwrite /IgnorePermissions” the table script will be altered to:

CREATE TABLE [dbo].[Table1]

(

column_1 int NOT NULL,

column_2 int NULL,

column_3 as (column_1 * 14.3)

)

GO

EXEC sp_addextendedproperty @name='TableWithComputedColumn',

@value ='column_3',

@level0type = 'SCHEMA',

@level0name = 'dbo',

@level1type = 'TABLE',

@level1name = 'Table1',

@level2type = NULL,

@level2name = NULL

To import your second script, change the command in the Command Window to ““Project.ImportScript /FileName "C:\myScript2.sql”. After you run the command, the project system contains the following file:

MyProject \Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procedure1.proc.sql

CREATE PROCEDURE [dbo].[Procedure1]

@param1 int = 0,

@param2 int

AS

SELECT @param1, @param2

RETURN 0

Obviously you can now repeat this process for all the SQL files that make up your schema.

Conclusion

Note that any DTE command is available through DTE automation and is accessible through EnvDTE80.DTE2.ExecuteCommand or EnvDTE80.DTE2.Commands.Raise. You can use the DTE commands to create Add-Ins, external processes, or packages to automate your specific system. GDR for Visual Studio Team System 2008 Database Edition GDR provides other parameterized commands. I will blog about these soon.

 

- Patrick Sirr

“DataDude” Programmer

Comments

  • Anonymous
    November 22, 2008
    Patrick Sirr , one of the key developers in our team started his own blog. If you want to learn about

  • Anonymous
    November 25, 2008
    Finally the moment is there, the final version of the Visual Studio Team System 2008 Database Edition

  • Anonymous
    November 25, 2008
    The VS Database edition team has just released the golden version of the Visual Studio Team System 2008

  • Anonymous
    November 25, 2008
    The VS Database edition team has just released the golden version of the Visual Studio Team System 2008

  • Anonymous
    November 18, 2009
    The pictures no longer show.  Would you please restore them.  Clicking on them leads to a 404 message about: http://patrick.sirr.members.winisp.net/blog/b388cb959fca_13689/clip_image006.jpg thanks

  • Anonymous
    November 18, 2009
    Restored!  Thanks for letting me know.  Patrick

  • Anonymous
    November 18, 2009
    This works fine, one at a time.  I've got a file of 340 command to run.  is there a way to run them without pasting each one?   Thanks Andy

  • Anonymous
    November 18, 2009
    You have a bunch of options.  You could create a macro, addin or even drive Visual Studio from an external package.  If you're importing multiple scripts you might want to try my addin here: http://blogs.msdn.com/psirr/archive/2008/11/24/database-project-import-scripts-add-in.aspx