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
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
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
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