Database

PIEBALDconsult29-Mar-12 12:12

29-Mar-12 12:12

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]

Kevin Marois29-Mar-12 12:17

Kevin Marois

29-Mar-12 12:17

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

PIEBALDconsult29-Mar-12 14:53

29-Mar-12 14:53

Not that I know of without executing it. If SQL Server, maybe check through the sqlcmd options.

Bernhard Hiller29-Mar-12 22:36

Bernhard Hiller

29-Mar-12 22:36

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.

PIEBALDconsult30-Mar-12 3:08

How do you lock records in Access 2007

30-Mar-12 3:08

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.

Richard.Berry10029-Mar-12 9:34

Richard.Berry100

29-Mar-12 9:34

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?

Re: How do you lock records in Access 2007

David Mujica29-Mar-12 9:51

David Mujica

29-Mar-12 9:51

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

Re: How do you lock records in Access 2007

Richard.Berry10029-Mar-12 10:18

Richard.Berry100

29-Mar-12 10:18

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?

Re: How do you lock records in Access 2007

Mycroft Holmes29-Mar-12 13:09

Mycroft Holmes

29-Mar-12 13:09

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

XML data import to SQL

milenalukic29-Mar-12 6:21

milenalukic

29-Mar-12 6:21

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. D'Oh! | :doh:

Re: XML data import to SQL

jschell29-Mar-12 8:08

jschell

29-Mar-12 8:08

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.

Re: XML data import to SQL

PIEBALDconsult29-Mar-12 12:24

Re: XML data import to SQL

29-Mar-12 12:24

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.

milenalukic30-Mar-12 11:46

milenalukic

30-Mar-12 11:46

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.

New User.

Abhishek kumar12129-Mar-12 4:24

Abhishek kumar121

29-Mar-12 4:24

Hi,
I have listen that in code project there is ready code of asp.net. Please guide me how to find that.

Re: New User.

Simon_Whale29-Mar-12 4:46

Simon_Whale

29-Mar-12 4:46

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

Where .. IN ...

eyalle27-Mar-12 20:47

eyalle

27-Mar-12 20:47

Hello all
I have this query

SQL

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

SQL

select * 
from tableName 
where ColumnId in (1,3)

please advice
Thank's

Vipin_Arora27-Mar-12 20:57

Vipin_Arora

27-Mar-12 20:57

Hi,

Try with two diffrenet integer parameters if your columnID is integer. its working fine 4 me..

Happy Coding... Smile | :)

Mycroft Holmes27-Mar-12 23:14

Mycroft Holmes

27-Mar-12 23:14

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

Blue_Boy27-Mar-12 23:24

Blue_Boy

27-Mar-12 23:24

You can try this:

SQL

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.