Solved

Can not find nor query sys.spt_columns_odbc_view

Posted on 2013-06-24
9
916 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
  • 5
  • 3
9 Comments
 
LVL 24

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 9

Accepted Solution

by:
MattSQL 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
 
LVL 9

Expert Comment

by:MattSQL
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 9

Expert Comment

by:MattSQL
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 9

Expert Comment

by:MattSQL
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 9

Expert Comment

by:MattSQL
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now