Microsoft KB Archive/936612

From BetaArchive Wiki

Article ID: 936612

Article Last Modified on 8/9/2007



APPLIES TO

  • Microsoft Visual Studio 2005 Team Edition for Database Professionals



INTRODUCTION

Service Release 1 (SR1) for Microsoft Visual Studio 2005 Team Edition for Database Professionals is available. This update requires Visual Studio 2005 Professional with Service Pack 1 (or a later version).

MORE INFORMATION

The following issues are corrected in SR1. These issues have been found through customer and partner feedback.

  • Cross-database references


Support is improved so that you can reference objects within different databases by using database project references or by referencing a database metafile (.dbmeta). This support reduces or eliminates the cross-database reference warnings within a database project.

  • Improved file support within SQL Server file groups


You can define files within file groups as database project properties instead of having to create files and file groups within the pre-deployment storage script.

  • Variables


A Variables page is added to the database properties. This new page enables you to define setvar variables for use in the deployment scripts.

Additionally, SR1 supports the latest service pack release from Microsoft SQL Server 2005. Currently, the latest service pack is Service Pack 2 (SP2). SR1 also supports the Windows Vista operating system.

System requirements

Supported operating systems

  • Microsoft Windows 2000 Professional Service Pack 4 (SP4)
  • Microsoft Windows 2000 Server SP4
  • Microsoft Windows 2000 Advanced Server SP4
  • Microsoft Windows 2000 Datacenter Server SP4
  • Microsoft Windows XP Professional x64 Edition (WOW)
  • Microsoft Windows XP Professional Service Pack 2 (SP2)
  • Microsoft Windows XP Home Edition SP2
  • Microsoft Windows XP Media Center Edition 2002 SP2
  • Microsoft Windows XP Media Center Edition 2004 SP2
  • Microsoft Windows XP Media Center Edition 2005
  • Microsoft Windows XP Tablet PC Edition SP2
  • Microsoft Windows Server 2003 Standard Edition Service Pack 1 (SP1)
  • Microsoft Windows Server 2003 Enterprise Edition SP1
  • Microsoft Windows Server 2003 Datacenter Edition SP1
  • Microsoft Windows Server 2003 Web Edition SP1
  • Microsoft Windows Server 2003 Standard x64 Edition SP1 (WOW)
  • Microsoft Windows Server 2003 Enterprise x64 Edition SP1 (WOW)
  • Microsoft Windows Server 2003 Datacenter x64 Edition SP1 (WOW)
  • Microsoft Windows Server 2003 R2 Standard Edition
  • Microsoft Windows Server 2003 R2 Standard x64 Edition (WOW)
  • Microsoft Windows Server 2003 R2 Enterprise Edition
  • Microsoft Windows Server 2003 R2 Enterprise x64 Edition (WOW)
  • Microsoft Windows Server 2003 R2 Datacenter Edition
  • Microsoft Windows Server 2003 R2 Datacenter x64 Edition (WOW)
  • Windows Vista

Note Visual Studio 2005 on the Intel Itanium (IA-64) is not supported.

Other system requirements

Requirements vary for different combinations of features within Microsoft Visual Studio 2005 Team Edition for Database Professionals. To install Visual Studio 2005 Team Edition for Database Professionals, we recommend the following:

  • A 2.0 GHz or faster processor
  • 512 MB of RAM or more (1 gigabyte (GB) or more recommended)
  • 3 GB of available hard disk space
  • A DVD drive
  • A 1024x768 or higher-resolution display that has 256 colors
  • A keyboard and a Microsoft mouse or compatible pointing device
  • An instance of Microsoft SQL Server 2005 (Developer Edition, Enterprise Edition, Standard Edition, Express Edition, or Evaluation Edition)

Known issues

General

Statement restrictions in schema object definitions

