Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1054
  • Last Modified:

Can not find nor query sys.spt_columns_odbc_view

Hello,

my problem is that I can not find view in the management studio's object explorer, nor I can not query that view. The problematic view is:

sys.spt_columns_odbc_view

If I try to query it  with:

select * from sys.spt_columns_odbc_view

I am getting an error:

 Invalid object name 'sys.spt_columns_odbc_view'.

What is interesting is that the same view is executed inside stored procedure sp_columns. Here's the code snippet from sp_columns:

select
            TABLE_QUALIFIER             = s_cov.TABLE_QUALIFIER,
            TABLE_OWNER                 = s_cov.TABLE_OWNER,
            TABLE_NAME                  = s_cov.TABLE_NAME,
            COLUMN_NAME                 = s_cov.COLUMN_NAME,
            DATA_TYPE                   = s_cov.DATA_TYPE_28,
            TYPE_NAME                   = s_cov.TYPE_NAME_28,
            "PRECISION"                 = s_cov.PRECISION_28,
            "LENGTH"                    = s_cov.LENGTH_28,
            SCALE                       = s_cov.SCALE_90,
            RADIX                       = s_cov.RADIX,
            NULLABLE                    = s_cov.NULLABLE,
            REMARKS                     = s_cov.REMARKS,
            COLUMN_DEF                  = s_cov.COLUMN_DEF,
            SQL_DATA_TYPE               = s_cov.SQL_DATA_TYPE_28,
            SQL_DATETIME_SUB            = s_cov.SQL_DATETIME_SUB_90,
            CHAR_OCTET_LENGTH           = s_cov.CHAR_OCTET_LENGTH_28,
            ORDINAL_POSITION            = s_cov.ORDINAL_POSITION,
            IS_NULLABLE                 = s_cov.IS_NULLABLE,
            SS_DATA_TYPE                = s_cov.SS_DATA_TYPE

        from
            sys.spt_columns_odbc_view s_cov

It works inside system stored procedure.

Why it's not working when I try to run it? And why I can't find this view?

Please note:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24609950.html

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23115729.html

This question has already been asked twice on experts exchange, yet there is still no answer to two basic questions - where this view comes from and how to query it.

Best regards, Frenky
0
AntonioRodrigo
Asked:
AntonioRodrigo
  • 5
  • 3
2 Solutions
 
chaauCommented:
Check this article how to "hack" MSSQLSystemResource database. I have not done this myself because I do not have a test SQL Server to play with at the moment, but I think it is very easy to do. Once you have done this, it is very easy to run
SELECT object_definition(OBJECT_ID('sys.spt_columns_odbc_view'))

Open in new window

0
 
Matt BowlerDB team leadCommented:
This is an internal table so you need to connect using the DAC.

Just connect to the server as you would but put 'admin:' in front of the server name. Be aware that you can only have one DAC connection at a time - so close the object explorer etc...
or use SQLCMD...

http://msdn.microsoft.com/en-us/library/ms178068(v=sql.105).aspx
0
 
AntonioRodrigoAuthor Commented:
Thanks a lot for answer, chaau. Your answer is very useful, I didn't know before about system resources database, nor for Mladen's blog.

But, the problem still remains - even inside system resources database I can't find sys.spt_columns_odbc_view - but sp_columns calls this view. Basically the same thing is happening like in other databases. Really strange.

The query:

SELECT object_definition(OBJECT_ID('sys.spt_columns_odbc_view'))

returns NULL, because

object_id('sys.spt_columns_odbc_view')

 is NULL (like sys.spt_columns_odbc_view doesn't exist at all).


This is really strange and mysterious behaviour from Sql server. There's got to be something about this (and I guess some other's too) view, not known to us.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Matt BowlerDB team leadCommented:
Have you tried using the DAC connection?

BTW because it is an internal view - you can query from it (using the DAC) but you still wont be able to find definitions or listings in other catalog views.
0
 
Matt BowlerDB team leadCommented:
FYI - internal code for the view in SQL 2008 is:

create view sys.spt_columns_odbc_view
as
    select
    -- begin (for doing joins)
        OBJECT_ID           = o.object_id,
        SCHEMA_ID           = o.schema_id,
        ODBCVER             = d.ODBCVer,
    -- end (for doing joins)
        TABLE_QUALIFIER     = convert(sysname,DB_NAME()),
        TABLE_OWNER         = convert(sysname,schema_name(o.schema_id)),
        TABLE_NAME          = convert(sysname,o.name),
        COLUMN_NAME         = convert(sysname,c.name),
        DATA_TYPE           = d.DATA_TYPE,
        DATA_TYPE_90          = convert(smallint, -- new date time types for downlevel client
                                case
                                when d.ss_dtype IN (40, 41, 42, 43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                    -9  -- SQL_WVARCHAR
                                when (d.ss_dtype = 240 and c.max_length = -1) then -- Large UDT => varbinary(max) for 9.0 Client
                                    -3
                                else
                                    d.DATA_TYPE
                                end),
        DATA_TYPE_28        = convert(smallint, -- backward-compatible ODBC types
                                case
                                when (d.ss_dtype = 240) then -- CLR UDT
                                    -4
                                when (d.ss_dtype = 241) then -- XML
                                    -10
                                when (c.max_length = -1 and d.ss_dtype = 167) then -- varchar(max)
                                    -1
                                when (c.max_length = -1 and d.ss_dtype = 231) then -- nvarchar(max)
                                    -10
                                when (c.max_length = -1 and d.ss_dtype = 165) then -- varbinary(max)
                                    -4
                                when d.ss_dtype IN (40, 41, 42, 43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                     -9  -- SQL_WVARCHAR
                                else
                                    d.DATA_TYPE
                                end),
        TYPE_NAME           = convert(sysname,
                                case
                                when (t.system_type_id = 240 or t.user_type_id > 255) then -- CLR UDTs
                                    t.name
                                else
                                    d.TYPE_NAME collate database_default
                                end),
        TYPE_NAME_28        = convert(sysname,
                                case
                                when (t.system_type_id = 240 or t.user_type_id > 255) then -- CLR UDTs
                                    t.name
                                when (c.max_length = -1 and d.ss_dtype = 167) then -- varchar(max)
                                    N'text'
                                when (c.max_length = -1 and d.ss_dtype = 231) then -- nvarchar(max)
                                    N'ntext'
                                when (c.max_length = -1 and d.ss_dtype = 165) then -- varbinary(max)
                                    N'image'
                                else
                                    d.TYPE_NAME
                                end) collate database_default,
        "PRECISION"         = convert(int,
                                case
                                when d.DATA_TYPE in (6,7) then -- FLOAT/REAL
                                    d.data_precision
                                when c.max_length = -1 then
                                    0
                                else
                                    OdbcPrec(c.system_type_id,c.max_length,c.precision)
                                end),
        PRECISION_28        = convert(int,
                                case
when d.DATA_TYPE in (6,7) then -- FLOAT/REAL
                                    d.data_precision
                                when d.ss_dtype = 241 then -- XML
                                    1073741823
                                when (c.max_length = -1 and d.ss_dtype = 167) then -- varchar(max)
                                    2147483647
                                when (c.max_length = -1 and d.ss_dtype = 231) then -- nvarchar(max)
                                    1073741823
                                when (c.max_length = -1 and d.ss_dtype = 165) then -- varbinary(max)
                                    2147483647
                                when (c.max_length = -1 and d.ss_dtype = 240) then -- Large UDT => image for non-SNAC clients
                                    2147483647
                                else
                                    OdbcPrec(c.system_type_id,c.max_length,c.precision)
                                end),
        "LENGTH"            = convert(int,
                                case
                                when d.ss_dtype IN (108,106) then    -- decimal/numeric types
                                    OdbcPrec(c.system_type_id,c.max_length,c.precision)+2
                                when c.max_length = -1 then -- Large UDT => Unlimited Length like varbinary(max)
                                    0
                                when d.ss_dtype = 240 then -- Small UDT
                                    isnull(u.max_length, c.max_length)
                                else
                                    isnull(d.length, c.max_length)
                                end),
        LENGTH_90            = convert(int,
                                case
                                when d.ss_dtype IN (108,106) then    -- decimal/numeric types
                                    OdbcPrec(c.system_type_id,c.max_length,c.precision)+2
                                when d.ss_dtype = 240 then -- CLR UDTs
                                    isnull(u.max_length, c.max_length)
                                when c.max_length = -1 then
                                    0
                                when d.ss_dtype IN (40, 41, 42, 43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                    OdbcPrec(c.system_type_id,c.max_length,c.precision)*2
                                else
                                    isnull(d.length, c.max_length)
                                end),
        LENGTH_28           = convert(int,
                                case
                                when d.ss_dtype IN (108,106) then    -- decimal/numeric types
                                    OdbcPrec(c.system_type_id,c.max_length,c.precision)+2
                                when (c.max_length = -1 and d.ss_dtype = 240) then -- Large UDT => image for non-SNAC clients
                                    2147483647
                                when d.ss_dtype = 240 then -- Small UDT
                                    isnull(u.max_length, c.max_length)
                                when d.ss_dtype = 241 then -- XML
                                    2147483646
                                when (c.max_length = -1 and d.ss_dtype = 167) then -- varchar(max)
                                    2147483647
                                when (c.max_length = -1 and d.ss_dtype = 231) then -- nvarchar(max)
                                    2147483646
                                when (c.max_length = -1 and d.ss_dtype = 165) then -- varbinary(max)
                                    2147483647
                                when d.ss_dtype IN (40, 41, 42, 43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                    OdbcPrec(c.system_type_id,c.max_length,c.precision)*2
                                else
  isnull(d.length, c.max_length)
                                end),
        SCALE               = convert(smallint, OdbcScale(c.system_type_id,c.scale)),
        SCALE_90            = convert(smallint,
                                case
                                when d.ss_dtype IN (40, 41, 42, 43) then  -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                    null
                                else
                                    OdbcScale(c.system_type_id,c.scale)
                                end),
        RADIX               = d.RADIX,
        NULLABLE            = convert(smallint, ColumnProperty (c.object_id, c.name, 'AllowsNull')),
        REMARKS             = convert(varchar(254),null),   -- Remarks are NULL
        COLUMN_DEF          = convert(nvarchar(4000), object_definition(ColumnProperty(c.object_id, c.name, 'default'))),
        SQL_DATA_TYPE       = d.SQL_DATA_TYPE,
        SQL_DATA_TYPE_90    = convert(smallint,
                                case
                                when d.ss_dtype IN (40, 41, 42, 43) then  -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                     -9  -- SQL_WVARCHAR
                                when (d.ss_dtype = 240 and c.max_length = -1) then -- Large UDT => varbinary(max) for 9.0 clients
                                    -3
                                else
                                    d.SQL_DATA_TYPE
                                end),
        SQL_DATA_TYPE_28    = convert(smallint, -- backward-compatible ODBC types
                                case
                                when (d.ss_dtype = 240) then -- CLR UDT
                                    -4
                                when (d.ss_dtype = 241) then -- XML
                                    -10
                                when (c.max_length = -1 and d.ss_dtype = 167) then -- varchar(max)
                                    -1
                                when (c.max_length = -1 and d.ss_dtype = 231) then -- nvarchar(max)
                                    -10
                                when (c.max_length = -1 and d.ss_dtype = 165) then -- varbinary(max)
                                    -4
                                when d.ss_dtype IN (40, 41, 42, 43) then  -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                     -9  -- SQL_WVARCHAR
                                else
                                    d.SQL_DATA_TYPE
                                end),
        SQL_DATETIME_SUB    = d.SQL_DATETIME_SUB,
        SQL_DATETIME_SUB_90      = convert(smallint,
                                case
                                when d.ss_dtype IN (40, 41, 42, 43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                    null
                                else
                                    d.SQL_DATETIME_SUB
                                end),
        CHAR_OCTET_LENGTH   = convert(int,
                                case
                                when c.max_length = -1 then -- Large UDT => Unlimited length like varbinary(max)
                                    0
                                when d.ss_dtype = 240 then -- Small UDT
                                    isnull(u.max_length, c.max_length)
                                else
                                    isnull(d.length, c.max_length)+d.charbin
                                end),
        CHAR_OCTET_LENGTH_28= convert(int,
                                case
                                when (c.max_length = -1 and d.ss_dtype = 240) then -- Large UDT => image for non-SNAC clients
                                    2147483647
                                when d.ss_dtype = 240 then -- Small UDT
                                    isnull(u.max_length, c.max_length)
                                when d.ss_dtype = 241 then -- XML
                                    2147483646
                                when (c.max_length = -1 and d.ss_dtype = 167) then -- varchar(max)
                                    2147483647
                                when (c.max_length = -1 and d.ss_dtype = 231) then -- nvarchar(max)
                                    2147483646
                                when (c.max_length = -1 and d.ss_dtype = 165) then -- varbinary(max)
                                    2147483647
                                else
                                    isnull(d.length, c.max_length)+d.charbin
                                end),
        ORDINAL_POSITION    = convert(int, ColumnProperty(c.object_id, c.name, 'ordinal')),
        IS_NULLABLE         = convert(varchar(254),
                                      rtrim(substring('NO YES',(ColumnProperty (c.object_id, c.name, 'AllowsNull')*3)+1,3))),
        SS_IS_SPARSE        = convert ( smallint, c.is_sparse ),
        SS_IS_COLUMN_SET    = convert ( smallint, c.is_column_set ),
        SS_IS_COMPUTED      = convert(smallint,c.is_computed),
        SS_IS_IDENTITY      = convert(smallint,c.is_identity),
        SS_UDT_CATALOG_NAME = convert(sysname,
                                case
                                when d.ss_dtype = 240 then -- CLR UDTs
                                    db_name()
                                else
                                    null
                                end),
        SS_UDT_SCHEMA_NAME  = convert(sysname,
                                case
                                when d.ss_dtype = 240 then -- CLR UDTs
                                    schema_name(u.schema_id)
                                else
                                    null
                                end),
        SS_UDT_ASSEMBLY_TYPE_NAME    = u.assembly_qualified_name,
        SS_XML_SCHEMACOLLECTION_CATALOG_NAME = convert(sysname,
                                case
                                when (d.ss_dtype = 241 and xsc.name is not null) then -- XML
                                    db_name()
                                else
                                    null
                                end),
        SS_XML_SCHEMACOLLECTION_SCHEMA_NAME  = convert(sysname,
                                case
                                when (d.ss_dtype = 241 and xsc.name is not null) then -- XML
                                    schema_name(xsc.schema_id)
                                else
                                    null
                                end),
        SS_XML_SCHEMACOLLECTION_NAME = xsc.name,
        -- Info here is for backward compatibility - SQL 6.5
        SS_DATA_TYPE        = convert(tinyint,
                                case
                                when d.ss_dtype = 240 then -- CLR UDTs
                                    23
                                else
                                    XTypeToTds(c.system_type_id, c.is_nullable)
                                end),
        -- Used to filter by callers of the view
        OBJECT_TYPE         = o.type
    from
        sys.all_columns c inner join
        sys.all_objects o on
            (
                o.object_id = c.object_id and
                o.type in ('S','U','V', 'TF', 'IF', 'TT')  -- limit columns to tables, views, table-valued functions, and table types only
            ) inner join
        sys.types t on
            (
                t.user_type_id = c.user_type_id
            ) inner join
        sys.spt_datatype_info d on
            (
                d.ss_dtype = c.system_type_id and
                d.AUTO_INCREMENT = c.is_identity
            )
        left join
        sys.xml_schema_collections xsc on
            (
                xsc.xml_collection_id = c.xml_collection_id
            )
        left join
        sys.assembly_types u on
            (
                u.user_type_id = c.user_type_id
            )
0
 
AntonioRodrigoAuthor Commented:
@MattSQL - how did you get the code for view creation?
0
 
Matt BowlerDB team leadCommented:
If you follow the steps in the link above about "hacking the resource db". Basically:

- Stop SQL
-Copy the mdf and ldf files of the resource database and rename.
-Start SQL.
-Attach a new database with these files.

USE [master]
GO
CREATE DATABASE [mssqlsystemresource1] ON
( FILENAME = N'<your path>\mssqlsystemresource1.mdf' ),
( FILENAME = N'<your path>\mssqlsystemresource1.ldf' )
 FOR ATTACH
GO
if not exists (select name from master.sys.databases sd where name = N'mssqlsystemresource1' AND
    SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
        EXEC [mssqlsystemresource1].dbo.sp_changedbowner @loginame=N'sa', @map=false

-Now you can browse the system views.
-You can't script them out but running this will do it...

use mssqlsystemresource1
go

SELECT object_definition(OBJECT_ID('sys.spt_columns_odbc_view'))
0
 
AntonioRodrigoAuthor Commented:
@MattSQL - thanks for answer. The only thing I didn't know is that I need DAC connection. With DAC, connection to resources database it's not needed.

So, DAC is the solution, because sys.spt_columns_odbc_view is internal view.
0
 
Matt BowlerDB team leadCommented:
Once you've restored the resource database as a standard user database you don't need to use the DAC any more :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now