Case & Nested table selection and errors

hi

Here are the two tables again.

1)PATIENT(PATIENT_ID,NAME,CITY)

2) DISEASES(DISEASE_ID,NAME)

I am trying to select patient table as case and diseases table as nested to create an association model. i m getting following error.

Disease table cannot be used as a nested table because it does not have a many-to-one relationship with the case table. You need to create a many-to-one relationship between the two tables in the data source file.

i have created a relationship by dragging Disease_id from diseases table on Patient_id in patient table. when i am trying to select Patient_id as key, City as input, it is not showing disease_id to choose as a predict column.

please suggest me if i am doing anything wrong? i have not done any thing to do my datbase, just selected the tables i want to create an association model on and trying to create association model.

your help and insight is highly appreciated.

regards

Raju

For nested table modeling, you need a one to many relationship between the patient table and the disease table. Such a relationship would relate, for instance, a patient with the diseases he suffers. Your table would typically look like this:

1) PATIENT (PATIENT_ID, NAME, CITY)

2) DISEASES (PATIENT_ID, DISEASE_ID)

In this set of tables, you can define the relationship linking Patient.Patient_ID to Diseases.Patient_ID (assuming that one or more diseases were reported for most of the patients)

In the set of tables you mentioned above, assuming that Patient.Name is the patient's name and Diseases.Name is the name of the disease, it is not possible to have such a relationship between the tables. However, in most normalized DB architectures, your tables suggest that there must be another table, linking patients to diseases. That one should be modeled as nested.

Once you get this problem solved, it is possible during mining to use the actual disease name ( by setting the Name binding for the nested Disease_id column). If you have troubles setting this binding, please post a question here

Regards

|||

Thank you!

Sorry! i didn't mention about third table patient_diseases(patient_id, Disease_id). There is thrid table with foreign keys table for patient and diseases tables.

what table should i select as case and what table as nested? After that what colums should i select from which tables as Key, input, predict? could you let me setting the Name binding for the nested Disease_id column?

Your help is appreciated.

regards

raju

|||Please see this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=690139&SiteID=1&mode=1

Capturing Error Message Text

