Showing posts with label model. Show all posts
Showing posts with label model. Show all posts

Friday, March 30, 2012

PredVariance for NN

Hi,

We're building a model using the NN algorithm, and had a question about the how the PredVariance value is computed. Our testing data set has ~28K cases, but for some reason when we run our prediction query there are only 10 unique PredVariance values generated. Why doesn't each case with a distinct predicted value have its own PredVariance value?

For e.g. here are 3 different PredValues that all have the same PredVariance (229985900) and each has a TrueVal of 0:

15307.6681537296
17759.1791905724
1843.85682442577

If you need more specific info please let me know.

Thanks.

Hello

PredictVariance outputs the error variance for the subnetwork used in executing the prediction. This is detected during training for each subnetwork and, for all predictions executed on that respective subnetwork, PredictVariance will return the same value (same applies to PredictStdev).

If your target variable is a continuous one, then there are at most 2 subnetworks built for the variable (one for the value, one for the probability of Missing state, assuming your training data contains missing values).

If your target variable is discrete or discretized, then one subnetwork is trained to predict the probability of each individual state (including Missing).

Assuming that a prediction returns TargetValue1, the associated PredictVariance should return always the same value (the error variance of the TargetValue1 subnetwork)

You mention that there are 10 unique PredVariance values generated -- it seems that your target variable is discrete with at least 10 distinct states, is this correct?

thanks

|||

Thanks Bogdan,

Our target variable is actually continuous. That's what initially prompted the question, as we expected a PredVariance to be computed for all distinct target variable values (of which there are as many as there are cases).

Does that clarify the question?

Thanks.

|||

The error variance is not computed for each distinct target variable value, but for each subnetwork.

Here are the steps for one continuous target:

- partition the training set in two blocks (training and holdout -- the HOLDOUT_PERCENTAGE and SAMPLE_SIZE parameters control the size of the partitions)

- Iteratively train one subnetwork based on the training partition, and estimate the error (at each step) based on the holdout partition

- at the end -- compute the error variance of the trained subnetwork over all the cases the holdout partition

Therefore, there is a single variance value for the whole subnetwork that predicts one continuous target.

At prediction time, Predict(Target) runs the subnetwork for the target and returns the result, PredictVariance simply returns the (pre-computed) variance for the respective subnetwork (mapped from the normalized training space to the original input space). Therefore, the value returned by PredictVariance should always be the same.

You mentioned that there are 10 distinct variance values being returned?

|||i'll read your feedback more thoroughly this evening, but I wanted to answer your question...yes, there are 10 distinct PredVariance values.|||

Hi,

It's now clear, based on your response, why we have 10 distinct PredVariance values. We were using 10-fold cross-validation and each fold has a corresponding PredVariance.

Going to follow-up with one additional post/question before we close it out....stay tuned. Smile

Thanks,

mike

PredictProbability with Association Rule model..

I have run into a .. somewhat of a "duh" question. I'm running association rule to run a basket analysis, and I'm trying to get probability of each prediction. I know this is wrong, but how do I go about running PredictProbability on each ProductPurchase prediction?

When I run the below DMX query, I get this error message...

Error (Data mining): the dot expression is not allowed in the context at line 5, column 25. Use sub-SELECT instead.

Thanks in advance...

-Young K

SELECT
t.[AgeGroupName]
, t.[ChildrenStatusName]
, (Predict([Basket Analysis AR].[Training Product], 3)) as [ProductPurchases]
, (PredictProbability([Basket Analysis AR].[Training Product].[ProductName])) as [ProductPurchases]
From
[Basket Analysis AR]
PREDICTION JOIN
OPENQUERY([DM Reports DM],
'SELECT
[AgeGroupName]
, [ChildrenStatusName]
FROM
[dbo].[DM.BasketAnalysis.Contact]
WHERE isTrainingData = 0
') AS t
ON
[Basket Analysis AR].[Age Group Name] = t.[AgeGroupName]
AND [Basket Analysis AR].[Children Status Name] = t.[ChildrenStatusName]

You can actually get the statistics directly from the Predict function call:

Note the extra flag for the Predict function (INCLUDE_STATISTICS) and the removal of the PredictProbability call. PredictProbability will not work for nested table columns

SELECT
t.[AgeGroupName]
, t.[ChildrenStatusName]
, (Predict([Basket Analysis AR].[Training Product], INCLUDE_STATISTICS, 3)) as [ProductPurchases]
From
[Basket Analysis AR]
PREDICTION JOIN
OPENQUERY([DM Reports DM],
'SELECT
[AgeGroupName]
, [ChildrenStatusName]
FROM
[dbo].[DM.BasketAnalysis.Contact]
WHERE isTrainingData = 0
') AS t
ON
[Basket Analysis AR].[Age Group Name] = t.[AgeGroupName]
AND [Basket Analysis AR].[Children Status Name] = t.[ChildrenStatusName]

The results results will include the probability/support and adjusted probability for each of the predicted items

|||Thanks so much. :-D

Prediction Query in MS Association Rules

Hi!

I'm building a mining model wiht MS Association Rules. After processing this model, the result includes some rules(example):

E = Existing, C = Existing -> B = Existing
F = Existing -> E = Existing
C = Existing, B = Existing -> E = Existing
F = Existing -> B = Existing
B = Existing, A = Existing -> C = Existing
F = Existing, B = Existing -> E = Existing
F = Existing, E = Existing -> B = Existing
D = Existing -> A = Existing
C = Existing -> A = Existing
E = Existing, A = Existing -> B = Existing

I want to buid a query that has two or more items on the left of the rules, example: E = Existing, C = Existing -> B = Existing
->I want to buid a query to predict that: when a customer buy 'E' and 'C' then he likely buys 'B'


All the rules are used when you use AR for prediction. The first place to look is the prediction query builder. There is a button on the top to switch the mode from batch to singleton. With a singleton prediction you can manually specify the inputs for your query.

The prediction function you need to specify is something like "Predict(<my nested table name>, 5)". To build such a prediction in the query builder, select Prediction Function, then Predict, then type the name of your nested table, comma, then the number of recommendations you want into the parameters box.

To see the query select the SQL mode from the toolbar.

Let me know if this helps or if you were looking for some other type of answer

THanks

-Jamie

|||

Hi!

Thanks for interesting in my question!

My domain has two tables: Customer (Customer_ID, Name, ....) and Purchase (Customer_ID, Product_Name, Quantity,...)

Creating Mining Model:

Create Mining Model ProductPredict{

Customer_ID long key,

Purchase Table Predict {Product_Name text key}

}

So, when i buid a query such as:

Select Predict(Purchase, 3)

From ProductPredict Prediction Join

(Select 'A' As Product_Name

) as customer

On [ProductPredict].[Purchase].Product_Name = customer.Product_Name;

Result as all item in the right side of the rules contain 'A' in the left side.

But I want to buid a query that result as all item in the right side of the rules contain 'A' and 'B' in the left side.?

Summary: I want to buid a query that result as all item in the right side of the rules contain some items in the left side?

|||

You need a query such as

Select Predict(Purchase, 3)

From ProductPredict Prediction Join

(select

(Select 'A' As Product_Name UNION Select 'B' AS Product_Name)

as Products

) as customer

On [ProductPredict].[Purchase].Product_Name = customer.Product_Name;

This will cause rules with A and B to fire. You may still get predictions based on A alone and B alone, though, depending on their probability and lift.

|||

Hi!

Thank you very much! That's interesting, but when i run that query, it has error, so the correct query is:

Select PredictAssociation(Purchase, 3)

From ProductPredict Prediction Join

(

select

( Select 'A' As Product_Name

UNION

Select 'B' AS Product_Name

)as Products

) as customer

On [ProductPredict].[Purchase].Product_Name = [customer].[Products].Product_Name

|||Predict is a polymorphic function - DMX maps it to the appropriate function based on the model that's being queried. In this case, it maps to PredictAssociation so you should get the same results either way. What errors did you see with the earlier query?

Prediction Query for a "weighted" clustering model

I have a question about writing a prediction query against a clustering model that has the same column added more than once.

Per Jamie, I can accomplish some crude weighting by adding a column to my model multiple times. See this post for an explnation... Now that I have that worked out, I was wondering how my DM query would look? If I have Input_A1, Input_A2 , & Input_A3 all being source from the same column in my structure do I have to reference all three when writing my prediction query?

to be most theoretically accurate, yes, however, I would check to see how the results change for your particular model as you change inputs. If you don't have any missing data, it may not make a significant difference.sql

prediction on multi columns

i have mining model with 20 columns; 10 columns are for data (A1,A2...A10)
and 10 columns are for prediction (B1,B2...B10) data is not in nest table, just one table
using Association Rules
A1 text
A2 text
...
A10 text

B1 text prediction only
B2 text prediction only
...
B10 text prediction only

i have rules as form Ai-->Bj.

i want to make a statement to prediction Bj values when i have Ai values, with Ai get from some textbox on screen, Can you show me some Examples.

Thanks

You can use PredictAssociation() in a DMX statement to get the rules. In your example, the satement will look like:

Select

PredictAssociation(Ai, INCLUDE_STATISTICS, n)

FROM

[Model]

NATURAL PREDICTION JOIN

(SELECT Value as Ai) AS T

Where Ai is replaced with the specific A column you're providing as input and the Value is the Value of the Ai column.

Hope this helps

Predicting player win over a period of time

I would like to create a simple regression equation to predict player win on their next trip. I have tried to create the model using a linear regression tree based on two players (as a test). The result gives me a single node (expected) with only a coefficient instead of a regression equation. I can do this math by hand to get a regression equation and predicted value for the next trip for each player.

The dataset I used for a simple test is.....

Trip #PlayerWin110011,2501100250210011,4502100275310011,60031002100410012,00041002175

I also tried to predict next trip worth using a forecasting model. I was able to process the model but I was not able to browse the model content in the viewer.

Ultimately, I want to predict next trip worth for individual players off of a cube. The cube has about 1.5- 6M records (multiple records per player) depending on the datasource.

FYI - I have created a working linear regression and a forecasting model off of a cube I think I am setting it up correctly.

Can you provide how the mining structure/model column for the linear regression are set up? More specifically,

1. The datatype for each column in the structure

2. The attribute (Predict, PredictOnly, Input, Ignore) set on each of the model column

Thanks

Shuvro

|||

The variables are all set with a numeric datatype in the table

The attribute for each column:

actual continuous, predict

trip -- continuous, input

win continuous, predict

player account number -- discrete, key

Basically, I want to run predict worth at the player level (return a regression equation for each player) over a large list of players. I am not trying to get one regression equation for the whole universe of players.

In the time series model - is there a limit to the number of unique cases that can be inputed into the model?

Thanks so much for your help.

|||

You can have pretty much any number of series for a time series model, I'm not sure it will help you in this circumstance.

For example, you likely don't want to cross-predict between players. For example you may want a model such as

CREATE MINING MODEL PlayerModel
(
Player TEXT KEY,
Trip LONG KEY TIME,
Win LONG CONTINUOUS PREDICT,
Actual LONG CONTINUOUS PREDICT
) using Microsoft_Time_Series

This would create a time series model that contained models for each player for Win and Actual. However, since they are marked "Predict" rather than "Predict Only" Player A's "Win" values could influence Player B's "Actual" values if the data happened to line up that way.

You would think that you could simply make a model like this

CREATE MINING MODEL PlayerModel
(
Player TEXT KEY,
Trip LONG KEY TIME,
Win LONG CONTINUOUS PREDICT ONLY,
Actual LONG CONTINUOUS PREDICT ONLY
) using Microsoft_Time_Series

This causes the time series to only be based on their own valus, and not of others so this cross-predict thing is not an issue. However, you probably want Actual to influence Win for an individual player, just not other players. The modeling scheme for this situation is to make a seperate model for each player.

Similar things would happen if you tried other regression models such as decision trees, logistic, or linear regression. Your model structure would look like

CREATE MINING MODEL PlayerModel
(
Trip LONG KEY,
Players TABLE
(
Player TEXT KEY,
Actual LONG CONTINUOUS PREDICT ONLY,
Win LONG CONTINUOUS PREDICT ONLY
)
) using Microsoft_Linear_Regression

This model wouldn't do anything (probalby return an error) because there simply are no inputs whatsoever. Again, if you changed a column to Predict (or just left it as Input), the values for different players could influence the regressions for other players. Again, the solution is to create unique models/player.

Predicting player win over a period of time

I would like to create a simple regression equation to predict player win on their next trip. I have tried to create the model using a linear regression tree based on two players (as a test). The result gives me a single node (expected) with only a coefficient instead of a regression equation. I can do this math by hand to get a regression equation and predicted value for the next trip for each player.

The dataset I used for a simple test is.....

Trip #

Player

Win

1

1001

1,250

1

1002

50

2

1001

1,450

2

1002

75

3

1001

1,600

3

1002

100

4

1001

2,000

4

1002

175

I also tried to predict next trip worth using a forecasting model. I was able to process the model but I was not able to browse the model content in the viewer.

Ultimately, I want to predict next trip worth for individual players off of a cube. The cube has about 1.5- 6M records (multiple records per player) depending on the datasource.

FYI - I have created a working linear regression and a forecasting model off of a cube I think I am setting it up correctly.

Can you provide how the mining structure/model column for the linear regression are set up? More specifically,

1. The datatype for each column in the structure

2. The attribute (Predict, PredictOnly, Input, Ignore) set on each of the model column

Thanks

Shuvro

|||

The variables are all set with a numeric datatype in the table

The attribute for each column:

actual continuous, predict

trip -- continuous, input

win continuous, predict

player account number -- discrete, key

Basically, I want to run predict worth at the player level (return a regression equation for each player) over a large list of players. I am not trying to get one regression equation for the whole universe of players.

In the time series model - is there a limit to the number of unique cases that can be inputed into the model?

Thanks so much for your help.

|||

You can have pretty much any number of series for a time series model, I'm not sure it will help you in this circumstance.

For example, you likely don't want to cross-predict between players. For example you may want a model such as

CREATE MINING MODEL PlayerModel
(
Player TEXT KEY,
Trip LONG KEY TIME,
Win LONG CONTINUOUS PREDICT,
Actual LONG CONTINUOUS PREDICT
) using Microsoft_Time_Series

This would create a time series model that contained models for each player for Win and Actual. However, since they are marked "Predict" rather than "Predict Only" Player A's "Win" values could influence Player B's "Actual" values if the data happened to line up that way.

You would think that you could simply make a model like this

CREATE MINING MODEL PlayerModel
(
Player TEXT KEY,
Trip LONG KEY TIME,
Win LONG CONTINUOUS PREDICT ONLY,
Actual LONG CONTINUOUS PREDICT ONLY
) using Microsoft_Time_Series

This causes the time series to only be based on their own valus, and not of others so this cross-predict thing is not an issue. However, you probably want Actual to influence Win for an individual player, just not other players. The modeling scheme for this situation is to make a seperate model for each player.

Similar things would happen if you tried other regression models such as decision trees, logistic, or linear regression. Your model structure would look like

CREATE MINING MODEL PlayerModel
(
Trip LONG KEY,
Players TABLE
(
Player TEXT KEY,
Actual LONG CONTINUOUS PREDICT ONLY,
Win LONG CONTINUOUS PREDICT ONLY
)
) using Microsoft_Linear_Regression

This model wouldn't do anything (probalby return an error) because there simply are no inputs whatsoever. Again, if you changed a column to Predict (or just left it as Input), the values for different players could influence the regressions for other players. Again, the solution is to create unique models/player.

Predicting in Trees

Hi! I have created a DMM using Trees. But when I go to the Mining Model Predition tab and select a Predict function, I get this in the criteria column: <Scalar column reference>[, EXCLUDE_NULL|INCLUDE_NULL][, INCLUDE_NODE_ID]. When select Result, I get this error: "An incorrect number of arguments are used in the function at line 3, column 3." I'm predicting a continuous variable.

But when I delete everything except <Scalar column reference> I get this error: "Parser: The syntax for '<' is incorrect."

When I delete everything in the criteria column, I get this: "Query execution failed."

If I change the criteria to "<Scalar column reference>,INCLUDE_NULL, INCLUDE_NODE_ID" I get the error again that the query execution failed.

I'm working from a data set I created. I had no problems with predictions using clustering, but can't seem to get Trees to work.

Hello,

<Scalar column reference> is supposed to be a placeholder for the actual column name. For example, if you are building a Decision Tree model to predict, say the [Bike Buyer] column (the example in the sample database coming with SQL Server 2005), the function call may look like: Predict( [Bike buyer]) or Predict( [Bike buyer], EXCLUDE_NULL).

Hope this helps

|||Very helpful! Thanks!

Predicting from a clustering model

Hi,

I have built a Clustering model that captures customer demographic information

and identify various hidden clusters based on the information.

What kind of predictions can I make using the above model?

You can do multiple types of queries with a clustering model:

Get the most likely cluster for each case (or a new singleton case) - SELECT Cluster() FROM [model]|||

Thanks Raman,

The 1st, 2nd and the 4th points are clear but I have certain doubts

regarding the 3rd point you mentioned

Firstly as the attributes are customer characteristics like

age, gender and other demographic information and we suppose that the values

for these always exist for all the cases (new/old). What will be the point of

making them predictable and predicting their values again.

Secondly if I want to predict an attribute(s) like

MovieBuyer (determines whether a customer has bought a movie(s) or no) for new

cases, wouldn’t it be better if I use ‘decision trees’ or ‘neural networks’

algorithm rather than clustering.

Basically I cannot imagine a scenario in which clustering

stands out as the best suited algorithm for prediction.

It seems that Clustering is best suited for exploring and

understanding the data rather than for prediction.

I hope I have expressed my problem adequately and clearly.

Any help would be most appreciated.

|||

Your assessment is correct in that clustering is more suitable for data exploration/understanding - I was just pointing out that it is *possible* to do prediction with the SQL Server DM Clustering algorithm (which was your original question).

|||Thanks ramansql

Predicting from a clustering model

Hi,

I have built a Clustering model that captures customer demographic information

and identify various hidden clusters based on the information.

What kind of predictions can I make using the above model?

You can do multiple types of queries with a clustering model:

Get the most likely cluster for each case (or a new singleton case) - SELECT Cluster() FROM [model]|||

Thanks Raman,

The 1st, 2nd and the 4th points are clear but I have certain doubts

regarding the 3rd point you mentioned

Firstly as the attributes are customer characteristics like

age, gender and other demographic information and we suppose that the values

for these always exist for all the cases (new/old). What will be the point of

making them predictable and predicting their values again.

Secondly if I want to predict an attribute(s) like

MovieBuyer (determines whether a customer has bought a movie(s) or no) for new

cases, wouldn’t it be better if I use ‘decision trees’ or ‘neural networks’

algorithm rather than clustering.

Basically I cannot imagine a scenario in which clustering

stands out as the best suited algorithm for prediction.

It seems that Clustering is best suited for exploring and

understanding the data rather than for prediction.

I hope I have expressed my problem adequately and clearly.

Any help would be most appreciated.

|||

Your assessment is correct in that clustering is more suitable for data exploration/understanding - I was just pointing out that it is *possible* to do prediction with the SQL Server DM Clustering algorithm (which was your original question).

|||Thanks raman

Monday, March 26, 2012

PowerBuilder problem

Hello,

I am trying to modify my MS SQL 2005 database by PowerDesigner model using PowerDesigner built-in Modify feature.

PD however seems to have problems reverse engineering the database.

I get tons of errors like this:
Reverse engineering tables...
Table _MyTable
Unable to list the columns.
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
SQLSTATE = 37000

It also seems that it generates incompatible SQL code when modifying the database. E.g. (Parentheses after nonclustered violates syntax)...

create table _H_IpNetworkToVlanNetwork (
ipNetAddress bigint null,
vpnNetId int null default 0,
vlanId int null,
_ChangeDate datetime null,
_ChangeType int null,
constraint PK__H_IPNETWORKTOVLANNETWORK primary key nonclustered ()
)
go

What may be wrong? Do I have badly configured PowerBuilder? Or maybe badly configured ODBC data source?

Thanks for any help.

I have PowerBuilder 12.1 and MS SQL 2005Sounds like a PB problem. So tell me again why you dropped this in the SQL Server forum?|||Oh, ok sorry, where should I put this then? Thanks|||It is partially a Power Designer problem, so knowing how it works helps, but the problem appears in SQL Server (in other words SQL 2005 is the first product to complain).

The problem is that you once had a primary key defined for the table, then you removed the PK definition. This leaves Power Designer a bit confused, since it never seems to figure out that there isn't really a PK anymore. The fine folks at Sybase have a hot fix for this problem, and it is going to be addressed in a future service pack.

The work around is to either a) re-declare a PK so that Power Designer can work with the existing table, or b) re-create the table inside Power Designer without specifying a PK. More than 95% of the time you really want to have a Primary Key and simply undefined it by accident.

-PatP|||Hello Pat,

Thanks for your reply.

I however don't seem to have any problem with primary keys. They are set the same way in the designer as they are in database. Or maybe I understood you incorrectly.

Thanks|||The empty set of parentheses in the PRIMARY KEY clause of your posted SQL indicates that PowerDesigner does not have any Primary Key defined for the _H_IpNetworkToVlanNetwork table. This normally means that you once had a Primary Key defined, but that somehow it is no longer defined.

-PatP|||Ok, now I get it. It was actually a different problem that I thought you were talking about. I think now all my problems are resolved, thanks for helping :-)

The problem was that I had wrong DMBS selected and there were some incompatibilities.

Friday, March 23, 2012

Power Failure for SQL Server 2000

We encounter a power failure for about half an hour and
the SQL Server 2000 is back again.
For production databases, we use FULL Recovery Model with
Transaction Log backed up every half an hour. After power
is up, everything works properly.
I would like to know what happens to the SQL Server 2000
when the power fails and how the data is recovered when
power is up again.
ThanksRegardless of the recovery model, each database is automatically recovered
when the instance starts. Data are read from the transaction log since the
last checkpoint and applied to the database. Uncommitted transactions are
then rolled back. The end result is that the database is recovered to the
point of the failure, less uncommitted transactions.
Your FULL recovery model and log backups provide extra protection in the
event of media loss due to hardware failure or data corruption.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
> We encounter a power failure for about half an hour and
> the SQL Server 2000 is back again.
> For production databases, we use FULL Recovery Model with
> Transaction Log backed up every half an hour. After power
> is up, everything works properly.
> I would like to know what happens to the SQL Server 2000
> when the power fails and how the data is recovered when
> power is up again.
> Thanks|||Dear Dan,
Thank you for your advice.
However, for SIMPLE Recovery Model, there will be no
transaction log backup. To what state does the database
recovered to ?
Besides, would you mind to elaborate on the extra benefit
of using FULL Recovery Model ?
Thanks again.
>--Original Message--
>Regardless of the recovery model, each database is
automatically recovered
>when the instance starts. Data are read from the
transaction log since the
>last checkpoint and applied to the database. Uncommitted
transactions are
>then rolled back. The end result is that the database is
recovered to the
>point of the failure, less uncommitted transactions.
>Your FULL recovery model and log backups provide extra
protection in the
>event of media loss due to hardware failure or data
corruption.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
>> We encounter a power failure for about half an hour and
>> the SQL Server 2000 is back again.
>> For production databases, we use FULL Recovery Model
with
>> Transaction Log backed up every half an hour. After
power
>> is up, everything works properly.
>> I would like to know what happens to the SQL Server 2000
>> when the power fails and how the data is recovered when
>> power is up again.
>> Thanks
>
>.
>|||Peter
<http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
> --administaiting best practices
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
> >--Original Message--
> >Regardless of the recovery model, each database is
> automatically recovered
> >when the instance starts. Data are read from the
> transaction log since the
> >last checkpoint and applied to the database. Uncommitted
> transactions are
> >then rolled back. The end result is that the database is
> recovered to the
> >point of the failure, less uncommitted transactions.
> >
> >Your FULL recovery model and log backups provide extra
> protection in the
> >event of media loss due to hardware failure or data
> corruption.
> >
> >--
> >Hope this helps.
> >
> >Dan Guzman
> >SQL Server MVP
> >
> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
> >> We encounter a power failure for about half an hour and
> >> the SQL Server 2000 is back again.
> >>
> >> For production databases, we use FULL Recovery Model
> with
> >> Transaction Log backed up every half an hour. After
> power
> >> is up, everything works properly.
> >>
> >> I would like to know what happens to the SQL Server 2000
> >> when the power fails and how the data is recovered when
> >> power is up again.
> >>
> >> Thanks
> >
> >
> >.
> >|||Automatic recovery (what happens when you start SQL Server) doesn't have anything to do with
backups. SQL Server records all modifications in the transaction log, regardless of recovery model.
In simple, SQL Server removes log records from the transaction log when they aren't needed anymore
for this automatic recovery.
Full recovery model allow you to backup transaction log. This has a lot of advantages, like backup
log even of the database becomes corrupt, point in time restore etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
>>--Original Message--
>>Regardless of the recovery model, each database is
> automatically recovered
>>when the instance starts. Data are read from the
> transaction log since the
>>last checkpoint and applied to the database. Uncommitted
> transactions are
>>then rolled back. The end result is that the database is
> recovered to the
>>point of the failure, less uncommitted transactions.
>>Your FULL recovery model and log backups provide extra
> protection in the
>>event of media loss due to hardware failure or data
> corruption.
>>--
>>Hope this helps.
>>Dan Guzman
>>SQL Server MVP
>>"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
>> We encounter a power failure for about half an hour and
>> the SQL Server 2000 is back again.
>> For production databases, we use FULL Recovery Model
> with
>> Transaction Log backed up every half an hour. After
> power
>> is up, everything works properly.
>> I would like to know what happens to the SQL Server 2000
>> when the power fails and how the data is recovered when
>> power is up again.
>> Thanks
>>
>>.|||To add to the other responses, automatic recovery will recover databases to
the same consistent state regardless of the recovery model.
Separately, database and transaction log backups reduce your vulnerability
to potential data loss. For example, if your power outage caused a hardware
problem that corrupted your log file, you could still restore from your most
recent database backup and then apply your log backups. At most, you would
lose one half hour of work. If only data files were lost, you could backup
the current log with NO_TRUNCATE and then restore your database and log
backups. No data would be lost in this case.
In the SIMPLE recovery model, your only recourse after losing data or log
files is to restore from your most recent database backup. All data
modifications since the backup would be lost.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
>>--Original Message--
>>Regardless of the recovery model, each database is
> automatically recovered
>>when the instance starts. Data are read from the
> transaction log since the
>>last checkpoint and applied to the database. Uncommitted
> transactions are
>>then rolled back. The end result is that the database is
> recovered to the
>>point of the failure, less uncommitted transactions.
>>Your FULL recovery model and log backups provide extra
> protection in the
>>event of media loss due to hardware failure or data
> corruption.
>>--
>>Hope this helps.
>>Dan Guzman
>>SQL Server MVP
>>"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
>> We encounter a power failure for about half an hour and
>> the SQL Server 2000 is back again.
>> For production databases, we use FULL Recovery Model
> with
>> Transaction Log backed up every half an hour. After
> power
>> is up, everything works properly.
>> I would like to know what happens to the SQL Server 2000
>> when the power fails and how the data is recovered when
>> power is up again.
>> Thanks
>>
>>.

Power Failure for SQL Server 2000

We encounter a power failure for about half an hour and
the SQL Server 2000 is back again.
For production databases, we use FULL Recovery Model with
Transaction Log backed up every half an hour. After power
is up, everything works properly.
I would like to know what happens to the SQL Server 2000
when the power fails and how the data is recovered when
power is up again.
Thanks
Regardless of the recovery model, each database is automatically recovered
when the instance starts. Data are read from the transaction log since the
last checkpoint and applied to the database. Uncommitted transactions are
then rolled back. The end result is that the database is recovered to the
point of the failure, less uncommitted transactions.
Your FULL recovery model and log backups provide extra protection in the
event of media loss due to hardware failure or data corruption.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
> We encounter a power failure for about half an hour and
> the SQL Server 2000 is back again.
> For production databases, we use FULL Recovery Model with
> Transaction Log backed up every half an hour. After power
> is up, everything works properly.
> I would like to know what happens to the SQL Server 2000
> when the power fails and how the data is recovered when
> power is up again.
> Thanks
|||Dear Dan,
Thank you for your advice.
However, for SIMPLE Recovery Model, there will be no
transaction log backup. To what state does the database
recovered to ?
Besides, would you mind to elaborate on the extra benefit
of using FULL Recovery Model ?
Thanks again.

