I have a table containing call records, and made a mining model from that table only. The model has 3 columns : calling_number, called_number, and target_operator, using Association Rule algorithm. The key is calling_number, input was operator, and predicted column called_number.
The result shows no rule, but there are results with item-set size of 1 (column) and 2 (column). On the top record of the result, SQL Server says there are 1891 support for called_number = 1891 and operator = 'INDOSAT'.
I queried the table with this query
SELECT DISTINCT calling_number FROM call_records WHERE called_number = '07786000815' AND target_operator = 'INDOSAT';
It returns 2162 records instead of 1891. If I removed the DISTINCT qualifier, SQL Server returns 2159 records. Why is this differences with the result of mining?
This is a general question about data modeling. I'm more curious than anything else.
There is much talk about over-training data model, and I'm sure there are under training as well. As a rule of thumb, depending on the algorithm, what is a good ratio of attributes vs data points?
I deleted some records out of an entity, I'd like to keep the Codes as contiguous and incremental, meaning no breaks between the code numbers.I created a business rule and applied it but codes remain the same.
I used the "Default to a generated Value" action, then selected the Code attrib. --Saved.
Then back to the Entity, I applied business rules. But nothing seemed to have happened. As there was no change in codes.
I exporting a table of comments. There are some line returns in the comments. Some of these data are paragraphs of data! For some reason, when I am exporting the data, it treats the line return within the comment column as a new record. I am using a -c character data type so (newline character) is the row terminator. How do I get the BCP OUT to ignore a newline character within a record?
For example: ID~Comment -- ------- 1~This is a comment 2~Hi,how are (user hit carriage) you (you is part of next row in bcp out) 3~Next record
I am comparing two fields one from our legacy table and one in our new table structure that should have identical text data. The new field has an assortment of ANSI characters where the legacy data did not have these. Is there anything I can do that will ignore all ansi character differences? The only route I can think of is just do a replace on each ANSI type on the new column but there are quite a few character types.
I have a script to be used to backup a specific table in a weekly basis, here is the approach what I take:
1. script the source table's schema to a create 2. the new script:
If not exists (select * from sysobjects where name='EventlogHistory' and xtype='U') CREATE TABLE [dbo].[EventlogHistory]( [LogID] [int] IDENTITY(1,1) NOT NULL, [ProjectID] [int] NOT NULL, [Description] [nvarchar](max) NOT NULL, [EventType] [varchar](10) NOT NULL, [IP] [varchar](50) NOT NULL, [UserLogon] [varchar](30) NOT NULL, [CreatedOn] [datetime] NOT NULL, ArchivedOn datetime default getdate()) insert into EventlogHistory
It throws me the error message saying "String or binary data would be truncated" which is nonsense to me, I need to let sql ignore the error, it is ridiculous to do a max(len()) to find out as it should never happen, right?
Data flow A take data from the Excel File A, Data B from Excel File B, Data C from Excel File C. What I'd like to do is that if something goes wrong on Data Flow A I would be alerted but the package should continue to running. The same for the DataFlow B, if A it's ok go on, if B fail send me the mail but continue until the end (so running the Data Flow C).
I need to insert multiple rows for input rows that meet certain conditions.
My input data is as follows.
ZIPCode, Plus 4, Range, City, State
What I need to happen is that if there is a value in the range column that is > 0 then I need to insert a row for every range item.
For instance say I have the following data.
54952, 1001, 10, Menasha, WI
What I need imported is :
54952, 1001, Menasha, WI 54952, 1002, Menasha, WI 54952, 1003, Menasha, WI 54952, 1004, Menasha, WI 54952, 1005, Menasha, WI 54952, 1006, Menasha, WI 54952, 1007, Menasha, WI 54952, 1008, Mensaha, WI 54952, 1009, Mensaha, WI 54952, 1010, Mensaha, WI 54952, 1011, Mensaha, WI
Any help in pointing me in the right direction would be great. Thanks for your help in advance.
Hi there, I'm having a problem where the data taken from the textboxes is being inserted into a table twice. I looked online and someone on another forum was experiencing the same problem. His solution was making the "submitdsabledcontrols" attribute of the form false. That hasn't fixed the issue for me. Below is my code. I'm still learning all this so this is just a test page, hence the wacky naming conventions. When the button click calls Button1_Click, all it runs is "SqlDataSource1.Insert()" Any ideas? <%@ Page EnableEventValidation="false" Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Default3.aspx.vb" Inherits="Default3" title="Untitled Page" %> <asp:Content ID="Content1" ContentPlaceHolderID="mainContent" Runat="Server"> <form id="form1" submitdisabledcontrols=false> <br /> <asp:Label ID="Label1" runat="server">Name</asp:Label> <asp:TextBox ID="NameTextBox" runat="server"></asp:TextBox> <br /> <br /> <asp:Label ID="Label2" runat="server" Text="Label">Number</asp:Label> <asp:TextBox ID="NumberTextBox" runat="server"></asp:TextBox> <br /> <br /> <asp:Label ID="Label3" runat="server" Text="Label">Salary</asp:Label> <asp:TextBox ID="SalaryTextBox" runat="server"></asp:TextBox> <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_click" /> </form> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:pubsConnectionString1 %>" DeleteCommand="DELETE FROM [Table1] WHERE [TableID] = @original_TableID" InsertCommand="INSERT INTO [Table1] ([Name], [Number], [Salary]) VALUES (@Name, @Number, @Salary)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Table1]" UpdateCommand="UPDATE [Table1] SET [Name] = @Name, [Number] = @Number, [Salary] = @Salary WHERE [TableID] = @original_TableID"> <DeleteParameters> <asp:Parameter Name="original_TableID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Number" Type="String" /> <asp:Parameter Name="Salary" Type="Decimal" /> <asp:Parameter Name="original_TableID" Type="Int32" /> </UpdateParameters>
I have a VERY simple program, it contains a datagrid, textbox and button I want it so when I insert something into the textbox and press the button it puts it into the database that is connected to the datagrid and displays it I have the following code and I cannot get it to work: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server"> Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs) SqlDataSource1.InsertParameters.Add("@Name", TextBox1.Text) End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head> <body><form id="Form1" action="Default.aspx" runat="server"> <table> <tr> <td> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display."> <Columns> <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" SortExpression="ID" /> <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString1 %>"DeleteCommand="DELETE FROM [Table] WHERE [ID] = @ID" InsertCommand="INSERT INTO [Table] ([Name]) VALUES (@Name)" ProviderName="<%$ ConnectionStrings:DatabaseConnectionString1.ProviderName %>"SelectCommand="SELECT [ID], [Name] FROM [Table]" UpdateCommand="UPDATE [Table] SET [Name] = @Name WHERE [ID] = @ID"> <InsertParameters> <asp:Parameter Name="Name" Type="String" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="ID" Type="Int32" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="ID" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource> </td> </tr> <tr> <td align="center"> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> </td> <td> <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click1" /> </td></tr> </table></form> </body> </html>
I have data in an old database I would like to capture for my new system but I dont have the original insert scripts. Is there a tool (in SQL Server 2000 or thirdparty) that will help me export the data as SQL inserts?
m inserting some data in big-sized field and small-sized fields, data of varchar type, int's, dateTime, and others... i am using something called a stored procedure to add data into a table.. now when i execute the stored procedure my data gets truncated (although i made the sizes for my fields ridiculously big like 1000 just in case) for example if i want to enter Jasmine it only inserts 'J' in the field
I am sure there's something wrong in the stored procedure, and I am guessing it's a problem of using single vs. double quotes and stuff like that within my insert statement... the following is my stored procedure:
CREATE procedure addLabor @lName varchar, @fName varchar, @mName varchar, @title varchar, @craft varchar, @lastFour varchar, @SSN varchar, @dateOfHire varchar, @currentProj varchar, @status tinyInt, @project_id int, @updateBy varchar, @updateDate varchar, @address varchar, @email varchar, @phone varchar, @zip varchar, @myfeedBack varchar, @ethnicity varchar, @userID int as BEGIN SET NOCOUNT OFF DECLARE @newid INT insert into laborPersonal ( lName, fName, mName, title, craft, lastFour, SSN, dateOfHire, currentProj, status, project_id, updateBy,
When i use 2 single quotes it insert the following string: "@lName" not the actual value of the variable @lName my exec statement is this: EXEC addLabor 'Razor', 'Nazor', 'mid', 'Mr', 'Carpenter Forman', '1234', 'keOWVozC+wmBvaqgkVkZci5y4vFLdTKfZOVG4C6BSN6H2MBP6pdsIWA0SdPAlPJra0EjEj+uXI/kXSiBuwwnKQ==', '6/27/2005 12:00:00 AM' , 'O.C. Public Library', 1, 3, '3', '7/25/2005 2:38:02 PM', '1233 Shady Canyon, Irvine', '', '', '12345', '123-12-1234', 'African American', '3'
Hello all,I just started a new job this week and they complain about the length oftime it takes to load data into their data warehouse,which they do once a month.From what I can gather, they rebuild the indexes before the insert with an80% Fillfactor, then insert the data (with theindexes enabled), then rebuild the indexes with a 100% Fillfactor.Most of my RDBMS experience is with a different product. We would havedisabled the indexes and Foreign Keys, loaded the data, thenre-enabled them, moving any records that violated the constraints into anappropriate audit table to be checked after.Can someone share with me what the accepted "best practices" are for loadingdata efficiently into a data warehouse?Any thoughts would be deeply appreciated.Steve
In my trivial example below I have a Nationality entity. The entity just has the code and Name attributes, with the code being an automatically generated Integer. I am inserting one record and then trying to prevent the same record being inserted, by setting the ImportType to 1:
After this I truncate the staging table and repeat the process. Unfortunately a new record is entered into the Entity even though the name is identical to a record that already exists.
I am trying to create a trigger on a table. Let's call it table ABC. Table looks like this:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ABC]( [id] [uniqueidentifier] NOT NULL,
[Code] ....
When someone updates a row on table ABC, I want to insert the original values along with the current date and time getdate() into table ABCD with the current date and time into the updateDate field as defined below:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ABCD]( [id] [uniqueidentifier] NOT NULL,
[Code] .....
The trigger I've currently written looks like this:
/****** Object: Trigger [dbo].[ABC_trigger] Script Date: 4/10/2015 1:32:33 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[ABC_trigger] ON [dbo].[ABC]
[Code] ...
This trigger works, but it inserts all of the rows every time. My question is how can I get the trigger to just insert the last row updated?
I can't sort by uniqueidentifier in descending as those can be random.
I have a very simple SSIS task whcih opens a flat file data source, checks the first two characters of each row and if it is equal to "06", inserts that row into an OLDB destination table. However it is inserting EVERY row, even the ones not equal to "06" in first two columns. So, how do I get it to NOT insert the rows I don't want? Any help is very much appreciated and the following is my code:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Ok, I think this may have a simple answer. Basically I have no problems in setting up QueryString/Control/etc parameters when I use SELECT in the Configure Data Source Wizard as it prompts me for the necessary parameters. But when I try to use the Configure Data Source Wizard with an UPDATE, INSERT or DELETE it does NOT prompt me for the required parameters.Is this a bug or am I just missing something? Do I have to put them in manually or something?Thanks!
I apologize if this has been asked, but I can't find a complete answer.
We have a situation with parent/child tables which have an identity column as their PK. We need to be able to insert into the live tables from staging tables. The data in the staging tables are related via a surrogate key.
I have found the OUTPUT clause, but that can only refer to columns of the actual table (since there is no FROM clause in an INSERT). Our current best solution to this problem involves adding bogus "staging" columns to the destination tables, and removing them after we've inserted everything from staging. This is an unattractive solution to say the least.
I'll give an example that mirrors our actual solution, and ask if anyone has a better solution? ----------
Code Snippet CREATE TABLE [dbo].[TABLE_A]( [ID] [int] IDENTITY(1,1) NOT NULL, [DATA] [nchar](10) NOT NULL, [STAGING_COLUMN] [bigint] NULL, CONSTRAINT [PK_TABLE_A] PRIMARY KEY ([ID] ASC) ) GO CREATE TABLE [dbo].[TABLE_B]( [ID] [int] IDENTITY(1,1) NOT NULL, [A_ID] [int] NOT NULL, [DATA] [nchar](10) NOT NULL, [STAGING_COLUMN] [bigint] NULL, CONSTRAINT [PK_TABLE_B] PRIMARY KEY ([ID] ASC) ) GO ALTER TABLE [dbo].[TABLE_B] ADD CONSTRAINT [FK_TABLE_A_TABLE_B] FOREIGN KEY([A_ID]) REFERENCES [dbo].[TABLE_A] ([ID]) GO CREATE TABLE [dbo].[STAGE_TABLE_A]( [A_Key] [bigint] NOT NULL, [DATA] [nchar](10) NOT NULL ) GO CREATE TABLE [dbo].[STAGE_TABLE_B]( [B_Key] [bigint] NOT NULL, [DATA] [nchar](10) NOT NULL, [A_Key] [bigint] NOT NULL ) GO
The STAGING_COLUMN columns are the ones that will be added before, and dropped after.
Code Snippet DECLARE @TABLE_A_MAP TABLE ( A_ID INT, A_Key BIGINT ) INSERT INTO TABLE_A (DATA, STAGING_COLUMN) OUTPUT INSERTED.ID, INSERTED.STAGING_COLUMN INTO @TABLE_A_MAP SELECT DATA, A_Key FROM STAGE_TABLE_A INSERT INTO TABLE_B (A_ID, DATA) SELECT TAM.A_ID, STB.DATA FROM STAGE_TABLE_B STB INNER JOIN @TABLE_A_MAP TAM ON TAM.A_Key = STB.A_Key
This seems to work, but I'd really like another alternative. Even though this is happening when nobody else is using the database, I cringe at the thought of adding and removing columns just to make this work.
Here are a few of my constraints:
The above is a simplification of the actual problem. The actual problem goes about five levels deep (hence the B_Key in STAGE_TABLE_B). At the top level, our larger customer will have 100,000 rows to insert. Each level will average 3 times as many rows as the next higher level, so we're talking about real volumes here.
This has to finish over the course of a weekend.
This has to be delivered to QA this Friday Thanks for any help or insight.
I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?
Hi I came across something like 3-4-5 rule while going through datamining book....but couldn't get from where that rule has been generalized and how it really works....
How can I setup the dbs in sql server so that when I change the data in one table the changes will cascade down to the tables in my other dbs. Therefore, one database would hold a primary key table. If I had 15 other dbs, then I could somehow link them so the data changed in the primary key table of the 1st database would cascade down to the other dbs.
Hi, I have a database which saves data about bus links. I want to provide a information to passenger about price of their journay. The price depends on three factors: starting busstop, ending busstop and type of ticket (full, part - for students and old people, ...). So I created a table with three foreign key constraints (two for busstops and one for type). When the busstop is deleted or type of ticket I want all data connected with it to be deleted automatically. I wanted to use cascade deleting. But I receive a following exception: Introducing FOREIGN KEY constraint 'FK_TicketPrices_BusStops1' on table 'TicketPrices' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. How can I achieve my task? Why should it cause cycles or multiple cascade paths?
Hi,I have a table with the following columns:ID INTEGEDR,Name VARCHAR(32),Surname VARCHAR(32),GroupID INTEGER,SubGroupOneID INTEGER,SubGroupTwoID INTEGERHow can I create a rule/default/check which update SubGroupOneID &SubGroupTwoID columns when GroupID for example is equal 15 onMSSQL2000.It is imposible to make changes on client, so I need to checkinserted/updated value of GroupID column and automaticly updateSubGroupOneID & SubGroupTwoID columns.Sincerely,Rustam Bogubaev
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]
I haven't been able to find a DMX query which will spit out the cases which support a particular association rule. I was hoping it would work sort of like drillthrough but show only the cases supporting a particular rule. Am I missing something?
What I ended up doing was extracting the itemsets of the rule from the model's content then running a SQL query to retrieve the cases that contain both the left-hand and right-hand itemset of the rule. I'm hoping there's a better way.
