set @v_dbquery = char(39)+'SELECT
[ProspectAlternateKey],
[FirstName],
[LastName],
[MaritalStatus],
[Gender],
[YearlyIncome],
[TotalChildren],
[NumberChildrenAtHome],
[HouseOwnerFlag],
[NumberCarsOwned]
FROM
[dbo].[ProspectiveBuyer]'+char(39);
set @v_query = 'SELECT
[TM_Cluster].[Bike Buyer],
t.[ProspectAlternateKey],
PredictProbability([TM_Cluster].[Bike Buyer])
From
[TM_Cluster]
PREDICTION JOIN
OPENQUERY([Adventure Works DW],@v_dbquery) AS t
ON
[TM_Cluster].[Marital Status] = t.[MaritalStatus] AND
[TM_Cluster].[Gender] = t.[Gender] AND
[TM_Cluster].[Yearly Income] = t.[YearlyIncome] AND
[TM_Cluster].[Total Children] = t.[TotalChildren] AND
[TM_Cluster].[Number Children At Home] = t.[NumberChildrenAtHome] AND
[TM_Cluster].[House Owner Flag] = t.[HouseOwnerFlag] AND
[TM_Cluster].[Number Cars Owned] = t.[NumberCarsOwned]'
-- print @v_query
set @full_query = 'select * from openquery (DMserver,'+char(39)+ @v_query +char(39)+')' ;
Hi all, I am trying to create a diagram for our database, during the creating, I create some of the relationships which were not there(basically our original database is not relational database, that's why I am doing it) So sometimes I have to chage data type in order to create a relationship for the coloumns in different tables. i.e. change char(16) to varchar(7) (I checked the field that make sure all the data in this field is <= 7 characters)
But when I saved the diagram, there is an error message that state: Errors were encountered during the save process. Some of your database objects are not saved on your diagram.
'agent' table saved successfully 'VisitUSA' table - Unable to create relationship 'FK_VisitUSA_agent'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_VisitUSA_agent'. The conflict occurred in database 'CMC', table 'agent', column 'AgentCode'.
What does that mean? is it caused by some of the agentcode data in VisitUSA table which is not in agent table? Thanks! Betty
hello, I am beginner for asp.net and sql server. I used Sql server manegement studio full version and I exported my aspnetdb which was created by VS2005 to my host sql server. I have a question: relational tables are not relational no longer. I noticed that when I created database diagram. what is wrong by exporting? thanks for your helps...
I've been playing around with the association mining model in SQL server 2005 and built a market-basket analysis of my data that I'm pretty happy with. The next task for me is figuring out how to run DMX queries against the data that I've just mined, so we may possibly use it in a web based application. This wouldn't necessarily be a difficult problem (and still may not be), but every example I've seen for the Mining Model Prediction Designer uses relational databases and I built my mining model off OLAP. Therefore, my predictable attribute is nested and when relating the mining model structure to the relational database that the cube was built off always gives me an error:
"Errors in the high-level relational engine. The 'CompanyName' column could not be found in the top-level clause of the SHAPE statement."
What I would like to do, and I'm not really even sure how I should structure any of my queries, is feed the model a product and have it return a listing of all the products it predicts. Currently, I've only been able to get the designer mode to process a singleton query, and even that didn't return any useful data. I know that this probably can be done pretty easily so any advice you may be able to offer would be greatly appreciated!!
So you may better understand my question, my association mining structure hierarchy looks as this..
[Model] ProductRecommend
[Case][Key]CustomerList
[Case][Attribute]CompanyName
[NestedTable]Product
[Nested][Key]PRODUCTCLASSID
[Nested][Attribute]PRODUCT
With that in mind, I'm trying to perform a query simliar to this:
SELECT
PredictProbability([ProductRecommend].[Product].[PRODUCTCLASSID]), <---- Throws Error for PredictProbability syntax no matter what I try to get to [PRODUCTCLASSID]
(SELECT [PRODUCT] FROM [ProductRecommend].[Product])
I would like to use analysis services to analyze stock prices.
I want to find conditional probabilities: P (YpriceChg >= 10% s.t. Ydate between A and B| X Price Chg >= 20%)?
€¦ Like given a price change of X percent or greater, predict the probability of a price change of Y percent or greater, within a specified time window (like 2 days, 3 months etc.).
I also want to add a support filter, like:
N > 30 cases (i.e., there have been at least 10 instances of a 10% or greater price change, for the chosen time window)
I have a database of prices, monthly, daily, etc. I also have a number of cols that compute statistics such as pChg1M, pChg-1M, vChg1d. Like price chg 1 month forward, price change 1 month backward, volumeChg1d forward. Ideally, I would like to minimize the column flags necessary for the experiment. Can you offer some hints, as far as setting up appropriate columns/flags and choosing a algorithm (maybe decision trees, association rules, or NB)?
After having built a decision tree model to predict a boolean output attribute using 64-bit SQL Server 2005 (build 9.0.3054), we have observed that predictions for some cases are being done at non-leaf nodes in the tree.
Specifically, after executing a prediction join which returns:
and comparing the values of PredictNodeID(MiningModel.OutputAttribute) with the mining model content column [NODE_UNIQUE_NAME] to determine the actual "rule" used to make the case-level prediction.
We have observed that for a subset of cases, predictions are being made at nodes in the tree that are not leaf nodes. Specifically, predictions are being made at a node that is 3 levels deep. The leaf nodes below this inner-tree node are 2 levels further down the tree.
Also supporting the fact that that predictions are being made at this non-leaf node is that the PredictProbability corresponds exactly with the output attribute distribution at this non-leaf node.
In this particular application, we would have obtained better results if the predictions were made at the leaf-nodes.
A few questions: 1. Why are predictions with decision trees made at non-leaf nodes? 2. Is there a way to "force" predictions to occur at leaf nodes via DMX?
I have a mining structure that I am using to perform a text-mining classification task. The mining structure contains three models: a decision tree, a naive bayes and a neural network.
Both the decision tree and the naive bayes models process without any problems, but I am having significant difficulties with the neural network model.
Initially when I processed the model, processing would fail altogether with the following error message:
"Memory error: Allocation failure : Not enough storage is available to process this command"
This was remedied by taking the steps prescribed in http://support.microsoft.com/kb/917885 (I upgraded to SQL 2005 SP1 and applied all available hotfixes listed in http://support.microsoft.com/kb/918222/). This got me to the point where the model (seemingly) processed correctly after restricting the MAXIMUM_INPUT_ATTRIBUTES to a relatively low number. So after processing, I went to try and browse the neural network model and view the lift chart...
<error>
"Execution of the managed stored procedure GetAttributeScores failed with the following error: Exception has been thrown by the target of an invocation.Input string was not in a correct format.."
</error>
(see http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=935340&SiteID=17)
Also when I would attempt to view the lift chart and the classification matrix the queries would time out with the following error message:
<error>
XML for Analysis parser: The XML for Analysis request timed out before it was completed.
Execution of the managed stored procedure GenerateLiftTableUsingDatasource failed with the following error: Exception has been thrown by the target of an invocation.Microsoft::AnalysisServices::AdomdServer::AdomdException.
</error>
Now, my poking around on Technet lead me to believe that this issue could finally be resolved by uprading to the CTP release of SQL server 2005 SP2. Now I am still encountering problems. When I go to browse the model in the Neural Network Viewer, I see the correct drop down menus to select attributes and attribute values in the "Input" and "Output" panes but I see no data displayed in the "Variables" pane at the bottom.
Interestingly, while I cannot view the model contents in the graphical viewer, the mining model contents viewer reveals model contents that look to be pretty normal for a trained neural network.
Attempts to view the lift chart time out with the error message:
<error>
XML for Analysis parser: The XML for Analysis request timed out before it was completed.
Execution of the managed stored procedure GenerateLiftTableUsingDatasource failed with the following error: Exception has been thrown by the target of an invocation.Microsoft::AnalysisServices::AdomdServer::AdomdException.
</error>
and when I run predictions against the trained NN model in the "Mining Model Prediction" pane it predicts the same value for every case in the testing set.
I have a membership database and a profile database with a userid column in both. The foreign key is in the profile database where i want a persons profile details to go like location,occupation and what not. How do I make it so when a new user registers a userId is created in the profile database that matched the userid in the membership database so i can query out profile details based on a registered members userid?
Hi all! I am working on a piece of SQL at the moment and I'm getting a little confused. I have 3 tables: Items, Attributes and a table linking them. I have 5 attributes and an item can have any of the 5 attributes. So my linking table holds the ItemID and the AttributeID and there can be 1-5 entries for each Item. A user can search for items based on Attributes; so they can tick 5 checkboxes that represent the 5 Attributes. So I need to build a query based on their choices. At the moment I'm using:Select * FROM ItemsINNER JOIN linking on Link_ItemID = Item_IDWHERE Link_AttributeID IN (10, 13, 17)But this brings out the Item that have either AttributeID of 10 or 13 or 17 whereas I need it to pull out ONLY items that have a AttributeID of 10 AND 13 AND 17.Can anyone help with this query? Sorry if this is badly worded. The solutions is prolly something really simple I have overlooked... :SI've also tried:Select * FROM ItemsINNER JOIN linking on Link_ItemID = Item_IDWHERE Link_AttributeID = 10 AND AttributeID = 13 etcBut obviously that won't work! :s
Table1 contains ID,Name Table2 contains ID,Marks,Foreign Key ie Primary key of the table Table1.
Give me an information how to create these two tables in SQLServer (I know how to create a table but i don't know how to create a table which includes Foreign Key.)
Then using Dataset i want to display the records as Name,Marks which are stored in two tables.
I have studied that in ADO Join query and record set object is used but it gives a problems and it is not good when we want to transfer the data between two applications or pages but dataset solves all those problems.
what is the difference in making relational databases in SQL vs. Access? Do you need to make them if you will be using ColdFusion? What do the other fields in minor tables need to be in order to connect them to a major table when it will all be based on a number that is in the major table? i'm kind of confused on this issue...Thanks!
I created the third table to hold the employeerenewal dates, I did this because I cant have repeating vaules in the primary table. I am just making sure that my course of action is the correct one. The purpose of the Employeerenewals is to give the users an indication that a certain renewal has been processed, because in the past there was several hundred that were not processed. The problem was that the users had no way of knowing this. What I was thinking was having the primary table calculate a renewal date based off the finalsuit and show the results in the Employeerenewals with a yes of no drop down. Now the yes or no drop down box will be give the users their red flag if the renewals have been processed or not.
Hi I am developing a website current and i have a database that seems to be growing very rapidly.. I would like to ask if anyone knows of any good tutorials/examples on relational databases? Also does anyone know of any good database design tutorials/examples?
If it helps i am using Microsoft Visual Web Developer
My question: How to have an unknown number of authors returned for a list of books, with the multiple authors being returned on the SAME row as each book
My table structure:
Table A - Books ------------------- BookID | BookName ------------------- 1 | Book One
Hi,I have a very simple question.In what cases are relational databases necessary?Are they really necessary in cases where only asingle type of query is to be performed based on one uniquefield or can we just put all fields together in a single databaseand just access them through that unique field?
Hi,This question has been bothering me for some time. A lot of peopleseem to "think" XML is the king of data problems, and I've heard thatnext version of SQL Server is going to have a strong XML flavor,meantime, I seem to get the impression that a large number ofhard-core relational model gurus do not seem to be that impressed withXML (technical value of this extra layer seems to be limited whilebusiness value might be substantial for instance, more software work,more disk space requirement etc. etc.). What's your take on this?Generality or specifics, all welcome. One specific question is, howcan XML supplement relational model?Thanks.
I have a few questions for you guys. I have a client application that can be offline or online. While offline, records can be added and need to be later synced to production.
I will use rda to pull the table down, and this is working fine. Now what if I have multiple tables where I want a foreign key relationship?
With rda I can only pull down one table at a time from everything I've read. Now say create a constraint after pulling the two or more tables down. While in offline mode I create a new record on two seperate tables with foreign key/primary key relationship.
When I do the push to the server will it automatically update the foreign key reference (locally) to the right one on the production server? Or will I get a duplicate primary key error? On the production server the primary key will be different because of the identity. This is very important because I will have multiple clients.
Hi, not sure if this is the right forum for this question. I am creating relational tables for the first time in sql server express. I will have an orderItems table and an orders table. the MenuItems table is the problem. It is a catalogue of books. There will be about ten columns. all are unique to each book. i.e isbn number, title, author, publisher etc. but ten columns seems to be quite cumbersome. it may be easier to break the table down into two tables (i.e. primary details and secondary details perhaps) However to populate the table in the first place it would be easier to have it as one table instead of opening and closing 2 tables Adding the odd book to the two tables in the future would not be a problem. so the question is can i create a table and then brak it into two relational tables afterwards. If so how do i do this. this is my foirst go at relational tables and i am still trying to get a handle on visualising them. If my logic is up the wall please let me know.... Nick
I am just now starting the switch from .NET 1.1 to .NET 2.0. I really like the new way of using the SQLDataSource and setting up Views declaratively as opposed to doing it all in code, which brings me to my question.In some of my applications I have a single Stored Procedure return multiple result sets to a single DataSet where I have a DataRelation set up. Then I can have nested DataGrids that use the GetChildRows() method to filter the results to display the hierarchical data. I would like to do something similar with the SQLDataSource and GridViews but haven't found a way to get multiple result sets.One thought I had was to create a Strongly Typed Dataset and then use the ObjectDataSource object, but I still didn't see a way to get child rows out of the datasource. I've seen an example that uses a <FilterParameter> to get nested data, but there is an extra trip made to the server for each parent item as it just put an extra parameter in the WHERE clause of the query.
This is a bit of a trivial question, since I know how to actually create the code in SQL. Hope this is the right section to post it in too, since it sort of concerns design I guess..
My problem is how to I represent the following SQL query in relational algebra:
SELECT * FROM Staff;
Since I'm using SELECT, I assume I use selection to represent it, so would it be as "ó(Staff)" [using the sigma symbol, in case it doesn't show]? But as I'm not specifying a predicate since I wish to select all fields in Staff, would it be simply represented as "Staff"?
I appreciate any help... it's been baffling me for a while
Hi Please help for this simple problemDTS Transfer or any other method?I have Customer_Order Table and customer_Order_Details Table.For OrderID = 1, I have 3 rows of Order Details.I want to transferCustomer_Order Table for OrderID 1 in DTS, the system should transferOrder Table as well as Order details table Rows for ORderID =1.How to customize in DT or is there any way to get this data to transferfrom source DB to Dest DB?KAMAL KUMAR V--Posted via http://dbforums.com
I understand the basics for doing an insert. Their seems to be many ways of achieving the same thing in .net. I have used the tableadpaters and sqlcommand functionality to achieve this. My one question is how foregin keys should be created in associated tables. EG so if i create a new record entry in one table and the primary key for that entry is a foreign key in another table, do I need to call 2 table adapters, or run 2 sqlcommands(taking the primary key from the first traction and use this in the next transaction)?
Any help or direction for some good tutorials on this would be much appreciated.
Is there a good, common way to migrate data from relational tables into hierarchical json?
I am asking this because, basically, I prefer not to write a lot of procedural code.
I am currently working with MS SQL Server db and I’ll soon need to do the same with My SQL db. I use java and java db connectivity for access. It would be great if any of these two db’s can somehow assemble the data server-side.
I've not found much guidance so far about the pros and cons of mining OLAP cubes vs their underlying fact and dimension tables. Can anyone offer advice in this area, or point me to more info about this?
Hi i am trying to use this query to pull all the publications stored in the database and all the authors contributing to that publication (1 to many relationship). I am trying to use a sub query so that i can display the results on one row of a gridview (including a consecutive list of all the authors). However i am recieving this error: Incorrect keyword near the word SET. ?
Maybe i need to add a temp column in the sub query to pull all the related authors for a single publication - but i dont know the sql for this? can anyone help?
Thanks SELECT ISNULL(Publication.month, '')+ ' ' + ISNULL(convert(nvarchar, Publication.year), '') as SingleColumn, Publication.publicationID, Publication.title FROM Publication WHERE Publication.publicationID IN (SELECT (convert(nvarchar, Authors.authorName)) FROM Authors INNER JOIN PublicationAuthors ON Authors.authorID = PublicationAuthors.authorID) AND Publication.typeID IN (SELECT PublicationType.typeName FROM PublicationType INNER JOIN PublicationType ON Publication.typeID = PublicationType.typeID
What is the best approach for storing hierarchical data in a database? For example, if I need to store a tree menu system, how would I do that allowing for the most normalization within the database, using the least number of queries/resources when pulling the data out, and using the least amount of overhead both in storage and retrival?