Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
padohmen
Starting Member
3 Posts |
 Posted - 2006-11-03 : 06:37:35
|
| I work with SQL server 2005 now. Updated it from sql 2000. Most of my SQL 'work' executes in homemade stored procedures.I have one fairly simpel but crucial table, called administrations. I never update this table programmaticly, nore I did this in the past. I created and filled it manualy.For some reason (I discoverd by tracing) somewhere in the middel of my in vb6 programmed proces, sp_executesql starts automaticly updating and messes up my table. I never consiously use this system storedprocedure. My guess is that it has something to do with a automaticly started recovery proces for a 'for ever what reason???'It leaves me with no warning, but a messed up table.Can somebody give me a clue why this sp_executesql starts firing the next statement?exec sp_executesql N'UPDATE "reports".."administrations" SET "Administration_Name"=@P1 WHERE "Administration_Name"=@P2',N'@P1 nvarchar(1000),@P2 nvarchar(1000)',N'051 - blah.1',N'950 - blah.1'For me it's verry important to find a solution so I'll be verry gratefull.Thanks in advance,Paul DohmenThe Netherlands |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-11-03 : 08:25:29
|
| Profiler will tell you all kinds of things about who/where/when and how queries are run. Find out who is running that query and go ask them about it.Jay White |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-11-03 : 08:36:14
|
| It seems funny that the statement got into the "middel of my in vb6 programmed proces"without any human intervention !?! |
|
|
|
padohmen
Starting Member
3 Posts |
Posted - 2006-11-03 : 09:19:28
|
| I know. It's strange. But true. Hmm.. no human intervention is relative ofcause, because there are all kind of events that firethat are programmed by a human carrying my name. Including inserts, updates and selects. But there is for shure no update or insert statement on the administrations table. I didn't know about the existance of the sp_Execsql procedure so I'm for shure not using it in code.In the trace file you can see the column "application name" = Visual BasicDoes this exclude the possibility that some recovery proces from SQL server is taking place?Paul |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-11-03 : 09:54:48
|
| It is not some automatic recovery process.Search the code for any references to "sp_executesql".What is the "LoginName" in the trace, the same as your App?If you include the "HostName" column in your trace, it might give clues from where the query is executed.Check your security setup.Which users&logins are able to update the table?If someone is able to do illegal updates to a table, then the security is not good enough.rockmoose |
|
|
|
padohmen
Starting Member
3 Posts |
Posted - 2006-11-04 : 04:46:18
|
| Ok. Thank you. I'll run some more test and use the trace tool. But I can not get to the machine on the moment. But I'll keep you updated.This is something realy weird. It is not somebody else doing this. It's the vb application proces. But not run by code. Sorry it's so vague. Drives me crazy,But thanks for the help,Paul |
|
|
|
|
|
|
|
|
sp_executesql updates my table un wanted. How can?