Database

13-Sep-11 9:15

Do you have an obvious partitioning column?

Re: Sql Server Table Partitioning

countmein15-Sep-11 7:22

countmein

15-Sep-11 7:22

Thank you for your replies. Yes, I do have a column(primary key) that starts from 1 and automatically generated by adding 1.

Re: Sql Server Table Partitioning

Jörgen Andersson15-Sep-11 8:46

15-Sep-11 8:46

That's not really the best partitioning column.
What you want is to partition the table so that queries is only fetching or inserting data to and from one partition at a time, if possible.

For example, If you want to partition a sales table, the date column would normally be a good choice. Most updates, inserts and queries would be done on the current year partition.

How To Write Database Backup

black0712-Sep-11 20:05

black07

12-Sep-11 20:05

I want to use Backgroundworker, Progressbar and Linq
Please, can i get some reference

I've alread written Database backup using Linq.How can i user backgroundworker?

Re: How To Write Database Backup

Pravin Patil, Mumbai12-Sep-11 21:05

Pravin Patil, Mumbai

12-Sep-11 21:05

Backgroundworker :
1. BackgroundWorker Threads and Supporting Cancel[^]
2. Generic Background Worker[^]
3. Basic Backgroundworker[^]
4. Asynchronous Invocation Using BackgroundWorker[^]
5. BackgroundWorker.ReportProgres...[^]

Progressbar :
1. Progress Bar Best Practices[^]
2. Advanced Progressbar[^]
3. Percent Progress Bar[^]
4. ProgressBar with Percentage[^]
5. Color ProgressBar[^]

Linq :
1. http://msdn.microsoft.com/en-us/netframework/aa904594[^]
2. http://msdn.microsoft.com/en-us/library/bb308959.aspx[^]
3. http://msdn.microsoft.com/en-us/vcsharp/aa336746[^]
4. http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx[^]
5. http://www.hookedonlinq.com/[^]

Hope this helps.
All the best.

I quit being afraid when my first venture failed and the sky didn't fall down.

Re: How To Write Database Backup

Pradeep Shukla17-Sep-11 15:19

Pradeep Shukla

17-Sep-11 15:19

wow..this is a big list..kudos to you Pravin.. Smile | :)

Re: How To Write Database Backup

Pravin Patil, Mumbai23-Sep-11 7:15

Pravin Patil, Mumbai

23-Sep-11 7:15

If OP had used Google, he would have got more than this.....:lol
anyways thanks Prasad.....

I quit being afraid when my first venture failed and the sky didn't fall down.

CDC and views

Gennady Oster12-Sep-11 1:57

Gennady Oster

12-Sep-11 1:57

Hi !

I need to track changes in the MS SQL 2008 DB, where the user works with the most updated data through views. One of my tasks is to show to the user two data chunks (before month and current) with highlighted changes.
CDC evidently works on the table basis. So my question: are there any tools/tutorials/walkthoughs that may help me recreate the view record and syncronize LSNs for different tables. In other words - represent the view record for certain point in time.

Thanks in advance.

Regards,
Gennady

My English is permanently under construction. Be patient !!

This should be simple

Andy_L_J11-Sep-11 23:19

Andy_L_J

11-Sep-11 23:19

I have the following table structure:

Company:
Id (Int),
Name (VarChar)

Group:
Id (Int),
Name (VarChar)

Division:
Id (Int),
GroupId (Int) FK,
Name

Company_Group:
CompanyId (Int) FK,
GroupId (Int) FK

Team:
Id (Int),
DivisionId (Int) FK,
Name (VarChar)

I am trying to retrieve the Team Id and Name according to the CompanyId and DivisionId supplied.
Note that it is entirely possible that a team may belong to a division in multiple Company's.

I have tried various things but seem to get always get unwanted Teams in the results.

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

Blue_Boy11-Sep-11 23:44

Blue_Boy

11-Sep-11 23:44

try this

SQL

select t.id,t.name,d.name,g.name,c.*
 from team t
inner join division d on d.id = t.divisionid 
inner join [group] g on g.id = d.groupid 
inner join Company_Group cg on cg.groupid = g.id
inner join company c on c.id = cg.companyid

Hope it will help you

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

Andy_L_J12-Sep-11 0:37

Andy_L_J

12-Sep-11 0:37

Sorry Blu_Boy, not quite the expected results. I may not have explained correctly,

I want to get only the Teams from the supplied Company (@CompanyId) and where the supplied DivisionId (and thus referenced GroupId) maps to the GroupId in Company_Group table.

This is where I'm stuck. This really should be super easy, but I'm not seeing it tonight.

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

Blue_Boy12-Sep-11 0:48

Blue_Boy

12-Sep-11 0:48

I may be wrong again with query.

SQL

select c.company,g.name,d.name,t.name
from Company_Group cg
inner join company c on c.id=cg.companyid
inner join [group] g on g.id=cg.groupid 
inner join division d on d.id=g.id and d.groupid = cg.groupid 
inner join team t on t.id = d.id

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

Andy_L_J12-Sep-11 1:31

Andy_L_J

12-Sep-11 1:31

Thanks for your time Blue_Boy, I have found that the design is flawed and there will have to be a work-around outside of the database.

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

SQL Server Time data type and negative TimeSpan values?

Blue_Boy12-Sep-11 1:35

Blue_Boy

12-Sep-11 1:35

Andy_L_J wrote:
hanks for your time Blue_Boy,

My pleasure to help others Smile | :)

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

kbalias8-Sep-11 22:16

kbalias

8-Sep-11 22:16

Hi
I am busy working on a windows application to log working times of staff. The application is being developed using C# with SQL Server 2008 as the backend.

I have a [NetTime] column (Time data type) in my table in the database. I get the TimeSpan value by subtracting the official working hours and the actual working hours. This TimeSpan value can be positive or negative. I would like to store this value in the [NetTime] column, but the Time data type does not accept negative values.

What is the usual way of dealing with this problem? I will need to do calculations with the values in this column, for example to calculate the net or total time a staff member worked overtime or not.

Thank you.

Kobus

Re: SQL Server Time data type and negative TimeSpan values?

Wayne Gaylard8-Sep-11 22:27

Wayne Gaylard

8-Sep-11 22:27

My advice would be to use a Long data type to store the Timespan. You can easily store this in the db using TimeSpan.Ticks() and convert back to a TimeSpan in your app using TimeSpan.FromTicks(). That way there is no problem storing positive or negative values.

Hope this helps

When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman

Re: SQL Server Time data type and negative TimeSpan values?

Mycroft Holmes8-Sep-11 23:15

Mycroft Holmes

8-Sep-11 23:15

Most timekeeping systems have a minimum timespan for calculations eg a minute, timespan.tick has no place in a timekeeping system. Therefore I would store the values as integers.

Sometimes you need to look at the real world, not the theoretical or technical capabilities of the system.

Never underestimate the power of human stupidity
RAH

Re: SQL Server Time data type and negative TimeSpan values?

David Skelly9-Sep-11 1:41

David Skelly

9-Sep-11 1:41

A Time represents a point in time. A TimeSpan is a duration of time. They are fundamentally different things. 11 o'clock is a point in time, and so is 12 o'clock. The time span between them is 1 hour.

An analogy: the location of Paris is at 48°48'N 2°20'E. London is at 51°32'N 0°5'W. The distance between Paris and London is 211 miles. Location and distance are fundamentally different things and are not interchangeable.

Similarly, Time and TimeSpan are different things and are not interchangeable.

Re: SQL Server Time data type and negative TimeSpan values?

Jörgen Andersson9-Sep-11 5:09

9-Sep-11 5:09

A (pretty bad) solution is to store the Abs(Timespan) value in one column and Sign(Timespan) in another.
The others have given you better answers .

Re: SQL Server Time data type and negative TimeSpan values?

Corporal Agarn9-Sep-11 6:19

Corporal Agarn

9-Sep-11 6:19

I almost posted a "bad" solution with a disclaimer like yours. (store it as a character).

Re: SQL Server Time data type and negative TimeSpan values?

Chris Meech9-Sep-11 7:40

Chris Meech

9-Sep-11 7:40

You could even use the '+' character for positive values and the '-' character for negative ones. I'm not sure what a NULL value would mean though. Might have to make it a non-nullable column. D'Oh! | :doh:

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Re: SQL Server Time data type and negative TimeSpan values?

Jörgen Andersson9-Sep-11 8:29

9-Sep-11 8:29

The problem for me is that I'm often enough set on the wrong track by how the question is formulated.

Re: SQL Server Time data type and negative TimeSpan values?

jschell9-Sep-11 7:16

jschell

9-Sep-11 7:16

kbalias wrote:
I get the TimeSpan value by subtracting the official working hours and the actual working hours.

Which would seem to me that you would need to store the "official working hours" as well. Given that then there is no point in storing the difference for the second value. Instead you just store the "actual working hours". If you need the difference then the caller subtracts the two.

If you want the difference anyways then store it as seconds. That insures enough precision and is probably a more business friendly value than Ticks.

Re: SQL Server Time data type and negative TimeSpan values?

Jörgen Andersson9-Sep-11 8:18

9-Sep-11 8:18

<Getting my brain back into gear>
The standard way is to store the login and logout times in a table. The nettime is easily enough calculated from those two values. It's a part of normalization to not store values you can calculate from other columns in the database.