multityped column

Hi!
I have a typed xml column whith two schemas in the corresponding xml
schema collection (multityped column). Is there a simple way of
creating a new column, showing the name of the xml schema that each
row is typed to.
This is very useful to implement in cases of multiple versions of a
schema in a schema collection. Your application code could then more
easily apply schema specific logic.
I guess I could always create a computed column that uses a sql
function that figures it out, but since it seemes like a standard
problem, I first want to make sure there are no neat, built in, out of
the box solutions for this problem.
Thanks,
Paul
The easiest way to check is to use the namespace-uri() function on the
top-level elements in your XML column. There is no such built-in.
Best regards
Michael
<paululvinius@.hotmail.com> wrote in message
news:1179156961.804277.146930@.y80g2000hsf.googlegr oups.com...
> Hi!
> I have a typed xml column whith two schemas in the corresponding xml
> schema collection (multityped column). Is there a simple way of
> creating a new column, showing the name of the xml schema that each
> row is typed to.
> This is very useful to implement in cases of multiple versions of a
> schema in a schema collection. Your application code could then more
> easily apply schema specific logic.
> I guess I could always create a computed column that uses a sql
> function that figures it out, but since it seemes like a standard
> problem, I first want to make sure there are no neat, built in, out of
> the box solutions for this problem.
> Thanks,
> Paul
>
|||On 14 Maj, 21:58, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> The easiest way to check is to use the namespace-uri() function on the
> top-level elements in your XML column. There is no such built-in.
> Best regards
> Michael
> <paululvin...@.hotmail.com> wrote in message
> news:1179156961.804277.146930@.y80g2000hsf.googlegr oups.com...
>
>
>
>
> - Visa citerad text -
Thanks!
I used a computed column like: "dbo.GetNamespaceUri(MyXmlColumn)". The
function uses xpath with the namespace-uri() function to determine the
namespace. I wasnt allowed to make the column persisted though because
its "non-deterministic".
This leaves us with a design choice:
If we use this solution, will it result in a noticable performance
impact? Maybe a insert/update trigger that fills the value is a more
performance friendly solution?
(We expect more queries than non-queries on this table)
|||Did you define the function as schema bound? That should make it
deterministic I think.
Also, you could promote the value out on insert instead of using triggers.
Best regards
Michael
<paululvinius@.hotmail.com> wrote in message
news:1179224729.491872.79810@.w5g2000hsg.googlegrou ps.com...
On 14 Maj, 21:58, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> The easiest way to check is to use the namespace-uri() function on the
> top-level elements in your XML column. There is no such built-in.
> Best regards
> Michael
> <paululvin...@.hotmail.com> wrote in message
> news:1179156961.804277.146930@.y80g2000hsf.googlegr oups.com...
>
>
>
>
> - Visa citerad text -
Thanks!
I used a computed column like: "dbo.GetNamespaceUri(MyXmlColumn)". The
function uses xpath with the namespace-uri() function to determine the
namespace. I wasnt allowed to make the column persisted though because
its "non-deterministic".
This leaves us with a design choice:
If we use this solution, will it result in a noticable performance
impact? Maybe a insert/update trigger that fills the value is a more
performance friendly solution?
(We expect more queries than non-queries on this table)
|||On 20 Maj, 22:50, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> Did you define the function as schema bound? That should make it
> deterministic I think.
> Also, you could promote the value out on insert instead of using triggers.
> Best regards
> Michael
> <paululvin...@.hotmail.com> wrote in message
> news:1179224729.491872.79810@.w5g2000hsg.googlegrou ps.com...
> On 14 Maj, 21:58, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>
>
>
>
>
>
>
> Thanks!
> I used a computed column like: "dbo.GetNamespaceUri(MyXmlColumn)". The
> function uses xpath with the namespace-uri() function to determine the
> namespace. I wasnt allowed to make the column persisted though because
> its "non-deterministic".
> This leaves us with a design choice:
> If we use this solution, will it result in a noticable performance
> impact? Maybe a insert/update trigger that fills the value is a more
> performance friendly solution?
> (We expect more queries than non-queries on this table)- Dlj citerad text -
> - Visa citerad text -
Thanks for all your help!
I think we will decide to promote the value out on insert, seems like
the "keep it simple and safe" -way to do it

multityped column

Hi!
I have a typed xml column whith two schemas in the corresponding xml
schema collection (multityped column). Is there a simple way of
creating a new column, showing the name of the xml schema that each
row is typed to.
This is very useful to implement in cases of multiple versions of a
schema in a schema collection. Your application code could then more
easily apply schema specific logic.
I guess I could always create a computed column that uses a sql
function that figures it out, but since it seemes like a standard
problem, I first want to make sure there are no neat, built in, out of
the box solutions for this problem.
Thanks,
PaulThe easiest way to check is to use the namespace-uri() function on the
top-level elements in your XML column. There is no such built-in.
Best regards
Michael
<paululvinius@.hotmail.com> wrote in message
news:1179156961.804277.146930@.y80g2000hsf.googlegroups.com...
> Hi!
> I have a typed xml column whith two schemas in the corresponding xml
> schema collection (multityped column). Is there a simple way of
> creating a new column, showing the name of the xml schema that each
> row is typed to.
> This is very useful to implement in cases of multiple versions of a
> schema in a schema collection. Your application code could then more
> easily apply schema specific logic.
> I guess I could always create a computed column that uses a sql
> function that figures it out, but since it seemes like a standard
> problem, I first want to make sure there are no neat, built in, out of
> the box solutions for this problem.
> Thanks,
> Paul
>|||On 14 Maj, 21:58, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> The easiest way to check is to use the namespace-uri() function on the
> top-level elements in your XML column. There is no such built-in.
> Best regards
> Michael
> <paululvin...@.hotmail.com> wrote in message
> news:1179156961.804277.146930@.y80g2000hsf.googlegroups.com...
>
>
>
>
>
>
> - Visa citerad text -
Thanks!
I used a computed column like: "dbo.GetNamespaceUri(MyXmlColumn)". The
function uses xpath with the namespace-uri() function to determine the
namespace. I wasnt allowed to make the column persisted though because
its "non-deterministic".
This leaves us with a design choice:
If we use this solution, will it result in a noticable performance
impact? Maybe a insert/update trigger that fills the value is a more
performance friendly solution?
(We expect more queries than non-queries on this table)|||Did you define the function as schema bound? That should make it
deterministic I think.
Also, you could promote the value out on insert instead of using triggers.
Best regards
Michael
<paululvinius@.hotmail.com> wrote in message
news:1179224729.491872.79810@.w5g2000hsg.googlegroups.com...
On 14 Maj, 21:58, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> The easiest way to check is to use the namespace-uri() function on the
> top-level elements in your XML column. There is no such built-in.
> Best regards
> Michael
> <paululvin...@.hotmail.com> wrote in message
> news:1179156961.804277.146930@.y80g2000hsf.googlegroups.com...
>
>
>
>
>
>
> - Visa citerad text -
Thanks!
I used a computed column like: "dbo.GetNamespaceUri(MyXmlColumn)". The
function uses xpath with the namespace-uri() function to determine the
namespace. I wasnt allowed to make the column persisted though because
its "non-deterministic".
This leaves us with a design choice:
If we use this solution, will it result in a noticable performance
impact? Maybe a insert/update trigger that fills the value is a more
performance friendly solution?
(We expect more queries than non-queries on this table)|||On 20 Maj, 22:50, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> Did you define the function as schema bound? That should make it
> deterministic I think.
> Also, you could promote the value out on insert instead of using triggers.
> Best regards
> Michael
> <paululvin...@.hotmail.com> wrote in message
> news:1179224729.491872.79810@.w5g2000hsg.googlegroups.com...
> On 14 Maj, 21:58, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
> Thanks!
> I used a computed column like: "dbo.GetNamespaceUri(MyXmlColumn)". The
> function uses xpath with the namespace-uri() function to determine the
> namespace. I wasnt allowed to make the column persisted though because
> its "non-deterministic".
> This leaves us with a design choice:
> If we use this solution, will it result in a noticable performance
> impact? Maybe a insert/update trigger that fills the value is a more
> performance friendly solution?
> (We expect more queries than non-queries on this table)- D=F6lj citerad t=
ext -
> - Visa citerad text -
Thanks for all your help!
I think we will decide to promote the value out on insert, seems like
the "keep it simple and safe" -way to do it :)

multiple tables

Hi

I am trying to import an excel spreadsheet into a sql server database. The problem is I need the information in the spreadsheet to be imported into 3 different tables. I thought of transfering all details into a temporary table and then setting 3 triggers for each transfer of information. But this seems very inefficient. I know there should be an easier way to do this in dts.

Any help would be greatly appreciated

ThanksCreate a DTS package with Excel spreadSheet as the Source and the SQL Server as the Destination.
Create 3 Destination SQL Server and Define a connection between the one Excel Source and the three SQL Server destination table. It should just work perfect for you.|||Hi

Thanks for your reply...is that a connection using the transform data task??

Thanks|||Yes you have to set the Transform data task.|||Hi

Is this just using a for loop in the activeX script and incrementing each time...I have been trying to do this, but it keeps returning an error.

Thanks|||In the DTS tool bar there is a tool called Transofm task. Just select the source(excel) and Destination(sql server) and run it. You don't need to code any ActiveX script unless you plan to do some data messaging during the import/|||Also refer to http://www.sqldts.com for code examples on ActiveX & DTS goodies.

Multiple SQL statements in a stored procedure

Hi!

I got 2 stored procedure, proc1 executes proc2,
proc2 does some updates and inserts on different tables ...

proc1:

ALTER PROCEDURE
AS
execute proc2

SELECT * FROM tblFoo
______________________
my problem is, that when executing proc1, I receive the message:

"THE SP executed successfully, but did not return records!"

But I need the resultset from "SELECT * FROM tblFoo" that is executed
at the end of proc1.
I'm not sure, but I think that I solved a similira problem with "set
nocount on", I put it into both SP, but it's still the same ... no
resultset ...

How can I display "SELECT * FROM tblFoo" within a SP, where SQL
statements are executed before?!

Thank you!Err, stupid question but did you check that tblFoo actually has records in
it?

"Peter Neumaier" <Peter.Neumaier@.gmail.com> wrote in message
news:1117053332.823938.254990@.g14g2000cwa.googlegr oups.com...
> Hi!
> I got 2 stored procedure, proc1 executes proc2,
> proc2 does some updates and inserts on different tables ...
>
> proc1:
> ALTER PROCEDURE
> AS
> execute proc2
> SELECT * FROM tblFoo
> ______________________
> my problem is, that when executing proc1, I receive the message:
> "THE SP executed successfully, but did not return records!"
> But I need the resultset from "SELECT * FROM tblFoo" that is executed
> at the end of proc1.
> I'm not sure, but I think that I solved a similira problem with "set
> nocount on", I put it into both SP, but it's still the same ... no
> resultset ...
> How can I display "SELECT * FROM tblFoo" within a SP, where SQL
> statements are executed before?!
> Thank you!

Multiple Sources, One Dimension

Hi
I have several DB's, and only one datawarehouse. I need to make sure that I'm not replicating dimensions. And then i need to map the references in the sources, to the references from the fact tables to the dimension table.
I use SQLServer2K
ThanksNot sure on your question, I got two possible answers.

1) if you are trying to use multiple facts or dim tables from multiple data sources you can create a cube based on a standard SQL view.

2) You typically use a surrogate key and a natural key for each dimension, the fact table should be able to reference your dim table by its natural key.

If neither addresses what you are asking, let me know.

HTH|||Table Worker on source1
ID name
---- ----------------
1 John
2 Paul
3 George

Table Worker on source2
ID name
---- ----------------
1 Paul
2 John
3 Micheal

what sort of primary key should I use in de Dimension table?

And having a table with sales referencing the worker by it's ID, how can I get the dimension table primary key?

Thanks|||You typically use a natural and surrogate key, surrogate key would be arbitrary identity field while your natural key is what your OLTP system uses to reference the row. DO you mean how to get the natural key in your fact table? This you do by just specifying to pull the primary key of the transaction for all your dims in your ETL process (process used to pull your data from the OLTP store)

HTH|||I'm making a tool to do the ETL process... and my problem is that the same member of one dimension may by present in multiple OLPT's, naturally with different natural keys.

What key sould I use in the DW as natural key?

When loading the fact table, what sould how can I get the surrogate key?

Thanks|||a Surrogate key could be just a sequential identity column, some people don't use them but they can be helpful in case there are changes in your natural key among other things.

I see what you are saying in multiple natural keys, would like an example of what you are seeing but I'll try to take a stab at what you mean.

Let's say I have 4 applications each with customer addresses. Each of thsoe applications would typically have their own data mart consisting of subject oriented cubes. You could either have multiple dimensions for each customer address (one for each application) or you could store within your dimension table all natural keys for that row, Something like this:

Dim_cust_address
Columns - sur_key (only one if you are storing all natural keys) - nat_key1 - nat_key2 - nat_key3 - nat_key4 - any other dimension properties (street, house_nbr, prefix,suffix,etc)

Now for your subject oriented data marts you would only need to store one natural key per fact table. (nat_key1, nat_key2, etc.)

Then when building your cubes you can either use a view to link the dims to the different facts or make shared dimensions and access that that way. I'll admit I haven't done something like this but it should work OK.

HTH

BTW: The best book for these type of questions and much more is Ralph Kimball's Data Warehouse Lifecycle toolkit.

multiple sessions when using ADO

Hi
I have an application that uses an ADOConnection named CONNECTION1 and an AD
ODataset.The dataset fetches a record for editing it.This dataset has no Con
nectionString and its connection is the CONNECTION1.After opening the datase
t and editing record (befor
posting it) I execute a Stord Procedure to update another record in another
table with CONNECTION1.EXECUTE.
After opening the dataset I see a row in Management -> Current Activity -> P
rocess Info in EnterPrise Manager.And After CONNECTION.EXECUTE statement I s
ee 2 rows in Enterprise Manager.
I do all data modification by one ADOConnection in Delphi5 but SQL SERVER 20
00 do these modification by separate SPIDs (sessions).
How can I prevent this?
Dose ADOConnection or ADODataset have any property to prevent that? Or shoul
d I do anything in SQL SERVER?
ThanksYou may not be able to prevent it. ADO and ODBC, for that matter, will
spawn connections as they see fit. In most cases it does this because the
first SPID is in the middle of some process that has not completed, so the
second SPID is necesary to perform any other task. Normally this does not
cause any problems.
Rand
This posting is provided "as is" with no warranties and confers no rights.

multiple sessions when using ADO

Hi
I have an application that uses an ADOConnection named CONNECTION1 and an ADODataset.The dataset fetches a record for editing it.This dataset has no ConnectionString and its connection is the CONNECTION1.After opening the dataset and editing record (befor
posting it) I execute a Stord Procedure to update another record in another table with CONNECTION1.EXECUTE.
After opening the dataset I see a row in Management -> Current Activity -> Process Info in EnterPrise Manager.And After CONNECTION.EXECUTE statement I see 2 rows in Enterprise Manager.
I do all data modification by one ADOConnection in Delphi5 but SQL SERVER 2000 do these modification by separate SPIDs (sessions).
How can I prevent this?
Dose ADOConnection or ADODataset have any property to prevent that? Or should I do anything in SQL SERVER?
Thanks
You may not be able to prevent it. ADO and ODBC, for that matter, will
spawn connections as they see fit. In most cases it does this because the
first SPID is in the middle of some process that has not completed, so the
second SPID is necesary to perform any other task. Normally this does not
cause any problems.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Multiple ResultSets

Hi
I have a stored procedure which returns mulitple resultsets
rs = call.executeQuery();
call.getMoreResults();
irs = call.getResultSet();
I want to pass these two resultsets to another method.
When i pass these two
i get error saying "ResultSet is closed" for the first resultset.
i came to know there is method in jdk1.4.2 which keeps the resultsets open.
"call.getMoreResults(Statement.Keep_Current_Result )"
But i tried to use that But again i have another problem
Im using WSAD5.1 which uses to jdk1.4.1
So i configured the setting in WSAD so that it uses jdk1.4.2.
So at compile time im not gettign any problem
But at runtime it says
"getMoreResults(int) is not supported by webshpere java.sql.Statement
Implementation"
Can you please help me regarding this..
Thanks in Advance
jaya nair wrote:

> Hi
> I have a stored procedure which returns mulitple resultsets
> rs = call.executeQuery();
> call.getMoreResults();
> irs = call.getResultSet();
> I want to pass these two resultsets to another method.
> When i pass these two
> i get error saying "ResultSet is closed" for the first resultset.
> i came to know there is method in jdk1.4.2 which keeps the resultsets open.
> "call.getMoreResults(Statement.Keep_Current_Result )"
> But i tried to use that But again i have another problem
> Im using WSAD5.1 which uses to jdk1.4.1
> So i configured the setting in WSAD so that it uses jdk1.4.2.
> So at compile time im not gettign any problem
> But at runtime it says
> "getMoreResults(int) is not supported by webshpere java.sql.Statement
> Implementation"
> Can you please help me regarding this..
> Thanks in Advance
Hi. By JDBC spec, getMoreResults() will close any previous
result set. You have to do whatever processing you want
with a given resultset before getting another from the same
statement.
Joe Weinstein at BEA Systems
|||Hi Joe,
I have a problem again while retriveing multiple results sets from SQLServer
stored procedure
I used call.execute(), then tried to retrive OUT parameters using
call.getString(1) , it works fine
later im trying to retrive multiple resultsets one after other.
As you said first i tried to retrieve first resultset using
rs1 = call.getResultSet();
and tried to retrieve second one
call.getMoreResults()
rs2 = call.getResultSet()
But im getting rs1 and rs1 are null. Im not getting resultsets
But when i execute this stored proc using Query Analyser. It returns both
resultsets and i can see the records.
But using jdbc im not able to retrieve.
Can you tell me why?
thanks in advance
sudha
"Joe Weinstein" wrote:

>
> jaya nair wrote:
>
> Hi. By JDBC spec, getMoreResults() will close any previous
> result set. You have to do whatever processing you want
> with a given resultset before getting another from the same
> statement.
> Joe Weinstein at BEA Systems
>