Database

Aadhar Joshi22-Oct-12 3:18

Aadhar Joshi

22-Oct-12 3:18

U please read the question..
he is talking about only 100 clients..

u know in a second how many number of locks sql can handle??

every time i don't belive i am good enough for all condition.. same applies to u also..

Eddy Vluggen22-Oct-12 3:31

Eddy Vluggen

22-Oct-12 3:31

Aadhar Joshi wrote:
he is talking about only 100 clients..

u know in a second how many number of locks sql can handle??

Yes, I do.

Aadhar Joshi wrote:
every time i don't belive i am good enough for all condition.. same applies to u also..

Making mistakes is the only way I learn; trial and error, and the forum is filled with mistakes I made. Drop the subject and the assumptions, it should not have become this personal.

It's just code. Something we do.

Now excuse me, the hedonist in me is calling.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Eddy Vluggen22-Oct-12 5:16

Eddy Vluggen

22-Oct-12 5:16

The reason I dislike the dirty read is because it's not fun if you have to debug a race-condition. Imagine the app being a huge success, and being used in another department with ten times the amount of users. Imagine a simple employee-count being incorrect, because only the employee-record has been inserted, but not yet the contract on which the query joins.

Having a consistent and reliable database is "always" more important than having a speedy one.

Yes, I'm always this rude, just not that touchy. Got some red wine, some red meat, it's time to relax a bit and have a party. It's good to see a dev with passion, and even better to see one defend his opinion. I hope you're gonna stick around, you'll see I'm not an arse all the time. (Sometimes I'm offline and asleep).

FWIW, you'll already have seen this[^] page? That's where you must have found the "cheat" to read before all writes are done. You'll have to assume that most programmers don't use the documentation. If it does not produce any problems with your current architecture, then great - means someone put some extra effort in designing the database. If not, mark it and highlight it and explain what it does with comments in code, as most programmers will not go to MSDN. They'll not even ask here.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Aadhar Joshi22-Oct-12 18:05

Aadhar Joshi

22-Oct-12 18:05

I apologies for what i did..
I know i should listen you first but as u said i am a small kid and i need to go far away..
I can not compete with u, your knowledge, your points but as a senior u should have helping hands..
I can learn today a big lesson and now i can think i was fool for what i did and totally unprofessional..
I have to go far away, I hope i get support from u and u help a kid to grow up..

Sorry,
Aadhar

The Sql Coder22-Oct-12 20:01

The Sql Coder

22-Oct-12 20:01

Good

Eddy Vluggen22-Oct-12 23:33

Eddy Vluggen

22-Oct-12 23:33

Aadhar Joshi wrote:
I apologies for what i did..

So do I, my apologies for the way I acted.

Aadhar Joshi wrote:

I can learn today a big lesson and now i can think i was fool for what i did and totally unprofessional..

We both acted like fools for a few minutes. There's no need to compete; programming is a huge topic, it's impossible to cover all topics.

Aadhar Joshi wrote:
I have to go far away, I hope i get support from u and u help a kid to grow up..

Just stick with your work, it's good to see someone who reads documentation and dares defend his opinion Thumbs Up | :thumbsup:

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Aadhar Joshi22-Oct-12 23:48

Aadhar Joshi

22-Oct-12 23:48

Thanks

Mycroft Holmes23-Oct-12 0:36

Mycroft Holmes

23-Oct-12 0:36

Well good for you! Being able to admit you were wrong is a BIG thing and will make you a better person. Don't let one lousy experience drive you away, stick around and learn we all do that! Oh and we all screw up occasionallyso don't let it stop you from learning.

Never underestimate the power of human stupidity
RAH

kankeyan26-Oct-12 0:58

kankeyan

26-Oct-12 0:58

Locking will not solve your problem. Just optimize your query for better performance. Smile | :)

Wrong retrieved attendance and leave data

Hitesh R5-Nov-12 22:42

Hitesh R

5-Nov-12 22:42

One reason would be occurance of deadlock while executing the update query.

deadlock occurs at that time you are trying to do multiple operation on the same table at the same time.
e.g. select and update both operations are done on same table same time then deadlock will occurs.

To avoid this situation use no lock in your select query and use update lock in your update query.

Amr Muhammed19-Oct-12 16:04

Amr Muhammed

19-Oct-12 16:04

Greetings. I'm having a problem that is we have a fingerprint devices in the factory, we have 2 work shifts OK so the security guys must switch the devices from "in state" to "out state" so that the [in/out mode] in the database being 0 "means attend state" it changes to 1 "means leave state", and vice versa. The problem is that sometimes the security guys forget to change the state of the device to in mode or out mode in the proper time so all the coming records will be wrong for example an employee will have 2 records one at 8:00:00 AM with an in/out mode value 0 and another one at 4:00:00 PM again with in/out mode value 0 instead of 1 so what I want is to adjust such records so that the returned data be correct. Here is the table of the attendance and leave log

We have 2 shifts on starts at 8:00:00 AM and ends at 4:00:00 PM and another starts at 6:30:00 PM and ends on the next day at 8:00:00 AM, that it is, one starts and ends in the same day and another starts on a day and ends on another day. So I want to adjust such invalid records before retrieving the data to based on work shift.

HR_Attend_And_Leave_Of_Staff: [Record No.], [Machine ID], [Employee ID], [Verify Mode] having value 1 if verify mode is a fingerprint or 15 if the mode is face, [In/Out Mode] having 0 for attendance 1 for leave, [Date], [Time]
HR_Employee_Job_Info: [Employee ID], [Shift ID]

Mycroft Holmes20-Oct-12 2:10

Mycroft Holmes

20-Oct-12 2:10

The question arises, do you want to fix the problem after the fact or change the business process before the error happens. Can you raise a dialog from the timekeeping s/w if the mode has not been switched at a specified time.

Otherwise you need to look for paired 0 or 1 valued records in the database and flip the second occurrence.

Never underestimate the power of human stupidity
RAH

Amr Muhammed21-Oct-12 12:20

Amr Muhammed

21-Oct-12 12:20

I want to call a method to correct such data within the database table before calling another one the retrieving the attendance records for employees whereas the person who is responsible for obtaining the staff attendance data. So what I want is a SQL statement(s) that correct such wrong data within the table itself before I retrieve the attendance data. Hope my idea is clear Smile | :)

thanks for support in advance

Mycroft Holmes21-Oct-12 13:01

Mycroft Holmes

21-Oct-12 13:01

So the second suggestion is the way to go. Look for the paired 0 and 1 records in sequence within a period of time and flip sign on the second occurrence.

I would break this down to a number of steps:
Get the last 2 records for each person
Check if the sign is the same
Get the last record based on date/time.
Flip it's sign

1 and 2 may be combined into 1 query

Never underestimate the power of human stupidity
RAH

Assumed off

David Mujica22-Oct-12 5:37

David Mujica

22-Oct-12 5:37

Consider this logic ...

A fingerprint swipe comes from the device

A query is made to the database as to the last time the employee has clocked on

If the employee has not clocked on in 16 hours (some value which represents the longest shift), then the tranasction should be considered an "ON" transaction; otherwise it must be an "OFF" transaction.

When querying your database, if you don't find matching On/Off transactions for a given shift, then you could have an "assumed Off" which is equal to the On time plus the scheduled duration of the shift.

To make this work, your really need to know the shift duration for the employee scheduled on a particular day.

Hope this helps.

Michael Potter22-Oct-12 8:04

Michael Potter

22-Oct-12 8:04

Seems to me that if the security crew has to switch the on/off at the same times each day, you can easily use the time to decide whether it is an in/out punch. In fact, why is there a switch anyway?

We reconcile via a pre-determined schedule for each employee. They should be at the clock at the beginning and end of each shift. If there are missing punches that correspond with these times, a supervisor has to manually reconcile the work day for the erred employee. We gave up on trying to identify a punch as in/out at the time of punch a long time ago.

Report of the SQL Server Agent, job scheduler

Amr Muhammed22-Oct-12 15:19

Amr Muhammed

22-Oct-12 15:19

The fingerprint device is working that way. It is manufactured with that switch button. So if the employee of a given shift was about leaving while the security guys did not change the in/out state of the fingerprint device and that employee put his finger he will have 2 records each with in state and when reading the data this employee considered did not record leaving state. On the other hand, if the employee of a second shift came before his shift start time and the security guys did not switch the device from out state "as they changed the in state to out state at the end of the first shift" and that employee puts his finger so the final result that this employee has 2 records with a leave state. Such matter of wrong records is a case sensitive as the company will depend on the data in calculating the overtime and in salary discount(s). I hope my idea is clear now. The shift 1 and shift 2 begins and ends on 08:00:00 AM - 04:00:00 PM and 06:30:00 PM - 08:00:00 AM on the next day.

vanikanc19-Oct-12 8:05

vanikanc

19-Oct-12 8:05

Hello,

Is it possible to get a list of the jobs running on a sql server, with their names and scheduled timing of run?

Thank you!

Re: Report of the SQL Server Agent, job scheduler

fjdiewornncalwe19-Oct-12 9:07

fjdiewornncalwe

19-Oct-12 9:07

This[^] thread seems to have a pretty in depth discussion on the matter. You should check that out.

I wasn't, now I am, then I won't be anymore.

Copy a tables to another database (Flashdisk)

Richard.Berry10018-Oct-12 11:50

Richard.Berry100

18-Oct-12 11:50

Hi
I'd like to copy a couple of tables with all field information (primary keys, constraints etc) from SQL server 2008 at work to a pc at home running same SQL server.

I dont have any sort of link to the work server from home, so I need to somehow export the tables and save them on a flashdisk, then import them to the other database at home.

How can I do this?

Tks

Richard

R. Giskard Reventlov18-Oct-12 12:22

R. Giskard Reventlov

18-Oct-12 12:22

Easiest way is to right-click the table and then select 'Script Table As', 'Create To...' and then select whichever is the easiest way for you to accomplish your task.

You can then run that script on another database.

You can also use the Database Export Wizard in VS to accomplish a similar task and it will, optionally, get the table data as well. This is found on the Server Explorer tab. Right click on a db connection and select 'Publish to provider...' and follow the prompts.

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Richard.Berry10018-Oct-12 12:40

Richard.Berry100

18-Oct-12 12:40

Thank Marc

Tried the 'Script Table' .. 'Create To' and this looks good..

I would like the data as well though, when you refer to VS do you mean Visual Studio?

I looked at Database Explorer in VS, and tried to add a connection, using SQL Authentication, browsed to (mmmm what I think is the path to the database) and get error You dont have permission...

Am I on the right track here?

How can I tell from SQL management Studio what the path is to the actual database ( I battled with the SQL server installation, and I think I have more than one instance installed)

R. Giskard Reventlov18-Oct-12 13:14

R. Giskard Reventlov

18-Oct-12 13:14

It's 'Mark' spelt the correct way: with a 'K'.

Yes, Visual Studio or you can download a stand-alon version from here[^].

Try using Windows Authentication.

To get the path to a database right-click on the database and select 'Properties', then select 'Files' in the dialog.

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me