Number of statements during a specific time

Dear fellows,
I'd like to see how many queries are launched in a hour in a live server. In
order to achieve such information I've though in to open profiler applicatio
n
and then save that information on a .TRC file (encompassing two hours for
example). After that, by DTS export that file to a table and to retrieve the
number of SELECT commited with the help of specific strings (Textdata and
loginname). That will mean know the number of queries launched for our
developers from its QA sessions as well as queries and another things for an
y
kind of processes (If I am not wrong)
It take part of a set of measures intended to demonstrate that we'd need
news hardware requirements, hd, and so on.
Any help/though/advice of how do this better? In aid of faster or reliabilit
y.
Thanks a lot for your input,It is not a good solution due to I've got now 140mb of tracefile for just a
hour!!
Let me know any idea...
"Enric" wrote:

> Dear fellows,
> I'd like to see how many queries are launched in a hour in a live server.
In
> order to achieve such information I've though in to open profiler applicat
ion
> and then save that information on a .TRC file (encompassing two hours for
> example). After that, by DTS export that file to a table and to retrieve t
he
> number of SELECT commited with the help of specific strings (Textdata and
> loginname). That will mean know the number of queries launched for our
> developers from its QA sessions as well as queries and another things for
any
> kind of processes (If I am not wrong)
> It take part of a set of measures intended to demonstrate that we'd need
> news hardware requirements, hd, and so on.
> Any help/though/advice of how do this better? In aid of faster or reliabil
ity.
> Thanks a lot for your input,|||try use performans counters - perfmon (sqlserver:sqlserverstatistics)
but... is is not idealy metod to demonstrate that you need new hardware...
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Enric" wrote:

> Dear fellows,
> I'd like to see how many queries are launched in a hour in a live server.
In
> order to achieve such information I've though in to open profiler applicat
ion
> and then save that information on a .TRC file (encompassing two hours for
> example). After that, by DTS export that file to a table and to retrieve t
he
> number of SELECT commited with the help of specific strings (Textdata and
> loginname). That will mean know the number of queries launched for our
> developers from its QA sessions as well as queries and another things for
any
> kind of processes (If I am not wrong)
> It take part of a set of measures intended to demonstrate that we'd need
> news hardware requirements, hd, and so on.
> Any help/though/advice of how do this better? In aid of faster or reliabil
ity.
> Thanks a lot for your input,

NULLS and ''

Hi,

I don't have any specific details as I do not really understand why
this is happening but we have a customer database that we have been
using and queries on text fields have begun returning empty string
values instead of NULL.

So for example:

SELECT TAB1.DESCRIPTION
FROM TABLE1 TAB1
WHERE TAB1.DESCRIPTION IS NOT NULL
ORDER BY 1

may have returned 50 records in the past (purely an example). It is
now returning an additional record first that appears just to be ''.
Now I am not too sure where to begin looking into this. ODBC
connections, collation settings? I just am not sure where to begin.
The column will not have had '' inserted into it; therefore it should
be NULL.

I have posted this mainly so I can see if anybody else has seen this
sort of behaviour - I cannot see why this has suddenly happened.
The collation setting on our server is different to that of the
customer DB, but this shouldn't make a difference should it?

If anybody has any ideas then I can post some more information.
Thanks,

PaulOn Feb 6, 3:04 am, "Paul" <paulwragg2...@.hotmail.comwrote:

Quote:

Originally Posted by

Hi,
>
I don't have any specific details as I do not really understand why
this is happening but we have a customer database that we have been
using and queries on text fields have begun returning empty string
values instead of NULL.
>
So for example:
>
SELECT TAB1.DESCRIPTION
FROM TABLE1 TAB1
WHERE TAB1.DESCRIPTION IS NOT NULL
ORDER BY 1
>
may have returned 50 records in the past (purely an example). It is
now returning an additional record first that appears just to be ''.
Now I am not too sure where to begin looking into this. ODBC
connections, collation settings? I just am not sure where to begin.
The column will not have had '' inserted into it; therefore it should
be NULL.
>
I have posted this mainly so I can see if anybody else has seen this
sort of behaviour - I cannot see why this has suddenly happened.
The collation setting on our server is different to that of the
customer DB, but this shouldn't make a difference should it?
>
If anybody has any ideas then I can post some more information.
Thanks,
>
Paul

