Data Source View of Mining Structure cant be edited?

Hi, all here,

Thank you very much for your kind attention.

I got a quite a strange problem with Mining structure for OLAP data source though. The problem is: I am not able to edit the mining structure in the mining structure editor. The whole data source view within the mining structure editor is greyed. Could please anyone here give me any advices for that?

Thank you very much in advance for any help for that.

With best regards,

Yours sincerely,

You can't edit the data source view in the data mniing designer. The DM designer has a "read-only" view of the DSV. You need to switch to the DSV designer to make edits.|||

Hi, Jamie, thank you very much for your guidance.

Sorry for the bad description of the problem. What I originally meant is that: the mining structure cant be edited within the mining structure editor when its data source is OALP. Why is that?

Is it the limitation for OALP data source?

Thank you very much for your further advices.

With best regards,

Yours sincerely,

|||This is a limitation of the OLAP data source. This is not intended for user modification.|||

Hi, Jamie, thank you very much.

Regards,

Data returned in QA is different than EM

Hi all,
Something strange is going on: When I do a select * from table some of the
data appears to be in the wrong field. However, when I look at the table
contents in the EM, they are all correct. When I do select statement in the
QA based on criteria, the correct rows are not returned.
Any one know what this is all about?
Thanks for your guidance,
SeanWhat version of SQL Server / Query Analyzer? Have you applied the latest
(SQL Server) service pack to the server and client?
How is Query Analyzer set to display data (grid or text)?
By "wrong field" I assume that you mean it appears that the data that is
displayed appears to be in the wrong <ahem> "field" because it appears in
the wrong area of the [text] display of the results. Do you store <carriage
return> and <line feed> (char(13) char(10)) within your data? If so perhaps
those characters along with results in text mode are causing the display to
look incorrect.
Keith
"sean sobey" <Sobey@.yahoo.com> wrote in message
news:%23iVjrebSFHA.508@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Something strange is going on: When I do a select * from table some of
> the
> data appears to be in the wrong field. However, when I look at the table
> contents in the EM, they are all correct. When I do select statement in
> the
> QA based on criteria, the correct rows are not returned.
> Any one know what this is all about?
> Thanks for your guidance,
> Sean
>|||How can we reproduce the problem?
AMB
"sean sobey" wrote:

> Hi all,
> Something strange is going on: When I do a select * from table some of th
e
> data appears to be in the wrong field. However, when I look at the table
> contents in the EM, they are all correct. When I do select statement in t
he
> QA based on criteria, the correct rows are not returned.
> Any one know what this is all about?
> Thanks for your guidance,
> Sean
>
>|||Hi, Sean
See:
http://groups-beta.google.com/group...ea781f3bc49bf23
Razvan

Data returned from .WriteXML is different than what is returned in query analyzer.

I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.

Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).

Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.

If someone is able to help me figure this out, I would appreciate it.

1. public DataSet GetMarketList(string region, string marketRegion)
2. {
3. string sql = @."SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode";
5. DataSet dsMarketList = new DataSet();
6. SqlConnection sqlConn = new SqlConnection(intranetConnStr);

7. SqlCommand cmd = new SqlCommand(sql,sqlConn);
8. sqlConn.Open();
9. SqlDataAdapter adapter = new SqlDataAdapter(cmd);

10. try
11. {
12. adapter.Fill(dsMarketList);

13. String bling = adapter.SelectCommand.CommandText;//BRG
14. dsMarketList.DataSetName="RegionMarket";
15. dsMarketList.Tables[0].TableName = "MarketList";
16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4
18. // from the SQL query
19. }
20. catch(Exception e)
21. {
22. // Handle the exception (Code not shown)

I think that you need to look at what data isn't being returned in your app. Can you load the XML file into Excel? You could then paste the SQL Analyzer results into Excel and look at the difference.

Also, there isn't an order by clause in your SQL, so SQL Server is free to return the results in a different order between invocations of the same SQL. (It rarely happens, but it does occasionally.)

|||Ugh !!! I figured out what the problem is. Thanks !sql

Data or index corruption?

Since this morning we have strange behaviour on our production SQL Server
With a simple select I have those results :
select serialno from table1 where serialno=205749
'Syntax error converting the varchar value '15LA02269' to a column of data
type int.'
But this one work :
select serialno from table1 where serialno='205749'
Of course serialno is a auto increment of type 'INT', but some other columns
are varchar
Dbcc checktable, dbcc checkalloc, debcc checktable return no errors. No
error in SQL error Log.
I don't know if one or several tables are corrupted because I ve got those
type of message on other simple query with other tables.
I don't know what to do now... I hope someone have an idea.
Thanks in advanceOuch! Did you try DBCC CHECKDB and DBCC CHECKCATALOG?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"smf" <smf@.discussions.microsoft.com> wrote in message
news:9B1C9A08-F2EF-4223-8E5F-2432442F938A@.microsoft.com...
> Since this morning we have strange behaviour on our production SQL Server
> With a simple select I have those results :
> select serialno from table1 where serialno=205749
> 'Syntax error converting the varchar value '15LA02269' to a column of data
> type int.'
> But this one work :
> select serialno from table1 where serialno='205749'
> Of course serialno is a auto increment of type 'INT', but some other columns
> are varchar
> Dbcc checktable, dbcc checkalloc, debcc checktable return no errors. No
> error in SQL error Log.
> I don't know if one or several tables are corrupted because I ve got those
> type of message on other simple query with other tables.
> I don't know what to do now... I hope someone have an idea.
> Thanks in advance|||Yes for DBCC CHECK and yes for DBCC CHECKCATALOG
No error or warning.
Arrghhh : I've just realized something : serialno is not 'int' it's a
VARCHAR !!! And it appears in lot of tables, that why I have this message in
several cases.
I've never had this sort of message before. But few days ago we have changed
SQL Server compatibility level from 65 to 80. Perhaps it's the origine of all
my problems.
I think I have now to change all query for that columns (change int
parameters to string in ADO) ... If SantaClauss have nothing to do now
perhaps could he help me?
Many thanks for your help
"Tibor Karaszi" wrote:
> Ouch! Did you try DBCC CHECKDB and DBCC CHECKCATALOG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "smf" <smf@.discussions.microsoft.com> wrote in message
> news:9B1C9A08-F2EF-4223-8E5F-2432442F938A@.microsoft.com...
> > Since this morning we have strange behaviour on our production SQL Server
> > With a simple select I have those results :
> >
> > select serialno from table1 where serialno=205749
> > 'Syntax error converting the varchar value '15LA02269' to a column of data
> > type int.'
> >
> > But this one work :
> > select serialno from table1 where serialno='205749'
> >
> > Of course serialno is a auto increment of type 'INT', but some other columns
> > are varchar
> > Dbcc checktable, dbcc checkalloc, debcc checktable return no errors. No
> > error in SQL error Log.
> >
> > I don't know if one or several tables are corrupted because I ve got those
> > type of message on other simple query with other tables.
> >
> > I don't know what to do now... I hope someone have an idea.
> > Thanks in advance
>
>|||That explains it.
In SQL Server 2000, a WHERE clause when you don't have the same datatype is evaluated according to
the section in Books Online called "datatype precedence". According to this, a varchar is converted
to an int. So your where clause would be the same as
select serialno from table1 where CAST(serialno AS int) = 205749
And not only can't above use an index on the serialno column, if you have something which isn't
convertible to an int, you get that error message.
In earlier releases the constant was converted to the columns datatype. I.e.:
select serialno from table1 where serialno= CAST(205749 AS varchar(nn))
Above can use an index on the serialno column and will also work for not numeric values in the
serial no column.
So, yes. changing compatibility level is most probably what made you see these run-time errors.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"smf" <smf@.discussions.microsoft.com> wrote in message
news:D9CCEA0A-8754-421C-A57E-FB286A5E32E5@.microsoft.com...
> Yes for DBCC CHECK and yes for DBCC CHECKCATALOG
> No error or warning.
>
> Arrghhh : I've just realized something : serialno is not 'int' it's a
> VARCHAR !!! And it appears in lot of tables, that why I have this message in
> several cases.
> I've never had this sort of message before. But few days ago we have changed
> SQL Server compatibility level from 65 to 80. Perhaps it's the origine of all
> my problems.
> I think I have now to change all query for that columns (change int
> parameters to string in ADO) ... If SantaClauss have nothing to do now
> perhaps could he help me?
> Many thanks for your help
>
> "Tibor Karaszi" wrote:
>> Ouch! Did you try DBCC CHECKDB and DBCC CHECKCATALOG?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "smf" <smf@.discussions.microsoft.com> wrote in message
>> news:9B1C9A08-F2EF-4223-8E5F-2432442F938A@.microsoft.com...
>> > Since this morning we have strange behaviour on our production SQL Server
>> > With a simple select I have those results :
>> >
>> > select serialno from table1 where serialno=205749
>> > 'Syntax error converting the varchar value '15LA02269' to a column of data
>> > type int.'
>> >
>> > But this one work :
>> > select serialno from table1 where serialno='205749'
>> >
>> > Of course serialno is a auto increment of type 'INT', but some other columns
>> > are varchar
>> > Dbcc checktable, dbcc checkalloc, debcc checktable return no errors. No
>> > error in SQL error Log.
>> >
>> > I don't know if one or several tables are corrupted because I ve got those
>> > type of message on other simple query with other tables.
>> >
>> > I don't know what to do now... I hope someone have an idea.
>> > Thanks in advance
>>

Data or index corruption?

Since this morning we have strange behaviour on our production SQL Server
With a simple select I have those results :
select serialno from table1 where serialno=205749
'Syntax error converting the varchar value '15LA02269' to a column of data
type int.'
But this one work :
select serialno from table1 where serialno='205749'
Of course serialno is a auto increment of type 'INT', but some other columns
are varchar
Dbcc checktable, dbcc checkalloc, debcc checktable return no errors. No
error in SQL error Log.
I don't know if one or several tables are corrupted because I ve got those
type of message on other simple query with other tables.
I don't know what to do now... I hope someone have an idea.
Thanks in advance
Ouch! Did you try DBCC CHECKDB and DBCC CHECKCATALOG?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"smf" <smf@.discussions.microsoft.com> wrote in message
news:9B1C9A08-F2EF-4223-8E5F-2432442F938A@.microsoft.com...
> Since this morning we have strange behaviour on our production SQL Server
> With a simple select I have those results :
> select serialno from table1 where serialno=205749
> 'Syntax error converting the varchar value '15LA02269' to a column of data
> type int.'
> But this one work :
> select serialno from table1 where serialno='205749'
> Of course serialno is a auto increment of type 'INT', but some other columns
> are varchar
> Dbcc checktable, dbcc checkalloc, debcc checktable return no errors. No
> error in SQL error Log.
> I don't know if one or several tables are corrupted because I ve got those
> type of message on other simple query with other tables.
> I don't know what to do now... I hope someone have an idea.
> Thanks in advance
|||Yes for DBCC CHECK and yes for DBCC CHECKCATALOG
No error or warning.
Arrghhh : I've just realized something : serialno is not 'int' it's a
VARCHAR !!! And it appears in lot of tables, that why I have this message in
several cases.
I've never had this sort of message before. But few days ago we have changed
SQL Server compatibility level from 65 to 80. Perhaps it's the origine of all
my problems.
I think I have now to change all query for that columns (change int
parameters to string in ADO) ... If SantaClauss have nothing to do now
perhaps could he help me?
Many thanks for your help
"Tibor Karaszi" wrote:

> Ouch! Did you try DBCC CHECKDB and DBCC CHECKCATALOG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "smf" <smf@.discussions.microsoft.com> wrote in message
> news:9B1C9A08-F2EF-4223-8E5F-2432442F938A@.microsoft.com...
>
>
|||That explains it.
In SQL Server 2000, a WHERE clause when you don't have the same datatype is evaluated according to
the section in Books Online called "datatype precedence". According to this, a varchar is converted
to an int. So your where clause would be the same as
select serialno from table1 where CAST(serialno AS int) = 205749
And not only can't above use an index on the serialno column, if you have something which isn't
convertible to an int, you get that error message.
In earlier releases the constant was converted to the columns datatype. I.e.:
select serialno from table1 where serialno= CAST(205749 AS varchar(nn))
Above can use an index on the serialno column and will also work for not numeric values in the
serial no column.
So, yes. changing compatibility level is most probably what made you see these run-time errors.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"smf" <smf@.discussions.microsoft.com> wrote in message
news:D9CCEA0A-8754-421C-A57E-FB286A5E32E5@.microsoft.com...[vbcol=seagreen]
> Yes for DBCC CHECK and yes for DBCC CHECKCATALOG
> No error or warning.
>
> Arrghhh : I've just realized something : serialno is not 'int' it's a
> VARCHAR !!! And it appears in lot of tables, that why I have this message in
> several cases.
> I've never had this sort of message before. But few days ago we have changed
> SQL Server compatibility level from 65 to 80. Perhaps it's the origine of all
> my problems.
> I think I have now to change all query for that columns (change int
> parameters to string in ADO) ... If SantaClauss have nothing to do now
> perhaps could he help me?
> Many thanks for your help
>
> "Tibor Karaszi" wrote:

Data or index corruption?

Since this morning we have strange behaviour on our production SQL Server
With a simple select I have those results :
select serialno from table1 where serialno=205749
'Syntax error converting the varchar value '15LA02269' to a column of data
type int.'
But this one work :
select serialno from table1 where serialno='205749'
Of course serialno is a auto increment of type 'INT', but some other columns
are varchar
Dbcc checktable, dbcc checkalloc, debcc checktable return no errors. No
error in SQL error Log.
I don't know if one or several tables are corrupted because I ve got those
type of message on other simple query with other tables.
I don't know what to do now... I hope someone have an idea.
Thanks in advanceOuch! Did you try DBCC CHECKDB and DBCC CHECKCATALOG?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"smf" <smf@.discussions.microsoft.com> wrote in message
news:9B1C9A08-F2EF-4223-8E5F-2432442F938A@.microsoft.com...
> Since this morning we have strange behaviour on our production SQL Server
> With a simple select I have those results :
> select serialno from table1 where serialno=205749
> 'Syntax error converting the varchar value '15LA02269' to a column of data
> type int.'
> But this one work :
> select serialno from table1 where serialno='205749'
> Of course serialno is a auto increment of type 'INT', but some other colum
ns
> are varchar
> Dbcc checktable, dbcc checkalloc, debcc checktable return no errors. No
> error in SQL error Log.
> I don't know if one or several tables are corrupted because I ve got those
> type of message on other simple query with other tables.
> I don't know what to do now... I hope someone have an idea.
> Thanks in advance|||Yes for DBCC CHECK and yes for DBCC CHECKCATALOG
No error or warning.
Arrghhh : I've just realized something : serialno is not 'int' it's a
VARCHAR !!! And it appears in lot of tables, that why I have this message in
several cases.
I've never had this sort of message before. But few days ago we have changed
SQL Server compatibility level from 65 to 80. Perhaps it's the origine of al
l
my problems.
I think I have now to change all query for that columns (change int
parameters to string in ADO) ... If SantaClauss have nothing to do now
perhaps could he help me?
Many thanks for your help
"Tibor Karaszi" wrote:

> Ouch! Did you try DBCC CHECKDB and DBCC CHECKCATALOG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "smf" <smf@.discussions.microsoft.com> wrote in message
> news:9B1C9A08-F2EF-4223-8E5F-2432442F938A@.microsoft.com...
>
>|||That explains it.
In SQL Server 2000, a WHERE clause when you don't have the same datatype is
evaluated according to
the section in Books Online called "datatype precedence". According to this,
a varchar is converted
to an int. So your where clause would be the same as
select serialno from table1 where CAST(serialno AS int) = 205749
And not only can't above use an index on the serialno column, if you have so
mething which isn't
convertible to an int, you get that error message.
In earlier releases the constant was converted to the columns datatype. I.e.
:
select serialno from table1 where serialno= CAST(205749 AS varchar(nn))
Above can use an index on the serialno column and will also work for not num
eric values in the
serial no column.
So, yes. changing compatibility level is most probably what made you see the
se run-time errors.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"smf" <smf@.discussions.microsoft.com> wrote in message
news:D9CCEA0A-8754-421C-A57E-FB286A5E32E5@.microsoft.com...[vbcol=seagreen]
> Yes for DBCC CHECK and yes for DBCC CHECKCATALOG
> No error or warning.
>
> Arrghhh : I've just realized something : serialno is not 'int' it's a
> VARCHAR !!! And it appears in lot of tables, that why I have this message
in
> several cases.
> I've never had this sort of message before. But few days ago we have chang
ed
> SQL Server compatibility level from 65 to 80. Perhaps it's the origine of
all
> my problems.
> I think I have now to change all query for that columns (change int
> parameters to string in ADO) ... If SantaClauss have nothing to do now
> perhaps could he help me?
> Many thanks for your help
>
> "Tibor Karaszi" wrote:
>

data mixed up after update

Hi!

I have quite strange problem, that I haven't seen before.

When I use update command:

UPDATE categorys SET banner_valid= '0', section_id= '1', main_cat_default= '0', banner= '', b_link= '', external_text= 'Nākotnes parks ', in_frontpage= '1', name = '100. pants' WHERE (id = 130)

Then the field EXTERNAL_TEXT should have value Nākotnes parks but instead of this it makes it Nakotnes parks.

I changed collation to Latvian and this did not work.

But!!! When I open Enterprise manager and just type in Nākotnes parks and save it then it is ok, but it does not work with Update/add script

Any help or ideas?

I would try changing Asp.net to UTF16, maybe Latvian is only UTF 16. That maybe the reason it works in Enterprise manager and not in your application. SQL Server Unicode is multibyte UTF16 while .NET you have the option of using singlebyte UTF8. Hope this helps.

Kind regards,

Gift Peddie

|||You need to tell SQL Server that you're submitting Unicode text by giving your string the N prefix:
UPDATE categorys
SET banner_valid= '0',section_id= '1', main_cat_default= '0', banner= '', b_link= '',external_text= N'Nākotnes parks ', in_frontpage= '1', name = '100.pants'
WHERE id = 130
|||

SQL Server Unicode is multibyte UTF16

Actually it's not. It's UCS2, which is a fixed-length encoding.

you have the option of using singlebyte UTF8

UTF-8 is a variable-length encoding, so a given character encoded with UTF-8 *may* only require a single byte, or it may require two bytes, or four. It can represent anything that you can store in UCS2 just fine.

Data missing after transaction committed

We have discovered a very strange anomaly in one of our databases yesterday.
The database is being run on SQL 2000, and has been running problem free for
about 5 years, until an isolated event yesterday.
In simple terms, a transaction that has been committed is "missing" from the
database.
The normal steps for our transaction are as follows.
1. Begin a transaction.
2. Update some records in some tables.
3. Write a new record to the database in another table.
4. If all successful, Commit transaction. Read the data from written in step
3, and print out a hard copy. Included in the hardcopy is the ID field
(primary Key) of the row written in step 3.
5. If any step fails, roll back transaction, give message to the user.
Yesterday, we had a printout generated. The printout happens after the
commit, is a totally separate step, and is read from the data that is
written in step 3. At some point shortly there after, that record has
"disappeared" from the database. (Also, the Updated rows from step 2 were
not updated like they should be. They looked as they would have if the
transaction had never been committed.)
There is no way for that record to be deleted by the user, and I can say
with certainty a sysadmin didn't delete it, as I am the only user with admin
rights to do something like that, and I know I didn't execute a delete on
that row (let alone un-do the updates in step 2 above).
Now, there is where it gets more interesting. At around the same this
happened, we had blocking situation. Essencially, another transaction had
started, and locked a row on an unrelated table. That users network
connection went down before his transaction could be completed. Since the
lock being placed on the table was affecting other users (who needed to read
from that table) we killed the process of that connection.
Now, my questions are:
1. It seems like more than a coincidence that these 2 events happened at the
same time, but they happened at completly different and unrelated tables.
Can anyone think of a reason something like this might happen?
2. How could this have happened at all? I was under the assumption that once
a transaction was committed, there was no way it could not be in the
database?
3. Is there anything I can do to prevent this from happening in the future?
Thanks for any suggestions.
-Ryan
> 1. It seems like more than a coincidence that these 2 events happened at
> the same time, but they happened at completly different and unrelated
> tables. Can anyone think of a reason something like this might happen?
Does your application maintain persistent database connections? If that is
the case, consider that the blocking episode could have caused a client-side
timeout. The transaction that was in progress when the timeout occurred
might not have gotten rolled back and all subsequent work done on that
connection was performed in the context of a transaction that was never
committed.

> 2. How could this have happened at all? I was under the assumption that
> once a transaction was committed, there was no way it could not be in the
> database?
A committed transaction is durable. However, note that COMMIT only
decrements @.@.TRANCOUNT; the transaction is not durable until @.@.TRANCOUNT
reaches zero.

> 3. Is there anything I can do to prevent this from happening in the
> future?
One method is to SET XACT_ABORT ON, which will roll back the transaction in
the event of a timeout. This is especially important when you issue BEGIN
TRAN in stored procedure code. I also believe some client APIs have the
smarts to issue a ROLLBACK following a detected exception as long as the
transaction was started via the API rather than a Transact-SQL BEGIN TRAN.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:ObACMuiVHHA.4832@.TK2MSFTNGP04.phx.gbl...
> We have discovered a very strange anomaly in one of our databases
> yesterday.
> The database is being run on SQL 2000, and has been running problem free
> for about 5 years, until an isolated event yesterday.
> In simple terms, a transaction that has been committed is "missing" from
> the database.
> The normal steps for our transaction are as follows.
> 1. Begin a transaction.
> 2. Update some records in some tables.
> 3. Write a new record to the database in another table.
> 4. If all successful, Commit transaction. Read the data from written in
> step 3, and print out a hard copy. Included in the hardcopy is the ID
> field (primary Key) of the row written in step 3.
> 5. If any step fails, roll back transaction, give message to the user.
> Yesterday, we had a printout generated. The printout happens after the
> commit, is a totally separate step, and is read from the data that is
> written in step 3. At some point shortly there after, that record has
> "disappeared" from the database. (Also, the Updated rows from step 2 were
> not updated like they should be. They looked as they would have if the
> transaction had never been committed.)
> There is no way for that record to be deleted by the user, and I can say
> with certainty a sysadmin didn't delete it, as I am the only user with
> admin rights to do something like that, and I know I didn't execute a
> delete on that row (let alone un-do the updates in step 2 above).
> Now, there is where it gets more interesting. At around the same this
> happened, we had blocking situation. Essencially, another transaction had
> started, and locked a row on an unrelated table. That users network
> connection went down before his transaction could be completed. Since the
> lock being placed on the table was affecting other users (who needed to
> read from that table) we killed the process of that connection.
> Now, my questions are:
> 1. It seems like more than a coincidence that these 2 events happened at
> the same time, but they happened at completly different and unrelated
> tables. Can anyone think of a reason something like this might happen?
> 2. How could this have happened at all? I was under the assumption that
> once a transaction was committed, there was no way it could not be in the
> database?
> 3. Is there anything I can do to prevent this from happening in the
> future?
> Thanks for any suggestions.
> -Ryan
>

Data missing after transaction committed

We have discovered a very strange anomaly in one of our databases yesterday.
The database is being run on SQL 2000, and has been running problem free for
about 5 years, until an isolated event yesterday.
In simple terms, a transaction that has been committed is "missing" from the
database.
The normal steps for our transaction are as follows.
1. Begin a transaction.
2. Update some records in some tables.
3. Write a new record to the database in another table.
4. If all successful, Commit transaction. Read the data from written in step
3, and print out a hard copy. Included in the hardcopy is the ID field
(primary Key) of the row written in step 3.
5. If any step fails, roll back transaction, give message to the user.
Yesterday, we had a printout generated. The printout happens after the
commit, is a totally separate step, and is read from the data that is
written in step 3. At some point shortly there after, that record has
"disappeared" from the database. (Also, the Updated rows from step 2 were
not updated like they should be. They looked as they would have if the
transaction had never been committed.)
There is no way for that record to be deleted by the user, and I can say
with certainty a sysadmin didn't delete it, as I am the only user with admin
rights to do something like that, and I know I didn't execute a delete on
that row (let alone un-do the updates in step 2 above).
Now, there is where it gets more interesting. At around the same this
happened, we had blocking situation. Essencially, another transaction had
started, and locked a row on an unrelated table. That users network
connection went down before his transaction could be completed. Since the
lock being placed on the table was affecting other users (who needed to read
from that table) we killed the process of that connection.
Now, my questions are:
1. It seems like more than a coincidence that these 2 events happened at the
same time, but they happened at completly different and unrelated tables.
Can anyone think of a reason something like this might happen?
2. How could this have happened at all? I was under the assumption that once
a transaction was committed, there was no way it could not be in the
database?
3. Is there anything I can do to prevent this from happening in the future?
Thanks for any suggestions.
-Ryan> 1. It seems like more than a coincidence that these 2 events happened at
> the same time, but they happened at completly different and unrelated
> tables. Can anyone think of a reason something like this might happen?
Does your application maintain persistent database connections? If that is
the case, consider that the blocking episode could have caused a client-side
timeout. The transaction that was in progress when the timeout occurred
might not have gotten rolled back and all subsequent work done on that
connection was performed in the context of a transaction that was never
committed.

> 2. How could this have happened at all? I was under the assumption that
> once a transaction was committed, there was no way it could not be in the
> database?
A committed transaction is durable. However, note that COMMIT only
decrements @.@.TRANCOUNT; the transaction is not durable until @.@.TRANCOUNT
reaches zero.

> 3. Is there anything I can do to prevent this from happening in the
> future?
One method is to SET XACT_ABORT ON, which will roll back the transaction in
the event of a timeout. This is especially important when you issue BEGIN
TRAN in stored procedure code. I also believe some client APIs have the
smarts to issue a ROLLBACK following a detected exception as long as the
transaction was started via the API rather than a Transact-SQL BEGIN TRAN.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:ObACMuiVHHA.4832@.TK2MSFTNGP04.phx.gbl...
> We have discovered a very strange anomaly in one of our databases
> yesterday.
> The database is being run on SQL 2000, and has been running problem free
> for about 5 years, until an isolated event yesterday.
> In simple terms, a transaction that has been committed is "missing" from
> the database.
> The normal steps for our transaction are as follows.
> 1. Begin a transaction.
> 2. Update some records in some tables.
> 3. Write a new record to the database in another table.
> 4. If all successful, Commit transaction. Read the data from written in
> step 3, and print out a hard copy. Included in the hardcopy is the ID
> field (primary Key) of the row written in step 3.
> 5. If any step fails, roll back transaction, give message to the user.
> Yesterday, we had a printout generated. The printout happens after the
> commit, is a totally separate step, and is read from the data that is
> written in step 3. At some point shortly there after, that record has
> "disappeared" from the database. (Also, the Updated rows from step 2 were
> not updated like they should be. They looked as they would have if the
> transaction had never been committed.)
> There is no way for that record to be deleted by the user, and I can say
> with certainty a sysadmin didn't delete it, as I am the only user with
> admin rights to do something like that, and I know I didn't execute a
> delete on that row (let alone un-do the updates in step 2 above).
> Now, there is where it gets more interesting. At around the same this
> happened, we had blocking situation. Essencially, another transaction had
> started, and locked a row on an unrelated table. That users network
> connection went down before his transaction could be completed. Since the
> lock being placed on the table was affecting other users (who needed to
> read from that table) we killed the process of that connection.
> Now, my questions are:
> 1. It seems like more than a coincidence that these 2 events happened at
> the same time, but they happened at completly different and unrelated
> tables. Can anyone think of a reason something like this might happen?
> 2. How could this have happened at all? I was under the assumption that
> once a transaction was committed, there was no way it could not be in the
> database?
> 3. Is there anything I can do to prevent this from happening in the
> future?
> Thanks for any suggestions.
> -Ryan
>

Data missing after transaction committed

We have discovered a very strange anomaly in one of our databases yesterday.
The database is being run on SQL 2000, and has been running problem free for
about 5 years, until an isolated event yesterday.
In simple terms, a transaction that has been committed is "missing" from the
database.
The normal steps for our transaction are as follows.
1. Begin a transaction.
2. Update some records in some tables.
3. Write a new record to the database in another table.
4. If all successful, Commit transaction. Read the data from written in step
3, and print out a hard copy. Included in the hardcopy is the ID field
(primary Key) of the row written in step 3.
5. If any step fails, roll back transaction, give message to the user.
Yesterday, we had a printout generated. The printout happens after the
commit, is a totally separate step, and is read from the data that is
written in step 3. At some point shortly there after, that record has
"disappeared" from the database. (Also, the Updated rows from step 2 were
not updated like they should be. They looked as they would have if the
transaction had never been committed.)
There is no way for that record to be deleted by the user, and I can say
with certainty a sysadmin didn't delete it, as I am the only user with admin
rights to do something like that, and I know I didn't execute a delete on
that row (let alone un-do the updates in step 2 above).
Now, there is where it gets more interesting. At around the same this
happened, we had blocking situation. Essencially, another transaction had
started, and locked a row on an unrelated table. That users network
connection went down before his transaction could be completed. Since the
lock being placed on the table was affecting other users (who needed to read
from that table) we killed the process of that connection.
Now, my questions are:
1. It seems like more than a coincidence that these 2 events happened at the
same time, but they happened at completly different and unrelated tables.
Can anyone think of a reason something like this might happen?
2. How could this have happened at all? I was under the assumption that once
a transaction was committed, there was no way it could not be in the
database?
3. Is there anything I can do to prevent this from happening in the future?
Thanks for any suggestions.
-Ryan> 1. It seems like more than a coincidence that these 2 events happened at
> the same time, but they happened at completly different and unrelated
> tables. Can anyone think of a reason something like this might happen?
Does your application maintain persistent database connections? If that is
the case, consider that the blocking episode could have caused a client-side
timeout. The transaction that was in progress when the timeout occurred
might not have gotten rolled back and all subsequent work done on that
connection was performed in the context of a transaction that was never
committed.
> 2. How could this have happened at all? I was under the assumption that
> once a transaction was committed, there was no way it could not be in the
> database?
A committed transaction is durable. However, note that COMMIT only
decrements @.@.TRANCOUNT; the transaction is not durable until @.@.TRANCOUNT
reaches zero.
> 3. Is there anything I can do to prevent this from happening in the
> future?
One method is to SET XACT_ABORT ON, which will roll back the transaction in
the event of a timeout. This is especially important when you issue BEGIN
TRAN in stored procedure code. I also believe some client APIs have the
smarts to issue a ROLLBACK following a detected exception as long as the
transaction was started via the API rather than a Transact-SQL BEGIN TRAN.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:ObACMuiVHHA.4832@.TK2MSFTNGP04.phx.gbl...
> We have discovered a very strange anomaly in one of our databases
> yesterday.
> The database is being run on SQL 2000, and has been running problem free
> for about 5 years, until an isolated event yesterday.
> In simple terms, a transaction that has been committed is "missing" from
> the database.
> The normal steps for our transaction are as follows.
> 1. Begin a transaction.
> 2. Update some records in some tables.
> 3. Write a new record to the database in another table.
> 4. If all successful, Commit transaction. Read the data from written in
> step 3, and print out a hard copy. Included in the hardcopy is the ID
> field (primary Key) of the row written in step 3.
> 5. If any step fails, roll back transaction, give message to the user.
> Yesterday, we had a printout generated. The printout happens after the
> commit, is a totally separate step, and is read from the data that is
> written in step 3. At some point shortly there after, that record has
> "disappeared" from the database. (Also, the Updated rows from step 2 were
> not updated like they should be. They looked as they would have if the
> transaction had never been committed.)
> There is no way for that record to be deleted by the user, and I can say
> with certainty a sysadmin didn't delete it, as I am the only user with
> admin rights to do something like that, and I know I didn't execute a
> delete on that row (let alone un-do the updates in step 2 above).
> Now, there is where it gets more interesting. At around the same this
> happened, we had blocking situation. Essencially, another transaction had
> started, and locked a row on an unrelated table. That users network
> connection went down before his transaction could be completed. Since the
> lock being placed on the table was affecting other users (who needed to
> read from that table) we killed the process of that connection.
> Now, my questions are:
> 1. It seems like more than a coincidence that these 2 events happened at
> the same time, but they happened at completly different and unrelated
> tables. Can anyone think of a reason something like this might happen?
> 2. How could this have happened at all? I was under the assumption that
> once a transaction was committed, there was no way it could not be in the
> database?
> 3. Is there anything I can do to prevent this from happening in the
> future?
> Thanks for any suggestions.
> -Ryan
>

Data Lost-Merge Replication

We are using merge replication over internet to repliacte our data b/w 2 servers located at 2 different branch offices. But the strange thing is we are losing data i.e. data is being automatically deleted during replication.

any idea/help for fixing it would be hihgly appreciated

thank in advanceProbably failed insert at subscriber poss due to Referential Integrity Failure.

Because SQL Must make publisher & Subscriber with same data it will come back & delete publisher row

Keep an eye on the conflicts

Data incorrectly repeating itself

I have run into a strange issue that I believe is a SQL Reporting Services issue.

I have a report laid out in landscape setting that has 4 columns of text. Two of the columns are sub-reports (due to the complexity and size, we did not flatten out the data in the stored procedure) and two of the columns are regular fields.

The 2 columns of regular fields are smaller, and normally only grow to about 1/2 the height of he page. One of the two sub-reports contains large amounts of text, and at time grows larger than the height of the page.

When the sub-report grows larger than the current page, it correctly starts up on the next page. But the 2 fields of data from the main dataset (not the sub-report columns) repeat themselves on the next page as well.

What is even more strange is the 2 fields of data from the main dataset only repeat data to grow vertically as far as the sub-report needs to grow. So if there is more data in either of these 2 fields than is needed for the sub-report to grow on the 2nd page, it will cut off the data in both of these fields.

I have tried placing the information in a group header. Turned the "Repeat on new page" both True and False, Took away the table header and footer, forced a page break after each group, tried using the "Hide Duplicates" property on the field within the details section, and nothing has seemed to fix the issue.

If anyone has run into this and found a work around, let me know.

Thank you,

T.J.

Just bumping this thread on a Monday morning after posting late on a Friday afternoon.

I am wondering if anyone has run into this issue, and if so if there have been any resolutions or work arounds.

Thank you,

T.J.