I am trying to find and debug errors in distributed transactions. I have
altered many of the 7300 series of errors so that they are logged in the SQL
Server Error Log file. The problem I am having is that the error messages
represent the values stored in the sysmessages table, and not what would be
displayed had I ran these queries in Query Analyzer.
For example if I execute this obviously garbled passthrough query I get back
a message 7321.
SELECT *
FROM OPENQUERY(QI3T,'
SELECT sdaf
FROM sdfadsf
')
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00942: table or view does not exist
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare
returned 0x80040e14].
When I look in the SQL Log file the I see the following:
2005-01-21 23:08:23.88 spid55 Error: 7321, Severity: 16, State: 2
2005-01-21 23:08:23.88 spid55 An error occurred while preparing a query
for execution against OLE DB provider 'MSDAORA'. .
How can I capture this part of the message that I see when I execute the
statement in QA?
[OLE/DB provider returned message: ORA-00942: table or view does not exist
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare
returned 0x80040e14].
I have looked everywhere on how to actually capture error message text, and
not just the error string stored in sysmessage. I am about to give up but it
would help me sleep better if someone could definitively tell me that it is
not possible.
Thanks for the good night's rest in advance. Whoever answers this is my hero
.
JeremyHave you looked at Erland's excellent articles on error handling...
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
-oj
"Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> wrote in message
news:8B85D3FA-6C90-4A04-A4F4-3EA29AF723DF@.microsoft.com...
>I am trying to find and debug errors in distributed transactions. I have
> altered many of the 7300 series of errors so that they are logged in the
> SQL
> Server Error Log file. The problem I am having is that the error messages
> represent the values stored in the sysmessages table, and not what would
> be
> displayed had I ran these queries in Query Analyzer.
> For example if I execute this obviously garbled passthrough query I get
> back
> a message 7321.
> SELECT *
> FROM OPENQUERY(QI3T,'
> SELECT sdaf
> FROM sdfadsf
> ')
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'MSDAORA'.
> [OLE/DB provider returned message: ORA-00942: table or view does not exist
> ]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare
> returned 0x80040e14].
> When I look in the SQL Log file the I see the following:
> 2005-01-21 23:08:23.88 spid55 Error: 7321, Severity: 16, State: 2
> 2005-01-21 23:08:23.88 spid55 An error occurred while preparing a query
> for execution against OLE DB provider 'MSDAORA'. .
> How can I capture this part of the message that I see when I execute the
> statement in QA?
> [OLE/DB provider returned message: ORA-00942: table or view does not exist
> ]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare
> returned 0x80040e14].
>
> I have looked everywhere on how to actually capture error message text,
> and
> not just the error string stored in sysmessage. I am about to give up but
> it
> would help me sleep better if someone could definitively tell me that it
> is
> not possible.
> Thanks for the good night's rest in advance. Whoever answers this is my
> hero.
> Jeremy|||You could try to get the linked server to log the errors or alternatively
the client could possibly trap and log/display both messages (like query
analyser does).
I couldnt find a way to get SQL server itself to log the error thrown by the
linked OLEDB server. Although I got SQL agent to log it with no problem, I
also got a web server running ASP/ADO to trap and list both error messages:
on error resume next /* it only displays the useless one
without this */
Conn.Execute "my_sp"
for Cnt = 0 to Conn.Errors.Count-1
Response.write Conn.Errors(Cnt).Description&"<BR>"
next
hope this helps :)
Mr Tea
http://mr-tea.blogspot.com
"Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> wrote in message
news:8B85D3FA-6C90-4A04-A4F4-3EA29AF723DF@.microsoft.com...
>I am trying to find and debug errors in distributed transactions. I have
> altered many of the 7300 series of errors so that they are logged in the
> SQL
> Server Error Log file. The problem I am having is that the error messages
> represent the values stored in the sysmessages table, and not what would
> be
> displayed had I ran these queries in Query Analyzer.
> For example if I execute this obviously garbled passthrough query I get
> back
> a message 7321.
> SELECT *
> FROM OPENQUERY(QI3T,'
> SELECT sdaf
> FROM sdfadsf
> ')
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'MSDAORA'.
> [OLE/DB provider returned message: ORA-00942: table or view does not exist
> ]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare
> returned 0x80040e14].
> When I look in the SQL Log file the I see the following:
> 2005-01-21 23:08:23.88 spid55 Error: 7321, Severity: 16, State: 2
> 2005-01-21 23:08:23.88 spid55 An error occurred while preparing a query
> for execution against OLE DB provider 'MSDAORA'. .
> How can I capture this part of the message that I see when I execute the
> statement in QA?
> [OLE/DB provider returned message: ORA-00942: table or view does not exist
> ]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare
> returned 0x80040e14].
>
> I have looked everywhere on how to actually capture error message text,
> and
> not just the error string stored in sysmessage. I am about to give up but
> it
> would help me sleep better if someone could definitively tell me that it
> is
> not possible.
> Thanks for the good night's rest in advance. Whoever answers this is my
> hero.
> Jeremy|||oj,
Thanks for you reply. I took a look at the articles you suggested and it
looks like Yukon has a new method to retrieve the text of the error messages
that I am trying to obtain within SQL. This excerpt from the article confirm
s
my fears that what I need to do might not be possible the way I had hoped...
"The construct is similar to error-handling concepts in languages like C++.
If an error occurs in the TRY block, or in a stored procedure called by the
TRY block, execution is transferred to the CATCH block. In the CATCH block,
you have access to six new functions: error_number(), error_severity(),
error_state(), error_message(), error_procedure() and error_state(), that
gives you all parts of the message associated with the error. And, yes,
error_message(), is the expanded message with the parameters filled in.–"
http://www.sommarskog.se/error-handling-I.html#Yukon
Thanks again,
Jeremy Lubich
"oj" wrote:

> Have you looked at Erland's excellent articles on error handling...
> http://www.sommarskog.se/error-handling-I.html
> http://www.sommarskog.se/error-handling-II.html
>
> --
> -oj
>
> "Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> wrote in message
> news:8B85D3FA-6C90-4A04-A4F4-3EA29AF723DF@.microsoft.com...
>
>|||Thanks for you reply. Unfortunately it seems that SQL Server is unable to
provide error message text from linked servers to any error handling code
until Yukon is around. I can't wait to get at the error handling features of
Yukon, it will dramatically improve our ability to gracefully handle
disruptions of connectivitly to the many linked servers that our databases
access.
Regards,
Jeremy Lubich
"Lee Tudor" wrote:

