Visual Studio Database Development Tools (Formerly "Database Edition Forum") Forum

Strange behavior during deploy

Wed, 25 Nov 2009 15:30:56 Z

I am working on a GDR project that I inherited from a colleague.

The issue I'm dealing with is this:

At one point, the project included a stored procedure, let's call it ProcA that included the following code: backup log tempdb with truncate_only

This proc was later deleted (since SQL 2005 does not support the Truncate_only option) and a new version of the proc (ProcB ) was created and added to the project. This new ProcB did not have the Backup Log statement in it.
The current state of the GDR project shows the new ProcB and shows no traces of the old ProcA.
When the project is deployed, it fails with the following error message : Gen-363(330,24)Error TSD02010: Incorrect syntax near truncate_only. Proj_DB.dbschema(0,0)Error TSD01234: The application encountered an unexpected error. To diagnose this problem, enable tracing.
Proj_DB.dbschema(0,0)Error TSD01234: Failed to import target model Proj_DB. Detailed message A fatal error occurred while the model was being imported.
I ran a search in the entire GDR solution for the words 'truncate_only' and it yielded no results.
Since I could not figure out the source of this piece of code, I turned on tracing and ran the deploy. Within the output of the trace I found that ProcA was still being included in the deployment.
I deleted the .dbdml file assuming that there may have been traces of the old proc left behind in the model.
It still did not work. Every time I deploy, it fails with this error message.
Why is it still including ProcA in the deployment ?

Model Already Has an Element That Has the Same Name error

Mon, 23 Nov 2009 21:25:32 Z

I have used Database file references (.dbschema files) to include references to objects in an external database.
When I deploy this database project I see errors stating - Model already has an element that has the same name ...
These errors relate to objects in the referenced database project.
Both the projects build without a problem. It is only at deploy time that the errors are displayed.
I started investigating the referenced database project to verify the cause of this. The objects it was complaining about related to indexes using the same name as columns in a table.
So for example, if I have a table called Contact with a column called EmailAddress
and I have an index called EmailAddress
Is this a problem ? Why is this a duplicate element ? And why does this not complain at compile time ?

Configuration settings are not being saved in the database project file

Wed, 25 Nov 2009 15:25:33 Z

The "Deployment configuration file" and "Sql command variables file" settings are not being saved correctly in our VSTS database project files. We are using VSTS GDR R2 (9.1.40413.00)

We have a database project with 6 separate Configurations. They are called: Debug; Release; Development; TFSBuild; SystemTest and Live. We want to use the same Deployment configuration and command variables files for the first 5 of these configurations and different files for the Live configuration. So the desired mapping of configurations to files is:

Config      Deployment config file                       Sql command variable file
=========== ============================================ ==============================
Debug       Properties\Tosca.Database.sqldeployment      Properties\Tosca.Database.sqlcmdvars
Release     Properties\Tosca.Database.sqldeployment      Properties\Tosca.Database.sqlcmdvars
Development Properties\Tosca.Database.sqldeployment      Properties\Tosca.Database.sqlcmdvars
TFSBuild    Properties\Tosca.Database.sqldeployment      Properties\Tosca.Database.sqlcmdvars
SystemTest  Properties\Tosca.Database.sqldeployment      Properties\Tosca.Database.sqlcmdvars
Live        Properties\LIVE.Tosca.Database.sqldeployment Properties\LIVE.Tosca.Database.sqlcmdvars

When editing the project in VSTS we find that sometimes the changes we make to these settings do not get saved to the database project file.

For example, we can

- open the Properties window for the database;

- choose the Release Configuration,

- select the appropriate files from the combo boxes,

- click Save,

- close the Properties window,

- reopen the Properties window,

- select the Release Configuration again

- and the files we selected are lost. Instead VSTS shows: Properties\Database.sqldeployment and Properties\Database.sqlcmdvars.

Is this a known bug or am I missing something obvious? At the moment this has caused us to suspend use of database projects due to the resultant loss of productivity and confidence. I would be keen to resume, but cannot until we know what is going on with this.

TSD03006: User: [X] has an unresolved reference to Login [X]

Mon, 23 Nov 2009 17:32:15 Z

Hi Friends,

When i try to create data base project with LARGE data base I am getting this error : TSD03006: User: [X] has an unresolved reference to Login [X]

User 'X' i have created for our own purpose.

I have given reference this user in all stored procedures and functions, so I don't want to remove this user

Can you please someone help?

Thanks
Ragu

?? Missing the "Data Generation Plan" in VS 2008 ??

Tue, 16 Sep 2008 16:02:20 Z

Hi All,

I have Visual Studio Database Edition (Visual Studio 2008, SP1) installed
and I'm trying to create a Data Generation Plan according to these
instructions:

http://msdn.microsoft.com/en-us/library/aa833237.aspx

My problem is that I do not have a Data Generation Plans node in Solution
Explorer. I cannot add a Data Generation Plan. The Data Generation Plan does
not exist.

Does anyone have an idea on how to fix this?

TSD04105 and TSD03006 - Trying to reference across databases but getting this error in VSTS DB Edition 2008 GRU

Mon, 23 Nov 2009 22:42:44 Z

Hello - I've seen this mentioned in slightly different ways across the board but was really hoping someone could either help. I've got a database project, and have imported all the objects into it. Some of the views have references to another database on the sql server such as

select * from db2.dbo.table

However I was getting TSD03006 errors. So I tried creating a dbschema file of the db2 database, and then added as a reference in the original project. That kinda helped but then for similar stored procs and views I get TSD04105. The only reason I did this was because using Add Database Reference would not work.

So the question, should I try to create a master sql project and put both database projects into the master. Will that allow me to have the cross database references without all the name collision problems. I really don't want to rename all my views in one database just so I can get a clean build.

Thanks much for any help.

D. Shapiro
London

Remove from Project doesn't work -- file is still in the project file

Fri, 20 Nov 2009 23:35:51 Z

I am trying to clean up a project. There are hundreds of uneccessary files that I have chosen to "Remove from Project" to get a good build. However, about 70 of the files I have removed from the project remain in the .dbproj file. They don't show up in the Solution Explorer, but when I build, they are causing errors. I manually went into the dbproj file and removed one of the files and rebuilt. That file is no longer causing an error.

Is there a bug or a glitch, or have I missed something?

msbuild Recovering from Failed Deployments

Fri, 20 Nov 2009 15:29:26 Z

Is there a supported mechanism to allow a rollback the whole process if any part of the deployment generates and error.

Currently, msbuild seems to rollback if the error comes from the database design update. However, it does not seem to rollback if the error comes from pre or post deployment scripts.

Is this a misunderstanding on my part or is there an option/setting I overlooked?

Update existing database using latest dbschema and vsdbcmd tries to alter mdf

Thu, 05 Nov 2009 16:25:42 Z

When I try to do a vsdbcmd deploy using my latest dbschema, the resulting script tries to alter the mdf file, which results in the sql failing because the database is in use. Is there a way to avoid the alter mdf statement from generating in the deployment script?

This alter is appearing I feel because the $DatabaseName references in the dbschema file doesn't match the statically named file on the server, so it tries to alter it to the $DatabaseName

This is in GDR2

Role memberships get dropped

Fri, 23 Oct 2009 15:07:20 Z

I have a solution that is made up of multiple composite projects. I have one project RBSecurity containing the security objects - users, roles and role memberships. I have another project RBDatabase containing the user objects and code: tables, views, functions, procs etc.
The RBDatabase project makes a reference to the RBSecurity project.
The deploy order is such: RBSecurity Project, RBDatabase Project.
The RBDatabase project has the 'Drop objects in target but not in project' option checked / enabled.
When I deploy the RBDatabase project (NOTE: I don't deploy the solution - I only deploy the project), the role memberships defined in the RBSecurity project get dropped .
Why should that happen ? Since the RBSecurity project is a composite project, all the objects contained within it should be considered part of the complete database model. It appears though that the RBDatabase project is not aware of the existence of the role memberships and therefore drops them from the target during the deploy because of the Drop setting being enabled. No other objects are being dropped - only the role memberships.
I checked the project settings to verify if the option 'Ignore role memberships' was enabled - but that was not the case.
Can you please explain this behavior ?

MSDB reference projects

Wed, 30 Apr 2008 05:41:01 Z

The references projects for MSDB are finally available on MSDN Code Gallery

http://code.msdn.microsoft.com/dbpromsdbsamples

-GertD

Color me silly. What does GDR stand for?

Tue, 28 Oct 2008 17:24:09 Z

Subject says it all.

Can we continue to use DB Pro 2005 with Visual Team Suite 2008?

Fri, 21 Aug 2009 14:35:34 Z

It appears that our plan to upgrade from DB Pro 2005 to DB Pro 2008 has been scuppered by the fact that the 2008 version does not preserve comments outside of the code block. This means (for us) that we cannot use it.

However my company still wants to upgrade to Visual Studio Team Foundation Suite 2008 and I am struggling to find something that definitively states that we could do so and continue to use an integrated DB Pro 2005 for our database management.

Could someone confirm or deny for me please?

-- Andy Kramek

Tables and stored procedure are not created

Mon, 23 Nov 2009 17:15:50 Z

Hi friends,

I am totally new to data base projects.

I having an issue: tables and stored procedures are not created when creating Isolated Database Development Environment.

I have specified SOURCE and TARGET databases. When i look into TESTDb (data base name within the project) i dont see any tables/stored procedures there.

Can you please some one help me?

Thanks in advance

Ragu

Pretty big bug in database project schema compare

Fri, 20 Nov 2009 14:48:37 Z

The below only happens when I have ignore database collation on for schema compare:

Go to my database using Sql Management studio. Add a new column to a table and add a default value of ((0)).

Save the table

Go to the database project and do a schema compare to update the project using the differences from the database.

Write Updates

The new constraint file for the default does not get created in the project.

The project somehow still thinks the file is there however, if I go to the table, and delete the column when I try and build the database project it complains that the constraint cannot be applied to a column that doesn't exist.

When I double click the error it takes me to a file that is the contraint file. So the file is on the hard drive, but it never got added to the project. I cannot find anywhere in the project where it is referencing this file to include in the build. So now since I cannot delete the file from the project, and I cannot find where the project is referencing this file(even using find in all files) my database project is now effectively broken and I can no longer build.

The above works perfectly fine when I do not have "Ignore database collation" on.

I am using visual studio 2008 on windows 7

File script with variable for file size fails to compile, is this supported scenario?

Tue, 24 Nov 2009 16:06:46 Z

I want to replace the file size of my database file with a variable. This is because we are deploying this database and running ETLs from the source system to populate this database every night. Some environment have huge datasets, others very small, so we want to initialize the file to an appropriate size. This is the file script in my storage/files folder:

ALTER DATABASE [$(DatabaseName)]

ADD FILE (NAME = [$(DatabaseName)_Data], FILENAME = '$(DefaultDataPath)$(DatabaseName)_Data.mdf', SIZE = $(DefaultDataSize) KB, MAXSIZE = UNLIMITED, FILEGROWTH = 500000 KB) TO FILEGROUP [PRIMARY];

Whenever I try to compile I get the error: TSD02010: Incorrect syntax near $(DefaultDataSize)

If I replace it with 300000 then I have no problems.

Is this not a supported scenario?

Export to Tsql Editor Grayed out after GDR upgrade and Schema compare

Fri, 12 Dec 2008 21:13:33 Z

I just upgraded to VS 2008 GDR, from VS 2008 database edition. I then converted an existing 2008 project. I then did a schema compare and it shows the results, many more results and options which I love, but I only am able to "Write Updates" the "Export To Editor" button is grayed out. What am I doing wrong? This was steps that I would do before I upgraded to the GDR.

Thanks,
Sean

SqlDeployTask Failed in GDR 2008

Tue, 09 Dec 2008 21:18:30 Z

I recently upgraded my existing DB Pro 2005 project to the GDR 2008. After the upgrade I created a server project, and second database project to handle the permissions for my DEV environment. I can get the solution to build successfully but each time it gets to the deploy task it fails with this error message:

C:\Program Files\MSBuild\Microsoft\VisualStudio\v9.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets(120,5)Error MSB4018: The "SqlDeployTask" task failed unexpectedly.

System.InvalidOperationException: Nullable object must have a value.

at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource)

