Database Discussion Boards

Re: how to get latest usage time per pc

kageorge2-Mar-06 23:38

kageorge

2-Mar-06 23:38

Hi
Just have a try with the below code

<br />
Create Table #softwareMeter(id Int primary key, pcID Int, softID Int, myDateTime DateTime)<br />
<br />
Insert Into #softwareMeter Values (1,100,501,'20060301 10:12')<br />
Insert Into #softwareMeter Values (2,102,509,'20060301 12:40')<br />
Insert Into #softwareMeter Values (3,100,503,'20060302 08:12')<br />
Insert Into #softwareMeter Values (4,102,508,'20060302 13:50')<br />
Insert Into #softwareMeter Values (5,103,508,'20060303 13:50')<br />
Insert Into #softwareMeter Values (6,104,509,'20060303 15:20')<br />
Insert Into #softwareMeter Values (7,105,503,'20060303 18:13')<br />
<br />
select top 10 with ties PCID,max(MyDateTime)  from #softwareMeter <br />
Group by PCID<br />
Order By PCID

The top close is a must and the number after top should be set wisely.

Re: how to get latest usage time per pc

batmanAgen3-Mar-06 0:00

batmanAgen

3-Mar-06 0:00

hey thanks for the reply... and for giving me the sql command.. thanks alot.. Big Grin | :-D

i appreciate it! Smile | :)

Query About NULL

Subramaniam s.V.2-Mar-06 1:06

Subramaniam s.V.

2-Mar-06 1:06

Hi All,

Is the value NULL different from SQL NULL??If yes, in what way??

create proc test
(
@dtm datetime
) As
begin
if(@dtm=NULL)
print 'It is NULL'
else
print 'It is not NULL'

when I execute this SP with like the one shown below

test NULL

it displays me the output as 'It is not NULL'. y is it so?? what is actually happening?

Thanks

Re: Query About NULL

Dave Mavin2-Mar-06 1:33

Dave Mavin

2-Mar-06 1:33

Not even NULL is NULL. WTF | :WTF:

In SQL a value of NULL indicates the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal.

Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.

Null values usually indicate data that is unknown, not applicable, or to be added at a later time.

I think the comparison you are trying to make is testing a NULL value against another NULL which is like saying is Unknown = Unknown which cannot be compared, its Unknown OMG | :OMG:

When doing comparisons you have to state :-

IS NULL or IS NOT NULL in a WHERE clause. So I am not too sure if you could say if(@dtm IS NULL)
....

Don't know if this helps any. Confused | :confused:

Re: Query About NULL

Dave Mavin2-Mar-06 1:52

Dave Mavin

2-Mar-06 1:52

Hi,

I think what you are doing is something like this. Confused | :confused:

CREATE PROCEDURE dbo.Test
(@dtm datetime)
As
if(@dtm IS NULL) <--- Compare NULL not =
print 'It is NULL'
else
print 'It is not NULL'
GO

To check against NULL if thats what you really wish to do try this.

EXECUTE test Null;

Which produces this.

It is NULL

If you are trying to stop invalid dates you will need to look at other options as doing something like this, will not detect NULL and not passing a value would simply generate an error...

EXECUTE test ''; <-- This is not NULL its a value.

Hope this rambling helps? Big Grin | :-D

Cheers
Dave

To pass parameters in Data Access Application Block..

MudkiSekhon2-Mar-06 1:02

MudkiSekhon

2-Mar-06 1:02

I am trying to use Data Access block V2. I am trying to use stored procedures to manipulate with data. Please tell me the way to pass Input and Output parameters with Data Access Application block.

Thanks,
Sandy Sekhon

Tier Development : DataSets

Dave Mavin1-Mar-06 23:52

Dave Mavin

1-Mar-06 23:52

Hi,

I am new to .NET and trying to understand the concept of the DataSet, so if I am talking rubbish please excuse my ignorance. Sigh | :sigh:

I believe one usage for the DataSet cab be to transport changes using the GetChanges() method to data services, hence limiting the amount of data in this transfer via a new DataSet.

In my simple example I use the Northwind database from SQL server to populate a DataSet and stored procedures to perform the required update commands. If I use these they work fine.

DataSet changedDataSet = new DataSet(); // Transport DataSet
DataSet commonDataSet = new DataSet(); // Working DataSet
SqlDataAdapter commonDataAdapter = new SqlDataAdapter ( ) ;
commonDataAdapter.SelectCommand = GenerateSelectCommand ( Sqlconn ) ;
commonDataAdapter.InsertCommand = GenerateInsertCommand ( Sqlconn ) ;
commonDataAdapter.UpdateCommand = GenerateUpdateCommand ( Sqlconn ) ;
commonDataAdapter.DeleteCommand = GenerateDeleteCommand ( Sqlconn ) ;
commonDataAdapter.Fill(commonDataSet,"Region");

At this point the commonDataSet is filled will all the records I require from the Region database table using a stored procedure for the select. When reviewing the data I get the following, this also indicates the RowState. I realise that the DataSet is a cached data/schema and AcceptChanges would commit and the Data Adapter Update() would fire the stored procedure updates to the DB based on RowState.

1 My Record To Edit. Unchanged
2 Western Unchanged
3 Northern Unchanged
4 Southern Unchanged
5 NorthWest Unchanged
6 NorthWest Unchanged

Now I perform some simple operations on the records as follows.

// Add new record
DataRow aRecord = commonDataSet.Tables["Region"].Rows.Add(new object [] {999, "NorthWest"});

// Edit record 1
aRecord = commonDataSet.Tables["Region"].Rows[0];
aRecord.BeginEdit();
aRecord["RegionDescription"] = "Edit this.";
aRecord.EndEdit();

// Delete record 4
aRecord = commonDataSet.Tables["Region"].Rows[3];
aRecord.Delete();

This results in the following. I have simply dumped the text out if the record is deleted, as I cannot access fields.

1 Edit this. Modified
2 Western Unchanged
3 Northern Unchanged
Row Deleted
5 NorthWest Unchanged
6 NorthWest Unchanged
999 NorthWest Added

I now trap the changes into a new DataSet as follows. I could use the override methods to select what I want but here I take all.

changedDataSet = commonDataSet.GetChanges();

//changedDataSet = commonDataSet.GetChanges(DataRowState.Modified | //DataRowState.Added); - Could do this to overload.

Now I look at the changes (changedDataSet) as follows.

1 Edit this. Modified
999 NorthWest Added
Row Deleted

Now my question is if I transport this DataSet to a data service component, which is responsible for updating the records on the central DB, how do I handle the deleted record as I have no field information such as Primary key ID etc? WTF | :WTF:

Any help would be appreciated. WTF | :WTF:

Re: Tier Development : DataSets

cbhkenshin_home2-Mar-06 8:06

cbhkenshin_home

2-Mar-06 8:06

Hi Dave,

Cant you retrieve all the Required information for the deleted row before deleting it buy adding a new command before deleting.

For updating the DB you can use the commonDataAdapter Update Command
//Like this
commonDataAdapter.Update(commonDataSet);

and the adapter will update the DB and apply the changes to the DataSet.

That can be of help for you.

Best Regards

Live forever or just live a happy life (You Decide !)

Re: Tier Development : DataSets

Dave Mavin3-Mar-06 0:21

Dave Mavin

3-Mar-06 0:21

Hi,

I was thinking that also but what I am confused about is that a DataSet is detached and designed to do this, unlike the Data Reader. Dead | X|

User UI (DataSet)<----> Middle Service Component (DataSet) <----> DataBase

If Microsoft is providing the ability to capture updates to pass to other objects, specifically via a DataSet, it does not make any sense to me that we have to deal with deleted records using a different rule? I understand a DataSet could hold other stuff apart from SQL like XML, Array, and Direct Table etc.

So what we are suggesting here is that the updated DataSet captures all the edits and updates, which can be passed to another component, but we have to deal with deletions manually? Obviously that’s possible but considering
a DataSet can be a complete set of relational tables, e.g. Customer ->> Orders -->Order Lines etc. D'Oh! | :doh:

does this not seem a bit of a task?

I am sure its me being stupid and someone will says its standard functionality but I can't find it? Blush | :O

I will dig about for some articles on this me thinks....
Cheers
Dave

Re: Tier Development : DataSets

Dave Mavin3-Mar-06 3:51

Dave Mavin

3-Mar-06 3:51

Hi,

I think I have worked this out now by using a DataView in cobination with a DataSet! Roll eyes | :rolleyes:

DataSet does indeed operate as expected but we can sync the DataSet up with a DataView and this details the information I wanted regarding ViewRowState, e.g capturing changes that have been made to the DataSet in terms of Deletes, Updates and Additions.

So the DataView provides the sync record information in terms of rowstate between the DataSet such as :-

Added,
CurrentRows,
Deleted,
ModifiedCurrent,
ModifiedOrigional,
OrigionalRows,
Unchanged.

We can also combine these flags so I can get Added, Deleted and ModifiedCurrent.

I have created a sample application that demonstrates and explains if you are interested. Sniff | :^)

Cheers
Dave

HAL 9000: I'm sorry Dave, I'm afraid I can't do that.
DAVE: What's the problem?
HAL 9000: I think you know what the problem is just as well as I do.
DAVE: What are you talking about, HAL?
HAL 9000: Dave, this conversation can serve no purpose anymore. Goodbye.

Join SELECT statements?

myNameIsRon1-Mar-06 20:41

myNameIsRon

1-Mar-06 20:41

Hi,
I'm not sure how to go about this...
I need to compare ID numbers to achieve two different columns (Size and Category from the same field). If I use UNION I'll get a new row, I just want another column added to the existing columns. Can you have more than one SELECT statement to add more columns to your output? Or am I going about it wrong?

Thanks,
Ron

SELECT Products.ItemNumber, SizesAndCategories.SizeCatName AS Size FROM Products, SizesAndCategories WHERE Products.SizeID=SizesAndCategories.SizeCatID AND Products.ItemID=?
//Join Somehow?
SELECT SizesAndCategories.SizeCatName AS Category FROM Products, SizesAndCategories WHERE Products.CategoryID=SizesAndCategories.SizeCatID AND Products.ItemID=?

Re: Join SELECT statements?

Colin Angus Mackay1-Mar-06 21:04

Colin Angus Mackay

1-Mar-06 21:04

myNameIsRon wrote:
Can you have more than one SELECT statement to add more columns to your output? Or am I going about it wrong?

Yes, but from your example I cannot see what you are trying to do.

If this does not work, could you provide some sample data and show how you'd like it all joined up.

SELECT Products.ItemNumber, SizesAndCategories.SizeCatName AS Size, Categories.Category
FROM Products
INNER JOIN SizeAndCategories ON Products.SizeID=SizesAndCategories.SizeCatID
INNER JOIN (SELECT SizesAndCategories.SizeCatName AS Category
            FROM Products
            INNER JOIN SizeAndCategories ON Products.CategoryID=SizesAndCategories.SizeCatID 
            WHERE Products.ItemId = @ItemId) AS Categories
WHERE Products.ItemId = @ItemId

By the way, you can probably tell I prefer the explicit use of the JOIN type (e.g. INNER JOIN) to make it very obvious on what conditions two tables are joined together. I happen to think it makes the code easier to understand.

ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?

-- modified at 3:04 Thursday 2nd March, 2006

Re: Join SELECT statements?

myNameIsRon3-Mar-06 5:33

myNameIsRon

3-Mar-06 5:33

Hi Colin,
Thanks for your help. Sorry about not been better at explaining myself (new to sql), also sorry for not using the right sql terms...
Note: I'm using MS Access DB, so I think I only have LEFT and RIGHT JOIN?

Here is what I want to do, maybe JOIN is not the way to go?

