Executing Dynamic SQL with update

I have a temp table #Temp(id int, statement varchar, result int)
The statement column contains a prebuilt sql select statement e.g
id statement
Result
1 select count(*) from books where authorname like 'A%'.
2 select count(*) from books where authorname like 'J%'.
I want run an update statement on the table so that i can set the result
column to the result of the select statement in the statement column.
e.g. update #temp
set result = [statement result]
I want to aviod cursors. Can this be done?
Please help.This is in general, a poor approach. You can use certain undocumented
procedures ( in SQL 2000 ) to get this done, but it is complex, error prone
and rarely worth it.
Instead of having SQL statements as data values & updating #temp tables,
consider using a view. Alternatively, depending on tables & wild card
patterns involved, in some cases you might be able to resolve the problem
with a single query with CASE.
If you want a workable solution, pl. refer to www.aspfaq.com/5006 and post
the required information along with a brief explanation of your
requirements.
In case you are wondering how to get the scalar result of a SELECT statement
into a variable dynamically, refer to the procedure sp_ExecuteSQL in SQL
Server Books Online.
Anith

Executing an update command after subscription

I need to execute an update stored procedure once the subscription report
has run.
The only way I can find to do this is to add an extra TSQL step into the SQL
Agent job through SQL Management Studio. But whenever the subscription is
modified via report manager, the newly added TSQL step is deleted.
Is there a way to get around this?
Cheers
GrantHello Grant,
My suggestion is create a new SQL Job and in this job, you could first
execute the job for the subscription and then you could add your extra
taskes. The schedule of this new job could be configured as you wish.
Once you create a subsciption in the report manager, the sql server agent
will create a job which have a GUID.
You could run the following TSQL statement to run the subscription job:
exec msdb..sp_start_job @.job_name='<GUID of the subscription job>'
Hope my suggestion will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Executing an insert in a linked server.

Hi:
Im performing an insert in a table that has a trigger.
The propossal of this trigger is to keep update two
tables when an insert occurss in server A; it starts a
trigger to insert a row in one table in server B
The servers are linked and the user has permission to
perform insert in the remote server. Im getting the
following error:
Unable to start a nested transaction for OLE DB
provider 'SQLOLEDB'. A nested transaction was required
because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more
transactions on this session.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionLocal::StartTransaction returned 0x8004d013:
ISOLEVEL=4096].
Can anybody helpme'
Regards"Jose Martinez" <jomapa01@.hotmail.com> wrote in message
news:09fa01c38c59$79adb0f0$a101280a@.phx.gbl...
> Hi:
> Im performing an insert in a table that has a trigger.
> The propossal of this trigger is to keep update two
> tables when an insert occurss in server A; it starts a
> trigger to insert a row in one table in server B
> The servers are linked and the user has permission to
> perform insert in the remote server. Im getting the
> following error:
> Unable to start a nested transaction for OLE DB
> provider 'SQLOLEDB'. A nested transaction was required
> because the XACT_ABORT option was set to OFF.
That is - set XACT_ABORT ON at begining of your transaction
> [OLE/DB provider returned message: Cannot start more
> transactions on this session.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionLocal::StartTransaction returned 0x8004d013:
> ISOLEVEL=4096].
>
> Can anybody helpme'
> Regards|||Regards, I appreciate your help
>--Original Message--
>"Jose Martinez" <jomapa01@.hotmail.com> wrote in message
>news:09fa01c38c59$79adb0f0$a101280a@.phx.gbl...
>> Hi:
>> Im performing an insert in a table that has a trigger.
>> The propossal of this trigger is to keep update two
>> tables when an insert occurss in server A; it starts a
>> trigger to insert a row in one table in server B
>> The servers are linked and the user has permission to
>> perform insert in the remote server. Im getting the
>> following error:
>> Unable to start a nested transaction for OLE DB
>> provider 'SQLOLEDB'. A nested transaction was required
>> because the XACT_ABORT option was set to OFF.
>That is - set XACT_ABORT ON at begining of your
transaction
>> [OLE/DB provider returned message: Cannot start more
>> transactions on this session.]
>> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
>> ITransactionLocal::StartTransaction returned
0x8004d013:
>> ISOLEVEL=4096].
>>
>> Can anybody helpme'
>> Regards
>
>.
>

executeUpdate return 0 rowcount for an update

Hi! I have a developer who reported the following problem
"For those of you who aren't familiar, the way we modify data in the Java
apps is through an API called JDBC. The JDBC API provides a
PreparedStatement class that allows you to assemble sql and then have it
executed. In the case of an update statement, you are supposed to call an
executeUpdate method. That method is supposed to return the number of rows
updated by the command. If it returns 0, I was assuming that no rows were
updated, hence the locking failure. I finally decided that maybe the row was
getting updated, but executeUpdate was incorrectly returning 0. I just
tested that theory and found it to be the case. The row is updated, but
executeUpdate is returning 0."
The query statement passed to executeUpdate is a simple update statement
(update ... set .. where ..)
The table developer tried to update has an update trigger which has executed
2 INSERT statements:
INSERT statement #1 adds records into transaction_touch table and
INSERT statement #2 adds records into an audit table
The transaction_touch table has an insert trigger which populates data into
another table. The test showed that
no record was added into this table. Therefore, the rowcount is 0. If I
commented out the INSERT statement inside
the transaction_touch insert trigger, a non-zero rrowcount was returned back
to Java apps.
I don't know if anybody has experienced this problem using MS JDBC driver to
access MS SQL Server 2000? If
you do and has a solution, please let me know. Thanks
-- Peter Lo
Peter:
We're going to investigate this one further and post back what we find.
Can you post the trigger repro code -- that would be helpful.
-shelby
Shelby Goerlitz
Microsoft, SQL Server
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:OLvobEkhFHA.3568@.TK2MSFTNGP10.phx.gbl...
> Hi! I have a developer who reported the following problem
> "For those of you who aren't familiar, the way we modify data in the Java
> apps is through an API called JDBC. The JDBC API provides a
> PreparedStatement class that allows you to assemble sql and then have it
> executed. In the case of an update statement, you are supposed to call an
> executeUpdate method. That method is supposed to return the number of rows
> updated by the command. If it returns 0, I was assuming that no rows were
> updated, hence the locking failure. I finally decided that maybe the row
> was
> getting updated, but executeUpdate was incorrectly returning 0. I just
> tested that theory and found it to be the case. The row is updated, but
> executeUpdate is returning 0."
> The query statement passed to executeUpdate is a simple update statement
> (update ... set .. where ..)
> The table developer tried to update has an update trigger which has
> executed
> 2 INSERT statements:
> INSERT statement #1 adds records into transaction_touch table and
> INSERT statement #2 adds records into an audit table
> The transaction_touch table has an insert trigger which populates data
> into
> another table. The test showed that
> no record was added into this table. Therefore, the rowcount is 0. If I
> commented out the INSERT statement inside
> the transaction_touch insert trigger, a non-zero rrowcount was returned
> back
> to Java apps.
> I don't know if anybody has experienced this problem using MS JDBC driver
> to
> access MS SQL Server 2000? If
> you do and has a solution, please let me know. Thanks
>
> -- Peter Lo
>
>
>
>
|||Shelby:
The update statement executed by Java apps looks like this:
update icts_user where user_init = '?' and trans_id = ?
==> In this example, only one icts_user record will be modified
Here are the triggers:
create trigger icts_user_updtrg
on icts_user
for update
as
declare @.num_rows int,
@.count_num_rows int,
@.errmsg varchar(255)
select @.num_rows = @.@.rowcount
if @.num_rows = 0
return
if update(loc_code)
begin
if exists (select 1 from inserted
where loc_code not in (select loc_code from location))
begin
if @.num_rows = 1
begin
select @.errmsg = 'The loc_code ''' + loc_code + ''' is not a valid
code defined in the location table.'
from inserted
end
else
begin
select @.errmsg = 'The loc_code is not a valid code defined in the
location table.'
end
raiserror 20002 @.errmsg
rollback tran
return
end
end
if exists (select 1 from inserted, icts_user
where icts_user.user_logon_id = rtrim(inserted.user_logon_id) and
icts_user.user_init != inserted.user_init)
begin
if @.num_rows = 1
begin
select @.errmsg = 'The new user_logon_id ''' + user_logon_id + ''' has
existed in the icts_user table. Duplicate is not allowed!'
from inserted
end
else
begin
select @.errmsg = 'The new user_logon_id(s) have existed in the
icts_user table. Duplicate is not allowed!'
end
raiserror 20003 @.errmsg
rollback tran
return
end
/* END_DATA_INTEGRITY */
if not update(trans_id)
begin
raiserror 20090 '(icts_user) The change needs to be attached with a new
trans_id'
rollback tran
return
end
if exists (select 1
from master.dbo.sysprocesses
where spid = @.@.spid and
rtrim(program_name) IN ('ISQL-32', 'OSQL-32', 'SQL Query
Analyzer') )
begin
if (select count(*) from inserted, deleted where inserted.trans_id <=
deleted.trans_id) > 0
begin
select @.errmsg = '(icts_user) New trans_id must be larger than
original trans_id.'
select @.errmsg = @.errmsg + char(10) + 'You can use the the
gen_new_transaction procedure to obtain a new trans_id.'
raiserror 20092 @.errmsg
rollback tran
return
end
end
if exists (select * from inserted i, deleted d
where i.trans_id < d.trans_id and
i.user_init = d.user_init )
begin
raiserror 20091 '(icts_user) new trans_id must not be older than current
trans_id.'
rollback tran
return
end
select @.dummy_update = 0
if update(user_init)
begin
select @.count_num_rows = (select count(*) from inserted i, deleted d
where i.user_init = d.user_init )
if (@.count_num_rows = @.num_rows)
begin
select @.dummy_update = 1
end
else
begin
raiserror 20072 '(icts_user) primary key can not be changed.'
rollback tran
return
end
end
insert transaction_touch
select 'UPDATE',
'IctsUser',
'DIRECT',
convert(varchar(40), i.user_init),
null,
null,
null,
null,
null,
null,
null,
i.trans_id,
it.sequence
from inserted i, icts_transaction it
where i.trans_id = it.trans_id and
it.type != 'E'
if @.dummy_update = 0
insert aud_icts_user
(user_init,
user_last_name,
user_first_name,
desk_code,
loc_code,
user_logon_id,
us_citizen_ind,
user_job_title,
user_status,
user_employee_num,
email_address,
defaults_information_id,
trans_id,
resp_trans_id)
select
d.user_init,
d.user_last_name,
d.user_first_name,
d.desk_code,
d.loc_code,
d.user_logon_id,
d.us_citizen_ind,
d.user_job_title,
d.user_status,
d.user_employee_num,
d.email_address,
d.defaults_information_id,
d.trans_id,
i.trans_id
from deleted d, inserted i
where d.user_init = i.user_init
return
go
/* ***************************** */
create trigger transaction_touch_instrg
on transaction_touch
for insert
as
declare @.num_rows int,
@.count_num_rows int,
@.errmsg varchar(255)
select @.num_rows = @.@.rowcount
if @.num_rows = 0
return
insert into als_run_touch
(als_module_group_id, operation, entity_name,key1,key2,
key3,key4,key5,key6,key7,key8,trans_id,sequence,to uch_key)
select a.als_module_group_id,
case when i.operation = 'INSERT' then 'I'
when i.operation = 'UPDATE' then 'U'
when i.operation = 'DELETE' then 'D'
end,
i.entity_name,
i.key1,
i.key2,
i.key3,
i.key4,
i.key5,
i.key6,
i.key7,
i.key8,
i.trans_id,
i.sequence,
i.touch_key
from als_module_entity a,
inserted i
where ( ( ((a.operation_type_mask & 1) = 1) and (i.operation =
'INSERT') ) OR
( ((a.operation_type_mask & 2) = 2) and (i.operation =
'UPDATE') ) OR
( ((a.operation_type_mask & 4) = 4) and (i.operation =
'DELETE') ) ) AND
(a.entity_name = i.entity_name)
return
go
The aud_icts_user table does not have triggers. I hope this help
-- Peter Lo
"Shelby Goerlitz [MSFT]" <shelbyg@.nospam.microsoft.com> wrote in message
news:uaP2%235whFHA.2180@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Peter:
> We're going to investigate this one further and post back what we find.
> Can you post the trigger repro code -- that would be helpful.
> -shelby
> Shelby Goerlitz
> Microsoft, SQL Server
>
> "Peter Lo" <peter.lo@.tradecapture.com> wrote in message
> news:OLvobEkhFHA.3568@.TK2MSFTNGP10.phx.gbl...
Java[vbcol=seagreen]
an[vbcol=seagreen]
rows[vbcol=seagreen]
were[vbcol=seagreen]
driver
>
|||Peter --
thx for the info -- that is very helpful. just to let you know, dev is
still looking at this issue.
-shelby
Shelby Goerlitz
Microsoft, SQL Server
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:OM5tnTyhFHA.3608@.TK2MSFTNGP12.phx.gbl...
> Shelby:
> The update statement executed by Java apps looks like this:
> update icts_user where user_init = '?' and trans_id = ?
> ==> In this example, only one icts_user record will be modified
> Here are the triggers:
> create trigger icts_user_updtrg
> on icts_user
> for update
> as
> declare @.num_rows int,
> @.count_num_rows int,
> @.errmsg varchar(255)
> select @.num_rows = @.@.rowcount
> if @.num_rows = 0
> return
> if update(loc_code)
> begin
> if exists (select 1 from inserted
> where loc_code not in (select loc_code from location))
> begin
> if @.num_rows = 1
> begin
> select @.errmsg = 'The loc_code ''' + loc_code + ''' is not a valid
> code defined in the location table.'
> from inserted
> end
> else
> begin
> select @.errmsg = 'The loc_code is not a valid code defined in the
> location table.'
> end
> raiserror 20002 @.errmsg
> rollback tran
> return
> end
> end
> if exists (select 1 from inserted, icts_user
> where icts_user.user_logon_id = rtrim(inserted.user_logon_id)
> and
> icts_user.user_init != inserted.user_init)
> begin
> if @.num_rows = 1
> begin
> select @.errmsg = 'The new user_logon_id ''' + user_logon_id + ''' has
> existed in the icts_user table. Duplicate is not allowed!'
> from inserted
> end
> else
> begin
> select @.errmsg = 'The new user_logon_id(s) have existed in the
> icts_user table. Duplicate is not allowed!'
> end
> raiserror 20003 @.errmsg
> rollback tran
> return
> end
> /* END_DATA_INTEGRITY */
> if not update(trans_id)
> begin
> raiserror 20090 '(icts_user) The change needs to be attached with a new
> trans_id'
> rollback tran
> return
> end
> if exists (select 1
> from master.dbo.sysprocesses
> where spid = @.@.spid and
> rtrim(program_name) IN ('ISQL-32', 'OSQL-32', 'SQL Query
> Analyzer') )
> begin
> if (select count(*) from inserted, deleted where inserted.trans_id <=
> deleted.trans_id) > 0
> begin
> select @.errmsg = '(icts_user) New trans_id must be larger than
> original trans_id.'
> select @.errmsg = @.errmsg + char(10) + 'You can use the the
> gen_new_transaction procedure to obtain a new trans_id.'
> raiserror 20092 @.errmsg
> rollback tran
> return
> end
> end
> if exists (select * from inserted i, deleted d
> where i.trans_id < d.trans_id and
> i.user_init = d.user_init )
> begin
> raiserror 20091 '(icts_user) new trans_id must not be older than current
> trans_id.'
> rollback tran
> return
> end
> select @.dummy_update = 0
> if update(user_init)
> begin
> select @.count_num_rows = (select count(*) from inserted i, deleted d
> where i.user_init = d.user_init )
> if (@.count_num_rows = @.num_rows)
> begin
> select @.dummy_update = 1
> end
> else
> begin
> raiserror 20072 '(icts_user) primary key can not be changed.'
> rollback tran
> return
> end
> end
> insert transaction_touch
> select 'UPDATE',
> 'IctsUser',
> 'DIRECT',
> convert(varchar(40), i.user_init),
> null,
> null,
> null,
> null,
> null,
> null,
> null,
> i.trans_id,
> it.sequence
> from inserted i, icts_transaction it
> where i.trans_id = it.trans_id and
> it.type != 'E'
> if @.dummy_update = 0
> insert aud_icts_user
> (user_init,
> user_last_name,
> user_first_name,
> desk_code,
> loc_code,
> user_logon_id,
> us_citizen_ind,
> user_job_title,
> user_status,
> user_employee_num,
> email_address,
> defaults_information_id,
> trans_id,
> resp_trans_id)
> select
> d.user_init,
> d.user_last_name,
> d.user_first_name,
> d.desk_code,
> d.loc_code,
> d.user_logon_id,
> d.us_citizen_ind,
> d.user_job_title,
> d.user_status,
> d.user_employee_num,
> d.email_address,
> d.defaults_information_id,
> d.trans_id,
> i.trans_id
> from deleted d, inserted i
> where d.user_init = i.user_init
> return
> go
> /* ***************************** */
> create trigger transaction_touch_instrg
> on transaction_touch
> for insert
> as
> declare @.num_rows int,
> @.count_num_rows int,
> @.errmsg varchar(255)
> select @.num_rows = @.@.rowcount
> if @.num_rows = 0
> return
> insert into als_run_touch
> (als_module_group_id, operation, entity_name,key1,key2,
> key3,key4,key5,key6,key7,key8,trans_id,sequence,to uch_key)
> select a.als_module_group_id,
> case when i.operation = 'INSERT' then 'I'
> when i.operation = 'UPDATE' then 'U'
> when i.operation = 'DELETE' then 'D'
> end,
> i.entity_name,
> i.key1,
> i.key2,
> i.key3,
> i.key4,
> i.key5,
> i.key6,
> i.key7,
> i.key8,
> i.trans_id,
> i.sequence,
> i.touch_key
> from als_module_entity a,
> inserted i
> where ( ( ((a.operation_type_mask & 1) = 1) and (i.operation =
> 'INSERT') ) OR
> ( ((a.operation_type_mask & 2) = 2) and (i.operation =
> 'UPDATE') ) OR
> ( ((a.operation_type_mask & 4) = 4) and (i.operation =
> 'DELETE') ) ) AND
> (a.entity_name = i.entity_name)
> return
> go
>
> The aud_icts_user table does not have triggers. I hope this help
>
> -- Peter Lo
>
> "Shelby Goerlitz [MSFT]" <shelbyg@.nospam.microsoft.com> wrote in message
> news:uaP2%235whFHA.2180@.TK2MSFTNGP15.phx.gbl...
> Java
> an
> rows
> were
> driver
>
|||Peter,
I used the following SQL to create some DB tables and triggers for my
testing and determined that the behavior you are seeing is by design.
The MS SQL Server driver for JDBC does not have the means to return the
update count for the statement executed by the application when that
statement causes other SQL DML statements to be executed via a trigger.
create table trig1(col1 int, col2 char(5))
create table trig2(col1 int, col2 char(5))
create table trig3(col1 int, col2 char(5))
create trigger inserttrig1
on trig1
for insert
as
if update(col1)
begin
insert into trig2 values(1,'row')
end
create trigger inserttrig2
on trig2
for insert
as
if update(col1)
begin
update trig3 set col1=1 where col2='none'
end
The inserttrig2 trigger will never affect any rows as there are no rows
in the trig3 table. This is intentional. I used a simple JDBC app to
insert a row into the trig1 table so I could see what kind of update
count was returned. With the MS SQL Server driver for JDBC, an update
count of 0 was returned as I expected. This is the update count for
the execution of the inserttrig2 trigger. Due to the way SQL Server
executes triggers and the resulting TDS packet sent to the driver upon
completion, the update count returned corresponds to the most nested
SQL statement that was executed in a trigger.
For example, if my application execute an insert statement that results
in trigger1 performing and insert which results in trigger2 performing
and update, then the results returned to the application are in the
order trigger2 update count, trigger1 update count, application
statement update count.
I ran a second test using the 3.5 DataDirect SQL Server driver for JDBC
and saw an update count of 1 returned which corresponded to the update
count for the initial insert. This is the correct behavior as the
DataDirect driver has the connection option alwaysReportTriggerResults
which defaults to false. As a result, the driver parses the results
and discards update counts for trigger that are returned by the server.
This results in the driver returning the update count for the
statement executed by the app.
When I set this option to true, then an update count of zero is
returned thereby behaving the same as the MS driver as expected.
If you wish to take advantage of this functionality, then I suggest
downloading the DataDirect driver and taking it for a spin. You can
download from here:
http://www.datadirect.com/download/index.ssp
-Brian
Brian Derwart
DataDirect Technologies SupportLink

ExecuteNonQuery requires an open and available Connection

Using SQL 2000 Developer, Windows XP, and DotNet 2.0 I get this error
message when trying to UPDATE a very large table:
"ExecuteNonQuery requires an open and available Connection. The connection's
current state is closed."
The UPDATE command affects all the rows in the column. The confusing part
is - it only occurs on a very large table (over 1M rows). It does not orrur
on smaller tables. So I don't think it actually has anything to do with the
connection.
Any ideas would be greatly appreciated.Just a guess, the connection might have timed out or broken. Try increasing
the timeout
"Neil W." wrote:

> Using SQL 2000 Developer, Windows XP, and DotNet 2.0 I get this error
> message when trying to UPDATE a very large table:
> "ExecuteNonQuery requires an open and available Connection. The connection
's
> current state is closed."
> The UPDATE command affects all the rows in the column. The confusing part
> is - it only occurs on a very large table (over 1M rows). It does not orr
ur
> on smaller tables. So I don't think it actually has anything to do with t
he
> connection.
> Any ideas would be greatly appreciated.
>
>
>|||Neil W. (neilw@.NOSPAMTHANKYOUnetlib.com) writes:
> Using SQL 2000 Developer, Windows XP, and DotNet 2.0 I get this error
> message when trying to UPDATE a very large table:
> "ExecuteNonQuery requires an open and available Connection. The
> connection's current state is closed."
> The UPDATE command affects all the rows in the column. The confusing
> part is - it only occurs on a very large table (over 1M rows). It does
> not orrur on smaller tables. So I don't think it actually has anything
> to do with the connection.
Might be so, but I would put my bets on the ADO .Net code. How does
the relevant bits look like.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

ExecuteNonQuery - Add working/Update not working

I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...

This was my test:

Dim cmd As New SqlCommand("pContact_Update", cn)
 'Dim cmd As New SqlCommand("pContact_Add", cn)
 Try
 cmd.CommandType = CommandType.StoredProcedure
 cmd.Parameters.Add("@.UserId", SqlDbType.VarChar).Value = UserId
 cmd.Parameters.Add("@.FirstName", SqlDbType.VarChar).Value = TextBox1.Text
 [...etc more parameters...]
 cmd.Parameters.Add("@.Id", SqlDbType.VarChar).Value = ContactId
 cn.Open()
 cmd.ExecuteNonQuery()
 Label1.Text = "done"
 cn.Close()
 Catch ex As Exception
 Label1.Text = ex.Message
 End Try

When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.

I have looked at the stored procedures and the syntax is correct according to SQL Server.

Please I would appreciate any advice...Before you do your executeNonQuery - - make sure (do a response.write or a Trace.Write) to make sure your USERID and textbox1.text values are actually populated.

Many times, if the update statement has a where clause, and it continues through, the WHERE arguments are not being fulfilled.|||Thanks for your reply...
I followed the code and the Id field is getting a value. I also added:

Dim NbrRows As Integer = cmd.ExecuteNonQuery()
Label1.Text = NbrRows

And I do receive a message that 1 row has been affected. However, the value I entered changes back to the original value and the record is not updated.

Execute SQL task with update statement

I am running an update statement in an execute sql task, it will run one time but then fails after that. Whats going on?

Here is my query I'm running.

UPDATE encounter
SET mrn = r.mrn, resourcecode = r.resourcecode, resgroup = r.resgroup, apptdate = r.apptdate, appttime = r.appttime
FROM SCFDBWH.SigSched.dbo.EncounterNARaw AS r INNER JOIN
encounter ON encounter.encounter_number = r.Encounter

What I'm doing is pulling info from a flat file, inserting it into the encounter table, I then run this sql statement to pull additional info from another table on another server, and update the encounter table with the corresponding info. Pretty straight forward. But what is really getting me is that the package will run 1 time but if I wait ten minutes and try running it again it bombs. Any ideas?

Please provide any error messages. That usually helps us help you.|||Error: OLE DB provider "SQLNCLI" for linked server "SCFDBWH" returned message "Login timeout expired".

Error: OLE DB provider "SQLNCLI" for linked server "SCFDBWH" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

[Execute SQL Task] Error: Executing the query "UPDATE encounter SET mrn = r.mrn, resourcecode = r.resourcecode, resgroup = r.resgroup, apptdate = r.apptdate, appttime = r.appttime FROM SCFDBWH.SigSched.dbo.EncounterNARaw AS r INNER JOIN encounter ON encounter.encounter_number = r.Encounter " failed with the following error: "Named Pipes Provider: Could not open a connection to SQL Server [5]. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This is what the SSIS package says. I'm not sure how helpful it is but I can run the update statement in management studio all day long and it works great. It just bombs through the SSIS package.

|||

Seems as though the package is having problems talking to the server, "login timeout expired"

|||

I have all the timeouts settings set to 60 seconds, which should be plenty of time, one would think. When it does work it runs real fast-less than 4 seconds.

The main frustration is that it will work 1 time when I do something to the query but then it doesn't work again.

Execute SQL Task with no rows affected

Hi,

I used with Execute SQL Task for update a table in Oracle DB.

I saw that when the command has no rows for updeting, the task fails.

Here is my command:

update tableName set fieldA=sysdate where fieldB is Null

and again, when there are some rows that fieldB is Null then the command succeed, but when the fieldB in all the rows is not null the command fails.

I tried to play with the ResultSet with no success.

Please your advice.

Thank you in advance

Noam

What error or errors are returned when the task fails? Can you please copy and paste the error output from BIDS or from a log file into a reply?

Are you attempting to store the resultset from the query in a variable?

Can you run the query successfully (where zero rows are affected) without error using SQL*Plus or another query tool?

This information will likely be invaluable in helping track down the source of the error.

|||

Thank you for your reply.

This is the error:

"Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "update TableName set FieldA=SYSDATE where FieldB is Null" failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task."

No, I didn't attempt to store the resultset, I just executed the query.

and yes, I ran the query successfully using SQL*Plus with the result : "0 rows updated."

Please your help.

Thanks

Noam

|||

I found it.

My connection was odbc.

I changed the connection to Ado.Net provider for odbc and it works without fail.

Why?

It's another question.

Thank you anyway.

Noam

execute SQL task with in a For Each Loop

hi guys,

i'm trying to do a bulk insert through a t-sql which accepts 3 params.

i frist run a SQL statement to get a list of DB's to update through a SQL task, then i save the output to a variable object. which is been used by the For each loop.

i've done the mapping with the object fine. but i want to call a SQL task within for each loop, and parse the above results as parameters. i tried parsing it as

exec mysp3 @.[User::fundid], @.[User::dbCode], @.[User::subfund]

where fundiid, dbcode and subfund are user defined variables that gets updated by the ado recordset.

this fails with the error msg:

SSIS package "SP test.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec ConnectODBC @.[User::fundid], @.[User::dbcode], @.[User::subfund] failed with the following error: "Incorrect syntax near 'User::fundid'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Warning: 0x80019002 at Shred the contents of the variable: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "SP test.dtsx" finished: Failure.

i also tried

exec mysp3 ?, ?, ?

then mapping the inputs still doesn't work

same thing with

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec ConnectODBC ?,?,? failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Look here:

http://www.sqlis.com/default.aspx?81

|||

Thanks Loom,

just what i wanted. i didn't know you have to interpret your expressions differently depending on the connection type. well now i do

by the way because of these different data conection types, my variable types are different. for example i save my fundid variable as int32. when using a OLE DB connection under parameter mapping there's no int, but numeric and it craps out when i try to do the mapping. is there a way to cast this? when passing to the t-sql?

Execute SQL task to update variables

Hi there

I'm attempting to update a variable using the Execute SQL task, I've read a lot of posts on this and seems reasonably simple but obviously not. The first time I ran it the variable was updated correctly, I then manually changed the variable value and since then it doesn't work.

I have a task with the following properties;

Resultset: SingleRow

SQLStatement: SELECT MAX(Player_Daily_Data_Pull_Date) as 'PlayerDaily' From Job_Control

On the resultset tab I have the resultsetname = PlayerDailyand the variable I want to update.

The variable has a type of datatime and it's scope is the container that I'm running the sql task within.

Any help would be appreciated.

Derek

What are the symptoms of this not working? Are there errors or warnings?

Donald

|||

Hi Donald

No symptoms or problems, the package executes successfully. Apart from the fact that the variables aren't updated at the end of the package when I look at them in the variable screen.

Derek

|||

Hi Derek,

The Variable Screen will always show the Initial Value set for that variable.

To check whether your program works fine -

1) Change the DateTime variable to 'Package' Scope.
2) Place a breakpoint for the PreExecute Event for the following task
3) Execute the Package and When the program stops for the BreakPoint - Select Debug Option -> Windows -> Locals
4) Scroll through the list and Locate the DateTime Variable.
5) You will find that the DatTime Variable is updated with the value returned by the SQL Task.

Alternatively -

1) Change the DateTime variable to 'Package' Scope.
2) On Success of the SQL Task - Have a Script Task
3) Put that DateTimeVariable in ReadOnlyVariables list in Script Task
4) Have the following lines of code in the Public Sub Main() method in the script task

Windows.Forms.MessageBox.Show(Dts.Variables("<DateTimeVariable>").Value.ToString())
Dts.TaskResult = Dts.Results.Success

5) You will observe that the datetime value returned by the SQL Statement in SQL Task will be poped up in a small popup window.

After the successful execution of the package - if you go to the Variables window - you will observe that it will only show the initial value. The runtime value assigned to the DateTimeVaribale isn't stored permanently and the runtime value will only be available during execution.

Thanks,
Loonysan

|||

Thanks Loonysan

you were right, it was working just couldn't see it.

Cheers,

Derek

EXECUTE SQL TASK --> Object Reference Not Set to An Instance of an Object

Hi all,

Does anyone see the error below before?

I am using SSIS Execute SQL Task (ADO.NET) to update a table using a stored procedure.

It works like this many times for me and all of a sudden, not sure what is changing in the environment, I kept getting this WARNING when I click on PARSE QUERY

“Object Reference Not Set to An Instance of an Object” when I click on PARSE QUERY.

This is going against SQL SERVER 2005 SP2 x64 Enterprise.

Note that this task executes fine and the stored procedure updates data.

The stored procedure does the following.

There are other stored procedures of different kinds and they all worked.

But all of them give this error when I click on PARSE QUERY.

Code Snippet

DECLARE @.TodayDate datetime

SET @.TodayDate = GETDATE()

Exec dbo.updDimBatch

@.BatchKey = @.BatchKey,

@.ParentBatchKey = @.ParentBatchKey,

@.BatchName = 'Load Customer Increment',

@.BatchStartDate = NULL,

@.BatchEndDate = @.TodayDate,

@.StatusKey = NULL,

@.RowsInserted = @.Count_Insert,

@.RowsUpdated = @.Count_Update,

@.RowsException = NULL,

@.RowsError = NULL,

@.UpdatedDate = @.TodayDate,

@.BatchDescription = NULL

OLEDB Sample also give me syntax error

exec dbo.updDimBatch ?,?,'Load Activity Increment','6/27/2007','6/27/2007',1,?,?,0,0,'6/27/2007',''

I tried to change to OLEDB and call the stored procedure like this but got syntax error?

Not sure what is the error here.

I believe there is a known issue when trying to do a Parse Query with ADO.NET, when variables or parameters are being used. Does the task succeed if you execute it?|||

yes, it executes fine.

This is going against SQL SERVER 2005 SP2 x64 Enterprise.

Note that this task executes fine and the stored procedure updates data.

The stored procedure does the following.

This is just an example. But to call ANY STORED PROCEDURE will give the same problem.

I think it is a bug with the ADO.NET query parser.

Note that I saw this exact same error LAST YEAR with SP1. I think it never get fixed.

work around? customers don't want to use OLEDB because the syntax ?,? etc is not straightforward and easy to maintain.

They elect to just ignore it.

There are other stored procedures of different kinds and they all worked.

But all of them give this error when I click on PARSE QUERY.

Code Snippet

DECLARE @.TodayDate datetime

SET @.TodayDate = GETDATE()

Exec dbo.updDimBatch

@.BatchKey = @.BatchKey,

@.ParentBatchKey = @.ParentBatchKey,

@.BatchName = 'Load Customer Increment',

@.BatchStartDate = NULL,

@.BatchEndDate = @.TodayDate,

@.StatusKey = NULL,

@.RowsInserted = @.Count_Insert,

@.RowsUpdated = @.Count_Update,

@.RowsException = NULL,

@.RowsError = NULL,

@.UpdatedDate = @.TodayDate,

@.BatchDescription = NULL

EXECUTE SQL TASK --> Object Reference Not Set to An Instance of an Object

Hi all,

Does anyone see the error below before?

I am using SSIS Execute SQL Task (ADO.NET) to update a table using a stored procedure.

It works like this many times for me and all of a sudden, not sure what is changing in the environment, I kept getting this WARNING when I click on PARSE QUERY

“Object Reference Not Set to An Instance of an Object” when I click on PARSE QUERY.

This is going against SQL SERVER 2005 SP2 x64 Enterprise.

Note that this task executes fine and the stored procedure updates data.

The stored procedure does the following.

There are other stored procedures of different kinds and they all worked.

But all of them give this error when I click on PARSE QUERY.

Code Snippet

DECLARE @.TodayDate datetime

SET @.TodayDate = GETDATE()

Exec dbo.updDimBatch

@.BatchKey = @.BatchKey,

@.ParentBatchKey = @.ParentBatchKey,

@.BatchName = 'Load Customer Increment',

@.BatchStartDate = NULL,

@.BatchEndDate = @.TodayDate,

@.StatusKey = NULL,

@.RowsInserted = @.Count_Insert,

@.RowsUpdated = @.Count_Update,

@.RowsException = NULL,

@.RowsError = NULL,

@.UpdatedDate = @.TodayDate,

@.BatchDescription = NULL

OLEDB Sample also give me syntax error

exec dbo.updDimBatch ?,?,'Load Activity Increment','6/27/2007','6/27/2007',1,?,?,0,0,'6/27/2007',''

I tried to change to OLEDB and call the stored procedure like this but got syntax error?

Not sure what is the error here.

I believe there is a known issue when trying to do a Parse Query with ADO.NET, when variables or parameters are being used. Does the task succeed if you execute it?|||

yes, it executes fine.

This is going against SQL SERVER 2005 SP2 x64 Enterprise.

Note that this task executes fine and the stored procedure updates data.

The stored procedure does the following.

This is just an example. But to call ANY STORED PROCEDURE will give the same problem.

I think it is a bug with the ADO.NET query parser.

Note that I saw this exact same error LAST YEAR with SP1. I think it never get fixed.

work around? customers don't want to use OLEDB because the syntax ?,? etc is not straightforward and easy to maintain.

They elect to just ignore it.

There are other stored procedures of different kinds and they all worked.

But all of them give this error when I click on PARSE QUERY.

Code Snippet

DECLARE @.TodayDate datetime

SET @.TodayDate = GETDATE()

Exec dbo.updDimBatch

@.BatchKey = @.BatchKey,

@.ParentBatchKey = @.ParentBatchKey,

@.BatchName = 'Load Customer Increment',

@.BatchStartDate = NULL,

@.BatchEndDate = @.TodayDate,

@.StatusKey = NULL,

@.RowsInserted = @.Count_Insert,

@.RowsUpdated = @.Count_Update,

@.RowsException = NULL,

@.RowsError = NULL,

@.UpdatedDate = @.TodayDate,

@.BatchDescription = NULL

execute sql statements in text file

I am new to batch files. I have a text file that has several INSERT/UPDATE
sql statements. I would like to create a batch file to execute the sql
statements in the text file on my server. I am not having any success with
this. I thought I could create a batch file using osql that executes the sql
statements in text file but it's not working and I have no idea what I'm
doing wrong. Maybe there is a better way to do this? Any suggestions are
welcomed.
I am using the following:
osql -S server -d database -E -I file pathname
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1> osql -S server -d database -E -I file pathname
Change -I to -i
Linchi
"fullertee via SQLMonster.com" wrote:
> I am new to batch files. I have a text file that has several INSERT/UPDATE
> sql statements. I would like to create a batch file to execute the sql
> statements in the text file on my server. I am not having any success with
> this. I thought I could create a batch file using osql that executes the sql
> statements in text file but it's not working and I have no idea what I'm
> doing wrong. Maybe there is a better way to do this? Any suggestions are
> welcomed.
> I am using the following:
> osql -S server -d database -E -I file pathname
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1
>|||Thanks Linchi
Sorry that was just a typo. It not working with the i.
osql -S server -d database -E -i file pathname
fullertee
Linchi Shea wrote:
>> osql -S server -d database -E -I file pathname
>Change -I to -i
>Linchi
>> I am new to batch files. I have a text file that has several INSERT/UPDATE
>> sql statements. I would like to create a batch file to execute the sql
>[quoted text clipped - 5 lines]
>> I am using the following:
>> osql -S server -d database -E -I file pathname
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Error message? Can you post the exact command you execute?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fullertee via SQLMonster.com" <u21934@.uwe> wrote in message news:6066962294ae1@.uwe...
> Thanks Linchi
> Sorry that was just a typo. It not working with the i.
> osql -S server -d database -E -i file pathname
> fullertee
> Linchi Shea wrote:
>> osql -S server -d database -E -I file pathname
>>Change -I to -i
>>Linchi
>> I am new to batch files. I have a text file that has several INSERT/UPDATE
>> sql statements. I would like to create a batch file to execute the sql
>>[quoted text clipped - 5 lines]
>> I am using the following:
>> osql -S server -d database -E -I file pathname
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Thank you so much. Your question made me look at my bat file closer. I was
referencing the wrong database. Thanks! It's working now.
or Karaszi wrote:
>Error message? Can you post the exact command you execute?
>> Thanks Linchi
>> Sorry that was just a typo. It not working with the i.
>[quoted text clipped - 12 lines]
>> I am using the following:
>> osql -S server -d database -E -I file pathname
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1

execute query later

Hi NG
Im writing a web-interface that enables the user to update a DB. The
(non)queries takes heaps of time, and since my page "waits" for the query to
finish, wich is a pain. So basically I was wondering if there is kinda a
"update mytable set myrow=... LATER" kinda keyword or something, to make the
query execute whenever the sqlserver feels like it, but return "ok" right
away.
- Kasper"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
to
> finish, wich is a pain. So basically I was wondering if there is kinda a
> "update mytable set myrow=... LATER" kinda keyword or something, to make
the
> query execute whenever the sqlserver feels like it, but return "ok" right
> away.
> - Kasper
>
As far as I know, there is no direct way of doing this.
One thing comes to mind... Have a simple table where you can drop the
information quickly. A SQL Server job can come along and check the table
every few minutes and then perform the work.
There are some caveats however. What if the data is bad, or SQL Server has
an error attempting to process the data. You have already told the web-user
that everything is ok, but in reality it is not. How do you notify the
user that there were problems.
You may need to do some architecturual work to get this to work the way you
want it to.
Rick Sawtell
MCT, MCSD, MCDBA|||Implement the sql update as a stored procedure. If you are using an ADO /
ADO.NET connection, then look executing the SP asynchronously. The SP can
send an email notification back to the user when it completes.
"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
to
> finish, wich is a pain. So basically I was wondering if there is kinda a
> "update mytable set myrow=... LATER" kinda keyword or something, to make
the
> query execute whenever the sqlserver feels like it, but return "ok" right
> away.
> - Kasper
>|||Service Broker queue(s) come to mind. Need 2005 however or come up with
your own queue table.
William Stacey [MVP]
"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
> to finish, wich is a pain. So basically I was wondering if there is kinda
> a "update mytable set myrow=... LATER" kinda keyword or something, to make
> the query execute whenever the sqlserver feels like it, but return "ok"
> right away.
> - Kasper
>