You cannot use the following statements in the definition file for the specified schema objects:

  • Check constraints – ALTER TABLE [ WITH { CHECK | NOCHECK } ] {CHECK | NOCHECK} CONSTRAINT
  • Foreign keys – ALTER TABLE [ WITH {CHECK | NOCHECK} ] {CHECK | NOCHECK} CONSTRAINT
  • DML triggers – ALTER TABLE {ENABLE | DISABLE} TRIGGER TriggerName
  • DML triggers – DISABLE TRIGGER { [Schema.]TriggerName ON ObjectName
  • Database triggers – DISABLE TRIGGER { [Schema.]TriggerName ON DATABASE
  • All Server triggers – DISABLE TRIGGER { [Schema.]TriggerName ON ALL SERVER
  • Tables – ALTER TABLE { ENABLE | DISABLE } TRIGGER ALL
  • Indexes – ALTER INDEX DISABLE
  • Full-text indexes – ALTER FULLTEXT INDEX ON TableName {ENABLE | DISABLE}
  • Queues – ALTER QUEUE ObjectName WITH STATUS = { ON | OFF }

To resolve this issue, you must include these kinds of statements in a post-deployment script. For more information about post-deployment scripts, see the product documentation.

Warnings about ambiguous references in JOIN statements

You may receive a warning for a valid Transact-SQL statement that involves JOIN statements even if SQL Server would accept that valid statement. For example, you can create the following view definition:

CREATE VIEW [dbo].[View1]
AS 
    SELECT column_2 FROM 
    Table1 LEFT OUTER JOIN (SELECT column_1 FROM Table2 as T_T2) as B on 1 = 1
       LEFT OUTER JOIN (SELECT column_1 FROM Table3 as T_T3) as C on 1 = 1

However, a warning appears when the “SELECT SELECT column_1 FROM Table3 as T_T3” query is executed.

To resolve this issue, fully qualify the reference. For example, you can use the following statements to replace the previous statements:

CREATE VIEW [dbo].[View1]
AS 
    SELECT column_2 FROM 
    Table1 LEFT OUTER JOIN (SELECT column_1 FROM Table2 as T_T2) as B on 1 = 1
       LEFT OUTER JOIN (SELECT T_T3.column_1 FROM Table3 as T_T3) as C on 1 = 1
Vardecimal storage format

Visual Studio 2005 Team Edition for Database Professionals does not directly support the vardecimal storage format. This storage format is implemented in SQL Server 2005 SP2. If you import a schema from a database that enables the vardecimal storage format for the database and for one or more tables, the setting that enables the vardecimal storage format is ignored. No errors occur. However, no statements are added to the ScriptsIgnoredOnImport.sql file. You can build and deploy the database project. However, the build script does not create the vardecimal storage format in the database or in any tables.

You also encounter issues if you import a script that contains the following statements:

-- enable vardecimal storage format for database
exec sp_db_vardecimal_storage_format 'DatabaseName', 'on'
-- enable vardecimal storage format on t1 in database
exec sys.sp_tableoption 'TableName', 'vardecimal storage format', 'on'

The statement for the storage format of the database is imported into the ScriptsIgnoredOnImport.sql file. The statement of the table storage format is imported into the definition for the table. You cannot deploy the database project because the statement that enables vardecimal storage format of the database was not executed. This causes the statement of the table storage format to fail.

To resolve the issue when you import a schema from a database, add the exec sp_db_vardecimal_storage_format statements to the pre-deployment script. Then, add the exec sys.sp_tableoption statements to the tables in which you want to use the vardecimal storage format.

To resolve the issue when you import a script, add the exec sp_db_vardecimal_storage_format statements to the pre-deployment script.

Missing dependencies when you use the “SELECT * INTO” statement to populate a temporary table

You may receive a warning for a valid Transact-SQL statement that involves a select * statement to populate a temporary table even if SQL Server would accept that valid statement. For example, you may execute the following statements:

CREATE TABLE [dbo].[userprofile] (
    [user_id] [INT] NOT NULL,
    [keyname] [VARCHAR](30) NOT NULL,
    [value] [VARCHAR](50) NOT NULL,
)

CREATE PROCEDURE [dbo].[testtemp]
AS
BEGIN
    SELECT * INTO #up FROM [dbo].[userprofile]

    UPDATE #up 
        SET value='value'
    WHERE keyname='key'
END;

However, the following warnings may appear:

Warning 98 TSD3028: The following dependencies are missing from your database project: [Variable|Column] dbo.#up.value. Your database application might fail at runtime when [Procedure] dbo.testtemp is executed.

Warning 99 TSD3028: The following dependencies are missing from your database project: [Variable|Column] dbo.#up.keyname. Your database application might fail at runtime when [Procedure] dbo.testtemp is executed.

To resolve this issue, fully qualify the reference. For example, you can use the following statements to replace the previous statements:

CREATE PROCEDURE [dbo].[testtemp]
AS
BEGIN
    SELECT user_id, keyname, value INTO #up FROM [dbo].[userprofile]

    UPDATE #up 
        SET value='value'
    WHERE keyname='key'
END;

If you execute a query in the T-SQL editor in the Japanese version of Visual Studio Team Edition for Database Professionals on Windows 2000, an incorrect font is used to display the Results pane and the Client Statistics pane. The results are rendered in a font that cannot correctly display Japanese characters. To resolve this issue, follow these steps:

  1. On the Tools menu, click Options.
  2. In the Options dialog box, expand the Environment node, and then click Fonts and Colors.
  3. In the Show settings for drop-down list box, click T-SQL Editor Results Pane.
  4. Change the value for Font (bold type indicates fixed width fonts) from Automatic to a font that supports Japanese characters.


The Results pane and the Client Statistics pane should now display correctly.

Data Compare and Schema Compare

Wrong decimal separator in Data Compare

Data Compare always uses a dot (.) as the decimal separator for columns that are the decimal type or the money type, even for the locales that use a different decimal separator character such as a comma (,). Columns of other data types, such as real and double, use the correct decimal separator.

There is no workaround for this issue now.

Errors when you update filegroups and files by using Schema Compare

An error may occur when you compare files and file groups to update a schema. You may receive an error message when you try to update the target database from Schema Compare if you use a SETVAR variable in the physical path and in the name of a file. This issue occurs because Schema Compare cannot handle the variable to the value that you defined on the Variables tab of the database project properties.

To resolve this issue, follow these steps:

  1. On the Data menu, point to Export To, and then click Editor.
  2. In the Transact-SQL editor, add a statement at the top of the update script to explicitly declare the SETVAR variable that you used in the definition of the file path.
  3. On the Data menu, point to T-SQL Editor, and then click Execute SQL.

How to obtain the SR1 update

To obtain Service Release 1 (SR1) for Microsoft Visual Studio 2005 Team Edition for Database Professionals, visit the following Microsoft Web site:

Keywords: kbtshoot kbinfo KB936612