Flipping back to simple mode

Hi,
I have three user databases on my production server and I noticed a strange
behavior whenever the server gets rebooted. on two of the databases the
recovery model flips back from Full to simple when the server is rebooted
any time. On the third database the recover model stays as Full even after
the reboot. Even after I switch the recovery model back to full on the two
databases what I see is, as soon as the server gets rebooted, they both flip
back to simple.
I am confused as to why it is happening only with two databases and not the
third. Is there something that is set up wrongly on these two databases?.
Any insight is highly helpful.
Thanks
MeherCheck under Management | jobs in Enterprise Manager, something this is
running sp_dboption '<database','select into','true' and sp_dboption
'<database>','trunc','true'
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"Meher Malakapalli" <mmalakapalli@.cohesioninc.com> wrote in message
news:OiCIFAW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have three user databases on my production server and I noticed a
strange
> behavior whenever the server gets rebooted. on two of the databases the
> recovery model flips back from Full to simple when the server is rebooted
> any time. On the third database the recover model stays as Full even after
> the reboot. Even after I switch the recovery model back to full on the two
> databases what I see is, as soon as the server gets rebooted, they both
flip
> back to simple.
> I am confused as to why it is happening only with two databases and not
the
> third. Is there something that is set up wrongly on these two databases?.
> Any insight is highly helpful.
> Thanks
> Meher
>|||I checked them. No job is running with those options. I only have the
following options set to true on both the databases.
torn page detection
auto create statistics
auto update statistics
BTW I am running SQL Server 2000 with SP3 Installed.
Thanks for the suggestion Andy.
Meher
"Andy Svendsen" <andymcdba1@.NOMORESPAM.yahoo.com> wrote in message
news:ubIupIW$DHA.212@.TK2MSFTNGP12.phx.gbl...
> Check under Management | jobs in Enterprise Manager, something this is
> running sp_dboption '<database','select into','true' and sp_dboption
> '<database>','trunc','true'
> --
> *******************************************************************
> Andy S.
> MCSE NT/2000, MCDBA SQL 7/2000
> andymcdba1@.NOMORESPAM.yahoo.com
> Please remove NOMORESPAM before replying.
> Always keep your antivirus and Microsoft software
> up to date with the latest definitions and product updates.
> Be suspicious of every email attachment, I will never send
> or post anything other than the text of a http:// link nor
> post the link directly to a file for downloading.
> This posting is provided "as is" with no warranties
> and confers no rights.
> *******************************************************************
> "Meher Malakapalli" <mmalakapalli@.cohesioninc.com> wrote in message
> news:OiCIFAW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I have three user databases on my production server and I noticed a
> strange
> > behavior whenever the server gets rebooted. on two of the databases the
> > recovery model flips back from Full to simple when the server is
rebooted
> > any time. On the third database the recover model stays as Full even
after
> > the reboot. Even after I switch the recovery model back to full on the
two
> > databases what I see is, as soon as the server gets rebooted, they both
> flip
> > back to simple.
> >
> > I am confused as to why it is happening only with two databases and not
> the
> > third. Is there something that is set up wrongly on these two
databases?.
> > Any insight is highly helpful.
> >
> > Thanks
> >
> > Meher
> >
> >
>

Flat file to table - rows out of order

Hi,

I noticed something strange today. I created a pkg that reads a flat file and writes the rows to a table.

In checking the data in the file against what's in the table, I noticed that the rows were inserted in a different order than they are in the file.

All the rows appear to be in the table correctly, but they're just not in the same order as in the file. I've never seen this before. But I checked very carefully, and this is indeed the case.

Is this normal?

Thanks

Is it normal? Well...its not not normal!

There is no concept of order in a database table. You should never assume that rows will get returned to you in the order that (you assume) they were inserted.

-Jamie

|||

That is not my understanding. For example, if you create a table, then insert a bunch of rows, one at a time, they will most definitely be returned in the order they were inserted. I have *never* seen an exception to this.

Perhaps the SSIS package is not inserting the rows in the order they are in the file?

Anyhow, I could be wrong, but this goes against my experience completely.

|||Not to sound mean or anything, but Jamie is absolutely right. There is no such thing as ordering in database land. Just because your experience "proves" otherwise, doesn't make it fact. The only way to guarantee order is to use an ORDER BY clause on your SQL statement which only controls the PRESENTATION of the data, not the way it's stored.

Do you have a situation that the records are out of order when ordering by an identity column, or are you merely using a "select * from table" statement without an ORDER BY clause?

This is perfectly normal behavior. You might want to add a sort transformation right before the destination. But still, there are no guarantees that the data will be stored "in order."|||By its definition, a database table is an unordered set of rows. While "most" of the time, a select without an ORDER BY clause will return the rows in the order they were entered, it is never guaranteed. The only way to guarantee retrieving rows in the order you want is with an ORDER BY clause on the query.|||

