Re: Weird MS SQL "Error" - Database Discussion Boards

Re: sql server

PIEBALDconsult23-Jul-14 8:32

PIEBALDconsult

23-Jul-14 8:32

Just put it back where you found it.

You'll never get very far if all you do is follow instructions.

Re: sql server

saharmirzaei23-Jul-14 22:26

saharmirzaei

23-Jul-14 22:26

thank you for your help Smile | :)

Re: sql server

Bernhard Hiller23-Jul-14 20:49

Bernhard Hiller

23-Jul-14 20:49

Perhaps you actually think of a function for converting from/to Persian calendar?

Re: sql server

saharmirzaei23-Jul-14 22:23

saharmirzaei

23-Jul-14 22:23

yeah i think about it and i dont know how i can use this.

Re: sql server

Mycroft Holmes23-Jul-14 22:47

Mycroft Holmes

23-Jul-14 22:47

Seems to be a solved problem, have some google foo[^]

Never underestimate the power of human stupidity
RAH

Weird MS SQL "Error"

GenJerDan23-Jul-14 7:07

GenJerDan

23-Jul-14 7:07

I ran this in the Management Studio:

update [Master] set Data_Thru = '7/23/2014 9:49:00 AM'
where Data_Thru = '7/23/2014 8:00:00 AM'

So why did it actually set the smalldatetime to 7/23/2014 9:50:00 AM?

(And don't yell at me for the table name. It wasn't my idea.)

We won't sit down.
We won't shut up.
We won't go quietly away.

YouTube
and
My Mu[sic], Films and Windows Programs, etc.

Re: Weird MS SQL "Error"

Richard Deeming23-Jul-14 7:47

Richard Deeming

23-Jul-14 7:47

Are you absolutely sure that that's the precise query that you executed, and that the row you're looking at is the one it updated?

smalldatetime[^] will round to the nearest minute, but it will only round up if the seconds are greater than or equal to 29.999; anything else will round down.

SQL

SELECT
    CAST('7/23/2014 9:49:29.998 AM' as smalldatetime),
    CAST('7/23/2014 9:49:29.999 AM' as smalldatetime)
;

/*
Output:
2014-07-23 09:49:00
2014-07-23 09:50:00
*/

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Weird MS SQL "Error"

GenJerDan23-Jul-14 8:10

GenJerDan

23-Jul-14 8:10

Richard Deeming wrote:
Are you absolutely sure that that's the precise query that you executed, and
that the row you're looking at is the one it updated?

Yep.

Freaked me out. I've never seen that before, and it had no problem with me going in and correcting it back down to 9:49 via T-Sql

We won't sit down.
We won't shut up.
We won't go quietly away.

YouTube
and
My Mu[sic], Films and Windows Programs, etc.

Re: Weird MS SQL "Error"

Richard Deeming23-Jul-14 8:18

Richard Deeming

23-Jul-14 8:18

Weird. Are there any triggers on that table? Or other users updating the same row at the same time? Confused | :confused:

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Weird MS SQL "Error"

GenJerDan23-Jul-14 8:28

GenJerDan

23-Jul-14 8:28

Nope. No triggers. And no users at all (yet).

Maybe we'll have to pass it off as a cosmic ray hitting the server and flipping a bit at just the right time. I see no resason for it, and Google came up with nothing, either.

We won't sit down.
We won't shut up.
We won't go quietly away.

YouTube
and
My Mu[sic], Films and Windows Programs, etc.

Re: Weird MS SQL "Error"

jschell23-Jul-14 11:55

jschell

23-Jul-14 11:55

When I run into impossible situations in code then the only solution is to examine my assumptions. That is always where I find the problem that lead to the impossibility in the first place.

Re: Weird MS SQL "Error"

Bernhard Hiller23-Jul-14 21:01

Bernhard Hiller

23-Jul-14 21:01

What's the version / localization of your SQL Server?
I tried it with SQL Server 10.0.5500, German localized Management studio. I adjusted the date format of your query, and tested it. And it worked as it was expected to work - I cannot reproduce your bug.

Re: Weird MS SQL "Error"

GenJerDan24-Jul-14 3:14

GenJerDan

24-Jul-14 3:14

10.50.1617.0 Management studio hitting 2008 R2 10.0.5500.0

I think it was a one-time glitch. I hate those.

We won't sit down.
We won't shut up.
We won't go quietly away.

YouTube
and
My Mu[sic], Films and Windows Programs, etc.

CONCAT names with condition

Jassim Rahma20-Jul-14 11:51

Jassim Rahma

20-Jul-14 11:51

Hi,

I have first_name, middle_name and last_name in my customers table.

I want to do

SQL

SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name)

The middle name is optional

so I want to ignore it if it's blank to be:

SQL

SELECT CONCAT(first_name, ' ', last_name)

because if I do it the normal way I'll have extra space between the first_name and last_name

Technology News @ www.JassimRahma.com

Re: CONCAT names with condition

Mycroft Holmes20-Jul-14 12:53

Mycroft Holmes

20-Jul-14 12:53

You can use ISNULL on the ~~first~~ middle name or a case statement.

Case statement is more robust as it caters for ''.

Never underestimate the power of human stupidity
RAH

modified 20-Jul-14 22:36pm.

Re: CONCAT names with condition

PIEBALDconsult20-Jul-14 14:11

PIEBALDconsult

20-Jul-14 14:11

As long as you're doing it that way, you might as well use REPLACE. Big Grin | :-D

You'll never get very far if all you do is follow instructions.

Re: CONCAT names with condition

GuyThiebaut20-Jul-14 21:35

GuyThiebaut

20-Jul-14 21:35

Without using CONCAT you can do this:

SQL

rtrim(isnull(first_name,'',first_name+' ') + isnull(middle_name,'',middle_name+' ') + isnull(last_name,'',last_name))

The rtrim will remove a trailing space where the person does not have a last name.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Fatal error encountered during command execution while executing Mysql query

ven75319-Jul-14 19:42

ven753

19-Jul-14 19:42

Hi i have created one web application and in that i need to read the data from Mysql database and bind to gridview.

I have written Mysql query as follows
SELECT @row_number:=@row_number+1 AS row_number,Name FROM tablename, (SELECT @row_number:=0) AS r

After running in Mysql in database it will and work show the result.

But if take same query and execute through C# code below error is coming.

An exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll but was not handled in user code

Additional information: Fatal error encountered during command execution.

and i will get Break or continue pop up. If i give continue it will show Parameter '@row_number' must be defined.
even i defined like int @row_number = 0; but same error.

I need to achieve serial number as shown below

ex in database
slno name
10102 nama1
2123 name2
5203 name3

result
1 nama1
2 name2
3 name3

how to achieve this. If anybody knows please reply me.

Thanks in advance.

The Log For the Database is Not Available

Prakash S19-Jul-14 0:58

Prakash S

19-Jul-14 0:58

This error occurs frequently and no insert query is running.
if i restart the sql server services it is working fine,but often getting this error message.

Recently i changed my management studio to 2012 as my server is 2008 R2 version.How to fix this issue permanently?

Re: The Log For the Database is Not Available

Andrius Leonavicius19-Jul-14 13:40

Andrius Leonavicius

19-Jul-14 13:40

Hi,

Usually it's because there are corruption problems related to the storage system. Have you tried running DBCC CHECKDB against the database?

SQL

DBCC CHECKDB ('Your DB Name') WITH NO_INFOMSGS;
GO

This could give you more information.

Also, check the Auto Close property:

SQL

SELECT DATABASEPROPERTY ('Your DB Name', 'IsAutoClose');
GO

If it returns 1 (Auto Close is on), then that may be the reason of your problem...

Regards,
Andrius Leonavicius

Re: The Log For the Database is Not Available

Prakash S20-Jul-14 20:08

Prakash S

20-Jul-14 20:08

Dear Sir,

It shows the following error

Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created.
Either the reason is given in a previous error or one of the underlying volumes
does not support sparse files or alternate streams. Attempting to get exclusive
access to run checks offline.

my database shows is_auto_close_on=1.

What i should do to solve this?

Re: The Log For the Database is Not Available

Andrius Leonavicius21-Jul-14 3:01

Andrius Leonavicius

21-Jul-14 3:01

I'd recommend to turn off the Auto Close:

SQL

ALTER DATABASE database_name
SET AUTO_CLOSE OFF;
GO

Speaking about errors, try to restart the server and run DBCC CHECKDB again. Also, take a look at Windows Event Logs for errors.

BTW, do you have enough disk space?

Regards,
Andrius Leonavicius

Re: The Log For the Database is Not Available

Kornfeld Eliyahu Peter19-Jul-14 22:54

Kornfeld Eliyahu Peter

19-Jul-14 22:54

The Log For the Database is Not Available[^]
And please, do not post your question all over the place...

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

How to select persons who didn't pay they'r bill for two mounth

mohammadkaab18-Jul-14 5:57

mohammadkaab

18-Jul-14 5:57

hi , i have two table first table name is person and second table name is payment
what i'd like to do is that , i want to select those persons who haven't paid they'r bill for two month

SQL

select person.* from (person INNER JOIN
                         payment ON person.national_number = payment.national_number) 
where (payment.payment_date > 'two month ago from now')

but this w'll fetch those persons who paid their bill for the two month ( i don't want them) . but i need those who didn't pay they'r bill for the two month
i don't know if you understand it or no .

Re: How to select persons who didn't pay they'r bill for two mounth

Richard MacCutchan18-Jul-14 6:32

Richard MacCutchan

18-Jul-14 6:32

Surely

SQL

where (payment.payment_date < 'two month ago from now')

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