Sunday, March 25, 2012
Archive of DB Project Plan
Thursday, March 22, 2012
Apriori algorithm [SQL Server 2005 and ASP.NET]
Hi
I am working on academic project using SQL Server 2005 & Visual studio 2005. Using Apriori algorithm to find the association between Patient City and likely diseases.
I have created PATIENT table with Patient_Id, Patient_name, Age, City attributes and Diseases table with Disease_Id, Disease_name. Connected these two tables, MANY - MANY [M:N]. Got a third relation with Patient_Id and Disease_Id attributes.
I am just inputting some dummy data into patient table and disease tables to make Apriori algorithm work. When a new Patient City is entered into patient table, System checks Patient table for same City previously stored and using Third relation, pulls Disease that associated with the City.
Here are my tables with attributes:
PATIENT ( Patient_Id, Patient_name, Age, City)
Diseases(Disease_Id, Disease_name)
[M:N] Got third below third relation bcz its Many to Many relationship
PATIENT_DISEASES(Patient_Id, Disease_Id)
I do think and believe that there is an efficient way of doing , instead of usin dummy data or using this relationships. I did check Microsoft Association algorithm and realised it is not Apriori algorithm.
Could you suggest the best or efficient way of doing this using SQL Server 2005?
Your help and insight into this matter is highly appreciated.
Regards
Sukumar Raju
The Association Rules is Apriori. What kind of rules are you looking for? Disease --> Disease, City --> Disease, Age -->Disease? All of the above?|||
Thank you.
Looking for association between City --> Disease.
I am just inputting some dummy data into patient table and disease table to make Apriori algorithm work. When a new Patient City is entered into patient table, System checks Patient table for same City previously stored and using Third relation, pulls Disease that associated with the City.
I think i have to write T-SQL statements to get the above algorithm work. Is there other way to achieve?
Is there best way other than simply writing T-SQL statements to implement Apriori algorithm work! Am i going in the right way?
Your suggestion and help is appreciated
regards
Sukumar raju
|||You need to use SQL Server Analysis Services to use the Association Rules algorithm. You will launch BI Dev Studio and create a Analysis Services project containing a data source, data source view, and data mining structure - there are UI tools to do all of this - you don't need to write any TSQL.
I recommend checking out the resources at www.sqlserverdatamining.com - in particular the tutorial to learn how to use the DM functionality in SQL Server.
Thanks
-Jamie
|||Thank you Mr.Jammie
I would do that. Could you give any resources that are useful to achieve my objective?
regards
Sukumar Raju
|||You can try the book I wrote with Zhaohui Tang - Data Mining with SQL Server 2005 - as a starting point. It describes how to create models of the type you need.|||
Thank you!
Your help and information is much appreciated. Can I retrieve this particular association model on web page? i m using asp.net for my web interface. I think i can
1) Create association model using BI in SQL Server 2005
2) Access this model on web interface, such a way when i enter patient city in PATIENT table from Web interface, get a prompt message showing "more likely diseases" from Diseases table , that associated with the entered City.
hoping i m going in the right way. I have ordered the book writeen by you Data mining with SQL Server 2005. Shouldbe getting in couple of days.
your suggestion and help is appreciated. Please advise.
regards
Sukumar Raju
|||Sukumar Raju wrote:
Thank you! Jamie!
Your help and information is much appreciated. Can I retrieve this particular association model on web page? i m using asp.net for my web interface. I think i can
1) Create association model using BI in SQL Server 2005
2) Access this model on web interface, such a way when i enter patient city in PATIENT table from Web interface, get a prompt message showing "more likely diseases" from Diseases table , that associated with the entered City.
hoping i m going in the right way. I have ordered the book writeen by you Data mining with SQL Server 2005. Getting late...As my project submission date is just 5 days away.
your suggestion and help is appreciated. Please advise.
regards
Sukumar Raju
Wednesday, March 7, 2012
Application Role
I have an Access 2003 project which uses a "dummy" login for the standard
connection without any rights on objects. After establishing the ADO
connection via VBA code I enable the application role which is then used to
do all database access
cn.Execute "EXEC sp_setapprole 'App_Role', '" & strPW & "'"
This works fine unless I assign a rowsource via a code line like this
...Form.RecordSource = "Exec GET_" & Str_SubFormName & " " & strWhere
This works only when I assign the dummy login the right to execute the
stored procedure. It is not enough to assign this right to the Application
role. But this means that the dummy role has access to this table via the
stored procedure which I wanted to avoid. What do I miss, or is this by
design? Any other solutions for this scenario?
TIA,
Norbert MeissNorbert Meiss (NorbertMeiss@.discussions.microsoft.com) writes:
> I have an Access 2003 project which uses a "dummy" login for the
> standard connection without any rights on objects. After establishing
> the ADO connection via VBA code I enable the application role which is
> then used to do all database access
> cn.Execute "EXEC sp_setapprole 'App_Role', '" & strPW & "'"
> This works fine unless I assign a rowsource via a code line like this
> ...Form.RecordSource = "Exec GET_" & Str_SubFormName & " " & strWhere
> This works only when I assign the dummy login the right to execute the
> stored procedure. It is not enough to assign this right to the Application
> role. But this means that the dummy role has access to this table via the
> stored procedure which I wanted to avoid. What do I miss, or is this by
> design? Any other solutions for this scenario?
Since I don't know Access, I will have to guess a little here. But I have
a feeling that the RecordSource stored procedure gets executed on a second
connection to SQL Server. In such case, that connection will not have the
application role set.
Why you get this second connection, I don't know. It could be inherent
with the Form.RecordSource thing, and thus no way to avoid it. But there
is also an issue with ADO. If you fail to get all data from a query, some
client libraries will tell you that the connection is busy, and no new
query can be submitted. But ADO tries to be a nice guy, and opens a new
connection to SQL Server and submits the qurey on that connection. This
often causes more problem than it solves. Really to avoid this, I don't
know, since I know nothing about your code. But "SET NOCOUNT ON" saves
the day in many cases, since the result sets you have not consumed,
often are rowcounts from INSERT/UPDATE/DELETE statements.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I cannot reproduce the strange behaviour from my first post - maybe I was
wrong with my observations. The SET NOCOUNT ON hint is helpful anyway. I
didn't understand its meaning so far. Thank you.
Norbert Meiss
"Erland Sommarskog" wrote:
> Norbert Meiss (NorbertMeiss@.discussions.microsoft.com) writes:
> Since I don't know Access, I will have to guess a little here. But I have
> a feeling that the RecordSource stored procedure gets executed on a second
> connection to SQL Server. In such case, that connection will not have the
> application role set.
> Why you get this second connection, I don't know. It could be inherent
> with the Form.RecordSource thing, and thus no way to avoid it. But there
> is also an issue with ADO. If you fail to get all data from a query, some
> client libraries will tell you that the connection is busy, and no new
> query can be submitted. But ADO tries to be a nice guy, and opens a new
> connection to SQL Server and submits the qurey on that connection. This
> often causes more problem than it solves. Really to avoid this, I don't
> know, since I know nothing about your code. But "SET NOCOUNT ON" saves
> the day in many cases, since the result sets you have not consumed,
> often are rowcounts from INSERT/UPDATE/DELETE statements.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||In addition to what Erland said, you are going to have intermittent
problems using application roles with Access unless you turn off
connection pooling in the connection string and test to make sure that
the application role is active before you execute any EXEC statements.
This is all going to add overhead to the app and slow it down somewhat
-- how much depends on the app and number of users. It is generally
recommended NOT to use application roles with Access because it opens
and closes connections under the covers. See
http://support.microsoft.com/defaul...B;EN-US;Q229564 for
more info on the connection pooling issue.
--Mary
On Mon, 27 Dec 2004 00:19:03 -0800, "Norbert Meiss"
<NorbertMeiss@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I cannot reproduce the strange behaviour from my first post - maybe I was
>wrong with my observations. The SET NOCOUNT ON hint is helpful anyway. I
>didn't understand its meaning so far. Thank you.
>Norbert Meiss
>"Erland Sommarskog" wrote:
>|||Thanks Mary.
While performance is not an issue so far with some 25 users and a big server
I will give it a second thougt and some testing.
Norbert
"Mary Chipman" wrote:
> In addition to what Erland said, you are going to have intermittent
> problems using application roles with Access unless you turn off
> connection pooling in the connection string and test to make sure that
> the application role is active before you execute any EXEC statements.
> This is all going to add overhead to the app and slow it down somewhat
> -- how much depends on the app and number of users. It is generally
> recommended NOT to use application roles with Access because it opens
> and closes connections under the covers. See
> http://support.microsoft.com/defaul...B;EN-US;Q229564 for
> more info on the connection pooling issue.
> --Mary
> On Mon, 27 Dec 2004 00:19:03 -0800, "Norbert Meiss"
> <NorbertMeiss@.discussions.microsoft.com> wrote:
>
>|||A good tool to use when testing is Profiler, which allows you to
eavesdrop on the actual conversation Access is having with the SQL
Server. It's interesting to see what's going on under the covers when
it comes to troubleshooting existing problems and avoiding potential
ones.
--Mary
On Mon, 27 Dec 2004 06:39:04 -0800, "Norbert Meiss"
<NorbertMeiss@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks Mary.
>While performance is not an issue so far with some 25 users and a big serve
r
>I will give it a second thougt and some testing.
>Norbert
>"Mary Chipman" wrote:
>
Saturday, February 25, 2012
Application is very Slow
Dear All,
Finally I completed my project. Thanks all you helped me to do it.
Now I have the biggest problem.
In my application the Data grid is filled with Data from SQL server table which has large number of records. When I run my queries in SS Management Studio it runs very fast. To fill data to datagrid it takes lots of time. How can I reduce this time. How can I increase the performance of my Application.
Thanks,
Janaka
Try to disable debugging and trace in your web.config after deployment.
Also for large amount of data you can use a custom paging solution to paging through data to prevent getting all the data which for sure affect the performance.
|||Thanks I did custom paging its amazing. Now ok.
Again another problem.
Users edit some records in Gridview and click update button. Data will be updated in SQL table. But I want to track what are the records actually edited.
Do you have any idea?
thanks,
Janaka
You may do this in many ways:
- You can handle the GridView RowUpdated event and log the information to say Database table ( Data may be : current login user id , Update time stamp,record id)
Hi,
You understood what I have to do clearly. But in multi editing grid view, row updating event is not firing. Because when page loading the grid is in editable mood and there is no edit button or update button inside the grid for each and every row.
Thanks,
Janaka
Sunday, February 19, 2012
Append Query Problems
I am doing an A level database project. I need to build a system to deal with ordering and selling products.
I am trying to make an append query that will append all the products bought in my "invoices" table to my "archive" table. I want invoices that are three months old to be transferred the the "archive" table. I have been trying to put a criteria in the query for the Date field but I haven't managed to find an expression that works.
I'm sorry if this is too simple and you guys think im just being stupid.
I figured it was worth asking on here!! It's really bugging me.Show us what you got so far. Also ... look up dateadd and datediff in BOL.|||Sorry, what is BOL?
"Date"<=#01/01/2006#
Is that what you mean by show? I don't know how to show the whole query :-s|||Can you write a SELECT statement to retrieve the rows that you want to archive?
-PatP|||Thanks but I don't know what a select statement is! Is it the same as a select query? there's something about that in one of my books.|||Is this project in Access? You may need to ask over in the Access forum...|||Yes it is. I realised after I posted here that there is an access forum too. I have posted in there now thanks!
Monday, February 13, 2012
App.Config files in a Custom Database Extension Class Library
Good Morning..
We're having a heck of a good time trying to implement our first CDE project in SSRS 2005.
In our SDE class library we have included an App.Config file where we want to store configuration settings..
Trouble is that when we view the configuration settings or connection string settings in debug mode, they're not being read for some reason..
Here's our app.config file:
-
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
</configSections>
<appSettings>
<add key="eventLogName" value="FocusDPEEventLog" />
</appSettings>
<connectionStrings>
<add name="PassConnString" connectionString="Data Source=SOMEDATASOURSE;Persist Security Info=True;User ID=SOMEUSERNAME;Password=SOMEPASSWORD;Unicode=True"
providerName="System.Data.OracleClient" />
</connectionStrings>
</configuration>
-
Here's what our Immediate window Debugger is tellin' us about our configuration settings:
-
ConfigurationManager.AppSettings
{System.Configuration.KeyValueInternalCollection}
[System.Configuration.KeyValueInternalCollection]: {System.Configuration.KeyValueInternalCollection}
base {System.Collections.Specialized.NameObjectCollectionBase}: {System.Configuration.KeyValueInternalCollection}
AllKeys: {Dimensions:[0]}<-incorrect should be 1
ConfigurationManager.ConnectionStrings
Count = 1 <-ok, is one, but the wrong 1, see 3 lines down...
base {System.Configuration.ConfigurationElementCollection}: Count = 1
ConfigurationManager.ConnectionStrings[0]
{data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true}<--Should be PassConnString
base {System.Configuration.ConfigurationElement}: {data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true}
ConnectionString: "data source=.\\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"
Name: "LocalSqlServer"
ProviderName: "System.Data.SqlClient"
Now notice the stuff in bold...I have NO IDEA where this gosh-danged thing is reading, but it doesn't seem like it's the app.config file in our class library...
thanks..
Doug
Hi
I am facing the same problem. I dont know where to set the custom configuration settings. I am pretty sure it will have to be somewhere in Report Server Config files, but where?
Regarding the stuff in bold you are seeing is coming from the base asp.net machine settings.
App.Config files in a Custom Database Extension Class Library
Good Morning..
We're having a heck of a good time trying to implement our first CDE project in SSRS 2005.
In our SDE class library we have included an App.Config file where we want to store configuration settings..
Trouble is that when we view the configuration settings or connection string settings in debug mode, they're not being read for some reason..
Here's our app.config file:
-
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
</configSections>
<appSettings>
<add key="eventLogName" value="FocusDPEEventLog" />
</appSettings>
<connectionStrings>
<add name="PassConnString" connectionString="Data Source=SOMEDATASOURSE;Persist Security Info=True;User ID=SOMEUSERNAME;Password=SOMEPASSWORD;Unicode=True"
providerName="System.Data.OracleClient" />
</connectionStrings>
</configuration>
-
Here's what our Immediate window Debugger is tellin' us about our configuration settings:
-
ConfigurationManager.AppSettings
{System.Configuration.KeyValueInternalCollection}
[System.Configuration.KeyValueInternalCollection]: {System.Configuration.KeyValueInternalCollection}
base {System.Collections.Specialized.NameObjectCollectionBase}: {System.Configuration.KeyValueInternalCollection}
AllKeys: {Dimensions:[0]}<-incorrect should be 1
ConfigurationManager.ConnectionStrings
Count = 1 <-ok, is one, but the wrong 1, see 3 lines down...
base {System.Configuration.ConfigurationElementCollection}: Count = 1
ConfigurationManager.ConnectionStrings[0]
{data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true}<--Should be PassConnString
base {System.Configuration.ConfigurationElement}: {data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true}
ConnectionString: "data source=.\\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"
Name: "LocalSqlServer"
ProviderName: "System.Data.SqlClient"
Now notice the stuff in bold...I have NO IDEA where this gosh-danged thing is reading, but it doesn't seem like it's the app.config file in our class library...
thanks..
Doug
Hi
I am facing the same problem. I dont know where to set the custom configuration settings. I am pretty sure it will have to be somewhere in Report Server Config files, but where?
Regarding the stuff in bold you are seeing is coming from the base asp.net machine settings.
Thursday, February 9, 2012
anyone use a 3rd party product to load balance SQL Server?
solution. The data is in 1 database. So SQL 2000 doesn't support load
balancing, and neither will 2005. That sucks. Oracle supposedly does load
balancing and I can't understand why even SQL Server 2005 won't do it. So
either I go with an 8+ CPU SQL Server or consider a 3rd party load balancer
that will somehow (?) make it work so that multiple SQL brains can balance
the load of 1 single database that is presumably shared on 1 set of disks.
I'm finding very little information on load balancing SQL Server when
searching the net. Is there a product like Legato, for example, (I haven't
tried it) that will somehow give me load balancing with SQL Server?
HK wrote:
> I'm working on a project needing a high-end large mission critical
> database solution. The data is in 1 database. So SQL 2000 doesn't
> support load balancing, and neither will 2005. That sucks. Oracle
> supposedly does load balancing and I can't understand why even SQL
> Server 2005 won't do it. So either I go with an 8+ CPU SQL Server or
> consider a 3rd party load balancer that will somehow (?) make it work
> so that multiple SQL brains can balance the load of 1 single database
> that is presumably shared on 1 set of disks.
> I'm finding very little information on load balancing SQL Server when
> searching the net. Is there a product like Legato, for example, (I
> haven't tried it) that will somehow give me load balancing with SQL
> Server?
You may want to take a look at Federated Database Servers on SQL Server
2000/2005 if that architecture will work for you.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uzLN8u5wFHA.3180@.TK2MSFTNGP14.phx.gbl...
> HK wrote:
> You may want to take a look at Federated Database Servers on SQL Server
> 2000/2005 if that architecture will work for you.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
Creating the partitioned views sounds very challenging for an already-in-use
DB that needs to be cut over in the middle of the night. Plus ongoing
maintenance. Maybe I'm missing the boat, but that's why I'd want to stay
away from federated databases.
anyone use a 3rd party product to load balance SQL Server?
solution. The data is in 1 database. So SQL 2000 doesn't support load
balancing, and neither will 2005. That sucks. Oracle supposedly does load
balancing and I can't understand why even SQL Server 2005 won't do it. So
either I go with an 8+ CPU SQL Server or consider a 3rd party load balancer
that will somehow (?) make it work so that multiple SQL brains can balance
the load of 1 single database that is presumably shared on 1 set of disks.
I'm finding very little information on load balancing SQL Server when
searching the net. Is there a product like Legato, for example, (I haven't
tried it) that will somehow give me load balancing with SQL Server?HK wrote:
> I'm working on a project needing a high-end large mission critical
> database solution. The data is in 1 database. So SQL 2000 doesn't
> support load balancing, and neither will 2005. That sucks. Oracle
> supposedly does load balancing and I can't understand why even SQL
> Server 2005 won't do it. So either I go with an 8+ CPU SQL Server or
> consider a 3rd party load balancer that will somehow (?) make it work
> so that multiple SQL brains can balance the load of 1 single database
> that is presumably shared on 1 set of disks.
> I'm finding very little information on load balancing SQL Server when
> searching the net. Is there a product like Legato, for example, (I
> haven't tried it) that will somehow give me load balancing with SQL
> Server?
You may want to take a look at Federated Database Servers on SQL Server
2000/2005 if that architecture will work for you.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uzLN8u5wFHA.3180@.TK2MSFTNGP14.phx.gbl...
> HK wrote:
> You may want to take a look at Federated Database Servers on SQL Server
> 2000/2005 if that architecture will work for you.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
Creating the partitioned views sounds very challenging for an already-in-use
DB that needs to be cut over in the middle of the night. Plus ongoing
maintenance. Maybe I'm missing the boat, but that's why I'd want to stay
away from federated databases.