spt_datatype_info table in 2005

Hot Topic

SSIS runtime components Install
how do i have alternative background color
Installing Right Components for Sql Server Express
Images aren't being displayed in deployed Reports
Network access to Sql Server 2005 Express
Registering assembly in SQL Server 2005
Migration Maint Plns SQL Server 2005 ==> SQL Server 2005
How xml could help us with our ETL processes on daily-basis?
SQL express ?
How to not render blank reports?

SQL Server

this should be easy but it's not.
Compilation/sec is more on production where recompilation / sec is zero
RS2005's own SPs to add users to RS
DTS_E_COLUMNDATAOVERFLOWDISKIOBUFFER Error
MDX Parameter
ReportService2005 and ReportExecution2005
Add a variable from custom component
SSIS - how to set ServerName and DatabaseName at run time inside FOREACH LOOP?
image resource returned in code
SQL Server Management Studio Express CTP?

spt_datatype_info table in 2005

Hi,

We are converting to sql 2005 and it appears that the spt_datatype_info table does not exist in sql 2005. I use column SQL_DATA_TYPE in code from this table.

Does anybody know what view the information that use to be in spt_datatype_info is in

Thanks

Answer this question

spt_datatype_info table in 2005

  • nono92_b

    CREATE TABLE [#spt_datatype_info] (
    [TYPE_NAME] [sysname] NOT NULL ,
    [DATA_TYPE] [smallint] NOT NULL ,
    [PRECISION] [int] NULL ,
    [LITERAL_PREFIX] [varchar] (32) COLLATE Latin1_General_BIN NULL ,
    [LITERAL_SUFFIX] [varchar] (32) COLLATE Latin1_General_BIN NULL ,
    [CREATE_PARAMS] [varchar] (32) COLLATE Latin1_General_BIN NULL ,
    [NULLABLE] [smallint] NOT NULL ,
    [CASE_SENSITIVE] [smallint] NOT NULL ,
    [SEARCHABLE] [smallint] NOT NULL ,
    [UNSIGNED_ATTRIBUTE] [smallint] NULL ,
    [MONEY] [smallint] NOT NULL ,
    [AUTO_INCREMENT] [smallint] NULL ,
    [LOCAL_TYPE_NAME] [sysname] NULL ,
    [MINIMUM_SCALE] [smallint] NULL ,
    [MAXIMUM_SCALE] [smallint] NULL ,
    [SQL_DATA_TYPE] [smallint] NOT NULL ,
    [SQL_DATETIME_SUB] [smallint] NULL ,
    [NUM_PREC_RADIX] [int] NULL ,
    [INTERVAL_PRECISION] [smallint] NULL ,
    [USERTYPE] [smallint] NULL
    )
    INSERT #spt_datatype_info EXEC sp_datatype_info

    ALTER TABLE [#spt_datatype_info] ADD [ss_dtype] [tinyint]

    UPDATE #spt_datatype_info SET ss_dtype = xtype FROM systypes WHERE [name] = CASE
    WHEN charindex('(', [TYPE_NAME]) > 0
    THEN LEFT([TYPE_NAME], charindex('(', [TYPE_NAME])-1)
    WHEN charindex(' ', [TYPE_NAME]) > 0
    THEN LEFT([TYPE_NAME], charindex(' ', [TYPE_NAME])-1)
    ELSE [TYPE_NAME] END

  • mluckham

    There is no equivalent in sp_datatype_info. The ss_dtype is simply the SQL Server type id of the type. You can get the equivalent type information with the system_type_id column of sys.types with 2005.

  • asureus

    The view is used by the ODBC client driver and is no longer exposed directly. In order to get the same information, you can use sp_datatype_info (http://msdn2.microsoft.com/en-us/library/ms173819.aspx) which is also in SQL Server 2000. The SQL_DATA_TYPE column is the ODBC type id. If you want the SQL Server Type Id, use sys.types.



  • Upsetian

    Mark,

    I am trying to use sp_datatype_info SP instead of spt_datatype_info. I would like to know if there is an equivalent to "ss_dtype" (from spt_datatype_info) in sp_datatype_info SP. Apparently, when this SP is created from spt_datatype_info, this field was ignored.

    Thanks in Advance
  • spt_datatype_info table in 2005
Answer this question