Hidden Columns Problem in Excel

Hi there,

Been so annoyed to get rid of all the hidden columns after exporting my report to Excel which when I merge and centre then unhide, shows blank columns. The hidden column could appear in the middle of report or at the end of each report.

Known from somewhere that this might cause by the below mentioned facts and i've got some of the solutions with me but may i know is there a guideline to follow when designing a report for Excel and meanwhile get rid of hidden column's problem ?

i. The width of each and every tables are not aligned when there are more than 2 tables presented in a page vertically (each one on top of another) and therefore, gotta make sure the location from the left (start location) for all tables are the same, and the total width of the table are equal to the sum of width of each column, and also the length of every single column in all tables is align

--SOMEHOW, this is not enough to fix the problem and so i have to try steps ii (as follows).

ii. The text width in a field is greater than the width of the field. (Need to keep on trying for various width until the hidden column gone) e.g. i had allocate 2cm for a 'int' type field and apparently, i could see from preview the value just occupied bout half of the field. However, i need to expand the field to 3cm at last to accomodate the value or hidden column may appear when i export to Excel.

--SO, is there a way to know how much space a field needs and is data type affect the column width needed?

iii. Matrixes' width is greater than page width after expanding (This is hard to measure because we do not know how many columns a matrix is going to expand), and the best i could do is to set it less than or equal to the lenght of (page width - left margin -right margin) but somehow the hidden column still appear

-- ANY better solution to deal with this?

Any comment is welcome and thank you in advance!

Cheers,

Julez

Here is some information about merged cells with the Excel renderer:

http://blogs.msdn.com/chrisbal/archive/2006/07/08/659545.aspx

-Chris

|||

Hi Chris,

Thank you so much! This is really helpful!! =)

Cheers,

Julez

hi

can anyone tell me in what situation you should force a hash join or merge
join option in a query?When it gives you better performance than the plan the optimizer choose, and
when you have done everything possible to encourage the optimizer to find
the best plan on its own, like building better indexes.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ukZYFz1HFHA.1172@.TK2MSFTNGP12.phx.gbl...
> can anyone tell me in what situation you should force a hash join or merge
> join option in a query?
>
>|||Or updating your statistics with
EXEC sp_updatestats
However, make sure the actual execution is better than the plan offerred by
the Optimizer, not just the estimated plan.
Sincerely,
Anthony Thomas
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u8iL$51HFHA.1396@.TK2MSFTNGP10.phx.gbl...
When it gives you better performance than the plan the optimizer choose, and
when you have done everything possible to encourage the optimizer to find
the best plan on its own, like building better indexes.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ukZYFz1HFHA.1172@.TK2MSFTNGP12.phx.gbl...
> can anyone tell me in what situation you should force a hash join or merge
> join option in a query?
>
>

Help-me merge replication

Good morning.

I have a big problem with merge replication.

This a scenary.

I have one database SQL 2000 in my server, and one aplication in PDA with SQL CE, but a read very turorials and always ask for a developer another aplication for a sincronization !

Exists one control in SQL CE for this ?

[]'s

You're in the wrong place. try the Replication forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=90&SiteID=1

-Jamie

|||Sorry

HELP: MSmerge_past_partition_mappings is Huge !

I have a SQL 2005 Merge Replication setup with SQL Mobile Subscribers. The
merge replication performance has been incredibly slow (a couple of hours in
some cases). I looked at the size of some the Replication tables and their
sizes are huge compared with the rest of my db tables e.g.
MSmerge_past_partition_mappings637451 Rows
MSmerge_current_partition_mappings350554 Rows
MSmerge_contents252368 Rows
The most amount of rows in any of my user defined tables is under 200,000
Rows. My question is - is there any way to fix these Replication tables
either my deleting the rows, running a cleanup script, etc. without affecting
the Subscribers?
Thanks for the help
http://www.mssqlserver.com/replication/purgemetadata.asp
This article will help you understand why the meger metadata tables can get
quite big.
You can run sp_mergemetadataretentioncleanup procedure to clean up some
metadata.
http://msdn2.microsoft.com/en-us/library/ms178557.aspx
Hope this helps.
"Cube" <ciaran@.nospam.insero-technologies.com> wrote in message
news:3F420705-1567-4257-9570-393795D005D8@.microsoft.com...
>I have a SQL 2005 Merge Replication setup with SQL Mobile Subscribers. The
> merge replication performance has been incredibly slow (a couple of hours
> in
> some cases). I looked at the size of some the Replication tables and their
> sizes are huge compared with the rest of my db tables e.g.
> MSmerge_past_partition_mappings 637451 Rows
> MSmerge_current_partition_mappings 350554 Rows
> MSmerge_contents 252368 Rows
> The most amount of rows in any of my user defined tables is under 200,000
> Rows. My question is - is there any way to fix these Replication tables
> either my deleting the rows, running a cleanup script, etc. without
> affecting
> the Subscribers?
> Thanks for the help
>

HELP: Merge Agent failed - Error number: MSSQL_REPL-2147199402)

We have 50+ clients that use a pull merge subscription. There are 2
databases and each databases has only 1 publication. Each client has ONE
subscription to each of the 2 databases.
We had to set the retention to unlimited to avoid the following error:
The Merge Agent failed after detecting that retention-based metadata cleanup
has deleted metadata at the Publisher for changes not yet sent to the
Subscriber. You must reinitialize the subscription (without upload).
(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199402)
Get help: http://help/MSSQL_REPL-2147199402
The article http://support.microsoft.com/kb/919929/kb/919929 has a hotfix
BUT it says that it is if a Subscriber subscribes to two publications in the
same Subscriber database. This is not the case.
We had a ticket open with Miscrosoft but they were unable to find the
solution for it after 4 or 5 weeks.
The clients use their subscription databases directly and they are synced to
the server every 30 minutes.
Anybody have any idea what is causing this and how to solve this problem.
The LDF's are getting quite large and we need to find a solution soon.
Thanks in advance!
I fail to understand how the ldf's fit in here. It sounds like there are
some long running transactions which transcend the log dump boundaries. Have
you investigated what these open transactions are? Are they replication
related?
Hilary Cotter
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
"RichardD" <RichardD@.discussions.microsoft.com> wrote in message
news:B0445940-5C26-403B-8589-692458B82098@.microsoft.com...
> We have 50+ clients that use a pull merge subscription. There are 2
> databases and each databases has only 1 publication. Each client has ONE
> subscription to each of the 2 databases.
> We had to set the retention to unlimited to avoid the following error:
> The Merge Agent failed after detecting that retention-based metadata
> cleanup
> has deleted metadata at the Publisher for changes not yet sent to the
> Subscriber. You must reinitialize the subscription (without upload).
> (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199402)
> Get help: http://help/MSSQL_REPL-2147199402
> The article http://support.microsoft.com/kb/919929/kb/919929 has a hotfix
> BUT it says that it is if a Subscriber subscribes to two publications in
> the
> same Subscriber database. This is not the case.
> We had a ticket open with Miscrosoft but they were unable to find the
> solution for it after 4 or 5 weeks.
> The clients use their subscription databases directly and they are synced
> to
> the server every 30 minutes.
> Anybody have any idea what is causing this and how to solve this problem.
> The LDF's are getting quite large and we need to find a solution soon.
> Thanks in advance!
>
|||I also expect, the size of the log file has nothing to do with your
retention period.
I'm afraid I can't help on your retention period problem, but perhaps
your ldfs are growing because of a combination of the recovery model
your database is using, and your backup strategy.
Have a look at the following articles, perhaps they will help:
http://msdn2.microsoft.com/en-us/library/ms175987.aspx
http://msdn2.microsoft.com/en-us/library/ms175495.aspx
Tim Hill
On Feb 23, 6:24 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> I fail to understand how the ldf's fit in here. It sounds like there are
> some long running transactions which transcend the log dump boundaries. Have
> you investigated what these open transactions are? Are they replication
> related?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "RichardD" <Richa...@.discussions.microsoft.com> wrote in message
> news:B0445940-5C26-403B-8589-692458B82098@.microsoft.com...
>
>
>
>
|||You are right. I have already fixed the issue with the log files.
Regarding the error MSSQL_REPL-2147199402 we suspect that the subscribers
changed their computer date beyond 14 days (default retention) and it caused
this error.
One reason we suspect this is that out of 40+ subscribers to the one
publication, only a few had this problem and a couple of them had it happen
twice.
Does this sound like a possibility to you?
We are about to change their retention from unlimited back to 14 days. Is
there anything we should be aware of before doing this?
Thanks so much for your help.
|||Yes, we figured out the log file issue. Thank you anyway.
We are still looking for the solution for the Error number:
MSSQL_REPL-2147199402.
Any ideas?