Showing posts with label encountered. Show all posts
Showing posts with label encountered. Show all posts

Friday, March 30, 2012

No Labels Showing in the Graphs

Hi..

Just want to ask if anyone has encountered the same problem..

I have used the Simple Bar Graph to display the list of employees with the most number of emails/chats and sorted it so that the highest number of emails will appear at the bottom of the graph. The problem is that the graph doesnt display a label on the highest number of email/chats. It displays the number of email/chats that the other employee has and its only the employee with the highest number of email/chats that has no number label. It just wont show and I have no idea now on what to do about it.

Does anybody have a solution for this one?

Try going into the chart properties, selecting the X Axis tab and checking Side Margins, and then do the same for Y Axis.

That worked for me when I had that problem.

BobP

|||

Thanks BobP - BIM...

i'll try that...

|||I have a similar problem : importing a bar chart from Excel 2003, I now don't get the labels any more, but a series of numbers. When I question the data window, this says that the labels are too complex to show up on the graph! It used to work well on the older software.

No Labels Showing in the Graphs

Hi..

Just want to ask if anyone has encountered the same problem..

I have used the Simple Bar Graph to display the list of employees with the most number of emails/chats and sorted it so that the highest number of emails will appear at the bottom of the graph. The problem is that the graph doesnt display a label on the highest number of email/chats. It displays the number of email/chats that the other employee has and its only the employee with the highest number of email/chats that has no number label. It just wont show and I have no idea now on what to do about it.

Does anybody have a solution for this one?

Try going into the chart properties, selecting the X Axis tab and checking Side Margins, and then do the same for Y Axis.

That worked for me when I had that problem.

BobP

|||

Thanks BobP - BIM...

i'll try that...

Wednesday, March 28, 2012

No exception message from CLR stored procedure

Hello everybody,

I've encountered a strange thing using a CLR Stored procedure:

The procedure throws an exception with no message inside... value = {" "}

Basically the procedure has a string as argument which contains a SQL statement that changes according to the users selections...

The results of the query are saved into a dataset for further processing.

Those resultsets can sometimes be very big (ex: 15000 records...). (For the record the procedure works fine for smaller datasets ex 6000 records and running the same query on the application server returns the expected resultset )

By Debugging the procedure I could determine that the failing point was when the dataset is filled...

Anyone having any idea?

The only information I have from this exception is the stacktrace:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.CloseInternal(Boolean closeReader)

at System.Data.SqlClient.SqlDataReader.Close()

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteScalar()

at DataAccess.runScalar(String strSQL, Boolean isStoredProcedure) in c:\Inetpub\wwwroot\Statistix\App_Code\DataAccess.cs:line 114

Thanks in advance

Alaindlk

Hello,

Still nobody having any idea of what is happening here?

Could it be possible that the SQL server is running out of memory or even having a connection timeout?

this is the procedure I've written that causes this behaviour:

[Microsoft.SqlServer.Server.SqlProcedure]

public static int Stats_Print_Data(string SqlQuery)

{

// Put your code here

int jobId = -1;

using (DataSet dsResults = new DataSet())

{

using (SqlConnection mConn = new SqlConnection("context connection = true"))

{

SqlCommand mCmd = new SqlCommand(SqlQuery, mConn);

mCmd.CommandTimeout = 100;

SqlDataAdapter mDa = new SqlDataAdapter(mCmd);

mDa.Fill(dsResults, "Print_Data");

if (dsResults.Tables[0].Rows.Count > 0)

{

ArrayList ColumnNames = new ArrayList();

for (int i = 8; i < dsResults.Tables[0].Columns.Count; i++)

{

DataColumn dc = dsResults.Tables[0].ColumnsIdea;

ColumnNames.Add(dc.ColumnName);

}

StringBuilder InsertCommand = new StringBuilder();

StringBuilder Values = new StringBuilder();

try

{

mConn.Open();

mCmd.CommandText = "Select max(report_id) from Statistix_Report";

jobId = System.Convert.ToInt16(mCmd.ExecuteScalar());

jobId++;

foreach (DataRow dr in dsResults.Tables[0].Rows)

{

if (InsertCommand.Length > 0) InsertCommand.Remove(0, InsertCommand.Length);

if (Values.Length > 0) Values.Remove(0, Values.Length);

mCmd.CommandText = "Select count(*) from print_data where AG_LIB = '" + dr[2].ToString().Replace("'", "''") +

"' and AN_LIB = '" + dr[3].ToString().Replace("'", "''") + "' and PRD_LIB = '" + dr[4].ToString().Replace("'", "''") +

"' and TM_LIB = '" + dr[5].ToString().Replace("'", "''") + "' and RM_LIB = '" + drDevil.ToString().Replace("'", "''") +

"' and SU_LIB = '" + dr[7].ToString().Replace("'", "''") + "'";

if (System.Convert.ToInt16(mCmd.ExecuteScalar()) > 0)

{

InsertCommand.Append("Update PRINT_DATA set ");

for (int i = 0; i < ColumnNames.Count; i++)

{

InsertCommand.Append(ColumnNamesIdea + "_" + int.Parse(dr["Month"].ToString()) + " = ");

if (dr[i + 8] != DBNull.Value)

{

InsertCommand.Append(dr[i + 8].ToString().Replace(",", "."));

}

else

{

InsertCommand.Append("0");

}

if (i != ColumnNames.Count - 1)

{

InsertCommand.Append(",");

}

}

InsertCommand.Append(" where AG_LIB = '" + dr[2].ToString().Replace("'", "''"));

InsertCommand.Append("' and AN_LIB = '" + dr[3].ToString().Replace("'", "''") + "' and PRD_LIB = '" + dr[4].ToString().Replace("'", "''"));

InsertCommand.Append("' and TM_LIB = '" + dr[5].ToString().Replace("'", "''") + "' and RM_LIB = '" + drDevil.ToString().Replace("'", "''"));

InsertCommand.Append("' and SU_LIB = '" + dr[7].ToString().Replace("'", "''") + "' and JOB_ID = " + jobId);

mCmd.CommandText = InsertCommand.ToString();

mCmd.ExecuteNonQuery();

}

else

{

Values = new StringBuilder();

InsertCommand = new StringBuilder();

InsertCommand.Append("insert into print_data(AG_LIB,AN_LIB,PRD_LIB,TM_LIB,RM_LIB,SU_LIB,JOB_ID,");

Values.Append("'" + dr[2].ToString().Replace("'", "''") + "','" + dr[3].ToString().Replace("'", "''") + "','");

Values.Append(dr[4].ToString().Replace("'", "''") + "','" + dr[5].ToString().Replace("'", "''") + "','");

Values.Append(drDevil.ToString().Replace("'", "''") + "','" + dr[7].ToString().Replace("'", "''") + "'," + jobId + ",");

for (int i = 0; i < ColumnNames.Count; i++)

{

InsertCommand.Append(ColumnNamesIdea + "_" + int.Parse(dr["Month"].ToString()));

if (dr[i + 8] != DBNull.Value)

{

Values.Append(dr[i + 8].ToString().Replace(",", "."));

}

else Values.Append("0");

if (i != ColumnNames.Count - 1)

{

InsertCommand.Append(",");

Values.Append(",");

}

}

InsertCommand.Append(") values (");

InsertCommand.Append(Values.ToString() + ")");

mCmd.CommandText = InsertCommand.ToString();

mCmd.ExecuteNonQuery();

}

}

}

catch (Exception ex)

{

throw new Exception(InsertCommand.ToString(), ex);

}

finally

{

mCmd.Dispose();

mConn.Close();

}

}

}

}

return jobId;

}

Any help would be greatly appreciated...

How can i debug something like this?

Thanks in advance

|||

Are you sure that it is the error message thrown by SqlClient is empty and not the exception you throw here:

catch (Exception ex)

{

throw new Exception(InsertCommand.ToString(), ex);

}

? What happens if you remove this catch block and let the unhandled exception get propogated back to sql server - is it still empty?

|||

Hello Steven,

Thanks for your reply...

The exception I've given above is effectively thrown by the stored procedure...

Further investigation learned me that it was indeed a timeout of the stored procedure, so I deed improve the indexing on the tables which now solves partly my problem...

However The problem still remains as the stored procedure, after fetching the required data, saves this data into a table for reporting. The problem is that each 12 rows in the dataset has to be transformed as one record with 12 columns (Table sucks but I have to work with this solution imposed to me ) This means I first need to know if the record already exists, if not then create an insert statement else create an update statement.
Now it seems that executing this causes a timeout for the procedure.
Is there any way to increase this timeout period?

|||

Hello everybody,

I think I've finally found the reason why this timeout occurs: Thats because of the fact that the stored procedure returns a value...
In the worst case, my procedure takes about 10 minutes to complete.... letting the Application server waiting that long is "unacceptable". I think that SQL server automatically throws a timeout exception whenever a procedure/ function takes to long to complete.
I've solved this by changing the procedure signature from Static int SP_Name to static void SP_name(); This way the procedure doesn't have to return a value and isn't any more interrupted by a timeout thrown by SQL server...

Can anyone confirm me this behaviour of SQL server? (By the way this solution works fine for me now...)

Thanks in advance

Monday, March 26, 2012

No distinct in a select into stement ?

Dear MSSQL experts,

I use MSSQL 2000 and encountered a strange problem wqhile I tried to
use a select into statement .

If I perform the command command below I get only one dataset which has
the described properties.
If I use the same statement in a select into statement (see the second
select) I get several datasets with the described properties like I
didn't use distinct
Is there any posiibility to use destinct in a select into statement

select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
Title2, Title3 as Title3,
AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
AggregationTitle2,
AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
AggregationTitle4
from Variables where Title1 is not NULL or Title2 is not NULL or
Title3 is not NULL or
AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
AggregationTitle3 is not NULL or AggregationTitle4 is not NULL;

This is the same with select into :

select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
Title2, Title3 as Title3,
AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
AggregationTitle2,
AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
AggregationTitle4
into VarTitles from Variables where Title1 is not NULL or Title2 is
not NULL or Title3 is not NULL or
AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
AggregationTitle3 is not NULL or
AggregationTitle4 is not NULL;

Hope anyone can help.

Best regards,

Daniel Wetzler

IDaniel Wetzler wrote:
> Dear MSSQL experts,
> I use MSSQL 2000 and encountered a strange problem wqhile I tried to
> use a select into statement .
> If I perform the command command below I get only one dataset which has
> the described properties.
> If I use the same statement in a select into statement (see the second
> select) I get several datasets with the described properties like I
> didn't use distinct
> Is there any posiibility to use destinct in a select into statement
> select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
> Title2, Title3 as Title3,
> AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
> AggregationTitle2,
> AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
> AggregationTitle4
> from Variables where Title1 is not NULL or Title2 is not NULL or
> Title3 is not NULL or
> AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
> AggregationTitle3 is not NULL or AggregationTitle4 is not NULL;
>
> This is the same with select into :
> select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
> Title2, Title3 as Title3,
> AggregationTitle1 as AggregationTitle1, AggregationTitle2 as
> AggregationTitle2,
> AggregationTitle3 as AggregationTitle3, AggregationTitle4 as
> AggregationTitle4
> into VarTitles from Variables where Title1 is not NULL or Title2 is
> not NULL or Title3 is not NULL or
> AggregationTitle1 is not NULL or AggregationTitle2 is not NULL or
> AggregationTitle3 is not NULL or
> AggregationTitle4 is not NULL;
> Hope anyone can help.
> Best regards,
> Daniel Wetzler

