Re: Unable to Start MSSQL SERVER - Database Discussion Boards

For many years I have been using vb6 with Access Database as back end. In many cases while returning record sets Access is omitting the very first record and it returns the rest of the records. I am really baffled. Please help.

Hi all of you, I have corrected my mistake and got the answer for this question.

Here is how I got it solved. The records were stored with time field. When querying up I did not mention the begining of the starting day (like 00:01 - which implies the start time of the day) and the end of the day in the finishing date. (Thought up this solution very late.) And so I was getting a few records eliminated from the query result set.

Thanks for all of you who tried to help and you can consider my question closed.

Hope this helps somebody.

-- modified 1-Apr-16 12:47pm.

Are you confusing zero-based and one-based indexing for arrays?
Otherwise, showing some code might help us help you.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

Getting an article published on CodeProject now is hard and not sufficiently rewarded.

Whatever it returns first is the first. Or maybe you're using it wrong.

Dear,
Can anyone help me to make good database structure?

What i have are some locations where I have some machines. Every machines have 2 counters (IN and OUT). I need to be able to enter state of that counters and after that to compare that values with previous entry IN and OUT counters.

How I see it seams that somewere I need to keep information wich machine is on wich location. Beside that I need to track information about date and time of entries.

Situation:

Location 1 -> machine1, machine2, machine3
Location 2 -> machine4
Location 3 -> machine5, machine6, machine7, machine8
....

I need 3 kind of reports:
○ Deference between new IN / OUT and some old IN/OUT counters for every machine
○ Total IN/OUT for some location
○ Total IN/OUT for all location

How I need to organize this datas?

Thanks for helping me,
akioki

3 tables

Location
 LocationID int identity(1,1)
 Location varchar(20)

Machine
 MachineID int identity (1,1)
 LocationID int
 Machine varchar(20)

TranTable
 TranID identity (1,1)
 MachineID
 Direction
 Date

This assumes a machine can have only 1 location and the machine can be checked in/out many times. Using a date on the transaction table will allow you to get durations, and state on a given date

Never underestimate the power of human stupidity
RAH

Hi All,
I am using SQL Server 2005. My problem is that I am unable to start MSSQL Server. When I go to services.msc, i found that the state of MSSQLServer is stopped. When I click on start, it gives me this error:

The SQL Server (MSSQLSERVER) service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts Service.

I try all possible things, google it and atlast re-install SQL Server but nothing happen. So please help me??

Thanks

Have you checked the event log to see if there are any messages relating to this in there?

me, me, me

"The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!"
Larry Niven

Ya, I checked it but didn't understand. I am providing you the log.

2010-03-25 18:55:55.53 Server      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) 
	Oct 14 2005 00:33:37 
	Copyright (c) 1988-2005 Microsoft Corporation
	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2010-03-25 18:55:55.53 Server      (c) 2005 Microsoft Corporation.
2010-03-25 18:55:55.53 Server      All rights reserved.
2010-03-25 18:55:55.53 Server      Server process ID is 2772.
2010-03-25 18:55:55.53 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\ERRORLOG'.
2010-03-25 18:55:55.53 Server      This instance of SQL Server last reported using a process ID of 212 at 3/25/2010 6:40:35 PM (local) 3/25/2010 1:10:35 PM (UTC). This is an informational message only; no user action is required.
2010-03-25 18:55:55.53 Server      Registry startup parameters:
2010-03-25 18:55:55.53 Server      	 -d C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\master.mdf
2010-03-25 18:55:55.53 Server      	 -e C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\ERRORLOG
2010-03-25 18:55:55.53 Server      	 -l C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mastlog.ldf
2010-03-25 18:55:55.54 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2010-03-25 18:55:55.54 Server      Detected 2 CPUs. This is an informational message; no user action is required.
2010-03-25 18:55:55.85 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2010-03-25 18:55:55.87 Server      Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2010-03-25 18:55:56.90 Server      Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2010-03-25 18:55:56.90 Server      Database Mirroring Transport is disabled in the endpoint configuration.
2010-03-25 18:55:56.90 spid4s      Starting up database 'master'.
2010-03-25 18:55:56.90 spid4s      Error: 17207, Severity: 16, State: 1.
2010-03-25 18:55:56.90 spid4s      FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\master.mdf'. Diagnose and correct the operating system error, and retry the operation.
2010-03-25 18:55:57.07 spid4s      SQL Trace ID 1 was started by login "sa".
2010-03-25 18:55:57.09 spid4s      Starting up database 'mssqlsystemresource'.
2010-03-25 18:55:57.24 spid4s      Server name is 'MAC5'. This is an informational message only. No user action is required.
2010-03-25 18:55:57.24 spid9s      Starting up database 'model'.
2010-03-25 18:55:57.24 spid9s      Error: 17207, Severity: 16, State: 1.
2010-03-25 18:55:57.24 spid9s      FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\model.mdf'. Diagnose and correct the operating system error, and retry the operation.
2010-03-25 18:55:57.37 spid9s      Clearing tempdb database.
2010-03-25 18:55:57.42 Server      A self-generated certificate was successfully loaded for encryption.
2010-03-25 18:55:57.42 Server      Error: 17182, Severity: 16, State: 1.
2010-03-25 18:55:57.42 Server      TDSSNIClient initialization failed with error 0x7e, status code 0x60.
2010-03-25 18:55:57.42 Server      Error: 17182, Severity: 16, State: 1.
2010-03-25 18:55:57.42 Server      TDSSNIClient initialization failed with error 0x7e, status code 0x1.
2010-03-25 18:55:57.42 Server      Error: 17826, Severity: 18, State: 3.
2010-03-25 18:55:57.42 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2010-03-25 18:55:57.42 Server      Error: 17120, Severity: 16, State: 1.
2010-03-25 18:55:57.42 Server      SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Yikes - wish I hadn't asked.

Okay, well, I'd be looking for the first reported error and concentrate on that:

Starting up database 'master'.<br />
Error: 17207, Severity: 16, State: 1.

Start with that and see how far you get. (If anyone else has a better suggestion now would be the time...)

me, me, me

"The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!"
Larry Niven

Hello,

let's have the following simple example. An SQL table consists of three columns:

RecordId,
GenerationId,
Fitness.

RecordId is a unique key. GenerationId and Fitness columns contain values that might and do repeat themselves over many rows.

Now how to do a simple select of the unique RecordId which has the highest Fitness, grouped by GenerationId?

Obviously,

SELECT max(Fitness) FROM myTable GROUP BY GenerationId

returns the highest Fitness for each GenerationId, but how to get the particular row which has this highest Fitness?

Fitness values might repeat themselves for the same GenerationId values, so an aggregate function also has to be used to select a distinct RecordId for a particular GenerationId. It doesn't matter which RecordId is selected as long as it has the highest Fitness for the particular GenerationId.

Thank you very much for any ideas!
Michal

the result should look something like this:

GenerationId, Fitness, RecordId
0,312.53,12
1,365.42,18
2,412.56,28
3,418.26,34
4,526.79,41
5,528.,48

...

Do you have some sample data for the table that would be used to get the result you're looking for?
Scott

yes, let's use the following sample data.

source table:

RecordId GenerationId Fitness
0 0 150.5
1 0 148.2
2 0 172.5
3 1 183.2
4 1 189.8
5 1 183.2
6 1 215.1
7 1 221.3
8 2 208.4
9 2 232.1
10 3 225.1
11 3 236.4
12 3 231.5
13 3 236.4
14 4 236.4
15 4 245.1
16 5 251.9
17 5 250.4
18 6 251.9
19 6 262.4

based on how one aggregates multiple rows with the same Fitness for the same GenerationId, one of the possible outcomes should look like the following. here I used max(RecordId), but it doesn't really matter:

GenerationId maxFitness RecordId
0 172.5 2
1 221.3 7
2 232.1 9
3 236.4 13
4 245.1 15
5 251.9 16
6 262.4 19

I'm able to achieve this result with joining the source table to itself, but I hope there has to be a more elegant way on how to do this without breaking one's neck with multiple lines of SQL code.

thanks for any help and ideas!
Michal

SELECT g.GenerationId,
	d.Fitness,
	d.RecordId
FROM
(
	SELECT DISTINCT GenerationId
	FROM [Source]
) AS g
	CROSS APPLY
	(
		SELECT TOP 1 s.RecordId, s.Fitness
		FROM [Source] AS s
		WHERE s.GenerationId = g.GenerationId
		ORDER BY s.Fitness DESC
	) AS d
ORDER BY g.GenerationId ASC

Personally, I don't see a problem with joining to the source table itself, or an inline view built off of the source table, so I came up with this which returns your desired result:

SELECT cp.generationid, cp.fitness, MAX(cp.recordid)
FROM cp,
(SELECT generationid, MAX(fitness) maxfitness
FROM cp
GROUP BY generationid) view1
WHERE cp.fitness = view1.maxfitness AND cp.generationid = view1.generationid
GROUP BY cp.generationid, cp.fitness
ORDER BY 1, 2, 3

view1 is an inline view off of the original table that I'm joining to.

I saw i.j.russell's response, but I'm not familiar with it (I'll need to read up on it).
Scott

Thank you very much for all your replies!

I used my original code with joining the table to itself at last. I was looking for some really simple way on how to handle this, but it looks there's no free lunch in T-SQL Smile | :)

Thanks again for all your valuable input!
Michal

Let's assume I have such table:

Pictures:

p_id
p_name
p_path
p_userId_mobile_addition [int]
p_userId_mobile_modification [int]
p_userId_www_addition [uniqueidentifier]
p_userId_www_modification [uniqueidentifier]

p_userId_mobile keys reference UsersMobile table.
p_userId_www keys reference UsersWWW table.

These keys give us information about the user that added or modified the picture. There are 4 keys as addition and modification can be performed with www or PocketPC interface.

How to create a view presenting:

1. p_id,
2. p_name,
3. p_path
4. username (from UsersMobile or UsersWWW) for addition
5. username for modification through www
6. username for modification through PocketPc

Platform: MS SQL Server 2005

Best regards,

where is the problem? Confused | :confused:

what did you try?

we make no query for you Dead | X|

Try something similar to this:

select  p_id, 
        p_name, 
        p_path,
        case when uw.username is not null then uw.username 
             when um.username is not null then um.username 
             else null
        end creator,
        uw.username,
        um.username
from    pics
left join usersmobile um
    on  pics.p_userId_mobile = um.p_userId_mobile
left join  UsersWWW uw
    on  pics.p_userId_www = uw.p_userId_www

But your problem is really bigger than the query, your datamodel isn't very good.
You need to normalize the tables to avoid corruption of your data

Here's a suggestion (with room for improvement):

Image
  imageid PK
  name
  path

users /*only one usertable*/
  userid PK
  username

accesstype
  accesstypeid PK
  accesstype  /*www and mobile*/

useraccess
  useraccessid PK
  userid       FK
  accesstypeid FK

unique key (userid,accesstypeid)

imagecreated
  imagecreatedid PK
  imageid        FK
  useraccessid   FK
  createdate

unique key (imageid,useraccessid) /*an image can only be created once*/

imagechanged
  imagechangedid PK
  imageid        FK
  useraccessid   FK
  changedate

As this allows storage of several changes per image you need to rethink your original query, or use max(changedate) to get the last change

"When did ignorance become a point of view" - Dilbert

Dear All,
My question is: where the actual files stored in the database, i.e: if i have records of pdf files, are these files saved in the database tables of application it self (oracle, sql2008), or it will be some where on the H.D. and the path will be recorded in the database tables, lets say Oracle D.B??

Any clarification guys,
Thanks in advance.

0 will always beats the 1.

Depends on the datatype. If it's a BLOB the file is stored directly in the database. If it's BFILE a reference to the filesystem is stored in the table.
This answer is valid for Oracle. I would be surprised if SqlServer didn't have the same possibilities.

"When did ignorance become a point of view" - Dilbert

Thanks Jörgen

0 will always beats the 1.

You're welcome.

"When did ignorance become a point of view" - Dilbert

Hy, im evaluating how to implement Audit trail in a multi-tier .NET 3.5 Database driven Webapplication based on nettiers and SQL-Server 2008.

I read a lot about the subject, but im still not sure whats the easiest way to do it.

Is it possible at all to implement it (without refactoring everything) with the entity framework if the rest of the application never used the EF?
Or do you guys think, that triggers would do the job just fine?

Ah, and a 2nd question: Do I get this right, that frameworks like log4net are just for logging debug-infos and not for audittrail?

thanx a lot for any input!

Andreas

andreas.schaerer wrote:
that triggers would do the job just fine

Sorry, stopped reading at this point. Anyone who has positive thoughts about triggers needs to be taken out and boiled in oil, shot, drawn and quartered and then made to support the abomination they have created. I hate triggers.

I know they are the "recommended" solution to this problem but I still hate em. I have also never had to implement a really stringent auditing solution. You also need to define the level of auditing you need to do. We usually settle for a log of who changed the record and when, tracking from and to data is whole nother level of nasties.

Never underestimate the power of human stupidity
RAH