TOP with fully qualified table_name (SS7 SP2)

TOP with fully qualified table_name (SS7 SP2)

Post by Don Macpherso » Sun, 25 Feb 2001 07:42:58



Hi,

    SELECT TOP 1 Column1 from OTHERDB.dbo.Table1
    gives:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '1'.

    Does anyone know of a detour so that I can use this as a subquery?

    As essentially,

    -- we are using THISDB
    DELETE FROM Table2
    WHERE Table2.C1 = (SELECT TOP 1 Column1 FROM OTHERDB.dbo.Table1)

Thanks,

Don Macpherson

 
 
 

TOP with fully qualified table_name (SS7 SP2)

Post by Aidas Kavaliauska » Sun, 25 Feb 2001 08:37:11


Hi,

Please check if u don't have extra spaces.

Aidas

 
 
 

TOP with fully qualified table_name (SS7 SP2)

Post by Don Macpherso » Sun, 25 Feb 2001 09:05:19


Aidas,
    I had the minimum number of space characters required for it to parse
correctly.  (none of the tokens had embedded spaces)

    You did get me looking deeper though.  It appears to depend on what my
current database is...
    Why would the syntax depend on the database, not the server?
    Ahh, somebody has left the current DB at 65 compatability.  (OTHERDB was
70)

Thanks,

Don Macpherson


Quote:> Hi,

> Please check if u don't have extra spaces.

> Aidas

 
 
 

1. SP2 blues: owner object not seen by owner unless fully qualified

Oh man...I ran sql server SP2 and now I'm in trouble:

I have tons of code that's referencing Tables and SPs via ADO and VB...we're
talking 3 years of apps and applets...

Before the SP2, if a table owner was connected, my SQL queries did not have
to specifically reference Table objects owned by that owner by owner name.

ie:  "Select * from TableName" USED to work, EVEN if TableName was owned by,
say Bob, as long as Bob was connecting.

Since SP2.... when Bob is connected, ADO can't find the table, unless I
reference the owner: "Select * from Bob.TableName"

I've learned that its best to have DBO own objects, so I do that from now
on, but I've got 100s of tables and procedures and 100s of apps that
reference tables without the full qualifier.  Is there anything I can do,
short of uninstalling SP2, or changing all my apps, that will allow BOB to
see BOB's tables without referencing Bob.Tablename?  There's got to be some
SQL setting that allows this?

Desperate Eric.

2. Yes/No in Logical Field

3. fully qualified table names

4. Coerce to Domain

5. fully qualified table names for select statment?

6. Help with interesting SQL Server Problem

7. Fully Qualified Object Names

8. DDE for word

9. Fully qualifying Database Objects

10. Script to search for databast objects that aren't fully qualified

11. Fully Qualified Name Components

12. Fully Qualified Column Names

13. fully qualified naming