Creating an Audit trail on a table using a trigger

Hi,
This is kind of following on from my last couple of posts regarding Identity
columns and so on.
Basically, I want to ensure that for a particular table, every row has a
numeric reference. This reference must be unique and gapless. Ideally it
should order in the sequence of the records being inserted however this
isn't an absolute requirement.
From my (limited) understanding of SQLS, I think I can achieve this with a
FOR INSERT Trigger - in that the trigger is fired every time a row is
inserted and the trigger is the same transaction as the initial insert hence
I avoid any concurrency issues.
However I'm not completely sure how to achieve this. I think that my trigger
should be along the lines of this...
CREATE TRIGGER AssignAuditReference ON tblBooking
FOR INSERT
AS
DECLARE @.Ref int
--Get the highest reference and add one.
SELECT @.Ref = isnull(max(job_id),0)+1 from tblBooking
--Update the inserted row to have a booking_referecen of the new reference
obtained above.
Update tblBooking
SET Booking_Reference = @.ref
WHERE Booking_ID = INSERTED.Booking_ID
However I'm getting problems with the INSERTED table not being recognised.
I understood that the INSERTED table contained the row that the insert that
started the trigger inserted.
Two questions:
1. Where am I going wrong with my trigger. Have I misunderstood some key
point of using triggers.
2. Is this the right approach to achieve what I am after? Are there any
better approaches...Hi Chris,
You have to mention the Inserted Table in your Update Query
Update tblBooking
SET tblBooking.Booking_Reference = @.Ref
FROM tblBooking
INNER JOIN INSERTED
ON (tblBooking.Booking_ID= INSERTED.Booking_ID)
Because of the lack between getting the @.Ref-Value and writing it in the
table i would prefer an inline Query and Update
Update tblBooking
SET tblBooking.Booking_Reference = NewJobIdTable.NewJobId
FROM tblBooking,
(
Select ISNULL(MAX(job_id),0)+1 AS NewJobId from tblBooking
) NewJobIdTable
INNER JOIN INSERTED
ON (tblBooking.Booking_ID= INSERTED.Booking_ID)
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Chris Strug" <hotmail@.solace1884.com> schrieb im Newsbeitrag
news:eBcd9GOQFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is kind of following on from my last couple of posts regarding
> Identity
> columns and so on.
> Basically, I want to ensure that for a particular table, every row has a
> numeric reference. This reference must be unique and gapless. Ideally it
> should order in the sequence of the records being inserted however this
> isn't an absolute requirement.
> From my (limited) understanding of SQLS, I think I can achieve this with a
> FOR INSERT Trigger - in that the trigger is fired every time a row is
> inserted and the trigger is the same transaction as the initial insert
> hence
> I avoid any concurrency issues.
> However I'm not completely sure how to achieve this. I think that my
> trigger
> should be along the lines of this...
> CREATE TRIGGER AssignAuditReference ON tblBooking
> FOR INSERT
> AS
> DECLARE @.Ref int
> --Get the highest reference and add one.
> SELECT @.Ref = isnull(max(job_id),0)+1 from tblBooking
> --Update the inserted row to have a booking_referecen of the new
> reference
> obtained above.
> Update tblBooking
> SET Booking_Reference = @.ref
> WHERE Booking_ID = INSERTED.Booking_ID
> However I'm getting problems with the INSERTED table not being recognised.
> I understood that the INSERTED table contained the row that the insert
> that
> started the trigger inserted.
> Two questions:
> 1. Where am I going wrong with my trigger. Have I misunderstood some key
> point of using triggers.
> 2. Is this the right approach to achieve what I am after? Are there any
> better approaches...
>|||Syntactically, your UPDATE statement is missing the FROM clasue:
UPDATE tblBooking
SET Booking_Reference = @.ref
FROM tblBooking , INSERTED
WHERE Booking_ID = INSERTED.Booking_ID
1) This will FAIL if more than one row is inserted - not a good idea
for maintaining an audit trail. 2) I don't see what advantage this has
over the more concise and reliable solution(s) already discussed in
your earlier threads. For example:
INSERT INTO tblBooking (booking_reference, x, y, z, ...)
SELECT COALESCE(MAX(booking_reference),0)+1, 'foo', 'bar', 1234, ...
FROM tblBooking
IMO an incrementing counter is a poor way to maintain an audit trail
anyway. Why not just store the CURRENT_TIMESTAMP on each row and then
preserve the history of changes to rows? This is easy to do in triggers
or in your data access code and doesn't suffer the inevitable and
serious blocking problems that your approach implies.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1113478283.991829.222620@.g14g2000cwa.googlegroups.com...
> Syntactically, your UPDATE statement is missing the FROM clasue:
> UPDATE tblBooking
> SET Booking_Reference = @.ref
> FROM tblBooking , INSERTED
> WHERE Booking_ID = INSERTED.Booking_ID
> 1) This will FAIL if more than one row is inserted - not a good idea
> for maintaining an audit trail. 2) I don't see what advantage this has
> over the more concise and reliable solution(s) already discussed in
> your earlier threads. For example:
> INSERT INTO tblBooking (booking_reference, x, y, z, ...)
> SELECT COALESCE(MAX(booking_reference),0)+1, 'foo', 'bar', 1234, ...
> FROM tblBooking
> IMO an incrementing counter is a poor way to maintain an audit trail
> anyway. Why not just store the CURRENT_TIMESTAMP on each row and then
> preserve the history of changes to rows? This is easy to do in triggers
> or in your data access code and doesn't suffer the inevitable and
> serious blocking problems that your approach implies.
> --
> David Portas
> SQL Server MVP
> --
>
First of all thanks to both David and Jens for their replies.
Apologies for repeating myself, I just want to make sure that I understand
what I'm doing rather than repeating it parrot fashion into my database.
Regarding the trigger, I was under the impression that the trigger would
occur for every new row, I gather that it in fact applies to every INSERT.
Ahh... That makes things clearer.
Regards the actual implementation (TIMESTAMP vs. numeric reference),
unfortunately this is out of my hands. I've been informed that this a is a
non negotiatable requirement. What can you do?
if I may ask one more question, assuming that I did attempt to implement my
apprioach using triggers, could you expand on the blocking problems that you
would expect me to face?
Anyway, thank you once again for taking the time to help me, I do appreciate
it.
Regards
Chris.

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

sql

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

Creating a Table Of Contents

I would like to create a table of contents on the first page in a report I'm working on. I've been looking around for a couple of days now and have come up with nothing. I'm wondering if I can hook into the document map to create a custom TOC, if not how else might I be able to do this. I'm currently using the June CTP. Any help would be appreciated.
Thanks,
Brian Schmidt

Did you find a solution? I am also interested in creating a TOC to be printed from PDF.

Do you know if Reporting Services for SQL Server 2005 has the functionality to create a Table of Contents in a report?

Thanks,

Toby

|||Did not find a solution - the answer seems to be that you can't do it without running the report twice (once to create the pagination, then again to put the toc in (which hypothetically could change the pagination)), and write some custom specific code to put the toc in the doc.

I ended up just making sure there were bookmarks where I needed them so that you could at least jump to parts using the bookmark feature of acrobat reader. Works pretty well as long as the user reads the report interactively online. Not so good for a printed hardcopy.|||I am very new to SQL Reporting and would like to create a table of contents. You reference that you are currently using the June CTP. Could you please elaborate? Any help is greatly appreciated. It seems the table of contents is not very easy to automate. Thanks again!|||

Reporting Services does not support a table of contents for a report.

You can work around using a little trickery: You can add a query to your report that returns all of your group names and the number of rows for each group. Then design your report to include only a certain number of lines on a physical page. Then you would be able to carefully craft a report that shows a table at the beginning with the group names and an expected page number. Of course the page number would be dependent on the size of paper you're printing on. Not an ideal solution but it would get the job done.

As a previous post said, you can generally get around this by using the Document Map feature of the report. It works great interactively and is included when exporting to PDF.

Hope that helps,

-Lukasz

|||

Wouldn't putting together an index at the end of the report be easier and work better? I have a large order guide that I am working on via Reporting Services, and I have come to the conclusion that an index might be easier to implement. If I get it to work decently, I'll post an explanation, if desired.

What I think it can boil down to is supressing the page numbers in the footer (or header) after the "main" report, and after everything is hardcopy, move the un-numbered index to the front to work as a table of contents.

Thanks!

Curtis

|||

This is how I overcame my Table of contents issue. I used the following code in my SELECT statement. This allowed me to determine what page x item will be on. I do not know if this will be a fix all for everyone interested, but it worked well for me!

SELECT

,...

, ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

, ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

...

I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

|||

I have been working on this table of content thing for a week now. I have somehow found a solution for that. You can write an assembly containing a function which would take 2 paramenters the page number and your group name (Which needs to be on the table of contents) and write them to an xml file or a database table. Once you are done with the assembly you can reference that assembly in you rdl file and pass that the page number and the current group on the page to that function. You will have a complete table of contents in form of an xml or database table whatever you select.

I have done this so far and now only thing left is to display that TOC on the original report again. I m wroking on it... so far this is what i tried... i added my TOC data set to a new report and made my original report a sub report in that report. Now there are 2 issues. (1) The sub report wont show the page numbers. (2) I will have to run the subreport once before the main report so that it writes the TOC values to the xml file or table which can be accessed then in the main report. I think it can be done on windows form or a web form to call that subreport as an independent report somehow hidden from user, but i would be more interested to do all this stuff from the report if possible.

Any body have some better idea to overcome the problems which i m facing.

Thanx!

creating a full text index on sql2k

I need to set up a couple of full text indexes on a sql 2k database, but no
matter what I do, the "full text index " options remain greyed out in
Enterprise manager.
The table has a primary key and a couple of indexes.
I've tried a basic CREATE statement
CREATE FULLTEXT INDEX ON navigate_items
KEY INDEX PK_navigate_items
that returns an error
05/03/2007 12:11:33: SQL Server Database Error: Line 1: Incorrect syntax
near 'FULLTEXT'.Please mention the key as well.
Take a look into below sample.
The following example creates a full-text index on the
HumanResources.JobCandidate table.
CREATE UNIQUE INDEX ui_ukJobCand ON
HumanResources.JobCandidate(JobCandidateID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) KEY INDEX
ui_ukJobCand;
GO
ThanksHari
"s_m_b" <smb20002ns@.hotmail.com> wrote in message
news:Xns98EA7CF797E91smb2000nshotrmailco
m@.207.46.248.16...
>I need to set up a couple of full text indexes on a sql 2k database, but no
> matter what I do, the "full text index " options remain greyed out in
> Enterprise manager.
> The table has a primary key and a couple of indexes.
> I've tried a basic CREATE statement
> CREATE FULLTEXT INDEX ON navigate_items
> KEY INDEX PK_navigate_items
> that returns an error
> 05/03/2007 12:11:33: SQL Server Database Error: Line 1: Incorrect syntax
> near 'FULLTEXT'.
>

creating a full text index on sql2k

I need to set up a couple of full text indexes on a sql 2k database, but no
matter what I do, the "full text index " options remain greyed out in
Enterprise manager.
The table has a primary key and a couple of indexes.
I've tried a basic CREATE statement
CREATE FULLTEXT INDEX ON navigate_items
KEY INDEX PK_navigate_items
that returns an error
05/03/2007 12:11:33: SQL Server Database Error: Line 1: Incorrect syntax
near 'FULLTEXT'.
Please mention the key as well.
Take a look into below sample.
The following example creates a full-text index on the
HumanResources.JobCandidate table.
CREATE UNIQUE INDEX ui_ukJobCand ON
HumanResources.JobCandidate(JobCandidateID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) KEY INDEX
ui_ukJobCand;
GO
ThanksHari
"s_m_b" <smb20002ns@.hotmail.com> wrote in message
news:Xns98EA7CF797E91smb2000nshotrmailcom@.207.46.2 48.16...
>I need to set up a couple of full text indexes on a sql 2k database, but no
> matter what I do, the "full text index " options remain greyed out in
> Enterprise manager.
> The table has a primary key and a couple of indexes.
> I've tried a basic CREATE statement
> CREATE FULLTEXT INDEX ON navigate_items
> KEY INDEX PK_navigate_items
> that returns an error
> 05/03/2007 12:11:33: SQL Server Database Error: Line 1: Incorrect syntax
> near 'FULLTEXT'.
>

creating a full text index on sql2k

I need to set up a couple of full text indexes on a sql 2k database, but no
matter what I do, the "full text index " options remain greyed out in
Enterprise manager.
The table has a primary key and a couple of indexes.
I've tried a basic CREATE statement
CREATE FULLTEXT INDEX ON navigate_items
KEY INDEX PK_navigate_items
that returns an error
05/03/2007 12:11:33: SQL Server Database Error: Line 1: Incorrect syntax
near 'FULLTEXT'.Please mention the key as well.
Take a look into below sample.
The following example creates a full-text index on the
HumanResources.JobCandidate table.
CREATE UNIQUE INDEX ui_ukJobCand ON
HumanResources.JobCandidate(JobCandidateID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) KEY INDEX
ui_ukJobCand;
GO
ThanksHari
"s_m_b" <smb20002ns@.hotmail.com> wrote in message
news:Xns98EA7CF797E91smb2000nshotrmailcom@.207.46.248.16...
>I need to set up a couple of full text indexes on a sql 2k database, but no
> matter what I do, the "full text index " options remain greyed out in
> Enterprise manager.
> The table has a primary key and a couple of indexes.
> I've tried a basic CREATE statement
> CREATE FULLTEXT INDEX ON navigate_items
> KEY INDEX PK_navigate_items
> that returns an error
> 05/03/2007 12:11:33: SQL Server Database Error: Line 1: Incorrect syntax
> near 'FULLTEXT'.
>