asked on

No shuch object sys.spt_...

Dear Experts, I have a problem with some system objects in SQL2005:
Previous explanation: I have an old aplication that was developed using SQL 6.5 database, since SQL6.5 I have upgraded to SQL7, SQL2000 and now I want the application to use SQL2005. Every time I have upgraded the database I have made the same changes to compatiblize with MSSQL, but now in SQL2005 have a problem with system objects or similar. All I need is that the stored procedure sp_columns returns data in char(128) type instead of nvarchar. I Explain what I do and what I get:
I have copied the stored procedure sp_columns in another database stored procedure called sp_mycolumns (just copied the code without modifying anything). If you have a look to the sp_columns, it's making a SELECT ... FROM sys.spt_columns_odbc_view, well, when I execute the same sentence in my new stored procedure I got "Msg 208, Level 16, State 1, Procedure sp_mycolumns, Line 78. Invalid object name 'sys.spt_columns_odbc_view'. The diference between the procedures is that sp_columns is in System Stored Procedures folder. I have tried with other spt_objects like sys.spt_datatype_info and got the same response.
Any idea to acces to this system objects?
sp-mycolumns.txt

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JOSEP LLUIS MELIS

ASKER

I've joined sys.columns and information_schema.columns to get my final solution. For your information, here's the select that provides me backward compatibility with application:
      SELECT CONVERT(char(128),i.TABLE_NAME) as TABLE_NAME,
CONVERT(char(128),i.COLUMN_NAME) as COLUMN_NAME,
              CASE t.usertype
                        -- CONVERSIONS QUE EMPRAM DE SQL2000 a SQL2005
                        WHEN 16 THEN -7
                        WHEN 20 THEN -4
                        WHEN 80 THEN -2
                        WHEN 19 THEN -1
                        WHEN 1 THEN 1
                        WHEN 262 THEN 1
                        WHEN 7 THEN 4
                        WHEN 10 THEN 2
                        WHEN 8 THEN 6
                        WHEN 24 THEN 3
                        WHEN 259 THEN 3
                        WHEN 260 THEN 3
                        WHEN 261 THEN 3
                        WHEN 12 THEN 11
                  END as DATA_TYPE,
                  CONVERT(char(128),CASE WHEN i.DOMAIN_NAME IS NULL THEN DATA_TYPE ELSE DOMAIN_NAME END) as TYPE_NAME,
                  CASE WHEN c.max_length > c.precision THEN c.max_length ELSE c.precision END as PRECISION,
                  c.scale as SCALE
      FROM information_schema.columns i
                        INNER JOIN sys.columns c on (UPPER(i.COLUMN_NAME) = UPPER(c.NAME))
                        INNER JOIN sys.systypes t on (c.user_type_id = t.xusertype)
      WHERE TABLE_NAME = @table_name

Guy Hengel [angelIII / a3]

as the grading comments are not visible to everyone, AFAIK, I copy it here:

<Grading Comments>
I've joined sys.columns and information_schema.columns to get my final solution. For your information, here's the select that provides me backward compatibility with application:
</Grading Comments>

SELECT CONVERT(char(128),i.TABLE_NAME) as TABLE_NAME,
CONVERT(char(128),i.COLUMN_NAME) as COLUMN_NAME,
CASE t.usertype
-- CONVERSIONS QUE EMPRAM DE SQL2000 a SQL2005
WHEN 16 THEN -7
WHEN 20 THEN -4
WHEN 80 THEN -2
WHEN 19 THEN -1
WHEN 1 THEN 1
WHEN 262 THEN 1
WHEN 7 THEN 4
WHEN 10 THEN 2
WHEN 8 THEN 6
WHEN 24 THEN 3
WHEN 259 THEN 3
WHEN 260 THEN 3
WHEN 261 THEN 3
WHEN 12 THEN 11
END as DATA_TYPE,
CONVERT(char(128),CASE WHEN i.DOMAIN_NAME IS NULL THEN DATA_TYPE ELSE DOMAIN_NAME END) as TYPE_NAME,
CASE WHEN c.max_length > c.precision THEN c.max_length ELSE c.precision END as PRECISION,
c.scale as SCALE
FROM information_schema.columns i
INNER JOIN sys.columns c on (UPPER(i.COLUMN_NAME) = UPPER(c.NAME))
INNER JOIN sys.systypes t on (c.user_type_id = t.xusertype)
WHERE TABLE_NAME = @table_name

Open in new window

oldmantrafford

I don't agree with the comment spt_columns_odbc_view does not exist on any of my sql 2005, sql 2000 or sql 7 databases...

When you decompile the stored proc it shows this code:

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,
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,
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

You can clearly see that the sys.spt_columns_odbc_view is there but like you I can't find it either.