Scripting ALTER TABLE

I need to create a script to disable all triggers and constraints in my
database.

It appears as though I cannot use a local variable for the table name in the
ALTER TABLE statement (e.g. ALTER TABLE @.TBL).

Is there any reason for this?

Thanks,

Kevin"Kevin Haugen" <khaugen@.pacbell.net> wrote in message
news:jx1Hd.12736$5R.1377@.newssvr21.news.prodigy.co m...
>I need to create a script to disable all triggers and constraints in my
>database.
> It appears as though I cannot use a local variable for the table name in
> the ALTER TABLE statement (e.g. ALTER TABLE @.TBL).
> Is there any reason for this?
> Thanks,
> Kevin

You can't use variables in place of table or column names, except when using
dynamic SQL, which has its own issues. Although if you're a DBA running an
admin script, then it's usually a reasonable option - there's more
discussion here:

http://www.sommarskog.se/dynamic_sql.html

Unfortunately, you don't say what your goal is, but if it's to load data
into the database, then all the usual loading tools (bcp.exe, DTS, BULK
INSERT) can ignore both constraints and triggers, so you might not need a
script anyway.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41ecd751$1_1@.news.bluewin.ch...
> "Kevin Haugen" <khaugen@.pacbell.net> wrote in message
> news:jx1Hd.12736$5R.1377@.newssvr21.news.prodigy.co m...
>>I need to create a script to disable all triggers and constraints in my
>>database.
>>
>> It appears as though I cannot use a local variable for the table name in
>> the ALTER TABLE statement (e.g. ALTER TABLE @.TBL).
>>
>> Is there any reason for this?
>>
>> Thanks,
>>
>> Kevin
>>
> You can't use variables in place of table or column names, except when
> using dynamic SQL, which has its own issues. Although if you're a DBA
> running an admin script, then it's usually a reasonable option - there's
> more discussion here:
> http://www.sommarskog.se/dynamic_sql.html
> Unfortunately, you don't say what your goal is, but if it's to load data
> into the database, then all the usual loading tools (bcp.exe, DTS, BULK
> INSERT) can ignore both constraints and triggers, so you might not need a
> script anyway.
> Simon

I'll look into it. I'm planning on converting existing data into a new
format. Since I have to identify each table and write a query to do the
conversion, I could easily do a copy/paste for each table to disable and
re-enable the triggers and constraints. I am hoping to shortcut some of the
work by automating that piece.

Thanks,

Kevin

Script Task vs. Script Component

Under "Control Flow Items" appears the Script Task. Under "Data Flow Sources" appears the Script Component. The Script Task also supports .NET scripting so why is it intended to be used with control flow and not data flow?

TIA,

barkingdog

The Script Task executes in isolation. It has no reliance on any other task nor vice versa. Components however work in unison with other components in the same data-flow. That is why the script task works in the control-flow and script component works in the data-flow.

What requirement do you have that caused you to ask?

-Jamie

|||

Jamie,

I don't have any requirements as such. I am learning about SSIS as fast as I can any many of my questions are just about things that seem peculiar to me. The answers I get often help me understand the "bigger" picture of how things in SSIS fit together. In many ways, I'm still at the "how many letters are there in your alphabet?" phase of SSIS

Barkding

sql