The IDENTITY function makes each row unique so DISTINCT doesn't
eliminate the duplicates in this case. Interestingly, this behaviour
seems to have changed in SQL Server 2005. If I run your SELECT INTO on
2005 I get a different execution plan with the IDENTITY value computed
after DISTINCT.

For 2000 the workaround is easy. The following should insert just one
row into vartitles.

CREATE TABLE variables (title1 VARCHAR(10) NULL, title2 VARCHAR(10)
NULL, title3 VARCHAR(10) NULL, aggregationtitle1 VARCHAR(10) NULL,
aggregationtitle2 VARCHAR(10) NULL, aggregationtitle3 VARCHAR(10) NULL,
aggregationtitle4 VARCHAR(10) NULL);

INSERT INTO variables VALUES ('1','1','1','1','1','1','1');
INSERT INTO variables VALUES ('1','1','1','1','1','1','1');

SELECT IDENTITY (INT) AS id,
title1, title2, title3, aggregationtitle1, aggregationtitle2,
aggregationtitle3, aggregationtitle4
INTO VarTitles
FROM (
SELECT DISTINCT
title1, title2, title3, aggregationtitle1, aggregationtitle2,
aggregationtitle3, aggregationtitle4
FROM variables
WHERE title1 IS NOT NULL
OR title2 IS NOT NULL
OR title3 IS NOT NULL
OR aggregationtitle1 IS NOT NULL
OR aggregationtitle2 IS NOT NULL
OR aggregationtitle3 IS NOT NULL
OR aggregationtitle4 IS NOT NULL) AS V ;

SELECT * FROM vartitles ;

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Thank you very much.
This was a very helpful hint.

Best regards,

Daniel|||Minor trick to make the code easier to read and maintain:

WHERE COALESCE (title1, title2, title3) IS NOT NULL
OR COALESCE (ggregation_title1, aggregation_title2,
aggregation_title3, aggregation_title4) IS NOT NULL

Unfortunately these columns look like repeated and a really bad 1NF
problem. I have the feeling that you might have wanted to use
COALESCE() in the SELECT list to get a non-null title and non-null
aggregation_title instead of this convoluted query.|||> Minor trick to make the code easier to read and maintain:
> WHERE COALESCE (title1, title2, title3) IS NOT NULL
> OR COALESCE (ggregation_title1, aggregation_title2,
> aggregation_title3, aggregation_title4) IS NOT NULL

and... bang goes performance too other than a probable clustered index scan
/ table scan.

Consider these two statements on my 800,000 row 834MByte message table for
the nntp forums...

There is a non-clustered index on nntp_author and there is non-clustered
index on author_id

-- Query 1
select count(*)
from mb_message_detail
where nntp_author is not null
or author_id is not null

-- Query 2
select count(*)
from mb_message_detail
where coalesce( nntp_author, author_id ) is not null

Query 1 will use the index author_id and give a half reasonable plan.
Query 2 will do a clustered index scan

Out of 100%, Query 1 is 12% and Query 2 is a whopping 88%

Seriously, go and get a junior job as a programmer and get some very needed
industrial / real world experience instead of bashing people down on here,
as far as 'newbie' goes - you have room to talk.....

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145969845.021110.74070@.t31g2000cwb.googlegro ups.com...
> Minor trick to make the code easier to read and maintain:
> WHERE COALESCE (title1, title2, title3) IS NOT NULL
> OR COALESCE (ggregation_title1, aggregation_title2,
> aggregation_title3, aggregation_title4) IS NOT NULL
> Unfortunately these columns look like repeated and a really bad 1NF
> problem. I have the feeling that you might have wanted to use
> COALESCE() in the SELECT list to get a non-null title and non-null
> aggregation_title instead of this convoluted query.|||-------
Seriously, go and get a junior job as a programmer and get some very
needed
industrial / real world experience instead of bashing people down on
here,
as far as 'newbie' goes - you have room to talk.....
-------

<BIG GRIN> from on who has been the receiving side of one of CELKO's
many put-downs.