In my Table "Products" I have two fields "SizeID" and "CategoryID" that references the SizeCatID in my Table "SizesAndCategories". The SizeCatName could be either a Size Or Category name, so I need a condition each time I pull it.

So basically (in none sql terms):
SizesAndCategories.SizeCatName AS Size (if SizeID = SizeCatID)
SizesAndCategories.SizeCatName AS Category (if CategoryID = SizeCatID)

Maybe renaming the Tables would work (I've seen this a,b used before)?

SELECT a.SizeCatName AS Size, b.SizeCatName AS Category FROM Products, SizesAndCategories a, SizesAndCategories b WHERE Products.SizeID = a.SizeCatID, Products.CategoryID = b.SizeCatID

Thanks for your help!!

Ron

-- modified at 11:34 Friday 3rd March, 2006

Create index using ADO.NET???

wasife1-Mar-06 17:16

wasife

1-Mar-06 17:16

Hi,
I want to create index in my SQL Server database using
ADO.NET. I have a query something like this:

CREATE INDEX <index name="">
ON ( <column name=""> )

The query is correct( i have checked it in SQL Query Analyzer). But i dont know how to execute it using ADO.Net. I have used ExecuteNonQuery of SqlCommand class. But that does not work.

Any suggestion/solution please...

Re: Create index using ADO.NET???

Colin Angus Mackay1-Mar-06 20:43

Colin Angus Mackay

1-Mar-06 20:43

wasife wrote:
I have used ExecuteNonQuery of SqlCommand class. But that does not work.

Too vague. How does it "not work"?

Since you can run just about any SQL through a SqlCommand (including CREATE INDEX) there must be some explanation and saying "that does not work" gives us no idea of where to start helping you.

ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?

Re: Create index using ADO.NET???

Paul Conrad2-Mar-06 5:35

Paul Conrad

2-Mar-06 5:35

Colin Angus Mackay wrote:
saying "that does not work" gives us no idea of where to start helping you

Please give more detail as to what is not working Smile | :)

Paul

SQL Profiler results question

theJazzyBrain28-Feb-06 23:19

theJazzyBrain

28-Feb-06 23:19

Deat CPians,

I have a heavy duty database on SQL Server 2000.
Every now and then I monitor the transactions applied against the database using SQL Profiler in order to spot the queries and SPs that use alot of CPU resources.

Here are a few of many transactions that my question will be based on:

APPLICATION			TEXTDATA					DURATION STARTTIME			READS	WRITES	CPU
---------------------------------------------------------------------------------------------------------------------------------------------
.Net SqlClient Data Provider						1229250	1/3/2006 10:53:53 am	2080346	366	60811
Internet Information Services						618203	1/3/2006 10:54:38 am	61550	1	4016
.Net SqlClient Data Provider	exec lc_getLatestIpById @mem_id = 267980	483	1/3/2006 11:26:44 am	4594	0	484

As you can see the second column displays the actual querry.
I noticed the there are many requests that have no TEXTDATA at all like the two firsts ones. Not only I do not know what these transactiosn are, but they are very heavy, they cost alot!
What are these transactions? What triggers them?

Thanking you in advance.

theJazzyBrain

Excellence is not an act, but a habit!

Aristotle

Arithmetic overflow error converting expression to data type int.

japel28-Feb-06 13:39

japel

28-Feb-06 13:39

Hello All

I have looked into this on the web and found many articals explaining what causes the problem. Some I understand and some I don't but I am no closer to fixing it so some help would be great.

Problem:

I have a table I AM NOT allowed to alter, I can only run select queries against it. The table has a column called docsize (Document Size) in btyes, I run the following query on it.

Select Sum (DocSize) AS myDocSize From Document

Being bytes these are generally "1001374" bytes in size with around 100 000 rows on the table. The resulting number of the SUM is to big I guess so how to I work around this.

P.S. When I get this final figure I bring it in with this Sub

<br />
    Private Sub TotalDocumentSize()<br />
        Dim Command1 As SqlCommand = New SqlCommand("Select Sum (DocSize) AS myDocSize From Document")<br />
<br />
        Command1.CommandType = CommandType.Text<br />
        Command1.Connection = SQLConnection1<br />
<br />
        Dim myDocSizeVarTmp As Integer = CType(Command1.ExecuteScalar(), Integer)<br />
        BytesToMegabytes(myDocSizeVarTmp)<br />
<br />
    End Sub<br />

And then run this Function

<br />
    Public Function BytesToMegabytes(ByVal Bytes As Double) As Double<br />
        'This function gives an estimate to two decimal<br />
        'places.  For a more precise answer, format to<br />
        'more decimal places or just return dblAns<br />
<br />
        Dim dblAns As Double<br />
        dblAns = (Bytes / 1024) / 1024<br />
        BytesToMegabytes = FormatNumber(dblAns, 2, , , TriState.False)<br />
<br />
        Dim MyItem As ListViewItem<br />
        MyItem = New ListViewItem("Total Document Size (MB)")<br />
        MyItem.SubItems.Add(BytesToMegabytes)<br />
        listStat.Items.Add(MyItem)<br />
    End Function<br />

Any help would be great... Smile | :)

When people make you see red, be thankful your not colour blind.

Re: Arithmetic overflow error converting expression to data type int.

WoutL1-Mar-06 0:37

WoutL

1-Mar-06 0:37

You can try:
Select Sum (cast(DocSize as bigint)) AS myDocSize From Document

Wout Louwers

Re: Arithmetic overflow error converting expression to data type int.

Le centriste1-Mar-06 1:53

Le centriste

1-Mar-06 1:53

Also, in your code, try to store it in a UInt64, much bigger.

--------

"I say no to drugs, but they don't listen."
- Marilyn Manson

Re: Arithmetic overflow error converting expression to data type int.

japel1-Mar-06 12:39

japel

1-Mar-06 12:39

Thanks both your ideas worked the UInt64 gave an error but prompted me to use long for now.

Thanks you Both

"Hummmm what should we call an something bigger than an int 'BIGINT'"
Two SQL developers when home very happy with their decision. Big Grin | :-D

When people make you see red, be thankful your not colour blind.

newbie SQL question

Mundo Cani28-Feb-06 10:57

Mundo Cani

28-Feb-06 10:57

Hello,

I'm developing a simple website with a SQL server 2005 back-end. I've designed my database schema and I'm ready to create the database and add tables. So, here's my question:

When building a database backend for a web application, do you generally build the database on a local development machine and then somehow move it to the server? Or do you build it in place on the server somehow (by running server-side sql queries or something)?

I just need a little high-level help here.

Thanks!

-Ian

Ian

Re: newbie SQL question

Al Ortega28-Feb-06 11:14

Al Ortega

28-Feb-06 11:14

You can generate scripts while creating your schema objects on your local dev machine, modify as needed and then run the scripts against your production machine later. As you make changes you keep this same practice and then you can rebuild the schema whenever you want when you get new development or testing boxes...

hth
Al

Re: newbie SQL question

Colin Angus Mackay1-Mar-06 1:25

Colin Angus Mackay

1-Mar-06 1:25

I create scripts. When I'm ready to deploy I run the scripts on the live server.

Once the system is in production and there is an upgrade to do I take a backup of the live (production) system and restore it on the development platform and test the migration scripts. Once they work, I run them on the live server when deploying the changes.

ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?

Re: newbie SQL question

Paul Conrad1-Mar-06 16:44

Paul Conrad

1-Mar-06 16:44

Colin Angus Mackay wrote:
I create scripts. When I'm ready to deploy I run the scripts on the live server.

Yeah, no sense in risking live production data if something isn't right in the script.

Colin Angus Mackay wrote:
Once the system is in production and there is an upgrade to do I take a backup of the live (production) system and restore it on the development platform and test the migration scripts. Once they work, I run them on the live server when deploying the changes.

Good to have your bases covered Smile | :)

PJC

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.