Alter table command to add a column 1

[vionca]

Hi there,

I've tried to execute the following comand during a data transformation services job and intended to add a new column named ComanyName to an existing table named CompanyTotals_tbl with a value of "ACM" in that field:

ALTER TABLE CompanyTotals_tbl ADD companyName AS 'ACM'

In that same DTS package - I later try to update a line I just inserted with a value of the next CompanyName ('BBS' for example) but receive the following error message:

---------------------------
Package Error
---------------------------
Error Source : Microsoft OLE DB Provider for SQL Server

Error Description : Deferred prepare could not be completed.
Statement(s) could not be prepared.
Invalid object name 'companyTotals_tbl'.

---------------------------
OK
---------------------------



Is this happening because of the way I wrote my alter table command?

Thanks for all your help,
vionca

 

[nigelrivett]

the statement should be something like

ALTER TABLE CompanyTotals_tbl ADD companyName varchar(3) default 'ACM'

If it's not that then
Is this within an sql task?
The query plan will be generated when the task is executed and as tables are meant to be static the insert will be compiled on the structure before the alter table.

You can try putting a "go" after the alter table - better putting thye alter in a different step.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[vionca]

That line did it. I hope you might be able to help me a bit further, nigelrivett. I need some expertise.

I have a bunch of databases (each containing data for all of my companies separate divisions). We'll use the following database names for this example:

ABC
DEF
GHI

which all contain data specific to their division. Each of these databases has a table named customer_tbl and a column named Over30days which I would like to run a SUM() function on. I would then like to take the results of that SUM() as well as the name of the division(ABC,DEF,GHI) and insert a record into another table in another database.

So I would have a record in database AllDivisions and table DivisionTotals_tbl which might contain the following:

DivisionName Over30Days
ABC 2,000,000.00
DEF 1,300,000.00
GHI 900,000.00

This would need to be done for all divions (approximately 23).
I intended to do this all via DTS and a nightly job. Does that sound like the best route?

Thanks for your help,
vionca

 

[nigelrivett]

I would do it via a stored proc.
Have a table with all the database names (or you can get them from sysdatabases and check that there is a customer_tbl in the database)..

declare @db varchar(128) ,
@sql varchar(2000)
select @db = ''
while @db < (select max(name) from DataBaseNames)
begin
select @db = min(name) from DataBaseNames where name > @db
select @sql = 'insert DivisionTotals_tbl select @db, sum(Over30days) from ' + @db + '.dbo.customer_tbl'
exec(@sql)
end


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.