Database

I want to diplay particular records between two dates using nvarchar

13-Nov-08 4:45

Perhaps somehthing like:

UPDATE SomeTableName
SET SomeColumnName = (SELECT *
                      FROM OPENROWSET(BULK 
                                      'C:\AnyPath\AnyFile.Extension',
                                      SINGLE_BLOB) TableAlias )

The need to optimize rises from a bad design.

My articles[^]

Samiullah13-Nov-08 0:12

Samiullah

13-Nov-08 0:12

Hi,
I have created a table and in that i have inserted some records. then i want to display the records between specified dates then how to do that.

create table rough(checkeddate nvarchar(100))

These are the inserted values..

insert into rough values('1/01/2002')
insert into rough values('3/01/2002')
insert into rough values('12/01/2003')
insert into rough values('11/09/2006')
insert into rough values('8/01/2008')
insert into rough values('1/01/2004')
insert into rough values('1/01/2006')

insert into rough values('1/21/2003')
insert into rough values('1/31/2005')
insert into rough values('1/11/2006')
insert into rough values('1/01/2006')
insert into rough values('1/01/2002')
insert into rough values('1/01/2004')
insert into rough values('1/01/2006')

The output as follows

mm/dd/yyyy
----------
12/01/2003
11/09/2006
1/01/2006
1/21/2003
1/31/2005
1/11/2006
1/01/2006
1/01/2006

so my requirement is now i want to diplay particular records between two dates like as follows

select * from rough where checkeddate between '1/01/2006' and '12/31/2006'

Can anyone help me..

Re: I want to diplay particular records between two dates using nvarchar [modified]

Syed Mehroz Alam13-Nov-08 2:15

Syed Mehroz Alam

13-Nov-08 2:15

Samiullah wrote:
create table rough(checkeddate nvarchar(100))

Why are you storing date as an nvarchar? Try using a datetime type instead and you can easily filter records using the query you mentioned. But if you still want to achieve it using an nvarchar, you can simply cast the value to a datetime. Here's a small change to your query:

select * from rough where cast(checkeddate as datetime) between '1/01/2006' and '12/31/2006'

Regards,
Syed Mehroz Alam

My Blog
My Articles

Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

modified on Thursday, November 13, 2008 8:22 AM

Re: I want to diplay particular records between two dates using nvarchar

Samiullah13-Nov-08 23:44

Samiullah

13-Nov-08 23:44

Thanks for your immediate response. As iam new to sql server and its also my requirement to use the nvarchar thats why!

Re: I want to diplay particular records between two dates using nvarchar

Mycroft Holmes13-Nov-08 15:57

Mycroft Holmes

13-Nov-08 15:57

as Syed said - STORE YOUR DATES AS DATETIME. This is a fundamental error in design and you should be smacked for using nvarchar. You are now going to forever need to cast/convert that column to do anything with it.

More emphasis - USE THE CORRECT DATA TYPE

Never underestimate the power of human stupidity
RAH

Re: I want to diplay particular records between two dates using nvarchar

PIEBALDconsult13-Nov-08 16:04

PIEBALDconsult

13-Nov-08 16:04

Yeah, what they said... or at least use an ISO 8601 compliant format YYYY-MM-DD.

Debugging null data adapter

theFrenchHornet12-Nov-08 9:38

12-Nov-08 9:38

using C# and SQL Server 2005

Instantiate a data adapter on startup and re-use.
No problems when 1 station running.
When a number of stations running at the same time, data adapter goes null in the middle of the operation. i.e., It deletes some records from the database as it is supposed to do, but then goes null.

Is there some way to query the database to get more information on why this is happening? Any debugging ideas? I don't have much experience with SQL Server, so if you have an idea please give me the specifics. Thanks.

Wendelius13-Nov-08 5:15

13-Nov-08 5:15

First, I didn't quite understand what you mean with 'station'. However, I believe that it's hard to observe this situation from database since the reason most likely isn't in the database.

Instead, use debugger in Visual Studio, catch the exception immediately when it happens (use menu Debug/Exceptions... to catch all handled/unhandled exceptions) and then use breakpoints to narrow the area.

The most likely cause for this is that if you are reusing same data adapter, when the first operation ends and you perhaps dispose the object, you use the same variable somewhere else (or same code in another thread) and the value is set to null.

One way to get more information is to add diagnostics messages (for example using Trace.WriteLine) to every place where you instantiate the data adapter, set it to null (or dispose it, i.e. using blocks) or use it. This will give you info how things are going and in which order (especially if you're using threads).

Hope this helps,

Mika

The need to optimize rises from a bad design.

My articles[^]

theFrenchHornet13-Nov-08 5:47

13-Nov-08 5:47

Thanks for the suggestions, Mika.
By station I meant client.
Via Debug/Exceptions is how null data adapter was determined.
Adapter is instantiated on startup and disposed of when application quits - isolated in one class. Pretty simple in that respect.
Agree it might be caused in some other area - since it happens sporadically and has been hard to pinpoint using the debugger, was just wanting to try another avenue. I was hoping the database might keep something like a 'last error' the way it tracks number of deadlocks.

Have found a way around it for the time being.

Thanks again ...

Wendelius13-Nov-08 7:14

13-Nov-08 7:14

Okay,

Just for explanation: If the command would fail while in the database or connection gets lost for some reason, you end up in an error at the database. As a result of an error you would get an exception at calling side (in C# in this case). So, if you're not receiving any exceptions in your code, but the data adapter just suddenly goes to null, it's quite certain that the problem is in the code.

If you're using singleton or static class, perhaps one way to find out the problem is to define a property that returns your data adapter and keep the data adapter private in your class (if not already done so). Now in the property setter, first test if new value for the property is null and act based on that (throw an exception, write log etc). This could help you to isolate the cause.

Also you could double-check that you are not ignoring any exceptions anywhere. One reason could be that the statement actually fails in the database, but you don't get information about the exception (for example an empty catch block).

If you would end up in a deadlock you would get an exception in C# and have a message similar to:

"Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

However, if you suspect that deadlock might occur, you can set trace flag 1204 in startup parameters. After restarting the SQL Server, deadlock info if is written into errorlog at server side if a deadlock occurs.

Hope this helps you forward,

Mika

The need to optimize rises from a bad design.

My articles[^]

theFrenchHornet15-Nov-08 9:21

15-Nov-08 9:21

Thanks Mika,

It is the last bit about the trace flag that I was looking for. Setting it I believe requires access to the server machine which I don't have, but the person who does says they can help me do this next week and he thinks he knows how to set a trace.

Maybe more information than you are interested in, but since you put so much time in, don't want you to think I was ignoring your ideas. This was a situation of you have today to figure it out and then things are moving on and you need to be working on something else. Not to say it won't get revisited, but these are decisions made 'above' me. The null data adapter came about after code was introduced to allow for possible deadlock. It is most likely a case of interference/timing - something hasn't been thought through well enough for when multiple clients are running at the same time. Removing some functionality removed the deadlock problem and thus the need for the code that caused the null data adapter. Other people were looking into it from the C# side, and I was just asked to help if I could. I knew you could query the database for number of deadlocks and I had the permissions to do this. I thought if it was possible that the database could give any information, then it might at least be a clue for where to look.

Now I will know about the database trace flag - it will come in handy at some point. Thanks.

Wendelius15-Nov-08 9:59

15-Nov-08 9:59

You're welcome Big Grin | :-D

And I really appreciate that you took the time time answer. It's always good to know if a reply is helpful or not since we're all learning here Smile | :)

If you need assistance in the future, post a message and we'll try to help you.

Mika

The need to optimize rises from a bad design.

My articles[^]

PIEBALDconsult13-Nov-08 16:11

PIEBALDconsult

13-Nov-08 16:11

Look into threading problems (as Mika hinted).

Other than that, we may need to see your code.

theFrenchHornet15-Nov-08 9:22

How to get this nested level output

15-Nov-08 9:22

PIEBALDconsult,

Please see my response to Mika, and yes, if it gets revisted and I am involved in the revisit, I will look at the threading and try to get it narrowed down more. The code sits on an isolated system and is too complicated to reproduce here. The data adapter code is quite simple, but I think we're all agreed that that's probably just where another problem is manifesting itself. Thanks for your input.

SQL CE Exception

hammerstein0512-Nov-08 4:01

hammerstein05

12-Nov-08 4:01

I have a SQL CE 3.5 database that I'm building on a server and downloading to a handheld. Before trying to open the database I'm calling SqlCeEngine.Verify. In my development environment, I rarely have this fail, and when I do it appears to be my fault. Our customers are now using this software, but they are experiencing an issue I cannot recreate.

Basically when the download completes, the verify is failing on the customers handhelds. Each time the verify fails, I redownload the database and try again. However, it seems that when the database finally does verify, I get;

Unspecified Error [sqlceqp35.dll]

Does anybody have any idea what this does? I've tried searching, and I've tried putting in more logging to get a more accurate idea of where this is occuring. But it seems to prevent the database from opening until the software is restarted.

Re: SQL CE Exception

Ennis Ray Lynch, Jr.13-Nov-08 4:39

Ennis Ray Lynch, Jr.

13-Nov-08 4:39

Different version of the database. One solution I have seen is to manually create the DB on the handheld when the application starts rather than just copying the CE db. This allows the correct version to be used from the device standpoint. Really a PITA.

Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.

Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway

Most of this sig is for Google, not ego.

VenkataRamana.Gali11-Nov-08 18:10

VenkataRamana.Gali

11-Nov-08 18:10

There are two tables, NAMES (ID INT IDENTITY (1,1), NAME VARCHAR(255)) and
RELATIONSHIPS ( NAMEID INT , PARENT _ NAMEID INT ) linked via NAMES.ID = RELATIONSHIPS.NAMEID and where top-most name has a PARENT _ NAMEID = 0.

Show a nested list of names including LEVEL , NAMEID and NAME , where LEVEL indicates the nest level (or depth) from the top, as indicated in the expected output (below).

NAMES table content:
==================
ID NAME
1 Frank
2 Jo
3 Mary
4 Peter
5 Amy

RELATIONSHIPS table content:
==========================
NAMEID PARENT _ NAMEID
1 0
2 1
3 2
4 1
5 2

Expected Output:
===================
In the expected output, please note The sequence of the output, in which child elements appear immediately beneath their respective parent elements.

LEVEL ID NAME
0 1 Frank
1 2 Jo
2 5 Amy
2 3 Mary
1 4 Peter

any sounds please...

Ramana

Re: How to get this nested level output

ScottM111-Nov-08 19:42

ScottM1

11-Nov-08 19:42

This looks like a homework assignment.
First try and do it before you ask for help otherwise it defeats the purpose of it.

Re: How to get this nested level output

Wendelius12-Nov-08 7:39