>--Original Message--
>Regardless of the recovery model, each database is
automatically recovered
>when the instance starts. Data are read from the
transaction log since the
>last checkpoint and applied to the database. Uncommitted
transactions are
>then rolled back. The end result is that the database is
recovered to the
>point of the failure, less uncommitted transactions.
>Your FULL recovery model and log backups provide extra
protection in the
>event of media loss due to hardware failure or data
corruption.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
with[vbcol=seagreen]
power
>
>.
>
|||Peter
<http://vyaskn.tripod.com/sql_server_...ices.htm#Step1
> --administaiting best practices
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power
|||Automatic recovery (what happens when you start SQL Server) doesn't have anything to do with
backups. SQL Server records all modifications in the transaction log, regardless of recovery model.
In simple, SQL Server removes log records from the transaction log when they aren't needed anymore
for this automatic recovery.
Full recovery model allow you to backup transaction log. This has a lot of advantages, like backup
log even of the database becomes corrupt, point in time restore etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power
|||To add to the other responses, automatic recovery will recover databases to
the same consistent state regardless of the recovery model.
Separately, database and transaction log backups reduce your vulnerability
to potential data loss. For example, if your power outage caused a hardware
problem that corrupted your log file, you could still restore from your most
recent database backup and then apply your log backups. At most, you would
lose one half hour of work. If only data files were lost, you could backup
the current log with NO_TRUNCATE and then restore your database and log
backups. No data would be lost in this case.
In the SIMPLE recovery model, your only recourse after losing data or log
files is to restore from your most recent database backup. All data
modifications since the backup would be lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power

