Database

Simulate transaction: non blocking insert / update

7-Feb-13 13:45

Yes
G to connectionstrings.com as a research beginning.

Never underestimate the power of human stupidity
RAH

Bob Stanneveld7-Feb-13 11:12

Bob Stanneveld

7-Feb-13 11:12

Hello,

We're currently implementing a warehouse management system with some customizations. One task is to plan the work in the warehouse by releasing waves of picking activities. The release is handled by a stored procedure. This stored procedure generates data on the amount of work that needs to be done, number of containers that are required, etc.. The planner needs to know this information in order to release the appropriate waves.

So, in order to capture the required data, we have another stored procedure which releases all waves that are not yet released and rolls back the transaction once it's done. This roll-back is a guarantee. The problem is that during this 'simulation' other queries are blocked, which creates a performance problem.

Is there a way to run the 'simulation' without blocking all other queries?

Thanks in advance!

Kind regards,
Bob Stanneveld

Re: Simulate transaction: non blocking insert / update

Mycroft Holmes7-Feb-13 13:44

Propagating a MySQL transaction with MSMQ II

7-Feb-13 13:44

Sounds to me like you are doing something dramatically wrong, running a simulation on production data, therefore requiring the roll back, or building a business process on cancelling a transaction.

A more normal method of simulating would be to restore production to another instance, run the process to completion and then restore again to repeat.

Never underestimate the power of human stupidity
RAH

Ger Hayden6-Feb-13 8:08

Ger Hayden

6-Feb-13 8:08

Earlier this week I queried the possibility of Propagating a MySQL transaction with MSMQ on a concept level.

Thanks to Pete I crossed that bridge. But there is a stumbling block.

MySQL transactions themselves do not appear compatible with TransactionScope!

Short of migration to SQLServer what are my choices?

Ger

Missing Data in an Access Report

Vimalsoft(Pty) Ltd6-Feb-13 3:03

6-Feb-13 3:03

Good Day All

I imported data into a Ms Access 2013 from SQL and all the data is there. I created a Query and also created a report that will use the Query. So the Query returns all the data. There are around 590 000 records that needs to be displayed by the record and records that are returned by the Query. when i do a Print Preview it gives me

Page 32767 of 12224

So i am afraid to start printing because from this look it seems like it does not show all data, but my assumption is that Print Preview cant show all the data , even if i try to go to the last page, can anyone clear my assumption.

Thanks

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com

jschell6-Feb-13 8:11

6-Feb-13 8:11

Vuyiswa Maseko wrote:
There are around 590 000 records...So i am afraid to start printing

You should be.

Why exactly do you think that you should print something that is probably going to be about 6000 pages (at 100 records per page).

Who has told you that they are going to read this? Have you suggested to them what 6000 pages represents? Perhaps if you put a box (10 x 500 packages) on their desk and ask them what they planned to do with that then you might start a conversation that would lead to reports that are actually useful.

Other than that I certainly wouldn't attempt to print that at one go. If it messes up in the middle you have to start the entire job over. So select 400 pages at a time and print.

Might tell them you need your own printer too as it is going to tie it up for quite a while.

Vimalsoft(Pty) Ltd6-Feb-13 19:01

6-Feb-13 19:01

Good Day Jschell

Thank you for your reply. i will like to reserve my harsh comment for the other reply that i received after you , but i will politely reply to your question.

Who has told you that they are going to read this?

This is a book called the valuation roll , everyone comes and read this book because it tells a person how much they are going to pay in taxes ,for sure more than a million people are going to read the book.

Have you suggested to them what 6000 pages represents? Perhaps if you put a box (10 x 500 packages) on their desk and ask them what they planned to do with that then you might start a conversation that would lead to reports that are actually useful.

I have now decided to split it to 30000 to be on the safe side and created temp tables with the data sequential and its fine now thanks , what i learned is that Access Report will never output more than "32767", so basically in a Page you would have 10 records and if you take 590 000 and Divide by 10 it gives more 30000 Pages, so that is when i realized where and how to fix the problem.

Your reply was still going to point me to the direction of Splitting the data.

Thanks

jschell7-Feb-13 9:52

7-Feb-13 9:52

Vuyiswa Maseko wrote:
for sure more than a million people are going to read the book.

Nope.

There is no way that you are going to get one million people to read 6000 pages (plural) much les 30,000.

What is going to happen instead is that they are going to search for what they already know they are looking for. And then read 1 or perhaps as many as 10 pages to find what they are looking for.

And that is where the solution lies. Provide a way for them to search for it.

Vuyiswa Maseko wrote:
so that is when i realized where and how to fix the problem.

Presumably your design will have 30,000 static pages and then provide a way to figure out where something is in those pages and then go to the specific page.

Mycroft Holmes6-Feb-13 12:27

6-Feb-13 12:27

I agree with jschell, you are asking access to do something inherently dumb, print 1000s of pages and you wonder why it is giving you wrong results!

Never underestimate the power of human stupidity
RAH

Vimalsoft(Pty) Ltd6-Feb-13 19:06

6-Feb-13 19:06

Requirements in Software Development or in any industry might sometimes overcome the basic understanding of what makes sense to you.

Sometimes before reply you must try to understand what is the poster's point of view and after that you can reply or decide to be rude.

For your information, you can read my reply on jschell's reply.

Mycroft Holmes6-Feb-13 20:27

6-Feb-13 20:27

Your "harsh reply" may be justified. I should have been somewhat more diplomatic. However you say more than a million people are going to read this tome, astonishing, no really I will be absolutely astounded if a million people read that book.

Some may reference the book and here is where I should have been more sensitive, I automatically assure that referencing such a vast amount of data would only be done electronically, this may not be an option for some!

Still the viability of printing 1000s of pages via Access stikes me as using the wrong tool for the job. Anything lees than a professional printing solution would be the wrong tool!

I am mightily impressed that you did not resort to down voting the response Smile | :)

Never underestimate the power of human stupidity
RAH

Vimalsoft(Pty) Ltd6-Feb-13 20:32

6-Feb-13 20:32

Pardon me for the word "Read" it should be substituted with the word "Reference",

This book is also available electronically, but there are people who comes in some parts of the Offices in the the city who don't have access to the electronic version

jschell7-Feb-13 9:56

oracle error with winserver2008

7-Feb-13 9:56

Vuyiswa Maseko wrote:
Requirements in Software Development or in any industry might sometimes overcome
the basic understanding of what makes sense to you.

That it true to a limited extent.

HOWEVER
1. That doesn't mean that the requirements are always right.
2. That doesn't mean that the requirements are always complete.
3 That doesn't mean that the requirement are always understood.
4 That doesn't mean that the requirements are always possible nor logical.

A professional developer must be willing and capable of recognizing when there is a flaw in the requirements and tell the business people what those are so together they can work towards a solution that meets the business needs.

And as a guess in terms of this posting, for what you posted, I would suppose that #2 in the above was the problem. Since presumably you already knew you needed to produce indivdiual pages in an electronic form and not just one massive single paper report.

At least I am hoping that is what your other post meant.

fares35-Feb-13 1:03

fares3

5-Feb-13 1:03

Hello Dears,
I have a problem with my application within Win server 2008
, when it is work well within Win server 2003
when I try to use my app with win_ser2008 get the following error message:
/////////////////////////////////////////////////
Server Error in '/yyy' Application.
--------------------------------------------------------------------------------

ORA-00161: طول فرع الحركة 90 غير مشروع (العدد الأقصى المسموح64 )
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OracleClient.OracleException: ORA-00161: طول فرع الحركة 90 غير مشروع (العدد الأقصى المسموح64 )

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[OracleException (0x80131938): ORA-00161: طول فرع الحركة 90 غير مشروع (العدد الأقصى المسموح64 )]
System.Data.OracleClient.OciEnlistContext.Join(OracleInternalConnection internalConnection, Transaction indigoTransaction) +264679
System.Data.OracleClient.OracleInternalConnection.Enlist(String userName, String password, String serverName, Transaction transaction, Boolean manualEnlistment) +202
System.Data.OracleClient.OracleInternalConnection.Activate(Transaction transaction) +68
System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction) +33
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +1318
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +100
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +116
System.Data.OracleClient.OracleConnection.Open() +40
cOracleDBManipulation.tableHasData(OracleConnection dbConn, String tableName) +78
cOracleDBManipulation.fillDataTable(String TableName, String SelectStatment) +75
cOracleDBManipulation.SequenceNextValGen(String SequenceName) +40
INSERTGENIMP_New.exportItemsNo(DataTable toDT) +643
INSERTGENIMP_New.btm_isert_Click(Object sender, EventArgs e) +551
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.3053; ASP.NET Version:2.0.50727.3053
/////////////////////////////////////////////////////

Please I need to resolve this problem in near time as can.
Thanks for anybody help,
Eslam Fares

Re: oracle error with winserver2008

Bernhard Hiller5-Feb-13 4:57

Bernhard Hiller

5-Feb-13 4:57

Oracle provides the error code - just google for ORA-00161. Looks like a problem with the different data providers you installed on the machines.

char(255) vs varchar(255) vs varchar(MAX)

devvvy1-Feb-13 18:22

devvvy

1-Feb-13 18:22

I guess, the question is, why would I use

char(255)

instead of

varchar(255)

When I specify varchar(255), does SQL sever allocate [255] per cell? or does it allocate dynamically? If it statically allocate [255] bytes, then I don't see if there's much practical difference between char(255) and varchar(255) -- well, except varchar always store a one (or two) byte termination character internally.

If SQL server allocates 255 bytes when i specify varchar(255), then what about varchar(MAX)?

Thanks

dev

Richard MacCutchan1-Feb-13 23:07

Richard MacCutchan

1-Feb-13 23:07

See http://msdn.microsoft.com/en-us/library/ms176089.aspx[^].

devvvy2-Feb-13 15:39

devvvy

2-Feb-13 15:39

I did but not sure if I interpreted MSDN correctly.

<br />
<br />
char [ ( n ) ]<br />
<br />
    Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.<br />
varchar [ ( n | max ) ]<br />
<br />
    Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes. The ISO synonyms for varchar are char varying or character varying.

varchar(255) --- this means SQL server will always allocate 255bytes? Or depending on actual values being inserted for a particular row?

If say row 1, "Name" = 25 bytes and row 2, "Name" = 225 bytes (say no other row longer than 225), then...
POSSIBILITY 1: SQL server always allocate 225 bytes (actual length of data, taken from row 2)
POSSIBILITY 2: SQL server always allocate 255 bytes for all rows as defined in column definition varchar(255)
POSSIBILITY 3: SQL Server allocate 25 bytes for row 1, and 225 bytes for row 2 (actual length of data vary from one row to the next)

My guess is - POSSIBILITY 3 is how SQL server actually behalves. This said, then why we bother specify varchar(n)? We should always specify varchar(MAX). Two reasons I can think of after a bit of digging...[^]

a. You cannot index anything varchar longer than 900 bytes
b. by MAX, you remove column max length validation provided for you at database level.

Am I missing something?

dev

modified 2-Feb-13 21:55pm.

PIEBALDconsult2-Feb-13 5:22

PIEBALDconsult

2-Feb-13 5:22

Allocation of storage is the primary difference, but it won't affect how you work with the data.
The main difference you'll have to deal with is in trailing SPACEs -- CHAR stores them, VARCHAR doesn't. Which can occasionally cause confusion, in that with VARCHAR 'BOB' and 'BOB ' will test equal and LEN report the same length; you'll want to use DATALENGTH for VARCHAR -- this sort of thing bit me again this week. Sigh | :sigh:

devvvy2-Feb-13 23:30

devvvy

2-Feb-13 23:30

I think it's the opposite. CHAR don't store trailing termination char (2 bytes for SQL), varchar does.

I think I found the answer. With VARCHAR(MAX), you can't index the column and also you don't have length checks provided for you by default by database, that's why you should always use VARCHAR(n) as supposed to VARCHAR(MAX)

CHAR(n) when lenght same for all/most rows.

dev

jschell3-Feb-13 6:36