at Microsoft.Data.Schema.Tasks.DBDeployTask.Execute()

at Microsoft.Build.BuildEngine.TaskEngine.ExecuteInstantiatedTask(EngineProxy engineProxy, ItemBucket bucket, TaskExecutionMode howToExecuteTask, ITask task, Boolean& taskResult)

Done executing task "SqlDeployTask" -- FAILED.

Done building target "DspDeploy" in project "PVE.dbproj" -- FAILED.

Done executing task "CallTarget" -- FAILED.

Done building target "DBDeploy" in project "PVE.dbproj" -- FAILED.

Done building project "PVE.dbproj" -- FAILED.

If I take the build scripts and run them against the server they all complete successfully, so I have some confidence that they are correct but it just seems to fail on the build. Any ideas?

Thanks,
Dave

How do I deploy my database?

Mon, 23 Nov 2009 17:34:00 Z

I'm getting the "TSD01268: .Net SqlClient Data Provider: Msg 1834" as described in serveral posts on this forum - but either I don't understand the explaination - or the "workaround" isn't working for me. I'm trying to automate build and deploy for this project - so I'm not sure what steps to take other than "Manually edit the SQL script after it's generated (which is hardly an automated solution)

I've edited the scripts in the storage folder to use the variables for the [$(DatabaseName)] and $(DefaultDataPath) and make sure all the settings exactly match the server - yet every time I try to run a deployment I get the error.

I've ensured the settings match by running a schema compmare and exporting the settings. Note that the generated scripts don't use the variable name - so I manually edit those back (especially since there is a need to control this - so we can have multiple copies on the same server).

Is there any solution to this problem other than excluding the storage scripts from the database project?

Please let me know if you need any additional information.

Thanks

Dan

Deployment script generated to:

C:\directbuy\DirectBuy NGE\Source\Main\DirectBuyDB\DirectBuy.VSR.Main\sql\release\DirectBuy.VSR.Main.sql

Creating DirectBuy.VSR.Main.DPS...

DirectBuy.VSR.Main.dbschema(0,0)Error TSD01268: .Net SqlClient Data Provider: Msg 1834, Level 16, State 1, Line 1 The file 'D:\Program Files\Microsoft SQL Server\MSSQL10.NGEDEV\MSSQL\DATA\DirectBuy.VSR.Main.DPS.mdf' cannot be overwritten. It is being used by database 'DirectBuy.VSR.Main.DPS'.

DirectBuy.VSR.Main.dbschema(0,0)Error TSD01268: .Net SqlClient Data Provider: Msg 1828, Level 16, State 4, Line 1 The logical file name "DirectBuy.VSR.Main.DPS" is already in use. Choose a different name.

An error occurred while the batch was being executed.

Done executing task "SqlDeployTask" -- FAILED.

Done building target "DspDeploy" in project "DirectBuy.VSR.Main.dbproj" -- FAILED.

Done executing task "CallTarget" -- FAILED.

Done building target "DBDeploy" in project "DirectBuy.VSR.Main.dbproj" -- FAILED.

Done building project "DirectBuy.VSR.Main.dbproj" -- FAILED.

Server project and multiple databases in one solution

Fri, 20 Nov 2009 19:55:11 Z

Up until this point, we have been creating a seperate database project for each of our databases in TFS. So I have a Customer database and a Products database, each with their own solution. They both reside on the same database server, named PRE01.

I have read in various articles that it's possible to create a server project for PRE01 that holds server level objects for just the server, and further, I should be able to put the server project PRE01 in the same solution as the databases that reside on it, in this case Customer and Products. So far I don't seem able to do this though - when I create a new project it is one of the other and while I can put references between them, I don't see how to add a server project and database projects to the same solution.

I'm using VSTS:DB 2008. Am I misunderstanding or can someone give me a quick rundown on how this is done?

Thanks!

Patrick

Bug: "Schema compare does not support the specified data source guid."

Mon, 27 Apr 2009 10:19:29 Z

Hello!

I know this place isn't meant for bug reports, but I tried to register on Microsoft Connect, but it hang everytime I tried registering there. (Where do I report a bug on the bug reporting tool?)

So in the interest of the community, I decided to post the bug here.

Last week I had to reinstall my computer, and after installing Visual Studio 2008 Team Suite, VS SP1 and the VSTSDB GDR R2, I loaded the project I'm working on from source control. (TFS) I have SQL Server 2008 Developer Edition installed on my local machine, but I had not added a connection the local DB in the Server Explorer. Then I wanted to do a Schema Compare with my project as the source and the local db as the target. In the dialog where I choose source and target, the source was already set to Project and the target was set to Database. In the drop down list, the item "[RENE-PC.MyDB]" was selected, where RENE-PC is the name of my PC and MyDB is of course the database name. It was already selected, so I did nothing but click "OK". Then I got a dialogbox saying "Schema compare does not support the specified data source guid." This was a bit strange, I tried closing Visual Studio, reloading the project, restoring the database etc. Then I tried to do the Schema Compare the other way around, but the Source, Database drop down list was empty. So I pressed "New Connection" and typed "." for the server, kept "Windows authentication" on and selected the correct database. Then after clicking OK, the New Connection dialog closed, but an error dialog opened saying something like "The data source is not supported". I couldn't figure out why this was happening, so I tried uninstalling VSTSDB and reinstalling it, and reinstalling VS SP1, but the result was the same. Then I tried to create a connection in the Server Explorer in VS, and then everything worked as it should! When I select the connection as the source or the target, it behaves as expected.
Hope this helps someone.

TSD00565 error - isreplicated and cannot be modified

Mon, 16 Nov 2009 20:05:12 Z

I just added a bunch of stored procedures to my DB project through Import Scripts. When I am doing a DB comparison, I am getting the error TSD00565 now. I don't think these SPs are replicated. I have also changed the option VerifyDeployment to false. It is still happening.

What should I do to get rid of these errors?

Thanks,
-Jason

Can't reference project variables in post deployment script?

Fri, 20 Nov 2009 14:40:13 Z

I have a variable under project properties -> variables

It is: Name: FileServer, Value: .\StaticData\dbo.FileServer.staticdata.sql

In my Script.PostDeployment I have included a line:

I have tried both

:r $(FileServer)

and

:r [$(FileServer)

For the first one, I don't get an error on build but in the deploy script I see:

-- The specified file: $(FileServer) could not be found. :r $(FileServer))

for the second one, I get an error right when I try and build:

Error TSD162: Could not find file 'D:\Projects\SQSData_Dev\SQSData_Dev\Scripts\Post-Deployment\[$(FileServer)]'.

What am I doing wrong... I can't find any help on this anywhere.

Send content of a file to sqlcmdvar

Mon, 16 Nov 2009 16:03:19 Z

I am trying to put the content of an xml file into a single variable. I have a script which does it but it takes a long time to execute.

I am therefore trying to achieve it differently.

I have the following which tells how to edit a dbproj to put the content of a file in a project property.
http://timstall.dotnetdevelopersjournal.com/msbuild_read_and_write_from_a_file_to_a_variable.htm

I also have this post which tells how to assign a property to a sqlcmdvar
http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/0033e77f-665e-4bda-ba5a-57e34e45c05a

Problem is, I have no clue how to put 1 and 1 together.

I am thinking reading into a property and sending to sqlcmdvar. Sqlcmdvar are accessible by sql scripts and the only exemple I got wrote the content of file to property. If there is simpler way to do it, like accessing the dbproj property directly in sql scripts or send the content of the file directly to a sqlcmdvar variable, I'll be glad to hear it.

Re-using server variables for multiple database references

Thu, 19 Nov 2009 22:25:35 Z

We have been semi-successfully using database references between our projects as we have numerous databases that have views and stored procs that are interconnected between multiple dbs. It's a data warehouse environment and without getting overly specific, we have a PRE server (7 databases) where source system databases are replicated and then an ETL server (4 databases) where we do our ETL and presentation layers. Rather than have a mess of interconnected dependencies, on the PRE server there is one database (PREStage) that basically has references to all the other databases AND references to the ETL server too. The database references on PREStage look like this (using sample DB names):

Referenced Database Schema File          Server Variable Name          Server Variable Value          Database Variable Name          Database Variable Value
a.dbschema                                                                                                                             $(a)                                        a
b.dbschema                                                                                                                             $(b)                                        b
c.dbschema                                          $(ETL)                                 ETL                                    $(c)                                        c
d.dbschema                                          $(ETL1)                               ETL                                    $(d)                                        d

My question relates to the d.dbschema entry. It is on the same server as c.dbschema, but VSTS won't let me give it the same Server Variable Name - instead it insists on adding a 1 after the "ETL" though it will let me give it any name that is unique. But it's the same server, so shouldn't I be able to re-use $(ETL)? As I mentioned above, I have 4 different databases that I want to reference that all live on the ETL server. So do I have to use ETL, ETL1, ETL2, ETL3 to represent the same server for the 4 different databases I want to reference on it??

I suspect I am missing something related to how these database references actually work. If you could shed some light on this for me it'd be greatly appreciated.

Patrick

Schema Compare generates null results

Thu, 19 Nov 2009 19:51:08 Z

In GDR R2 (Microsoft Visual Studio Team System 2008 Database Edition GDR Version 9.1.40413.00), I have created a db project with tables only. I want to pull in a few functions and tried to do this via a schema compare with the source database (SQL 2000). However, the schema compare generates absolutely nothing - doesn't show any objects, equal or otherwise.

Could this possibly have to do with the number of objects involved (there are 575 tables for example)? When I run a compare between a SQL 2008 db and it's project (different project, different solution), the compare works fine.

Or do I need to build the project before running the schema compare?

Deployment Error TSD01268: .Net SqlClient Data Provider: Msg 156, Level 15, State 1, Line 17 Incorrect syntax near the keyword 'FROM'

Wed, 09 Sep 2009 14:46:48 Z

The error is being thrown during deployment because of malformed INSERT INTO statement. The select list is empty. This is generated by the deployment engine and I'm unsure of how to correct it outside of manually editing and deploying the *.sql file.

CREATE TABLE [dbo].[tmp_ms_xx_Report_Hierarchy] (



    [Hierarchy_Id]          INT           IDENTITY (1, 1) NOT NULL,



    [Hierarchy_Name]        VARCHAR (100) NOT NULL,



    [Hierarchy_Description] VARCHAR (255) NULL



);







