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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrandonGalderisiCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.