managing SQL server 2000 servers with 2K5 management studio

For some reason when I try to use sql 2005 management studio to manage a sql server 2000 server and attempt to use activity monitor to view processes I get an error (see below) (BTW I love being able to copy the message text). I can perform this operation from enterprise manager without issue. Is this a problem with management studio?

TITLE: Microsoft SQL Server Management Studio

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Unable to execute requested command.

Unable to launch Activity Monitor. You may not have sufficient permissions. (ActivityMonitor)

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)

Could you please confirm that you can connect to the server otherwise and run a query (say select * from sysprocesses) and only activity monitor is not working.

What are the versions of Management Tools and SQL Server you have? What is the configuration? Are you doing it on the same machine or going over network? Was it this way from the beginning?

Boris.

|||That query worked fine. It was being accessed over a network. I was able to run querys on databases. However the server did have an issue with 1 database not being accessable. Now that the server issue has been corrected (the drive repaired). It seems to work fine from management studio. I am going to try to recreate the original server in a lab to see if this is some sort of bug in 2k5.|||

I do this all the time, but as the DBA I have the luxury of logging into the 2K box as 'sa'. I would guess the permissions issue is the root here... what account are you using to login? What roles/permissions does the account have?

More info could help us all help you.

|||

You can connect a profiler to your SQL Server 2000 instance and see what is going on when you double click on Activity Monitor in Management Studio. Since you are getting a timeout error and you had a suspect database, one of the queries did not return result in time and thus the message.

If you are able to reproduce the problem in your lab environment, please post the results here.

Thank you,

Boris.

managing SQL server 2000 servers with 2K5 management studio

For some reason when I try to use sql 2005 management studio to manage a sql server 2000 server and attempt to use activity monitor to view processes I get an error (see below) (BTW I love being able to copy the message text). I can perform this operation from enterprise manager without issue. Is this a problem with management studio?

TITLE: Microsoft SQL Server Management Studio

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Unable to execute requested command.

Unable to launch Activity Monitor. You may not have sufficient permissions. (ActivityMonitor)

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)

Could you please confirm that you can connect to the server otherwise and run a query (say select * from sysprocesses) and only activity monitor is not working.

What are the versions of Management Tools and SQL Server you have? What is the configuration? Are you doing it on the same machine or going over network? Was it this way from the beginning?

Boris.

|||That query worked fine. It was being accessed over a network. I was able to run querys on databases. However the server did have an issue with 1 database not being accessable. Now that the server issue has been corrected (the drive repaired). It seems to work fine from management studio. I am going to try to recreate the original server in a lab to see if this is some sort of bug in 2k5.|||

I do this all the time, but as the DBA I have the luxury of logging into the 2K box as 'sa'. I would guess the permissions issue is the root here... what account are you using to login? What roles/permissions does the account have?

More info could help us all help you.

|||

You can connect a profiler to your SQL Server 2000 instance and see what is going on when you double click on Activity Monitor in Management Studio. Since you are getting a timeout error and you had a suspect database, one of the queries did not return result in time and thus the message.

If you are able to reproduce the problem in your lab environment, please post the results here.

Thank you,

Boris.

Managing Large Views on Large Tables

An indexed view appears to be a solution to managing huge tables, but my
efforts for this have missed the mark and there may be better solutions
anyhow. In a table of roughly 500 million records, 50 million may belong to
a given month of a year. Without creating 12 tables of each month, can this
be done somehow without using an indexed view? For that matter, it may not
hurt to try the indexed view again. What is the best solution for handling
queries against enormous amounts of data (Windows 2000 Advanced Server with 4
Gig RAM - not /3GB switching, no /PAE).
Regards,
Jamie
If your main concern is performance, index tuning is paramount. Indexed
views can greatly improve performance of certain types of queries,
especially aggregations. You may need different indexes to support
different types of queries.
However, you still need to consider manageability since it will take a while
to rebuild indexes on large tables/views such as this. If possible,
consider SQL 2005 since it provides partitioning to better facilitate
managing large tables.
-
Hope this helps.
Dan Guzman
SQL Server MVP
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:E18AA5C1-AC1E-4E82-8B2E-9E2282D81A53@.microsoft.com...
> An indexed view appears to be a solution to managing huge tables, but my
> efforts for this have missed the mark and there may be better solutions
> anyhow. In a table of roughly 500 million records, 50 million may belong
> to
> a given month of a year. Without creating 12 tables of each month, can
> this
> be done somehow without using an indexed view? For that matter, it may
> not
> hurt to try the indexed view again. What is the best solution for
> handling
> queries against enormous amounts of data (Windows 2000 Advanced Server
> with 4
> Gig RAM - not /3GB switching, no /PAE).
> --
> Regards,
> Jamie
|||A truly great help at this point would be an example of an indexed view using
an aggregate.
Regards,
Jamie
"Dan Guzman" wrote:

> If your main concern is performance, index tuning is paramount. Indexed
> views can greatly improve performance of certain types of queries,
> especially aggregations. You may need different indexes to support
> different types of queries.
> However, you still need to consider manageability since it will take a while
> to rebuild indexes on large tables/views such as this. If possible,
> consider SQL 2005 since it provides partitioning to better facilitate
> managing large tables.
>
> -
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:E18AA5C1-AC1E-4E82-8B2E-9E2282D81A53@.microsoft.com...
>
|||>A truly great help at this point would be an example of an indexed view
>using
> an aggregate.
Below is an example using the Northwind [Order Details] table:
CREATE VIEW dbo.Product_Order_Summary
WITH SCHEMABINDING
AS
SELECT
ProductID,
SUM(UnitPrice * Quantity) AS GrossTotal,
SUM((UnitPrice * Quantity) - Discount) AS NetTotal,
SUM(Quantity) AS OrderQuantity,
COUNT_BIG(*) AS Orders
FROM dbo.[Order Details]
GROUP BY
ProductID
GO
CREATE UNIQUE CLUSTERED INDEX cdx_Product_Order_Summary
ON Product_Order_Summary(ProductID)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:D8E0434F-A220-497F-8012-6AD38B42F4EF@.microsoft.com...[vbcol=seagreen]
>A truly great help at this point would be an example of an indexed view
>using
> an aggregate.
> --
> Regards,
> Jamie
>
> "Dan Guzman" wrote:
|||Thank you.
Regards,
Jamie
"Dan Guzman" wrote:

> Below is an example using the Northwind [Order Details] table:
> CREATE VIEW dbo.Product_Order_Summary
> WITH SCHEMABINDING
> AS
> SELECT
> ProductID,
> SUM(UnitPrice * Quantity) AS GrossTotal,
> SUM((UnitPrice * Quantity) - Discount) AS NetTotal,
> SUM(Quantity) AS OrderQuantity,
> COUNT_BIG(*) AS Orders
> FROM dbo.[Order Details]
> GROUP BY
> ProductID
> GO
> CREATE UNIQUE CLUSTERED INDEX cdx_Product_Order_Summary
> ON Product_Order_Summary(ProductID)
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:D8E0434F-A220-497F-8012-6AD38B42F4EF@.microsoft.com...
>

Managing Large Views on Large Tables

An indexed view appears to be a solution to managing huge tables, but my
efforts for this have missed the mark and there may be better solutions
anyhow. In a table of roughly 500 million records, 50 million may belong to
a given month of a year. Without creating 12 tables of each month, can thi
s
be done somehow without using an indexed view? For that matter, it may not
hurt to try the indexed view again. What is the best solution for handling
queries against enormous amounts of data (Windows 2000 Advanced Server with
4
Gig RAM - not /3GB switching, no /PAE).
--
Regards,
JamieIf your main concern is performance, index tuning is paramount. Indexed
views can greatly improve performance of certain types of queries,
especially aggregations. You may need different indexes to support
different types of queries.
However, you still need to consider manageability since it will take a while
to rebuild indexes on large tables/views such as this. If possible,
consider SQL 2005 since it provides partitioning to better facilitate
managing large tables.
-
Hope this helps.
Dan Guzman
SQL Server MVP
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:E18AA5C1-AC1E-4E82-8B2E-9E2282D81A53@.microsoft.com...
> An indexed view appears to be a solution to managing huge tables, but my
> efforts for this have missed the mark and there may be better solutions
> anyhow. In a table of roughly 500 million records, 50 million may belong
> to
> a given month of a year. Without creating 12 tables of each month, can
> this
> be done somehow without using an indexed view? For that matter, it may
> not
> hurt to try the indexed view again. What is the best solution for
> handling
> queries against enormous amounts of data (Windows 2000 Advanced Server
> with 4
> Gig RAM - not /3GB switching, no /PAE).
> --
> Regards,
> Jamie|||A truly great help at this point would be an example of an indexed view usin
g
an aggregate.
--
Regards,
Jamie
"Dan Guzman" wrote:

> If your main concern is performance, index tuning is paramount. Indexed
> views can greatly improve performance of certain types of queries,
> especially aggregations. You may need different indexes to support
> different types of queries.
> However, you still need to consider manageability since it will take a whi
le
> to rebuild indexes on large tables/views such as this. If possible,
> consider SQL 2005 since it provides partitioning to better facilitate
> managing large tables.
>
> -
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:E18AA5C1-AC1E-4E82-8B2E-9E2282D81A53@.microsoft.com...
>|||>A truly great help at this point would be an example of an indexed view
>using
> an aggregate.
Below is an example using the Northwind [Order Details] table:
CREATE VIEW dbo.Product_Order_Summary
WITH SCHEMABINDING
AS
SELECT
ProductID,
SUM(UnitPrice * Quantity) AS GrossTotal,
SUM((UnitPrice * Quantity) - Discount) AS NetTotal,
SUM(Quantity) AS OrderQuantity,
COUNT_BIG(*) AS Orders
FROM dbo.[Order Details]
GROUP BY
ProductID
GO
CREATE UNIQUE CLUSTERED INDEX cdx_Product_Order_Summary
ON Product_Order_Summary(ProductID)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:D8E0434F-A220-497F-8012-6AD38B42F4EF@.microsoft.com...[vbcol=seagreen]
>A truly great help at this point would be an example of an indexed view
>using
> an aggregate.
> --
> Regards,
> Jamie
>
> "Dan Guzman" wrote:
>|||Thank you.
--
Regards,
Jamie
"Dan Guzman" wrote:

> Below is an example using the Northwind [Order Details] table:
> CREATE VIEW dbo.Product_Order_Summary
> WITH SCHEMABINDING
> AS
> SELECT
> ProductID,
> SUM(UnitPrice * Quantity) AS GrossTotal,
> SUM((UnitPrice * Quantity) - Discount) AS NetTotal,
> SUM(Quantity) AS OrderQuantity,
> COUNT_BIG(*) AS Orders
> FROM dbo.[Order Details]
> GROUP BY
> ProductID
> GO
> CREATE UNIQUE CLUSTERED INDEX cdx_Product_Order_Summary
> ON Product_Order_Summary(ProductID)
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:D8E0434F-A220-497F-8012-6AD38B42F4EF@.microsoft.com...
>

Managing large number of objects (table,sp,view)

Hi,

Our database has very large number of objects. We have a naming convension by modules, subprojects etc. But for example when we need to open a specific table it still takes time to find it. If we could create custom folders under table folder or stored procedure folder it will be easier to find an object. We could create sub folders by module, subproject and classify our objects with these folders. Will the next version SQL Server 2008 support this kind of functionality?

I think to a certian limit Schema can solve your problem. You can post your suggestion in Microsoft Connect...

Madhu

|||

I've worked with databases containing hundreds of tables, and thousand of code objects. Good schema design, and enforcing rigorous naming conventions have always worked to expediently locate the sought object.

As Madhu indicated, you can 'group' by schema. Explore the AdventureWorks database for topical seperation examples.

If you think that having some form of 'sub-folder' would help with organization, please offer the suggestion to the SQL Deveopment team at:

http://connect.microsoft.com/sqlserver

(I would search first. If someone else has offered the idea, add your 'vote' to the suggestion. It is a 'popularity' contest.

|||

Thanks for reply, I have posted this to Microsoft Connect as suggestion.

|||

if we grouped tables by schema, shall we modify programs code files developed over the database?

|||Yes, you will have to add the schema to the object name every place the object is referenced.

|||

Microsoft connect suggestion for this issue is here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289521

Managing Hierarchies in Dimension Tables

I am currently looking at the capabilities in SSIS from the point of view of an ETL developer who has worked with other products eg. Informatica, Cognos DecisionStream and one of things I note is a lack of support for dimensional hierarchies.

It appears that MS have assumed that SSIS users will automatically use SSAS.

We use Hyperion Essbase. Other sites have Cognos or Business Objects for their OLAP/BI.

I would like to be able build multi-level dimension hierarchies directly from within SSIS.

Has MS considered this for future versions?

We don't have any support for building hierarchies in these products simply becuase we do not connect directly to their metadata. However, it is possible to load hierarchies in SSIS - although again without direct metadata support.

Could you outline some features that you think would be useful? Or perhaps some issues you are currently facing in loading hierarchies?

Thanks

Donald Farmer

|||

Hi Donald,

Many enterprises are moving towards the concept of Master Data Management. This is the function of maintaining the reporting dimensions externally to any DW/BI system. There are specialised tools to do this but it can be managed in spreadsheets and fed into a dimension repository. This may be as simple as a few tables which store the attributes and parent/child relationships. The data in this repository has been validated.

As an ETL developer, when I wish to build dimensional tables, I can use the Master Data to source the hierarchies etc and then build the appropriate dimensional tables for either a star or snowflake schema.

Cognos 8 Data Integration (formerly the DecisionStream ETL tool) allows a developer to define a hierarchy. The GUI allows them to select the parent, child, description, and Top Parent Node of the hierarchy. It then generates a table containing chiild, description, parent, and level. From this, I can build a star schema dimension. One of the best features is that it checks the hierarchy for errors. The Developer can also view the hierarchy tree.

With SSIS, and I am a relative newbie, the only way to do the above is to too build a dimension in SSAS and then call that component from SSIS.

Management tools SQL2005 and table ovner name dbo.tablename.

Hei all
Using the new SQL Server2005 Management tools to view and edit
tables/storedprosedures it automatically prefix the table and SP names with
the ovner name. We usually type the first letters of the tablename to find
the table name. Now we have to start with dbo.tablename every time. Is there
a setting to get rid of this ovner name in this list?
thanx all
geirFor 'normal' users in the database, you can specify a default schema:
use master
create login user1 with password = 'bla'
use adventureworks
create user user1
alter user user1 with default_schema = person
Given that proper permissions are in place, user1 can now:
select * from address
where the actually table name is person.address.
Default schema is ignored for members of 'sysadmin'. All members of the
sysadmin fixed server role have a default schema of dbo.
Hope this helps,
Hans
"Geir Holme" <geir@.multicase.no> wrote in message
news:%231jTdJMgGHA.5088@.TK2MSFTNGP02.phx.gbl...
> Hei all
> Using the new SQL Server2005 Management tools to view and edit
> tables/storedprosedures it automatically prefix the table and SP names
> with the ovner name. We usually type the first letters of the tablename to
> find the table name. Now we have to start with dbo.tablename every time.
> Is there a setting to get rid of this ovner name in this list?
> thanx all
> geir
>|||Hi Hans.
Thanx for your reply.
Mabe I don't explain good enough. The short version is:
In the Management tools (Enterprice manger on sql2000) I don't want to see
the owner of the tables and SP' in the list. Can I remove this.
Why?
Because when I want to look up a table I just writes the name and it
"autosearches" the tablename. In SQL2005 I have to start with dbo.tablename
every time. I want to scip dbo. and just write the tablename to find my
table and edit it. The same for procedures.
regards
geir
"Hans Dingemans" <hans_dingemans@.hotmail.com> wrote in message
news:%23m0$VnMgGHA.1204@.TK2MSFTNGP02.phx.gbl...
> For 'normal' users in the database, you can specify a default schema:
> use master
> create login user1 with password = 'bla'
> use adventureworks
> create user user1
> alter user user1 with default_schema = person
> Given that proper permissions are in place, user1 can now:
> select * from address
> where the actually table name is person.address.
> Default schema is ignored for members of 'sysadmin'. All members of the
> sysadmin fixed server role have a default schema of dbo.
> Hope this helps,
> Hans
> "Geir Holme" <geir@.multicase.no> wrote in message
> news:%231jTdJMgGHA.5088@.TK2MSFTNGP02.phx.gbl...
>> Hei all
>> Using the new SQL Server2005 Management tools to view and edit
>> tables/storedprosedures it automatically prefix the table and SP names
>> with the ovner name. We usually type the first letters of the tablename
>> to find the table name. Now we have to start with dbo.tablename every
>> time. Is there a setting to get rid of this ovner name in this list?
>> thanx all
>> geir
>

Management Studio: Fatal Scripting Error

I'm trying to add more than one view to my database in a script. While the view in my scripts are different than the example below, it illustrates the problem. Essentially, when I try to add more than one view, I'm getting a parsing error on the word "GO". The script works OK in SQL 2000 Query Analyzer but fails in Management Studio. I've installed the SQL 2005 Service Pack 1 CTP on my Windows XP SP2 workstation. When I run only the top or bottom portion of the view, the respective view script parses and runs.
Here is the script:
CREATE view [dbo].[Object_View] as
select * from sysobjects where name = 'sysobjects'
GO
CREATE view [dbo].[Column_View] as
select * from table_detail where name = 'syscolumns'
GO
Here is the error:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.
I found the problem. I was using an old script that had been set up for both Oracle and SQL Server to generate the views from metadata. The scripts were generating a CHAR(13) for carriage return without a CHAR(10) for line feed. This caused an error in Management Studio - but curiously not in Query Analyzer.
Is anybody else finding little idiosyncrasies in this that make Query Analyzer look still good? There are some things I really like in Management Studio but things like this drive me crazy. I really don't like that I can't script the DROP and PERMISSIONS when I script an object - at least I haven't found the configuration yet.
Chuck Hawkins
"Dr. Network" <charles.hawkins@.jenzabar.net> wrote in message news:%23CIX4S1VGHA.5100@.TK2MSFTNGP11.phx.gbl...
I'm trying to add more than one view to my database in a script. While the view in my scripts are different than the example below, it illustrates the problem. Essentially, when I try to add more than one view, I'm getting a parsing error on the word "GO". The script works OK in SQL 2000 Query Analyzer but fails in Management Studio. I've installed the SQL 2005 Service Pack 1 CTP on my Windows XP SP2 workstation. When I run only the top or bottom portion of the view, the respective view script parses and runs.
Here is the script:
CREATE view [dbo].[Object_View] as
select * from sysobjects where name = 'sysobjects'
GO
CREATE view [dbo].[Column_View] as
select * from table_detail where name = 'syscolumns'
GO
Here is the error:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.
|||This was addressed in Bug ID FDBK44155.
Chuck Hawkins
"Dr. Network" <charles.hawkins@.jenzabar.net> wrote in message news:ucmK$k1VGHA.5592@.TK2MSFTNGP09.phx.gbl...
I found the problem. I was using an old script that had been set up for both Oracle and SQL Server to generate the views from metadata. The scripts were generating a CHAR(13) for carriage return without a CHAR(10) for line feed. This caused an error in Management Studio - but curiously not in Query Analyzer.
Is anybody else finding little idiosyncrasies in this that make Query Analyzer look still good? There are some things I really like in Management Studio but things like this drive me crazy. I really don't like that I can't script the DROP and PERMISSIONS when I script an object - at least I haven't found the configuration yet.
Chuck Hawkins
"Dr. Network" <charles.hawkins@.jenzabar.net> wrote in message news:%23CIX4S1VGHA.5100@.TK2MSFTNGP11.phx.gbl...
I'm trying to add more than one view to my database in a script. While the view in my scripts are different than the example below, it illustrates the problem. Essentially, when I try to add more than one view, I'm getting a parsing error on the word "GO". The script works OK in SQL 2000 Query Analyzer but fails in Management Studio. I've installed the SQL 2005 Service Pack 1 CTP on my Windows XP SP2 workstation. When I run only the top or bottom portion of the view, the respective view script parses and runs.
Here is the script:
CREATE view [dbo].[Object_View] as
select * from sysobjects where name = 'sysobjects'
GO
CREATE view [dbo].[Column_View] as
select * from table_detail where name = 'syscolumns'
GO
Here is the error:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.

Management Studio will not allow me to create a view

Hi there,
When I run the following query I get the correct result.
select * from Inventory As I Full Outer Join Publisher As P on
I.ID=P.InventoryID
However, when I try to create a view with the same select statement I get
the following error:
Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
Column names in each view or function must be unique. Column name 'ID' in
view or function 'InventoyPublisherView' is specified more than once.
The CREATE VIEW statement I'm using is:
CREATE VIEW InventoyPublisherView AS
(SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
I.ID=P.InventoryID)
Many thanks in advance for the help. Very much appreciatedName the columns in the SELECT statement. Both tables has a column named ID,
and the view cannot
have two columns with the same name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> Hi there,
> When I run the following query I get the correct result.
> select * from Inventory As I Full Outer Join Publisher As P on
> I.ID=P.InventoryID
> However, when I try to create a view with the same select statement I get
> the following error:
> Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
> Column names in each view or function must be unique. Column name 'ID' in
> view or function 'InventoyPublisherView' is specified more than once.
> The CREATE VIEW statement I'm using is:
> CREATE VIEW InventoyPublisherView AS
> (SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
> I.ID=P.InventoryID)
> Many thanks in advance for the help. Very much appreciated
>|||"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> Hi there,
> When I run the following query I get the correct result.
> select * from Inventory As I Full Outer Join Publisher As P on
> I.ID=P.InventoryID
> However, when I try to create a view with the same select statement I get
> the following error:
> Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
> Column names in each view or function must be unique. Column name 'ID' in
> view or function 'InventoyPublisherView' is specified more than once.
> The CREATE VIEW statement I'm using is:
> CREATE VIEW InventoyPublisherView AS
> (SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
> I.ID=P.InventoryID)
> Many thanks in advance for the help. Very much appreciated
It's because you have a column named ID in both tables.
If you didn't use Select * (and you should not) you wouldn't have the
problem. Name the columns.
Besides, why would you select I.ID and P.InventoryID in the query since they
have the same value.|||chris,
use column names in the select list instead of the asterisk:
select i.id, i.col2, i.col3, p.col1, p.col2, etc..
from Inventory As I Full Outer Join Publisher As P on I.ID=P.InventoryID
dean
"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> Hi there,
> When I run the following query I get the correct result.
> select * from Inventory As I Full Outer Join Publisher As P on
> I.ID=P.InventoryID
> However, when I try to create a view with the same select statement I get
> the following error:
> Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
> Column names in each view or function must be unique. Column name 'ID' in
> view or function 'InventoyPublisherView' is specified more than once.
> The CREATE VIEW statement I'm using is:
> CREATE VIEW InventoyPublisherView AS
> (SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
> I.ID=P.InventoryID)
> Many thanks in advance for the help. Very much appreciated
>|||Thank you very much, Tibor. I am following exercises from a Wrox book. I
think I will have to shoot the author for writing incorrect code in his
examples.
"Tibor Karaszi" wrote:

> Name the columns in the SELECT statement. Both tables has a column named I
D, and the view cannot
> have two columns with the same name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
> news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
>|||Thank you very much Raymond. The speed of all of your replies (from all of
you guys) is very reassuring for a total beginner like myself. Fantastic job
.
thanks
"Raymond D'Anjou" wrote:

> "Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
> news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> It's because you have a column named ID in both tables.
> If you didn't use Select * (and you should not) you wouldn't have the
> problem. Name the columns.
> Besides, why would you select I.ID and P.InventoryID in the query since th
ey
> have the same value.
>
>|||Thanks Dean. Much appreciated. Like I mentioned in the post above the author
of the book I′m following put incorrect code in his examples. Luckily, ther
e
are great people out there to come to the resuce. Cheers
"Dean" wrote:

> chris,
> use column names in the select list instead of the asterisk:
> select i.id, i.col2, i.col3, p.col1, p.col2, etc..
> from Inventory As I Full Outer Join Publisher As P on I.ID=P.InventoryID
> dean
> "Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
> news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
>
>|||"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:00CEBECA-7183-436E-BB8A-CA36F3388B23@.microsoft.com...
> Thank you very much, Tibor. I am following exercises from a Wrox book. I
> think I will have to shoot the author for writing incorrect code in his
> examples.
This must be why WROX went bankrupt.
All their authors were shot. :-)