ALTER TABLE [dbo].[tmp_ms_xx_Report_Hierarchy]



    ADD CONSTRAINT [tmp_ms_xx_clusteredindex_PK_Report_Hierarchy] PRIMARY KEY CLUSTERED ([Hierarchy_Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);







IF EXISTS (SELECT TOP 1 1



           FROM   [dbo].[Report_Hierarchy])



    BEGIN



        INSERT INTO [dbo].[tmp_ms_xx_Report_Hierarchy]



        SELECT 



        FROM   [dbo].[Report_Hierarchy];



    END







DROP TABLE [dbo].[Report_Hierarchy];







EXECUTE sp_rename N'[dbo].[tmp_ms_xx_Report_Hierarchy]', N'Report_Hierarchy';

Orlanzo

VSTSDB 2010: msdb..sysdtspackages table not recongized

Wed, 18 Nov 2009 16:29:10 Z

Hi,

Keeping on evaluating VSTSDB 2010, I found out that sysdtspackages system table is not recognized even after having set a database reference to msdb.schema (C:\Program Files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Extensions\SqlServer\2008\DBSchemas).

This table is still supported with SQL Server 2008 (as a view), so is this a bug ?

Thanks.

Deployment when I have no connection to the target database

Thu, 19 Nov 2009 16:28:35 Z

The environment I am dealing with has a local development server (and each developer has their own SQL Server instance) but QA, Staging and Live are all physically and logically separate so we cannot connect to and deploy to them using VSTS, instead having to provide scripts for the operators to run against the target machines.

I'm sure many of you are faced with similar architectures, so how can I best make use of the features of Database Pro/GDR, while maintaining a sensible source-controlled environment for the development team which captures changes as easily as possible?

Thanks,

Dave.

DSL and VSTSDB integration - Question 1: How do I get my script item to appear in a database project?

Fri, 13 Nov 2009 22:41:53 Z

<installerDefinition xmlns="http://schemas.microsoft.com/VisualStudio/2005/DslTools/InstallerDefinitionModel" 
  productUrl="http://redox.com"
  defaultDirectoryName="DataWarehouseDesigner"
  productVersion="1.0.0"
  requiresCSharp="false"
  requiresVisualBasic="false"
  productCode="4cb674c0-cda7-420a-98bc-3e165563e67a"
  upgradeCode="282a4f0c-e8fb-403c-9fcb-9b693ef15546"
  localeId="1033">
	<dslPackage name="DataWarehouseDesigner" project="DslPackage" assemblyPath="Redox.DataWarehouseDesigner.DslPackage.dll" registryRoot="SOFTWARE\Microsoft\VisualStudio\9.0">
		<fileExtensions>
			<fileExtension name="redoxdw" extension="redoxdw" descriptionKey="FileDescription" hasIcon="true" iconId="0"/>
		</fileExtensions>
		<supportingAssemblies>
			<supportingAssembly name="Dsl" project="Dsl" assemblyPath="Redox.DataWarehouseDesigner.Dsl.dll"/>
		</supportingAssemblies>
	</dslPackage>
	<licenseAgreement filePath="Files\EULA.rtf" isEmbedded="true" />
	<supportingFiles>
		<supportingFile name="Readme" filePath="Files\Readme.htm" installShortcut="false" shortcutIconPath="Resources\ReadmeShortcut.ico" openAfterInstall="true" />
	</supportingFiles>
	<vsItemTemplates>
		<vsItemTemplate localeId="1033" targetDirectories="CSharp" templatePath="Files\Data Warehouse helper.zip"/>
		<vsItemTemplate localeId="1033" targetDirectories="VisualBasic" templatePath="Files\Data Warehouse helper.zip"/>
	</vsItemTemplates>
	<dslSchemas>
		<dslSchema project="Dsl" filePath="GeneratedCode\DataWarehouseDesignerSchema.xsd"/>
	</dslSchemas>
</installerDefinition>

DSL and VSTSDB integration - Question 2: How should i generate a script item in a database project?

Fri, 13 Nov 2009 23:04:29 Z

I have written a Domain Specific Language model that creates a large quantity of database scripts. This works nicely in the expoerimental hive or in a CSharp or VB project but not in a database project. Do VSTSDB projects heavily subclass the VSProject and do their own thing?

I am using Visual Studio's templating host to create sql script files much as one would do manually in a database project. I create folders and files in the project but when the code adds the file into the project it adds the file in the root of the project. What can I do to fix my code?

You can see the project heirarchy i want to create and i think you can envision the scripting in each folder. The problem happens when the code executes:

ContainingProject.ProjectItems.AddFromFile(filePath);

What is a better piece of code in the context of a database project? Must I use the script object model through ISql90Schema or is there a way to do this in a way that leverages the template process I have already created?

Thanks,
Ron

 
 	private DTE Dte { get; set; } 
	private Project ContainingProject { get; set; }  
	private ITextTemplating TemplatingService { get; set; }  
	private ModelingDocData CurrentDocData { get; set; } 
	private OutputWindowPane Pane { get; set; }...

	 // If the generated code directory doesn't exist then create it
            SrcTablesDir = CreateDirectory(ContainingProject.FullName, @"Schema Objects\Schemas\Src\Tables");
            SrcProcDir = CreateDirectory(ContainingProject.FullName, @"Schema Objects\Schemas\Src\Stored Procedures");
            DwTablesDir = CreateDirectory(ContainingProject.FullName, @"Schema Objects\Schemas\DW\Tables");
            DwKeysDir = CreateDirectory(ContainingProject.FullName, @"Schema Objects\Schemas\DW\Tables\Keys");
            DimViewDir = CreateDirectory(ContainingProject.FullName, @"Schema Objects\Schemas\Dim\Views");
            FactViewDir = CreateDirectory(ContainingProject.FullName, @"Schema Objects\Schemas\Fact\Views");
            EtlTablesDir = CreateDirectory(ContainingProject.FullName, @"Schema Objects\Schemas\ETL\Tables");
            EtlKeysDir = CreateDirectory(ContainingProject.FullName, @"Schema Objects\Schemas\ETL\Tables\Keys");
            EtlProcDir = CreateDirectory(ContainingProject.FullName, @"Schema Objects\Schemas\ETL\Stored Procedures");
...
        private string CreateDirectory(string projectFullName, string subDir)
        {
            string codeDir = Path.Combine(Path.GetDirectoryName(projectFullName), subDir);
            if (!Directory.Exists(codeDir))
                Directory.CreateDirectory(codeDir);
            if (!Directory.Exists(codeDir))
                throw new InvalidOperationException("Failed to create output directory: " + codeDir);
            return codeDir;
        }

        private void OutputDefinition(string specificTemplate,
            string fileDir, string fileName, string fileExtension)
        {
            Pane.OutputString("Transforming template creating " + fileName + fileExtension + " ... ");
            string output = TemplatingService.ProcessTemplate("", specificTemplate, null, null);
            string filePath = Path.Combine(fileDir, fileName + fileExtension);
            
            using (StreamWriter writer = new StreamWriter(filePath))
            {
                writer.Write(output);
                writer.Flush();
            }
            // If this is a new file then add it to the project
            try
            {
                ContainingProject.ProjectItems.AddFromFile(filePath);
            }
            catch // Ignore add failures
            {
            }
            if (output.StartsWith("Error"))
                Pane.OutputString("failed.\r\n");
            else
                Pane.OutputString("succeeded.\r\n");
        }

TSD03006 error

Thu, 08 Jan 2009 18:59:19 Z

On GDR, getting TSD03006 errors (which is now an error not a warning, so can't be ignored) for example on following query on AssessorAccountID field. Error says it's ambiguous. Did not get this error on previous version and view runs fine.

CREATE VIEW DH_AssessmentsAppealsDetail_0000
AS
SELECT
'0000'                        ReportingYear,
ae.CompanyID                CompanyID,
ae.StateCode                StateCode,
ae.PropertyTypeCode            PropertyTypeCode,
ae.AssessorID                AssessorID,
f.IsCentrallyAssessed        IsCentrallyAssessed,
ae.ParcelID                    ParcelID,
p.ParcelNumber                ParcelNumber,
p.InactiveDate                ParcelInactiveDate,
ae.AssessorAccountID        AssessorAccountID,
aa.AssessorAccountNumber    AssessorAccountNumber,
aa.InactiveDate                AssessorAccountInactiveDate,
COALESCE(tea.EffectiveDate,tes.EffectiveDate)
                            AssessmentExpectedDate,
COALESCE(trp.EffectiveDate,
         traa.EffectiveDate,
         tra.EffectiveDate,
        trs.EffectiveDate)
                            AssessmentReceivedDate,

fyym1.GroupTotal            FinalYYYYMinus1Value,
ryy.GroupTotal                RenderedYYYYValue,
ayy.GroupTotal                AssessorsYYYYValue,
fyy.GroupTotal                FinalYYYYValue,
ap.DueDate                    AppealDueDate,
ap.FiledDate                AppealFiledDate,
aps.AppealStatusDescription    AppealStatusDescription,
0                            TaxAtIssue,
0                            TaxSavings,
0                            NetSavings,
e.EnterpriseID                EnterpriseID,
e.EnterpriseName            EnterpriseName,
c.CompanyCode                CompanyCode,
c.CompanyName                CompanyName,
sy.NotesToAssessor            StateYear_NotesToAssessor,
sy.OtherNotes                StateYear_OtherNotes,
a.Title                        Assessor_Title,
a.Notes                        Assessor_Notes,
a.NotesToAssessor            Assessor_NotesToAssessor

FROM (SELECT
      CompanyID,
      StateCode,
      PropertyTypeCode,
      AssessorID,
      ParcelID,
      AssessorAccountID,
      MAX(FilingAccountID) FilingAccountID,
      SUM(GroupTotal)        GroupTotal
      FROM [$(PROP_YYYY)].dbo.AssessmentsExpected_Count     WITH (NOEXPAND)
      GROUP BY CompanyID,StateCode,PropertyTypeCode,AssessorID,ParcelID,AssessorAccountID
      ) ae
LEFT JOIN FilingAccount f ON f.FilingAccountID = ae.FilingAccountID
LEFT JOIN AssessorAccount aa ON aa.AssessorAccountID = ae.AssessorAccountID AND ae.ParcelID = 0
LEFT JOIN Parcel p ON p.ParcelID = ae.ParcelID AND ae.ParcelID <> 0

LEFT JOIN CalendarEvent tea
       ON tea.CalendarEventTypeID = 7
      AND tea.ReportingYear = 0000
      AND tea.StateCode = ae.StateCode
      AND tea.CompanyID = ae.CompanyID
      AND tea.PropertyTypeCode = ae.PropertyTypeCode
      AND tea.JurisdictionType = 2 AND tea.AssessorID = ae.AssessorID

LEFT JOIN CalendarEvent tes
       ON tes.CalendarEventTypeID = 7
      AND tes.ReportingYear = 0000
      AND tes.StateCode = ae.StateCode
      AND tes.CompanyID = ae.CompanyID
      AND tes.PropertyTypeCode = ae.PropertyTypeCode
      AND tes.JurisdictionType = 1

LEFT JOIN CalendarEvent traa
       ON traa.CalendarEventTypeID = 8
      AND traa.ReportingYear = 0000
      AND traa.StateCode = ae.StateCode
      AND traa.CompanyID = ae.CompanyID
      AND traa.PropertyTypeCode = ae.PropertyTypeCode
      AND traa.JurisdictionType = 2 AND traa.AssessorAccountID = ae.AssessorAccountID
      AND ae.ParcelID = 0

LEFT JOIN CalendarEvent trp
       ON trp.CalendarEventTypeID = 8
      AND trp.ReportingYear = 0000
      AND trp.StateCode = ae.StateCode
      AND trp.CompanyID = ae.CompanyID
      AND trp.PropertyTypeCode = ae.PropertyTypeCode
      AND trp.JurisdictionType = 6 AND trp.ParcelID = ae.ParcelID
      AND ae.ParcelID <> 0

LEFT JOIN CalendarEvent tra
       ON tra.CalendarEventTypeID = 8
      AND tra.ReportingYear = 0000
      AND tra.StateCode = ae.StateCode
      AND tra.CompanyID = ae.CompanyID
      AND tra.PropertyTypeCode = ae.PropertyTypeCode
      AND tra.JurisdictionType = 2 AND tra.AssessorID = ae.AssessorID

LEFT JOIN CalendarEvent trs
       ON trs.CalendarEventTypeID = 8
      AND trs.ReportingYear = 0000
      AND trs.StateCode = ae.StateCode
      AND trs.CompanyID = ae.CompanyID
      AND trs.PropertyTypeCode = ae.PropertyTypeCode
      AND trs.JurisdictionType = 1

LEFT JOIN (SELECT CompanyID,StateCode,AssessorID,AssessorAccountID,ParcelID, SUM(GroupTotal) GroupTotal
           FROM DH_Final_Value
           WHERE ReportingYear = 0000 - 1
           GROUP BY CompanyID,StateCode,AssessorID,AssessorAccountID,ParcelID
           ) fyym1
           ON fyym1.CompanyID = ae.CompanyID
          AND fyym1.StateCode = ae.StateCode
          AND fyym1.AssessorID = ae.AssessorID
          AND fyym1.AssessorAccountID = ae.AssessorAccountID
          AND fyym1.ParcelID = ae.ParcelID

LEFT JOIN (SELECT CompanyID,StateCode,AssessorID,AssessorAccountID,ParcelID, SUM(GroupTotal) GroupTotal
           FROM DH_AssessmentsRendered_Value
           WHERE ReportingYear = 0000
           GROUP BY CompanyID,StateCode,AssessorID,AssessorAccountID,ParcelID
           ) ryy
           ON ryy.CompanyID = ae.CompanyID
          AND ryy.StateCode = ae.StateCode
          AND ryy.AssessorID = ae.AssessorID
          AND ryy.AssessorAccountID = ae.AssessorAccountID
          AND ryy.ParcelID = ae.ParcelID

LEFT JOIN (SELECT CompanyID,StateCode,AssessorID,AssessorAccountID,ParcelID, SUM(GroupTotal) GroupTotal
           FROM DH_Assessors_Value
           WHERE ReportingYear = 0000
           GROUP BY CompanyID,StateCode,AssessorID,AssessorAccountID,ParcelID
           ) ayy
           ON ayy.CompanyID = ae.CompanyID
          AND ayy.StateCode = ae.StateCode
          AND ayy.AssessorID = ae.AssessorID
          AND ayy.AssessorAccountID = ae.AssessorAccountID
          AND ayy.ParcelID = ae.ParcelID

LEFT JOIN (SELECT CompanyID,StateCode,AssessorID,AssessorAccountID,ParcelID, SUM(GroupTotal) GroupTotal
           FROM DH_Final_Value
           WHERE ReportingYear = 0000
           GROUP BY CompanyID,StateCode,AssessorID,AssessorAccountID,ParcelID
           ) fyy
           ON fyy.CompanyID = ae.CompanyID
          AND fyy.StateCode = ae.StateCode
          AND fyy.AssessorID = ae.AssessorID
          AND fyy.AssessorAccountID = ae.AssessorAccountID
          AND fyy.ParcelID = ae.ParcelID

LEFT JOIN Appeal ap
       ON ap.ReportingYear = 0000
      AND ap.CompanyID = ae.CompanyID
      AND ((ap.ParcelID = ae.ParcelID AND ap.ParcelID > 0)
           OR (ap.AssessorAccountID = ae.AssessorAccountID AND ap.AssessorAccountID > 0))

LEFT JOIN AppealStatus aps
       ON aps.AppealStatusID = (SELECT TOP 1 AppealStatusID
                                    FROM AppealDetail apd
                                   WHERE apd.AppealID = ap.AppealID
                                ORDER BY apd.AppealDetailID DESC)

LEFT JOIN Company c ON c.CompanyID = ae.CompanyID
LEFT JOIN Enterprise e ON e.EnterpriseID = c.EnterpriseID
LEFT JOIN StateYear sy ON sy.StateCode = ae.StateCode AND sy.ReportingYear = 0000
LEFT JOIN Assessor a ON a.AssessorID = ae.AssessorID

Possible bug in pre- and post-deployment scripting?

Wed, 04 Nov 2009 15:57:34 Z

Hi folks, I came a cross a problem recently with our post-deployment scripting and was wondering if anyone else had encountered it or even if it was a known bug in VSDB?

I'm writing our post-deployment scripts such that we insert our static data for certain tables into temp tables, then insert each line if it doesn't exist, like so:

DECLARE @RowVariable int;
DECLARE @MaxRowVariable int;

CREATE TABLE #TempTable(
RowNumber int NOT NULL,
PK_Value int NOT NULL,
Identifier nvarchar(75) NOT NULL,
Text_Value nvarchar(2000) NOT NULL
);

WITH TableContent (
PK_Value,
Identifier,
Text_Value
)
AS(
SELECT 1, N'Identifier_1', N'I am some text'
UNION ALL
SELECT 2, N'Identifier_2', N'I am some more text'
)
INSERT INTO #TempTable
SELECT
ROW_NUMBER() OVER(ORDER BY PK_Value)
PK_Value,
Identifier,
Text_Value
FROM TableContent;

SET @RowVariable = 1;

SET @MaxRowVariable = (SELECT MAX(RowNumber) FROM #TempTable);

WHILE @RowVariable <=@MaxRowVariable
BEGIN
	IF NOT EXISTS(
			SELECT [Row].RowNumber
			FROM dbo.RealTable
				CROSS APPLY(
					SELECT RowNumber
					FROM #TempTable
					WHERE #TempTable.RowNumber = @RowVariable
					AND #TempTable.PK_Value = dbo.RealTable.PK_Value) AS [Row])
		BEGIN;
			INSERT INTO dbo.RealTable (
				PK_Value,
				Identifier,
				Text_Value)
			SELECT
				PK_Value,
				Identifier,
				Text_Value
			FROM #TempTable
			WHERE RowNumber = @RowVariable;
		END;
	SET @RowVariable = @RowVariable + 1;
END;

DROP TABLE #TempTable;

However I came across another "Fatal Parser Error" when one of the Text_Values contained a string like "The following characters are not permitted in this field: a-z A-Z 0-9 !£$()[]{};'@~,.`¬".

It appears that the substring "$()" is being interpreted as a SQLCMD variable, even though it is being declared as a nvarchar string like so:

N'The following characters are not permitted in this field: a-z A-Z 0-9 !£$()[]{};''@~,.`¬'

I worked around it by splitting the $ and ( like so:

N'The following characters are permitted in this field: a-z A-Z 0-9 !£$' + N'()[]{};''@~,.`¬'

But surely the SQLCMD parsing should be bright enough to ignore the $() stuff if it's been placed inside a string? The Visual Studio UI changes the string's colour-coding to reflect that it is a string, so why doesn't the parser understand the difference??

Thanks, Rob

Updating a stored procedure

Mon, 16 Oct 2006 14:47:07 Z

I think I must be doing something stupid here. I have a stored procedure defined that already exists in my test database. I make a quick change to the procedure in Visual Studio and then I want to test it on my test environment. I therefore right-click the code and select 'Execute SQL', but am told the procedure already exists. If I add some code before the CREATE PROCEDURE line to check if the procedure exist and delete it then I get parse errors popping up in the IDE. I can't change the CREATE to a MODIFY as this causes parse errors.

I don't see an option to 'overwrite' stored procedures when executing them, and I don't want to have to use the Compare Schema option to create a full release script whenever I make a small procedure change, and selecting the procedure in the server exporer and deleting it each time is just annoying.

How can I add some code or similar to drop the procedure when I want to run my script?

Cannot invoke Regular Expression Builder for Data Generation

Wed, 11 Nov 2009 22:09:10 Z

Running VSTS 2008 Database Edition GDR 2. I have also downloaded and installed the Power Tools.

I have a Database Project that I'm trying to launch and use the RegEx Builder - in a Data Generation Plan I choose a column on a table, set the Generator to "Regular Expression". I cannot invoke the Builder (from Power Tools) either by:
a) there is no "..." browse button in the column to the right of Generator Output
b) the Data>Data Generator>Regular Expression Builder menu option does nothing

Lastly, how do I know if I really have the Power Tools (correctly) installed?

GDR CTP 17 - Error - The model already has an element that has the same name AutoCreatedLocal.

Tue, 07 Oct 2008 05:42:37 Z

Greetings,

When creating a database reference to Master in a project that was created from importing the schema of an existing database I get the following error:-

Error 16 The model already has an element that has the same name AutoCreatedLocal.

Although I can easily delete the Route definintion from my project to make the error go away I can't help wondering if this is the correct behaviour and whether the route difinintions should really be in the master.dbschema file?

Regards,

Neale NOON

Refactor Error: Failed to Check out files error when trying to rename via Refactor command

Mon, 16 Nov 2009 22:11:17 Z

Getting an error (no error number) when using the Refactor command to rename an object. Funny thing is, it was working this morning. My computer shut down unexpectedly and since then I have not been able to do a rename refactor because of this error.

STR:
1. Go to Schema View and right click object to change
2. Select Refactor > Rename
3. Edit name and select all 4 options (preview, assume default schema, update references, generate refactoring log)
4. click OK
5. In the Preview Window, select Apply
6. Error appears --> Refactor Error Failed to check out files.

I can individually check out each of the items that are going to be changed for the rename. The error only occurs when doing a Refactor Rename. Tried doing this with no options (per step 3 above) and it still happens.

Did a google and forum search and there is no instance of this particular Refactoring Error.

Don't know where to go from here...

Still a bit slow opening projects

Thu, 23 Nov 2006 09:36:58 Z

Hello,
As mentioned before, the import schema process is extremely fast now, which was pleasantly surprising, however opening an existing project with lots (>5,000) of objects still takes a long time. This is also without using source control and so I'm guessing it would take up to 10 minutes to open if I put my project into TFS / VSS.

Is there any way to speed up the opening process, e.g. by disabling particular schema checks when first opening the project?

Database deployment for beginners

Tue, 17 Nov 2009 01:03:14 Z

I am trying to get my head around deployment configurations. We are planning to have each ASP.NET developer have a local copy of the db for development. The biggest problem is that we have our production versions in source control, and the users are different in prod than on the local databases. I have been trying to find a tutorial or overview for how to accomplish this, but my long running search has so far been somewhat vain.

Does anyone have suggestions on reading material for this particular subject?

Microsoft Visual Studio Team System 2008 Database Edition GDR R2 (Available)

Wed, 22 Apr 2009 17:45:28 Z

The RTM version of Microsoft Visual Studio Team System 2008 Database Edition GDR R2 is now available for customers to download. The GDR R2 version is the most current release of Database Edition that adds support for SQL Server 2008 and numerous new features. See the GDR R2 download page for more details. The GDR R2 is available to all customers through the same Microsoft Download channel customers acquired the GDR from. The previous version of the GDR is no longer available.

We appreciate customers who participated in service release candidate evaluation program and confirmed fixes in that limited release.

Version Upgrade

Customers currently using the [9.1.31124.01] version of GDR should upgrade to GDR R2 version [9.1.40413.00] to get the latest set of fixes available for the product.
Customers currently using Microsoft Visual Studio Team System 2008 Database Edition [9.0.30729.1 SP] should upgrade to GDR R2. It is not necessary to install the GDR prior to installing GDR R2. A project upgrade for projects previously created with the GDR is required when upgrading to GDR R2, but the upgraded project will stay compatible with the GDR. A project upgrade for projects previously created with the Microsoft Visual Studio Team System 2008 Database Edition [9.0.30729.1 SP] is required when upgrading to GDR R2 resulting in upgraded projects that are only compatible with GDR and GDR R2.
Customers who have installed the Visual Studio 2008 Database Edition GDR v2 RC1 will need to uninstall the GDR RC version [9.1.40320.00] to successfully install the GDR R2. No project upgrade will be necessary for these customers.

Read more about this release here: Microsoft Visual Studio Team System 2008 Database Edition GDR R2

Barclay Hill Program Manager VSTS: DB Team (DataDude, DBPro, Database Edition) Please mark the responses as answer if it resolves your question.