Changes that could cause this include:
1. setting a default value of '' for a column
2. added a default value of '' to a sproc that inserted records
3. changing NULL to '' in the insert in the application|||Hi Steve,

Unfortunately I have already covered all of these and determined that
it is not possible for this to have happened (unless the customer had
done this without our knowledge).

The thing is that they have not reported this as an issue, and there
are some areas of the system where it would show up for sure.

It seems only to have happened once we put their DB in to our
environment.

Thanks for the reply anyway!

Paul|||Hi Paul,

Here is one more option to check. I have seen similar behavior on client
databases when somebody turned the CONCAT_NULL_YIELDS_NULL option to OFF.
Basically when it is OFF a string concatenation like SELECT TAB1.DESCRIPTION
+ NULL will return the string in TAB1.DESCRIPTION, not NULL. When
CONCAT_NULL_YIELDS_NULL is ON then SELECT TAB1.DESCRIPTION + NULL will
return NULL. I am not sure what is the logic and SQL of your application but
if you have string concatenation it could be it.

A good example of how it will affect returned records is if you have a query
like this:

SELECT FirstName + ' ' + MiddleInitial + ' ' + LastName AS EmployeeName
FROM Employees
WHERE FirstName + ' ' + MiddleInitial + ' ' + LastName IS NOT NULL

Then if you have MiddleInitial set to NULL for some records you will see
different results based on CONCAT_NULL_YIELDS_NULL being ON or OFF.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Thanks Plamen, I am just about to go home for the evening so I will
check this out tommorrow.

I just wanted to post an update before I go home though.

I have been looking a bit more in to this and I have found that the
following seems even stranger to me.

If I have a column DESCRIPTION VARCHAR(250) and the value is empty
i.e. NULL then
shouldn't doing a:

SELECT TAB1.DESCRIPTION
FROM TABLE1 TAB1
WHERE TAB1.DESCRIPTION IS NOT NULL
AND DATALENGTH(TAB1.DESCRIPTION) = 0

always return zero records?
A VARCHAR is variable length based on the actual data stored in it, so
shouldn't a VARCHAR column of zero DATALENGTH be NULL?

If anybody can explain this then please do!

I will be looking in to Plamens suggestion tommorrow but in the
meantime if anybody has any other ideas it will be appreciated!

Thanks,

Paul|||On Feb 6, 10:25 am, "Paul" <paulwragg2...@.hotmail.comwrote:

Quote:

Originally Posted by

Thanks Plamen, I am just about to go home for the evening so I will
check this out tommorrow.
>
I just wanted to post an update before I go home though.
>
I have been looking a bit more in to this and I have found that the
following seems even stranger to me.
>
If I have a column DESCRIPTION VARCHAR(250) and the value is empty
i.e. NULL then
shouldn't doing a:
>
SELECT TAB1.DESCRIPTION
FROM TABLE1 TAB1
WHERE TAB1.DESCRIPTION IS NOT NULL
AND DATALENGTH(TAB1.DESCRIPTION) = 0
>
always return zero records?
A VARCHAR is variable length based on the actual data stored in it, so
shouldn't a VARCHAR column of zero DATALENGTH be NULL?
>
If anybody can explain this then please do!
>
I will be looking in to Plamens suggestion tommorrow but in the
meantime if anybody has any other ideas it will be appreciated!
>
Thanks,
>
Paul

An empty string has a DATALENGTH of 0 and is not NULL|||Hi Paul,

There is a difference between NULL (or unknown value) and blank string (i.e.
''). If your column is NULL then DATALENGTH will return NULL, but if the
column is blank string then it will return 0.

