Database

krishnaveer19-Oct-09 23:45

krishnaveer

19-Oct-09 23:45

No any complex thing is their in my system. only i'm selecting data from web sqlserver table and inserting it in to local sql server tables.

krishna veer singh

Ashfield20-Oct-09 1:52

20-Oct-09 1:52

yes, but I bet there is a where clause, so are those columns indexed? Its far too complex to do in a fotrum without any table defs, index defs, usage stats etc etc. Run your query through query analyser and look at the execution plan, that should at least give you some clues.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

εїзεїзεїз19-Oct-09 21:20

εїзεїзεїз

19-Oct-09 21:20

Can you show your store procedure statement

Best Regards,
SOFTDEV
If you have knowledge, let others light their candles at it

can an Access DB and a SQLce DB be used by multiple people simultaneously ?

krishnaveer19-Oct-09 23:43

krishnaveer

19-Oct-09 23:43

In SP there is nothing but only select and insert statements, i mean i'm selecting new data from web sql server and inserting it into our local sqlserver.

krishna veer singh

abiemann19-Oct-09 8:21

abiemann

19-Oct-09 8:21

I always assumed that accessing the same Access database by multiple users somehow just works, i.e. many users can read and write to an Access database in a shared folder on the network.
I assumed the same functionality with SQLce.

However, I've never been able to test it.

Does it work as I assumed ?

Re: can an Access DB and a SQLce DB be used by multiple people simultaneously ?

Eddy Vluggen19-Oct-09 10:27

Eddy Vluggen

19-Oct-09 10:27

abiemann wrote:
I always assumed that accessing the same Access database by multiple users somehow just works, i.e. many users can read and write to an Access database in a shared folder on the network.

Happens way too often and although Access provides locking-mechanisms and the likes, you'll find that it fails to perform adequately.

SQL Express is also available free, and you can easily upsize your Access-database to SQL Express.

I are Troll Smile | :)

Re: can an Access DB and a SQLce DB be used by multiple people simultaneously ?

_Damian S_19-Oct-09 14:33

_Damian S_

19-Oct-09 14:33

Yes, it works as you assumed... However, in both cases, each user needs to have their own front end installed (whether that's an Access DB, VB, whatever) You don't want a bunch of users opening the same file at the same time (ie: a single access database containing both tables as well as queries, forms and reports....)

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!!

Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!!
Do something wonderful - make a donation to Camp Quality today!!

Re: can an Access DB and a SQLce DB be used by multiple people simultaneously ?

abiemann19-Oct-09 16:07

abiemann

19-Oct-09 16:07

the Access DB is used to only store data and it will be located in some shared folder on the network.
There's about 10 client apps (which is probably what you meant by "front end") that will, at any point of time, read data from the database and also write data to the database.

Forms will not be implemented in the database directly.

Re: can an Access DB and a SQLce DB be used by multiple people simultaneously ?

_Damian S_19-Oct-09 19:52

_Damian S_

19-Oct-09 19:52

Access and SQL Server will both be fine as a back end then...

server down

baala.m19-Oct-09 3:07

baala.m

19-Oct-09 3:07

let us take the server is down and all the data are corrupted. other than backup,replication, log shipping what can we do to get back the data or make the customer to proceed with the work ?

Repeat

Not Active19-Oct-09 3:38

Not Active

19-Oct-09 3:38

Repeating the question won't help

only two letters away from being an asset

Re: Repeat

scottgp19-Oct-09 5:34

scottgp

19-Oct-09 5:34

Ah, but this time some of it is bold and underlined (I guess for those of use that were too dim to understand the first time). Smile | :)

Re: Repeat

Mycroft Holmes19-Oct-09 14:25

Mycroft Holmes

19-Oct-09 14:25

I'm waiting for a reply, but I don't have a backup, and my response will be then you don't have a client!

converting nvarchar to smalldatetime

Lalit singh19-Oct-09 1:08

19-Oct-09 1:08

Hi friends.

Please tell me how to convert nvarchar to smalldatetime in sql server

Ashfield19-Oct-09 1:18

19-Oct-09 1:18

With the convert function.

declare @a nvarchar(20)
set @a = '1 jul 2009 15:30:45'
select convert(smalldatetime,@a)

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Lalit singh19-Oct-09 1:52

19-Oct-09 1:52

Hi Thanks For your reply ,

The thing is that i have one table in which date is stored in column(datataype nvarchar)
There are more than lacs of records in this table.

so i want to convert this field into following format

ORIGINAL FORMAT OF FIELD IN TABLE (DATATYPE:-NVARCHAR) => 30/11/2009

I WANT INTO FORMAT : 30112009
AND ANOTHE FORMAT : 30/11/2009(SMALL DATETIME)

Please help me out.

I tried the solution which you gave but its giving following error

"Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type smalldatetime.
"

Please help me out

Ashfield19-Oct-09 8:45

19-Oct-09 8:45

Not sure why you get this error.

declare @a nvarchar(20)
select @a = '30/11/2009' 
select convert(smalldatetime,@a)

gives me 2009-11-30 00:00:00, which is correct. How it is displayed (i.e. 30/11/2009 or 30112009) is down to your format mask, SQL Server simply stores the datetime value as a number.

Please, before posting any more questions around this, use the F1 key and read up on date time data types.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Lalit singh19-Oct-09 18:40

19-Oct-09 18:40

What you said is very correct , i am not denying it. The thing is that i am inserting into destination column(smalldatetime) from source column(nvarchar) and the number of records are around 4lac.
I am inserting through sql script in query analyser. After insrting some 20k records it generates that "Arithmetic overflow error".

This was task given to me. thats why otherwise i could change my destination column to datetime. Its working.

But I am forcebly want it to smalldatetime as this was task for me.

_Damian S_19-Oct-09 19:04

_Damian S_

19-Oct-09 19:04

Have you done any data analysis to make sure that all the values you are trying to convert fall into the acceptable range for smalldatetime values?

Perhaps you have a record (around the 20K mark) that has a typo in the date, and instead of 30/11/2009 it's 31/11/2009 (which isn't valid) or 30/11/9999...

Ashfield19-Oct-09 20:56

19-Oct-09 20:56

So you have an invalid date in there. Use the isdate function to find any that are invalid.

declare @a nvarchar(20)
set @a = '30/11/2009'
select isdate(@a)
-- returns 1
set @a = '31/11/2009'
select isdate(@a)
-- returns 0

Its not rocket science.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Lalit singh19-Oct-09 23:08

19-Oct-09 23:08

You were correct there were dates in dd/mm/yyyy which were incorrect format according to ISDATE() function . So i converted it into mm/dd/yyyy format and it worked.

Now thing is that i just saved in date format only like '11/30/2009' but in table having column date (datatype smalldatetime) it stored like '11/30/2009 00:00:00.000'

I want to store only date (datatype=smalldatetime). Is there any way to store into sql server 2005 date column with date only excluding time.

Ashfield20-Oct-09 1:54

20-Oct-09 1:54

Lalit singh wrote:
Is there any way to store into sql server 2005 date column with date only excluding time.

No, as I said in an earlier post, sql server stores the date and time as a number, not in any format. Just use a format when you display it.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Lalit singh20-Oct-09 2:04

20-Oct-09 2:04

Thank you very much for your valuable response.

Luc Pattyn19-Oct-09 3:29

Luc Pattyn

19-Oct-09 3:29

why would you choose smalldatetime when all you want to store is a date?

Smile | :)

Luc Pattyn

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

Local announcement (Antwerp region): Lange Wapper? 59.24% waren verstandig genoeg om NEEN te stemmen; bye bye viaduct.