|
I haven't done any tests on record locking, so I may not be the best person to ask. but as I understand it, there are two type of locking (optimistic and pessimistic). Pessimistic only allows one user to use it at a time and will hold the lock. Optimistic, however, only locks the record at the moment it is updating then releases it. I am not sure what type of locking is implemented by the command line instruction above, but if it is optimistic it may not be possible to test it reliably.
Sorry I can't be more helpful on that.
Jack of all trades ~ Master of none.
|
|
|
|
|
1) Thanks - keeps me from posting answers that you already seen 
2) It doesn't have to be the name of the field, but the names do have to match in the parameters. Using "@1" in the query and adding a parameter called "@1" will put that value on that position.
3) Access is a flat-file database, ideal for a single user. Sql Server is a database-server product, ideal for multiple users. It doesn't lock records, but pages.
Never had much concurrency-problems in Sql Server. Had a lot of them in Access.
Bastard Programmer from Hell 
|
|
|
|
|
Thanks again!
I really do want to move to SSE, but all the query strings in the app use '?' for all the parameters, then I have a generic function in a data layer, to which I pass the query string, and an array of parameter values. Then I loop through the array and add the parameters. So i need to change all the query strings in the app (there are lots of them  )
Public Function InsertRecord2(ByVal strInsert As String, params() As String) As Boolean
Using cn As New OleDb.OleDbConnection(cnStr)
Dim da As New OleDb.OleDbDataAdapter
Dim cmd As New OleDb.OleDbCommand(strInsert, cn)
cmd.CommandTimeout = 30
For i As Integer = 0 To params.Length - 1
If params(i) = Nothing Then params(i) = ""
cmd.Parameters.AddWithValue("@" & i.ToString, params(i))
Next
My other concern is that the company uses Small Business Server, which I understand also uses SSE, and I'm terrified I'm going to mess up the server when I install another instance of SSE....
As it is I have installed SSE on my pc, and managed to mess that up by installing access using Windows Authentication, and now I can't enable sa even after changing the authentication to SQL Server and Windows Authentication Mode - But that's another issue
|
|
|
|
|
Richard.Berry100 wrote: Then I loop through the array and add the parameters. So i need to change all the query strings in the app (there are lots of them )
Seems you already have the code to add parameters. How much could you change using "search and replace" over, say, the weekend?
Richard.Berry100 wrote: My other concern is that the company uses Small Business Server, which I understand also uses SSE, and I'm terrified I'm going to mess up the server when I install another instance of SSE....
..adding another instance of Sql Server would make the entire PC somewhat slower. Adding another server would be costly too.
Is at a large database? How many tables are we talking?
Bastard Programmer from Hell
|
|
|
|
|
Quote: Seems you already have the code to add parameters. How much could you change using "search and replace" over, say, the weekend? Smile |
Busy doing this now - Its a long weekend...
Quote: ..adding another instance of Sql Server would make the entire PC somewhat slower. Adding another server would be costly too.
Is at a large database? How many tables are we talking?
There are about 20 tables, some of the tables have about 4000 records (stock_qty)
In a month they added about 4000 rows to the Sales Order Detail table, each of which has a correspronding entry in a Stock History table
The Access db is about 4MB
|
|
|
|
|
Richard.Berry100 wrote: Busy doing this now - Its a long weekend...
Aight, good luck.
Richard.Berry100 wrote: There are about 20 tables, some of the tables have about 4000 records (stock_qty)
In a month they added about 4000 rows to the Sales Order Detail table, each of which has a correspronding entry in a Stock History table
The Access db is about 4MB
Instead of installing yet another Sql Server, you might also be able to put your tables on a database in their (existing) server. That would at least keep administration simpler (one server to manage is easier than updating two).
Bastard Programmer from Hell
|
|
|
|
|
Thanks - will consider installing on same server, but I'd get someone who knows a bit more about servers to assist!
I was leaning towards them getting another PC on the network and put and instance of SSE (with different instance name to the default) on that... Perhaps a performance loss but less risk
|
|
|
|
|
Richard.Berry100 wrote: I'd get someone who knows a bit more about servers to assist!
Always a good idea. And do make a backup
Richard.Berry100 wrote: I was leaning towards them getting another PC on the network and put and instance of SSE (with different instance name to the default) on that... Perhaps a performance loss but less risk
Both have some advantages and disadvantages; if there's a spare PC in the network, then that would be ideal; no risk of contaminating other data, a nice dedicated server (meaning traffic won't slow down other apps requesting data from another db on the same machine), and you'd be able to have all settings as required for your app.
Then again, machines are expensive. If you move to an existing server, you probably have to use an existing account to connect, check the collation of the server, all kinds of things that cost time.
Bastard Programmer from Hell
|
|
|
|
|
Thanks for all the advice Eddy.
If the app works properly, I'm sure they wouldn't mind investing in a mid range pc - and I guess the performance would be a bit better than with Access db as well.
|
|
|
|
|
My pleasure. And yes, performance would increase - most notable change will be if you have complex queries; since the database is on a dedicated machine, the local machine won't have to do this processing anymore
|
|
|
|
|
I created two tables 1) Attendance 2) Leave
1) Attendance table structure
EmpID int
Att_Date Datetime
2) Leave table Structure
EmpID int
LeaveID int
LeaveFrom Datetime
LeaveTo Datetime
While taking the Attendance i want to check whether this employee taken any leave on that day(Attendance day). How can i write a sql for this.
Eg: Attendance Date: 30/03/2012
Leave taken this Employee is
02/03/2012 - 05/03/2012
28/03/2012 - 31/03/2012
How can i write a sql to check this without any cursor.
|
|
|
|
|
I'm not 100% sure what you're after, I'll assume you want to find any records in the "leave" table for the attendance table.
SELECT a1.EmpID, COUNT(l1.LeaveID) as LeaveRecords FROM
Attendance a1 LEFT JOIN
Leave l1 ON l1.EmpID = a1.EmpID AND a1.Att_Date BETWEEN l1.LeaveFrom AND l1.LeaveTo
GROUP BY a1.EmpID
Does that suit your needs? The above would return a list of emp attendances and the no of leave records for that attendance date.
|
|
|
|
|
|
It is possible to compile a SQL script via a command line?
Everything makes sense in someone's mind
|
|
|
|
|
You don't compile SQL.
You can execute SQL statements or a file of statements depending on the database system.
For SQL Server, see sqlcmd
C:>sqlcmd /?
Microsoft (R) SQL Server Command Line Tool
Version 10.50.2500.0 NT x64
Copyright (c) Microsoft Corporation. All rights reserved.
usage: Sqlcmd [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-N Encrypt Connection][-C Trust Server Certificate]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w screen width]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-c cmdend] [-L[c] list servers[clean output]]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-m errorlevel] [-V severitylevel] [-W remove trailing spaces]
[-u unicode output] [-r[0|1] msgs to stderr]
[-i inputfile] [-o outputfile] [-z new password]
[-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
[-k[1|2] remove[replace] control characters]
[-y variable length type display width]
[-Y fixed length type display width]
[-p[1] print statistics[colon format]]
[-R use client regional setting]
[-b On error batch abort]
[-v var = "value"...] [-A dedicated admin connection]
[-X[1] disable commands, startup script, enviroment variables [and exit]]
[-x disable variable substitution]
[-? show syntax summary]
|
|
|
|
|
I guess I meant Parse. In SSMS there's a parse option. Can this be done on a command line?
Everything makes sense in someone's mind
|
|
|
|
|
Not that I know of without executing it. If SQL Server, maybe check through the sqlcmd options.
|
|
|
|
|
Do you think of "prepare"? In the .Net world, I use the prepare statement when I want to run the query repeatedly with different parameters. I do not know if that exists in the sql command line.
|
|
|
|
|
Oh yeah, there's that, I never use it so I didn't think of it.
A console app could be written to do it.
|
|
|
|
|
I have a VB.net app on about 10pc's that access an Access 2007 database on a server to control stock. The stock quantities are getting all messed up, and I cant find out why. I tried setting record locking from the Office button > Advanced Options > Record Locking (Default Record locking = Edited Record) but this seems to be more of a generic kind of setting, and not specific to a particular database - I.e. you can set this without opening a database. How do I ensure that a record is locked when executing a query?
|
|
|
|
|
Could you post a code snipet of how you are updating the table ?
For example, a statement like the following would be kind of hard to corrupt.
update Inventory
set qty_on_hand = qty_on_hand - qty_sold
where item_code = "ABC123"
Is it possible that you have two operators who say, "Oh yeah, we have 3 on hand"
and each one tries to confirm an order for 2 items? This would leave you with a
qty_on_hand = -1.
The way around this would be to build a transaction that makes a request for 2 items to be sold and if it can't then rollback the request. (or create a back order).
Something like:
begin transaction
update Inventory
set qty_on_hand = qty_on_hand - qty_sold
where item_code = "ABC123"
if (select qty_on_hand from Inventory where item_code = "ABC123") < 0 then
rollback
Message to user: "Unable to complete transaction. Not enough qty on hand."
else
commit
End if
|
|
|
|
|
Hi David
What you describe above is pretty much exactly the situation, except...
A Sales Order typically has about 20 lines (I display the lines in a datagrid, they fill in the qty despatched for each item, and click confirm despatch. Two users can definitely try to despatch the same item at the same time.
Then it gets worse. After they confirm the despatch...
a) I update the Sales Order Header table - Status changes from Awaiting Despatch to Despatched
b) For each row in the datagrid:
i) See how much stock there is of the item in the stock table(Select query)
ii) Update the Stock Table with new Qty (Update Query)
ii) Write the qty despatched to the Sales Order Detail Table (Update Query)
iii) Write a transaction to a stock History Table (Insert Query)
iv) Write batch information to a Batch table
Both your suggestions may help (i.e. instead of first running a select query to get the stock qty, calculating the new qty in the app, then doing a second query to update the table with the new value, to rather do the calculation in the query)
This is a bit of a problem because of the way I store the stock qty - they have about 1000 stock items, that can exist in any of 10 warehouses. I only keep items that have stock in a warehouse in this table - kind of hard to explain here - perhaps i need to load every stock item in every warehouse in this table.
Second I like the idea of a transaction rollback, but I need to rollback all the other table writes and updates as well? Is this possible.
I can post code, but its long and would be much more than a snippet - can try to shorten it if this would help?
|
|
|
|
|
Or
Change your stock table to have an allocated field and increment/decrement that as the request is processed or cancelled/completed. It will depend on your business process, if the sales process takes minutes or a longer period this may suit.
You have to deal with crash edge cases where you have orphaned allocated values.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have an XML document with parent and 3 different chile elements.
Does anyone know how to get this into SQL please?
Might be a silly question but it's the first time for me. 
|
|
|
|
|
Probably depends on what you really mean.
But some possible solutions.
1. MS SQL Server (TSQL)can process XML. So write some TSQL to do it.
2. Write some C# (or Java, C++, perl, etc) which parses the XML and outputs in to import files. Then use the inport feature of the database to import it.
Myself I would generally always chose 2 because I am more familar with doing it with something besides TSQL and because then I don't need to worry about impacting database performance while it runs.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
