Post reply

Query Seems to be failing

  • May 12, 2009 at 3:23 am

    #71519

    Hi All

    I got this code from:http://www.sqlservercentral.com/scripts/Miscellaneous/31574/

    But this section seems to be failing on SQL 2000

    SELECT syscolumns.[name],

    (SELECT systypes.[name] FROM systypes WHERE xtype = syscolumns.xtype),

    syscolumns.length,

    sysproperties.[value],

    syscolumns.prec,

    syscolumns.scale,

    syscolumns.[collation]

    FROM sysobjects INNER JOIN

    syscolumns ON sysobjects.id = syscolumns.id

    INNER JOIN systypes ON syscolumns.xtype = systypes.xtype

    LEFT OUTER JOIN sysproperties ON syscolumns.colid = sysproperties.smallid

    AND syscolumns.id = sysproperties.id

    WHERE sysobjects.id = 1 ORDER BY syscolumns.colorder

    Thanks

  • May 12, 2009 at 3:38 am

    #992708

    Query does not run in SQL 2005. sysproperties is not there in sql 2005.

    "Keep Trying"

  • May 12, 2009 at 6:21 am

    #992780

    Chirag (5/12/2009)

    Query does not run in SQL 2005. sysproperties is not there in sql 2005.

    Hi

    This is SQL 2000 specific.

    Thx

  • May 12, 2009 at 6:32 am

    #992797

    When you say 'failing', what do you mean? Is it not returning any rows? Is it giving a syntax error, and if so, what error?

    What's the code supposed to do?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 12, 2009 at 7:03 am

    #992846

    I just ran the code and it gives the error:

    Msg 512, Level 16, State 1, Line 1

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.

    'cause this subquery "SELECT systypes.[name] FROM systypes WHERE xtype = syscolumns.xtype" gives 2 rows for "xtype" = 256 (i.e. type is either "sysname" or "nvarchar")

    Fixed Code:

    1. Removed the sub-query

    2. Joined on "xusertype" instead of "xtype"

    SELECTsyscolumns.[name], systypes.[name] AS datatype, syscolumns.length, sysproperties.[value],

    syscolumns.prec, syscolumns.scale, syscolumns.[collation]

    FROMsysobjects

    INNER JOIN syscolumns ON sysobjects.id = syscolumns.id

    INNER JOIN systypes ON syscolumns.xusertype = systypes.xusertype

    LEFT OUTER JOIN sysproperties ON syscolumns.colid = sysproperties.smallid

    AND syscolumns.id = sysproperties.id

    WHEREsysobjects.id = 1

    ORDER BY syscolumns.colorder

    --Ramesh

  • May 12, 2009 at 9:32 am

    #993068

    Thanks very much for this.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply