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
beplluisAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
spt_columns_odbc_view does not exist on any of my sql 2005, sql 2000 or sql 7 databases...
so, these must be "normal" views that have been created on your databases somehow.
check out the code of those, and migrate them to sql 2005...

note: you might consider checking the sys.columns view, I think almost all the information should be there...
0
 
BrandonGalderisiConnect With a Mentor Commented:
Or information_schema.columns.  Depending on the format you desire.
0
 
beplluisAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
oldmantraffordCommented:
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.
0
All Courses

From novice to tech pro — start learning today.