2 Bugs to report

Topics: Database Publishing Wizard
Jan 27, 2007 at 8:49 AM
Great idea, but it wont work for me because of bug num 2..

1) The GUI wont allow a blank password for SQL authentication (not a huge deal because you can use the connection string instead)

2) If you have a view that is dependant on another view, then the script fails because it does not create the views in correct order. For example, View1 might join onto view2, in which case view2 needs to be scripted first... This is not supported in the tool and so we can't use it! I hope you plan to work on this sometime!

Thanks!
Jan 29, 2007 at 1:41 AM
Hi Mattwoberts

Thanks for reporting the issues to us. Can you provide us a sample script that can be used to create the views for which you are seeing bug 2? We have not been able repro this issue on your machines as yet. I have pasted one of the sample scripts that we tried below.

Thanks
Himanshu

create table Table1
(ID int primary key,
name nchar(256))
GO

Create table Table2
(ID int primary key,
address nchar(256)
)
GO

Create View View1
as select ID, name from Table1
GO

Create View View2
as select ID, address from Table2
GO

create View View3
as select v1.ID, v1.name, v2.address from View1 as V1 join View2 as v2 on v1.ID = v2.ID
GO
Jan 29, 2007 at 7:58 AM
Hi,

We have a View called "GroupAllSites", which queries from the tables. We have another view, called "SitesAppusersAccess", which joins onto GroupAllSites. WHen I look at the SQL generated, I see that "SitesAppUsersAccess" is scripted before "Group_AlLSites", which means that this will fail.

Here's the output from the wizard which shows the order of view generation:-

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'Sites_AppUsers_Access') AND OBJECTPROPERTY(id, N'IsView') = 1)
EXEC dbo.sp_executesql @statement = N'CREATE VIEW Sites_AppUsers_Access AS
SELECT <Rest of select statement truncated>
UNION
SELECT X,Y,Z
FROM AppUser au Join GroupAllSites g on au.GroupID = g.Group_ID
JOIN Site s on s.ID = g.Site_ID
WHERE au.User_Type = ''A''
UNION
SELECT <Rest of select statement truncated>
'
GO

/****** Object: View dbo.Group_AllSites Script Date: 01/29/2007 08:42:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'Group_AllSites') AND OBJECTPROPERTY(id, N'IsView') = 1)
EXEC dbo.sp_executesql @statement = N'CREATE VIEW Group_AllSites
AS
Select <Rest of select statement truncated>
'
GO
Jan 30, 2007 at 12:12 AM
Hi

Do you know if the view Group_AllSites was dropped and re-created after the view Sites_AppUsers__Access had been created?

We tried creating views similar to what you have, however, we were not able to repro the bug. If possible can you share the T-Sql statements that you used to create these views and the tables required for them? You can post the T-Sql statements here or you can mail them to me at himanshu@microsoft.com.


Thanks
Himanshu
Jan 30, 2007 at 7:34 AM
Hi,

I'll take a look and try to email you today.

Matt.
Jan 31, 2007 at 9:28 AM
Hi again,

Interestingly, I tried to generate the tables and views to send you to recreate the problem. I created a new database based on these tables and views, and then tried the wizard on this new database, and the SQL poroduced was OK - the order of the view generation was the right way round.

So, it must be something else in the database that is interfering with the code generation. I'm not sure what to do next...

Jan 31, 2007 at 7:44 PM
Edited Jan 31, 2007 at 7:51 PM

mattwoberts wrote:
Hi again,

Interestingly, I tried to generate the tables and views to send you to recreate the problem. I created a new database based on these tables and views, and then tried the wizard on this new database, and the SQL poroduced was OK - the order of the view generation was the right way round.

So, it must be something else in the database that is interfering with the code generation. I'm not sure what to do next...




Hi Matt -

The view ordering issue you had was a problem in builds of DPW prior to 1.0, but with the latest versions those should be fixed. All objects are ordered using data from sys.sql_dependencies (See http://msdn2.microsoft.com/en-us/library/ms345449.aspx). It is possible that if you created and dropped the views then this information could have been lost e.g.:

CREATE VIEW Group_AllSites AS ...
CREATE VIEW Sites_AppUsers_Access AS ...
DROP VIEW Group_AllSites
CREATE VIEW Group_AllSites

Your bet bet for your existing database is to drop the Sites_AppUsers_Access view and then re-create it, which should reestablish the dependency information.

In the future, you can use the WITH SCHEMABINDING option when you create views so that you will not be able to drop the view until you clear up dependencies. This should prevent things like this from arising in the future..

Let us know if you run into any more issues.

alex
Jan 31, 2007 at 9:50 PM
Hi Alex,

Thanks - you're right, re recently we-wrote the Group_AllSites view, so we will have dropped it and recreated it! Thanks for the help, I'll try to recreate the other view and see what happens.