Updating an Access database based on a sub-select - Database Discussion Boards

Re: error with OUTPUT

Not Active14-Oct-09 10:24

Not Active

14-Oct-09 10:24

jrahma wrote:
sql_command.Parameters.Add("@banner_file", SqlDbType.VarChar);

It seems fairly obvious, add a size

sql_command.Parameters.Add("@banner_file", SqlDbType.VarChar, 255);

only two letters away from being an asset

New memory manager for db4o object database

netquake14-Oct-09 3:45

netquake

14-Oct-09 3:45

Hi.

I just wanted to let you know that db4o has improved its memory manager making it more appealing to .NET & Java developers who work in memory constrained environments. In memory usage of db4o is now more closely integrated with the storage API which removes redundancies while bringing better support for administrative features such as defragment and backup. The new PagingMemoryStorage implementation significantly reduces memory footprint.

More info:

http://developer.db4o.com/blogs/product_news/archive/2009/08/13/memories-running-db4o-in-memory-revisited.aspx

Best!

Re: New memory manager for db4o object database

_Damian S_14-Oct-09 15:36

_Damian S_

14-Oct-09 15:36

I think you should pay to advertise like others have to...

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!!

Real time data transfer

krishnaveer14-Oct-09 3:04

krishnaveer

14-Oct-09 3:04

Hi,

Which method is the best way to transfer realtime data from one sqlserver to another sqlserver.

Plz let me know..

Thanx...

krishna veer singh

Re: Real time data transfer

J4amieC14-Oct-09 3:57

J4amieC

14-Oct-09 3:57

Depending on where you live, possibly carrier pigeon[^] Laugh | :laugh:

For a more traditional approach google database mirroring[^]

Re: Real time data transfer

i.j.russell14-Oct-09 4:38

i.j.russell

14-Oct-09 4:38

If you are using 2005 or greater then you should look at Sql Server Service Broker as it was designed specifically for your requirement.

Re: Real time data transfer

Ashfield14-Oct-09 21:01

Ashfield

14-Oct-09 21:01

Do you mean transfer or replicate? For the former, assuming sql server 2005 or later, useService Broker, for the latter google replication.

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

Updating an Access database based on a sub-select

Kyle Wood13-Oct-09 9:38

Kyle Wood

13-Oct-09 9:38

I have an Access database which contains every date between 01/01/2009 and 21/12/2029. In a 2nd column is a char value indicating whether the date is a working day or not. I need to decide whether xmas & boxing day fall on a non-working day and if so adjust the working day column value. I have the following to calculate the adjusted dates:

SELECT  switch((DATEPART("w",FullDate,2) In (6,7)),DATEADD("d",+2,FullDate),(DATEPART("w",FullDate,2) Not In (6,7)),FullDate)
FROM UKWorkingDays
WHERE DATEPART("m",FullDate,2)=12 And DATEPART("d",FullDate,2) In (25,26);

If I wrap this in an update statement the SQL appears to execute but does nothing at all

UPDATE UKWorkingDays SET WorkingDay = "B"
WHERE FullDate in
(

SELECT  switch((DATEPART("w",FullDate,2) In (6,7)),DATEADD("d",+2,FullDate),(DATEPART("w",FullDate,2) Not In (6,7)),FullDate)
FROM UKWorkingDays
WHERE DATEPART("m",FullDate,2)=12 And DATEPART("d",FullDate,2) In (25,26)

);

Can anyone give me any assistance please ?

Re: Updating an Access database based on a sub-select

Mycroft Holmes13-Oct-09 14:36

Mycroft Holmes

13-Oct-09 14:36

Ew Access again - and a newer version than I have worked with. Some thoughts.

Why do you have the switch (what is switch doing in SQL code) in the select portion, surely you want that in the where clause.

Select Fulldate
From UKWorkingDays
Where fulldate in (filter 25/6) and weekday in (filter weekdays)

I leave it to to grasshopper to fix the filter.

Re: Updating an Access database based on a sub-select

Kyle Wood14-Oct-09 5:03

Kyle Wood

14-Oct-09 5:03

Thanks for the reply Mycroft but I'm not sure that it is of much help.

Take this year for example. 25/12 is a Friday, 26/12 is a Saturday so 28/12 is actually a holiday not a working day. My inner select statement returns 2 rows correctly containing Friday 25/12 and Monday 28/12. I then have to check every row in the table against these returned values and if equal mark the date as a non-working day (my outer update). The inner select works a treat so I thought an update statement where date in (inner select) would achieve what I wanted but for some reason it appears to do nothing, no rows returned updated and no exception thrown. Maybe its looping too many times and would eventually finish. I'll have to re-visit my code and see if there's a better way.

Re: Updating an Access database based on a sub-select

Kyle Wood14-Oct-09 5:26

Kyle Wood

14-Oct-09 5:26

More on this..

I've just stripped out all but this years dates and run my query which has done exactly as I expected and updated 25 & 28/12. I guess its just baulking at the number of repetitions it has to perform on the full table so I'm on the right track but do need to improve the code.

How to Export a SQLExpress Database

BoySetsFire13-Oct-09 6:54

BoySetsFire

13-Oct-09 6:54

Hi,
i want to export an sql express database... i have create the db in vs 9... if i user the "publish to provider" function, i have a error...

I want my old enterprise manager back... Wink | ;-)

thx benny

Re: How to Export a SQLExpress Database

Not Active13-Oct-09 7:08

Not Active

13-Oct-09 7:08

BoySetsFire wrote:
I want my old enterprise manager back

So install it. SQL Server Management Studio Express[^]

only two letters away from being an asset

Re: How to Export a SQLExpress Database

BoySetsFire13-Oct-09 9:46

BoySetsFire

13-Oct-09 9:46

thank you!!! i try it... Smile | :)

Generate combinations for series

Member 277153113-Oct-09 4:34

Member 2771531

13-Oct-09 4:34

declare @a varchar(500)
set @a = '1,2,3,4'

how to generate output for all the combinations of input @a like 1,2,3,4 ; 1,2,4,3 ; 1,3,2,4 ; 1,3,4,2 ; 1,4,2,3 ; 1,4,3,2 ; 2,1,3,4; 2,1,4,3 ; 2,3,1,4...

All the numbers will be in series like for 10 digits.. 1,2,3,4,5,6,7,8,9,10.

i require all the combinations to be generated. the series i found like in the input if last (N) and last-1 (N-1) digits are interchanged, in series 1,2,3,4, second sequence wil be 1,2,4,3 ( interchange last two digits). next 1,3,2,4(N-3 digit is incremented and missing numbers 2,4 are placed).Next sequence wil be 1,3,4,2 ( interchange last two digits).. it could give all the combinations.

Could some one help me in providing solution for this. while i am using sql server 2005.

thanks in advance,
rpp

Re: Generate combinations for series

Henry Minute13-Oct-09 5:44

Henry Minute

13-Oct-09 5:44

I do not have an answer in T-SQL or SQL but here[^] is a link to a C# recursive algorithm that you might find useful.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Re: Generate combinations for series

PIEBALDconsult13-Oct-09 16:02

PIEBALDconsult

13-Oct-09 16:02

Ew, recursion is not necessary for this, but I agree that SQL isn't the right tool.

Re: Generate combinations for series

Member 277153114-Oct-09 21:53

Member 2771531

14-Oct-09 21:53

but my requirement is, i need to work on sql server 2005 only.

Stored Procedure

MsmVc12-Oct-09 22:20

MsmVc

12-Oct-09 22:20

Hi All

How can i use Stored Procedure?I am using MySql and ODBC.in the code i am create table andinsert the data.It's take too much time.So i want to do through Stored Procedure.Plz help me

Re: Stored Procedure

εїзεїзεїз12-Oct-09 23:07

εїзεїзεїз

12-Oct-09 23:07

Did you googled , plus on front end which tool are you using

try this

http://dev.mysql.com/tech-resources/articles/mysql-storedproc.html[^]

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

How to detect orphaned connections?

Oshtri Deka12-Oct-09 21:16

Oshtri Deka

12-Oct-09 21:16

I know about sp_who and sp-who2 procedures, but how can I detect orphaned connections/processes?
You will agree that LastBatch information should be ignored in most cases.
I am trying to make a services which logs some statistical data about connecting to sql server and killing orphaned connections would be a nice feature.

I use Sql Server 2005 Express.

Total Newbie Question...

Bomb_shell12-Oct-09 10:21

Bomb_shell

12-Oct-09 10:21

I've been asked to create a database for my department, using a template from another business unit. I'm good with HTML, MS Access, and SQL, but the current system is asp-based and I've never played with that before. Am I headed for trouble? What kind of transfer of learning should I expect?

My software administrators don't have anything on their list of "standard applications" for this, so I'll have to request them to purchase something new. What do you all recommend?

Visual Studio

David Mujica12-Oct-09 10:39

David Mujica

12-Oct-09 10:39

If you are starting from scratch, you should look at using Visual Studio 2008 and develop your web based app as a ASP.NET applicaiton.

Get your feet wet, then dive in head first. Smile | :)

Re: Total Newbie Question...

Paul Conrad13-Oct-09 5:37

Paul Conrad

13-Oct-09 5:37

I agree with David on the part of getting yourself VS2008 if you do not already have it.

Bomb_shell wrote:
Am I headed for trouble?

Not necessarily so. It will be a valuable learning experience for you and feel free to ask any additional questions you have Big Grin | :-D

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham

How to attached oracle Database in any other Computer?

Rahad Rahman12-Oct-09 0:41

Rahad Rahman

12-Oct-09 0:41

How to attached oracle Database in any other Computer?

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