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 SSIS package stored in remote SQL server from a client machine.

I have written a VB component to execute a SSIS package stored in SQL server.

I am using the Microsoft.sqlserver.dts.dtsclient, dtsconnection/dtscommand

and executereader and everything works perfect on the local machine. This is descibed in a msdn site .

But my customer have a remote SQL server and want no extra BI clients/SQL server clients etc on the client machine, only an ordinary windows client and network connection to the SQL server.

My question is: Can you still use dtsclient or do you have to do in some other way?

rose-marie

It depends where your VB component is going to be running. You will need SSIS installed on the same machine.

-Jamie

|||

The whole idea was to run the VB component in a client machine without any extra i.e without SSIS.

I understand that this is not possible, is it possible to achieve this in another way?

rose-marie

|||

Can you not run the component somewhere else?

|||

No, in this case there is a demand on a stand-alone windows application

without any dependencies other than oledb connections to SQL server.

Otherwise I would have liked a web service in the server machine or something like that, but...

rose-marie

Execute Script component after 2 sequence finished with sucess

Dear Friends,

In the control flow, I have more than one sequence containers, and I have a script component that I want to be executed only when of 2 last sequence finished with sucess... these 2 sequences does not have any relation with each other...

Regards!

If you want the script task to be executed after both sequences complete successfully, add a precedence constraint connecting each sequence container directly to the script task. If you want the script task to be executed when either of the sequence containers complete successfully, do the same, but set the LogicalAnd property of the precedence constraints to False.|||

Dear jwelch,

I already tried it but didnt work...

Check the image on the image in my blog:

http://pedrocgd.blogspot.com/2007/06/ssis-temporary-image-to-msdn-forum.html

Thanks!

|||The dotted lines for the precedence constraints show that you have set it to be an OR condition. double click on one of those constraints and set it to AND which means both have to succeed before the next task is executed.|||

I made a mistake... I already had this works and I was confusing!!!

Thanks both!!

Execute phase Memory Error => dtsx finished: Canceled

Hi,
I have a data flow component that retrieves records from a table. There is about 3 million records. I do sorts on the records, then use a merge join transformation to join that sorted data with the dimension data. However, when I pump 10,000 records through this transformation it works fine. When I try 3 million I get this error:
DTS.Pipeline: Thread "WorkThread1" has exited with error code 0x8007000E. I think this is a memory error.

I have 1000 MB of memory 30 GB of hard drive space. I am also running this on a virtual server.

Option 1:
Don't use merge join transformations and use Lookup's instead?
Option 2:
Stop using virtual server and put it on a real box and and beef up the memory and hard drive.

What do you suggest?

This could well be related to Sort component. There is a known issue with Sort. Please have a look at the following 2 posts. May help you.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=21620

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=18421

In these entries there are links to Jamie's blog along with 3rd party providers who has got their own sort component which we can use within SSIS.

Thanks
Sutha

|||Thanks Sutha,

I downloaded the sort compoent from ivolva.com. Very, very easy to download and install. However, that component is cancelling my package. Any ideas? I sent some feed back to Oleg, I hope I hear back.|||I am not sure why it is happening. What message are you getting on the progress page? Ash seems to have tried it out too.

Thanks
Sutha|||

In the Output dialog box the error message reads:

SSIS package "LoadFct_Trans 1.dtsx" finished: Canceled.
Then everything stops.

|||I'll contact you and try to diagnose this problem.

Thanks
Oleg|||I would try to go with the Lookup transform... The "problem" with the sort is that it's asynchronous... So it has to load all the records, sort it and send it out again... You don't have that problem with the lookup... You might run into other problems (i.e. the lookup stores everything in memory by default, you can switch that of but then it might be slow...), but I think it's a good alternative to try...|||I've downloaded the sort util as well but it crashes VS 2005 as soon as I try to add it in the toolbox. Has anyone else experienced this?|||

I have the same problem... could you post what you did to fix this?

Execute phase Memory Error

Hi,
I have a data flow component that retrieves records from a table. There is about 3 million records. I do sorts on the records, then use a merge join transformation to join that sorted data with the dimension data. However, when I pump 10,000 records through this transformation it works fine. When I try 3 million I get this error:
DTS.Pipeline: Thread "WorkThread1" has exited with error code 0x8007000E. I think this is a memory error.

I have 1000 MB of memory 30 GB of hard drive space. I am also running this on a virtual server.

Option 1:
Don't use merge join transformations and use Lookup's instead?
Option 2:
Stop using virtual server and put it on a real box and and beef up the memory and hard drive.

What do you suggest?

This could well be related to Sort component. There is a known issue with Sort. Please have a look at the following 2 posts. May help you.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=21620

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=18421

In these entries there are links to Jamie's blog along with 3rd party providers who has got their own sort component which we can use within SSIS.

Thanks
Sutha

|||Thanks Sutha,

I downloaded the sort compoent from ivolva.com. Very, very easy to download and install. However, that component is cancelling my package. Any ideas? I sent some feed back to Oleg, I hope I hear back.|||I am not sure why it is happening. What message are you getting on the progress page? Ash seems to have tried it out too.

Thanks
Sutha|||

In the Output dialog box the error message reads:

SSIS package "LoadFct_Trans 1.dtsx" finished: Canceled.
Then everything stops.

|||I'll contact you and try to diagnose this problem.

Thanks
Oleg|||I would try to go with the Lookup transform... The "problem" with the sort is that it's asynchronous... So it has to load all the records, sort it and send it out again... You don't have that problem with the lookup... You might run into other problems (i.e. the lookup stores everything in memory by default, you can switch that of but then it might be slow...), but I think it's a good alternative to try...|||I've downloaded the sort util as well but it crashes VS 2005 as soon as I try to add it in the toolbox. Has anyone else experienced this?|||

I have the same problem... could you post what you did to fix this?