Power Failure for SQL Server 2000

We encounter a power failure for about half an hour and
the SQL Server 2000 is back again.
For production databases, we use FULL Recovery Model with
Transaction Log backed up every half an hour. After power
is up, everything works properly.
I would like to know what happens to the SQL Server 2000
when the power fails and how the data is recovered when
power is up again.
ThanksRegardless of the recovery model, each database is automatically recovered
when the instance starts. Data are read from the transaction log since the
last checkpoint and applied to the database. Uncommitted transactions are
then rolled back. The end result is that the database is recovered to the
point of the failure, less uncommitted transactions.
Your FULL recovery model and log backups provide extra protection in the
event of media loss due to hardware failure or data corruption.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
> We encounter a power failure for about half an hour and
> the SQL Server 2000 is back again.
> For production databases, we use FULL Recovery Model with
> Transaction Log backed up every half an hour. After power
> is up, everything works properly.
> I would like to know what happens to the SQL Server 2000
> when the power fails and how the data is recovered when
> power is up again.
> Thanks|||Dear Dan,
Thank you for your advice.
However, for SIMPLE Recovery Model, there will be no
transaction log backup. To what state does the database
recovered to ?
Besides, would you mind to elaborate on the extra benefit
of using FULL Recovery Model ?
Thanks again.

