getTYPE NOT WORKING ON MSDE?? - SQL Server Forums

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 2000 Forums

MSDE (2000)

getTYPE NOT WORKING ON MSDE??

Author

Topic

christer100
Starting Member

6 Posts

Posted - 2005-11-13 : 09:33:07

I am getting this error on MSDE SP4, but it is working on SQL 2000 Standard/Developer/Enterprise SP3A.

Invalid object name 'getType'.

DECLARE
@v_accrec VARCHAR(10),
@v_type VARCHAR(10),
@v_old_accrec VARCHAR(10),
@v_string VARCHAR(400),
@v_ml VARCHAR(10)

UPDATE ACCOUNTINFO
SET TYPEML=''

set @v_ml= (SELECT MLTYPE
FROM dbo.ProgramConf)

DECLARE c1 CURSOR FOR SELECT rec2,
type
FROM getType(@v_ml) a
ORDER BY a.rec2

OPEN c1
SET @v_old_accrec = 0
SET @v_accrec = 0
SET @v_type = ''
SET @v_string = ''

FETCH NEXT FROM c1 INTO @v_accrec, @v_type

WHILE @@FETCH_STATUS = 0
BEGIN
IF (@v_old_accrec != @v_accrec)
BEGIN
UPDATE AccountInfo
SET typeml = case when @v_string is not null
then substring(@v_string, 1, len(@v_string) - 1) else NULL end
WHERE accrec = @v_old_accrec
SET @v_string = ''
SET @v_string = @v_string + @v_type + '|'
SET @v_type = ''
SET @v_old_accrec = @v_accrec
SET @v_accrec = 0
END
ELSE
BEGIN
SET @v_string = @v_string + @v_type + '|'
END

FETCH NEXT FROM c1 INTO @v_accrec, @v_type
END
UPDATE AccountInfo
SET typeml = case when @v_string is not null
then substring(@v_string, 1, len(@v_string) - 1) else NULL end
WHERE accrec = @v_old_accrec
CLOSE c1
DEALLOCATE c1

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-13 : 10:31:15

What's getType.
Is it a udf that you need to create on oyur new system?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

christer100
Starting Member

6 Posts

Posted - 2005-11-13 : 15:56:23

No, it is not a user definined function. I don't know where it is coming from, but apparently it is somewhere in SQL 2000, but not in MSDE. Could it be a system function just like getDate(), but where is it stored so I can copy it to MSDE. It is not a user defined function and I have never created it in the first place. It just exists in SQL 2000 but not MSDE. I have tried 2 different SQL 2000 servers and two different MSDE servers and the same problem on MSDE (but not SQL 2000)

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-13 : 16:58:36

It's not a system function. It's a method on some objects but not a tsql statement.
On the database where it works try

select * from sysobjects where name = 'getType'
sp_helptext getType

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

christer100
Starting Member

6 Posts

Posted - 2005-11-13 : 22:26:45

Thanks, I figured out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources