MDF Files currupted: mystery

Showing posts with label mystery. Show all posts

Monday, March 19, 2012

mystery truncating

I have table in SQL Server 2000 that stores comments in one field of type
varchar with a length of 250. A test value entered is 249 characters long.
When I run the following:
select len(comment) from tblcomments where emplid = '241s'
the len returned is 249, as expected. However, once I try concatenating
date and user name info, funny stuff starts happening.
For example, if I run this code:
select len(Cast([Date] as varchar(128)) + ' - ' + NTUSERNAME + ' - ' +
Comment)
from tblcomments where emplid = '241S'
len returned is 284, which is correct. But when the code is run without
using the len() function the result returned has been truncated by 28
characters. I end up with a character output of only 256 characters. I get
the date, the ntusername and the first 221 characters of the comment sting.
For some reason, after concatenating the additional info I'm losing 28
characters and the output is limited to 256 characters. But again, when
using the len function I'm getting 284. What is happening? Any suggestions
?> characters. I end up with a character output of only 256 characters
Where, in QA?
Go to Tools - Options... - Results and increase "Maximum characters per
column". Max value allowed is 8K or 8192.
AMB
"kiloez" wrote:

> I have table in SQL Server 2000 that stores comments in one field of type
> varchar with a length of 250. A test value entered is 249 characters long
.
> When I run the following:
> select len(comment) from tblcomments where emplid = '241s'
> the len returned is 249, as expected. However, once I try concatenating
> date and user name info, funny stuff starts happening.
> For example, if I run this code:
> select len(Cast([Date] as varchar(128)) + ' - ' + NTUSERNAME + ' - ' +
> Comment)
> from tblcomments where emplid = '241S'
> len returned is 284, which is correct. But when the code is run without
> using the len() function the result returned has been truncated by 28
> characters. I end up with a character output of only 256 characters. I g
et
> the date, the ntusername and the first 221 characters of the comment sting
.
> For some reason, after concatenating the additional info I'm losing 28
> characters and the output is limited to 256 characters. But again, when
> using the len function I'm getting 284. What is happening? Any suggestio
ns?
>|||Alejandro, thank you. How can this be done when running the same code from
a
stored procedure? I don't see a similar option in Enterprise Mgr. The
problem was first noticed when running this code from a sp.
"Alejandro Mesa" wrote:
> Where, in QA?
> Go to Tools - Options... - Results and increase "Maximum characters per
> column". Max value allowed is 8K or 8192.
>
> AMB
> "kiloez" wrote:
>|||Where did you run the sp?
This is an internal setting of the client app (QA).
AMB
"kiloez" wrote:
> Alejandro, thank you. How can this be done when running the same code fro
m a
> stored procedure? I don't see a similar option in Enterprise Mgr. The
> problem was first noticed when running this code from a sp.
> "Alejandro Mesa" wrote:
>|||Alejandro, thanks again for replying. I found out what the problem was. I'
m
actually running this sp from a dataenvironment in VB6. After checking the
properties for the sp in the DE I saw that the length for the output
parameter was only 250. After changing it to 8000 output was as it should
be. The character length had already been changed in the sp itself.
"Alejandro Mesa" wrote:
> Where did you run the sp?
> This is an internal setting of the client app (QA).
>
> AMB
> "kiloez" wrote:
>

Mystery Table Delete!

I'm hoping someone out there can help me solve a mystery!
29 tables in our live database were deleted the other day
and we've no real idea how this could have happened.
We have found an entry in Application Event Viewer that
happened at about the same time as the tables vanished,
but we don't really understand what it means:
EventID: 17055
Categorey: (6)
Description: 3041: BACKUP failed to complete the command
dump tran [Claims] with no_log
I understand that this is saying that the command failed,
but where did the command come from? The user on the
event was creating new tables and replacing stored
procedures in the database at the time, however, [Claims]
is the name of the whole database!
We have recovered from the disaster now but really do need
to understand what has happened here, how it happened, and
how we can ensure it never happens again in the future.
Thanks in advance for any help.
Regards
PaulaCheck your web server logs, if there is web application connected. Check
your security logs...
"Pegasus" <anonymous@.discussions.microsoft.com> wrote in message
news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
> I'm hoping someone out there can help me solve a mystery!
> 29 tables in our live database were deleted the other day
> and we've no real idea how this could have happened.
> We have found an entry in Application Event Viewer that
> happened at about the same time as the tables vanished,
> but we don't really understand what it means:
> EventID: 17055
> Categorey: (6)
> Description: 3041: BACKUP failed to complete the command
> dump tran [Claims] with no_log
> I understand that this is saying that the command failed,
> but where did the command come from? The user on the
> event was creating new tables and replacing stored
> procedures in the database at the time, however, [Claims]
> is the name of the whole database!
> We have recovered from the disaster now but really do need
> to understand what has happened here, how it happened, and
> how we can ensure it never happens again in the future.
> Thanks in advance for any help.
> Regards
> Paula|||There is no web application connected (to my knowledge)
and nothing is showing in the security logs.
Using SQL Server 2000.
Has been suggested that using Enterprise Manager and the
DTS Import/Export Wizard to move objects could have caused
the tables to be dropped? Any value in this theory?
>--Original Message--
>Check your web server logs, if there is web application
connected. Check
>your security logs...
>"Pegasus" <anonymous@.discussions.microsoft.com> wrote in
message
>news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
>> I'm hoping someone out there can help me solve a
mystery!
>> 29 tables in our live database were deleted the other
day
>> and we've no real idea how this could have happened.
>> We have found an entry in Application Event Viewer that
>> happened at about the same time as the tables vanished,
>> but we don't really understand what it means:
>> EventID: 17055
>> Categorey: (6)
>> Description: 3041: BACKUP failed to complete the command
>> dump tran [Claims] with no_log
>> I understand that this is saying that the command
failed,
>> but where did the command come from? The user on the
>> event was creating new tables and replacing stored
>> procedures in the database at the time, however,
[Claims]
>> is the name of the whole database!
>> We have recovered from the disaster now but really do
need
>> to understand what has happened here, how it happened,
and
>> how we can ensure it never happens again in the future.
>> Thanks in advance for any help.
>> Regards
>> Paula
>
>.
>|||Probably the best way to see might be to get the tool from Lumigent (
www.lumigent.com)...
Give them a call and see if the tool will help in your situation.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Pegasus" <anonymous@.discussions.microsoft.com> wrote in message
news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
> I'm hoping someone out there can help me solve a mystery!
> 29 tables in our live database were deleted the other day
> and we've no real idea how this could have happened.
> We have found an entry in Application Event Viewer that
> happened at about the same time as the tables vanished,
> but we don't really understand what it means:
> EventID: 17055
> Categorey: (6)
> Description: 3041: BACKUP failed to complete the command
> dump tran [Claims] with no_log
> I understand that this is saying that the command failed,
> but where did the command come from? The user on the
> event was creating new tables and replacing stored
> procedures in the database at the time, however, [Claims]
> is the name of the whole database!
> We have recovered from the disaster now but really do need
> to understand what has happened here, how it happened, and
> how we can ensure it never happens again in the future.
> Thanks in advance for any help.
> Regards
> Paula|||Try a log tool like LogExplorer from www.lumigent.com or ApexSQL Log from
www.apexsql.com to trawl trough your transaction logs and see what happened.
--
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:216db01c45aac$4cc30320$a401280a@.phx.gbl...
> There is no web application connected (to my knowledge)
> and nothing is showing in the security logs.
> Using SQL Server 2000.
> Has been suggested that using Enterprise Manager and the
> DTS Import/Export Wizard to move objects could have caused
> the tables to be dropped? Any value in this theory?
>
> >--Original Message--
> >Check your web server logs, if there is web application
> connected. Check
> >your security logs...
> >
> >"Pegasus" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
> >> I'm hoping someone out there can help me solve a
> mystery!
> >>
> >> 29 tables in our live database were deleted the other
> day
> >> and we've no real idea how this could have happened.
> >>
> >> We have found an entry in Application Event Viewer that
> >> happened at about the same time as the tables vanished,
> >> but we don't really understand what it means:
> >> EventID: 17055
> >> Categorey: (6)
> >> Description: 3041: BACKUP failed to complete the command
> >> dump tran [Claims] with no_log
> >>
> >> I understand that this is saying that the command
> failed,
> >> but where did the command come from? The user on the
> >> event was creating new tables and replacing stored
> >> procedures in the database at the time, however,
> [Claims]
> >> is the name of the whole database!
> >>
> >> We have recovered from the disaster now but really do
> need
> >> to understand what has happened here, how it happened,
> and
> >> how we can ensure it never happens again in the future.
> >>
> >> Thanks in advance for any help.
> >>
> >> Regards
> >>
> >> Paula
> >
> >
> >.
> >|||Hi,
There is only Copy objects option in DTS and this will just copy the objects
to destination.
What recovery model you are using for this database, If it is FULL or
Bulk_logged and if you have trasnaction logs you can use the Log explorer
tool to analyze the situation which caused the object drop.
www.lumigent.com
If you do not have the transaction log backup, it is very difficult to find
the cause for the object drop.
--
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:216db01c45aac$4cc30320$a401280a@.phx.gbl...
> There is no web application connected (to my knowledge)
> and nothing is showing in the security logs.
> Using SQL Server 2000.
> Has been suggested that using Enterprise Manager and the
> DTS Import/Export Wizard to move objects could have caused
> the tables to be dropped? Any value in this theory?
>
> >--Original Message--
> >Check your web server logs, if there is web application
> connected. Check
> >your security logs...
> >
> >"Pegasus" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
> >> I'm hoping someone out there can help me solve a
> mystery!
> >>
> >> 29 tables in our live database were deleted the other
> day
> >> and we've no real idea how this could have happened.
> >>
> >> We have found an entry in Application Event Viewer that
> >> happened at about the same time as the tables vanished,
> >> but we don't really understand what it means:
> >> EventID: 17055
> >> Categorey: (6)
> >> Description: 3041: BACKUP failed to complete the command
> >> dump tran [Claims] with no_log
> >>
> >> I understand that this is saying that the command
> failed,
> >> but where did the command come from? The user on the
> >> event was creating new tables and replacing stored
> >> procedures in the database at the time, however,
> [Claims]
> >> is the name of the whole database!
> >>
> >> We have recovered from the disaster now but really do
> need
> >> to understand what has happened here, how it happened,
> and
> >> how we can ensure it never happens again in the future.
> >>
> >> Thanks in advance for any help.
> >>
> >> Regards
> >>
> >> Paula
> >
> >
> >.
> >|||Thanks for your input. I have already got information
about LogExplorer from www.lumigent.com and we're looking
into purchasing this product for use in the future.
Unfortunately the log file no longer exists (don't ask!!!)
so this software would be of no use on this occasion. Is
there any other way forward on this?
Could the DTS Import/Export Wizard have caused this much
destruction by copying over 2 new tables and 3 existing
stored procedures from a development copy of the
database? The user insists that the objects were not
transferred to the live database using this Wizard. Even
if "dependant objects" was selected, could there be a
scenario where SQL would have dropped all tables
referenced in the SP's and all tables related to those
tables? If this is possible, can we disable the use of
DTS Import/Export wizard in Enterprise Manager for ALL
users?
>--Original Message--
>I'm hoping someone out there can help me solve a mystery!
>29 tables in our live database were deleted the other day
>and we've no real idea how this could have happened.
>We have found an entry in Application Event Viewer that
>happened at about the same time as the tables vanished,
>but we don't really understand what it means:
>EventID: 17055
>Categorey: (6)
>Description: 3041: BACKUP failed to complete the command
>dump tran [Claims] with no_log
>I understand that this is saying that the command failed,
>but where did the command come from? The user on the
>event was creating new tables and replacing stored
>procedures in the database at the time, however, [Claims]
>is the name of the whole database!
>We have recovered from the disaster now but really do
need
>to understand what has happened here, how it happened,
and
>how we can ensure it never happens again in the future.
>Thanks in advance for any help.
>Regards
>Paula
>.
>|||Thanks for your input.
No, we don't have the transaction log or a backup of it
(don't ask!!!) Is there another way to see what's
happened? There were 29 tables dropped in all so this
had very serious consequences and now we're trying to
ensure this doesn't happen again, but, without knowing
what DID happen we can't safeguard against it happening
again.
>--Original Message--
>Hi,
>There is only Copy objects option in DTS and this will
just copy the objects
>to destination.
>What recovery model you are using for this database, If
it is FULL or
>Bulk_logged and if you have trasnaction logs you can use
the Log explorer
>tool to analyze the situation which caused the object
drop.
>www.lumigent.com
>If you do not have the transaction log backup, it is very
difficult to find
>the cause for the object drop.
>--
>Thanks
>Hari
>MCDBA
><anonymous@.discussions.microsoft.com> wrote in message
>news:216db01c45aac$4cc30320$a401280a@.phx.gbl...
>> There is no web application connected (to my knowledge)
>> and nothing is showing in the security logs.
>> Using SQL Server 2000.
>> Has been suggested that using Enterprise Manager and the
>> DTS Import/Export Wizard to move objects could have
caused
>> the tables to be dropped? Any value in this theory?
>>
>> >--Original Message--
>> >Check your web server logs, if there is web application
>> connected. Check
>> >your security logs...
>> >
>> >"Pegasus" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:212c301c45a9e$73c9e3c0$a301280a@.phx.gbl...
>> >> I'm hoping someone out there can help me solve a
>> mystery!
>> >>
>> >> 29 tables in our live database were deleted the other
>> day
>> >> and we've no real idea how this could have happened.
>> >>
>> >> We have found an entry in Application Event Viewer
that
>> >> happened at about the same time as the tables
vanished,
>> >> but we don't really understand what it means:
>> >> EventID: 17055
>> >> Categorey: (6)
>> >> Description: 3041: BACKUP failed to complete the
command
>> >> dump tran [Claims] with no_log
>> >>
>> >> I understand that this is saying that the command
>> failed,
>> >> but where did the command come from? The user on
the
>> >> event was creating new tables and replacing stored
>> >> procedures in the database at the time, however,
>> [Claims]
>> >> is the name of the whole database!
>> >>
>> >> We have recovered from the disaster now but really do
>> need
>> >> to understand what has happened here, how it
happened,
>> and
>> >> how we can ensure it never happens again in the
future.
>> >>
>> >> Thanks in advance for any help.
>> >>
>> >> Regards
>> >>
>> >> Paula
>> >
>> >
>> >.
>> >
>
>.
>

Mystery Table Delete!

Mystery Proccess

I have set up a job on one of my sql servers to inform me
of any processes that run for more than a specified
amount of time. It eails me and the message tells me the
SPID, Host name, login and duration. A proccess keeps on
cropping up that i cannot account for so is causing me
concern. This procces is a bulk insert, the host is one of
out test servers, it is issued using dts designer and the
login used is for the server that the job is running on. I
also return the last TSQL command issued using dbcc
inputbuffer but this is blank. I have looked on the test
server and there are no jobs.
Can any one suggest a way that i can trace the source of
this processI have never realy used SQL profier before, can you
suggest which event i should be tracing
Cheers
>--Original Message--
>Hi,
>Start a trace with SQL Profiler. You can even specify the
minimum duration
>for a statement before it is show in the trace.
>--
>Karl Gram
>"Mat" wrote:
>> I have set up a job on one of my sql servers to inform
me
>> of any processes that run for more than a specified
>> amount of time. It eails me and the message tells me
the
>> SPID, Host name, login and duration. A proccess keeps
on
>> cropping up that i cannot account for so is causing me
>> concern. This procces is a bulk insert, the host is one
of
>> out test servers, it is issued using dts designer and
the
>> login used is for the server that the job is running
on. I
>> also return the last TSQL command issued using dbcc
>> inputbuffer but this is blank. I have looked on the
test
>> server and there are no jobs.
>> Can any one suggest a way that i can trace the source
of
>> this process
>>
>.
>|||Solved the problem..
Maybe you may wish to know the cause...
a dts package was moved from the test server to the
production server. The connectins were changed to reflect
this but the name was left as the original name (test
server)
For what ever reason, sp_who, current activity and SQL
profiler pick up the conection name as the host name for
proccesss created by scheduled dts packages.
>--Original Message--
>Mat,
>From the 4 default events classes only the Stored
Procedures (RPC:Completed)
>and TSQL (SQL:BatchCompleted) events should be enough.
The rest should be
>removed.
>--
>Karl Gram
>"mat" wrote:
>> I have never realy used SQL profier before, can you
>> suggest which event i should be tracing
>> Cheers
>> >--Original Message--
>> >Hi,
>> >
>> >Start a trace with SQL Profiler. You can even specify
the
>> minimum duration
>> >for a statement before it is show in the trace.
>> >
>> >--
>> >Karl Gram
>> >
>> >"Mat" wrote:
>> >
>> >> I have set up a job on one of my sql servers to
inform
>> me
>> >> of any processes that run for more than a specified
>> >> amount of time. It eails me and the message tells me
>> the
>> >> SPID, Host name, login and duration. A proccess
keeps
>> on
>> >> cropping up that i cannot account for so is causing
me
>> >> concern. This procces is a bulk insert, the host is
one
>> of
>> >> out test servers, it is issued using dts designer
and
>> the
>> >> login used is for the server that the job is running
>> on. I
>> >> also return the last TSQL command issued using dbcc
>> >> inputbuffer but this is blank. I have looked on the
>> test
>> >> server and there are no jobs.
>> >>
>> >> Can any one suggest a way that i can trace the
source
>> of
>> >> this process
>> >>
>> >>
>> >.
>> >
>.
>|||Hi,
Start a trace with SQL Profiler. You can even specify the minimum duration
for a statement before it is show in the trace.
--
Karl Gram
"Mat" wrote:
> I have set up a job on one of my sql servers to inform me
> of any processes that run for more than a specified
> amount of time. It eails me and the message tells me the
> SPID, Host name, login and duration. A proccess keeps on
> cropping up that i cannot account for so is causing me
> concern. This procces is a bulk insert, the host is one of
> out test servers, it is issued using dts designer and the
> login used is for the server that the job is running on. I
> also return the last TSQL command issued using dbcc
> inputbuffer but this is blank. I have looked on the test
> server and there are no jobs.
> Can any one suggest a way that i can trace the source of
> this process
>|||Mat,
From the 4 default events classes only the Stored Procedures (RPC:Completed)
and TSQL (SQL:BatchCompleted) events should be enough. The rest should be
removed.
--
Karl Gram
"mat" wrote:
> I have never realy used SQL profier before, can you
> suggest which event i should be tracing
> Cheers
> >--Original Message--
> >Hi,
> >
> >Start a trace with SQL Profiler. You can even specify the
> minimum duration
> >for a statement before it is show in the trace.
> >
> >--
> >Karl Gram
> >
> >"Mat" wrote:
> >
> >> I have set up a job on one of my sql servers to inform
> me
> >> of any processes that run for more than a specified
> >> amount of time. It eails me and the message tells me
> the
> >> SPID, Host name, login and duration. A proccess keeps
> on
> >> cropping up that i cannot account for so is causing me
> >> concern. This procces is a bulk insert, the host is one
> of
> >> out test servers, it is issued using dts designer and
> the
> >> login used is for the server that the job is running
> on. I
> >> also return the last TSQL command issued using dbcc
> >> inputbuffer but this is blank. I have looked on the
> test
> >> server and there are no jobs.
> >>
> >> Can any one suggest a way that i can trace the source
> of
> >> this process
> >>
> >>
> >.
> >
>

Mystery Database IDs in Profiler

I'm currently running Profiler sessions to track down Lock Timeout
problems.

My Profiler view contains (amongst others) the dbid column.

Much of the time, this displays familiar dbids, such as 2 (tempdb) and
5 (my main user db). However, it also regularly displays IDs of 0 and
132.

Using SELECT DB_NAME(), these translate as "master" and "NULL"
respectively.

Does anyone know:
a) why dbid = 0 translates to "master", when the actual id of this
database is 1, and
b) why Profiler reports these dbids in the first place?Hi

Database ID is a default data column and should be populated for all events.
What other columns are you capturing? Which event is displaying this value?

Not directly related to your question but these may help:
http://support.microsoft.com/defaul...kb;en-us;832524
http://msdn.microsoft.com/library/d...atabse_5xrn.asp
http://support.microsoft.com/kb/271509/EN-US/

John

"Phil" <philip.yale@.gmail.com> wrote in message
news:1109931995.630468.189250@.g14g2000cwa.googlegr oups.com...
> I'm currently running Profiler sessions to track down Lock Timeout
> problems.
> My Profiler view contains (amongst others) the dbid column.
> Much of the time, this displays familiar dbids, such as 2 (tempdb) and
> 5 (my main user db). However, it also regularly displays IDs of 0 and
> 132.
> Using SELECT DB_NAME(), these translate as "master" and "NULL"
> respectively.
> Does anyone know:
> a) why dbid = 0 translates to "master", when the actual id of this
> database is 1, and
> b) why Profiler reports these dbids in the first place?|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<42299950$0$8744$db0fefd9@.news.zen.co.uk>...
> Hi
> Database ID is a default data column and should be populated for all events.
> What other columns are you capturing? Which event is displaying this value?

Have variously tried capturing just the columns I thought I needed,
and then finally capturing every column possible just in case this
altered any output!

The only event I'm capturing is the LOCK TIMEOUT event. I've since
noticed that these lock timeouts (with the dbid equal to 0 or 255) all
have the same lock type (which I can't remember right now, and I'm
nowhere that I can look it up conveniently), which essentially
translates to a "key range test for a record insert". i.e. they're
not proper locks at all, and so the timeout is almost certainly an
internal mechanism for releasing them

I was just intrigued why these were associated with a dbid at all, why
0 and 255 was chosen, and why 0 additionally translates to 'master'.

BTW - genuine lock timeouts correctly display valid database IDs.

> Not directly related to your question but these may help:
> http://support.microsoft.com/defaul...kb;en-us;832524
> http://msdn.microsoft.com/library/d...atabse_5xrn.asp
> http://support.microsoft.com/kb/271509/EN-US/
>
> John
> "Phil" <philip.yale@.gmail.com> wrote in message
> news:1109931995.630468.189250@.g14g2000cwa.googlegr oups.com...
> > I'm currently running Profiler sessions to track down Lock Timeout
> > problems.
> > My Profiler view contains (amongst others) the dbid column.
> > Much of the time, this displays familiar dbids, such as 2 (tempdb) and
> > 5 (my main user db). However, it also regularly displays IDs of 0 and
> > 132.
> > Using SELECT DB_NAME(), these translate as "master" and "NULL"
> > respectively.
> > Does anyone know:
> > a) why dbid = 0 translates to "master", when the actual id of this
> > database is 1, and
> > b) why Profiler reports these dbids in the first place?|||Hi

It sounds like the INDID which will can be 0 or 255.

John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Mysterious mystery -- 2002-11-08/2002-11-09

I have function that returns a table of information about properties. The
data comes from three different tables -- addresses (called PropertyID),
property characteristics, and events concerning those properties (sales,
appraisals, etc.), plus a table that maps one representation of property
types into another. The records are selected on the basis of location
(longitude & latitude), property type, event type, and a range of
event dates (upper and lower date specified). There are tens of millions
of records of all types, and almost any location, property type, event type
and date range will yield records.

The heart of it is a cursor that selects records from joins on this basis:

SELECT <a bunch of fields>
FROM Property d
JOIN PropTypeMap ptm ON ptm.PropertyTypeID = d.PropertyTypeID
JOIN PropertyID a ON a.PropID = d.PropID
JOIN Event e1 ON e1.PropID = d.PropID
LEFT OUTER JOIN Event e2 ON e2.PropID = d.PropID
WHERE
d.LastSaleDate >= @.LoDate
AND a.GeoLongitude BETWEEN @.LowerLon AND @.UpperLon
AND a.GeoLatitude BETWEEN @.LowerLat AND @.UpperLat
AND ptm.PropCategory = @.PropType
AND a.GeoMatch <= @.MinGeoQuality

AND e1.EventTypeID = @.SaleEventType
AND e1.TransactionType = 'R'
AND e1.EventDt BETWEEN @.LoDate AND @.HiDate
AND e1.EventAmt > 0

AND e2.EventTypeID = @.AssessmentEventType
AND e2.EventDt <= @.HiDate
AND e2.EventAmt > 0

Each property has one PropertyID record, one Property record, and N Event
records (average perhaps five).

What is the mystery? If @.HiDate, which is the upper end of the time window,
is 2002-11-08 or earlier, nothing is returned. If it's 2002-11-09 or later,
oodles of records are found. I get the same query plan for either one, and
based on the content of the data, they should return almost exactly the same
set of records -- exactly the same set in almost all cases, in fact.

Is 2002-11-08/09 some sort of magic dividing point? I have replicated this
on the large database and on a smaller test version on another SQL Server.
(SQL Server 2000) I dropped the indexes and tried it, and
the same thing happened. This is driving me crazy![posted and mailed, please reply in news]

Jim Geissman (jim_geissman@.countrywide.com) writes:
> What is the mystery? If @.HiDate, which is the upper end of the time
> window, is 2002-11-08 or earlier, nothing is returned. If it's
> 2002-11-09 or later, oodles of records are found. I get the same query
> plan for either one, and based on the content of the data, they should
> return almost exactly the same set of records -- exactly the same set in
> almost all cases, in fact.
> Is 2002-11-08/09 some sort of magic dividing point? I have replicated
> this on the large database and on a smaller test version on another SQL
> Server. (SQL Server 2000) I dropped the indexes and tried it, and the
> same thing happened. This is driving me crazy!

Without access to the database, or a script that reproduces the problem,
there is much left for me, but to guess.

And the obvious guess is that the rows you find are from 2002-11-08, but
have a time porttion, and thus are > '2002-11-08 00:00:00'.

However, I notice one thing with your query which may not be correct:

LEFT OUTER JOIN Event e2 ON e2.PropID = d.PropID
WHERE ...
AND e2.EventTypeID = @.AssessmentEventType
AND e2.EventDt <= @.HiDate
AND e2.EventAmt > 0

The idea with a left outer join is that you include rows in the left
table, even if there is no matching rows in the right table. The
columns in the right table are then set to NULL.

Now, the FROM clause is (logically) evaluated before the WHERE clause,
which acts on the filter on the table defined by the FROM clause. This
means that you will filter away those rows with NULL in e2.EventTypeID
that comes from the outer join. Your join is now effectively an inner
join.

There are two ways to handle this:
1) Add conditions with OR clauses or coalesce to handle NULL in the WHERE
clause.
2) Move the conditions to the ON clause.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland. You hit the nail on the head. I was naively assuming that
the right side of the Left Outer Join didn't matter, but when I removed the
second condition in the excerpt below, the records all appeared. As you
said, it was because the FROM was being evaluated before the WHERE. Moving
the condition to ON fixed it. Thanks. I wonder if I have any other
scripts that make this mistake...

> However, I notice one thing with your query which may not be correct:
> LEFT OUTER JOIN Event e2 ON e2.PropID = d.PropID
> WHERE ...
> AND e2.EventTypeID = @.AssessmentEventType
> AND e2.EventDt <= @.HiDate
> AND e2.EventAmt > 0
> The idea with a left outer join is that you include rows in the left
> table, even if there is no matching rows in the right table. The
> columns in the right table are then set to NULL.
> Now, the FROM clause is (logically) evaluated before the WHERE clause,
> which acts on the filter on the table defined by the FROM clause. This
> means that you will filter away those rows with NULL in e2.EventTypeID
> that comes from the outer join. Your join is now effectively an inner
> join.
> There are two ways to handle this:
> 1) Add conditions with OR clauses or coalesce to handle NULL in the WHERE
> clause.
> 2) Move the conditions to the ON clause.|||Jim Geissman (jim_geissman@.countrywide.com) writes:
> Thanks, Erland. You hit the nail on the head. I was naively assuming
> that the right side of the Left Outer Join didn't matter, but when I
> removed the second condition in the excerpt below, the records all
> appeared. As you said, it was because the FROM was being evaluated
> before the WHERE. Moving the condition to ON fixed it. Thanks. I
> wonder if I have any other scripts that make this mistake...

A very common mistake to make. I recall that I did it myself a few
times when I switched to the new syntax from the old *= crap.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Monday, March 12, 2012

mysterious corruption of stored procedures

Hi everyone,

It looks like a mystery, but I hope there should be some explanation to
the issue I experience. Once in a blue moon a random stored procedure
stops working the way it was designed. The stored procedure code looks
unchanged. Recompiling, altering the code do not help. It looks like it
is simply does not execute some part of it or does not run at all.
However it returns no errors.
One time a procedure entered into infinite loop and almost hang the
whole server.

When I copy procedure code and save it under different name, it works
as designed. But nothing helps with existing procedure. The only way
how to fix it is to completely drop and recreate.

The problem is, that you usually have to do it in the middle of the
business day after you spent few hours trying to realize what went
wrong before you realize that you got another mysterious corruption. Of
cause I have no clue of how to detect such things in advance and to
prevent them from occurring in the future.

I can guarantee that the SQL code in those procedures was absolutely
bug free, fully tested and was working fine for a long time.

For the first time I thought that internal compiled code might corrupt.
In this case altering or recompiling should help.
I also thought about execution plan, but it should be also fixed by
doing things above.
DBCC checkdb does not find any errors
The issue never goes away until stored procedure is manually dropped
and recreated with the same SQL code.

So, I'm asking all if someone experienced something similar and can
explain how to prevent it, please share the knowledge. I would
appreciate any type of help.

Thank you.Sergey (afanas01@.gmail.com) writes:
> It looks like a mystery, but I hope there should be some explanation to
> the issue I experience. Once in a blue moon a random stored procedure
> stops working the way it was designed. The stored procedure code looks
> unchanged. Recompiling, altering the code do not help. It looks like it
> is simply does not execute some part of it or does not run at all.
> However it returns no errors.
> One time a procedure entered into infinite loop and almost hang the
> whole server.
> When I copy procedure code and save it under different name, it works
> as designed. But nothing helps with existing procedure. The only way
> how to fix it is to completely drop and recreate.

Have you ruled out the possibility there are two procedures with the same
name, but different schema? (= different owner in SQL 2000?) Or for some-
thing really exotic, what about numbered procedures? (That is, some_sp;2)

If you for instance change the code of the corrupted procedure to say
"PRINT 'Hello!" and nothing else, does print the message, or does it
continue what it used to do?

When this happens a SELECT * FROM sysobjects WHERE name = 'procname'
may reveal something.

--
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|||No numbered procedures, no different schema/owner. Everything is "plain
vanilla". Unfortunately, I did not have chance to play with corrupted
procedures.Usually when you have a production problem everybody whant
a) have it fixed ASAP and only then b) provide them with reasonable
explanation. I have a problm with b) ;-)|||Sergey (afanas01@.gmail.com) writes:
> No numbered procedures, no different schema/owner. Everything is "plain
> vanilla". Unfortunately, I did not have chance to play with corrupted
> procedures.Usually when you have a production problem everybody whant
> a) have it fixed ASAP and only then b) provide them with reasonable
> explanation. I have a problm with b) ;-)

I would still encourage you to examine sysobjects next time it happens.
Maybe there is something in your system, you did not know of. :-)

--
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|||You must be right. I will definitely check this next time. This morning
I restored my DB to the point before the fix, but as I was expecting
everything was OK.Next time I will rename the corrupted procedure and
create a new one with old name (to get customer support drop off my
shoulders ;-). After that I will have plenty of time to have that thing
cracked.
I just hoped that maybe someone already know about this problem, so I
could learn faster, but thank you anyway. I can tell you if you'd like
when I get that solved.
Thanks again|||You must be right. I will definitely check this next time. This morning
I restored my DB to the point before the fix, but as I was expecting
everything was OK.Next time I will rename the corrupted procedure and
create a new one with old name (to get customer support drop off my
shoulders ;-). After that I will have plenty of time to have that thing
cracked.
I just hoped that maybe someone already know about this problem, so I
could learn faster, but thank you anyway. I can tell you if you'd like
when I get that solved.
Thanks again

MySQL vs. MS SQL Server. What's the difference?

I'm a beginner programmer and databases are still a bit of a mystery to me. I have some basic questions that I can't find direct answers for anywhere but which I think should be fairly simple for someone acquainted with using databases in programming.

1. MySQL & MS SQL Server (or Express)...what's the difference? Are they mutually compatible? Are all SQL files written in the same file format?

2. If I write create a MySQL database on my website to store customer data, can my application which was written with VB 2005 Express (and which uses MS SQL Server Express) read those database files? Vice-versa?

3. If I create a MS SQL database in VB express for my application, is that database on some huge central server somewhere or is my computer used as the server, or what? (yeah, I know that's probably a really stupid question but I can find the answer in any of the three programming books I've bought in recent months or online either.)

4. I've read the MySQL is free unless I use the databases within an actual application from which I'm making money, in which case I need to buy a license. What about MS SQL Server or MS SQL Express? Microsoft can be so withholding about what costs money, what is free, and what is temporarily free.

That's all for now...I hope someone can answer my stupid questions.

1. No the file types are not compatible, they are from different vendors. Even the SQL symtax differ but shares a common dictionary the SQL Ansi standard.

2. That depends on how you did your database layer implementation. Some developers use the generic adapter approach making database capable for many database whereas only the adapter using to connect to the database has to be exchange. if you are using the SQL* classes in youa application and not the e.g. OLEDb like olecbcommand you cannot switch to another database within your application.

3. The SQL Server editions (beside the compact edition) run as services. They do not have to run on a "central computer" and can be based on normal pcs (depending on the edition you need a server OS to install the services like for enterprise edition) The SQL Server express editions were designed to run on normal worksatation having a local database stored on the computer.

4. All edition beside the SQL Server Express and the Compact editions have to be licensed. they either go by the Server/cal or the processor licence. SQL Server Express and Comnpact edition are free not matter if you shipping them with your applications or using them privately.

See the frature comparisons on this site for more information wheter SQL Server Express fits your needs, as it has some limitations:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

No stupid question at all :-)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Hey Jens, thank you so much for answering my questions.

Monday, March 19, 2012

mystery truncating

Mystery Table Delete!

Mystery Table Delete!

Mystery Table Delete!

Mystery Proccess

Mystery Proccess

Mystery Database IDs in Profiler

Mysterious mystery -- 2002-11-08/2002-11-09

Monday, March 12, 2012

mysterious corruption of stored procedures

MySQL vs. MS SQL Server. What's the difference?

MDF Files currupted

Blog Archive

About Me