Known Issues with Database Publishing Wizard 1.0
- Issues when scripting SQL Server 2005 databases to SQL Server 2000
- User-defined functions and stored procedures with 2005-specific T-SQL (PIVOT, CTE, APPLY, etc) will script without error, even though the resulting script will not work on SQL Server 2000.
- Tables with columns defined using 2005-only collations (IndicGeneral90CIAS, Japanese_90, etc) will script without error, even though the resulting script will not work on SQL Server 2000.
- User-defined functions containing the phrase "END;" at the end will script, even though this causes a syntax error on SQL Server 2000.
- Constraints other than PRIMARY KEY or UNIQUE placed on computed columns will script without error, even though the resulting script will not work on SQL Server 2000.
- User-defined functions and stored procedures specified explicitly with EXECUTE AS CALLER will script without error, even though the resulting script will not work on SQL Server 2000.
- Issues related to roles, users, and schemas
- When scripting a source database, Database Publishing Wizard converts any users the database contains into roles, and where appropriate, schemas. This behavior occurs because Database Publishing Wizard does not script logins.
- Roles owned by other roles or application roles (using the AUTHORIZATION option) may not work.
- User-defined data types will not be schema qualified when scripting to SQL Server 2000.
- If schema qualification is turned off in the Database Publishing Wizard options, this will not affect the T-SQL source of any user-defined functions or stored procedures.
- Issues when publishing directly to a shared hosting provider
- You may get errors from the Database Publishing Wizard that an object is too large to publish because of the maximum request length of the web service. Contact your shared hosting provider and ask for them to increase the maximum request length. Alternatively,
you can script the data to T-SQL and execute that.
- The web service will not publish data in situations where an individual row is larger than 2 GB.
- International issues
- Database Publishing Wizard explicitly defines collations for text columns on tables. If the collation of the source database does not match that of the target database, this can cause issues. Notably, creating temporary tables and comparing these temporary
tables to database tables will run into issues. To fix this, change the collation of one of your databases. You can change the collation of your database using the
ALTER DATABASE command. For more information on collations, visit
http://msdn2.microsoft.com/en-us/library/ms187582.aspx. You can check the collation of a database with
select DatabasePropertyEx(N'DATABASENAME', N'Collation')
- Other general issues
- The error "Property QuotedIdentifierStatus is not available" occurs due to a bug in the RTM version of SQL Server 2005 SQL Management Objects (SMO). Please update to the
SP1 version of SQL Server 2005 SMO.
- Precision on floats and doubles is not guaranteed beyond 17 digits of precision.
- Specifying both the "Initial Catalog" and "User Instance" in a connection string will cause a "File in use" error from SQL Server.
Hoster Specific Issues
- GoDaddy.com, 1and1.com, Aruba.it
- The "Query Analyzer" script execution window will issue an error if a "GOTO" is the first statement on a line. This can be worked around by simply removing the carriage return that precedes the "GOTO", placing the "GOTO"
on the same line as the statment that precedes it.