Blog Stats
  • Posts - 14
  • Articles - 0
  • Comments - 14
  • Trackbacks - 0

 

Thursday, September 17, 2009

Error:'This field name is not known' in Crystal Report when using a SQL View

I got this error on production for a report which was working fine in Dev and it took me a while to figure out the cause.

Report's Data Source was pointing to a SQL Server View, when I looked at the SQL Query ( Database - > Show SQL Query ), I noticed that Database name was there with view name: "DbName.dbo.ViewName"

I did a lot of googling, trying to figure out a way to change this behaviour and have just "dbo.ViewName" in SQL, but in vain.

For now, I have fixed it by putting the SQL in the view within a Command in Crystal Report and its working.

Its kind of OK for this report as view had a small SQL and report is pretty small.

 

I would love to find REAL solution though, Crystal Reports does not always put the DBName with View.

Monday, September 14, 2009

Line Feed in HTML Mail to avoid Line Too long error

Most of the times 'SMTP proxy service on Firewall ' would not allow a line longer  than '999' characters in HTML email. The solution would be to put a line feed in the HTML string of the mail 900 characters.

If you put this Line feed into the HTML string, it might break the formating.  The right approach will be to put linefeed in the text fields ( e.g. Comments / Description) at appropriate position.

 

I ended up putting linefeed every 200 characters:

 int j = TextColumn.Length/200;
for(int i=1;i<=j;i++)
{
          TextColumn = TextColumn.Insert(i * 200,Environment.NewLine);
}

 

 

 

 

 

Wednesday, August 19, 2009

A couple of Good links for SQL Server Pivots

http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

 

http://www.tsqltutorials.com/pivot.php

 

PS: For more complex  Queries, inner SQL's Group By are important and you might also need to use Sum in inner SQL

Hide table column Dynamically in SQL Server Reporting Services (SSRS)

Quite Straight forward:

  1. Select the Column , Right Click -> Propoerties OR hit F4
  2. Visibility -> Hidden -> Expression i.e. Click on Hidden under Visibility and select Expression from Drop Down
  3. Write the expression e,g.

=IIF(Parameters!MonthlyOrYearly.Value.Equals("Monthly"),false,true)

Format Date in SQL Server Reporting Services

compared to FormatDateTime Good old 'Format' function makes the job a lot easier :

 

  1.  Format(Fields!ResultDate.Value,"M/d/yyyy")
  2. Format(Fields!ResultDate.Value,"yyyy")

=IIF(Parameters!MonthlyOrYearly.Value.Equals(Monthly"),Format(Fields!ResultDate.Value,"M/d/yyyy"),Format(Fields!ResultDate.Value,"yyyy"))

 

 

 

Friday, August 07, 2009

Add new column/field in Oracle DB table - ora - 30649: missing Directory

I was trying to add a new field in an Oracle DB table using this script:

alter table TABLENAME add FIELDNAME char(1) not null default '0'  ;

it was throwing an error:

'ORA-30649:missing directory keyword'

it drove me nutts for a while and after some googling, I was able to figure it out, its the Order: 'not null' should come after default value. right script is:

alter table TABLENAME add FIELDNAME char(1)  default '0'  not null;

 

order does matter :)

Wednesday, March 25, 2009

Pass Multiple values to Modal Window

A JavaScript object can be of great help when you need to pass multiple values to a Modal Window. E.G.

 var objArgs = new Object();
 objArgs.Value1 = "Value 1";
 objArgs.Value2 = 2;
 var modalWindowFeatures = 'dialogHeight:600px;dialogWidth:600px;scroll:no;status:no;resizable:no';
  url = 'relative/path/to/page';
  var returnValue;

  returnValue = window.showModalDialog(url,objArgs,modalWindowFeatures );

Wednesday, February 04, 2009

Subquery returned more than 1 value

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"

As the error message suggests , this normally occurs when you are assigning a T-SQL variable a value within sql query and the query is returning more that one rows e.g.
Declare @var1 nvarchar(50)
' select @var1=name from table1'

Putting 'top 1' can resolve the problem  in many cases ( do check the business logic aspects):

' select top 1 @var1=name from table1'

Monday, January 26, 2009

Install Visual Studio 2003 when Visual 2005 is intalled already

other day I had to install Visual studio.NET 2003 on my system, the problem was I had Visual Studio 2005 and 2008 with framework 2.0 and 3.5 and I did not have .NET 1.1 on it.

There were two suggetions from ppl at work:

  1. Install .NET framework 1.1 and load project in VS 2005
  2. You will need to Un install VS 2005 and 2008 and then install VS.NET 2003

After googling a little first idea did not seem workable and I was not ready to  follow the second option of starting all over. Half hour down I found a solution:

The trick is to install .NET framework 1.1 separately first and then run Visual studio 2003 installer.

-- Follow UP

VS 2003 is working fine, VS 2005 and SQL Server 2005 prompt an error on start   "unable to load file, this error could not be resolved, Please reinstall the application". It seems to work fine though.


 

 

Thursday, January 08, 2009

Scroll Div on page load

If you have a gridview/repeater control or other tabular data within a div, scroll bar appears when height  of data gets bigger than that of DIV.
If you want to scroll down to a certain position, you can use 'Element.scrollTop' property in Javascript:

document.getElementById(divWithScroll').scrollTop = PixelsToScroll;

Calculating the PixelsToScroll may vary depending on scenerio,

In my case, I wanted to scroll down to a selected row within Repeater control. Row was selected using RadioButton and page was reloaded on selection of RadioButton. So, I saved the SelectedItem.ItemIndex  in Repeater_ItemDataBound in a hidden field and using the value in Javascript like following:

PixelsToScroll = SelectedItem * 22;

where 22 is height of table row
 

 

Copyright © faizanahmad