>--Original Message--
>Regardless of the recovery model, each database is
automatically recovered
>when the instance starts. Data are read from the
transaction log since the
>last checkpoint and applied to the database. Uncommitted
transactions are
>then rolled back. The end result is that the database is
recovered to the
>point of the failure, less uncommitted transactions.
>Your FULL recovery model and log backups provide extra
protection in the
>event of media loss due to hardware failure or data
corruption.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
with[vbcol=seagreen]
power[vbcol=seagreen]
>
>.
>|||Peter
<.htm#Step1" target="_blank">http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
> --administaiting best practices
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
>
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power|||Automatic recovery (what happens when you start SQL Server) doesn't have any
thing to do with
backups. SQL Server records all modifications in the transaction log, regard
less of recovery model.
In simple, SQL Server removes log records from the transaction log when they
aren't needed anymore
for this automatic recovery.
Full recovery model allow you to backup transaction log. This has a lot of a
dvantages, like backup
log even of the database becomes corrupt, point in time restore etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
>
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power|||To add to the other responses, automatic recovery will recover databases to
the same consistent state regardless of the recovery model.
Separately, database and transaction log backups reduce your vulnerability
to potential data loss. For example, if your power outage caused a hardware
problem that corrupted your log file, you could still restore from your most
recent database backup and then apply your log backups. At most, you would
lose one half hour of work. If only data files were lost, you could backup
the current log with NO_TRUNCATE and then restore your database and log
backups. No data would be lost in this case.
In the SIMPLE recovery model, your only recourse after losing data or log
files is to restore from your most recent database backup. All data
modifications since the backup would be lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
>
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power