As for your question if your query will always return zero records, that is
not correct. If you have a row where the DESCRIPTION column is a blank
string ('') then DATALENGTH will return 0 and since at the same time the
column is not NULL it will return it in the result set. Here is an example
to demonstrate this:

CREATE TABLE #Test (id int identity(1,1), description varchar(250) NULL)

INSERT INTO #Test (description) VALUES ('')
INSERT INTO #Test (description) VALUES (NULL)

SELECT id, description
FROM #Test
WHERE description IS NOT NULL
AND DATALENGTH(description) = 0

DROP TABLE #Test

The query above will return the first inserted row because it is not NULL
and the DATALENGTH of the blank string is 0.

Regards,

Plamen Ratchev
http://www.SQLStudio.com|||Paul (paulwragg2323@.hotmail.com) writes:

Quote:

Originally Posted by

If I have a column DESCRIPTION VARCHAR(250) and the value is empty
i.e. NULL then
shouldn't doing a:
>
SELECT TAB1.DESCRIPTION
FROM TABLE1 TAB1
WHERE TAB1.DESCRIPTION IS NOT NULL
AND DATALENGTH(TAB1.DESCRIPTION) = 0
>
always return zero records?
A VARCHAR is variable length based on the actual data stored in it, so
shouldn't a VARCHAR column of zero DATALENGTH be NULL?

No:

DECLARE @.x varchar(234)
SELECT @.x = ''
SELECT isnull = CASE WHEN @.x IS NULL THEN 1 ELSE 0 END,
bytes = datalength(@.x)

NULL and the empty string are two different values. Or maybe they the
same two values. After all, NULL is the unknown value, so you don't
know what it is. The empty string, on the other hamd, is a known value.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks to everybody for clarifying this for me. It still doesn't solve
my problem of how records that should be NULL are actually empty
strings - this should not have happened and I still cannot see why it
should have. Maybe it is our application after all. But if it was the
application then I would have hoped to see the same behaviour on
Oracle, but I do not, all the equivalent fields on Oracle are NULL
rather than ''.

If I do find anything I will post the results back here.

Thanks to everybody for their time,

Paul|||Just to contradict my last post, I thought I would check this on
Oracle - I know this is a SQL Server newsgroup but I needed to check.
So if we have the following on SQL Server:

CREATE TABLE TEST
(COL1 VARCHAR(50))
GO

INSERT INTO TEST (COL1) VALUES (NULL)
GO

INSERT INTO TEST (COL1) VALUES ('')
GO

SELECT COUNT(1)
FROM TEST
WHERE COL1 IS NOT NULL;
GO

----
1

(1 row(s) affected)

So here, on SQL Server there is 1 row that is NOT NULL - the '' row.
Now doing the equivalent on Oracle:

SQLCREATE TABLE TEST
2 (COL1 VARCHAR(50));

Table created.

Elapsed: 00:00:00.07
SQLINSERT INTO TEST (COL1) VALUES (NULL);

1 row created.

Elapsed: 00:00:00.00
SQLINSERT INTO TEST (COL1) VALUES ('');

1 row created.

Elapsed: 00:00:00.00
SQLSELECT COUNT(1)
2 FROM TEST
3 WHERE COL1 IS NOT NULL;

COUNT(1)
----
0

1 row selected.

So it appears that on SQL Server '' is treated as a known value i.e.
an empty string, whereas on Oracle '' is treated as NULL - maybe this
is because we do not specify anything between the quotes to insert.
Either way this is interesting, and could signify why we are now
seeing the problem on SQL Server but not on Oracle.

Paul|||So, it is our application by the looks of things. We will now have to
implement a function so that instead of saving '' we save NULL.

Thanks for all those who helped with this!

Paul|||On Feb 7, 3:20 am, "Paul" <paulwragg2...@.hotmail.comwrote:

Quote:

Originally Posted by