There are many factors that influence the order in which rows are returned. The most obvious being the presence of indexes.

Other possible causes may be the number of processors, what data is cached, datafile placement, datafile fill factors, hard drive configuration. There are a million and one things.

These same factors that affect the retrieval of data can also affect the insertion of data. Hopefully you can see how the order in which data is retrieved can be affected.

There is no concept of order in a database table. Period.

-Jamie

|||

Ok, ok - just had to make sure. As this goes against anything I have ever seen before. I've only been using SQL Server a couple years now, so there's a lot of things I haven't seen. This is one of them.

Anyways, thanks.

|||

sadie519590 wrote:

Ok, ok - just had to make sure. As this goes against anything I have ever seen before. I've only been using SQL Server a couple years now, so there's a lot of things I haven't seen. This is one of them.

Anyways, thanks.

No worries. All the training courses in the world wouldn't have taught you this. The only way you learn a product is by using it. I've been using this damn thing for seven years now and I only know a fraction of it

-Jamie

First INSERT in the transaction is not being committed

Hi All
I am having a strange problem. I have an excel sheet from which I fetch
the data using ADODB. I hava a loop which runs through all rows in the
excel sheet and calls a stored procedure to insert the rows into the
database.
The problem is when the statement oConn.CommitTrans is called, the
first row is not being inserted into the database.
But I am able to see the call to the stored procedure in the SQL
Profiler for the first row in addition to the other rows.
One more thing is, in the SQL Profiler, after the call to the stored
procedure for the first row, I am seeing the following:
SET NO_BROWSETABLE ON
SET FMTONLY ON EXEC spInsert ' ', ' ' SET FMTONLY OFF
set fmtonly off
SET NO_BROWSETABLE OFF
Please help me.
Thanks in advanceI think that SET FMTONLY ON is doing no results appearing. Put on
"hbase19-all@.yahoo.co.in" wrote:

> Hi All
> I am having a strange problem. I have an excel sheet from which I fetch
> the data using ADODB. I hava a loop which runs through all rows in the
> excel sheet and calls a stored procedure to insert the rows into the
> database.
> The problem is when the statement oConn.CommitTrans is called, the
> first row is not being inserted into the database.
> But I am able to see the call to the stored procedure in the SQL
> Profiler for the first row in addition to the other rows.
> One more thing is, in the SQL Profiler, after the call to the stored
> procedure for the first row, I am seeing the following:
> SET NO_BROWSETABLE ON
> SET FMTONLY ON EXEC spInsert ' ', ' ' SET FMTONLY OFF
> set fmtonly off
> SET NO_BROWSETABLE OFF
> Please help me.
> Thanks in advance
>|||It looks like your XL sheet does not have a header row. Try adding a header
row. SET FMTONLY will only return meta-data to the client and it looks like
this call is being made to determine the structure.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<hbase19-all@.yahoo.co.in> wrote in message
news:1136794436.468218.270140@.g47g2000cwa.googlegroups.com...
> Hi All
> I am having a strange problem. I have an excel sheet from which I fetch
> the data using ADODB. I hava a loop which runs through all rows in the
> excel sheet and calls a stored procedure to insert the rows into the
> database.
> The problem is when the statement oConn.CommitTrans is called, the
> first row is not being inserted into the database.
> But I am able to see the call to the stored procedure in the SQL
> Profiler for the first row in addition to the other rows.
> One more thing is, in the SQL Profiler, after the call to the stored
> procedure for the first row, I am seeing the following:
> SET NO_BROWSETABLE ON
> SET FMTONLY ON EXEC spInsert ' ', ' ' SET FMTONLY OFF
> set fmtonly off
> SET NO_BROWSETABLE OFF
> Please help me.
> Thanks in advance
>|||Thanks for the reply
The line "SET FMTONLY ON" is appearing automatically. I am not calling
that statement.
Also I found in ms web site that the statement "SET FMTONLY ON" starts
an implicit transaction even if we call "SET implicit_transactions
off". So they suggested MDAC 2.6 latest service pack. I did that. But
even then the problem is persistent.
I copied the series of statements (from BEGIN TRANSACTION to COMMIT
TRANSACTION) from SQL profiler and ran them in SQL Query Analyzer. An
error occurred saying
"The COMMIT TRANSACTION request has no corresponding BEGIN
TRANSACTION".
So I commented out the lines
SET NO_BROWSETABLE ON
SET FMTONLY ON EXEC spInsert ' ', ' ' SET FMTONLY OFF
set fmtonly off
SET NO_BROWSETABLE OFF
and ran the TRANSACTION statements. It worked fine. So the problem
might be in those 4 lines.
Please help me