EXEC Statements in a sql script

Thanks for the response. Didn't help however can tell you what is
happening now. I do an alter to create a new column. Put it in a
transaction and commit it. Then the next transaction I do an update to
the newly created column and it complains it can't find the column. If
I run all this manually it's fine. Could it be an issue with the speed
that the script is running that SQL Server, even though I committed
between alter and update, still is not done creating the tables or
something?
Thanks.
JRJR (jriker1@.yahoo.com) writes:
> Thanks for the response. Didn't help however can tell you what is
> happening now. I do an alter to create a new column. Put it in a
> transaction and commit it. Then the next transaction I do an update to
> the newly created column and it complains it can't find the column. If
> I run all this manually it's fine. Could it be an issue with the speed
> that the script is running that SQL Server, even though I committed
> between alter and update, still is not done creating the tables or
> something?
No, speed has nothing to do with it.
If you do:
ALTER TABLE tbl ADD newcol int
UPDATE tbl
SET newcol = 91
this will fail, because when SQL Server compiles this batch, it sees
that you references a column that does not exist in tbl, and that is
an error. SQL Server has deferred name resolution, so that if a table
does not exist when the batch is compiled, SQL Server is silent in hope
that the table is created. There is, thankfully, not deferred name
resolution for column names. It is bad as it is.
There are a couple of ways to skin the cat. The best is probably
to wrap the UPDATE into EXEC(), so that it will not be compiled
until after the ALTER TABLE statement has been executed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 27 Mar 2006 22:42:08 +0000 (UTC), Erland Sommarskog wrote:
(snip)
>There are a couple of ways to skin the cat. The best is probably
>to wrap the UPDATE into EXEC(), so that it will not be compiled
>until after the ALTER TABLE statement has been executed.
Hi Erland,
In a stored procedure: yes.
But in a SQL script, just adding a "go" between the ALTER TABLE and the
UPDATE is enough.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
> (snip)
> Hi Erland,
> In a stored procedure: yes.
> But in a SQL script, just adding a "go" between the ALTER TABLE and the
> UPDATE is enough.
I didn't mention that possibility because the hour was late, and there are
some caveates with it. Say that you do:
BEGIN TRANSACTION
-- Do something
go
-- Do something more
go
-- Yet something more
COMMIT TRANSACTION
Now, if there is an error on the line of the kind that aborts the batch,
the transaction will be rolled back, but the remaining batches will be
executed. You will get an error when you reach COMMIT, but then the damage
may already been done.
Of course, in this particular case if ALTER TABLE fails, the UPDATE command
will also fail. However, there can be other commands in other batches that
still can be carried out when they shouldn't.
One way to handles this is to open every batch with IF @.@.trancount > 0,
but I think would be prefer to keep all in one batch, and interleave
problematic statments in dynamic SQL. Not the least on SQL 2005, as I
then can have single CATCH handler at the end. (But note that if you
have:
BEGIN TRY
UPDATE tbl SET missingcolumn = <somevalue>
END TRY
BEGIN CATCH
-- handle error
END CATCH
that the CATCH handler will not be reached, as the error is a compilation
error.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Tue, 28 Mar 2006 10:27:33 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
>I didn't mention that possibility because the hour was late, and there are
>some caveates with it. Say that you do:
(snip)
Hi Erland,
Good point. Thanks for adding this warning!
Hugo Kornelis, SQL Server MVPsql

Exclusive Insert Lock on a Table

Hello All!

I want to perform 4 or 5 statements as a transaction but I need to make sure that during this complete transaction no one else inserts or deletes records from a table named SomeTable.

So how can I lock MyTable at the beggining of the transaction so that during my transaction no one else can insert or delete anything in table SomeTable?

Thanks!

David

You can open an transaction explicitly so other transactions are placed in a queue. Use BEGIN TRAN /COMMIT TRAN. Read up books on line for more info on transactions.

|||

Yup, I could read about this as you suggest, but it's certainly pretty complex so I was hoping someone experienced and knowleadgable would give me the answer.

Your post but it doesn't answer my question. I know how to create an explicit transaction.

My question is how do I lock a table during a transaction so that no one else inserts or deletes records from it.

I don't think that by just creating an explicit transaction and reading a row from MyTable this will lock the complete table.

|||

neutrino:

I don't think that by just creating an explicit transaction and reading a row from MyTable this will lock the complete table.

Yes it does. And thats all you have to do. Try this scenario:

(1) In a query analyzer window run this script

BEGIN TRAN

INSERT INTO TheTable ... <complete the rest of the INSERT>

(2) Open another query analyzer and do a SELECT * From TheTable. You will see that your query will be in "suspended" status waiting for the Insert to finish since you opened a Transaction explicitly.

(3) Now go back to the first window and run this:

Commit

(4) Check the second window and you will see results for your SELECT *...

Basically your BEGIN TRAN has opened a transaction --> locked the table for any other commited transactions (Even reads). As soon as you commit the transaction the lock on the table is released and others can read from the table. You can by pass this and do a dirty read by using NOLOCK hint. Not always suggested unless your business requirements allow you to.

|||

Thanks. This tells me how to lock a table by creating a transaction and doing and insert statement. However, in my scenario I need to lock the table innitially before doing any inserts to it and I want it to remain locked until the transaction ends (even if I don't do any inserts). I don't want any other transaction to be able to insert any rows until my transaction finishes.

I think that what I need is to set the transaction isolation level to SERIALIZABLE.

I found this: http://msdn2.microsoft.com/en-us/library/ms173763.aspx

I'll be trying it later and will post results.

David

|||

I do find your query analyzer excercise extremelly useful. I will use it to test my locking 'theories". Thanks!

|||

ndinakar:

neutrino:

I don't think that by just creating an explicit transaction and reading a row from MyTable this will lock the complete table.

Yes it does. And thats all you have to do. Try this scenario:

(1) In a query analyzer window run this script

BEGIN TRAN

INSERT INTO TheTable ... <complete the rest of the INSERT>

(2) Open another query analyzer and do a SELECT * From TheTable. You will see that your query will be in "suspended" status waiting for the Insert to finish since you opened a Transaction explicitly.

(3) Now go back to the first window and run this:

Commit

(4) Check the second window and you will see results for your SELECT *...

Basically your BEGIN TRAN has opened a transaction --> locked the table for any other commited transactions (Even reads). As soon as you commit the transaction the lock on the table is released and others can read from the table. You can by pass this and do a dirty read by using NOLOCK hint. Not always suggested unless your business requirements allow you to.

Actually, that doesn't lock the whole table. It locks a portion of the table, but your SELECT requires access to the entire table (including the locked portion), so it has to wait. If you have the table indexed, and the SELECT can use the index to determine that it doesn't need the locked portion then it won't delay the SELECT. In addition, a second INSERT should complete without being delayed.

To the original poster, what you are asking for is rather uncommon, and you are best to try and avoid doing what you are asking to do. Perhaps you need to rethink why you want the table locked, and what you are trying to accomplish by doing so. Usually there is a much better way of achieving that.

|||

Motley:

To the original poster, what you are asking for is rather uncommon, and you are best to try and avoid doing what you are asking to do. Perhaps you need to rethink why you want the table locked, and what you are trying to accomplish by doing so. Usually there is a much better way of achieving that.

You are totally right. This was actually what I did. I re-thought the process and found a better way that doesn't require the table lock.

But anyways it was a great learning experience.

Thanks all for your support.

David

|||

ndinakar:

neutrino:

I don't think that by just creating an explicit transaction and reading a row from MyTable this will lock the complete table.

Yes it does. And thats all you have to do. Try this scenario:

It actually doesn't but the rest of your post was really helpful. Thank you.

Excel to SQL 2000

I have an excel spread sheet that contains a header and three different
sections (delineated by text statements on separate lines between the data).
Header One Some Data by Name
$23,598.00 $23,598.00 $23,598.00 $23,598.00
$23,598.00 $23,598.00 $23,598.00 $23,598.00
Header Two Some Data by Name
$23,598.00 $23,598.00 $23,598.00 $23,598.00
$23,598.00 $23,598.00 $23,598.00 $23,598.00
Header Three Some Data by Name
$23,598.00 $23,598.00 $23,598.00 $23,598.00
$23,598.00 $23,598.00 $23,598.00 $23,598.00
I need to import the data from each section and import it into a different
SQL 2000 file for each section of the excel spreadsheet. I also need to
perform some data manipulation during the import.
I am a beginner so I would appreciate your advice on how to do this as well
as article, samples, links etc. that I might be able to learn from. Thank
you.Hi Mike
Check out sqldts.com for lots of information on how to do things using DTS!
The safest option would be if you can make each section a named range then
they could be imported separately. Another option, if all sections have the
same format would be to import the data into a staging table (with an
identity column) and then split it off from there (although I don't know if
the row order will be guaranteed!!!)
John
"Mike" wrote:

> I have an excel spread sheet that contains a header and three different
> sections (delineated by text statements on separate lines between the data
).
> Header One Some Data by Name
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> Header Two Some Data by Name
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> Header Three Some Data by Name
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> I need to import the data from each section and import it into a different
> SQL 2000 file for each section of the excel spreadsheet. I also need to
> perform some data manipulation during the import.
> I am a beginner so I would appreciate your advice on how to do this as wel
l
> as article, samples, links etc. that I might be able to learn from. Thank
> you.
>
>

Excel to SQL 2000

I have an excel spread sheet that contains a header and three different
sections (delineated by text statements on separate lines between the data).
Header One Some Data by Name
$23,598.00 $23,598.00 $23,598.00 $23,598.00
$23,598.00 $23,598.00 $23,598.00 $23,598.00
Header Two Some Data by Name
$23,598.00 $23,598.00 $23,598.00 $23,598.00
$23,598.00 $23,598.00 $23,598.00 $23,598.00
Header Three Some Data by Name
$23,598.00 $23,598.00 $23,598.00 $23,598.00
$23,598.00 $23,598.00 $23,598.00 $23,598.00
I need to import the data from each section and import it into a different
SQL 2000 file for each section of the excel spreadsheet. I also need to
perform some data manipulation during the import.
I am a beginner so I would appreciate your advice on how to do this as well
as article, samples, links etc. that I might be able to learn from. Thank
you.Hi Mike
Check out sqldts.com for lots of information on how to do things using DTS!
The safest option would be if you can make each section a named range then
they could be imported separately. Another option, if all sections have the
same format would be to import the data into a staging table (with an
identity column) and then split it off from there (although I don't know if
the row order will be guaranteed!!!)
John
"Mike" wrote:
> I have an excel spread sheet that contains a header and three different
> sections (delineated by text statements on separate lines between the data).
> Header One Some Data by Name
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> Header Two Some Data by Name
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> Header Three Some Data by Name
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> $23,598.00 $23,598.00 $23,598.00 $23,598.00
> I need to import the data from each section and import it into a different
> SQL 2000 file for each section of the excel spreadsheet. I also need to
> perform some data manipulation during the import.
> I am a beginner so I would appreciate your advice on how to do this as well
> as article, samples, links etc. that I might be able to learn from. Thank
> you.
>
>