Problem Wit Sp_execute SQL

Mar 30, 2007

I have to make a large number of updates (about 29k) so I generated teh update statements into a table and am trying to sue sp_executesql to run them. Here is my code:

Declare @SQLState NVARCHAR(500)

Declare Code Cursor
for
select SQLState from updates
open Code
FETCH NEXT FROM Code
into @SQLState
While @@fetch_Status = 0
Begin
Exec sp_executesql @SQLState

FETCH NEXT FROM Code
END

CLOSE Code
DEALLOCATE Code

IT appears to run succesfully, but the updates never happen - I get the following results for each update line:

UPDATE REEmployeeEvent SET UpdatedByEmployeeID= '00013' Where UpdatedByEmployeeID='00279'

(1 row(s) affected)

(0 row(s) affected)

Any ideas what I am doing wrong?

BTW - If I run the statements manually, they do work.

Thanks for any help!

View 1 Replies


ADVERTISEMENT

What's Wrong In My Script? -sp_execute

Jun 14, 1999

I cannot find bug. would you please help me? Thanks in advance,





declare @name nvarchar(20)
set @name = 'a'

DECLARE @sql nvarchar(2000)
, @para nvarchar(100)

set @para = N'@name nvarchar(20)'

set @sql = N'CREATE TABLE @name (
id int identity
, c1 char(1)
)'

select @sql
EXEC sp_executesql @sql, @para, @name

--
Regards,
Wonhyuk William Chung

View 1 Replies View Related

Syntax Of Sp_prepexec, Sp_execute

Jul 20, 2005

The following was captured from SQL Profiler during an interaction betweenAccess Xp and SQL Server 2000:declare @P1 intset @P1=1exec sp_prepexec @P1 output, N'@P1 nvarchar(50)', N'SELECT"totalid","FPSID","IFPSSYSID","FAMLNAME","OTHAGENID","FromFPW","prevdown","tempifpssysid","upsize_ts"FROM "dbo"."Family"WHERE "totalid" = @P1', N'-:1044463096'select @P1goI think I understand that sp_prepexec prepares a parameterized query with@P1 as a variable...but sp_prepexec is not in Books Online...could someone please explain the syntax of sp_prepexec?why is @P1 used for both the parameter within the select query and the nameof the prepared statement?the other seemingly strange thing, is that when I run the above in QueryAnalyzer and try to use the parameterized query like soexec sp_execute 1, N'-:1044463096'goI receive the error messageCould not find prepared statement with handle 1

View 1 Replies View Related

Deny Execute On Sp_prepare Or Sp_execute

Mar 11, 2003

I have a database X where user A has db_datareader role. User A can select data but cannot update/insert/delete.

Recently user A connected to my database using Brio SQRW tool and could successfully run an update command in database X. As I researched I found out that SQRW uses RPC calls with extended procedures sp_prepare and sp_execute to run an update command.

User A does not have explicit permission to either of these stored procedures. Additionally User A does not have access to master database where there procedures reside.

How can I prevent user A from updating my data?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved