Column values Deleted - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Column values Deleted

Author

Topic

chriztoph
Posting Yak Master

184 Posts

Posted - 2010-05-11 : 22:45:38

hi everyone!

Is there a case that SQL 2008 delete a table column's value(records)?

i have a table refZones with records


ZoneID  ZoneName
1	RECRUITMENT CORNER
2	SHARP NEWS

but then when i checked this table the day after I encode the ZoneName
then the ZoneName Column has no values..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 01:26:50

There must be a process that is changing the data.
update statement
- from application
- in a stored procedure
- in a trigger
or whatever.

No, you're never too old to Yak'n'Roll if you're too young to die.

chriztoph
Posting Yak Master

184 Posts

Posted - 2010-05-13 : 04:29:08

This is what i've got as of now:

i ran this query to check the lates run of the queries;


SELECT * , dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

the i saw this query that i don't know where this query is
and i don't understand the set part of the query..


update vwZonesQuery 
 set ZoneName=SUBSTRING(ZoneName,0,CHARINDEX(char(60)+char(47)+char(116)+char(105)+char(116)+char(108)+char(101)+char(62),cast(ZoneName as varchar(8000)))-0)

any idea of this?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-13 : 06:15:26

char(60)+char(47)+char(116)+char(105)+char(116)+char(108)+char(101)+char(62)
gives:</title>

Means the substring command is searching for </title> and if this isn't inside the ZoneName then the value gets deleted by the set statement.

For example try this:
select
SUBSTRING('Testtext',0,CHARINDEX(char(60)+char(47)+char(116)+char(105)+char(116)+char(108)+char(101)+char(62),cast('Testtext' as varchar(8000)))-0)

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-13 : 06:48:09

The correct update should look like this:


update vwZonesQuery 
 set ZoneName=
SUBSTRING(
ZoneName,
1,
case
when
CHARINDEX(char(60)+char(47)+char(116)+char(105)+char(116)+char(108)+char(101)+char(62),cast(ZoneName as varchar(8000)))
=0 then len(cast(ZoneName as varchar(8000)))
else CHARINDEX(char(60)+char(47)+char(116)+char(105)+char(116)+char(108)+char(101)+char(62),cast(ZoneName as varchar(8000)))-1
end
)

No, you're never too old to Yak'n'Roll if you're too young to die.

chriztoph
Posting Yak Master

184 Posts

Posted - 2010-05-13 : 20:48:05

but I don't know where to find this qeury..I didn't create this even the programmer did not..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources