Is it possible to running cursor query in Execute SQl Task?
My Cursor query is simple, just disable all constraint in all tables at
a database :

declare @tablename varchar(30), @str varchar(80)
declare c1 cursor for select name from sysobjects where type = 'U'
open c1
fetch next from c1 into @tablename
while ( @@fetch_status <> -1 )
begin
SET @str = 'alter table [dbo].[' + @tablename + '] nocheck
constraint all '
Execute(@str)
fetch next from c1 into @tablename
end
deallocate c1
go

If it's not possible, i've plan to create stored procedure and then
execute it from Execute SQL Task. 'Cause I want to execute that
procedure at many database, is it right to put that sp at master
database, so I can call it at all connection (database) ? Or could I
use other DTS Task to replace the Exceute SQL Task?

Any suggestion please...

Have you tried?

I can see no immediate reason that this would not work.

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Resant" <resant_v (AT) yahoo (DOT) com> wrote

Which one? Cursor at Execute SQL Task or my backup plans (create sp)?

Yes, i've tried Cursor at Execute SQL Task and get error :
'incorrect syntax near c1'
but it's work when I execute that's query at Query Analyzer.

And haven't try the backup plans...waiting for any other better
suggestion.

Thank you

Resant, I created a test DTS package using your Execute SQL Task cursor
code and it runs sucessfully to completion. Please check your connection data
source. Also, in general you should try to avoid using cursors in the DTS
Execute SQL Task and Transformation tasks. There are alternative T-SQL
mechanisms for achieving your desired result. The only time one should use a
cursor is if the SQL Profiler shows that the cursor requires less SQL
Server/Windows 2000/Windows 2003 resources than alternative T-SQL
formulations. Finally , I believe your @tablename variable show be
varchar(60) instead of varchar(30) because any tablenames greater than 30
characters will be truncated and cause a DTS run-time error. Thank you.

"Resant" wrote:

Resant, I wrote some Execute SQL Task SQL code for you which does not use
cursors and is fast. Please provide an email address where I can email this
to you.

"Resant" wrote:

If you don't mind you can send to ilryu18 (AT) hotmail (DOT) com

Big Thanks

It's weird, i also create new DTS package that's only consist a
connection (Ms. OLE DB Provider for SQL Server) and Execute SQL Task. I
think the @tablename size wasn't a problem, cause I've changed it and
still get the error and it's ok when i execute that query at Query
Analyzer without change the size.
At Execute SQL Task, when I try to Parse the Query, it's still show
error message :

Error description eferred prepare could not be completed
Statement(s) could not be prepared
Incorrect syntax near 'c1'

Actually I also know another query that's able to replace cursor
function, but I'm confused what's wrong with this query, it's seem a
mystery for me.

Thanks anyway

I've found the error cause.
Just delete 'go' and everything wil be ok

But why 'go' doesn't work in Execute SQL Task? I dunno