No data to print

Hello Guys,
I am having hard time printing "No data to print" when there are no records returned by the report. Rightnow report prints without data but with all column headings. I would appreciate hearing from you soon.
Thanks
Dilemma

:wave:Hi,
In all headers suppress events write the formula as below
if isnull({DatabaseFeild}) =True then True
Place a new header in the header showing the value "No data to Print"
Now in the suppress event of this header write the formula as
if isnull({DatabaseFeild}) =false then True
Madhivanan|||Hello Madhi,

Thanks for the reply. I appreciate your help.

I am just wondering how do u write code header supress event. Do u have to write some code in your eg. VB or PB's event or in crystal report designer itself ? Unfortunately I am using PowreBuilder (pb) for the development.

Thanks
Dilemma|||Hi,
You can use this code in the crystal report itself.
Right click on header and select format section. A pop up menu will appear. There will be a check box labelled as Suppress. In parallel to that a button labelled X-2 is there. Click that button and write the coding I have mentioned. You have to this process for each header. To display "No data to Print", just add a text object and write it as "No data to Print" and in the suppress option write the second formula.

Madhivanan

no count of zero

Count doesn't seem to be returning zero when there are no records. I've
checked my joins. I've tried ISNULL(Count(*), 0) [which I've NEVER had to do
before] to handle a null count (still no 0). It's a new machine to me, and
I'm wondering if there is some system setting I need to make sure count
returns a zero when no records are returned.
I can switch to returning @.@.RowCount value, but that's not what I'm trying
to do.
Any help?
Thanks
--
Buz WaitzPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
You might want to look up the behavior of an empty table and aggregate
functions; COUNT(*) and COUNT(<exp> ) are not the same.|||Buz (bwaitz@.minormiracles.com.nospamplease) writes:
> Count doesn't seem to be returning zero when there are no records. I've
> checked my joins. I've tried ISNULL(Count(*), 0) [which I've NEVER had
> to do before] to handle a null count (still no 0). It's a new machine to
> me, and I'm wondering if there is some system setting I need to make
> sure count returns a zero when no records are returned.
Better if you post the code you are having problem with. Else we are
completely in the dark of trying to understand what you are trying to do.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Found it. It was a group by clause that caused the problem.
Thanks,
--
Buz Waitz
"--CELKO--" wrote:

> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> You might want to look up the behavior of an empty table and aggregate
> functions; COUNT(*) and COUNT(<exp> ) are not the same.
>

no blocking with bulk insert

I am running in QA:
Bulk insert tableA from 'C:\file.tab'
In another window ,
select * from tableA gives me no records .. I would assume some kind of
blocking while the bulk insert does it jobs. So what exactly does bulk
insert to ? The job is running and the tab file has 50 million records but
still on the second window i can query and in sp_lock all I can see for the
table is an IX tab lock and theres no blocking too. I am using SQL 2000
So whats going on ?In addition after like 15 mins, it started blocking.. So my question is what
was it doing till then.. Does it try to read the file first or something ?
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e#4f7ykUDHA.2112@.TK2MSFTNGP10.phx.gbl...
> I am running in QA:
> Bulk insert tableA from 'C:\file.tab'
> In another window ,
> select * from tableA gives me no records .. I would assume some kind of
> blocking while the bulk insert does it jobs. So what exactly does bulk
> insert to ? The job is running and the tab file has 50 million records but
> still on the second window i can query and in sp_lock all I can see for
the
> table is an IX tab lock and theres no blocking too. I am using SQL 2000
> So whats going on ?
>
>|||No your are experiencing the effect of lock escalation. When you start the
bulk insert with no batch size or a batch size of 0 (zero) the complete file
will be inserted as one batch, so the lock accumulate and at a certain point
in time escalate to a table lock.
In order to prevent this you can take two approaches:
1) Use a batch size of 2499 to prevent the lock escalation form happening
2) Before you start the bulk insert take a rowlock on an entry in the table,
I always do this by inserting a dummy record at the end of the table. Keep
the lock. In another session do the bulk insert, because there is a lock
present you can not escalate to a table lock.
I prefer option 1.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uBzxW5kUDHA.2012@.TK2MSFTNGP10.phx.gbl...
> In addition after like 15 mins, it started blocking.. So my question is
what
> was it doing till then.. Does it try to read the file first or something ?
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e#4f7ykUDHA.2112@.TK2MSFTNGP10.phx.gbl...
> > I am running in QA:
> >
> > Bulk insert tableA from 'C:\file.tab'
> >
> > In another window ,
> >
> > select * from tableA gives me no records .. I would assume some kind of
> > blocking while the bulk insert does it jobs. So what exactly does bulk
> > insert to ? The job is running and the tab file has 50 million records
but
> > still on the second window i can query and in sp_lock all I can see for
> the
> > table is an IX tab lock and theres no blocking too. I am using SQL 2000
> >
> > So whats going on ?
> >
> >
> >
>

Next Range Starting Value Too High

Hi, I've got a push merge replication set up between two sql servers on the
same domain. I have a table with about 1200 records in it. For some reason
when I publish the article and set the identity range values it chooses a
Next Range Starting Value of over 7000000 when there are only the 1200
records in the table (identity range of 100).
The table constraints at the publisher show the range of 1200 to 1300 but at
the subscriber it shows like 7000000 to 7000100.
Can I change it so that it forces it use a more reasonable number like 1300
to 1400?
When you setup the publication what did you specify as the values of
@.pub_identity_range and @.identity_range parameter to sp_addmergearticle?What
did you set as the identity range values in the UI? I believe you must have
set a very high value for the pub identity range and that is the reason you
are seeing these huge numbers on the subscriber. You can change the
pub_identity_range parameter by using sp_changemergearticle. However that
will not affect the max number that has already been allocated. Next time
you setup the publication use lower values for the @.pub_identity_range
parameter.
Thanks,
Jayanthi
"MBurns" <MBurns@.discussions.microsoft.com> wrote in message
news:577A372C-C296-4214-80D1-2D2BE5A25F97@.microsoft.com...
> Hi, I've got a push merge replication set up between two sql servers on
the
> same domain. I have a table with about 1200 records in it. For some reason
> when I publish the article and set the identity range values it chooses a
> Next Range Starting Value of over 7000000 when there are only the 1200
> records in the table (identity range of 100).
> The table constraints at the publisher show the range of 1200 to 1300 but
at
> the subscriber it shows like 7000000 to 7000100.
> Can I change it so that it forces it use a more reasonable number like
1300
> to 1400?

NEWBIE: Replication Archive/deleting

Is it possible to run a query against a table and show records that have
been successfully replicated to the subscriber?
Thanks in advance
Tim
Timmeah,
what type of replication are you using? If it is transactional, you might be
interested in sp_browsereplcmds, and if it is merge, have a look at new! How
to...... Find a list of pending merge changes on
http://www.replicationanswers.com/Merge.asp.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

newbie: processing records sequentially

Hi to all,
I'm a newbie to T-SQL but not to programming. What I want to do is
process every record in a table, and depending on the values in the
record itself, either copy the record to another table or update a
third table, and the delete the original record. Basically, it's a data
preparation area table where the records are imported with no error
check, and then validated against a set of conditions.

THE QUESTION IS: which approach should I follow? Is there a sort of
FORALL loop in T-SQL that I can execute on every record in the table?
Or shall I rely on other tools (VB Scripting, or...)? SQL Server 2000

Thanx to allIn SQL your goal should generally be to avoid processing data a row at
a time. This example may help you:

INSERT INTO AnotherTable (col1, col2, ...)
SELECT col1, col2, ...
FROM YourTable
WHERE ... ? /* unspecified */

UPDATE AnotherTable
SET ... ? /* unspecified */
WHERE ... /* uspecified */

DELETE FROM YourTable
WHERE ...

etc.

Hopefully this gives you the idea. If you need more help, please refer
to the following article which describes the best way to post a fuller
description of your problem:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--|||David,
thanks for prompt response. I understand the process is somehow
reversed from a row-by-row cycle. It seems to me somehow inefficient,
though, to run a query against a third table twice in order to UPDATE
the first time and DELETE the second.

Cheers

David Portas wrote:
> In SQL your goal should generally be to avoid processing data a row at
> a time. This example may help you:
> INSERT INTO AnotherTable (col1, col2, ...)
> SELECT col1, col2, ...
> FROM YourTable
> WHERE ... ? /* unspecified */
> UPDATE AnotherTable
> SET ... ? /* unspecified */
> WHERE ... /* uspecified */
> DELETE FROM YourTable
> WHERE ...
> etc.
> Hopefully this gives you the idea. If you need more help, please refer
> to the following article which describes the best way to post a fuller
> description of your problem:
> http://www.aspfaq.com/etiquette.asp?id=5006|||It depends. INSERT, DELETE and UPDATE are separate operations so will
always require separate statements whatever method you use. The main
question is how best to optimise the WHERE selection criteria. You
didn't specify what those criteria are so I can only guess. It is at
least likely that putting the criteria in WHERE clauses, with suitable
indexes, will be much more efficient than the overhead of looping
through each row in a cursor. Understand that SQL is a declarative
language, unlike procedural languages that you are probably more
familiar with.Cursor processing (row by row) is rarely a good idea in
SQL and when manipulating data should usually be a last resort only.

--
David Portas
SQL Server MVP
--|||>> I'm a newbie to T-SQL but not to programming.<<

You missed the point of a declarative, set-oriented language and it
will take you about a year to un-learn your old mental models. I have
a book entitled SQL PROGRAMMING STYLE that deals with this problem in a
few chapters.

>> What I want to do is process every record [sic] in a table, and
depending on the values in the record [sic] itself, either copy the
record [sic] to another table or update a third table, and the delete
the original record [sic] . <<

This is a common problem; rows are not records, tables are not files
and columns are not fields. Totally differrent concepts. I will give
you the long painful details that I post on this topic if you ask.
Have you ever worked with LISP, FP, Prolog or a language that was not
based on procedures? This is a new way of thinking.

>> Basically, it's a data preparation area table where the records
[sic] are imported with no error check, and then validated against a
set of conditions. <<

This is usually done with an ETL tool these days, but you can do this
with a few statements. If you have an implementation of the new SQL-99
MERGE statement, then you can probably do it in one statement. It will
be a very complex statement, tho :)

BEGIN
UPDATE RawData
SET <column edits>;
DELETE GoodStuff
WHERE EXISTS
(SELECT *
FROM RawData
WHERE <<conditions>>;
INSERT INTO GoodStuff
SELECT a, b, c, ..
FROM RawData
WHERE <<conditions>>;
END;

First scrub the raw data, then remove the old data that is going to be
replaced, and finally insert the new clean data.|||moco (makelovenotspamdaldegan@.email.it) writes:
> thanks for prompt response. I understand the process is somehow
> reversed from a row-by-row cycle. It seems to me somehow inefficient,
> though, to run a query against a third table twice in order to UPDATE
> the first time and DELETE the second.

And with a loop there will be many queries against the table.

But as they say, seeing is believing. Go ahead and bench-mark. You will
be amazed...

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

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