|
|
Posted by Stu on 07/06/06 14:08
Hey Jim,
There are a number of issues with your approach, but the primary
reason that you're having the problems you are is that you're using
Enterprise Manager (known by many as Enterprise MANGLER) to edit the
script of your views. EM is a good tool for administration, but you
should be using Query Analzer for editing.
In Query Analyzer, you can right-click on the view and select EDIT, and
it will show you the script of the view, which you can then ALTER to
get to the correct format you need.
As far as the other issues, I think Erland tapped into them:
1. Don't use SELECT * in production code (or at least don't do so
wihout commenting). There was a recent discussion in another group
about it that you may want to read.
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/cb35a91ea599e6c3/c2efe874da82b7ef?lnk=st&q=&rnum=17&hl=en#c2efe874da82b7ef
2. The TOP...ORDER BY embedded in a view is, at best, flaky. It's
also one of the behaviors that was changed in SQL 2005, so if you can
avoid using it, I'd recommend that you do so. The only way to ensure
an order to your results is to use ORDER BY in your final outer SELECT
statement (e.g., SELECT columnlist FROM dbo.CustomerListQueryAccounts
ORDER BY AccountName).
HTH,
Stu
Jim Devenish wrote:
> I have 3 views, two of which depend on the other:
>
> CREATE VIEW dbo.CustomerListQueryAccounts
> AS
> SELECT dbo.CustomerListQuery.*
> FROM dbo.CustomerListQuery
> WHERE (isProspect = 0)
>
> CREATE VIEW dbo.CustomerListQueryProspects
> AS
> SELECT dbo.CustomerListQuery.*
> FROM dbo.CustomerListQuery
> WHERE (isProspect = 1)
>
> which depend on:
> CREATE VIEW dbo.CustomerListQuery
> AS
> SELECT TOP 100 PERCENT
> dbo.Customers.*, CAST(dbo.Customers.CustomerID AS int) AS
> CustomerIDAsNumber,
>
> dbo.NumberOfJobsPerCustomer.CountOfJobID,
> dbo.NumberOfQuotesPerCustomer.CountOfQuoteID,
>
> dbo.NumberOfComplaintsPerCustomer.CountOfComplaintID,
> dbo.NumberOfNotesPerCustomer.CountOfCustomerNoteID,
>
> dbo.NumberOfEnquiriesPerCustomer.CountOfEnquiryID
> FROM dbo.Customers
>
> LEFT OUTER JOIN
> dbo.NumberOfJobsPerCustomer ON
> dbo.Customers.CustomerID = dbo.NumberOfJobsPerCustomer.CustomerID
> LEFT OUTER JOIN
> dbo.NumberOfQuotesPerCustomer ON
> dbo.Customers.CustomerID = dbo.NumberOfQuotesPerCustomer.CustomerID
> LEFT OUTER JOIN
> dbo.NumberOfNotesPerCustomer ON
> dbo.Customers.CustomerID = dbo.NumberOfNotesPerCustomer.CustomerID
> LEFT OUTER JOIN
> dbo.NumberOfComplaintsPerCustomer ON
> dbo.Customers.CustomerID = dbo.NumberOfComplaintsPerCustomer.CustomerID
>
> LEFT OUTER JOIN
> dbo.NumberOfEnquiriesPerCustomer ON
> dbo.Customers.CustomerID = dbo.NumberOfEnquiriesPerCustomer.CustomerID
>
> ORDER BY dbo.Customers.AccountName
>
> These work well but I have an alternative version of this latter one
> which has fewer columns. At present it is:
> CREATE VIEW dbo.CustomerListQueryShorter
> AS
> SELECT dbo.Customers.*, CAST(dbo.Customers.CustomerID AS int) AS
> CustomerIDAsNumber
> FROM dbo.Customers
>
> I now wish to make this one CustomerListQuery and so rename the
> existing one CustomerListQueryOriginal (just to get is out of the way)
> and change its first line, using the View's properties (in Enterprise
> Manager) to :
> CREATE VIEW dbo.CustomerListQueryOriginal
>
> I then rename CustomerListQueryShorter to CustomerListQuery and change
> its first line to
> CREATE VIEW dbo.CustomerListQuery
>
> Now when I 'Return all rows' of CustomerListQueryAccounts I get an
> error message:
> 'dbo.CustomerListQueryAccounts' has more column names specified than
> columns defined.
>
> If however, I go into the design and then select Run, I get the correct
> output reflecting the new version of CustomerListQuery. How do I get
> the 'Return all rows' output to show the same.
>
> It appears that the orginal version is still being used. How do I get
> the system to replace this ?
Navigation:
[Reply to this message]
|