[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Unable to create new view in database

Maurizio - Roma - Italy

3/21/2007 11:02:00 AM

I receive this error when i try to create and save a new view on in one
database of my server. The message is:

"Impossible to resolve the expression for the tie or the associated object
schema"

I try to create the view with t-sql and also with SQL Management Studio.
I access to the database with dbo permission.
I can create a new table without errors...

With other database i don't have this problem.

What i can check?

Than you

Maurizio Salvatelli
8 Answers

Uri Dimant

3/21/2007 11:22:00 AM

0

Maurizio

Can you showe us the entire CREATE VIEW.... script?





"Maurizio - Roma - Italy" <MaurizioRomaItaly@discussions.microsoft.com>
wrote in message news:F2E1C5BE-3C1A-4880-9C6E-824DB16DA078@microsoft.com...
>I receive this error when i try to create and save a new view on in one
> database of my server. The message is:
>
> "Impossible to resolve the expression for the tie or the associated object
> schema"
>
> I try to create the view with t-sql and also with SQL Management Studio.
> I access to the database with dbo permission.
> I can create a new table without errors...
>
> With other database i don't have this problem.
>
> What i can check?
>
> Than you
>
> Maurizio Salvatelli


Maurizio - Roma - Italy

3/21/2007 11:50:00 AM

0

Is a very simple test view:

CREATE VIEW Testview AS
SELECT TOP (100) PERCENT dbo.Anagrafica.*
FROM dbo.Anagrafica
ORDER BY Anagrafica
GO

But the problem is the same creating view from SQL Studio Managenment Design
tool.

Thank you.

Maurizio Salvatelli

"Uri Dimant" wrote:

> Maurizio
>
> Can you showe us the entire CREATE VIEW.... script?
>
>
>
>
>
> "Maurizio - Roma - Italy" <MaurizioRomaItaly@discussions.microsoft.com>
> wrote in message news:F2E1C5BE-3C1A-4880-9C6E-824DB16DA078@microsoft.com...
> >I receive this error when i try to create and save a new view on in one
> > database of my server. The message is:
> >
> > "Impossible to resolve the expression for the tie or the associated object
> > schema"
> >
> > I try to create the view with t-sql and also with SQL Management Studio.
> > I access to the database with dbo permission.
> > I can create a new table without errors...
> >
> > With other database i don't have this problem.
> >
> > What i can check?
> >
> > Than you
> >
> > Maurizio Salvatelli
>
>
>

Uri Dimant

3/21/2007 12:11:00 PM

0

Hi
As I see it is SQL Server 2005, right? I cannot reproduced the problem on my
machime SQL Server Dev 2005 sp2a


"Maurizio - Roma - Italy" <MaurizioRomaItaly@discussions.microsoft.com>
wrote in message news:9662F4FF-B7D7-4BA7-BB57-D9D9FD52E094@microsoft.com...
> Is a very simple test view:
>
> CREATE VIEW Testview AS
> SELECT TOP (100) PERCENT dbo.Anagrafica.*
> FROM dbo.Anagrafica
> ORDER BY Anagrafica
> GO
>
> But the problem is the same creating view from SQL Studio Managenment
> Design
> tool.
>
> Thank you.
>
> Maurizio Salvatelli
>
> "Uri Dimant" wrote:
>
>> Maurizio
>>
>> Can you showe us the entire CREATE VIEW.... script?
>>
>>
>>
>>
>>
>> "Maurizio - Roma - Italy" <MaurizioRomaItaly@discussions.microsoft.com>
>> wrote in message
>> news:F2E1C5BE-3C1A-4880-9C6E-824DB16DA078@microsoft.com...
>> >I receive this error when i try to create and save a new view on in one
>> > database of my server. The message is:
>> >
>> > "Impossible to resolve the expression for the tie or the associated
>> > object
>> > schema"
>> >
>> > I try to create the view with t-sql and also with SQL Management
>> > Studio.
>> > I access to the database with dbo permission.
>> > I can create a new table without errors...
>> >
>> > With other database i don't have this problem.
>> >
>> > What i can check?
>> >
>> > Than you
>> >
>> > Maurizio Salvatelli
>>
>>
>>


Roy Harvey

3/21/2007 12:19:00 PM

0

The tool you used to create the view is defective in supporting TOP
(100) PERCENT.

In SQL Server 2000 there is an undocumented, unsupported, and
unreliable behavior that TOP (100) PERCENT was exploiting. It was
never a good idea, never could be counted on, and stopped working in
SQL Server 2005. TOP (100) PERCENT has no place in a view.

Remove the TOP (100) PERCENT and the ORDER BY clause and you should be
able to create the view.

Roy Harvey
Beacon Falls, CT

On Wed, 21 Mar 2007 04:50:28 -0700, Maurizio - Roma - Italy
<MaurizioRomaItaly@discussions.microsoft.com> wrote:

>Is a very simple test view:
>
>CREATE VIEW Testview AS
>SELECT TOP (100) PERCENT dbo.Anagrafica.*
>FROM dbo.Anagrafica
>ORDER BY Anagrafica
>GO
>
>But the problem is the same creating view from SQL Studio Managenment Design
>tool.
>
>Thank you.
>
>Maurizio Salvatelli
>
>"Uri Dimant" wrote:
>
>> Maurizio
>>
>> Can you showe us the entire CREATE VIEW.... script?
>>
>>
>> "Maurizio - Roma - Italy" <MaurizioRomaItaly@discussions.microsoft.com>
>> wrote in message news:F2E1C5BE-3C1A-4880-9C6E-824DB16DA078@microsoft.com...
>> >I receive this error when i try to create and save a new view on in one
>> > database of my server. The message is:
>> >
>> > "Impossible to resolve the expression for the tie or the associated object
>> > schema"
>> >
>> > I try to create the view with t-sql and also with SQL Management Studio.
>> > I access to the database with dbo permission.
>> > I can create a new table without errors...
>> >
>> > With other database i don't have this problem.
>> >
>> > What i can check?
>> >
>> > Than you
>> >
>> > Maurizio Salvatelli
>>
>>
>>

Maurizio - Roma - Italy

3/21/2007 12:32:00 PM

0

Yes, is SQL 2005 SP2,
also i can't reproduce the problem with different database on the same server.
Maybe sonthing about the autorizzation schema in that database?

Thank you

Maurizio Salvatelli


"Uri Dimant" wrote:

> Hi
> As I see it is SQL Server 2005, right? I cannot reproduced the problem on my
> machime SQL Server Dev 2005 sp2a
>
>
> "Maurizio - Roma - Italy" <MaurizioRomaItaly@discussions.microsoft.com>
> wrote in message news:9662F4FF-B7D7-4BA7-BB57-D9D9FD52E094@microsoft.com...
> > Is a very simple test view:
> >
> > CREATE VIEW Testview AS
> > SELECT TOP (100) PERCENT dbo.Anagrafica.*
> > FROM dbo.Anagrafica
> > ORDER BY Anagrafica
> > GO
> >
> > But the problem is the same creating view from SQL Studio Managenment
> > Design
> > tool.
> >
> > Thank you.
> >
> > Maurizio Salvatelli
> >
> > "Uri Dimant" wrote:
> >
> >> Maurizio
> >>
> >> Can you showe us the entire CREATE VIEW.... script?
> >>
> >>
> >>
> >>
> >>
> >> "Maurizio - Roma - Italy" <MaurizioRomaItaly@discussions.microsoft.com>
> >> wrote in message
> >> news:F2E1C5BE-3C1A-4880-9C6E-824DB16DA078@microsoft.com...
> >> >I receive this error when i try to create and save a new view on in one
> >> > database of my server. The message is:
> >> >
> >> > "Impossible to resolve the expression for the tie or the associated
> >> > object
> >> > schema"
> >> >
> >> > I try to create the view with t-sql and also with SQL Management
> >> > Studio.
> >> > I access to the database with dbo permission.
> >> > I can create a new table without errors...
> >> >
> >> > With other database i don't have this problem.
> >> >
> >> > What i can check?
> >> >
> >> > Than you
> >> >
> >> > Maurizio Salvatelli
> >>
> >>
> >>
>
>
>

Aaron [SQL Server MVP]

3/21/2007 12:36:00 PM

0

> CREATE VIEW Testview AS
> SELECT TOP (100) PERCENT dbo.Anagrafica.*
> FROM dbo.Anagrafica
> ORDER BY Anagrafica

Much simpler to have:

CREATE VIEW dbo.Testview
AS
SELECT <column list>
FROM dbo.Anagrafica;

(a) as others noted, you do not need TOP 100 PERCENT, for various reasons.
(b) you should always prefix objects with schema / owner name.
(c) you should never use SELECT * in production code. It is far too trivial
to drag the "Columns" node from Object Explorer (to avoid having to type the
columns out manually). Listing the columns explicitly will help prevent
your view from suddenly changing behavior when the schema of the underlying
table changes.
(d) since you are only referencing one table, it does not make sense to
prefix the column names with owner/schema.tablename.
(e) ORDER BY makes no sense in a view and doesn't belong there. A view,
like a table, is by definition an unordered set of rows. If you want to
SELECT from the view and get the rows in a certain order, then you add the
ORDER BY clause to the outer query, not to the view itself.

--
Aaron Bertrand
SQL Server MVP
http://www.sq...
http://www.aspfa...

Aaron [SQL Server MVP]

3/21/2007 12:39:00 PM

0

> also i can't reproduce the problem with different database on the same
> server.

Is it possible that dbo.Anagrafica does not exist in the database where this
does not work?

--
Aaron Bertrand
SQL Server MVP
http://www.sq...
http://www.aspfa...

Maurizio - Roma - Italy

3/21/2007 12:53:00 PM

0

The table Anagrafica exist, i try with different view, also copying an
existing view, but the problem is in the autorization to create view non in
the syntax.
I don't now how check if the schema have error or corrupted.
Do you know how to check the schema integrity?

Thank you

Maurizio Salvatelli

"Aaron Bertrand [SQL Server MVP]" wrote:

> > CREATE VIEW Testview AS
> > SELECT TOP (100) PERCENT dbo.Anagrafica.*
> > FROM dbo.Anagrafica
> > ORDER BY Anagrafica
>
> Much simpler to have:
>
> CREATE VIEW dbo.Testview
> AS
> SELECT <column list>
> FROM dbo.Anagrafica;
>
> (a) as others noted, you do not need TOP 100 PERCENT, for various reasons.
> (b) you should always prefix objects with schema / owner name.
> (c) you should never use SELECT * in production code. It is far too trivial
> to drag the "Columns" node from Object Explorer (to avoid having to type the
> columns out manually). Listing the columns explicitly will help prevent
> your view from suddenly changing behavior when the schema of the underlying
> table changes.
> (d) since you are only referencing one table, it does not make sense to
> prefix the column names with owner/schema.tablename.
> (e) ORDER BY makes no sense in a view and doesn't belong there. A view,
> like a table, is by definition an unordered set of rows. If you want to
> SELECT from the view and get the rows in a certain order, then you add the
> ORDER BY clause to the outer query, not to the view itself.
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sq...
> http://www.aspfa...
>
>
>