Execute statement?

Hi,
I have an ADO component in a delphi program that I use to check a database
for structure changes that are needed. This is basically just a long list o
f
SQL statements. In one instance I need to add a new field if it doesn't
exist, then update the field with values from another field. When I execute
the code, the program says it can't find the new field. If I take the updat
e
statement out it runs fine. Is there a way to either have the alter command
execute before the update command runs? If this were query analyzer I'd jus
t
use the 'GO' statement, is there an equivalent statement I could use? Here
is the SQL statements I'm using:
if not exists (SELECT *
FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('MYTABLE')
AND Name = 'NEWFIELD'
)
begin
alter table mytable
add [NEWFIELD] nvarchar(7);
/* this statement below is what is failing */
update mytable set NEWFIELD=OLDFIELD where IsNull(OldField,'') <> '';
end;Two roundtrips, the first one to create the column and the second one to
update if everything was ok. Your application can also call OSQL utility to
execute a batch file.
AMB
"Thread77" wrote:

> Hi,
> I have an ADO component in a delphi program that I use to check a database
> for structure changes that are needed. This is basically just a long list
of
> SQL statements. In one instance I need to add a new field if it doesn't
> exist, then update the field with values from another field. When I execu
te
> the code, the program says it can't find the new field. If I take the upd
ate
> statement out it runs fine. Is there a way to either have the alter comma
nd
> execute before the update command runs? If this were query analyzer I'd j
ust
> use the 'GO' statement, is there an equivalent statement I could use? Her
e
> is the SQL statements I'm using:
> if not exists (SELECT *
> FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('MYTABLE')
> AND Name = 'NEWFIELD'
> )
> begin
> alter table mytable
> add [NEWFIELD] nvarchar(7);
> /* this statement below is what is failing */
> update mytable set NEWFIELD=OLDFIELD where IsNull(OldField,'') <> '';
> end;|||You can just do what ISQL is doing. Break up your batches (on the same
connection) on a GO (you will have to add it to your query) and send two
distinct commands to the server.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Thread77" <Thread77@.discussions.microsoft.com> wrote in message
news:E2FA4078-BEDC-4D9F-BB35-75A9289E6EC0@.microsoft.com...
> Hi,
> I have an ADO component in a delphi program that I use to check a database
> for structure changes that are needed. This is basically just a long list
> of
> SQL statements. In one instance I need to add a new field if it doesn't
> exist, then update the field with values from another field. When I
> execute
> the code, the program says it can't find the new field. If I take the
> update
> statement out it runs fine. Is there a way to either have the alter
> command
> execute before the update command runs? If this were query analyzer I'd
> just
> use the 'GO' statement, is there an equivalent statement I could use?
> Here
> is the SQL statements I'm using:
> if not exists (SELECT *
> FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('MYTABLE')
> AND Name = 'NEWFIELD'
> )
> begin
> alter table mytable
> add [NEWFIELD] nvarchar(7);
> /* this statement below is what is failing */
> update mytable set NEWFIELD=OLDFIELD where IsNull(OldField,'') <> '';
> end;

Execute dynamic generate SQL with length > 8000

I am now writing a stored procedure that will dynamically generate a
trigger base on a specific table structure. The generated trigger
script will have variable lenght, depends on how many columns are
defined inside the specific table.
I plan to generate the CREATE TIRGGER script on-the-fly and execute it,
but I comes with a problem that, if the script generated is longer than
8000, VARCHAR is simply cannot handle it. While the length of the
script is undeterministic, I cannot split the trigger script into a
constant number of VARCHAR variable. May I know if there is any
workaround on it ?
Thx~>I am now writing a stored procedure that will dynamically generate a
> trigger base on a specific table structure. The generated trigger
> script will have variable lenght, depends on how many columns are
> defined inside the specific table.
And this will really exceed 8000 characters? I think you will find that
this will be impossible to manage. Before you go down this route, I
strongly recommend reading http://www.sommarskog.se/dynamic_sql.html, and
consider generating new triggers in your application as opposed to within a
stored procedure.
However note that you can execute longer strings quite simply.
DECLARE @.sql_1 VARCHAR(8000), @.sql_2 VARCHAR(8000)
SET @.sql_1 = '....'
SET @.sql_2 = '....'
EXEC(@.sql1 + @.sql2)|||John Shum (eurostar@.gmail.com) writes:
> I am now writing a stored procedure that will dynamically generate a
> trigger base on a specific table structure. The generated trigger
> script will have variable lenght, depends on how many columns are
> defined inside the specific table.
> I plan to generate the CREATE TIRGGER script on-the-fly and execute it,
> but I comes with a problem that, if the script generated is longer than
> 8000, VARCHAR is simply cannot handle it. While the length of the
> script is undeterministic, I cannot split the trigger script into a
> constant number of VARCHAR variable. May I know if there is any
> workaround on it ?
I don't know what the purpose is, but to me this sounds like something
I would prefer to do in Perl or Visual Basic. Least of all I would
like to do it in T-SQL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp