|
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.
|
|
|
|
|
If the XML is something like:
<records>
<record>
<id>1</id>
<name>foo</name>
</record>
<record>
<id>2</id>
<name>bar</name>
</record>
</records>
and I don't yet a table for it, then my preference is to read the XML into an XmlDocument, iterate the records, iterate the children, cobble up appropriate CREATE TABLE and INSERT statements, execute the CREATE TABLE, then iterate the document again executing the INSERT for each record.
I have a console application to do this.
|
|
|
|
|
The XML data will be put on my ftp server and I need to read the data.
I have created the tables in SQL but now need to write a stored procedure to read the XML. All examples I found only use the 1 table so I still haven't managed to get the data into multiple tables.
Also in the child elements there is a field that include the foreign key [/int] added to it. I suppose I could do a substring once I read the data but for now I'm still stuck.
|
|
|
|
|
Hi,
I have listen that in code project there is ready code of asp.net. Please guide me how to find that.
|
|
|
|
|
ASP.NET forum[^]
ASP.NET Articles[^]
and on the home page you will lists of the latest articles, tips and questions which you can filter to asp.net
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
Hello all
I have this query
DECLARE @a nvarchar(50)
SET @a = '1,3'
select *
from tableName
where CAST(ColumnId as nvarchar(50)) in (@a)
which doesn't work.
i would like it to work like the following query
select *
from tableName
where ColumnId in (1,3)
please advice
Thank's
|
|
|
|
|
Hi,
Try with two diffrenet integer parameters if your columnID is integer. its working fine 4 me..
Happy Coding...
|
|
|
|
|
You are trying to compare a string '1,3' with an integer field that is not going to work.
Either do as Wipin suggested and use 2 variables or change the entire query to dynamic sql
DECLARE @a nvarchar(50)
SET @a = 'select * from tableName where ColumnId in (1,3)'
exec (@a)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You can try this:
DECLARE @a nvarchar(50)
SET @a = '1,3'
declare @tsql as nvarchar(max)
set @tsql = 'select *
from tableName
where CAST(ColumnId as nvarchar(50)) in ('+@a+')'
exec (@tsql)
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
