Modifing the row that invokes a trigger from within that trigger

When a row gets modified and it invokes a trigger, we would like to be
able to update the row that was modified inside the trigger. This is
(basically) how we are doing it now:

CREATE TRIGGER trTBL ON TBL
FOR UPDATE, INSERT, DELETE
as
update TBL
set fld = 'value'
from inserted, TBL
where inserted.id= TBL.id

...

This work fine but it seems like it could be optimized. Clearly we are
having to scan the entire table again to update the row. But shouldn't
the trigger already know which row invoked it. Do we have to scan the
table again for this row or is their some syntax that allows us to
update the row that invoked the trigger. If not, why. It seems like
this would be a fairly common task. Thanks.--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

The trigger does "know" which rows have been updated, they are in the
inserted recordset.

If you want to eliminate table scans put an index on the "joining"
columns between inserted and the updated table: in your case the [id]
column in the TBL table.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmgkHIechKqOuFEgEQKj0ACg/gThmkK3I5FVs014i96EY1KEqyEAniEQ
TeGhjpWGcoYjj51fomXBxth4
=ilXF
--END PGP SIGNATURE--

nosbtr1 wrote:
> When a row gets modified and it invokes a trigger, we would like to be
> able to update the row that was modified inside the trigger. This is
> (basically) how we are doing it now:
> CREATE TRIGGER trTBL ON TBL
> FOR UPDATE, INSERT, DELETE
> as
> update TBL
> set fld = 'value'
> from inserted, TBL
> where inserted.id= TBL.id
> ...
> This work fine but it seems like it could be optimized. Clearly we are
> having to scan the entire table again to update the row. But shouldn't
> the trigger already know which row invoked it. Do we have to scan the
> table again for this row or is their some syntax that allows us to
> update the row that invoked the trigger. If not, why. It seems like
> this would be a fairly common task. Thanks.|||On 21 Apr 2005 14:11:20 -0700, nosbtr1 wrote:

>When a row gets modified and it invokes a trigger, we would like to be
>able to update the row that was modified inside the trigger. This is
>(basically) how we are doing it now:
>CREATE TRIGGER trTBL ON TBL
>FOR UPDATE, INSERT, DELETE
>as
>update TBL
> set fld = 'value'
>from inserted, TBL
> where inserted.id= TBL.id
>...
>This work fine but it seems like it could be optimized. Clearly we are
>having to scan the entire table again to update the row. But shouldn't
>the trigger already know which row invoked it. Do we have to scan the
>table again for this row or is their some syntax that allows us to
>update the row that invoked the trigger. If not, why. It seems like
>this would be a fairly common task. Thanks.

Hi nosbtr1,

1. There is no "special syntax" to find the rowS (note the plural)
affected by the statement that fired the trigger, other than what you
are already using: the inserted and deleted pseudo-tables.

2. Your statement that this will cause a table scan is incorrect - if
you have a PRIMARY KEY constraint, a UNIQUE constraint or an INDEX
defined for the id column in the table, SQL Server can use an index seek
(followed by a bookmark lookup if the index used is nonclustered).

3. Your trigger won't do anything on delete operations. First because
the inserted pseudotable is always empty on a delete, and second becuase
the rows you are attempting to modify are already removed from the
table.

4. Why not rewrite the UPDATE statement above to the ANSI-compliant
alternative:

UPDATE Tbl
SET Fld = 'value'
WHERE EXISTS (SELECT *
FROM inserted
WHERE inserted.id = Tbl.id)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||You could use an instead of trigger, in this way you actually control
the way the data is updated in the table.

the code is much more complex though|||nosbtr1 (nosbtr1@.yahoo.com) writes:
> When a row gets modified and it invokes a trigger, we would like to be
> able to update the row that was modified inside the trigger. This is
> (basically) how we are doing it now:
> CREATE TRIGGER trTBL ON TBL
> FOR UPDATE, INSERT, DELETE
> as
> update TBL
> set fld = 'value'
> from inserted, TBL
> where inserted.id= TBL.id
> ...
> This work fine but it seems like it could be optimized. Clearly we are
> having to scan the entire table again to update the row. But shouldn't
> the trigger already know which row invoked it.

A trigger fires once per statement, and thus many rows may be affected.

The main performance thing with triggers is that one should be aware of
that the inserted/deleted tables are fairly slow. If you trigger makes
frequent references to these tables, it's a good idea to insert the data
into the pseudo tables into table variables, and work with these instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Missing Transactions - REPOSTED.

Larry,
How about if you add a row manually to S2, does it work
then? If it does, then delete this row, run the merge
agent then add the same row on S1 and see if it
propagates through. Also, check that when the
transactional replication puts the record onto S2 is
there a resulting corresponding record in
msmerge_contents on s2? Finally please check the conflict
tables to confirm there's nothing there.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul,
Thanks for you response. To answer your questions....
I reinitialized the data to make sure everything was in sync. Then I
inserted a row into S2 and ran the merge agent and the row appeared on the
remote server. I delete the record from S2 and it was deleted off the remote
server.
Next I inserted a row, for the same remote location in to S1, ran the agents
(trans on S1 & merge on the remote location and the row appeared. The delete
worked fine also.
Next I ran my app that inserts data in to S1. A total of 263 rows were
inserted into 2 of the tables. I ran the trans agent on S1 and all the new
records were replicated to S2. Next I ran the merge agent on the remote
location and nothing.
I checked msmerge_contents before and after my app ran and it had the same
number of rows.
Finally, I repeated the insert as I did in the first paragraph and nothing.
As I was typing this message, I checked something. The remote locations did
not have merge replication enabled for the dB that was the subscriber. I
enabled them then delete the data from paragraph 3 (from above) and reran my
application and nothing.
I checked the conflict tables on the publisher (S2) and they were empty. Not
sure if they are suppose to exist, but there are no conflict tables on the
subscriber.
To make things even stranger, the changes being made on the remote
location/tables are making their way back to S2
TIA,
Larry...
|||Larry,
somehow, your app is able to force the replication stored procedures to run
on the subscriber without firing the triggers. This shouldn't be possible!
I'd run profiler on the transactional subscriber to see how this is
occurring and to confirm that the merge triggers aren't firing.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I am at such a loss. The manual insertions are not working now either.
This is what is strange...the updates that are being done at remote
locations are making it back to S2, but nothing is making it from S2 to the
remote locations. In fact, I have having to reinitialize the data daily and
the inserts into S2 are not being sent to the remote locations unless the
snapshot agent is run.
I know in trans repl, there is a log reader that gets the pending
transactions. What does this in merge? Could this be the problem?
I have disabled replication for this dB and deleted all the publications. I
recreated one and tried it as a push and a pull subscription and no luck.
HELP!!!!
Larry...
|||Larry,
please script out your publications/subscriptions and
post them up here. Also, please confirm that you are
using the same service pack (sp3/a) on all 3 computers
involved.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||The scrips below are from servers all running 8.00.534 (SP2)
~~~~
Transactional Publication & Subscription from S1 to S2
~~~~
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'EDI_On_RouseMain', @.optname =
N'publish', @.value = N'true'
GO
use [EDI_On_RouseMain]
GO
-- Adding the transactional publication
exec sp_addpublication @.publication = N'EDI_On_RouseMain', @.restricted =
N'false', @.sync_method = N'native', @.repl_freq = N'continuous', @.description
= N'Transactional publication of EDI_On_RouseMain database from Publisher
ROUSEMAIN.', @.status = N'active', @.allow_push = N'true', @.allow_pull =
N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false',
@.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran =
N'false', @.autogen_sync_procs = N'false', @.retention = 336,
@.allow_queued_tran = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_dts = N'false', @.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.logreader_job_name =
N'ROUSEMAIN-EDI_On_RouseMain-2'
exec sp_addpublication_snapshot @.publication =
N'EDI_On_RouseMain',@.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0,
@.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.snapshot_job_name =
N'ROUSEMAIN-EDI_On_RouseMain-EDI_On_RouseMain-2'
GO
exec sp_grant_publication_access @.publication = N'EDI_On_RouseMain', @.login
= N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'EDI_On_RouseMain', @.login
= N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'EDI_On_RouseMain', @.login
= N'EDI'
GO
exec sp_grant_publication_access @.publication = N'EDI_On_RouseMain', @.login
= N'sa'
GO
-- Adding the transactional articles
exec sp_addarticle @.publication = N'EDI_On_RouseMain', @.article =
N'tbl880Detail', @.source_owner = N'dbo', @.source_object = N'tbl880Detail',
@.destination_table = N'tbl880Detail', @.type = N'logbased', @.creation_script =
null, @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x00000000000000F3, @.status = 16, @.vertical_partition = N'false', @.ins_cmd =
N'CALL sp_MSins_tbl880Detail', @.del_cmd = N'CALL sp_MSdel_tbl880Detail',
@.upd_cmd = N'MCALL sp_MSupd_tbl880Detail', @.filter = null, @.sync_object =
null, @.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'EDI_On_RouseMain', @.article =
N'tbl880DetailAllow', @.source_owner = N'dbo', @.source_object =
N'tbl880DetailAllow', @.destination_table = N'tbl880DetailAllow', @.type =
N'logbased', @.creation_script = null, @.description = null, @.pre_creation_cmd
= N'drop', @.schema_option = 0x00000000000000F3, @.status = 16,
@.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_tbl880DetailAllow', @.del_cmd = N'CALL sp_MSdel_tbl880DetailAllow',
@.upd_cmd = N'MCALL sp_MSupd_tbl880DetailAllow', @.filter = null, @.sync_object
= null, @.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'EDI_On_RouseMain', @.article =
N'tbl880Header', @.source_owner = N'dbo', @.source_object = N'tbl880Header',
@.destination_table = N'tbl880Header', @.type = N'logbased', @.creation_script =
null, @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x00000000000000F3, @.status = 16, @.vertical_partition = N'false', @.ins_cmd =
N'CALL sp_MSins_tbl880Header', @.del_cmd = N'CALL sp_MSdel_tbl880Header',
@.upd_cmd = N'MCALL sp_MSupd_tbl880Header', @.filter = null, @.sync_object =
null, @.auto_identity_range = N'false'
GO
exec sp_addarticle @.publication = N'EDI_On_RouseMain', @.article =
N'tbl880HeaderAllow', @.source_owner = N'dbo', @.source_object =
N'tbl880HeaderAllow', @.destination_table = N'tbl880HeaderAllow', @.type =
N'logbased', @.creation_script = null, @.description = null, @.pre_creation_cmd
= N'drop', @.schema_option = 0x00000000000000F3, @.status = 16,
@.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_tbl880HeaderAllow', @.del_cmd = N'CALL sp_MSdel_tbl880HeaderAllow',
@.upd_cmd = N'MCALL sp_MSupd_tbl880HeaderAllow', @.filter = null, @.sync_object
= null, @.auto_identity_range = N'false'
GO
-- Adding the transactional subscription
exec sp_addsubscription @.publication = N'EDI_On_RouseMain', @.article =
N'all', @.subscriber = N'ROUSEONE', @.destination_db = N'EDI', @.sync_type =
N'automatic', @.update_mode = N'read only', @.offloadagent = 0,
@.dts_package_location = N'distributor'
GO
~~~~
Merge publication and push subscription from S2 to Remote location #18
~~~~
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'EDI', @.optname = N'merge publish',
@.value = N'true'
GO
use [EDI]
GO
-- Adding the merge publication
exec sp_addmergepublication @.publication = N'EDI - 18', @.description =
N'Merge publication of EDI database from Publisher ROUSEONE.', @.retention =
14, @.sync_mode = N'native', @.allow_push = N'true', @.allow_pull = N'true',
@.allow_anonymous = N'true', @.enabled_for_internet = N'false',
@.centralized_conflicts = N'true', @.dynamic_filters = N'false',
@.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false',
@.ftp_port = 21, @.ftp_login = N'anonymous', @.conflict_retention = 14,
@.keep_partition_changes = N'true', @.allow_subscription_copy = N'false',
@.allow_synctoalternate = N'false', @.add_to_active_directory = N'false',
@.max_concurrent_merge = 0, @.max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @.publication = N'EDI - 18',@.frequency_type =
4, @.frequency_interval = 1, @.frequency_relative_interval = 1,
@.frequency_recurrence_factor = 0, @.frequency_subday = 1,
@.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date = 0,
@.active_start_time_of_day = 500, @.active_end_time_of_day = 235959,
@.snapshot_job_name = N'ROUSEONE-EDI-EDI - 18-95'
GO
exec sp_grant_publication_access @.publication = N'EDI - 18', @.login =
N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'EDI - 18', @.login =
N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'EDI - 18', @.login = N'sa'
GO
-- Adding the merge articles
exec sp_addmergearticle @.publication = N'EDI - 18', @.article =
N'tbl880Detail', @.source_owner = N'dbo', @.source_object = N'tbl880Detail',
@.type = N'table', @.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
0x000000000000CFF1, @.article_resolver = null, @.subset_filterclause = N'loc =
18', @.vertical_partition = N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'false', @.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true',
@.check_permissions = 0
GO
exec sp_addmergearticle @.publication = N'EDI - 18', @.article =
N'tbl880DetailAllow', @.source_owner = N'dbo', @.source_object =
N'tbl880DetailAllow', @.type = N'table', @.description = null, @.column_tracking
= N'true', @.pre_creation_cmd = N'drop', @.creation_script = null,
@.schema_option = 0x000000000000CFF1, @.article_resolver = null,
@.subset_filterclause = N'loc = 18', @.vertical_partition = N'false',
@.destination_owner = N'dbo', @.auto_identity_range = N'false',
@.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true', @.check_permissions = 0
GO
exec sp_addmergearticle @.publication = N'EDI - 18', @.article =
N'tbl880Header', @.source_owner = N'dbo', @.source_object = N'tbl880Header',
@.type = N'table', @.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
0x000000000000CFF1, @.article_resolver = null, @.subset_filterclause = N'loc =
18', @.vertical_partition = N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'false', @.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true',
@.check_permissions = 0
GO
exec sp_addmergearticle @.publication = N'EDI - 18', @.article =
N'tbl880HeaderAllow', @.source_owner = N'dbo', @.source_object =
N'tbl880HeaderAllow', @.type = N'table', @.description = null, @.column_tracking
= N'true', @.pre_creation_cmd = N'drop', @.creation_script = null,
@.schema_option = 0x000000000000CFF1, @.article_resolver = null,
@.subset_filterclause = N'loc = 18', @.vertical_partition = N'false',
@.destination_owner = N'dbo', @.auto_identity_range = N'false',
@.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true', @.check_permissions = 0
GO
-- Adding the merge subscription
exec sp_addmergesubscription @.publication = N'EDI - 18', @.subscriber =
N'ROUSE18SERVER', @.subscriber_db = N'EDI', @.subscription_type = N'push',
@.subscriber_type = N'local', @.subscription_priority = 0.000000, @.sync_type =
N'automatic', @.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0,
@.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.enabled_for_syncmgr = N'false',
@.offloadagent = 0, @.use_interactive_resolver = N'false'
GO

Missing Row in Excel

All,
We have found a problem with the Excel export today that we cannot
explain at all. We have a plain tabular report that our users
constantly export to Excel.
So far, we haven't had any issues whatsoever, but we have found a very
specific scenario in which the last row doesn't get exported to Excel;
This problem happens only for the Excel export, as all other formats
don't miss the row and the export is 100% sucessful. Bear in mind,
that if we display a different set of results for the same report, the
export is correct. This behaviour is not random, and it can be
repeated time after time.
We would like to know if anybody else as experienced this problem
before, or if there is any known solution.
By the way, we also have seen that when the report doesn't produce any
results at all, that is, only the column headers are displayed, the
export fails with the following error: "Width of excel cell in the
excel sheet exceeded the maximum limit of 1726.5 Points". We know
this problem is documented, but when is it going to be corrected?
Cheers,
Justo.Justo,
I am going to contact you directly about getting a reproduction for the
first issue.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Justo Ruiz" <justoruiz@.hotmail.com> wrote in message
news:d9bb778e.0408200604.5d12c446@.posting.google.com...
> All,
> We have found a problem with the Excel export today that we cannot
> explain at all. We have a plain tabular report that our users
> constantly export to Excel.
> So far, we haven't had any issues whatsoever, but we have found a very
> specific scenario in which the last row doesn't get exported to Excel;
> This problem happens only for the Excel export, as all other formats
> don't miss the row and the export is 100% sucessful. Bear in mind,
> that if we display a different set of results for the same report, the
> export is correct. This behaviour is not random, and it can be
> repeated time after time.
> We would like to know if anybody else as experienced this problem
> before, or if there is any known solution.
> By the way, we also have seen that when the report doesn't produce any
> results at all, that is, only the column headers are displayed, the
> export fails with the following error: "Width of excel cell in the
> excel sheet exceeded the maximum limit of 1726.5 Points". We know
> this problem is documented, but when is it going to be corrected?
> Cheers,
> Justo.|||Bruce/Justo,
Did you resolve this issue? I am experiencing exactly the same
problem...the last row missing only in Excel exports.
Any ideas?
Thanks
TJ
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:%23OqJmTthEHA.704@.TK2MSFTNGP12.phx.gbl...
> Justo,
> I am going to contact you directly about getting a reproduction for the
> first issue.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Justo Ruiz" <justoruiz@.hotmail.com> wrote in message
> news:d9bb778e.0408200604.5d12c446@.posting.google.com...
> > All,
> >
> > We have found a problem with the Excel export today that we cannot
> > explain at all. We have a plain tabular report that our users
> > constantly export to Excel.
> >
> > So far, we haven't had any issues whatsoever, but we have found a very
> > specific scenario in which the last row doesn't get exported to Excel;
> > This problem happens only for the Excel export, as all other formats
> > don't miss the row and the export is 100% sucessful. Bear in mind,
> > that if we display a different set of results for the same report, the
> > export is correct. This behaviour is not random, and it can be
> > repeated time after time.
> >
> > We would like to know if anybody else as experienced this problem
> > before, or if there is any known solution.
> >
> > By the way, we also have seen that when the report doesn't produce any
> > results at all, that is, only the column headers are displayed, the
> > export fails with the following error: "Width of excel cell in the
> > excel sheet exceeded the maximum limit of 1726.5 Points". We know
> > this problem is documented, but when is it going to be corrected?
> >
> > Cheers,
> >
> > Justo.
>

Missing Row in CSV Export

Hi Everyone,
I'm having an issue with a row of data missing when exporting to CSV.
The report structure is as follows:
Group 1 Header - Grouping on State (Displays the State)
Group 2 Header - Grouping on Customer ID (Displays a summary for each
customer)
Detail Row - <This Row has Been Removed>
Group 2 Footer - <This Row has Been Removed>
Group 1 Footer - Displays totals for the state
The report renders fine to PDF and will export all data to XML. When I
export to CSV, the first row is always missing. I have two reports with
similar structure and suffers from the same issue.
Any help would be greatly appreciated.
Thanks
ChadIf you add rectangle/ blank image / blank label above your report, CSV export
will work.
"Chad McKee" wrote:
> Hi Everyone,
> I'm having an issue with a row of data missing when exporting to CSV.
> The report structure is as follows:
> Group 1 Header - Grouping on State (Displays the State)
> Group 2 Header - Grouping on Customer ID (Displays a summary for each
> customer)
> Detail Row - <This Row has Been Removed>
> Group 2 Footer - <This Row has Been Removed>
> Group 1 Footer - Displays totals for the state
> The report renders fine to PDF and will export all data to XML. When I
> export to CSV, the first row is always missing. I have two reports with
> similar structure and suffers from the same issue.
> Any help would be greatly appreciated.
> Thanks
> Chad
>|||We have posted a hotfix that includes a fix for this issue at
http://www.microsoft.com/downloads/details.aspx?FamilyId=7FFE50D4-AFF8-4C1E-9609-6798190C2D58&displaylang=en.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chad McKee" <cmckee@.startech-comp.com> wrote in message
news:%2340961OeFHA.3048@.TK2MSFTNGP12.phx.gbl...
> Hi Everyone,
> I'm having an issue with a row of data missing when exporting to CSV. The
> report structure is as follows:
> Group 1 Header - Grouping on State (Displays the State)
> Group 2 Header - Grouping on Customer ID (Displays a summary for each
> customer)
> Detail Row - <This Row has Been Removed>
> Group 2 Footer - <This Row has Been Removed>
> Group 1 Footer - Displays totals for the state
> The report renders fine to PDF and will export all data to XML. When I
> export to CSV, the first row is always missing. I have two reports with
> similar structure and suffers from the same issue.
> Any help would be greatly appreciated.
> Thanks
> Chad|||> The report renders fine to PDF and will export all data to XML. When I
> export to CSV, the first row is always missing. I have two reports with
> similar structure and suffers from the same issue.
Hmm, haven't looked into the problem but maybe the CSV export is getting
confused with header rows. In case you just can't find a work-around, a
custom CSV rendering extension might be the solution. I imagine that
shouldn't be too hard to accomplish.
Christoph|||Thanks to everyone who replied. The Hotfix fixed the issue.
Thanks
Chad
Brian Welcker [MSFT] wrote:
> We have posted a hotfix that includes a fix for this issue at
> http://www.microsoft.com/downloads/details.aspx?FamilyId=7FFE50D4-AFF8-4C1E-9609-6798190C2D58&displaylang=en.
>

Missing Last Row in Excel Output

I am experiencing an issue when I try to export a few of my reports to Excel.
Sometimes when I export to Excel I lose the last row. My reports are very
simple and do not include anything other than a table. I can export it one
day and all of the rows will be included and the next day I will lose the
last row. This is causing confusion among my end users because many of them
also receive a pdf document with the same information; however the pdf is
always correct.
Would someone from Microsoft please let me know if this is a known bug
because I can see nothing wrong with my report.
Thank you,
TimTim,
I am experiencing the same behaviour. Did you ever found out what the
issue was? If so, please share.
One of my report has 161 rows (including headers). When I export this
report to Excel, the last row is missing. Export to other formats work
fine. If I limit the number of rows in the report to 160, all of them
show up in the exported excel. Is there a limit on number of rows
exported on a tab to excel? How do I reset it?
Thanks|||Try adding an additional footer row to the table you are trying to export.
In the first cell enter a value of =" ". This isn't a fix but at least will
you will be able export the table with all of your data.
Tim
"kkaps" wrote:
> Tim,
> I am experiencing the same behaviour. Did you ever found out what the
> issue was? If so, please share.
> One of my report has 161 rows (including headers). When I export this
> report to Excel, the last row is missing. Export to other formats work
> fine. If I limit the number of rows in the report to 160, all of them
> show up in the exported excel. Is there a limit on number of rows
> exported on a tab to excel? How do I reset it?
> Thanks
>|||I had the same issue with a report that contains a matrix object...the
last row of matrix data was being dropped upon Export to Excel. I
simply put a small, empty textbox object directly below my matrix
object...this fixed the problem!

Missing guide

I have a database I wish to replicate, therefore i have set up a
transactional log replication..
But now when I add a row on the main database, this is NOT replicated to
the subscriber...
Then I inizilize a snapshot, (this will drop table and replicate all agian)
the row still do not come over...
What am I doing wrong.. is there a simple guide to how to set it up!!
Please help fast..
MvH
Klaus L Jensen
Ramboll Informatik
By definition this should work. Check to make sure that the log reader agent
is running. If it is, then check to see if the distribution agent is
running.
Also in your distribution database run sp_browsereplcmds to see if you see
this transaction there.
Do you have any filters on your table?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Klaus L Jensen" <NoSpam_News@.Spider_NoSpam.dk> wrote in message
news:%23WkFFuCZEHA.716@.TK2MSFTNGP11.phx.gbl...
> I have a database I wish to replicate, therefore i have set up a
> transactional log replication..
> But now when I add a row on the main database, this is NOT replicated to
> the subscriber...
> Then I inizilize a snapshot, (this will drop table and replicate all
agian)
> the row still do not come over...
> What am I doing wrong.. is there a simple guide to how to set it up!!
> Please help fast..
> MvH
> Klaus L Jensen
> Ramboll Informatik

Missing first row

Why is is in SSMSE or through code in VB.NET when running a query on a linked server that is an Excel spreadsheet is the first row not returned?

My spreadsheet has 320 rows with no column headings.

My select query within SSMSE returns only 319, the first row is ommitted, I beleive treated as a column heading.

If I insert a row at the very beginnning and enter any jibberish this row is ommitted and I get all my data.

Now I dont really want to have to tell my users that to get the import function of my app to work correctly they have to do this?

Is there a way to configure it to not treat first row as headings?

Thanks

http://support.microsoft.com/kb/257819/en-gb