The way I solved this was to modify the Velocity template that is used to
create the sql script. I added a _${velocityCount} to the reftable and
constraintname variables.
I changed the mssql drop.vm to this
#foreach ($fk in $table.ForeignKeys)
IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND
name='${table.Name}_FK_${velocityCount}')
ALTER TABLE $table.Name DROP CONSTRAINT
${table.Name}_FK_${velocityCount};
#end
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name =
'$table.Name')
BEGIN
DECLARE @reftable_${velocityCount} nvarchar(60),
@constraintname_${velocityCount} nvarchar(60)
DECLARE refcursor CURSOR FOR
select reftables.name tablename, cons.name
constraintname_${velocityCount}
from sysobjects tables,
sysobjects reftables,
sysobjects cons,
sysreferences ref
where tables.id = ref.rkeyid
and cons.id = ref.constid
and reftables.id = ref.fkeyid
and tables.name = '$table.Name'
OPEN refcursor
FETCH NEXT from refcursor into @reftable_${velocityCount},
@constraintname_${velocityCount}
while @@FETCH_STATUS = 0
BEGIN
exec ('alter table '+@reftable_${velocityCount}+' drop constraint
'+@constraintname_${velocityCount})
FETCH NEXT from refcursor into @reftable_${velocityCount},
@constraintname_${velocityCount}
END
CLOSE refcursor
DEALLOCATE refcursor
DROP TABLE $table.Name
END
This would probably be useful to add to change in cvs.
Skip
> -----Original Message-----
> From: Will Glass-Husain [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, March 28, 2002 2:52 PM
> To: [EMAIL PROTECTED]
> Subject: MS SQL Server - reftable / constraintname
>
>
> Hi,
>
> I wanted to add this to the mailing list archive for MS SQL
> Server (I've
> listed other useful email messages at the end of this note).
> This might be
> a bug, any comments?
>
> When I used Torque 3.0 dev (standalone) to generate SQL
> queries based on
> multiple tables, the SQL gave me one of these error messages
> in SQL Server
> 2000:
>
> The variable name '@reftable' has already been declared.
> Variable names
> must be unique within a query batch or stored procedure.
>
> The variable name '@constraintname' has already been
> declared. Variable
> names must be unique within a query batch or stored procedure.
>
> Turns out that the generated SQL script does not work
> properly when you dump
> it all into Query Analyzer at once. In particular, each
> table declares
> local variables "reftable" and "constraintname". Dumping the
> entire SQL
> script (with all the table defs) into the SQL Query Analyzer
> gave me this
> error as the references were redeclared for each table.
>
> The solution was to manually edit the script, doing a
> search/replace (whole
> word) changing "reftable" to "reftable1" in the first table,
> "reftable2" in
> the second table, etc. Then I did the same thing for
> "constraintname".
> Caution-- be careful not to change the keyword "reftables".
>
> WILL
>
> References:
>
> http://jakarta.apache.org/turbine/howto/mssql-howto.html
>
> http://www.mail-archive.com/turbine-user@jakarta.apache.org/ms
g05744.html
http://www.mail-archive.com/turbine-user@jakarta.apache.org/msg01677.html
http://www.mail-archive.com/turbine-user@jakarta.apache.org/msg01677.html
--
To unsubscribe, e-mail:
<mailto:[EMAIL PROTECTED]>
For additional commands, e-mail:
<mailto:[EMAIL PROTECTED]>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
