Solved

Can not find nor query sys.spt_columns_odbc_view

Posted on 2013-06-24
9
985 Views
Last Modified: 2013-06-25
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
Comment
Question by:AntonioRodrigo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 25

Assisted Solution

by:chaau
chaau earned 100 total points
ID: 39273281
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
 
LVL 10

Accepted Solution

by:
Matt Bowler earned 400 total points
ID: 39273831
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
 

Author Comment

by:AntonioRodrigo
ID: 39273950
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
Independent Software Vendors: 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!

 
LVL 10

Expert Comment

by:Matt Bowler
ID: 39273956
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
 
LVL 10

Expert Comment

by:Matt Bowler
ID: 39273972
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
 

Author Comment

by:AntonioRodrigo
ID: 39274239
@MattSQL - how did you get the code for view creation?
0
 
LVL 10

Expert Comment

by:Matt Bowler
ID: 39274377
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
 

Author Comment

by:AntonioRodrigo
ID: 39274475
@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
 
LVL 10

Expert Comment

by:Matt Bowler
ID: 39276192
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question