> You could try to get the linked server to log the errors or alternatively
> the client could possibly trap and log/display both messages (like query
> analyser does).
> I couldnt find a way to get SQL server itself to log the error thrown by t
he
> linked OLEDB server. Although I got SQL agent to log it with no problem, I
> also got a web server running ASP/ADO to trap and list both error messages
:
> on error resume next /* it only displays the useless one
> without this */
> Conn.Execute "my_sp"
> for Cnt = 0 to Conn.Errors.Count-1
> Response.write Conn.Errors(Cnt).Description&"<BR>"
> next
> hope this helps :)
> Mr Tea
> http://mr-tea.blogspot.com
> "Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> wrote in message
> news:8B85D3FA-6C90-4A04-A4F4-3EA29AF723DF@.microsoft.com...
>
>

Capture errors from "isql"

Hi,

Just wanted to know if there is anyway I can capture errors generated in the SQL batch passed into isql with the "-i" option.

ie

If there is an sql batch store in file say test.sql and I pass it as input to isql as

isql -Uxxxx -itest.sql -Pyyyy

is there anyway I can figure out (outside the isql using error valriables like $status in UNIX C-shell or ERRORLEVEL in DOS) if all the SQLs in that script file (test.sql) has executed successfully or notRE:
Hi, Just wanted to know if there is anyway I can capture errors generated in the SQL batch passed into isql with the "-i" option. ie If there is an sql batch store in file say test.sql and I pass it as input to isql as isql -Uxxxx -itest.sql -Pyyyy

Q1 Is there ANY way I can figure out (outside the isql using error valriables like $status in UNIX C-shell or ERRORLEVEL in DOS) if all the SQL in that script file (test.sql) has executed successfully or not?

This may not be the desired answer, however the question asked about (ANY) way.

A1 Yes.

One approach would require some development; assuming each stored procedure or a batch is designed to return meaningful "result codes"; one could programatically parse the resulting output file to examine each "result code" in the output file e.g.(osql -Uxxxx -itest.sql -Pyyyy -oOutPutFile.out).|||I tried the "-b" option and that worked too. The %ERRORLEVEL% was able to have a non-0 value in case of errors.

Capture Error In DTS Package

I have a SQL Server Package that is dumping a text file into an Access
Database. However, due to update timing every now and then it errors
out due to a primary key violation. How do I capture that error
number in my next Active Script Task and determine whether or not I
want to continue (since if it's a duplicate I just want to skip it and
finish the DTS package). If it's a different error I want to log
it.
Thanks!Hi
If these are identity values you may want to check out
http://www.sqldts.com/293.aspx
if not try
http://www.sqldts.com/282.aspx
John
"Creative" wrote:
> I have a SQL Server Package that is dumping a text file into an Access
> Database. However, due to update timing every now and then it errors
> out due to a primary key violation. How do I capture that error
> number in my next Active Script Task and determine whether or not I
> want to continue (since if it's a duplicate I just want to skip it and
> finish the DTS package). If it's a different error I want to log
> it.
> Thanks!
>|||On Nov 27, 3:00 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> If these are identity values you may want to check outhttp://www.sqldts.com/293.aspx
> if not tryhttp://www.sqldts.com/282.aspx
> John
>
> "Creative" wrote:
> > I have a SQL Server Package that is dumping a text file into an Access
> > Database. However, due to update timing every now and then it errors
> > out due to a primary key violation. How do I capture that error
> > number in my next Active Script Task and determine whether or not I
> > want to continue (since if it's a duplicate I just want to skip it and
> > finish the DTS package). If it's a different error I want to log
> > it.
> > Thanks!- Hide quoted text -
> - Show quoted text -
Thanks for the reply but it doesn't look like that answers my
question. I'm actually using a File Transformation from a text file
to an access database. I have a connection to the text file and a
connection to an access database with a transformation connecting the
two.|||Hi
There was no mention of the destination database being an access database,
in your original post. I suggest that you therefore load into a staging table
and either do an update if the PK exists or an insert if it doesn't.
John
"Creative" wrote:
> On Nov 27, 3:00 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> >
> > If these are identity values you may want to check outhttp://www.sqldts.com/293.aspx
> > if not tryhttp://www.sqldts.com/282.aspx
> >
> > John
> >
> >
> >
> > "Creative" wrote:
> > > I have a SQL Server Package that is dumping a text file into an Access
> > > Database. However, due to update timing every now and then it errors
> > > out due to a primary key violation. How do I capture that error
> > > number in my next Active Script Task and determine whether or not I
> > > want to continue (since if it's a duplicate I just want to skip it and
> > > finish the DTS package). If it's a different error I want to log
> > > it.
> >
> > > Thanks!- Hide quoted text -
> >
> > - Show quoted text -
> Thanks for the reply but it doesn't look like that answers my
> question. I'm actually using a File Transformation from a text file
> to an access database. I have a connection to the text file and a
> connection to an access database with a transformation connecting the
> two.
>|||I don't know the answer, but it's a good question, and I'm in a
similar situation in SSIS - except I want to test for a warning code!
Googling found this crufty answer:
http://www.quest-pipelines.com/newsletter-v4/0603_E.htm
There may be a better answer if you open up the multiphase data pump
business, but I have small experience with that.
Note there is also a DTS group, I'll copy this to.
Josh
On Mon, 26 Nov 2007 13:54:33 -0800 (PST), Creative <GraberJ@.gmail.com>
wrote:
>I have a SQL Server Package that is dumping a text file into an Access
>Database. However, due to update timing every now and then it errors
>out due to a primary key violation. How do I capture that error
>number in my next Active Script Task and determine whether or not I
>want to continue (since if it's a duplicate I just want to skip it and
>finish the DTS package). If it's a different error I want to log
>it.
>Thanks!|||Hi Josh
I pointed the OP to the tutorial on Multiphase datapump on SQL DTS. In SSIS
you have a standarderrorvariable which may be of some use to you, and there
is the configure error output dialog. you may want to check out Professional
SQL Server 2005 Integration Services by Brian Knight et al ISBN 0764584359 or
the short videos on http://www.jumpstarttv.com such as
http://www.jumpstarttv.com/Media.aspx?vid=34 where Brian shows how an
application similar to the examples in the book work.
John
"JXStern" wrote:
> I don't know the answer, but it's a good question, and I'm in a
> similar situation in SSIS - except I want to test for a warning code!
> Googling found this crufty answer:
> http://www.quest-pipelines.com/newsletter-v4/0603_E.htm
> There may be a better answer if you open up the multiphase data pump
> business, but I have small experience with that.
> Note there is also a DTS group, I'll copy this to.
> Josh
>
> On Mon, 26 Nov 2007 13:54:33 -0800 (PST), Creative <GraberJ@.gmail.com>
> wrote:
> >I have a SQL Server Package that is dumping a text file into an Access
> >Database. However, due to update timing every now and then it errors
> >out due to a primary key violation. How do I capture that error
> >number in my next Active Script Task and determine whether or not I
> >want to continue (since if it's a duplicate I just want to skip it and
> >finish the DTS package). If it's a different error I want to log
> >it.
> >
> >Thanks!
>|||John,
Thanks for several good hints, I'll be following them all. Configure error
output dialog?!?! Oh, in a data flow. Righto.
I keep waiting for some other, newer SSIS books to come out.
I've got the basics grinding along now, anyway.
Thanks again.
Josh
"John Bell" wrote:
> Hi Josh
> I pointed the OP to the tutorial on Multiphase datapump on SQL DTS. In SSIS
> you have a standarderrorvariable which may be of some use to you, and there
> is the configure error output dialog. you may want to check out Professional
> SQL Server 2005 Integration Services by Brian Knight et al ISBN 0764584359 or
> the short videos on http://www.jumpstarttv.com such as
> http://www.jumpstarttv.com/Media.aspx?vid=34 where Brian shows how an
> application similar to the examples in the book work.
> John
> "JXStern" wrote:
> > I don't know the answer, but it's a good question, and I'm in a
> > similar situation in SSIS - except I want to test for a warning code!
> >
> > Googling found this crufty answer:
> > http://www.quest-pipelines.com/newsletter-v4/0603_E.htm
> >
> > There may be a better answer if you open up the multiphase data pump
> > business, but I have small experience with that.
> >
> > Note there is also a DTS group, I'll copy this to.
> >
> > Josh
> >
> >
> > On Mon, 26 Nov 2007 13:54:33 -0800 (PST), Creative <GraberJ@.gmail.com>
> > wrote:
> >
> > >I have a SQL Server Package that is dumping a text file into an Access
> > >Database. However, due to update timing every now and then it errors
> > >out due to a primary key violation. How do I capture that error
> > >number in my next Active Script Task and determine whether or not I
> > >want to continue (since if it's a duplicate I just want to skip it and
> > >finish the DTS package). If it's a different error I want to log
> > >it.
> > >
> > >Thanks!
> >
> >