prediction in ms sql server 2005

Hey

Does anyone know if the following is possible:

I want to add a column to a table that contains the predicted value according to a decision tree mining model. (I know that this is possible). But now I would like that when a new row is added to this table, and every column except the prediction column is filled in manually, can ms sql server add the predicted value automatically for this row?
I know it is possible to execute a Singleton query for this kind of single prediction, but I would like to integrate this in my data table, because for now my steps would be:
- Create the table with one prediction column
- Add the known values of all columns for one row
- Use singleton query in Mining model prediction tab to know the predicted value
- Fill in the predicted value manually in my table.

I hope my question is clear.

Thanks in advance for the help.

SmileykeYou could probably do this with an INSERT trigger on your SQL Server database table that makes a singleton prediction query via a linked server to the AS server that holds your mining model.|||And how would this query look like?
I mean, you put me in the right direction I think, but I can't make it work.

Thnx|||Please see this article I just posted for details on how to do this: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx|||Ok, I tried this, and it was very helpful, but it still doesn't work here.

Do you have any idea why the first query here works, but the second one doesn't? The error is given at the end:

1st working query:
SELECT * FROM OPENQUERY(DMServer,
'SELECT Rings from [Abalone Training Half]')

2nd not working query:
SELECT * FROM OPENQUERY(DMServer,
'SELECT Rings FROM [Abalone Training Half]
NATURAL PREDICTION JOIN
(SELECT I AS Sex,
12 AS Length,
12 AS Diameter,
12 AS Height)
AS T')

The error is:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP" for linked server "DMServer" reported an error. The provider did not give any information about the error.
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT Rings FROM [Abalone Training Half]
NATURAL PREDICTION JOIN
(SELECT I AS Sex,
12 AS Length,
12 AS Diameter,
12 AS Height)
AS T" against OLE DB provider "MSOLAP" for linked server "DMServer".

As you notice, the predicted class is here Rings, and the input attributes are sex, length diameter and height.

I really hope you can still help me.

Smileyke|||

I may be wrong, but the Sex column seems TEXT. In this case, shouldn't "SELECT I AS Sex" be actually "SELECT 'I' AS Sex" ?

In this case, your OPENQUERY should look like below (2 single quotes around I )

SELECT * FROM OPENQUERY(DMServer,
'SELECT Rings FROM [Abalone Training Half]
NATURAL PREDICTION JOIN
(SELECT ''I'' AS Sex,
12 AS Length,
12 AS Diameter,
12 AS Height)
AS T')

|||Thank you. That was indeed the problem.

Now the complete trigger works, so thank you all.

smileyke

predicate using xml in sql server 2005

Hi ,
I have a doubt using xml in sql sever 2005.
I found in many places while specifying Xpath ,the predicate contains '1'
like
/Inst:root/Inst:Location[1]/Inst:step .
I tried with some other numbers in predicate like
/Inst:root/Inst:Location[2]/Inst:step .i got null value.
Can u plz explain me what is it doing.
thx.
priya.The square brackets are an abbreviation of the position() XPath function.
E.g.:
/Inst:root/Inst:Location[1]/Inst:step
equals to
/Inst:root/Inst:Location[position() = 1]/Inst:step
Return the Inst:step child-node of the first Inst:Location node in the
Inst:root node. A null value for /Inst:root/Inst:Location[2]/Inst:step means
that there is either only one Inst:Location node or there is no Inst:step
node on the second Inst:Location.
ML
http://milambda.blogspot.com/

precisions of DateTime

Hi,
I am trying to add a uniqueness constraint on a DATETIME column of a table,
which contains rows with timestamp values differ by several milliseconds.
Upon adding the unique index, SQL complains that table contains duplicate
values are found as they are only comparing up to the second interval. Sinc
e
my database has a fixed schema, it is not possible to introduce additional
columns. Any ideas to solve this problem? Thanks.> I am trying to add a uniqueness constraint on a DATETIME column of a
table,
This is not a great idea. How can you possibly enforce that two events
can't happen at the same time? Why would you want to identify a row based
on date and time down to the millisecond? I can see why you would want a
clustered index on a datetime column, but I can't comprehend the purpose of
enforcing uniqueness there.
A|||We have an automated data loader application that loads data into the
database. In order to avoid loading duplicate rows, we are trying to add
some checking mechanism. Since the column schema is fixed, we are not able
to add Identity columns so we will have to add uniqueness constraints agains
t
the existing columns. There are 15 columns in total and I am unsure whether
it is a good idea to make add the uniqueness constraints against all 15
columns. Please advise.
"Aaron [SQL Server MVP]" wrote:

> table,
> This is not a great idea. How can you possibly enforce that two events
> can't happen at the same time? Why would you want to identify a row based
> on date and time down to the millisecond? I can see why you would want a
> clustered index on a datetime column, but I can't comprehend the purpose o
f
> enforcing uniqueness there.
> A
>
>|||> the existing columns. There are 15 columns in total and I am unsure
whether
> it is a good idea to make add the uniqueness constraints against all 15
> columns. Please advise.
Is it really only a duplicate if ALL 15 rows are identical? That seems to
be a much different set of criteria than just checking the datetime value.
Why does the application load duplicate rows in the first place? Can't you
fix that problem? You seem to be trying to solve application problems with
constraints...|||If all 15 rows are identical then the row is identical. The application is
maintained by another team and changing its design would be my last resort.
Because the INSERT INTO statement is "hard-coded" within the application, I
am trying to find solutions at the database level to fix this. Another thin
g
is, users can load data manually into the database and we are trying to
eliminate rows that were manually loaded. Do you have any ideas to solve
this problem?
Thanks.
"Aaron [SQL Server MVP]" wrote:

> whether
> Is it really only a duplicate if ALL 15 rows are identical? That seems to
> be a much different set of criteria than just checking the datetime value.
> Why does the application load duplicate rows in the first place? Can't yo
u
> fix that problem? You seem to be trying to solve application problems wit
h
> constraints...
>
>|||Does the application *only* insert? If so, you could make a new table with
the structure of the real table, swap the names, and have the app insert
into the copy. Then, use a scheduled job to insert *unique* rows into the
real table, and then either log or discard the duplicates.
Better yet, make the app call a stored procedure instead of running its own
queries, then you can have a lot more control.
I'm still not clear on how defining a unique constraint on a datetime column
would ever have done anything to solve this problem.
"Terence Ip" <TerenceIp@.discussions.microsoft.com> wrote in message
news:8E1596D8-BE76-488E-B107-B48A46A94F2B@.microsoft.com...
> If all 15 rows are identical then the row is identical. The application
is
> maintained by another team and changing its design would be my last
resort.
> Because the INSERT INTO statement is "hard-coded" within the application,
I
> am trying to find solutions at the database level to fix this. Another
thing
> is, users can load data manually into the database and we are trying to
> eliminate rows that were manually loaded. Do you have any ideas to solve
> this problem?
> Thanks.
> "Aaron [SQL Server MVP]" wrote:
>
15
to
value.
you
with|||Defining unique constraint on datetime is just a way to avoid loading
duplicate rows. With few exceptions, the values in my datetime column will
be able to determine whether the rows are re-imported. This is why I though
t
of that solution at the beginning.
Thanks for your tips. I will take this up with the application owner.
"Aaron [SQL Server MVP]" wrote:

> Does the application *only* insert? If so, you could make a new table wit
h
> the structure of the real table, swap the names, and have the app insert
> into the copy. Then, use a scheduled job to insert *unique* rows into the
> real table, and then either log or discard the duplicates.
> Better yet, make the app call a stored procedure instead of running its ow
n
> queries, then you can have a lot more control.
> I'm still not clear on how defining a unique constraint on a datetime colu
mn
> would ever have done anything to solve this problem.
>
>
> "Terence Ip" <TerenceIp@.discussions.microsoft.com> wrote in message
> news:8E1596D8-BE76-488E-B107-B48A46A94F2B@.microsoft.com...
> is
> resort.
> I
> thing
> 15
> to
> value.
> you
> with
>
>

power and ^

Hi,
I am try to replicate an access query that contains the following syntax
173.3^2 which in access = 17.33
the access help describes the ^ operator as Used to raise a number to the power of an exponent.

I tried to replicate this with the power function
power(173.3,2) = 33043.29
so i guess power is not the right way to replicate this.
can any tell me how i can replicate this correctly ?Access also return the same result as power() function. What is the logic behind here.|||It turns out that it was the way that access priorities it's mathematic operations,
it did a ^ before a division.
so 173.3/10^2 results in 173.3 /100.
i had written the t-sql code as power(173.3/10,2) when it should have been 173.3/ power(10,2)
so my own fault did not read the code right.sql

Possible to set http://localhost/Reports default start report?

SQL 2005
Is it possible to set a default start report? I have a report which contains hyperlinks to all the other reports, I'd like to just load this one by default when a user hits: http://machine/Reports

Perhaps I'm barking up the wrong tree and need to set this in IIS? I want users to still be able to make their way back to the /Reports folder lists also.

- Thanks in advanceI would rather set a new virtual directory with pointing / redirecting to the new folder than messing with the old settings. But with that there would be not point in returning to the main folder structure, unless you put it as a hyperlink inyour report.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Possible to disable welll-formedness checking when inserting XML data.

Hi All,

Can anyone help with the following problem? I have a database which
contains a table with a 'text' field, and the text field contains an
xml document - typically 50-100K. Now I'd like to make use of SQL
Server 2005s XMLData type. To do this I have created a new field of
the 'xmldata' datatype, and run an SQL statement to update the contents
from one field to another - hoping to end up with a complete table of
xml (based on the old text field).

The problem I have is after a minute or so, it must come across an
badly-formed xml fragment because I get the following message:

Msg 9436, Level 16, State 1, Line 1
XML parsing: line 1, character 67640, end tag does not match start tag

Can I turn off the checking during the update, or is it not possible to
add badly formed data to the xmldata field. Any help appreciated as
the table runs into tens of thousands of rows, so I can't really check
the contents of each!

Many thanks,

Duncan.(DSmith1974@.googlemail.com) writes:
> Can anyone help with the following problem? I have a database which
> contains a table with a 'text' field, and the text field contains an
> xml document - typically 50-100K. Now I'd like to make use of SQL
> Server 2005s XMLData type. To do this I have created a new field of
> the 'xmldata' datatype, and run an SQL statement to update the contents
> from one field to another - hoping to end up with a complete table of
> xml (based on the old text field).
> The problem I have is after a minute or so, it must come across an
> badly-formed xml fragment because I get the following message:
> Msg 9436, Level 16, State 1, Line 1
> XML parsing: line 1, character 67640, end tag does not match start tag
> Can I turn off the checking during the update, or is it not possible to
> add badly formed data to the xmldata field. Any help appreciated as
> the table runs into tens of thousands of rows, so I can't really check
> the contents of each!

Indeed, you can only pass valid XML fragments to the xml data type. They
don't have to be valid documents, that is have exactly one top-level tag,
but apart from that they must follow the XML syntax. The reason is that
the XML is stored an internal format, so SQL Server have no idea of what
do with the poorly formed XML.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Possible border width bug

I have a simple report taht has no groups and contains a few iif's here and
there to colorize a few strings. I set up a border at the bottom of my
detail row, it's a 1pt solid border. When i render to pdf, it seems that
some lines are doubled and some others not. I double checked my data source
and i don't see anything like empty data rows or anything. I tried to remove
all border information and tried to set a border on only one cell and i still
got this problem. Other developpers tried to help me and found out the same
thing is happening in their reports. You can easily see it when you zoom at
around 800% in adore reader. It barely shows on paper but on some darker
printers it shows enough to be noticeable...
Right now the only explanation i can think of is that it could be a bug but
this seems too easy for a conclusion...I think this might be a bug with the calculation precision used in
positioning items. We have made some fixes in this area for SQL 2005. I
would be interesting to see if it still repros there.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"/dev/null" </dev/null@.discussions.microsoft.com> wrote in message
news:D940A89A-5B4B-4F06-B89F-1BAF2DCD01E0@.microsoft.com...
>I have a simple report taht has no groups and contains a few iif's here and
> there to colorize a few strings. I set up a border at the bottom of my
> detail row, it's a 1pt solid border. When i render to pdf, it seems that
> some lines are doubled and some others not. I double checked my data
> source
> and i don't see anything like empty data rows or anything. I tried to
> remove
> all border information and tried to set a border on only one cell and i
> still
> got this problem. Other developpers tried to help me and found out the
> same
> thing is happening in their reports. You can easily see it when you zoom
> at
> around 800% in adore reader. It barely shows on paper but on some darker
> printers it shows enough to be noticeable...
> Right now the only explanation i can think of is that it could be a bug
> but
> this seems too easy for a conclusion...
>|||Thanks for the information, i appreciate the quick response.
"Brian Welcker [MSFT]" wrote:
> I think this might be a bug with the calculation precision used in
> positioning items. We have made some fixes in this area for SQL 2005. I
> would be interesting to see if it still repros there.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "/dev/null" </dev> wrote in message
> news:D940A89A-5B4B-4F06-B89F-1BAF2DCD01E0@.microsoft.com...
> >I have a simple report taht has no groups and contains a few iif's here and
> > there to colorize a few strings. I set up a border at the bottom of my
> > detail row, it's a 1pt solid border. When i render to pdf, it seems that
> > some lines are doubled and some others not. I double checked my data
> > source
> > and i don't see anything like empty data rows or anything. I tried to
> > remove
> > all border information and tried to set a border on only one cell and i
> > still
> > got this problem. Other developpers tried to help me and found out the
> > same
> > thing is happening in their reports. You can easily see it when you zoom
> > at
> > around 800% in adore reader. It barely shows on paper but on some darker
> > printers it shows enough to be noticeable...
> >
> > Right now the only explanation i can think of is that it could be a bug
> > but
> > this seems too easy for a conclusion...
> >
> >
>
>

Positioning Tables and Graphs On A Page using Rectangles (or anything!)

I am creating a report that contains several tables and graphs. The report should fit on one page. It is a dashboard type of report that gives a quick look at key information.I have several sections that have related objects - a table and a graph for the same subject. I grouped the subjects into rectangles so that I could have a nice border around each section. However, when the report is rendered in IE, the rectangles do not stay together, and I have white space between them. I tried putting a background color on the body, but the background color does not show when the report is rendered in IE.

I tried a work-around using lines, not rectangles, to separate sections. The spacing seems to be working, but again the background color is not showing on the rendered report, so that is not a viable option.

As a work-around for my work-around, I put the objects within one rectangle, again using lines to separate sections. However, the spacing of the objects within the rectangle is not working - I am left with large blank spaces again.

Has anyone run into the same problems?

TIA,

eBeth

I haven't used rectangles, but I have had luck using the table control. Even when I only needed a label and textbox, I found adding these to a table kept my data where I wanted it. This might be an option for you as well. For the report where this was the most useful I had a main table across the top, in the middle I have 3 more tables (small, 2 are single lines) spaced appropriately, and the bottom of the report has yet another table similar to the main table. I know its hard to visualize, but all the objects remain in the correct position.

Simone

|||

Hi, eBeth,

See if this helps: Rendering Considerations for Automatic Sizing and Positioning .

In short, the position of report items in relation to each other influences how they behave when the report is rendered. Also, the white space on the background of the report design surface is preserved, so check that the rectangle border you create around the table & graph is not pushing the rendered report over the page boundary, and eliminate any of the white surface past the edges of your report items.

======================================

New: Search scoped to just SQL Server Books OnLine: http://search.live.com/macros/sql_server_user_education/booksonline

|||

Hi,

Well, the answer seems to be trial and error with tables and other graphical elements. I have the page looking "OK," and am in the process of putting more information in tables. I now have section headings in the first table of the section, and am using tranparent rows in tables to space elements. It is not the best solution, but there it is. I read the online references; they didn't help very much. One of the main problems is that it has to look good on the web and when downloaded to Excel or PDF.

Thanks,

eBeth

Positioning Tables and Graphs On A Page using Rectangles (or anything!)

I am creating a report that contains several tables and graphs. The report should fit on one page. It is a dashboard type of report that gives a quick look at key information.I have several sections that have related objects - a table and a graph for the same subject. I grouped the subjects into rectangles so that I could have a nice border around each section. However, when the report is rendered in IE, the rectangles do not stay together, and I have white space between them. I tried putting a background color on the body, but the background color does not show when the report is rendered in IE.

I tried a work-around using lines, not rectangles, to separate sections. The spacing seems to be working, but again the background color is not showing on the rendered report, so that is not a viable option.

As a work-around for my work-around, I put the objects within one rectangle, again using lines to separate sections. However, the spacing of the objects within the rectangle is not working - I am left with large blank spaces again.

Has anyone run into the same problems?

TIA,

eBeth

I haven't used rectangles, but I have had luck using the table control. Even when I only needed a label and textbox, I found adding these to a table kept my data where I wanted it. This might be an option for you as well. For the report where this was the most useful I had a main table across the top, in the middle I have 3 more tables (small, 2 are single lines) spaced appropriately, and the bottom of the report has yet another table similar to the main table. I know its hard to visualize, but all the objects remain in the correct position.

Simone

|||

Hi, eBeth,

See if this helps: Rendering Considerations for Automatic Sizing and Positioning .

In short, the position of report items in relation to each other influences how they behave when the report is rendered. Also, the white space on the background of the report design surface is preserved, so check that the rectangle border you create around the table & graph is not pushing the rendered report over the page boundary, and eliminate any of the white surface past the edges of your report items.

======================================

New: Search scoped to just SQL Server Books OnLine: http://search.live.com/macros/sql_server_user_education/booksonline

|||

Hi,

Well, the answer seems to be trial and error with tables and other graphical elements. I have the page looking "OK," and am in the process of putting more information in tables. I now have section headings in the first table of the section, and am using tranparent rows in tables to space elements. It is not the best solution, but there it is. I read the online references; they didn't help very much. One of the main problems is that it has to look good on the web and when downloaded to Excel or PDF.

Thanks,

eBeth