Just to contradict my last post, I thought I would check this on
Oracle - I know this is a SQL Server newsgroup but I needed to check.
So if we have the following on SQL Server:
>
CREATE TABLE TEST
(COL1 VARCHAR(50))
GO
>
INSERT INTO TEST (COL1) VALUES (NULL)
GO
>
INSERT INTO TEST (COL1) VALUES ('')
GO
>
SELECT COUNT(1)
FROM TEST
WHERE COL1 IS NOT NULL;
GO
>
----
1
>
(1 row(s) affected)
>
So here, on SQL Server there is 1 row that is NOT NULL - the '' row.
Now doing the equivalent on Oracle:
>
SQLCREATE TABLE TEST
2 (COL1 VARCHAR(50));
>
Table created.
>
Elapsed: 00:00:00.07
SQLINSERT INTO TEST (COL1) VALUES (NULL);
>
1 row created.
>
Elapsed: 00:00:00.00
SQLINSERT INTO TEST (COL1) VALUES ('');
>
1 row created.
>
Elapsed: 00:00:00.00
SQLSELECT COUNT(1)
2 FROM TEST
3 WHERE COL1 IS NOT NULL;
>
COUNT(1)
----
0
>
1 row selected.
>
So it appears that on SQL Server '' is treated as a known value i.e.
an empty string, whereas on Oracle '' is treated as NULL - maybe this
is because we do not specify anything between the quotes to insert.
Either way this is interesting, and could signify why we are now
seeing the problem on SQL Server but not on Oracle.
>
Paul

There are more similar examples here:

www.devx.com/dbzone/Article/32852
--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||Thanks for the link Alex - some very interesting points in there that
I am sure will be useful in the future.

Thanks again,

Paul

Null values in line charts