Friday, March 9, 2012

possible to move nodes in Nested Set Model?

Hi,
i work with the Nested Set Model and my question is, does anybody know if it is possible to move nodes inside this Nested Set Model, and if it is possible, if there is an example.
thanks in advice
deviant69Yes. Joe Celko published an example in his SQL For Smarties book, and I think it might be in one of his online columns too.

-PatP|||i tried to find some example in the internet, but there isn't. i found lots of examples for convertig, inserting and deleting, but no example for moving.

but thanks for the advice, i will look out for the book.
deviant69|||Isn't a move just a shorthand for a delete and an insert?|||you can delete and insert, but that would mean that u have to store the data somewhere. sure, i think there is a possibility to handle it that way, but finally i found another way to handle the movements. :)

deviant69

Wednesday, March 7, 2012

Possible to Create Report model - mySQL

Is it possible to create a Report Model based on a mySQL server? i am getting
errors: ERROR [42000] [MySQL][ODBC 3.51 when connecting. Ihave been able to
write standard reports but am running into this issue. Also I was looking
into using the DotNet Data connector but cannot figure out how to use it...Microsoft Support told me that they are not supporting any Report
Models based on non-SQL Server data sources. They suggested using a
linked server and creating a view to the linked server tables in a SQL
Server database. I wish they would have been more straight forward
about this in the documentation. It contradicts what I found in there
documentation "Data Sources Supported by Reporting Services":
http://msdn2.microsoft.com/en-us/library/ms159219.aspx.