We're putting together our data warehouse, and I had a questions regarding
design of fact tables for our situation. We have invoices and payments to
those invoices...would I include all information in one fact table, or would
I separate them into two tables? If I do two tables, can I include two fact
tables in an OLAP cube?
Thanks in advance.
You can use a view...
it's even better to use a partitioned view...
Message posted via http://www.sqlmonster.com
|||Hi,
It’s really depending how often you will be analyzing invoices and payments
together.
Sometimes - you can built two cubes and join them into virtual cube.
Very often - you should include all information in your fact table design.
Tomasz B.
"T." wrote:
> We're putting together our data warehouse, and I had a questions regarding
> design of fact tables for our situation. We have invoices and payments to
> those invoices...would I include all information in one fact table, or would
> I separate them into two tables? If I do two tables, can I include two fact
> tables in an OLAP cube?
> Thanks in advance.
>
>
Showing posts with label situation. Show all posts
Showing posts with label situation. Show all posts
Wednesday, March 7, 2012
Fact Table Design Question
We're putting together our data warehouse, and I had a questions regarding
design of fact tables for our situation. We have invoices and payments to
those invoices...would I include all information in one fact table, or would
I separate them into two tables? If I do two tables, can I include two fact
tables in an OLAP cube?
Thanks in advance.You can use a view...
it's even better to use a partitioned view...
Message posted via http://www.droptable.com|||Hi,
It’s really depending how often you will be analyzing invoices and payment
s
together.
Sometimes - you can built two cubes and join them into virtual cube.
Very often - you should include all information in your fact table design.
Tomasz B.
"T." wrote:
> We're putting together our data warehouse, and I had a questions regarding
> design of fact tables for our situation. We have invoices and payments to
> those invoices...would I include all information in one fact table, or wou
ld
> I separate them into two tables? If I do two tables, can I include two fa
ct
> tables in an OLAP cube?
> Thanks in advance.
>
>
design of fact tables for our situation. We have invoices and payments to
those invoices...would I include all information in one fact table, or would
I separate them into two tables? If I do two tables, can I include two fact
tables in an OLAP cube?
Thanks in advance.You can use a view...
it's even better to use a partitioned view...
Message posted via http://www.droptable.com|||Hi,
It’s really depending how often you will be analyzing invoices and payment
s
together.
Sometimes - you can built two cubes and join them into virtual cube.
Very often - you should include all information in your fact table design.
Tomasz B.
"T." wrote:
> We're putting together our data warehouse, and I had a questions regarding
> design of fact tables for our situation. We have invoices and payments to
> those invoices...would I include all information in one fact table, or wou
ld
> I separate them into two tables? If I do two tables, can I include two fa
ct
> tables in an OLAP cube?
> Thanks in advance.
>
>
Fact Table Design - Help
Hi There,
I have to build a Fact table, and I have this situation.
My OLTP database, keeps always the last image of the
record.
Table
Incident(IncidentId, CreationTimme, StatusId, PriorityId,
rankId, Description, ..)
My Log Database, keeps a table per field and tracks all
the changes.
Incident_CreationTime(IncidentId, CreationTime, Timestamp)
Incident_StatusId(IncidentId, StatusId, Timestamp)
Incident_PriorityId(IncidentId, PriorityId, Timestamp)
Incident_RankId(IncidentId, RankId, Timestamp)
You got the Idea.
I have to be based on the Log database to create my Fact
Table of Incident. So what's the best way to create the
fact table in this case ? What about the dimension too ?
ThanksWhat are you trying to measure and how are you measuring it "by" ?
These are the inputs to your design, not the existing schema!
"Elie Khammar" <ekhammar@.positron.qc.ca> wrote in message
news:0cd301c3bb40$ec0fb7e0$a401280a@.phx.gbl...
Statusid, PriorityId, per RankId.
it "by" ?
schema!
look something like:
DateID
StatusID
PriorityID
RankID
IncidentCount
And you would have four dimension tables, Date, Status, Priority, and Rank
each having the descriptive elements of those dimensions, like Date,
StatusDescription, PriorityNumber, RankNumber, or whatever is meaninful for
you for those dimensions.
"Elie Khammar" <ekhammar@.positron.qc.ca> wrote in message
news:067401c3bb4f$b6e6dee0$a001280a@.phx.gbl...[QUOTE]
> I'm trying to measure the number of incidents per
> Statusid, PriorityId, per RankId.
>
> it "by" ?
> schema!
> PriorityId,
> Timestamp)|||Hi Kevin,
Thanks for answering me back. I still have another
question for you.
It's not my first time that I develop a datawarehouse. My
only worry here, is the fact that these tables are related
together as many-to-many relationship in the OLTP system.
So If I do it like you suggested,
(IncidnetNumber, DateId, StatusId, PriorityId, RankId,
IncidentCount)
I could end up for each Incident something like this in my
FACT table:
1, 20031208, 1, 0, 0, 1
1, 20031208, 0, 1, 1, 1
1, 20031208, 4, 0, 0, 1
1, 20031208, 5, 0, 0, 1
Because the StatusId, PriorityId, RankId there is a
possibility that they change so many times during the life
cycle of an incident.
So what I thought to do, is to create a fact table for
each Fact_IncidentStatus, Fact_IncidentPriority,
Fact_IncidentRank each of these fact tables is related to
its own dimension. and then all those fact tables are
related to FACT_Incident which contains only unique
Incident Numbers.
Do you think this is a good design?
Thanks for your time
fact table would
Priority, and Rank
like Date,
whatever is meaninful for
It seems to me that you need to have a separate dimension
for the incident numbers.
You need:
DIM_Date
DIM_Status
DIM_Priority
DIM_Rank
DIM_Incident
And:
FACT_IncidentCount
With a surrogate key from each of your dimensions and a
count field that would contain just the number "1". As
the cube aggregated on each dimension, it would add the
count field up.
Hope this helps,
Asa Monsey
related
my
life
I have to build a Fact table, and I have this situation.
My OLTP database, keeps always the last image of the
record.
Table
Incident(IncidentId, CreationTimme, StatusId, PriorityId,
rankId, Description, ..)
My Log Database, keeps a table per field and tracks all
the changes.
Incident_CreationTime(IncidentId, CreationTime, Timestamp)
Incident_StatusId(IncidentId, StatusId, Timestamp)
Incident_PriorityId(IncidentId, PriorityId, Timestamp)
Incident_RankId(IncidentId, RankId, Timestamp)
You got the Idea.
I have to be based on the Log database to create my Fact
Table of Incident. So what's the best way to create the
fact table in this case ? What about the dimension too ?
ThanksWhat are you trying to measure and how are you measuring it "by" ?
These are the inputs to your design, not the existing schema!
"Elie Khammar" <ekhammar@.positron.qc.ca> wrote in message
news:0cd301c3bb40$ec0fb7e0$a401280a@.phx.gbl...
quote:|||I'm trying to measure the number of incidents per
> Hi There,
> I have to build a Fact table, and I have this situation.
> My OLTP database, keeps always the last image of the
> record.
> Table
> Incident(IncidentId, CreationTimme, StatusId, PriorityId,
> rankId, Description, ..)
> My Log Database, keeps a table per field and tracks all
> the changes.
> Incident_CreationTime(IncidentId, CreationTime, Timestamp)
> Incident_StatusId(IncidentId, StatusId, Timestamp)
> Incident_PriorityId(IncidentId, PriorityId, Timestamp)
> Incident_RankId(IncidentId, RankId, Timestamp)
> You got the Idea.
> I have to be based on the Log database to create my Fact
> Table of Incident. So what's the best way to create the
> fact table in this case ? What about the dimension too ?
> Thanks
>
Statusid, PriorityId, per RankId.
quote:
>--Original Message--
>What are you trying to measure and how are you measuring
it "by" ?
quote:
>These are the inputs to your design, not the existing
schema!
quote:|||You probably forgot about the date dimension. Then your fact table would
>
>"Elie Khammar" <ekhammar@.positron.qc.ca> wrote in message
>news:0cd301c3bb40$ec0fb7e0$a401280a@.phx.gbl...
PriorityId,[QUOTE]
Timestamp)[QUOTE]
>
>.
>
look something like:
DateID
StatusID
PriorityID
RankID
IncidentCount
And you would have four dimension tables, Date, Status, Priority, and Rank
each having the descriptive elements of those dimensions, like Date,
StatusDescription, PriorityNumber, RankNumber, or whatever is meaninful for
you for those dimensions.
"Elie Khammar" <ekhammar@.positron.qc.ca> wrote in message
news:067401c3bb4f$b6e6dee0$a001280a@.phx.gbl...[QUOTE]
> I'm trying to measure the number of incidents per
> Statusid, PriorityId, per RankId.
>
> it "by" ?
> schema!
> PriorityId,
> Timestamp)|||Hi Kevin,
Thanks for answering me back. I still have another
question for you.
It's not my first time that I develop a datawarehouse. My
only worry here, is the fact that these tables are related
together as many-to-many relationship in the OLTP system.
So If I do it like you suggested,
(IncidnetNumber, DateId, StatusId, PriorityId, RankId,
IncidentCount)
I could end up for each Incident something like this in my
FACT table:
1, 20031208, 1, 0, 0, 1
1, 20031208, 0, 1, 1, 1
1, 20031208, 4, 0, 0, 1
1, 20031208, 5, 0, 0, 1
Because the StatusId, PriorityId, RankId there is a
possibility that they change so many times during the life
cycle of an incident.
So what I thought to do, is to create a fact table for
each Fact_IncidentStatus, Fact_IncidentPriority,
Fact_IncidentRank each of these fact tables is related to
its own dimension. and then all those fact tables are
related to FACT_Incident which contains only unique
Incident Numbers.
Do you think this is a good design?
Thanks for your time
quote:
>--Original Message--
>You probably forgot about the date dimension. Then your
fact table would
quote:
>look something like:
>DateID
>StatusID
>PriorityID
>RankID
>IncidentCount
>And you would have four dimension tables, Date, Status,
Priority, and Rank
quote:
>each having the descriptive elements of those dimensions,
like Date,
quote:
>StatusDescription, PriorityNumber, RankNumber, or
whatever is meaninful for
quote:|||Elie,
>you for those dimensions.
>
>"Elie Khammar" <ekhammar@.positron.qc.ca> wrote in message
>news:067401c3bb4f$b6e6dee0$a001280a@.phx.gbl...
measuring[QUOTE]
message[QUOTE]
situation.[QUOTE]
all[QUOTE]
Timestamp)[QUOTE]
Fact[QUOTE]
the[QUOTE]
too ?[QUOTE]
>
>.
>
It seems to me that you need to have a separate dimension
for the incident numbers.
You need:
DIM_Date
DIM_Status
DIM_Priority
DIM_Rank
DIM_Incident
And:
FACT_IncidentCount
With a surrogate key from each of your dimensions and a
count field that would contain just the number "1". As
the cube aggregated on each dimension, it would add the
count field up.
Hope this helps,
Asa Monsey
quote:
>--Original Message--
>Hi Kevin,
>Thanks for answering me back. I still have another
>question for you.
>It's not my first time that I develop a datawarehouse. My
>only worry here, is the fact that these tables are
related
quote:
>together as many-to-many relationship in the OLTP system.
>So If I do it like you suggested,
>(IncidnetNumber, DateId, StatusId, PriorityId, RankId,
>IncidentCount)
>I could end up for each Incident something like this in
my
quote:
>FACT table:
>1, 20031208, 1, 0, 0, 1
>1, 20031208, 0, 1, 1, 1
>1, 20031208, 4, 0, 0, 1
>1, 20031208, 5, 0, 0, 1
>Because the StatusId, PriorityId, RankId there is a
>possibility that they change so many times during the
life
quote:
>cycle of an incident.
>So what I thought to do, is to create a fact table for
>each Fact_IncidentStatus, Fact_IncidentPriority,
>Fact_IncidentRank each of these fact tables is related to
>its own dimension. and then all those fact tables are
>related to FACT_Incident which contains only unique
>Incident Numbers.
>Do you think this is a good design?
>Thanks for your time
>
>
>fact table would
>Priority, and Rank
dimensions,[QUOTE]
>like Date,
>whatever is meaninful for
>measuring
>message
>situation.
>all
>Timestamp)
>Fact
>the
>too ?
>.
>
Sunday, February 19, 2012
Extracting SQL fields from XML Data stored in NText field
I have a interesting situation, where XML is stored inside of SQL NText
field.
Unfortunately, I need to extract that data as SQL from the XML.
Here is the data in the BillingAddress field:
<?xml version="1.0" encoding="utf-16"?>
<Address>
<ID>-1</ID>
<FirstName>Joe</FirstName>
<LastName>McLean</LastName>
<StreeLine1>360 E Columbia 48</StreeLine1>
<City>Emerson</City>
<StateName>Arkansas</StateName>
<StateCode>4</StateCode>
<PostalCode>71740</PostalCode>
<CountryName>United States</CountryName>
<CountryCode>en-US</CountryCode>
<PhoneNumber>870-547-2251</PhoneNumber>
</Address>
I need to be able to have a SQL query that returns all of those nodes
(FirstName, LastName, etc) as individual SQL fields.
Can this be done?
Thanks in advance!
Robert Johnson
Internet Web ZoneHave you looked in to using OPENXML? SQL Server Books Online has tons of
information and examples on this. Also, check out www.sqlxml.org
Anith|||I spent hours looking. That is why I posted the question. If you don't have
an answer, please don't respond.
Robert
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23DNdh8yAFHA.3376@.TK2MSFTNGP12.phx.gbl...
> Have you looked in to using OPENXML? SQL Server Books Online has tons of
> information and examples on this. Also, check out www.sqlxml.org
> --
> Anith
>|||Here is an example:
CREATE TABLE t (
keycol INT NOT NULL PRIMARY KEY,
xmlval NTEXT )
INSERT t SELECT 1,
'<Address>
<ID>-1</ID>
<FirstName>Joe</FirstName>
<LastName>McLean</LastName>
<StreeLine1>360 E Columbia 48</StreeLine1>
<City>Emerson</City>
<StateName>Arkansas</StateName>
<StateCode>4</StateCode>
<PostalCode>71740</PostalCode>
<CountryName>United States</CountryName>
<CountryCode>en-US</CountryCode>
<PhoneNumber>870-547-2251</PhoneNumber>
</Address>'
DECLARE @.xml VARCHAR(8000), @.idoc INT
SET @.xml = ( SELECT CAST( xmlval AS VARCHAR(8000 ) )
FROM t WHERE keycol = 1 )
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xml
SELECT *
FROM OPENXML ( @.idoc, '/Address', 2 )
WITH ( FirstName VARCHAR(20),
LastName VARCHAR(20) )
EXEC sp_xml_removedocument @.idoc
Anith|||There is no need to be rude. Where does it say in your original post that
you " ... spent hours looking ..."? And specifically, where does it say that
you spent hours looking at the topics in BOL about OpenXML? Or browsing
through the excellent resource at sqlxml.org?
All we know is what we see in your post. Don't expect us to be able to
somehow be aware of all the research that you've already done before posting
your question.
Ah, I see that Anith was gracious enough to overlook your rudeness and post
an example. Good for Anith.
Bob Barrows
Robert Johnson wrote:
> I spent hours looking. That is why I posted the question. If you
> don't have an answer, please don't respond.
> Robert
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23DNdh8yAFHA.3376@.TK2MSFTNGP12.phx.gbl...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
field.
Unfortunately, I need to extract that data as SQL from the XML.
Here is the data in the BillingAddress field:
<?xml version="1.0" encoding="utf-16"?>
<Address>
<ID>-1</ID>
<FirstName>Joe</FirstName>
<LastName>McLean</LastName>
<StreeLine1>360 E Columbia 48</StreeLine1>
<City>Emerson</City>
<StateName>Arkansas</StateName>
<StateCode>4</StateCode>
<PostalCode>71740</PostalCode>
<CountryName>United States</CountryName>
<CountryCode>en-US</CountryCode>
<PhoneNumber>870-547-2251</PhoneNumber>
</Address>
I need to be able to have a SQL query that returns all of those nodes
(FirstName, LastName, etc) as individual SQL fields.
Can this be done?
Thanks in advance!
Robert Johnson
Internet Web ZoneHave you looked in to using OPENXML? SQL Server Books Online has tons of
information and examples on this. Also, check out www.sqlxml.org
Anith|||I spent hours looking. That is why I posted the question. If you don't have
an answer, please don't respond.
Robert
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23DNdh8yAFHA.3376@.TK2MSFTNGP12.phx.gbl...
> Have you looked in to using OPENXML? SQL Server Books Online has tons of
> information and examples on this. Also, check out www.sqlxml.org
> --
> Anith
>|||Here is an example:
CREATE TABLE t (
keycol INT NOT NULL PRIMARY KEY,
xmlval NTEXT )
INSERT t SELECT 1,
'<Address>
<ID>-1</ID>
<FirstName>Joe</FirstName>
<LastName>McLean</LastName>
<StreeLine1>360 E Columbia 48</StreeLine1>
<City>Emerson</City>
<StateName>Arkansas</StateName>
<StateCode>4</StateCode>
<PostalCode>71740</PostalCode>
<CountryName>United States</CountryName>
<CountryCode>en-US</CountryCode>
<PhoneNumber>870-547-2251</PhoneNumber>
</Address>'
DECLARE @.xml VARCHAR(8000), @.idoc INT
SET @.xml = ( SELECT CAST( xmlval AS VARCHAR(8000 ) )
FROM t WHERE keycol = 1 )
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xml
SELECT *
FROM OPENXML ( @.idoc, '/Address', 2 )
WITH ( FirstName VARCHAR(20),
LastName VARCHAR(20) )
EXEC sp_xml_removedocument @.idoc
Anith|||There is no need to be rude. Where does it say in your original post that
you " ... spent hours looking ..."? And specifically, where does it say that
you spent hours looking at the topics in BOL about OpenXML? Or browsing
through the excellent resource at sqlxml.org?
All we know is what we see in your post. Don't expect us to be able to
somehow be aware of all the research that you've already done before posting
your question.
Ah, I see that Anith was gracious enough to overlook your rudeness and post
an example. Good for Anith.
Bob Barrows
Robert Johnson wrote:
> I spent hours looking. That is why I posted the question. If you
> don't have an answer, please don't respond.
> Robert
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23DNdh8yAFHA.3376@.TK2MSFTNGP12.phx.gbl...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Friday, February 17, 2012
Extracting data from SQL Server to XML documents
Hey guys, here's the situation.
I need to extract data from SQL Server to multiple XML files, on a daily
basis.
What is the best strategy to accomplish this task?
I did try two things already. These method were using stored procedures.
The first one was the use of the System Stored Procedure sp_makewebtask
using template files. This task failed due to a dll error.
My second attempt was using the "bcp" utility. It didn't work because of the
version of SQL Server we are using right now (too old).
Is there any other ways to generate XML from SQL Server? Can we use DTS to
accomplished this task?
Thanks for the help!
If you're using SQL Server 2000, you can use a variety of techniques -
including FOR XML queries, annotated schemas, etc. See SQL Server Books
Online, and install SQLXML 3.0
(http://www.microsoft.com/downloads/d...isplayLang=en.).
For earlier versions, you'd have to write a custom solution - there's no
built-in support for XML.
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
news:%23W6QGyiVFHA.3280@.TK2MSFTNGP09.phx.gbl...
Hey guys, here's the situation.
I need to extract data from SQL Server to multiple XML files, on a daily
basis.
What is the best strategy to accomplish this task?
I did try two things already. These method were using stored procedures.
The first one was the use of the System Stored Procedure sp_makewebtask
using template files. This task failed due to a dll error.
My second attempt was using the "bcp" utility. It didn't work because of the
version of SQL Server we are using right now (too old).
Is there any other ways to generate XML from SQL Server? Can we use DTS to
accomplished this task?
Thanks for the help!
|||thx for the info!
"Graeme Malcolm" <graemem_cm@.hotmail.com> a crit dans le message de
news:u4T2%23UjVFHA.1796@.TK2MSFTNGP15.phx.gbl...
> If you're using SQL Server 2000, you can use a variety of techniques -
> including FOR XML queries, annotated schemas, etc. See SQL Server Books
> Online, and install SQLXML 3.0
>
(http://www.microsoft.com/downloads/d...a154-8e23-47d2
-a033-764259cfb53b&DisplayLang=en.).
> For earlier versions, you'd have to write a custom solution - there's no
> built-in support for XML.
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
> news:%23W6QGyiVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys, here's the situation.
> I need to extract data from SQL Server to multiple XML files, on a daily
> basis.
> What is the best strategy to accomplish this task?
> I did try two things already. These method were using stored procedures.
> The first one was the use of the System Stored Procedure sp_makewebtask
> using template files. This task failed due to a dll error.
> My second attempt was using the "bcp" utility. It didn't work because of
the
> version of SQL Server we are using right now (too old).
> Is there any other ways to generate XML from SQL Server? Can we use DTS to
> accomplished this task?
> Thanks for the help!
>
>
I need to extract data from SQL Server to multiple XML files, on a daily
basis.
What is the best strategy to accomplish this task?
I did try two things already. These method were using stored procedures.
The first one was the use of the System Stored Procedure sp_makewebtask
using template files. This task failed due to a dll error.
My second attempt was using the "bcp" utility. It didn't work because of the
version of SQL Server we are using right now (too old).
Is there any other ways to generate XML from SQL Server? Can we use DTS to
accomplished this task?
Thanks for the help!
If you're using SQL Server 2000, you can use a variety of techniques -
including FOR XML queries, annotated schemas, etc. See SQL Server Books
Online, and install SQLXML 3.0
(http://www.microsoft.com/downloads/d...isplayLang=en.).
For earlier versions, you'd have to write a custom solution - there's no
built-in support for XML.
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
news:%23W6QGyiVFHA.3280@.TK2MSFTNGP09.phx.gbl...
Hey guys, here's the situation.
I need to extract data from SQL Server to multiple XML files, on a daily
basis.
What is the best strategy to accomplish this task?
I did try two things already. These method were using stored procedures.
The first one was the use of the System Stored Procedure sp_makewebtask
using template files. This task failed due to a dll error.
My second attempt was using the "bcp" utility. It didn't work because of the
version of SQL Server we are using right now (too old).
Is there any other ways to generate XML from SQL Server? Can we use DTS to
accomplished this task?
Thanks for the help!
|||thx for the info!
"Graeme Malcolm" <graemem_cm@.hotmail.com> a crit dans le message de
news:u4T2%23UjVFHA.1796@.TK2MSFTNGP15.phx.gbl...
> If you're using SQL Server 2000, you can use a variety of techniques -
> including FOR XML queries, annotated schemas, etc. See SQL Server Books
> Online, and install SQLXML 3.0
>
(http://www.microsoft.com/downloads/d...a154-8e23-47d2
-a033-764259cfb53b&DisplayLang=en.).
> For earlier versions, you'd have to write a custom solution - there's no
> built-in support for XML.
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
> news:%23W6QGyiVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys, here's the situation.
> I need to extract data from SQL Server to multiple XML files, on a daily
> basis.
> What is the best strategy to accomplish this task?
> I did try two things already. These method were using stored procedures.
> The first one was the use of the System Stored Procedure sp_makewebtask
> using template files. This task failed due to a dll error.
> My second attempt was using the "bcp" utility. It didn't work because of
the
> version of SQL Server we are using right now (too old).
> Is there any other ways to generate XML from SQL Server? Can we use DTS to
> accomplished this task?
> Thanks for the help!
>
>
Extracting data from SQL Server to XML documents
Hey guys, here's the situation.
I need to extract data from SQL Server to multiple XML files, on a daily
basis.
What is the best strategy to accomplish this task?
I did try two things already. These method were using stored procedures.
The first one was the use of the System Stored Procedure sp_makewebtask
using template files. This task failed due to a dll error.
My second attempt was using the "bcp" utility. It didn't work because of the
version of SQL Server we are using right now (too old).
Is there any other ways to generate XML from SQL Server? Can we use DTS to
accomplished this task?
Thanks for the help!If you're using SQL Server 2000, you can use a variety of techniques -
including FOR XML queries, annotated schemas, etc. See SQL Server Books
Online, and install SQLXML 3.0
(http://www.microsoft.com/downloads/...DisplayLang=en.).
For earlier versions, you'd have to write a custom solution - there's no
built-in support for XML.
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
news:%23W6QGyiVFHA.3280@.TK2MSFTNGP09.phx.gbl...
Hey guys, here's the situation.
I need to extract data from SQL Server to multiple XML files, on a daily
basis.
What is the best strategy to accomplish this task?
I did try two things already. These method were using stored procedures.
The first one was the use of the System Stored Procedure sp_makewebtask
using template files. This task failed due to a dll error.
My second attempt was using the "bcp" utility. It didn't work because of the
version of SQL Server we are using right now (too old).
Is there any other ways to generate XML from SQL Server? Can we use DTS to
accomplished this task?
Thanks for the help!|||thx for the info!
"Graeme Malcolm" <graemem_cm@.hotmail.com> a crit dans le message de
news:u4T2%23UjVFHA.1796@.TK2MSFTNGP15.phx.gbl...
> If you're using SQL Server 2000, you can use a variety of techniques -
> including FOR XML queries, annotated schemas, etc. See SQL Server Books
> Online, and install SQLXML 3.0
>
(http://www.microsoft.com/downloads/...4a154-8e23-47d2
-a033-764259cfb53b&DisplayLang=en.).
> For earlier versions, you'd have to write a custom solution - there's no
> built-in support for XML.
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
> news:%23W6QGyiVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys, here's the situation.
> I need to extract data from SQL Server to multiple XML files, on a daily
> basis.
> What is the best strategy to accomplish this task?
> I did try two things already. These method were using stored procedures.
> The first one was the use of the System Stored Procedure sp_makewebtask
> using template files. This task failed due to a dll error.
> My second attempt was using the "bcp" utility. It didn't work because of
the
> version of SQL Server we are using right now (too old).
> Is there any other ways to generate XML from SQL Server? Can we use DTS to
> accomplished this task?
> Thanks for the help!
>
>
I need to extract data from SQL Server to multiple XML files, on a daily
basis.
What is the best strategy to accomplish this task?
I did try two things already. These method were using stored procedures.
The first one was the use of the System Stored Procedure sp_makewebtask
using template files. This task failed due to a dll error.
My second attempt was using the "bcp" utility. It didn't work because of the
version of SQL Server we are using right now (too old).
Is there any other ways to generate XML from SQL Server? Can we use DTS to
accomplished this task?
Thanks for the help!If you're using SQL Server 2000, you can use a variety of techniques -
including FOR XML queries, annotated schemas, etc. See SQL Server Books
Online, and install SQLXML 3.0
(http://www.microsoft.com/downloads/...DisplayLang=en.).
For earlier versions, you'd have to write a custom solution - there's no
built-in support for XML.
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
news:%23W6QGyiVFHA.3280@.TK2MSFTNGP09.phx.gbl...
Hey guys, here's the situation.
I need to extract data from SQL Server to multiple XML files, on a daily
basis.
What is the best strategy to accomplish this task?
I did try two things already. These method were using stored procedures.
The first one was the use of the System Stored Procedure sp_makewebtask
using template files. This task failed due to a dll error.
My second attempt was using the "bcp" utility. It didn't work because of the
version of SQL Server we are using right now (too old).
Is there any other ways to generate XML from SQL Server? Can we use DTS to
accomplished this task?
Thanks for the help!|||thx for the info!
"Graeme Malcolm" <graemem_cm@.hotmail.com> a crit dans le message de
news:u4T2%23UjVFHA.1796@.TK2MSFTNGP15.phx.gbl...
> If you're using SQL Server 2000, you can use a variety of techniques -
> including FOR XML queries, annotated schemas, etc. See SQL Server Books
> Online, and install SQLXML 3.0
>
(http://www.microsoft.com/downloads/...4a154-8e23-47d2
-a033-764259cfb53b&DisplayLang=en.).
> For earlier versions, you'd have to write a custom solution - there's no
> built-in support for XML.
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
> news:%23W6QGyiVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys, here's the situation.
> I need to extract data from SQL Server to multiple XML files, on a daily
> basis.
> What is the best strategy to accomplish this task?
> I did try two things already. These method were using stored procedures.
> The first one was the use of the System Stored Procedure sp_makewebtask
> using template files. This task failed due to a dll error.
> My second attempt was using the "bcp" utility. It didn't work because of
the
> version of SQL Server we are using right now (too old).
> Is there any other ways to generate XML from SQL Server? Can we use DTS to
> accomplished this task?
> Thanks for the help!
>
>
Subscribe to:
Posts (Atom)