Hello,
I use the RS to charting some blood tests results (i work in an hospital).
These results have:
Date:DateTime
Results:Int
At a specific Date one blood test has not a result (in database is NULL).
The problem is that in line chart this appear as 0 values (this is very bad
for our patient-for doctors this mean something about the patient health).
My table
Date Test1Result Test2Result
12.02.2004 125 34
16.04.2004 NULL 25
18.08.2004 128 NULL
In the report i have 2 line charts 1 for test1result and 1 for test2resolt.
The doctors must see both charts. How to hide this 0 values (something like
Excel with zero values)
Thank youIn the field (or textbox) in which the value displays (the zero you
dont want) - Change the expression to say:
IIF(IsDbNull(Fields!Test1Result.Value) = True, (whatever value you want
to return instead of zero), Fields!Test1Result.Value)
Optionally - if this is from a Stored Procedure you could change the
Procedure to replace NULLS with someother more useful value.
The context here is a little vague.. I hope this helps.|||Thank you for your help. I can't insert any value here because this reflect
the patient healt state.
"papaboom" wrote:
> In the field (or textbox) in which the value displays (the zero you
> dont want) - Change the expression to say:
> IIF(IsDbNull(Fields!Test1Result.Value) = True, (whatever value you want
> to return instead of zero), Fields!Test1Result.Value)
> Optionally - if this is from a Stored Procedure you could change the
> Procedure to replace NULLS with someother more useful value.
> The context here is a little vague.. I hope this helps.
>|||Please read this thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a3bce317-0626-47f4-9ec2-aebe559a41b3&sloc=en-us
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"cleo_mihai" <cleo_mihai@.discussions.microsoft.com> wrote in message
news:DDCBA342-D771-4868-8009-5FC30D5ECB26@.microsoft.com...
> Hello,
> I use the RS to charting some blood tests results (i work in an hospital).
> These results have:
> Date:DateTime
> Results:Int
> At a specific Date one blood test has not a result (in database is NULL).
> The problem is that in line chart this appear as 0 values (this is very
bad
> for our patient-for doctors this mean something about the patient health).
> My table
> Date Test1Result Test2Result
> 12.02.2004 125 34
> 16.04.2004 NULL 25
> 18.08.2004 128 NULL
> In the report i have 2 line charts 1 for test1result and 1 for
test2resolt.
> The doctors must see both charts. How to hide this 0 values (something
like
> Excel with zero values)
> Thank you
>|||Hy,
After 5 months and sp2 of RS, is time to try to solve my problem (unsolved
yet).
First, because the 0 value can't appear without a reason (this value
represent something-(eventually the patient's health status is bad)- i can't
replace the null's with any value (or 0). I can not decide if patient health
is good or bad. This must be a result from laboratory.
Second, is so complicated in RS to hide the null values? In Excel is easy
"Plot empty cells as: Interpolated".
If this is impossible in RS please let me know, to look for other charting
solutions.
Thank you very much for your time,
"cleo_mihai" wrote:
> Hello,
> I use the RS to charting some blood tests results (i work in an hospital).
> These results have:
> Date:DateTime
> Results:Int
> At a specific Date one blood test has not a result (in database is NULL).
> The problem is that in line chart this appear as 0 values (this is very bad
> for our patient-for doctors this mean something about the patient health).
> My table
> Date Test1Result Test2Result
> 12.02.2004 125 34
> 16.04.2004 NULL 25
> 18.08.2004 128 NULL
> In the report i have 2 line charts 1 for test1result and 1 for test2resolt.
> The doctors must see both charts. How to hide this 0 values (something like
> Excel with zero values)
> Thank you
>|||Are you sure you have tried this on SP2? Empty data points (i.e. value =Nothing) in line and area charts will be ignored and "interpolated".
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"cleo_mihai" <cleomihai@.discussions.microsoft.com> wrote in message
news:9B015D0A-BB60-4615-8410-91F11832E3FD@.microsoft.com...
> Hy,
> After 5 months and sp2 of RS, is time to try to solve my problem (unsolved
> yet).
> First, because the 0 value can't appear without a reason (this value
> represent something-(eventually the patient's health status is bad)- i
> can't
> replace the null's with any value (or 0). I can not decide if patient
> health
> is good or bad. This must be a result from laboratory.
> Second, is so complicated in RS to hide the null values? In Excel is easy
> "Plot empty cells as: Interpolated".
> If this is impossible in RS please let me know, to look for other charting
> solutions.
> Thank you very much for your time,
>
> "cleo_mihai" wrote:
>> Hello,
>> I use the RS to charting some blood tests results (i work in an
>> hospital).
>> These results have:
>> Date:DateTime
>> Results:Int
>> At a specific Date one blood test has not a result (in database is NULL).
>> The problem is that in line chart this appear as 0 values (this is very
>> bad
>> for our patient-for doctors this mean something about the patient
>> health).
>> My table
>> Date Test1Result Test2Result
>> 12.02.2004 125 34
>> 16.04.2004 NULL 25
>> 18.08.2004 128 NULL
>> In the report i have 2 line charts 1 for test1result and 1 for
>> test2resolt.
>> The doctors must see both charts. How to hide this 0 values (something
>> like
>> Excel with zero values)
>> Thank you

NULL Entry in Lookup

Hi,

In my Integration Scenario I have a source field 'CompanyTypeCode' which is nullable in 'tblCompany' and There is a specific lookup Table 'tblCompanyType' to map the codes with descriptions for this field. When I want to transform this table, The NULL Entries fail in Lookup and I ran into problem. Please suggest a clean solution for bypassing the lookup when the entry in source is NULL. (If I bypass the lookup, the flow changes since there is not an extra column from the lookup component any more, therefore I must have another OLEDB Destination if the code is NULL).

Regards,
Sassan

Sassan,

You can still have the extra column if the lookup fails. What you do is in your error path from the lookup add a derived column for the description and put an "Unknown type" string in there. That way both the pipelines will have the same number of data columns.
You can then 'union all' the clean and error outputs from the lookup to a single destination.

I don't think you should bypass the lookup, but if you want to do it, how about using a conditional split before the lookup to re-route the NULL rows?

Hope that helps.