Database

convert EMPTY fields to NULL?

18-Mar-08 11:03

The report path should be the full path to the report relative to the root folder, including the name of the report itself. You appear to have just specified the name of the folder the report resides in and not the name of the report itself. If you don't specify the name of the report, how can the report server know what report you want to load?

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Reporting Services 2000

imnotso#18-Mar-08 4:13

imnotso#

18-Mar-08 4:13

Hi,

I'm exporting my report to Excel and there are colums missing ! Specifically columns where visibility is conditional. I can see them on the server, where they appear and disappear corrdctly. Is excel just copying each page or is it trying to be cleverer than that?

I just want what I can see exporting to Excel, that's not what is happening.

Thanks in Advance.

Harvey

John Sundar18-Mar-08 1:35

John Sundar

18-Mar-08 1:35

how to insert null to an EMPTY cell in SQL SERVER...

i.e, in a field, i enter some values and deleted it. so, it is considered as EMPTY field and not null. i need a query to make that field <NULL>

can anyone help me? - KARAN

Krish - KP18-Mar-08 1:43

Krish - KP

18-Mar-08 1:43

UPDATE <tablename> <br />
     SET <col> = NULL <br />
WHERE LEN(<col>) = 0 </col></col></tablename>

this works for string data

even recollect reading IsEmpty() function in SQL Server
you may even try with this. I've never used this function.

Regards
KP

Krish - KP18-Mar-08 2:01

Krish - KP

18-Mar-08 2:01

UPDATE TableA SET ColA =  NULL <br />
WHERE LEN(ColA) = 0 <br />
this works for string data

even recollect reading IsEmpty() function in SQL Server
you may even try with this. I've never used this function.

Regards
KP

John Sundar23-Mar-08 19:51

John Sundar

23-Mar-08 19:51

thanks a lot krish-KP... can i have it for DATETIME field also? i.e.. when the DATETIME field is EMPTY or 1/1/1900, it should be of <null>. how to achieve it? - KARAN

SeeSharp18-Mar-08 21:10

SeeSharp

18-Mar-08 21:10

This solution is when you are manually putting data in the fields rom enterprise manager.

Just click on the cell and do " control 0 ".

That is press control key and zero.
this will get NULL value in the empty field.

-Naina

--Here 2 See Sharp--
I have not failed. I've just found 10,000 ways that won't work. Smile | :)

Getting StartDate and EndDate using SQL query

John Sundar23-Mar-08 19:49

John Sundar

23-Mar-08 19:49

thanks seesharp...but i need it programmatically

meeram39518-Mar-08 0:08

meeram395

18-Mar-08 0:08

Hi,

I have a requirement to select the starting date and ending date of a month if I give a particular date as a parameter. HOw to do that? For eg>
If I give the select statement as below:
select startdate, enddate from <tablename> where particulardate = '17/03/2008'. If I execute this query, I should get the start date as '01/03/2008' and end date as '31/03/2008'

HOw to achieve this in SQL Server? Pls help

Regards
meeram.

Meeram395

Re: Getting StartDate and EndDate using SQL query

Krish - KP18-Mar-08 1:29

Krish - KP

18-Mar-08 1:29

Getting End Date
1. Add 1month to the given date taking 1st day and year of given date
2. which returns 1st of next month from given date
3. subtract 1day which returns last day of given month

DECLARE @GivenDate AS DateTime<br />
SET @GivenDate = GetDate()<br />
SELECT DateAdd(DD, -1, DateAdd(MM, 1, Cast(Month(@GivenDate) AS VarChar(2)) + '/01/' + Cast(Year(@GivenDate) AS VarChar(4))))

Getting Start date
similar to the end date take day as 1 with month & year from given date

Regards
KP

Re: Getting StartDate and EndDate using SQL query

meeram39518-Mar-08 3:55

meeram395

18-Mar-08 3:55

Thank you very much

Meeram395

sql query

rameshdontagani17-Mar-08 18:30

rameshdontagani

17-Mar-08 18:30

hi,

i have three fields in the database table

p1 p2 rank

10% 20% 3

21% 40% 2

now i am passing the input as 15%...this 15% is related to 3rd rank.. so i need the o/p rank as 3..

if the p1 and p2 datatypes are varchar..then how we can we write the query for that one tgo get the rank as 3.

Ramesh

Re: sql query

pmarfleet17-Mar-08 20:48

Reporting Services: Draw rectangle on chart's plot area

17-Mar-08 20:48

You haven't explained why a rank of 3 should be returned for 15% However I assume this is because 15% is between 10 and 20%. In this case, you shoul use the BETWEEN clause in your query.

Why are you storing percentages in varchar fields? They are numbers, so they should be stored in numeric fields. You will run into problems every time you want to do numeric comparisons because you will need to case your varchar values to numbers.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: sql query

SeeSharp18-Mar-08 23:54

SeeSharp

18-Mar-08 23:54

select rank from table where input between P1 and P2

--Here 2 See Sharp--
I have not failed. I've just found 10,000 ways that won't work. Smile | :)

Error on Mapping an UDT

faehne17-Mar-08 11:13

faehne

17-Mar-08 11:13

Hello..

I've got an InvalidOperationException: invalid Value vor the OracleObjectMappingAttribute nNR

Could NET have a problem with casting NUMBER to int? Or have i to do something
special, because I'm using a OUT-only Parameter?

Here are my code:

PLSQL:
-------------
create type TESTOBJ as OBJECT (
nNR NUMBER;
sTEXT VARCHAR2(20);
)

create testproc(p_testobj OUT TESTOBJ)
as
v_testobj TESTOBJ := TESTOBJ(1,'Test');
begin
p_testobj := v_testobj;
end;

C# the Mapped OBj:
-------------------------------
public class OracleXTBQueueObj : IOracleCustomType
{

private int nr;

[OracleObjectMappingAttribute("nNR")]
public int NR {
get { return nr; }
set { nr = value; }
}

private string tEXT;

[OracleObjectMappingAttribute("sTEXT")]
public string TEXT
{
get { return tEXT; }
set { tEXT = value; }
}

public virtual void FromCustomObject(OracleConnection con, IntPtr pUdt)
{
OracleUdt.SetValue(con, pUdt, "nNR", this.NR);
OracleUdt.SetValue(con, pUdt, "sTEXT", this.TEXT);
}

public virtual void ToCustomObject(OracleConnection con, IntPtr pUdt)
{
this.NR = ((int)(OracleUdt.GetValue(con, pUdt, "nNR")));
this.TEXT = ((string)(OracleUdt.GetValue(con, pUdt, "sTEXT")));
}
}

C# the Factory:
-------------------------------
[OracleCustomTypeMappingAttribute("USR.TESTOBJ")]
public class OracleXTBQueueObjFactory : IOracleCustomTypeFactory
{
public IOracleCustomType CreateObject()
{
OracleXTBQueueObj obj = new OracleXTBQueueObj();
return obj;
}
}

C# the function, callin the PROCEDURE:
------------------------------------------------------------
public static void GetOO(OracleConnection conn)
{
OracleCommand cmd = new OracleCommand("UDT_TESTPROC", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;

OracleParameter paramDaten = new OracleParameter("p_testobj", OracleDbType.Object, ParameterDirection.Output);
paramDaten.UdtTypeName = "USR.TESTOBJ";
cmd.Parameters.Add(paramDaten);

try { cmd.ExecuteNonQuery(); }
catch (Exception ex) //InvalidOperationException
{
Console.Out.WriteLine(ex.Message)
}
Console.In.ReadLine();
paramDaten.Value.ToString();

}

-----
Hope somebody can help me

thx in advance
faehne

Mark J. Miller17-Mar-08 9:47

17-Mar-08 9:47

I am working on a report which requires me to draw two polygons (rectangles) on a scatter chart to visually indicate "good" and "bad" ranges of plotted data. Is there a way to do this out-of-the-box?

Otherwise, if I must create a custom report item, how do I draw on the chart surface? I have looked at the following articles on Custom Report Items, but when I open the sample solutions I can't put the custom report items on the chart surface.

http://blogs.msdn.com/chrishays/default.aspx
[^]
http://msdn2.microsoft.com/en-us/magazine/cc188686.aspx[^]

Do I have to create a custom report item which inherits from a chart control? If so, does anyone have any reference samples?

Re: Reporting Services: Draw rectangle on chart's plot area

pmarfleet17-Mar-08 10:23

Re: Reporting Services: Draw rectangle on chart's plot area

17-Mar-08 10:23

I have used the Dundas Chart for Reporting Services component to solve a similar problem, i.e. applying custom rendering to a Reporting Services chart. The Dundas Chart component exposes a PostPaint event which allows a developer to draw onto the chart surface area. Code is written against the chart control in C#.

The component isn't cheap, but it does open up new possibilities for charting with Reporting Services. It is especially useful when you need to apply your own customizations to a chart that don't come out of the box.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Mark J. Miller17-Mar-08 10:54

17-Mar-08 10:54

Thanks, that's one of the solutions I am looking at. However, this is the only requirement I have (so far) which doesn't come out of the box. So I'm not sure if I can get approval to purchase a license just so I can draw a couple rectangles. Of course it's beginning to look like my time to develop it myself will cost a lot more than just to license a 3rd party charting solution.

Re: Reporting Services: Draw rectangle on chart's plot area

pmarfleet17-Mar-08 11:25

Re: Reporting Services: Draw rectangle on chart's plot area

17-Mar-08 11:25

Something else I have tried in the past is writing a custom assembly in .NET that internally renders a Reporting Services chart. This can be achieved by using the web services that Reporting Services exposes to programmicically render a report. You could develop a report that simply consists of the chart you want to display. If you request this report through the web service, the data for your report will be returned as a byte array. You can create an image from this data and then use the graphics capabilities in the .NET Framework to alter the image to your requirements. You could create a custom component to do all this by following the Microsoft article.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Mark J. Miller17-Mar-08 11:32

17-Mar-08 11:32

thanks, that's a good idea. I'll give it a shot.

Saving Bitmap File To MS Access 2003 Problem

cyberE-gor17-Mar-08 8:32

cyberE-gor

17-Mar-08 8:32

Hi,

Problem:
I'm using C# 2005. The data type of the bitmap file saved to database is "long binary data". It should be "OLE Object".

Source Code:

Bitmap myBitmap = new Bitmap(@"c:\Picture1.bmp");<br />
OleDbConnection conn = new OleDbConnection("<<My Connection String>>");<br />
conn.Open();<br />
string sql = "UPDATE Table1 SET fldOLE = @fldOLE WHERE fldID = 5";<br />
OleDbCommand command = new OleDbCommand(sql, conn);<br />
command.Parameters.Add(new OleDbParameter("@fldOLE", myBitmap));<br />
int rowsAffected = command.ExecuteNonQuery();

Question:
How can I save Bitmap to database with data type "OLE Object"?

Laugh | :laugh:

Thanks in advance

Re: Saving Bitmap File To MS Access 2003 Problem

Christian Graus17-Mar-08 10:09

Christian Graus

17-Mar-08 10:09

Please don't cross post.

Christian Graus - Microsoft MVP - C++

"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )

SQL DTS

kibromg17-Mar-08 7:08

kibromg

17-Mar-08 7:08

Hi All,
I am trying to get a file from an Ftp and Copy data (*csv file) on to one of my tables in SQL 2005 database.
I am not sure but SQL server 2005 integration service has a way to create a package that allows me to do this task.

Can any one tell me how to go about it?

your ideas are much appreciated.

Thank you

Re: SQL DTS

Mark J. Miller17-Mar-08 9:58

17-Mar-08 9:58

Use the FTP task to copy the file to an accessable directory, then you can create a data flow task which uses a flat file source, processes the file and then puts the data in the appropriate data flow destination.

Your package designer should resemble this:

<br />
FTP Task --> Data Flow Task<br />

Open the Data flow task (double-click) and it should resemble this:

<br />
Flat File Source --> [intermediate transformations you may need (if any)] --> Sql Server Destination<br />

Just drag the items mentioned to the designer surface, open each item and set the properties needed.