Mappings question in OLE DB Destination

Hi,

I have a situation where I want to map a column from a flat file to TWO columns in a table.

However, in the mappings tab, you can only select the "Input Column" once. Once a column has been used, it no longer appears in the drop down list.

I am wondering if there's a way to override this behavior, and if not, what is the best way to handle this type of situation?

I have added an EXECUTE SQL task to update the second column with the inserted column values, but I would like to know if the default mapping behavior can be changed, as it seems so limited.

Thanks

Add a derived column right before the destination and select the column that you want to use more than once and drag it to the expression box. Adjust the name of the new column accordingly.

Then in the OLE DB Destination you can select the column you just added.

Feel free to suggest new features over at http://connect.microsoft.com/sqlserver/feedback|||

Great, thanks

sql

Mapping two schema definitions to one column

Hi,
I have a xml data file that looks like this
<CT_DATASET>
<DATE-INTERVENTIONDATE>
<YEARS>0</YEARS>
<MONTHS>0</MONTHS>
<DAYS>-1</DAYS>
</DATE-INTERVENTIONDATE>
<DESCTEXT>Preoperative CT Diagnosis</DESCTEXT>
<NUMBER_OF_SLICES>58</NUMBER_OF_SLICES>
<DICOM_TAGS>
<TAGNR>00080014</TAGNR>
<TAGVALUE>1.2.840.113701.4.2.102</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080016</TAGNR>
<TAGVALUE>1.2.840.10008.5.1.4.1.1.4</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080018</TAGNR>
<TAGVALUE>1.2.840.113701.4.2.102.0.2187.8.1.0.7</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080070</TAGNR>
<TAGVALUE>Toshiba </TAGVALUE>
</DICOM_TAGS>
</CT_DATASET>
And I want to map the multiple tagnr and tagvalue to one column for the
patient.
My schema file looks liek this:
<xs:element name="CT_DATASET" sql:relation="CT_DATA"
sql:overflow-field="ADDITIONAL">
<xs:complexType>
<xs:sequence>
<xs:element ref="DATE-INTERVENTIONDATE" />
<xs:element ref="DESCTEXT" />
<xs:element ref="NUMBER_OF_SLICES" />
<xs:element minOccurs="0" maxOccurs="unbounded" ref="DICOM_TAGS" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="NUMBER_OF_SLICES" type="xs:string" />
<xs:element name="DICOM_TAGS" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="TAGNR"/>
<xs:element ref="TAGVALUE"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DATE-INTERVENTIONDATE" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="YEARS" />
<xs:element ref="MONTHS" />
<xs:element ref="DAYS" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DESCTEXT" type="xs:string"/>
<xs:element name="YEARS" type="xs:string" sql:field="years"/>
<xs:element name="MONTHS" type="xs:string" sql:field="months" />
<xs:element name="DAYS" type="xs:string" sql:field="days"/>
<xs:element name="TAGNR" sql:field="TAG_NO:"/>
<xs:element name="TAGVALUE" sql:field="TAG VALUE"/>
</xs:schema>
I get an error which says:
Data mapping to column 'TAG_NO' was already found in the data. Make
sure that no two schema definitions map to the same column.
Does anybody know a way around. Any help would be appreciated. Please
do help!!!Hello Prakruthi,
Are you trying to use this as a validation schema in an XML Schema collectio
n
or a XDR for mapping queries?

> I get an error which says:
> Data mapping to column 'TAG_NO' was already found in the data. Make
> sure that no two schema definitions map to the same column.
> Does anybody know a way around. Any help would be appreciated. Please
> do help!!!
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent,
Im trying to use this as a validation schema. And as you can see from
the xml data , each patient has multiple dicom tags and numbers. I want
to be able to map all these numbers into a single column. Is this
possible?|||Hi Kent,
I forgot to mention that Tag Numbers and Tag values are two separate
columns. But each column should be able to have multiple values.
Regards,
Prakruthi|||Hello Prakruthi,
Part of problem is that the schema you posted isn't a valid XSD schema:
a. The sql namespace (aliased by sql:) isn't defined by your schema.
b. The schema has some other problems, so I rewrote it -- sans the SQL parts
since I'm not sure what these are or do in your processor.
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="q
ualified">
<xs:element name="CT_DATASET">
<xs:complexType>
<xs:sequence>
<xs:element name="DATE-INTERVENTIONDATE">
<xs:complexType>
<xs:sequence>
<xs:element name="YEARS" type="xs:string"/>
<xs:element name="MONTHS" type="xs:string"/>
<xs:element name="DAYS" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DESCTEXT" type="xs:string"/>
<xs:element name="NUMBER_OF_SLICES" type="xs:string"/>
<xs:element ref="DICOM_TAGS" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DICOM_TAGS">
<xs:complexType>
<xs:sequence>
<xs:element ref="TAGNR"/>
<xs:element ref="TAGVALUE"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="TAGNR" type="xs:string"/>
<xs:element name="TAGVALUE" type="xs:string"/>
</xs:schema>
I was able to catalog is into a SQL Sever 2005 XML Schema Collection succesf
ully.
Does any of that help?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Dear Kent,
Thanks for reorganising my schema in the right manner. But I dont think
you understood what I meant to say. Even when I used your modified
schema I could not map the three tagnr and tagvalues associated with
one one single patient into the tagnr and tagvalue column.I get an
error that says
Data mapping to column 'TAG_NO' was already found in the data. Make
sure that no two schema definitions map to the same column.
Is it not possible to map three different values to one column of the
same row? I hope you can help me out again this time.
Regards,
Prakruthi|||Hello Prakruthi,
That's why I asked if you were using this an mapping schema or as a validati
on
schema. You said "validation," so I fixed it for that. However, I don't beli
eve
you can target more than one value into a column using a single mapping.
Sadly, I don't have the time to work on this today before going on the road
for the next couple of w. However, it looks like Michael is back online,
so he might be able to help more.:)

> Thanks for reorganising my schema in the right manner. But I dont
> think
> you understood what I meant to say. Even when I used your modified
> schema I could not map the three tagnr and tagvalues associated with
> one one single patient into the tagnr and tagvalue column.I get an
> error that says
> Data mapping to column 'TAG_NO' was already found in the data. Make
> sure that no two schema definitions map to the same column.
> Is it not possible to map three different values to one column of the
> same row? I hope you can help me out again this time.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent,
I wanted to say im not using it as a validation schema. I apologise for
the typo error. I hope micheal would help me out with this issue.
Regards,
Prakruthi|||Hi Kent,
I wanted to say im not using it as a validation schema. I apologise for
the typo error. I hope micheal would help me out with this issue.
Regards,
Prakruthi|||I have forwarded it to our mapping schema experts and I hope they will get
back to you.
Ping me if that is not the case.
Michael
"Prakruthi" <prakruthi.rao@.gmail.com> wrote in message
news:1143210046.980577.250430@.e56g2000cwe.googlegroups.com...
> Hi Kent,
> I wanted to say im not using it as a validation schema. I apologise for
> the typo error. I hope micheal would help me out with this issue.
> Regards,
> Prakruthi
>

Mapping two schema definitions to one column

Hi,
I have a xml data file that looks like this
<CT_DATASET>
<DATE-INTERVENTIONDATE>
<YEARS>0</YEARS>
<MONTHS>0</MONTHS>
<DAYS>-1</DAYS>
</DATE-INTERVENTIONDATE>
<DESCTEXT>Preoperative CT Diagnosis</DESCTEXT>
<NUMBER_OF_SLICES>58</NUMBER_OF_SLICES>
<DICOM_TAGS>
<TAGNR>00080014</TAGNR>
<TAGVALUE>1.2.840.113701.4.2.102</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080016</TAGNR>
<TAGVALUE>1.2.840.10008.5.1.4.1.1.4</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080018</TAGNR>
<TAGVALUE>1.2.840.113701.4.2.102.0.2187.8.1.0.7< /TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080070</TAGNR>
<TAGVALUE>Toshiba </TAGVALUE>
</DICOM_TAGS>
</CT_DATASET>
And I want to map the multiple tagnr and tagvalue to one column for the
patient.
My schema file looks liek this:
<xs:element name="CT_DATASET" sql:relation="CT_DATA"
sql:overflow-field="ADDITIONAL">
<xs:complexType>
<xs:sequence>
<xs:element ref="DATE-INTERVENTIONDATE" />
<xs:element ref="DESCTEXT" />
<xs:element ref="NUMBER_OF_SLICES" />
<xs:element minOccurs="0" maxOccurs="unbounded" ref="DICOM_TAGS" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="NUMBER_OF_SLICES" type="xs:string" />
<xs:element name="DICOM_TAGS" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="TAGNR"/>
<xs:element ref="TAGVALUE"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DATE-INTERVENTIONDATE" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="YEARS" />
<xs:element ref="MONTHS" />
<xs:element ref="DAYS" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DESCTEXT" type="xs:string"/>
<xs:element name="YEARS" type="xs:string" sql:field="years"/>
<xs:element name="MONTHS" type="xs:string" sql:field="months" />
<xs:element name="DAYS" type="xs:string" sql:field="days"/>
<xs:element name="TAGNR" sql:field="TAG_NO:"/>
<xs:element name="TAGVALUE" sql:field="TAG VALUE"/>
</xs:schema>
I get an error which says:
Data mapping to column 'TAG_NO' was already found in the data. Make
sure that no two schema definitions map to the same column.
Does anybody know a way around. Any help would be appreciated. Please
do help!!!
Hello Prakruthi,
Are you trying to use this as a validation schema in an XML Schema collection
or a XDR for mapping queries?

> I get an error which says:
> Data mapping to column 'TAG_NO' was already found in the data. Make
> sure that no two schema definitions map to the same column.
> Does anybody know a way around. Any help would be appreciated. Please
> do help!!!
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Hi Kent,
Im trying to use this as a validation schema. And as you can see from
the xml data , each patient has multiple dicom tags and numbers. I want
to be able to map all these numbers into a single column. Is this
possible?
|||Hi Kent,
I forgot to mention that Tag Numbers and Tag values are two separate
columns. But each column should be able to have multiple values.
Regards,
Prakruthi
|||Hello Prakruthi,
Part of problem is that the schema you posted isn't a valid XSD schema:
a. The sql namespace (aliased by sql isn't defined by your schema.
b. The schema has some other problems, so I rewrote it -- sans the SQL parts
since I'm not sure what these are or do in your processor.
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
<xs:element name="CT_DATASET">
<xs:complexType>
<xs:sequence>
<xs:element name="DATE-INTERVENTIONDATE">
<xs:complexType>
<xs:sequence>
<xs:element name="YEARS" type="xs:string"/>
<xs:element name="MONTHS" type="xs:string"/>
<xs:element name="DAYS" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DESCTEXT" type="xs:string"/>
<xs:element name="NUMBER_OF_SLICES" type="xs:string"/>
<xs:element ref="DICOM_TAGS" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DICOM_TAGS">
<xs:complexType>
<xs:sequence>
<xs:element ref="TAGNR"/>
<xs:element ref="TAGVALUE"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="TAGNR" type="xs:string"/>
<xs:element name="TAGVALUE" type="xs:string"/>
</xs:schema>
I was able to catalog is into a SQL Sever 2005 XML Schema Collection succesfully.
Does any of that help?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Dear Kent,
Thanks for reorganising my schema in the right manner. But I dont think
you understood what I meant to say. Even when I used your modified
schema I could not map the three tagnr and tagvalues associated with
one one single patient into the tagnr and tagvalue column.I get an
error that says
Data mapping to column 'TAG_NO' was already found in the data. Make
sure that no two schema definitions map to the same column.
Is it not possible to map three different values to one column of the
same row? I hope you can help me out again this time.
Regards,
Prakruthi
|||Hello Prakruthi,
That's why I asked if you were using this an mapping schema or as a validation
schema. You said "validation," so I fixed it for that. However, I don't believe
you can target more than one value into a column using a single mapping.
Sadly, I don't have the time to work on this today before going on the road
for the next couple of week. However, it looks like Michael is back online,
so he might be able to help more.

> Thanks for reorganising my schema in the right manner. But I dont
> think
> you understood what I meant to say. Even when I used your modified
> schema I could not map the three tagnr and tagvalues associated with
> one one single patient into the tagnr and tagvalue column.I get an
> error that says
> Data mapping to column 'TAG_NO' was already found in the data. Make
> sure that no two schema definitions map to the same column.
> Is it not possible to map three different values to one column of the
> same row? I hope you can help me out again this time.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Hi Kent,
I wanted to say im not using it as a validation schema. I apologise for
the typo error. I hope micheal would help me out with this issue.
Regards,
Prakruthi
|||Hi Kent,
I wanted to say im not using it as a validation schema. I apologise for
the typo error. I hope micheal would help me out with this issue.
Regards,
Prakruthi
|||I have forwarded it to our mapping schema experts and I hope they will get
back to you.
Ping me if that is not the case.
Michael
"Prakruthi" <prakruthi.rao@.gmail.com> wrote in message
news:1143210046.980577.250430@.e56g2000cwe.googlegr oups.com...
> Hi Kent,
> I wanted to say im not using it as a validation schema. I apologise for
> the typo error. I hope micheal would help me out with this issue.
> Regards,
> Prakruthi
>

Mapping Source and Destination columns

can somebody show an example of how to map source and destination columns when uploading a file to sql server?

Also, please send me the mapping when i want to map source to different destination columns.

I think you have to to be a bit more specific to get answers that will help you.

Mapping schema for GoogleBase XSD

I want to load into a SQL Server table with SQLXMLBulkload an xml file that
is formatted with google base xsd (http://base.google.com/base/base.xsd). I
need a mapping schema for that, correct? To map the elements of the xml to
my table. Does anyone have a mapping schema for this?
Steve
Hi Steve
The mapping depends on the database tables to which you want to map it to.
Best regards
Michael
"Steve Mc" <stevemc@.zillow.com> wrote in message
news:OGEN6NOiHHA.5052@.TK2MSFTNGP05.phx.gbl...
>I want to load into a SQL Server table with SQLXMLBulkload an xml file that
>is formatted with google base xsd (http://base.google.com/base/base.xsd).
>I need a mapping schema for that, correct? To map the elements of the xml
>to my table. Does anyone have a mapping schema for this?
> Steve
>
|||Hello,
Please take a look at http://msdn2.microsoft.com/en-us/library/ms172649.aspx
to understand how annotations work.
Let me know if you need further assistance.
Regards,
Monica Frintu
"Steve Mc" wrote:

> I want to load into a SQL Server table with SQLXMLBulkload an xml file that
> is formatted with google base xsd (http://base.google.com/base/base.xsd). I
> need a mapping schema for that, correct? To map the elements of the xml to
> my table. Does anyone have a mapping schema for this?
> Steve
>
>

Mapping schema for GoogleBase XSD

I want to load into a SQL Server table with SQLXMLBulkload an xml file that
is formatted with google base xsd (http://base.google.com/base/base.xsd). I
need a mapping schema for that, correct? To map the elements of the xml to
my table. Does anyone have a mapping schema for this?
SteveHi Steve
The mapping depends on the database tables to which you want to map it to.
Best regards
Michael
"Steve Mc" <stevemc@.zillow.com> wrote in message
news:OGEN6NOiHHA.5052@.TK2MSFTNGP05.phx.gbl...
>I want to load into a SQL Server table with SQLXMLBulkload an xml file that
>is formatted with google base xsd (http://base.google.com/base/base.xsd).
>I need a mapping schema for that, correct? To map the elements of the xml
>to my table. Does anyone have a mapping schema for this?
> Steve
>|||Hello,
Please take a look at http://msdn2.microsoft.com/en-us/library/ms172649.aspx
to understand how annotations work.
Let me know if you need further assistance.
Regards,
--
Monica Frintu
"Steve Mc" wrote:

> I want to load into a SQL Server table with SQLXMLBulkload an xml file tha
t
> is formatted with google base xsd (http://base.google.com/base/base.xsd).
I
> need a mapping schema for that, correct? To map the elements of the xml t
o
> my table. Does anyone have a mapping schema for this?
> Steve
>
>sql

Mapping pages in database file?

Hi there,
Recently for some reason, after I was trying adjusting the indexes om my
test database, the size of the database, balloned from ~4GB to 13GB+. This
is after I rolledback all the changes I did. The database runs in full
recovery mode.
Now I got the transaction log figured out, and with the help of dbcc
loginfo, I can see what's being used in the log. But what I'd like to find
out is how large the parts of the database is; ie. a complete run down, on
what SQL Server 2000 uses storage space on.
For reference, a dbcc shrinkdatabase gives the following -
11 2 16120 128 16120 128
According to BOL, the last number (Estimated Pages) is what it guess on the
database can be shrunked to, however, that's extremely unlikely, as I know,
the DB should be around 4GB just after being created and filled with data.
How do I go about this little project?
Necessity is the plea for every infringement of human freedom. It is the
argument of tyrants; it is the creed of slaves.
-- William Pitt, 1783
Kim Noer wrote:

> How do I go about this little project?
By using DBCC SHOWCONTIG(table), investigating the Avg. Page Density (full)
and remembering that fillfactor works reverse of what previously thought. To
boil it down, I applied a fillfactor of 15%, when I should had applied 85%.
Clever.
Necessity is the plea for every infringement of human freedom. It is
the argument of tyrants; it is the creed of slaves. -- William Pitt,
1783
|||Kim
http://www.sql-server-performance.co...showcontig.asp
"Kim Noer" <kn@.nospam.dk> wrote in message
news:esALBYs8FHA.3048@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> Recently for some reason, after I was trying adjusting the indexes om my
> test database, the size of the database, balloned from ~4GB to 13GB+. This
> is after I rolledback all the changes I did. The database runs in full
> recovery mode.
> Now I got the transaction log figured out, and with the help of dbcc
> loginfo, I can see what's being used in the log. But what I'd like to find
> out is how large the parts of the database is; ie. a complete run down, on
> what SQL Server 2000 uses storage space on.
> For reference, a dbcc shrinkdatabase gives the following -
> 11 2 16120 128 16120 128
> According to BOL, the last number (Estimated Pages) is what it guess on
> the database can be shrunked to, however, that's extremely unlikely, as I
> know, the DB should be around 4GB just after being created and filled with
> data.
> How do I go about this little project?
> --
> Necessity is the plea for every infringement of human freedom. It is the
> argument of tyrants; it is the creed of slaves.
> -- William Pitt, 1783
>

Mapping pages in database file?

Hi there,
Recently for some reason, after I was trying adjusting the indexes om my
test database, the size of the database, balloned from ~4GB to 13GB+. This
is after I rolledback all the changes I did. The database runs in full
recovery mode.
Now I got the transaction log figured out, and with the help of dbcc
loginfo, I can see what's being used in the log. But what I'd like to find
out is how large the parts of the database is; ie. a complete run down, on
what SQL Server 2000 uses storage space on.
For reference, a dbcc shrinkdatabase gives the following -
11 2 16120 128 16120 128
According to BOL, the last number (Estimated Pages) is what it guess on the
database can be shrunked to, however, that's extremely unlikely, as I know,
the DB should be around 4GB just after being created and filled with data.
How do I go about this little project?
Necessity is the plea for every infringement of human freedom. It is the
argument of tyrants; it is the creed of slaves.
-- William Pitt, 1783Kim Noer wrote:

> How do I go about this little project?
By using DBCC SHOWCONTIG(table), investigating the Avg. Page Density (full)
and remembering that fillfactor works reverse of what previously thought. To
boil it down, I applied a fillfactor of 15%, when I should had applied 85%.
Clever.
Necessity is the plea for every infringement of human freedom. It is
the argument of tyrants; it is the creed of slaves. -- William Pitt,
1783|||Kim
http://www.sql-server-performance.c..._showcontig.asp
"Kim Noer" <kn@.nospam.dk> wrote in message
news:esALBYs8FHA.3048@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> Recently for some reason, after I was trying adjusting the indexes om my
> test database, the size of the database, balloned from ~4GB to 13GB+. This
> is after I rolledback all the changes I did. The database runs in full
> recovery mode.
> Now I got the transaction log figured out, and with the help of dbcc
> loginfo, I can see what's being used in the log. But what I'd like to find
> out is how large the parts of the database is; ie. a complete run down, on
> what SQL Server 2000 uses storage space on.
> For reference, a dbcc shrinkdatabase gives the following -
> 11 2 16120 128 16120 128
> According to BOL, the last number (Estimated Pages) is what it guess on
> the database can be shrunked to, however, that's extremely unlikely, as I
> know, the DB should be around 4GB just after being created and filled with
> data.
> How do I go about this little project?
> --
> Necessity is the plea for every infringement of human freedom. It is the
> argument of tyrants; it is the creed of slaves.
> -- William Pitt, 1783
>

Mapping pages in database file?

Hi there,
Recently for some reason, after I was trying adjusting the indexes om my
test database, the size of the database, balloned from ~4GB to 13GB+. This
is after I rolledback all the changes I did. The database runs in full
recovery mode.
Now I got the transaction log figured out, and with the help of dbcc
loginfo, I can see what's being used in the log. But what I'd like to find
out is how large the parts of the database is; ie. a complete run down, on
what SQL Server 2000 uses storage space on.
For reference, a dbcc shrinkdatabase gives the following -
11 2 16120 128 16120 128
According to BOL, the last number (Estimated Pages) is what it guess on the
database can be shrunked to, however, that's extremely unlikely, as I know,
the DB should be around 4GB just after being created and filled with data.
How do I go about this little project?
--
Necessity is the plea for every infringement of human freedom. It is the
argument of tyrants; it is the creed of slaves.
-- William Pitt, 1783Kim Noer wrote:
> How do I go about this little project?
By using DBCC SHOWCONTIG(table), investigating the Avg. Page Density (full)
and remembering that fillfactor works reverse of what previously thought. To
boil it down, I applied a fillfactor of 15%, when I should had applied 85%.
Clever.
--
Necessity is the plea for every infringement of human freedom. It is
the argument of tyrants; it is the creed of slaves. -- William Pitt,
1783|||Kim
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
"Kim Noer" <kn@.nospam.dk> wrote in message
news:esALBYs8FHA.3048@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> Recently for some reason, after I was trying adjusting the indexes om my
> test database, the size of the database, balloned from ~4GB to 13GB+. This
> is after I rolledback all the changes I did. The database runs in full
> recovery mode.
> Now I got the transaction log figured out, and with the help of dbcc
> loginfo, I can see what's being used in the log. But what I'd like to find
> out is how large the parts of the database is; ie. a complete run down, on
> what SQL Server 2000 uses storage space on.
> For reference, a dbcc shrinkdatabase gives the following -
> 11 2 16120 128 16120 128
> According to BOL, the last number (Estimated Pages) is what it guess on
> the database can be shrunked to, however, that's extremely unlikely, as I
> know, the DB should be around 4GB just after being created and filled with
> data.
> How do I go about this little project?
> --
> Necessity is the plea for every infringement of human freedom. It is the
> argument of tyrants; it is the creed of slaves.
> -- William Pitt, 1783
>

Mapping Output Parameter to a variable!

Hi there,

I am working on SSIS package that gets data from SQL 2005 Database and writes that to a flat file. But I need to write the count of records as part of the header.

Here is what i am trying:

The OLE DB Source is calling a stored procedure and returning two things i.e. a resultset and an output parameter. The data access mode is SQL Command.

Code Snippet

EXEC [Get_logins] ?, ?, ? OUTPUT
In the Set Query Parameters dialogbox, all the three patameters are mapped to three different user variables.

What is happening is that the user variable that is mapped to output parameter is never updated. The header property expression is written as follows

Code Snippet

RIGHT("0000000000" + (DT_STR, 10, 1252)@.LoginCount, 10)

I tried to watch the variable in watch window but to no avail. Any guidance if it is bug or I am missing some thing? Any thoughts, how can I accomplish this? I have also tried adding Row Count Transformation but its variable has the same behaviour. If I set the value of @.LoginCount variable to some value, this initially set value is successfully written to the file header.

Thanks

Paraclete

No bug, the OLE DB Source just doesn't support output parameters from stored procedures. The Execute SQL Task does, though. You could execute that one in your control flow and put your resultset in a variable. A script source component can shred the resultset into rows in your Data Flow.

Or you could issue two queries. One to count the rows and put that value in a variable in the Control Flow, and then another one in the Data Flow to produce the rows.
|||

Hi,

Thanks for your response. Yes I can calculate the number of rows in a separate query, but some of the rows may have bad data. So in this case these rows will be ignored or sent to error output i.e. will not be written to the Flat File Destination. So the count taken in a separate query will be incorrect i.e. CountATStart-Errors not the CountAtstart. This may create problem becuase the header has count of records in the file. Any guidance/thoughts are wellcomed.

Thanks,

Paraclete

|||You were probably on the right track with the Row Count transformation, but it won't write to the variable until all the rows have been recieved, at which point you've already written your header. Try putting a Sort component after the Row Count. This will queue up the rows between the Row Count and the Destination and should allow Row Count to set the variable before the header gets created. By the way, how are you writing the header?

Mapping multiple elements to the same table

I have a huge 1+GB xml file that I'd like to bulk load into a single staging
table in SQL Server. The xml file has subelements that need to be loaded
into the same table as the parent element. For example:
<Customer>
<Address>
</Address>
<ContactInfo>
</ContactInfo>
</Customer>
I cannot seem to get an XSD that will load all the data into 1 table. Also
because of the size of the XML file, performing an XSLT transformation on the
file is not possible. What are my options? Is it possible to map
subelements to the same table as the containing element?
Could you map them to two different views of the same table?
Note also, that the mapping kind of expects that you give a relationship
annotation when you map parents and children. What is the error message or
behaviour that you are getting?
Best regards
Michael
"Mark Weber" <Mark Weber@.discussions.microsoft.com> wrote in message
news:14D8B400-733C-4080-8888-BD3551C6AD09@.microsoft.com...
>I have a huge 1+GB xml file that I'd like to bulk load into a single
>staging
> table in SQL Server. The xml file has subelements that need to be loaded
> into the same table as the parent element. For example:
> <Customer>
> <Address>
> </Address>
> <ContactInfo>
> </ContactInfo>
> </Customer>
> I cannot seem to get an XSD that will load all the data into 1 table.
> Also
> because of the size of the XML file, performing an XSLT transformation on
> the
> file is not possible. What are my options? Is it possible to map
> subelements to the same table as the containing element?

Mapping multiple elements to the same table

I have a huge 1+GB xml file that I'd like to bulk load into a single staging
table in SQL Server. The xml file has subelements that need to be loaded
into the same table as the parent element. For example:
<Customer>
<Address>
</Address>
<ContactInfo>
</ContactInfo>
</Customer>
I cannot seem to get an XSD that will load all the data into 1 table. Also
because of the size of the XML file, performing an XSLT transformation on th
e
file is not possible. What are my options? Is it possible to map
subelements to the same table as the containing element?Could you map them to two different views of the same table?
Note also, that the mapping kind of expects that you give a relationship
annotation when you map parents and children. What is the error message or
behaviour that you are getting?
Best regards
Michael
"Mark Weber" <Mark Weber@.discussions.microsoft.com> wrote in message
news:14D8B400-733C-4080-8888-BD3551C6AD09@.microsoft.com...
>I have a huge 1+GB xml file that I'd like to bulk load into a single
>staging
> table in SQL Server. The xml file has subelements that need to be loaded
> into the same table as the parent element. For example:
> <Customer>
> <Address>
> </Address>
> <ContactInfo>
> </ContactInfo>
> </Customer>
> I cannot seem to get an XSD that will load all the data into 1 table.
> Also
> because of the size of the XML file, performing an XSLT transformation on
> the
> file is not possible. What are my options? Is it possible to map
> subelements to the same table as the containing element?sql

Mapping columns of complexType to different table

In my xml file I have an element called pointInTime which has 4 elements
associated with it.
Among these 4 fields, I need to map 1 field to one SQL table and remaining
fields to another table.
How do I write XSD schema for this.
<xs:element name="pointInTime" sql:relation="SpanFileDetailsTemp">
<xs:complexType>
<xs:sequence>
<xs:element ref="date" sql:field="FileDate"/>
<xs:element ref="isSetl"/>
<xs:element ref="setlQualifier"/>
<xs:element ref="clearingOrg"/>
</xs:sequence>
</xs:complexType>
</xs:element>
I tried to map 1 field to the table.
Now should I repeat this for other table?
But If I repeat this, the lement name PointInTime would be duplicated,
right?
So, should I use <xs:element ref="PointInTime" sql:relation="xxxx"> like
this ?
What is the difference between <xs:> and <xsd:>
I found both these while surfing about XSD's.
Regards
Meena
Meenakshi wrote:

> What is the difference between <xs:> and <xsd:>
> I found both these while surfing about XSD's.
Whether the prefix is xs or xsd or some other prefix (e.g. pf) does not
matter in terms of XML, what matters is the namespace URI bound to the
prefix. So you can use
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"></xs:schema>
or
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"></xsd:schema>
or
<pf:schema xmlns:pf="http://www.w3.org/2001/XMLSchema"></pf:schema>
or any other allowed prefix you like as long as the prefix is bound to
the namespace URI http://www.w3.org/2001/XMLSchema.

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

Mapping columns of complexType to different table

In my xml file I have an element called pointInTime which has 4 elements
associated with it.
Among these 4 fields, I need to map 1 field to one SQL table and remaining
fields to another table.
How do I write XSD schema for this.
<xs:element name="pointInTime" sql:relation="SpanFileDetailsTemp">
<xs:complexType>
<xs:sequence>
<xs:element ref="date" sql:field="FileDate"/>
<xs:element ref="isSetl"/>
<xs:element ref="setlQualifier"/>
<xs:element ref="clearingOrg"/>
</xs:sequence>
</xs:complexType>
</xs:element>
I tried to map 1 field to the table.
Now should I repeat this for other table?
But If I repeat this, the lement name PointInTime would be duplicated,
right?
So, should I use <xs:element ref="PointInTime" sql:relation="xxxx"> like
this ?
What is the difference between <xs:> and <xsd:>
I found both these while surfing about XSD's.
Regards
MeenaMeenakshi wrote:

> What is the difference between <xs:> and <xsd:>
> I found both these while surfing about XSD's.
Whether the prefix is xs or xsd or some other prefix (e.g. pf) does not
matter in terms of XML, what matters is the namespace URI bound to the
prefix. So you can use
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"></xs:schema>
or
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"></xsd:schema>
or
<pf:schema xmlns:pf="http://www.w3.org/2001/XMLSchema"></pf:schema>
or any other allowed prefix you like as long as the prefix is bound to
the namespace URI http://www.w3.org/2001/XMLSchema.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/sql

many blank pages when report is exported to pdf

I have a problem in exporting my report to pdf file, there are so many
blank pages appear in pdf. I dont know why it happens. Anyone please
help me. I really need a solution on this for the release of our
project.
Thanks,
AltheaIs you report slightly wider than the page?
Kulgan.|||Hi,
I had the same issue and it took me couple of days to figure it out why it
was happening. Check the folowing:
1. Report Size
2. Report Body Size
3. Shrink the Table that holds the data..
Suresh
"Kulgan" wrote:
> Is you report slightly wider than the page?
> Kulgan.
>|||Make sure there isn't a lot of unused white space. That seemed to help me.
"Kulgan" <nickamckenna@.gmail.com> wrote in message
news:1123142322.629081.102570@.g14g2000cwa.googlegroups.com...
> Is you report slightly wider than the page?
> Kulgan.
>|||For another "fix" although maybe a long shot, see my post to the "UnWanted
Blank Pages" on 8/4/2005
"tulips_2812" wrote:
> I have a problem in exporting my report to pdf file, there are so many
> blank pages appear in pdf. I dont know why it happens. Anyone please
> help me. I really need a solution on this for the release of our
> project.
> Thanks,
> Althea
>

Manuela Importing File Xml into table with Sql Server 2005

Hi All,
I'm trying to import XML file into SQL without any success.
I use Sql Server 2005 and when I execute this code:
DECLARE @.xmlDoc xml;
SET @.xmlDoc = (
SELECT Q.BulkColumn
FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q);
I obtain this error:
Syntax non corrected in proximity of the word key "BULK". Why?
Moreover,I enable openrowset on window Sql Server 2005 Surface Area
Configuration.
I don't know where is the problem. Please help!!!
How about something like this?
DECLARE @.xmlDoc xml;
SELECT @.xmlDoc = BulkColumn
FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q(BulkColumn)
Does this work?
Denis Ruckebusch
http://blogs.msdn.com/denisruc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Manuela" <manuela@.discussions.microsoft.com> wrote in message
news:466B8045-B4C5-43E3-8C7D-48465DB5D525@.microsoft.com...
> Hi All,
> I'm trying to import XML file into SQL without any success.
> I use Sql Server 2005 and when I execute this code:
> DECLARE @.xmlDoc xml;
> SET @.xmlDoc = (
> SELECT Q.BulkColumn
> FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q);
> I obtain this error:
> Syntax non corrected in proximity of the word key "BULK". Why?
> Moreover,I enable openrowset on window Sql Server 2005 Surface Area
> Configuration.
> I don't know where is the problem. Please help!!!
>
|||Hello Denis,
Excuse me, I don't understand I what to make.
File Xml is Orders.xml:
<root>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">
<Orders CustomerID="XYZBB" OrderDate="2003-06-12T12:00:00"/>
</Customers>
</root>
and my schema is Orders.xsd:
<root>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">
<Orders CustomerID="XYZBB" OrderDate="2003-06-12T12:00:00"/>
</Customers>
</root>
I must use the schema?
I ask you excuse, but I have not just understood what to make.
Aspect one your answer. Thanks Denis.
This has the same error:
DECLARE @.xmlDoc xml;
SELECT @.xmlDoc = BulkColumn
FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q(BulkColumn)
Importing File Xml into table with Sql Server 2005
"Denis Ruckebusch [MSFT]" wrote:

> How about something like this?
> DECLARE @.xmlDoc xml;
> SELECT @.xmlDoc = BulkColumn
> FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q(BulkColumn)
> Does this work?
>
> Denis Ruckebusch
> http://blogs.msdn.com/denisruc
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "Manuela" <manuela@.discussions.microsoft.com> wrote in message
> news:466B8045-B4C5-43E3-8C7D-48465DB5D525@.microsoft.com...
>
>
|||Hi Denis,
I have a 1GB XML file that is provided to us by a vendor and I need t
load it into our SQL 2005 database. Do you have any recommendation on
which method to use in order to accomplish this task? I am not sure
that the simple BULK load can handle it. Thanks,
Adi
On Jan 23, 3:59 pm, "Denis Ruckebusch [MSFT]"
<denis...@.online.microsoft.com> wrote:[vbcol=seagreen]
> How about something like this?
> DECLARE @.xmlDoc xml;
> SELECT @.xmlDoc = BulkColumn
> FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q(BulkColumn)
> Does this work?
> Denis Ruckebuschhttp://blogs.msdn.com/denisruc
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm
> "Manuela" <manu...@.discussions.microsoft.com> wrote in messagenews:466B8045-B4C5-43E3-8C7D-48465DB5D525@.microsoft.com...
>
|||How do you want to load it? Into an XML data type column? Or shred it into
relational form?
Also what are you planning on doing to the document later?
Best regards
Michael
"Adi" <adisaric@.hotmail.com> wrote in message
news:1169661458.745857.233320@.a75g2000cwd.googlegr oups.com...
> Hi Denis,
> I have a 1GB XML file that is provided to us by a vendor and I need t
> load it into our SQL 2005 database. Do you have any recommendation on
> which method to use in order to accomplish this task? I am not sure
> that the simple BULK load can handle it. Thanks,
> Adi
> On Jan 23, 3:59 pm, "Denis Ruckebusch [MSFT]"
> <denis...@.online.microsoft.com> wrote:
>
|||The XML format should not have anything to do with your syntax error.
Are you sure you are using SQL Server 2005?
If so, you may also want to make sure that you use the same matching single
quotes for the file name argument:
FROM OPENROWSET(BULK 'C:\Orders.xml', SINGLE_CLOB) AS Q(BulkColumn)
Best regards
Michael
"Manuela" <manuela@.discussions.microsoft.com> wrote in message
news:6A48B9ED-0949-4C53-BF54-3D1D36D84C35@.microsoft.com...[vbcol=seagreen]
> Hello Denis,
> Excuse me, I don't understand I what to make.
> File Xml is Orders.xml:
> <root>
> <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
> <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
> <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
> </Customers>
> <Customers CustomerID="XYZBB" ContactName="Steve"
> CompanyName="Company2">
> <Orders CustomerID="XYZBB" OrderDate="2003-06-12T12:00:00"/>
> </Customers>
> </root>
> and my schema is Orders.xsd:
> <root>
> <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
> <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
> <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
> </Customers>
> <Customers CustomerID="XYZBB" ContactName="Steve"
> CompanyName="Company2">
> <Orders CustomerID="XYZBB" OrderDate="2003-06-12T12:00:00"/>
> </Customers>
> </root>
> I must use the schema?
> I ask you excuse, but I have not just understood what to make.
> Aspect one your answer. Thanks Denis.
> This has the same error:
> DECLARE @.xmlDoc xml;
> SELECT @.xmlDoc = BulkColumn
> FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q(BulkColumn)
>
> --
> Importing File Xml into table with Sql Server 2005
>
> "Denis Ruckebusch [MSFT]" wrote:
|||I am looking to shred it into relational form. Planning to populate
several tables based on the data contained in the file. Once that is
done the xml file can be either deleted or archived. Thanks,
Adi
On Jan 24, 4:51 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:[vbcol=seagreen]
> How do you want to load it? Into anXMLdata type column? Or shred it into
> relational form?
> Also what are you planning on doing to the document later?
> Best regards
> Michael
> "Adi" <adisa...@.hotmail.com> wrote in messagenews:1169661458.745857.233320@.a75g2000cwd.g ooglegroups.com...
>
>
>
>
>
|||In that case I would recommend looking into defining an annotated XML Schema
for the data and using the SQLXML XML Bulkload object.
Best regards
Michael
"Adi" <adisaric@.hotmail.com> wrote in message
news:1169741996.299835.197860@.l53g2000cwa.googlegr oups.com...
>I am looking to shred it into relational form. Planning to populate
> several tables based on the data contained in the file. Once that is
> done the xml file can be either deleted or archived. Thanks,
> Adi
> On Jan 24, 4:51 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>

Manuela Importing File Xml into table with Sql Server 2005

Hi All,
I'm trying to import XML file into SQL without any success.
I use Sql Server 2005 and when I execute this code:
DECLARE @.xmlDoc xml;
SET @.xmlDoc = (
SELECT Q.BulkColumn
FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q);
I obtain this error:
Syntax non corrected in proximity of the word key "BULK". Why?
Moreover,I enable openrowset on window Sql Server 2005 Surface Area
Configuration.
I don't know where is the problem. Please help!!!How about something like this?
DECLARE @.xmlDoc xml;
SELECT @.xmlDoc = BulkColumn
FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q(BulkColumn)
Does this work?
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Manuela" <manuela@.discussions.microsoft.com> wrote in message
news:466B8045-B4C5-43E3-8C7D-48465DB5D525@.microsoft.com...
> Hi All,
> I'm trying to import XML file into SQL without any success.
> I use Sql Server 2005 and when I execute this code:
> DECLARE @.xmlDoc xml;
> SET @.xmlDoc = (
> SELECT Q.BulkColumn
> FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q);
> I obtain this error:
> Syntax non corrected in proximity of the word key "BULK". Why?
> Moreover,I enable openrowset on window Sql Server 2005 Surface Area
> Configuration.
> I don't know where is the problem. Please help!!!
>|||Hello Denis,
Excuse me, I don't understand I what to make.
File Xml is Orders.xml:
<root>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">
<Orders CustomerID="XYZBB" OrderDate="2003-06-12T12:00:00"/>
</Customers>
</root>
and my schema is Orders.xsd:
<root>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">
<Orders CustomerID="XYZBB" OrderDate="2003-06-12T12:00:00"/>
</Customers>
</root>
I must use the schema?
I ask you excuse, but I have not just understood what to make.
Aspect one your answer. Thanks Denis.
This has the same error:
DECLARE @.xmlDoc xml;
SELECT @.xmlDoc = BulkColumn
FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q(BulkColumn)
Importing File Xml into table with Sql Server 2005
"Denis Ruckebusch [MSFT]" wrote:

> How about something like this?
> DECLARE @.xmlDoc xml;
> SELECT @.xmlDoc = BulkColumn
> FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q(BulkColumn)
> Does this work?
>
> Denis Ruckebusch
> http://blogs.msdn.com/denisruc
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "Manuela" <manuela@.discussions.microsoft.com> wrote in message
> news:466B8045-B4C5-43E3-8C7D-48465DB5D525@.microsoft.com...
>
>|||Hi Denis,
I have a 1GB XML file that is provided to us by a vendor and I need t
load it into our SQL 2005 database. Do you have any recommendation on
which method to use in order to accomplish this task? I am not sure
that the simple BULK load can handle it. Thanks,
Adi
On Jan 23, 3:59 pm, "Denis Ruckebusch [MSFT]"
<denis...@.online.microsoft.com> wrote:
> How about something like this?
> DECLARE @.xmlDoc xml;
> SELECT @.xmlDoc = BulkColumn
> FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q(BulkColumn)
> Does this work?
> Denis Ruckebuschhttp://blogs.msdn.com/denisruc
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified athttp:/
/www.microsoft.com/info/cpyright.htm
> "Manuela" <manu...@.discussions.microsoft.com> wrote in messagenews:466B804
5-B4C5-43E3-8C7D-48465DB5D525@.microsoft.com...
>
>|||How do you want to load it? Into an XML data type column? Or shred it into
relational form?
Also what are you planning on doing to the document later?
Best regards
Michael
"Adi" <adisaric@.hotmail.com> wrote in message
news:1169661458.745857.233320@.a75g2000cwd.googlegroups.com...
> Hi Denis,
> I have a 1GB XML file that is provided to us by a vendor and I need t
> load it into our SQL 2005 database. Do you have any recommendation on
> which method to use in order to accomplish this task? I am not sure
> that the simple BULK load can handle it. Thanks,
> Adi
> On Jan 23, 3:59 pm, "Denis Ruckebusch [MSFT]"
> <denis...@.online.microsoft.com> wrote:
>|||The XML format should not have anything to do with your syntax error.
Are you sure you are using SQL Server 2005?
If so, you may also want to make sure that you use the same matching single
quotes for the file name argument:
FROM OPENROWSET(BULK 'C:\Orders.xml', SINGLE_CLOB) AS Q(BulkColumn)
Best regards
Michael
"Manuela" <manuela@.discussions.microsoft.com> wrote in message
news:6A48B9ED-0949-4C53-BF54-3D1D36D84C35@.microsoft.com...
> Hello Denis,
> Excuse me, I don't understand I what to make.
> File Xml is Orders.xml:
> <root>
> <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
> <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
> <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
> </Customers>
> <Customers CustomerID="XYZBB" ContactName="Steve"
> CompanyName="Company2">
> <Orders CustomerID="XYZBB" OrderDate="2003-06-12T12:00:00"/>
> </Customers>
> </root>
> and my schema is Orders.xsd:
> <root>
> <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
> <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
> <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
> </Customers>
> <Customers CustomerID="XYZBB" ContactName="Steve"
> CompanyName="Company2">
> <Orders CustomerID="XYZBB" OrderDate="2003-06-12T12:00:00"/>
> </Customers>
> </root>
> I must use the schema?
> I ask you excuse, but I have not just understood what to make.
> Aspect one your answer. Thanks Denis.
> This has the same error:
> DECLARE @.xmlDoc xml;
> SELECT @.xmlDoc = BulkColumn
> FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q(BulkColumn)
>
> --
> Importing File Xml into table with Sql Server 2005
>
> "Denis Ruckebusch [MSFT]" wrote:
>|||I am looking to shred it into relational form. Planning to populate
several tables based on the data contained in the file. Once that is
done the xml file can be either deleted or archived. Thanks,
Adi
On Jan 24, 4:51 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> How do you want to load it? Into anXMLdata type column? Or shred it into
> relational form?
> Also what are you planning on doing to the document later?
> Best regards
> Michael
> "Adi" <adisa...@.hotmail.com> wrote in messagenews:1169661458.745857.233320
@.a75g2000cwd.googlegroups.com...
>
>
>
>
>
>
>
>
>
>|||Lines: 76
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: tide504.microsoft.com 131.107.0.74
Xref: leafnode.mcse.ms microsoft.public.sqlserver.xml:1506
In that case I would recommend looking into defining an annotated XML Schema
for the data and using the SQLXML XML Bulkload object.
Best regards
Michael
"Adi" <adisaric@.hotmail.com> wrote in message
news:1169741996.299835.197860@.l53g2000cwa.googlegroups.com...
>I am looking to shred it into relational form. Planning to populate
> several tables based on the data contained in the file. Once that is
> done the xml file can be either deleted or archived. Thanks,
> Adi
> On Jan 24, 4:51 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>sql

Manually truncate a LOG file

SQL 7.0
How do I manually truncate a LOG file?
Thanks,
Don
BACKUP LOG database_name
WITH TRUNCATE_ONLY
BOL describes it nicely.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
> SQL 7.0
> How do I manually truncate a LOG file?
> Thanks,
> Don
>
|||That's the command I used on my 30GB LOG file and after
it ran without errors, all the pink turned to blue in EM
and the space remained the same.
How can I get rid of all of this space in the LOG file?
Thanks,
Don

>--Original Message--
>BACKUP LOG database_name
> WITH TRUNCATE_ONLY
>BOL describes it nicely.
>--
>Mike Epprecht, Microsoft SQL Server MVP
>Zurich, Switzerland
>
>IM: mike@.epprecht.net
>MVP Program: http://www.microsoft.com/mvp
>Blog: http://www.msmvps.com/epprecht/
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>
>.
>
|||I do that command and then go into EM and right-click on DB.
Choose SHRINK files.
Click on the FILES button - brings you to another pop-up window - choose the
LOG file from the DROPDOWN. Then click OK (the correct check box should be
already the default). This window disappears - then I cancel off the main
window, so I don't shrink the DB itself.
I wish I knew the SQL command string to do this action - EM does it in a
very cumbersome fashion.
But the LOG is 1024 K after this operation - so I know it works!!
"Don" wrote:

> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
> message
>
|||Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
going to truncate the log why not set the recovery mode to SIMPLE?
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:546301c4c908$6a3ab450$a401280a@.phx.gbl...[vbcol=seagreen]
> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
> message
|||Andrew,
Don is using SQL 7. So best option is that he can enable the TRUNCATE LOG
ON CHECKPOINT option using sp_dboption.
Don,
Enable the database option TRUNCATE LOG ON CHECKPOINT and execute below:-
backup log dbname with truncate_only
go
DBCC SHRINKFILE (see books online)
Thanks
Hari
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O$gLdzRyEHA.3844@.TK2MSFTNGP09.phx.gbl...
> Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
> going to truncate the log why not set the recovery mode to SIMPLE?
> --
> Andrew J. Kelly SQL MVP
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:546301c4c908$6a3ab450$a401280a@.phx.gbl...
>
sql

Manually truncate a LOG file

SQL 7.0
How do I manually truncate a LOG file?
Thanks,
DonBACKUP LOG database_name
WITH TRUNCATE_ONLY
BOL describes it nicely.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
> SQL 7.0
> How do I manually truncate a LOG file?
> Thanks,
> Don
>|||That's the command I used on my 30GB LOG file and after
it ran without errors, all the pink turned to blue in EM
and the space remained the same.
How can I get rid of all of this space in the LOG file?
Thanks,
Don
>--Original Message--
>BACKUP LOG database_name
> WITH TRUNCATE_ONLY
>BOL describes it nicely.
>--
>Mike Epprecht, Microsoft SQL Server MVP
>Zurich, Switzerland
>
>IM: mike@.epprecht.net
>MVP Program: http://www.microsoft.com/mvp
>Blog: http://www.msmvps.com/epprecht/
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>> SQL 7.0
>> How do I manually truncate a LOG file?
>> Thanks,
>> Don
>
>.
>|||I do that command and then go into EM and right-click on DB.
Choose SHRINK files.
Click on the FILES button - brings you to another pop-up window - choose the
LOG file from the DROPDOWN. Then click OK (the correct check box should be
already the default). This window disappears - then I cancel off the main
window, so I don't shrink the DB itself.
I wish I knew the SQL command string to do this action - EM does it in a
very cumbersome fashion.
But the LOG is 1024 K after this operation - so I know it works!!
"Don" wrote:
> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
> >--Original Message--
> >BACKUP LOG database_name
> > WITH TRUNCATE_ONLY
> >
> >BOL describes it nicely.
> >
> >--
> >Mike Epprecht, Microsoft SQL Server MVP
> >Zurich, Switzerland
> >
> >
> >IM: mike@.epprecht.net
> >
> >MVP Program: http://www.microsoft.com/mvp
> >
> >Blog: http://www.msmvps.com/epprecht/
> >
> >"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
> >> SQL 7.0
> >>
> >> How do I manually truncate a LOG file?
> >>
> >> Thanks,
> >> Don
> >>
> >
> >
> >.
> >
>|||Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
going to truncate the log why not set the recovery mode to SIMPLE?
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:546301c4c908$6a3ab450$a401280a@.phx.gbl...
> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
>>--Original Message--
>>BACKUP LOG database_name
>> WITH TRUNCATE_ONLY
>>BOL describes it nicely.
>>--
>>Mike Epprecht, Microsoft SQL Server MVP
>>Zurich, Switzerland
>>
>>IM: mike@.epprecht.net
>>MVP Program: http://www.microsoft.com/mvp
>>Blog: http://www.msmvps.com/epprecht/
>>"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>> SQL 7.0
>> How do I manually truncate a LOG file?
>> Thanks,
>> Don
>>
>>.|||Andrew,
Don is using SQL 7. So best option is that he can enable the TRUNCATE LOG
ON CHECKPOINT option using sp_dboption.
Don,
Enable the database option TRUNCATE LOG ON CHECKPOINT and execute below:-
backup log dbname with truncate_only
go
DBCC SHRINKFILE (see books online)
Thanks
Hari
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O$gLdzRyEHA.3844@.TK2MSFTNGP09.phx.gbl...
> Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
> going to truncate the log why not set the recovery mode to SIMPLE?
> --
> Andrew J. Kelly SQL MVP
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:546301c4c908$6a3ab450$a401280a@.phx.gbl...
>> That's the command I used on my 30GB LOG file and after
>> it ran without errors, all the pink turned to blue in EM
>> and the space remained the same.
>> How can I get rid of all of this space in the LOG file?
>> Thanks,
>> Don
>>--Original Message--
>>BACKUP LOG database_name
>> WITH TRUNCATE_ONLY
>>BOL describes it nicely.
>>--
>>Mike Epprecht, Microsoft SQL Server MVP
>>Zurich, Switzerland
>>
>>IM: mike@.epprecht.net
>>MVP Program: http://www.microsoft.com/mvp
>>Blog: http://www.msmvps.com/epprecht/
>>"Don" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>> SQL 7.0
>> How do I manually truncate a LOG file?
>> Thanks,
>> Don
>>
>>.
>

Manually truncate a LOG file

SQL 7.0
How do I manually truncate a LOG file?
Thanks,
DonBACKUP LOG database_name
WITH TRUNCATE_ONLY
BOL describes it nicely.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
> SQL 7.0
> How do I manually truncate a LOG file?
> Thanks,
> Don
>|||That's the command I used on my 30GB LOG file and after
it ran without errors, all the pink turned to blue in EM
and the space remained the same.
How can I get rid of all of this space in the LOG file?
Thanks,
Don

>--Original Message--
>BACKUP LOG database_name
> WITH TRUNCATE_ONLY
>BOL describes it nicely.
>--
>Mike Epprecht, Microsoft SQL Server MVP
>Zurich, Switzerland
>
>IM: mike@.epprecht.net
>MVP Program: http://www.microsoft.com/mvp
>Blog: http://www.msmvps.com/epprecht/
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>
>.
>|||I do that command and then go into EM and right-click on DB.
Choose SHRINK files.
Click on the FILES button - brings you to another pop-up window - choose the
LOG file from the DROPDOWN. Then click OK (the correct check box should be
already the default). This window disappears - then I cancel off the main
window, so I don't shrink the DB itself.
I wish I knew the SQL command string to do this action - EM does it in a
very cumbersome fashion.
But the LOG is 1024 K after this operation - so I know it works!!
"Don" wrote:

> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
>
> message
>|||Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
going to truncate the log why not set the recovery mode to SIMPLE?
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:546301c4c908$6a3ab450$a401280a@.phx.gbl...[vbcol=seagreen]
> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
>
> message|||Andrew,
Don is using SQL 7. So best option is that he can enable the TRUNCATE LOG
ON CHECKPOINT option using sp_dboption.
Don,
Enable the database option TRUNCATE LOG ON CHECKPOINT and execute below:-
backup log dbname with truncate_only
go
DBCC SHRINKFILE (see books online)
Thanks
Hari
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O$gLdzRyEHA.3844@.TK2MSFTNGP09.phx.gbl...
> Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
> going to truncate the log why not set the recovery mode to SIMPLE?
> --
> Andrew J. Kelly SQL MVP
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:546301c4c908$6a3ab450$a401280a@.phx.gbl...
>