Function Returning error during compilation.....

Hi ,

I am creating a function which is going to return a table. The Code ofr the function is as follows...
===============================
Create function udf_qcard (@.cg1 varchar(25)) returns @.rec_card table (t_cusip varchar(10),t_data varchar(70))
AS
begin
declare @.t1_sys char(10),@.t1_all varchar(11)
declare @.temp_qcard table (tdata varchar(11) collate SQL_Latin1_General_CP1_CS_AS)
if (substring(@.cg1,1,2)='Q$')
set @.cg1 = (select substring(@.cg1,3,len(@.cg1)) where substring(@.cg1,1,2)='Q$')
DECLARE c1 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @.cg1 and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY
insert into @.temp_qcard values(@.cg1)
OPEN C1
FETCH NEXT FROM c1 INTO @.t1_sys,@.t1_all
WHILE @.@.FETCH_STATUS = 0
BEGIN

insert into @.temp_qcard values(@.t1_all)

declare @.t2_sys char(10),@.t2_all varchar(10)
DECLARE c2 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @.t1_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

begin
OPEN C2
FETCH NEXT FROM c2 INTO @.t2_sys,@.t2_all
WHILE @.@.FETCH_STATUS = 0
BEGIN
insert into @.temp_qcard values(@.t2_all)

declare @.t3_sys char(10),@.t3_all varchar(10)
DECLARE c3 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @.t2_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

begin

OPEN C3
FETCH NEXT FROM c3 INTO @.t3_sys,@.t3_all
WHILE @.@.FETCH_STATUS = 0
BEGIN
insert into @.temp_qcard values(@.t3_all)
FETCH NEXT FROM c3 INTO @.t3_sys,@.t3_all
end
end
close c3
deallocate c3
FETCH NEXT FROM c2 INTO @.t2_sys,@.t2_all
end
end
close c2
DEALLOCATE c2

FETCH NEXT FROM c1 INTO @.t1_sys,@.t1_all
END

CLOSE c1
DEALLOCATE c1
Insert @.rec_card select groups_q+groups_cusip,groups_data from tbl_groups
where groups_system in (select tdata from @.temp_qcard) and groups_seq>=1 and groups_alldata not like 'Q$%' order by groups_alldata

RETURN
END
==========================

While compiling this I am getting the Below error ...
==================
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 10
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 23
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 35
Mixing old and new syntax to specify cursor options is not allowed.
=================

Can Anyone please help me how to resolve this issue...

Thanks with Regards.

-Mohit.Try changing the declaration of all your cursors like this

DECLARE c1 SCROLL CURSOR READ_ONLY FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @.cg1 and groups_alldata like 'Q$%' and groups_seq>=1

Dont write "FOR READ_ONLY" at the end of the DECLARE. Instead, write it before the "FOR select...."

Full-text query (Freetexttable) returning duplicate rows

We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:

Code Snippet

SELECT *

FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL

ORDER BY RANK DESC

vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.

This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.

I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?

What you do get when you use LIKE.

Code Snippet

SELECT *

FROM vwSubtable

WHERE TitleSearch LIKE '%Across five Aprils%'

If you get dupes then take a closer look at your data.

|||

My guess is that your fulltext catalog has become slightly corrupt maybe due to a large amount of inserts/deletes/updates to the underlying table.

Have you rebuilt your catalog recently? I'd schedule a rebuild of the catalog which should sort out the problem.

HTH!

|||Thanks! Rebuilding the catalog fixed the problem.

Full-text query (Freetexttable) returning duplicate rows

We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:

Code Snippet

SELECT *

FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL

ORDER BY RANK DESC

vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.

This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.

I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?

What you do get when you use LIKE.

Code Snippet

SELECT *

FROM vwSubtable

WHERE TitleSearch LIKE '%Across five Aprils%'

If you get dupes then take a closer look at your data.

|||

My guess is that your fulltext catalog has become slightly corrupt maybe due to a large amount of inserts/deletes/updates to the underlying table.

Have you rebuilt your catalog recently? I'd schedule a rebuild of the catalog which should sort out the problem.

HTH!

|||Thanks! Rebuilding the catalog fixed the problem.

Full-text query (Freetexttable) returning duplicate rows

We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:

Code Snippet

SELECT *

FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL

ORDER BY RANK DESC

vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.

This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.

I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?

What you do get when you use LIKE.

Code Snippet

SELECT *

FROM vwSubtable

WHERE TitleSearch LIKE '%Across five Aprils%'

If you get dupes then take a closer look at your data.

|||

My guess is that your fulltext catalog has become slightly corrupt maybe due to a large amount of inserts/deletes/updates to the underlying table.

Have you rebuilt your catalog recently? I'd schedule a rebuild of the catalog which should sort out the problem.

HTH!

|||Thanks! Rebuilding the catalog fixed the problem.