I'm trying to add a column to my peer to peer replication. According to the
help I've found:
Adding Columns
a.. To add a new column to a table and include that column in an existing
publication, execute ALTER TABLE <Table> ADD <Column>. By default, the
column is then replicated to all Subscribers. The column must allow NULL
values or include a default constraint.
b.. To add a new column to a table and not include that column in an
existing publication, disable the replication of schema changes, and then
execute ALTER TABLE <Table> ADD <Column>.
c.. To include an existing column in an existing publication, use
sp_articlecolumn (Transact-SQL), sp_mergearticlecolumn (Transact-SQL), or
the Publication Properties - <Publication> dialog box.
For more information, see How to: Define and Modify a Column Filter
(Replication Transact-SQL Programming) and How to: Define and Modify a
Column Filter (SQL Server Management Studio). This will require
subscriptions to be reinitialized.
d.. Adding an identity column to a published table is not supported,
because it can result in non-convergence when the column is replicated to
the Subscriber. The values in the identity column at the Publisher depend on
the order in which the rows for the affected table are physically stored.
The rows might be stored differently at the Subscriber; therefore the value
for the identity column can be different for the same rows.
However, while I am able to add the column to the publisher and it is
replicated to the subscriber, I cannot update this column at the subscriber
and have it replicate to the publisher. However when I update at the
publisher it IS pushed to the subscriber.
Outside of the new column, my updates bi-directionally work. Can someone
tell me how I can get updates on newly added columns to be bi-directional?
Thanks,
Adam P. Cassidy
Did you quiesce the system as outlined in this link?
http://msdn2.microsoft.com/en-us/library/ms147385.aspx
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Adam Patrick Cassidy" <apcassidy@.aol.com> wrote in message
news:eoBB5en4GHA.1200@.TK2MSFTNGP02.phx.gbl...
> I'm trying to add a column to my peer to peer replication. According to
> the help I've found:
> Adding Columns
> a.. To add a new column to a table and include that column in an existing
> publication, execute ALTER TABLE <Table> ADD <Column>. By default, the
> column is then replicated to all Subscribers. The column must allow NULL
> values or include a default constraint.
> b.. To add a new column to a table and not include that column in an
> existing publication, disable the replication of schema changes, and then
> execute ALTER TABLE <Table> ADD <Column>.
> c.. To include an existing column in an existing publication, use
> sp_articlecolumn (Transact-SQL), sp_mergearticlecolumn (Transact-SQL), or
> the Publication Properties - <Publication> dialog box.
> For more information, see How to: Define and Modify a Column Filter
> (Replication Transact-SQL Programming) and How to: Define and Modify a
> Column Filter (SQL Server Management Studio). This will require
> subscriptions to be reinitialized.
> d.. Adding an identity column to a published table is not supported,
> because it can result in non-convergence when the column is replicated to
> the Subscriber. The values in the identity column at the Publisher depend
> on the order in which the rows for the affected table are physically
> stored. The rows might be stored differently at the Subscriber; therefore
> the value for the identity column can be different for the same rows.
> However, while I am able to add the column to the publisher and it is
> replicated to the subscriber, I cannot update this column at the
> subscriber and have it replicate to the publisher. However when I update
> at the publisher it IS pushed to the subscriber.
> Outside of the new column, my updates bi-directionally work. Can someone
> tell me how I can get updates on newly added columns to be bi-directional?
> Thanks,
> Adam P. Cassidy
>
|||Yes. That is the same article I used as a reference originally.
Adam P. Cassidy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%234SYrOq4GHA.1188@.TK2MSFTNGP05.phx.gbl...
> Did you quiesce the system as outlined in this link?
> http://msdn2.microsoft.com/en-us/library/ms147385.aspx
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Adam Patrick Cassidy" <apcassidy@.aol.com> wrote in message
> news:eoBB5en4GHA.1200@.TK2MSFTNGP02.phx.gbl...
>
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Friday, March 30, 2012
Peculiar problem with BCP data load
I have table in database with total column length about
225 bytes and have about 2.7 mil rows. When the data is
loaded using bcp some days it takes up more then 8 to 16
GB and other days it takes about 800 MB. Not consistent
as to how it behaves on any give days.
It's a row table with no index no primary key. I see from
statistics that on whole page only one to two rows are
stored and rest of the space is free. Any one has seen
this issue before.
DBCC SHOWCONTIG scanning 'PSN' table...
Table: 'PSN' (548197003); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1003989
- Extents Scanned.......................: 126702
- Extent Switches.......................: 126701
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.05%
[125499:126702]
- Extent Scan Fragmentation ...............: 2.61%
- Avg. Bytes Free per Page................: 7441.4
- Avg. Page Density (full)................: 8.06%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
There are many solutions to make it work. But just curious
as to what is causing this. Any pointers are greatly
appreciated.
Regards,
Don
Try creating a clustered index on the table, preferably one that is in order
of the data to some degree.
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:0c2c01c4abba$f0313540$a401280a@.phx.gbl...
> I have table in database with total column length about
> 225 bytes and have about 2.7 mil rows. When the data is
> loaded using bcp some days it takes up more then 8 to 16
> GB and other days it takes about 800 MB. Not consistent
> as to how it behaves on any give days.
> It's a row table with no index no primary key. I see from
> statistics that on whole page only one to two rows are
> stored and rest of the space is free. Any one has seen
> this issue before.
> DBCC SHOWCONTIG scanning 'PSN' table...
> Table: 'PSN' (548197003); index ID: 0, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1003989
> - Extents Scanned.......................: 126702
> - Extent Switches.......................: 126701
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.05%
> [125499:126702]
> - Extent Scan Fragmentation ...............: 2.61%
> - Avg. Bytes Free per Page................: 7441.4
> - Avg. Page Density (full)................: 8.06%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> There are many solutions to make it work. But just curious
> as to what is causing this. Any pointers are greatly
> appreciated.
> Regards,
> Don
>
225 bytes and have about 2.7 mil rows. When the data is
loaded using bcp some days it takes up more then 8 to 16
GB and other days it takes about 800 MB. Not consistent
as to how it behaves on any give days.
It's a row table with no index no primary key. I see from
statistics that on whole page only one to two rows are
stored and rest of the space is free. Any one has seen
this issue before.
DBCC SHOWCONTIG scanning 'PSN' table...
Table: 'PSN' (548197003); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1003989
- Extents Scanned.......................: 126702
- Extent Switches.......................: 126701
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.05%
[125499:126702]
- Extent Scan Fragmentation ...............: 2.61%
- Avg. Bytes Free per Page................: 7441.4
- Avg. Page Density (full)................: 8.06%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
There are many solutions to make it work. But just curious
as to what is causing this. Any pointers are greatly
appreciated.
Regards,
Don
Try creating a clustered index on the table, preferably one that is in order
of the data to some degree.
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:0c2c01c4abba$f0313540$a401280a@.phx.gbl...
> I have table in database with total column length about
> 225 bytes and have about 2.7 mil rows. When the data is
> loaded using bcp some days it takes up more then 8 to 16
> GB and other days it takes about 800 MB. Not consistent
> as to how it behaves on any give days.
> It's a row table with no index no primary key. I see from
> statistics that on whole page only one to two rows are
> stored and rest of the space is free. Any one has seen
> this issue before.
> DBCC SHOWCONTIG scanning 'PSN' table...
> Table: 'PSN' (548197003); index ID: 0, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1003989
> - Extents Scanned.......................: 126702
> - Extent Switches.......................: 126701
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.05%
> [125499:126702]
> - Extent Scan Fragmentation ...............: 2.61%
> - Avg. Bytes Free per Page................: 7441.4
> - Avg. Page Density (full)................: 8.06%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> There are many solutions to make it work. But just curious
> as to what is causing this. Any pointers are greatly
> appreciated.
> Regards,
> Don
>
Peculiar problem with BCP data load
I have table in database with total column length about
225 bytes and have about 2.7 mil rows. When the data is
loaded using bcp some days it takes up more then 8 to 16
GB and other days it takes about 800 MB. Not consistent
as to how it behaves on any give days.
It's a row table with no index no primary key. I see from
statistics that on whole page only one to two rows are
stored and rest of the space is free. Any one has seen
this issue before.
DBCC SHOWCONTIG scanning 'PSN' table...
Table: 'PSN' (548197003); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1003989
- Extents Scanned.......................: 126702
- Extent Switches.......................: 126701
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.05%
[125499:126702]
- Extent Scan Fragmentation ...............: 2.61%
- Avg. Bytes Free per Page................: 7441.4
- Avg. Page Density (full)................: 8.06%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
There are many solutions to make it work. But just curious
as to what is causing this. Any pointers are greatly
appreciated.
Regards,
DonTry creating a clustered index on the table, preferably one that is in order
of the data to some degree.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:0c2c01c4abba$f0313540$a401280a@.phx.gbl...
> I have table in database with total column length about
> 225 bytes and have about 2.7 mil rows. When the data is
> loaded using bcp some days it takes up more then 8 to 16
> GB and other days it takes about 800 MB. Not consistent
> as to how it behaves on any give days.
> It's a row table with no index no primary key. I see from
> statistics that on whole page only one to two rows are
> stored and rest of the space is free. Any one has seen
> this issue before.
> DBCC SHOWCONTIG scanning 'PSN' table...
> Table: 'PSN' (548197003); index ID: 0, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1003989
> - Extents Scanned.......................: 126702
> - Extent Switches.......................: 126701
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.05%
> [125499:126702]
> - Extent Scan Fragmentation ...............: 2.61%
> - Avg. Bytes Free per Page................: 7441.4
> - Avg. Page Density (full)................: 8.06%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> There are many solutions to make it work. But just curious
> as to what is causing this. Any pointers are greatly
> appreciated.
> Regards,
> Don
>
225 bytes and have about 2.7 mil rows. When the data is
loaded using bcp some days it takes up more then 8 to 16
GB and other days it takes about 800 MB. Not consistent
as to how it behaves on any give days.
It's a row table with no index no primary key. I see from
statistics that on whole page only one to two rows are
stored and rest of the space is free. Any one has seen
this issue before.
DBCC SHOWCONTIG scanning 'PSN' table...
Table: 'PSN' (548197003); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1003989
- Extents Scanned.......................: 126702
- Extent Switches.......................: 126701
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.05%
[125499:126702]
- Extent Scan Fragmentation ...............: 2.61%
- Avg. Bytes Free per Page................: 7441.4
- Avg. Page Density (full)................: 8.06%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
There are many solutions to make it work. But just curious
as to what is causing this. Any pointers are greatly
appreciated.
Regards,
DonTry creating a clustered index on the table, preferably one that is in order
of the data to some degree.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:0c2c01c4abba$f0313540$a401280a@.phx.gbl...
> I have table in database with total column length about
> 225 bytes and have about 2.7 mil rows. When the data is
> loaded using bcp some days it takes up more then 8 to 16
> GB and other days it takes about 800 MB. Not consistent
> as to how it behaves on any give days.
> It's a row table with no index no primary key. I see from
> statistics that on whole page only one to two rows are
> stored and rest of the space is free. Any one has seen
> this issue before.
> DBCC SHOWCONTIG scanning 'PSN' table...
> Table: 'PSN' (548197003); index ID: 0, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1003989
> - Extents Scanned.......................: 126702
> - Extent Switches.......................: 126701
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.05%
> [125499:126702]
> - Extent Scan Fragmentation ...............: 2.61%
> - Avg. Bytes Free per Page................: 7441.4
> - Avg. Page Density (full)................: 8.06%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> There are many solutions to make it work. But just curious
> as to what is causing this. Any pointers are greatly
> appreciated.
> Regards,
> Don
>
Pease help - Feild value is truncated
I use a stored procedure to populate a table and it truncates the value to
one character for the field eMail.
you have an idea what is the problem?
i have to say also that I have tried this and it works:
INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, `string`)
So it looks like there is a problem at the @.eMailPlus variable level.
TABLE IS:
Id int
eMail varchar(MAX)
STORED PROCEDURE IS:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_myInsertStoredProcedure] @.eMail varchar AS
DECLARE @.IdMaxPlus AS INT
DECLARE @.eMailPlus AS VARCHAR
BEGIN TRANSACTION
SET @.eMailPlus = 'Ring' VARCHAR
SELECT @.IdMaxPlus = coalesce(MAX(Id), 0) + 1 FROM VH_Data_NewsEmail
(UPDLOCK)
INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, @.eMailPlus)
COMMIT TRANSACTIONok fixed it
DECLARE @.eMailPlus AS VARCHAR(MAX)
"Progman" <adfawefqw@.hotmail.com> wrote in message
news:1fmKf.2089$Jb7.681075@.weber.videotron.net...
>I use a stored procedure to populate a table and it truncates the value to
>one character for the field eMail.
> you have an idea what is the problem?
> i have to say also that I have tried this and it works:
> INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, `string`)
> So it looks like there is a problem at the @.eMailPlus variable level.
> TABLE IS:
> Id int
> eMail varchar(MAX)
> STORED PROCEDURE IS:
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE [dbo].[sp_myInsertStoredProcedure] @.eMail varchar AS
> DECLARE @.IdMaxPlus AS INT
> DECLARE @.eMailPlus AS VARCHAR
> BEGIN TRANSACTION
> SET @.eMailPlus = 'Ring' VARCHAR
> SELECT @.IdMaxPlus = coalesce(MAX(Id), 0) + 1 FROM VH_Data_NewsEmail
> (UPDLOCK)
> INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, @.eMailPlus)
> COMMIT TRANSACTION
>
one character for the field eMail.
you have an idea what is the problem?
i have to say also that I have tried this and it works:
INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, `string`)
So it looks like there is a problem at the @.eMailPlus variable level.
TABLE IS:
Id int
eMail varchar(MAX)
STORED PROCEDURE IS:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_myInsertStoredProcedure] @.eMail varchar AS
DECLARE @.IdMaxPlus AS INT
DECLARE @.eMailPlus AS VARCHAR
BEGIN TRANSACTION
SET @.eMailPlus = 'Ring' VARCHAR
SELECT @.IdMaxPlus = coalesce(MAX(Id), 0) + 1 FROM VH_Data_NewsEmail
(UPDLOCK)
INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, @.eMailPlus)
COMMIT TRANSACTIONok fixed it
DECLARE @.eMailPlus AS VARCHAR(MAX)
"Progman" <adfawefqw@.hotmail.com> wrote in message
news:1fmKf.2089$Jb7.681075@.weber.videotron.net...
>I use a stored procedure to populate a table and it truncates the value to
>one character for the field eMail.
> you have an idea what is the problem?
> i have to say also that I have tried this and it works:
> INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, `string`)
> So it looks like there is a problem at the @.eMailPlus variable level.
> TABLE IS:
> Id int
> eMail varchar(MAX)
> STORED PROCEDURE IS:
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE [dbo].[sp_myInsertStoredProcedure] @.eMail varchar AS
> DECLARE @.IdMaxPlus AS INT
> DECLARE @.eMailPlus AS VARCHAR
> BEGIN TRANSACTION
> SET @.eMailPlus = 'Ring' VARCHAR
> SELECT @.IdMaxPlus = coalesce(MAX(Id), 0) + 1 FROM VH_Data_NewsEmail
> (UPDLOCK)
> INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, @.eMailPlus)
> COMMIT TRANSACTION
>
Wednesday, March 28, 2012
PDF Full Text Search
Hi all,
I have a table configured to support Full-Text Search and in this tables a column stores documents of three types, Word, Excel and PDF. Everything works fine with Word and Excel documents.
In order to search PDF documents I've installed "IFilter 5.0", which is published in Acrobat's page, but nothing. What else should I do, is there any other solution?
I've read that I can create a particular filter, so the question is where can find information about creating customized filters? But first, is it a good business to create a new filter?
Thanks in advance.Dear mvargasp,
I have a problem about FTS. I think you could help me...
I wrote to forum but nobody reply to me...
I need your help,
Thanks alot
Yusuf
--------
I've a problem. I'm inexperienced about FTS.
I have a TEXT datatype field in my table and I store MS Word documents.
I want to do full text search on this TEXT datatype field.
So I build catalog and run
"Select * from table where Contains (textfield,'Yusuf')" sql in analyzer.
but null value returns. however I know the table has 3 records.
Anyway, I insert a varchar field in the table and edit my fts catalog.
and then insert 3 records in the table.than rebuild catalog and I try
"Select * from table where Contains (varcharfield,'Yusuf')"
this sql returns true records.
I know that I can FTS in TEXT datatype field but I can't. I don't know why.
I need your help.
thanks alot
Yusuf|||Hi Yusuf,
It seems that you have created a catalog and rebuild it then, but you have not populate it. So What you must do after you create or rebuild a catalog is to populate it. I order to do so, you must right click your catalog in the Enterprise Manager and then click Populate.
After this the FTS must work.
God Bless.
I have a table configured to support Full-Text Search and in this tables a column stores documents of three types, Word, Excel and PDF. Everything works fine with Word and Excel documents.
In order to search PDF documents I've installed "IFilter 5.0", which is published in Acrobat's page, but nothing. What else should I do, is there any other solution?
I've read that I can create a particular filter, so the question is where can find information about creating customized filters? But first, is it a good business to create a new filter?
Thanks in advance.Dear mvargasp,
I have a problem about FTS. I think you could help me...
I wrote to forum but nobody reply to me...
I need your help,
Thanks alot
Yusuf
--------
I've a problem. I'm inexperienced about FTS.
I have a TEXT datatype field in my table and I store MS Word documents.
I want to do full text search on this TEXT datatype field.
So I build catalog and run
"Select * from table where Contains (textfield,'Yusuf')" sql in analyzer.
but null value returns. however I know the table has 3 records.
Anyway, I insert a varchar field in the table and edit my fts catalog.
and then insert 3 records in the table.than rebuild catalog and I try
"Select * from table where Contains (varcharfield,'Yusuf')"
this sql returns true records.
I know that I can FTS in TEXT datatype field but I can't. I don't know why.
I need your help.
thanks alot
Yusuf|||Hi Yusuf,
It seems that you have created a catalog and rebuild it then, but you have not populate it. So What you must do after you create or rebuild a catalog is to populate it. I order to do so, you must right click your catalog in the Enterprise Manager and then click Populate.
After this the FTS must work.
God Bless.
Monday, March 26, 2012
PDF Export For Matrix And Chart
I have a very simple report with a matrix and a subreport chart.
2 rows in a table object, Row 1 has Matrix, Row 2 has Chart.
When I export to PDF, the Page always breaks before I get the chart.
If I put the chart in Row 1 and the Matrix in Row 2, no problem with
page break.
Any ideas?
I've posted quite a few times re: the PDF exporting of Reporting
Services, I see this as the major weakness of the product. Does MS
have any plans to correct the PDF engine so it robust enought?
Thanks.
Tim Heuer
heuert@.comcast.netI have run into similar sounding problems and have had little response. It
appears there are a number of known issues with PDF and Excel for that
matter, but I havent heard if and when they are going to address them.
"teem60004" wrote:
> I have a very simple report with a matrix and a subreport chart.
> 2 rows in a table object, Row 1 has Matrix, Row 2 has Chart.
> When I export to PDF, the Page always breaks before I get the chart.
> If I put the chart in Row 1 and the Matrix in Row 2, no problem with
> page break.
> Any ideas?
> I've posted quite a few times re: the PDF exporting of Reporting
> Services, I see this as the major weakness of the product. Does MS
> have any plans to correct the PDF engine so it robust enought?
> Thanks.
> Tim Heuer
> heuert@.comcast.net
>
2 rows in a table object, Row 1 has Matrix, Row 2 has Chart.
When I export to PDF, the Page always breaks before I get the chart.
If I put the chart in Row 1 and the Matrix in Row 2, no problem with
page break.
Any ideas?
I've posted quite a few times re: the PDF exporting of Reporting
Services, I see this as the major weakness of the product. Does MS
have any plans to correct the PDF engine so it robust enought?
Thanks.
Tim Heuer
heuert@.comcast.netI have run into similar sounding problems and have had little response. It
appears there are a number of known issues with PDF and Excel for that
matter, but I havent heard if and when they are going to address them.
"teem60004" wrote:
> I have a very simple report with a matrix and a subreport chart.
> 2 rows in a table object, Row 1 has Matrix, Row 2 has Chart.
> When I export to PDF, the Page always breaks before I get the chart.
> If I put the chart in Row 1 and the Matrix in Row 2, no problem with
> page break.
> Any ideas?
> I've posted quite a few times re: the PDF exporting of Reporting
> Services, I see this as the major weakness of the product. Does MS
> have any plans to correct the PDF engine so it robust enought?
> Thanks.
> Tim Heuer
> heuert@.comcast.net
>
PDF Export / Bold Text / Underline text decoration
I'm having a problem when I have a text item in a table - the text is bold
and underlined; preview/print preview looks fine; but when I export the
report to PDF, the underline extends beyond the text to the right. If the
text is normal weight, the underline is fine. Is this a known issue? (maybe
fixed w/ RS SP2' - I looked at the bug list and saw some PDF fixes, but not
for this specific problem).
ThanksSome more information on this problem:
Any font specified as bold, bolder, extra-bold, etc. with the text
decoration set to Underline ... looks fine in designer / web preview whether
in a table or by itself in a textbox - but when exported to PDF, the bold
disappears (underline stays, though - but even this is a problem - the
underline extends to the right beyond the text) - maybe if the bold was
really bold, it would be the correct width for the text.
For example:
--
Instead of
For example:
--
I suspect this is a bug w/ the PDF export. Any ideas on possible workarounds?
TIA
"JKirst" wrote:
> I'm having a problem when I have a text item in a table - the text is bold
> and underlined; preview/print preview looks fine; but when I export the
> report to PDF, the underline extends beyond the text to the right. If the
> text is normal weight, the underline is fine. Is this a known issue? (maybe
> fixed w/ RS SP2' - I looked at the bug list and saw some PDF fixes, but not
> for this specific problem).
> Thanks
>|||JKirst,
I had a similar issue with text decoration in the export to PDF, but in my
case the underline disappeared altogether. Through an incident call with MS,
they confirmed there is a bug related to the calculation of the text
decoration and do not have a scheduled fix.
I did discover a work around in my case. It appears that if you have a text
box (whether in a table or just stand alone) that has its CanGrow property
set to true, but the height defined smaller than the defined font size and
text decorations, the text decorations may not render or render improperly if
the data text box does not wrap to 2 or more lines. To fix this, simply
define your textbox's starting height to be greater than the font size with
all potential text decorations applied.
"JKirst" wrote:
> Some more information on this problem:
> Any font specified as bold, bolder, extra-bold, etc. with the text
> decoration set to Underline ... looks fine in designer / web preview whether
> in a table or by itself in a textbox - but when exported to PDF, the bold
> disappears (underline stays, though - but even this is a problem - the
> underline extends to the right beyond the text) - maybe if the bold was
> really bold, it would be the correct width for the text.
> For example:
> --
> Instead of
> For example:
> --
> I suspect this is a bug w/ the PDF export. Any ideas on possible workarounds?
> TIA
> "JKirst" wrote:
> > I'm having a problem when I have a text item in a table - the text is bold
> > and underlined; preview/print preview looks fine; but when I export the
> > report to PDF, the underline extends beyond the text to the right. If the
> > text is normal weight, the underline is fine. Is this a known issue? (maybe
> > fixed w/ RS SP2' - I looked at the bug list and saw some PDF fixes, but not
> > for this specific problem).
> >
> > Thanks
> >
> >
and underlined; preview/print preview looks fine; but when I export the
report to PDF, the underline extends beyond the text to the right. If the
text is normal weight, the underline is fine. Is this a known issue? (maybe
fixed w/ RS SP2' - I looked at the bug list and saw some PDF fixes, but not
for this specific problem).
ThanksSome more information on this problem:
Any font specified as bold, bolder, extra-bold, etc. with the text
decoration set to Underline ... looks fine in designer / web preview whether
in a table or by itself in a textbox - but when exported to PDF, the bold
disappears (underline stays, though - but even this is a problem - the
underline extends to the right beyond the text) - maybe if the bold was
really bold, it would be the correct width for the text.
For example:
--
Instead of
For example:
--
I suspect this is a bug w/ the PDF export. Any ideas on possible workarounds?
TIA
"JKirst" wrote:
> I'm having a problem when I have a text item in a table - the text is bold
> and underlined; preview/print preview looks fine; but when I export the
> report to PDF, the underline extends beyond the text to the right. If the
> text is normal weight, the underline is fine. Is this a known issue? (maybe
> fixed w/ RS SP2' - I looked at the bug list and saw some PDF fixes, but not
> for this specific problem).
> Thanks
>|||JKirst,
I had a similar issue with text decoration in the export to PDF, but in my
case the underline disappeared altogether. Through an incident call with MS,
they confirmed there is a bug related to the calculation of the text
decoration and do not have a scheduled fix.
I did discover a work around in my case. It appears that if you have a text
box (whether in a table or just stand alone) that has its CanGrow property
set to true, but the height defined smaller than the defined font size and
text decorations, the text decorations may not render or render improperly if
the data text box does not wrap to 2 or more lines. To fix this, simply
define your textbox's starting height to be greater than the font size with
all potential text decorations applied.
"JKirst" wrote:
> Some more information on this problem:
> Any font specified as bold, bolder, extra-bold, etc. with the text
> decoration set to Underline ... looks fine in designer / web preview whether
> in a table or by itself in a textbox - but when exported to PDF, the bold
> disappears (underline stays, though - but even this is a problem - the
> underline extends to the right beyond the text) - maybe if the bold was
> really bold, it would be the correct width for the text.
> For example:
> --
> Instead of
> For example:
> --
> I suspect this is a bug w/ the PDF export. Any ideas on possible workarounds?
> TIA
> "JKirst" wrote:
> > I'm having a problem when I have a text item in a table - the text is bold
> > and underlined; preview/print preview looks fine; but when I export the
> > report to PDF, the underline extends beyond the text to the right. If the
> > text is normal weight, the underline is fine. Is this a known issue? (maybe
> > fixed w/ RS SP2' - I looked at the bug list and saw some PDF fixes, but not
> > for this specific problem).
> >
> > Thanks
> >
> >
PDF EXPORT
Dear All
I use sql server reporting services..in visual studio 2005..
when exporting report to pdf in the report viewer alignment varies..
single table report split up in to two pages not in proper manner..
how to tackle this export to pdf event in report viewer..
did anyone can help me in this.
Regards
Senthil
You need to do adjust the table correctly. just decrease the width of the table and other objects.
if width of the objects does not fit in a page than it will be divided into two pages in pdf. so try to adjust the width of the objects.
|||
I had the same problem and this is what I found. If the report is defined as an 11 x 8.5 (landscape) report with .5 margins (defined in report properties) all around your report data cannot exceed 10".
10" + .5 left margin + .5 right margin = 11.
If you exceed this the page will split.
|||
Thnx jhaberer
its working fine!...
sqlFriday, March 23, 2012
PDF Document Map Hierarchy
I've notice a couple other posts about this issue, but could not seem
to find any responses/resolutions.
I have a table with multiple groups. In HTML the document map
hierarchy looks fine. In PDF there is no hierarchy.
Thanks for any help with this.
Example - HTML:
- Sex
- Male
- Age
- 35
- 40
- 45
- Female
- Age
- 35
- 40
- 45
Example - PDF:
Sex
Male
Age
35
40
45
Female
Age
35
40
45Hi Bob,
how did you get the hierarchy in HTML and preview? I have no hierarchy at
all.
thanks
Matt
"BobA" <robert.ames@.mercer.com> wrote in message
news:b7b93183.0410051203.4d37c984@.posting.google.com...
> I've notice a couple other posts about this issue, but could not seem
> to find any responses/resolutions.
> I have a table with multiple groups. In HTML the document map
> hierarchy looks fine. In PDF there is no hierarchy.
> Thanks for any help with this.
> Example - HTML:
> - Sex
> - Male
> - Age
> - 35
> - 40
> - 45
> - Female
> - Age
> - 35
> - 40
> - 45
> Example - PDF:
> Sex
> Male
> Age
> 35
> 40
> 45
> Female
> Age
> 35
> 40
> 45|||Thanks, I figured it out. I needed to set the document map against the
group not the element in the table.
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:ulf3e8ArEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Hi Bob,
> how did you get the hierarchy in HTML and preview? I have no hierarchy at
> all.
> thanks
> Matt
>
> "BobA" <robert.ames@.mercer.com> wrote in message
> news:b7b93183.0410051203.4d37c984@.posting.google.com...
> > I've notice a couple other posts about this issue, but could not seem
> > to find any responses/resolutions.
> >
> > I have a table with multiple groups. In HTML the document map
> > hierarchy looks fine. In PDF there is no hierarchy.
> >
> > Thanks for any help with this.
> >
> > Example - HTML:
> >
> > - Sex
> > - Male
> > - Age
> > - 35
> > - 40
> > - 45
> > - Female
> > - Age
> > - 35
> > - 40
> > - 45
> >
> > Example - PDF:
> >
> > Sex
> > Male
> > Age
> > 35
> > 40
> > 45
> > Female
> > Age
> > 35
> > 40
> > 45
>|||This is still an issue. Are there any ideas on how to fix this
problem?
Matt wrote:
> Thanks, I figured it out. I needed to set the document map against
the
> group not the element in the table.
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:ulf3e8ArEHA.1152@.TK2MSFTNGP11.phx.gbl...
> > Hi Bob,
> >
> > how did you get the hierarchy in HTML and preview? I have no
hierarchy at
> > all.
> >
> > thanks
> >
> > Matt
> >
> >
> > "BobA" <robert.ames@.mercer.com> wrote in message
> > news:b7b93183.0410051203.4d37c984@.posting.google.com...
> > > I've notice a couple other posts about this issue, but could not
seem
> > > to find any responses/resolutions.
> > >
> > > I have a table with multiple groups. In HTML the document map
> > > hierarchy looks fine. In PDF there is no hierarchy.
> > >
> > > Thanks for any help with this.
> > >
> > > Example - HTML:
> > >
> > > - Sex
> > > - Male
> > > - Age
> > > - 35
> > > - 40
> > > - 45
> > > - Female
> > > - Age
> > > - 35
> > > - 40
> > > - 45
> > >
> > > Example - PDF:
> > >
> > > Sex
> > > Male
> > > Age
> > > 35
> > > 40
> > > 45
> > > Female
> > > Age
> > > 35
> > > 40
> > > 45
> >
> >|||Hi Bob,
I am facing the same dilema. The Document map hierarchy appears flat when
you export to PDF. Very annoying!!!
Thanks,
Terry
"BobA" wrote:
> This is still an issue. Are there any ideas on how to fix this
> problem?
> Matt wrote:
> > Thanks, I figured it out. I needed to set the document map against
> the
> > group not the element in the table.
> >
> > "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> > news:ulf3e8ArEHA.1152@.TK2MSFTNGP11.phx.gbl...
> > > Hi Bob,
> > >
> > > how did you get the hierarchy in HTML and preview? I have no
> hierarchy at
> > > all.
> > >
> > > thanks
> > >
> > > Matt
> > >
> > >
> > > "BobA" <robert.ames@.mercer.com> wrote in message
> > > news:b7b93183.0410051203.4d37c984@.posting.google.com...
> > > > I've notice a couple other posts about this issue, but could not
> seem
> > > > to find any responses/resolutions.
> > > >
> > > > I have a table with multiple groups. In HTML the document map
> > > > hierarchy looks fine. In PDF there is no hierarchy.
> > > >
> > > > Thanks for any help with this.
> > > >
> > > > Example - HTML:
> > > >
> > > > - Sex
> > > > - Male
> > > > - Age
> > > > - 35
> > > > - 40
> > > > - 45
> > > > - Female
> > > > - Age
> > > > - 35
> > > > - 40
> > > > - 45
> > > >
> > > > Example - PDF:
> > > >
> > > > Sex
> > > > Male
> > > > Age
> > > > 35
> > > > 40
> > > > 45
> > > > Female
> > > > Age
> > > > 35
> > > > 40
> > > > 45
> > >
> > >
>|||Hi,
I have the same issue also. Did anyone ever find a solution to it?
Thanks,
Melissa
"Terry" wrote:
> Hi Bob,
> I am facing the same dilema. The Document map hierarchy appears flat when
> you export to PDF. Very annoying!!!
> Thanks,
> Terry
> "BobA" wrote:
> > This is still an issue. Are there any ideas on how to fix this
> > problem?
> >
> > Matt wrote:
> > > Thanks, I figured it out. I needed to set the document map against
> > the
> > > group not the element in the table.
> > >
> > > "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> > > news:ulf3e8ArEHA.1152@.TK2MSFTNGP11.phx.gbl...
> > > > Hi Bob,
> > > >
> > > > how did you get the hierarchy in HTML and preview? I have no
> > hierarchy at
> > > > all.
> > > >
> > > > thanks
> > > >
> > > > Matt
> > > >
> > > >
> > > > "BobA" <robert.ames@.mercer.com> wrote in message
> > > > news:b7b93183.0410051203.4d37c984@.posting.google.com...
> > > > > I've notice a couple other posts about this issue, but could not
> > seem
> > > > > to find any responses/resolutions.
> > > > >
> > > > > I have a table with multiple groups. In HTML the document map
> > > > > hierarchy looks fine. In PDF there is no hierarchy.
> > > > >
> > > > > Thanks for any help with this.
> > > > >
> > > > > Example - HTML:
> > > > >
> > > > > - Sex
> > > > > - Male
> > > > > - Age
> > > > > - 35
> > > > > - 40
> > > > > - 45
> > > > > - Female
> > > > > - Age
> > > > > - 35
> > > > > - 40
> > > > > - 45
> > > > >
> > > > > Example - PDF:
> > > > >
> > > > > Sex
> > > > > Male
> > > > > Age
> > > > > 35
> > > > > 40
> > > > > 45
> > > > > Female
> > > > > Age
> > > > > 35
> > > > > 40
> > > > > 45
> > > >
> > > >
> >
> >
to find any responses/resolutions.
I have a table with multiple groups. In HTML the document map
hierarchy looks fine. In PDF there is no hierarchy.
Thanks for any help with this.
Example - HTML:
- Sex
- Male
- Age
- 35
- 40
- 45
- Female
- Age
- 35
- 40
- 45
Example - PDF:
Sex
Male
Age
35
40
45
Female
Age
35
40
45Hi Bob,
how did you get the hierarchy in HTML and preview? I have no hierarchy at
all.
thanks
Matt
"BobA" <robert.ames@.mercer.com> wrote in message
news:b7b93183.0410051203.4d37c984@.posting.google.com...
> I've notice a couple other posts about this issue, but could not seem
> to find any responses/resolutions.
> I have a table with multiple groups. In HTML the document map
> hierarchy looks fine. In PDF there is no hierarchy.
> Thanks for any help with this.
> Example - HTML:
> - Sex
> - Male
> - Age
> - 35
> - 40
> - 45
> - Female
> - Age
> - 35
> - 40
> - 45
> Example - PDF:
> Sex
> Male
> Age
> 35
> 40
> 45
> Female
> Age
> 35
> 40
> 45|||Thanks, I figured it out. I needed to set the document map against the
group not the element in the table.
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:ulf3e8ArEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Hi Bob,
> how did you get the hierarchy in HTML and preview? I have no hierarchy at
> all.
> thanks
> Matt
>
> "BobA" <robert.ames@.mercer.com> wrote in message
> news:b7b93183.0410051203.4d37c984@.posting.google.com...
> > I've notice a couple other posts about this issue, but could not seem
> > to find any responses/resolutions.
> >
> > I have a table with multiple groups. In HTML the document map
> > hierarchy looks fine. In PDF there is no hierarchy.
> >
> > Thanks for any help with this.
> >
> > Example - HTML:
> >
> > - Sex
> > - Male
> > - Age
> > - 35
> > - 40
> > - 45
> > - Female
> > - Age
> > - 35
> > - 40
> > - 45
> >
> > Example - PDF:
> >
> > Sex
> > Male
> > Age
> > 35
> > 40
> > 45
> > Female
> > Age
> > 35
> > 40
> > 45
>|||This is still an issue. Are there any ideas on how to fix this
problem?
Matt wrote:
> Thanks, I figured it out. I needed to set the document map against
the
> group not the element in the table.
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:ulf3e8ArEHA.1152@.TK2MSFTNGP11.phx.gbl...
> > Hi Bob,
> >
> > how did you get the hierarchy in HTML and preview? I have no
hierarchy at
> > all.
> >
> > thanks
> >
> > Matt
> >
> >
> > "BobA" <robert.ames@.mercer.com> wrote in message
> > news:b7b93183.0410051203.4d37c984@.posting.google.com...
> > > I've notice a couple other posts about this issue, but could not
seem
> > > to find any responses/resolutions.
> > >
> > > I have a table with multiple groups. In HTML the document map
> > > hierarchy looks fine. In PDF there is no hierarchy.
> > >
> > > Thanks for any help with this.
> > >
> > > Example - HTML:
> > >
> > > - Sex
> > > - Male
> > > - Age
> > > - 35
> > > - 40
> > > - 45
> > > - Female
> > > - Age
> > > - 35
> > > - 40
> > > - 45
> > >
> > > Example - PDF:
> > >
> > > Sex
> > > Male
> > > Age
> > > 35
> > > 40
> > > 45
> > > Female
> > > Age
> > > 35
> > > 40
> > > 45
> >
> >|||Hi Bob,
I am facing the same dilema. The Document map hierarchy appears flat when
you export to PDF. Very annoying!!!
Thanks,
Terry
"BobA" wrote:
> This is still an issue. Are there any ideas on how to fix this
> problem?
> Matt wrote:
> > Thanks, I figured it out. I needed to set the document map against
> the
> > group not the element in the table.
> >
> > "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> > news:ulf3e8ArEHA.1152@.TK2MSFTNGP11.phx.gbl...
> > > Hi Bob,
> > >
> > > how did you get the hierarchy in HTML and preview? I have no
> hierarchy at
> > > all.
> > >
> > > thanks
> > >
> > > Matt
> > >
> > >
> > > "BobA" <robert.ames@.mercer.com> wrote in message
> > > news:b7b93183.0410051203.4d37c984@.posting.google.com...
> > > > I've notice a couple other posts about this issue, but could not
> seem
> > > > to find any responses/resolutions.
> > > >
> > > > I have a table with multiple groups. In HTML the document map
> > > > hierarchy looks fine. In PDF there is no hierarchy.
> > > >
> > > > Thanks for any help with this.
> > > >
> > > > Example - HTML:
> > > >
> > > > - Sex
> > > > - Male
> > > > - Age
> > > > - 35
> > > > - 40
> > > > - 45
> > > > - Female
> > > > - Age
> > > > - 35
> > > > - 40
> > > > - 45
> > > >
> > > > Example - PDF:
> > > >
> > > > Sex
> > > > Male
> > > > Age
> > > > 35
> > > > 40
> > > > 45
> > > > Female
> > > > Age
> > > > 35
> > > > 40
> > > > 45
> > >
> > >
>|||Hi,
I have the same issue also. Did anyone ever find a solution to it?
Thanks,
Melissa
"Terry" wrote:
> Hi Bob,
> I am facing the same dilema. The Document map hierarchy appears flat when
> you export to PDF. Very annoying!!!
> Thanks,
> Terry
> "BobA" wrote:
> > This is still an issue. Are there any ideas on how to fix this
> > problem?
> >
> > Matt wrote:
> > > Thanks, I figured it out. I needed to set the document map against
> > the
> > > group not the element in the table.
> > >
> > > "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> > > news:ulf3e8ArEHA.1152@.TK2MSFTNGP11.phx.gbl...
> > > > Hi Bob,
> > > >
> > > > how did you get the hierarchy in HTML and preview? I have no
> > hierarchy at
> > > > all.
> > > >
> > > > thanks
> > > >
> > > > Matt
> > > >
> > > >
> > > > "BobA" <robert.ames@.mercer.com> wrote in message
> > > > news:b7b93183.0410051203.4d37c984@.posting.google.com...
> > > > > I've notice a couple other posts about this issue, but could not
> > seem
> > > > > to find any responses/resolutions.
> > > > >
> > > > > I have a table with multiple groups. In HTML the document map
> > > > > hierarchy looks fine. In PDF there is no hierarchy.
> > > > >
> > > > > Thanks for any help with this.
> > > > >
> > > > > Example - HTML:
> > > > >
> > > > > - Sex
> > > > > - Male
> > > > > - Age
> > > > > - 35
> > > > > - 40
> > > > > - 45
> > > > > - Female
> > > > > - Age
> > > > > - 35
> > > > > - 40
> > > > > - 45
> > > > >
> > > > > Example - PDF:
> > > > >
> > > > > Sex
> > > > > Male
> > > > > Age
> > > > > 35
> > > > > 40
> > > > > 45
> > > > > Female
> > > > > Age
> > > > > 35
> > > > > 40
> > > > > 45
> > > >
> > > >
> >
> >
PC SQL Server table (with a column classified "unique") synchronizes (using merge) wit
Hi, I have a doubt about the behaviour of SQL Server 2005 in the situation I'm going to describe you.
Suppose that you have a SQL Server 2005 database on your PC, and suppose that this database has a table with a column classified as "unique" (so it's impossible for this table to contain 2 records having the same value in this column).
Suppose that you publish this database and you create 2 SQL Server Mobile 2005 subscriptions on 2 Pocket PCs.
Suppose now that the first PPC (using an embedded program) creates a record with a certain value for the column (and adds it to the table), and the second PPC makes the same thing (it inserts a record with the same column value of the first PPC).
At this point, you connect the 2 PPCs to your PC (one by one, of course), to synchronize (using merge replication) the databases...
Suppose that you have a SQL Server 2005 database on your PC, and suppose that this database has a table with a column classified as "unique" (so it's impossible for this table to contain 2 records having the same value in this column).
Suppose that you publish this database and you create 2 SQL Server Mobile 2005 subscriptions on 2 Pocket PCs.
Suppose now that the first PPC (using an embedded program) creates a record with a certain value for the column (and adds it to the table), and the second PPC makes the same thing (it inserts a record with the same column value of the first PPC).
At this point, you connect the 2 PPCs to your PC (one by one, of course), to synchronize (using merge replication) the databases...
WHAT HAPPENS? Does an error raise?
Must you give a publication setting in which you say that, if this situation occurs, PC SQL Server holds the last (or the first, as you decide) record acquired? Is it possible?
Thank you very much
Of the two subscribers, the 1st will successfully sync with the publisher. The 2nd subscriber will then try to upload their change, and it should fail due to constraint violation. So you now have one conflict.
If the row at the publisher needs to be downloaded to the subscriber, this will also fail due to constraint violation. So you now have two conflicts, one from the upload, one from the download.
With these types of insert conflicts, you have to manually resolve the problem: you can delete the row at the 2nd subscriber, update the row at the publisher or 2nd subscriber so column contains a new unique value.
PC SQL Server table (with a column classified "unique") synchronizes (using merge)
Hi, I have a doubt about the behaviour of SQL Server 2005 in the situation I'm going to describe you.
Suppose that you have a SQL Server 2005 database on your PC, and suppose that this database has a table with a column classified as "unique" (so it's impossible for this table to contain 2 records having the same value in this column).
Suppose that you publish this database and you create 2 SQL Server Mobile 2005 subscriptions on 2 Pocket PCs.
Suppose now that the first PPC (using an embedded program) creates a record with a certain value for the column (and adds it to the table), and the second PPC makes the same thing (it inserts a record with the same column value of the first PPC).
At this point, you connect the 2 PPCs to your PC (one by one, of course), to synchronize (using merge replication) the databases...
Suppose that you have a SQL Server 2005 database on your PC, and suppose that this database has a table with a column classified as "unique" (so it's impossible for this table to contain 2 records having the same value in this column).
Suppose that you publish this database and you create 2 SQL Server Mobile 2005 subscriptions on 2 Pocket PCs.
Suppose now that the first PPC (using an embedded program) creates a record with a certain value for the column (and adds it to the table), and the second PPC makes the same thing (it inserts a record with the same column value of the first PPC).
At this point, you connect the 2 PPCs to your PC (one by one, of course), to synchronize (using merge replication) the databases...
WHAT HAPPENS? Does an error raise?
Must you give a publication setting in which you say that, if this situation occurs, PC SQL Server holds the last (or the first, as you decide) record acquired? Is it possible?
Thank you very much
Of the two subscribers, the 1st will successfully sync with the publisher. The 2nd subscriber will then try to upload their change, and it should fail due to constraint violation. So you now have one conflict.
If the row at the publisher needs to be downloaded to the subscriber, this will also fail due to constraint violation. So you now have two conflicts, one from the upload, one from the download.
With these types of insert conflicts, you have to manually resolve the problem: you can delete the row at the 2nd subscriber, update the row at the publisher or 2nd subscriber so column contains a new unique value.
Pbm with transaction
Hi,
I got a problem while testing the BEGIN TRAN.. COMMIT TRAN. I have created
3 tables and 3 sps for inserting values into the table.
Now I ran a query to call the 3 sps inside a Begi.. commit tranaction which
is given below (of course there is WAITFOR DELAY statement in between the
first to sp calls), but before executing completely i disaqbled the network.
When i reconnect it and checked the values in the tables, i found the
entries. i wonder how this happened before committing the operation. Can
anyone help me. The code is given below
regards Lara
CREATE TABLE [TableOne] (
[a] [int] IDENTITY (1, 1) NOT NULL ,
[atext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [TableThree] (
[c] [int] IDENTITY (1, 1) NOT NULL ,
[ctext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [TableTwo] (
[b] [int] IDENTITY (1, 1) NOT NULL ,
[btext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE InsertA
@.x varchar(32)
as
insert into TableOne select @.x
GO
CREATE PROCEDURE InsertB
@.x varchar(32)
as
BEGIN
--WAITFOR DELAY '000:00:59'
INSERT INTO TableTwo select @.x
END
GO
CREATE PROCEDURE InsertC
@.x varchar(32)
AS
INSERT INTO TableThree select @.x
GO
BEGIN TRAN
EXEC insertA 'A'
WAITFOR DELAY '000:00:59'
EXEC insertB 'B'
EXEC insertC 'C'
COMMIT TRANI assume you found data in all three tables? In this case, the entire batch
was sent to SQL Server where it executed to completion event though the
client was unplugged from the network. It's just that SQL Server couldn't
notify the client after the transaction completed successfully.
Hope this helps.
Dan Guzman
SQL Server MVP
"Lara" <aneeshattingal@.hotpop.com> wrote in message
news:OEZYoCmQFHA.2384@.tk2msftngp13.phx.gbl...
> Hi,
> I got a problem while testing the BEGIN TRAN.. COMMIT TRAN. I have
> created
> 3 tables and 3 sps for inserting values into the table.
> Now I ran a query to call the 3 sps inside a Begi.. commit tranaction
> which
> is given below (of course there is WAITFOR DELAY statement in between the
> first to sp calls), but before executing completely i disaqbled the
> network.
> When i reconnect it and checked the values in the tables, i found the
> entries. i wonder how this happened before committing the operation. Can
> anyone help me. The code is given below
> regards Lara
>
> CREATE TABLE [TableOne] (
> [a] [int] IDENTITY (1, 1) NOT NULL ,
> [atext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [TableThree] (
> [c] [int] IDENTITY (1, 1) NOT NULL ,
> [ctext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [TableTwo] (
> [b] [int] IDENTITY (1, 1) NOT NULL ,
> [btext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE PROCEDURE InsertA
> @.x varchar(32)
> as
> insert into TableOne select @.x
> GO
> CREATE PROCEDURE InsertB
> @.x varchar(32)
> as
> BEGIN
> --WAITFOR DELAY '000:00:59'
> INSERT INTO TableTwo select @.x
> END
> GO
> CREATE PROCEDURE InsertC
> @.x varchar(32)
> AS
> INSERT INTO TableThree select @.x
> GO
> BEGIN TRAN
> EXEC insertA 'A'
> WAITFOR DELAY '000:00:59'
> EXEC insertB 'B'
> EXEC insertC 'C'
> COMMIT TRAN
>
I got a problem while testing the BEGIN TRAN.. COMMIT TRAN. I have created
3 tables and 3 sps for inserting values into the table.
Now I ran a query to call the 3 sps inside a Begi.. commit tranaction which
is given below (of course there is WAITFOR DELAY statement in between the
first to sp calls), but before executing completely i disaqbled the network.
When i reconnect it and checked the values in the tables, i found the
entries. i wonder how this happened before committing the operation. Can
anyone help me. The code is given below
regards Lara
CREATE TABLE [TableOne] (
[a] [int] IDENTITY (1, 1) NOT NULL ,
[atext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [TableThree] (
[c] [int] IDENTITY (1, 1) NOT NULL ,
[ctext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [TableTwo] (
[b] [int] IDENTITY (1, 1) NOT NULL ,
[btext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE InsertA
@.x varchar(32)
as
insert into TableOne select @.x
GO
CREATE PROCEDURE InsertB
@.x varchar(32)
as
BEGIN
--WAITFOR DELAY '000:00:59'
INSERT INTO TableTwo select @.x
END
GO
CREATE PROCEDURE InsertC
@.x varchar(32)
AS
INSERT INTO TableThree select @.x
GO
BEGIN TRAN
EXEC insertA 'A'
WAITFOR DELAY '000:00:59'
EXEC insertB 'B'
EXEC insertC 'C'
COMMIT TRANI assume you found data in all three tables? In this case, the entire batch
was sent to SQL Server where it executed to completion event though the
client was unplugged from the network. It's just that SQL Server couldn't
notify the client after the transaction completed successfully.
Hope this helps.
Dan Guzman
SQL Server MVP
"Lara" <aneeshattingal@.hotpop.com> wrote in message
news:OEZYoCmQFHA.2384@.tk2msftngp13.phx.gbl...
> Hi,
> I got a problem while testing the BEGIN TRAN.. COMMIT TRAN. I have
> created
> 3 tables and 3 sps for inserting values into the table.
> Now I ran a query to call the 3 sps inside a Begi.. commit tranaction
> which
> is given below (of course there is WAITFOR DELAY statement in between the
> first to sp calls), but before executing completely i disaqbled the
> network.
> When i reconnect it and checked the values in the tables, i found the
> entries. i wonder how this happened before committing the operation. Can
> anyone help me. The code is given below
> regards Lara
>
> CREATE TABLE [TableOne] (
> [a] [int] IDENTITY (1, 1) NOT NULL ,
> [atext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [TableThree] (
> [c] [int] IDENTITY (1, 1) NOT NULL ,
> [ctext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [TableTwo] (
> [b] [int] IDENTITY (1, 1) NOT NULL ,
> [btext] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE PROCEDURE InsertA
> @.x varchar(32)
> as
> insert into TableOne select @.x
> GO
> CREATE PROCEDURE InsertB
> @.x varchar(32)
> as
> BEGIN
> --WAITFOR DELAY '000:00:59'
> INSERT INTO TableTwo select @.x
> END
> GO
> CREATE PROCEDURE InsertC
> @.x varchar(32)
> AS
> INSERT INTO TableThree select @.x
> GO
> BEGIN TRAN
> EXEC insertA 'A'
> WAITFOR DELAY '000:00:59'
> EXEC insertB 'B'
> EXEC insertC 'C'
> COMMIT TRAN
>
Pbm Migrating Access 2003 Database to Sql 2005Express Edition
I tried various ways to migrate an access 2003 database with 1 table to
sql Express 2005 Edition without success, I can only find the resulted
sql database in Sql Server Express Edition 2005 without data.
Please advise if any help.
This article might help:
http://support.microsoft.com/kb/237980
Buck Woody
|||Check that you don't have any replication field in the table. This will cause it to fail in migration.sqlPbm Migrating Access 2003 Database to Sql 2005Express Edition
I tried various ways to migrate an access 2003 database with 1 table to
sql Express 2005 Edition without success, I can only find the resulted
sql database in Sql Server Express Edition 2005 without data.
Please advise if any help.
This article might help:
http://support.microsoft.com/kb/237980
Buck Woody
|||Check that you don't have any replication field in the table. This will cause it to fail in migration.Pb table scan
I have a query like this :
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn
04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a crit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
choice[vbcol=seagreen]
traceon,[vbcol=seagreen]
understand[vbcol=seagreen]
>
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn
04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a crit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
choice[vbcol=seagreen]
traceon,[vbcol=seagreen]
understand[vbcol=seagreen]
>
Pb table scan
I have a query like this :
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a écrit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
> > I have a query like this :
> >
> > select * from table
> > where champs1=val1 and champs2 =val2
> > I have an index on (champs1,champs2).
> > The table has 698694 rows.
> > The table scan cost 17770 i/os.
> >
> > If the number of rows selected is greater than 4700, the optimizer
choice
> > the table scan. If i force the index, i have 4762 i/os ( is less than
> > 17770 )
> >
> > I don't know why the optimizer choice the table scan ( is there a
traceon,
> > command like the dbcc traceon(302) in sybase, that help you to
understand
> > the choice of the optimizer ? ).
> >
> > Thanks and sorry for my poor english.
> >
> >
> >
> >
> >
> >
>
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a écrit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
> > I have a query like this :
> >
> > select * from table
> > where champs1=val1 and champs2 =val2
> > I have an index on (champs1,champs2).
> > The table has 698694 rows.
> > The table scan cost 17770 i/os.
> >
> > If the number of rows selected is greater than 4700, the optimizer
choice
> > the table scan. If i force the index, i have 4762 i/os ( is less than
> > 17770 )
> >
> > I don't know why the optimizer choice the table scan ( is there a
traceon,
> > command like the dbcc traceon(302) in sybase, that help you to
understand
> > the choice of the optimizer ? ).
> >
> > Thanks and sorry for my poor english.
> >
> >
> >
> >
> >
> >
>
Pb table scan
I have a query like this :
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.
Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a crit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
choice[vbcol=seagreen]
traceon,[vbcol=seagreen]
understand
>
sql
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.
Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>
|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a crit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
choice[vbcol=seagreen]
traceon,[vbcol=seagreen]
understand
>
sql
Wednesday, March 21, 2012
Pause between pages when printing RS report
I am using Report Services on SQL Server 2005. I have a report that gets its data from a single SQL Server stored procedure. One other small table is used, but only to provide a list of values for selecting a parameter value to send to the stored procedure.
On the report I use a list control to display multiple records per page.
When printing the report (which currently produces over 500 pages - future printings of this report will not be nearly so large), the printer pauses between each page as if they are seperate print jobs. This is an HP LaserJet 5Si that normally cranks out reports very fast.
The only thing I can think of that might contribute to this sort of behavior is the fact that I set my margin sizes to 0in to get as much design room on the report as possible. Could that cause this sort of behavior?
Just bumping this topic. I'm very much in need of an answer on this. I've tried different settings for the margins as well, but anything other than 0in. causes extra pages to be printed.
How can I eliminate the pause between pages?|||How are you printing the report? Are you using the printing via the web or using delivery? I have not heard of this sort of behavior. The only thing I can think of is that the server is busy rendering each page and is causing the printer to wait for additional pages.
-Daniel|||Thanks for the reply.
I am printing the reports via the web. This behavior is exhibited when printing them from the Visual Studio design environment as well.
|||Most likely, your printer is able to print faster than the ability of the server to generate the pages. This could happen if the pages are complex and/or have lots of images. The overall time should be similar to exporting the report to an image format.|||I guess I can see where the report generation might be the bottleneck, however it is just a standard report with a basic list control displaying some 20-25 data fields per record. There are no images being used at all. The most graphically intensive element is a light gray filled box around the report title.
Honestly, I can't imagine a much simpler report. That is why I have trouble accepting this as being as good as it gets.
|||The fact that you see this in the designer as well as the server seems to mean that the issue may lie elsewhere. Do any other reports behave this way? Are you able to try any other printers?
When you print from the web you can preview the report first. Can you first preview the entire report before printing? By doing this you will have downloaded all the pages to the client, when you actually print you will just be sending the emf file to the printer. If the problem still occurs then it is most likely an issue downstream from RS if it doesn't occur then RS would have some issue.
Thanks.
-Daniel
Pause between pages when printing RS report
I am using Report Services on SQL Server 2005. I have a report that gets its data from a single SQL Server stored procedure. One other small table is used, but only to provide a list of values for selecting a parameter value to send to the stored procedure.
On the report I use a list control to display multiple records per page.
When printing the report (which currently produces over 500 pages - future printings of this report will not be nearly so large), the printer pauses between each page as if they are seperate print jobs. This is an HP LaserJet 5Si that normally cranks out reports very fast.
The only thing I can think of that might contribute to this sort of behavior is the fact that I set my margin sizes to 0in to get as much design room on the report as possible. Could that cause this sort of behavior?Just bumping this topic. I'm very much in need of an answer on this. I've tried different settings for the margins as well, but anything other than 0in. causes extra pages to be printed.
How can I eliminate the pause between pages?|||How are you printing the report? Are you using the printing via the web or using delivery? I have not heard of this sort of behavior. The only thing I can think of is that the server is busy rendering each page and is causing the printer to wait for additional pages.
-Daniel|||Thanks for the reply.
I am printing the reports via the web. This behavior is exhibited when printing them from the Visual Studio design environment as well.|||Most likely, your printer is able to print faster than the ability of the server to generate the pages. This could happen if the pages are complex and/or have lots of images. The overall time should be similar to exporting the report to an image format.|||I guess I can see where the report generation might be the bottleneck, however it is just a standard report with a basic list control displaying some 20-25 data fields per record. There are no images being used at all. The most graphically intensive element is a light gray filled box around the report title.
Honestly, I can't imagine a much simpler report. That is why I have trouble accepting this as being as good as it gets. 
When you print from the web you can preview the report first. Can you first preview the entire report before printing? By doing this you will have downloaded all the pages to the client, when you actually print you will just be sending the emf file to the printer. If the problem still occurs then it is most likely an issue downstream from RS if it doesn't occur then RS would have some issue.
Thanks.
-Danielsql
Patterns for table design
I am looking for something similar to patterns for table design, but I have been unable to find anything on that, or good rule of thumb performance guides for table design.
Most of the situations we face look like perfect candidates for patterns, and some good rules of thumb -- problems like scalable OLAP requirements PLUS real time reporting.
I am looking for several differnet approaches and a good summary of the trade-offs for each one.
Thanks!
You're in luck...the newest book in the Martin Fowler Signature Series is on Database Refactoring
http://martinfowler.com/books.html#refactoringDatabases
I haven't read it yet but looking at the website (that has a catalog of the patterns), it seems to be pretty thorough. It goes hand in hand with Martin's Patterns of Enterprise Application Architecture and the other book in the series on Enterprise Integration Patterns.
Subscribe to:
Posts (Atom)