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.

Last edited Jan 19, 2007 at 12:08 AM by alexd, version 13

Comments

tobad Apr 21, 2010 at 1:00 PM 
Has the issue with the views being created before tables been resolved, I downloaded the publisher week ago and I am having the same problem.

base Oct 7, 2009 at 12:08 PM 
@HarryBob, I noticed the same. Always do a search and replace for 'END;' to 'END'. It is not only on views.
Great product by the way, if you read the known issues and act to it, it works very well from 2005 to 2000.

Harrybob Oct 7, 2009 at 4:13 AM 
has anyone else noticed that sometimes the script contains a semi colon at the end of a create view statement and sometimes it does not. This causes sql server 2000 to throw an error

vince144 May 18, 2009 at 9:19 PM 
open a command prompt from here C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2 as the sqlpubwiz is not in the path.

elkinsenv May 7, 2009 at 5:43 PM 
I can't use the sqlpubwiz script because my computer does not recognize this as an internal command at the c:\ prompt. Does anyone know what I am doing wrong?

hfrmobile Aug 19, 2008 at 12:23 PM 
"Known Issues with Database Publishing Wizard 1.0"?

Current version of SqlPubWiz.exe is 1.1.1.0 ....

Seems to work fine ...

cfguidry May 28, 2008 at 9:09 PM 
This utility would be a great solution, but it's not going to work for me as long as the views get created out of order, and logins don't get created at all. Is it being updated any longer? If not, does anyone know of a similar tool that can be used?

therod May 27, 2008 at 6:14 PM 
I had been using using it with success but then, all of a sudden it started giving me this error that I can't get past.

- Gathering list of objects to script (Success)
- Scripting objects (Error)
Messages
* Object reference not set to an instance of an object. (SqlPubWiz)
- Writing script to disk (Stopped)

I have no idea what is causing this.

parley Mar 25, 2008 at 7:25 PM 
This will not run under VISTA as it is looking for the database in the wrong location. It looks for the MSDBData.mdf in the C:Cocuments and Settings\(user)\local settings\application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\ when with VISTA it is in the c:\Users\Ley\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS.

Prasannapete Mar 13, 2008 at 11:01 AM 
Hi
when i generate scripts, say for example only stored procedure
why it is also generating the dependent objects??????
Is there any way to stop scripting the dependent objects

buckley Oct 1, 2007 at 9:23 AM 
Hi,

I am also experiencing the problem that views are created before tables being referenced in the view.

Is there a solution for this problem?

Kind Regards, Tom

SteveGutteridge Jun 1, 2007 at 2:35 PM 
Ditto. I have found that some Views reference Tables not yet created and it crashes (using v 1.1). You just have to go through it in a text editor and move all the create table code to come before the views are created. It's a real pain, but better than nothing which is what I had before! Also, the text file created comes out in unicode and you need to load into Windows Notepad and resave as ANSI encoding.

Also, some views that reference other views are created in the wrong order, leading to a similar problem.

Another one for you: In SQL 2005 Views you often get the code generated: "SELECT TOP (100) PERCENT". It seems SQL 2000 doesn't like the brackets around the 100. Just do a search and replace in the script file.

After doing all of the above I am please to report that my upload was succesful (with 19 tables and 12 views). In debugging this I edited the RunSQL.aspx file to output each separate command to the screen before it was executed, so I could see which ones failed.

LWNet Apr 9, 2007 at 7:28 PM 
Hvasishth,
I tried 1.1 today and had the same problem. A view was scripted that referenced a table that had not yet been created.
This would be a very useful tool if it worked properly.

tlyczko Feb 20, 2007 at 7:26 PM 
How about explaining how to deal with moving/synchronizing users/logins?? if your tool does not script users.

Nicolaas Jan 22, 2007 at 11:05 AM 
How about specifying the only some tables. i dont want to script and publish by 1million records log table etc. Perhas also specify a query of some sort to limit some of the data?
thanks

Hvasishth Jan 18, 2007 at 11:53 PM 
Nemoby/Rickiswright

We made changes in the product which should fix the issues that you are having with database containg views. Can you download the V1 release and try again?

Thanks
Himanshu

nemoby Jan 16, 2007 at 5:47 PM 
I am having similar issue to "rickiswright" where views are created before tables as are sprocs. I might add that it appears that these very same issues occur when scripting the db from within SQL Server Management Studio. I wonder if there is a common code base between the two?

sctemp Jan 4, 2007 at 8:13 PM 
How about adding a selection for which table you want to publish?

rickiswright Nov 28, 2006 at 11:04 PM 
The product works great until you try to run your script when the scripted db contained views. When you attempt to create your database on the remote server, the utility attempts to script the views before the tables are created when you run it against a server to populate it with your db.