spt_datatype_info.fixlen in SQL srv 2005? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

spt_datatype_info.fixlen in SQL srv 2005?

Author

Topic

glt101
Starting Member

28 Posts

Posted - 2007-08-16 : 13:07:29

Hi All,

I have a bunch of stored procedures (for use with
Goldmine CE 7). They work fine under SQL server 2000
but fail in SQL 2005 due to changes in the master
database schema. I am porting them over to 2005.

My current problem is a reference to:
master.dbo.spt_datatype_info.fixlen

Is there an equivalent data field in somewhere in
SQL Server 2005?

Cheers,
Geoff

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-16 : 22:31:59

Why put apps sp in master db? Did you check with verdor if Goldmine supports sql2k5?

glt101
Starting Member

28 Posts

Posted - 2007-08-17 : 09:53:31

Hi rmiao,
Yes, Goldmine is supported for SQL 2005. It better be, SQL 2000 is
at end-of-life in April next year.

The apps sps are not in the master database. The sp just contains
a reference to a field in a table that no longer exists in the
master database. The offending code is copied below. It would
appear that I can mostly use sys.types instead, but the behavior
of the WHERE block will need to change because of the reference
to fixlen. I am not sure of how to change it or what the
consequences will be.

Cheers,
Geoff


SELECT
    @datatype = convert (sysname,case
                when t.xusertype > 255 then t.name
                else d.TYPE_NAME
                    end) 

FROM
    sysobjects o,
    master.dbo.spt_datatype_info d,
    systypes t,
    syscolumns c
    LEFT OUTER JOIN syscomments m on c.cdefault = m.id
	AND m.colid = 1

WHERE
	o.name like 'contsupp'
	AND o.id = c.id
	AND t.xtype = d.ss_dtype
	AND c.length = isnull(d.fixlen, c.length)
	AND o.type <> 'P'
	AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0)
	AND c.xusertype = t.xusertype
	AND c.name like @fieldname

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-17 : 23:31:12

If it's goldmain's sp, vendor should fix it before claiming sql2k5 support.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources