Hi, I'm trying to add a modified datetime and userid to all 72 tables in my
SQL 2000 database. I have the script to do one table, and a cursor, but it
won't run across all tables. Any help would be appreciated. Thanks...
DECLARE @tName varchar(40)
DECLARE C1 CURSOR FOR
select name from sysobjects where type = 'U'
OPEN C1
FETCH NEXT FROM C1 INTO @tName
-- Check @@FETCH_STATUS to see if there are any more rows to fetch
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE @tName ADD
ModifiedDT datetime NULL,
ModifiedUserID int NULL
GO
COMMIT
FETCH NEXT FROM C1
END
CLOSE C1
DEALLOCATE C1
GO 3 10845
Hi
As this is not production code then you may want to check out the
undocumented sp_MSforeachtable http://groups.google.co.uk/groups?hl...%40tkmsftngp03 http://groups.google.co.uk/groups?hl...an%40127.0.0.1
John
"Paul" <ps******@uecomm.com.au> wrote in message
news:10***************@proxy.uecomm.net.au... Hi, I'm trying to add a modified datetime and userid to all 72 tables in
my SQL 2000 database. I have the script to do one table, and a cursor, but it won't run across all tables. Any help would be appreciated. Thanks...
DECLARE @tName varchar(40) DECLARE C1 CURSOR FOR select name from sysobjects where type = 'U' OPEN C1 FETCH NEXT FROM C1 INTO @tName -- Check @@FETCH_STATUS to see if there are any more rows to fetch WHILE @@FETCH_STATUS = 0 BEGIN -- This is executed as long as the previous fetch succeeds BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION ALTER TABLE @tName ADD ModifiedDT datetime NULL, ModifiedUserID int NULL GO COMMIT FETCH NEXT FROM C1 END CLOSE C1 DEALLOCATE C1 GO
Paul (ps******@uecomm.com.au) writes: Hi, I'm trying to add a modified datetime and userid to all 72 tables in my SQL 2000 database. I have the script to do one table, and a cursor, but it won't run across all tables. Any help would be appreciated.
There are a number of errors in your script:
DECLARE @tName varchar(40) DECLARE C1 CURSOR FOR
While not an error, I recommend that you make your cursors INSENSITIVE
as a matter of routine. The default keyset-driven cursors can sometimes
give nasty surprises.
select name from sysobjects where type = 'U' OPEN C1 FETCH NEXT FROM C1 INTO @tName -- Check @@FETCH_STATUS to see if there are any more rows to fetch WHILE @@FETCH_STATUS = 0
I recommend that you write cursor loops as
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO @var1, @var2....
IF @@fetch_status <> 0
BREAK
-- Real job follows here.
END
DEALLOCATE cur
By only having one FETCH statement you make your code safer, because it's
easy to change the SELECT statement, and the new column to the first
FETCH, but forget the second, which may be the screens below.
BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT
There is no point in executing the SET statements in the loop, and
there is no point to make this a transaction. Not that it is wrong
either.
BEGIN TRANSACTION ALTER TABLE @tName ADD ModifiedDT datetime NULL, ModifiedUserID int NULL GO
Here are two serious flaws: ALTER TABLE does not accept a variable.
You need to use dynamic SQL for this. (Or sp_MSforeachtable.)
And the GO there is completely out of place. GO is not an SQL command,
but an instruction to the query tool to separate the commands into
different batches. Thus, this batch will fail with a compilation
error, because the BEGIN after WHILE does not have an END.
FETCH NEXT FROM C1
And if you thought what I said about FETCH above was silly, look here!
Here you don't insert into a variable, but produce a result set.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Thanks John, I'll check it out
"John Bell" <jb************@hotmail.com> wrote in message
news:3f*********************@reading.news.pipex.ne t... Hi
As this is not production code then you may want to check out the undocumented sp_MSforeachtable
http://groups.google.co.uk/groups?hl...%40tkmsftngp03 http://groups.google.co.uk/groups?hl...an%40127.0.0.1 John
"Paul" <ps******@uecomm.com.au> wrote in message news:10***************@proxy.uecomm.net.au... Hi, I'm trying to add a modified datetime and userid to all 72 tables in my SQL 2000 database. I have the script to do one table, and a cursor, but
it won't run across all tables. Any help would be appreciated. Thanks...
DECLARE @tName varchar(40) DECLARE C1 CURSOR FOR select name from sysobjects where type = 'U' OPEN C1 FETCH NEXT FROM C1 INTO @tName -- Check @@FETCH_STATUS to see if there are any more rows to fetch WHILE @@FETCH_STATUS = 0 BEGIN -- This is executed as long as the previous fetch succeeds BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION ALTER TABLE @tName ADD ModifiedDT datetime NULL, ModifiedUserID int NULL GO COMMIT FETCH NEXT FROM C1 END CLOSE C1 DEALLOCATE C1 GO
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: MuZZy |
last post by:
Hi,
I just wonder if someone can help me with this:
I need to create a sql script which will run when user installs/upgrades my app.
User may already have the database and tables tructure setup...
|
by: Paul |
last post by:
Hi
I have a table that currently has 466 columns and about 700,000
records. Adding a new DEFAULT column to this table takes a long time.
It it a lot faster to recreate the table with the new...
|
by: Bobbak |
last post by:
Hello All,
I have these tables (lets call it ‘EmpCalls', ‘EmpOrders', and
‘Stats') that each contain the list of EmployeeIDs, I want to be able
to create a Module in which I could call in my VB...
|
by: Luis Esteban Valencia |
last post by:
Once a user clicks "add new row", a new row is created in my Datagrid. The
datasource is huge so they have to scroll to the bottom of the page to edit
the data in the new row. I have come across...
|
by: Art |
last post by:
Hi everyone
I was hoping someone might be able to help me with this. I'm just starting to try to work with MS Access tables through VB.net. In Access I can take an existing table and add a new...
| |
by: Geoff Jones |
last post by:
Hi
Can anybody help me with the following, hopefully simple, question?
I have a table which I've connected to a dataset. I wish to add a new column
to the beginning of the table
and to fill...
|
by: DotNetJunkies User |
last post by:
I'm trying create a single page app that allows the user to enter text (Serial) via a form and submit and populate that value into a datagrid via a datatable.
It works for ONE value but each time...
|
by: Bernie Yaeger |
last post by:
I have a need to add a primary key to a dataset/datatable. How can this be
done using a standard oledb data provider?
Tx for any help.
|
by: JMO |
last post by:
I can import a csv file with no problem. I can also add columns to the
datagrid upon import. I want to be able to start importing at the 3rd
row. This will pick up the headers necessary for the...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |