Showing posts with label articles. Show all posts
Showing posts with label articles. Show all posts

Thursday, March 22, 2012

Difficulty on inserting data into several tables from XML file using Bulk Insert

Hi, I am a newbie for SQL's XML section and need some help from you guys. I just go through some simple articles with simple sample record. However, I found confusing when I want to inserting data into several tables from a prefer XML file.

My scenario is like that :

Tables :

1) trade ( id int)

2) trans ( id int , startdate datetime , enddate datetime , status bit ,comment varchar(50), tranCode int)

* tranCode = id from trade table

3) transInfo ( id int , userlogin int , amount money , transID int ) * transID = id from trans table

4) trandetails ( id int, transID int ) * transID = id from trans table

Preferred XML :

<?xml version='1.0' encoding='utf-8'?>

<ROOT>

<trade code="1">

<trans id = '123' startdate = '2007-08-02 11:35:00' enddate='2007-04-29 11:36:00' status = '1' >

<transinfo>

<clienttrans login="Alex" amount="1000">
</clienttrans>

</transinfo>

<trandetails>
<trandetail><value>Shipping</value></trandetail>
</trandetails>

<comments><comment>Done</comment></comments>

</trans>


<trans id = '123' startdate = '2007-08-02 11:35:00' enddate='2007-04-29 11:36:00' status = '1' >

<transinfo>

<clienttrans login="Ken" amount="20000">
</clienttrans>

</transinfo>

<trandetails>
<trandetail><value>Transportation</value></trandetail>
</trandetails>

<comments><comment>Done</comment></comments>
</trans>

</trade>

</ROOT>

I am confusing on how to arrange up the elements based on the tables which were created. Or have to re-fix my table ? Any suggestion based on my scenario. Hope able to get any ideas/opinions from here asap. Thanks alot.

Best Regards,

Hans

Problem solved although kinda confusing at the early stage when doing the XML schema.

Wednesday, March 21, 2012

Differntial Backup

Question
How exactly does the technology of a differntial backup work. I have read
msdn articles, etc... But nothing states in particular how the technology
itself operates. It doesn't appear to use the log file.
Since it is a single file (No secondaries used) how can it create a
differential file of updates that have occured since the last full backup?
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.
Paul
BOL says
differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.

> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
The sequence for restoring differential database backups is:
1.. Restore the most recent database backup.
2.. Restore the last differential database backup.
3.. Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
|||The GAM (global allocation map) keeps track of which Extents have been
changed since the last full backup. Those are the ones written by a
differential backup.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
|||This is exactly what I was looking for.
Thanks
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.
"Geoff N. Hiten" wrote:

> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>
|||Actually it is the BCM (Bulk Changed Map) not the GAM but the same concept
applies. If a single bit on the extent was changed since the last full
backup this bit for that extent in the BCM will be flipped.
Andrew J. Kelly SQL MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23NkgfLosHHA.1168@.TK2MSFTNGP02.phx.gbl...
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>
|||A differential backup copies only the extents that were changed since the
last full backup was made. The extents are copied onto a specified backup
device.
SQL Server can quickly tell which extents need to be backed up by examining
the bits on the Differential Change Map DCM pages for each data file in the
database. DCM pages are big bitmaps, with one bit representing an extent in a
file, just like the GAM and SGAM pages.
Each time a full backup is made, all the bits in the DCM are cleared to 0.
When any page in an extent is changed, its corresponding bit in the DCM page
is changed to 1.
Manu
"Paul Bergson" wrote:
[vbcol=seagreen]
> This is exactly what I was looking for.
> Thanks
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
>
> "Geoff N. Hiten" wrote:

Differntial Backup

Question
How exactly does the technology of a differntial backup work. I have read
msdn articles, etc... But nothing states in particular how the technology
itself operates. It doesn't appear to use the log file.
Since it is a single file (No secondaries used) how can it create a
differential file of updates that have occured since the last full backup?
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.Hi
have you seen this link
http://msdn2.microsoft.com/en-us/library/ms345448(SQL.90).aspx
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||Paul
BOL says
differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
The sequence for restoring differential database backups is:
1.. Restore the most recent database backup.
2.. Restore the last differential database backup.
3.. Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||The GAM (global allocation map) keeps track of which Extents have been
changed since the last full backup. Those are the ones written by a
differential backup.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||This is exactly what I was looking for.
Thanks
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.
"Geoff N. Hiten" wrote:
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> > Question
> >
> > How exactly does the technology of a differntial backup work. I have read
> > msdn articles, etc... But nothing states in particular how the technology
> > itself operates. It doesn't appear to use the log file.
> >
> > Since it is a single file (No secondaries used) how can it create a
> > differential file of updates that have occured since the last full backup?
> >
> > --
> > Paul Bergson
> > MVP - Directory Services
> > MCT, MCSE, MCSA, Security+, BS CSci
> > 2003, 2000 (Early Achiever), NT
> >
> > http://www.pbbergs.com
> >
> > This posting is provided "AS IS" with no warranties or rights.
>|||Actually it is the BCM (Bulk Changed Map) not the GAM but the same concept
applies. If a single bit on the extent was changed since the last full
backup this bit for that extent in the BCM will be flipped.
--
Andrew J. Kelly SQL MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23NkgfLosHHA.1168@.TK2MSFTNGP02.phx.gbl...
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>> Question
>> How exactly does the technology of a differntial backup work. I have
>> read
>> msdn articles, etc... But nothing states in particular how the technology
>> itself operates. It doesn't appear to use the log file.
>> Since it is a single file (No secondaries used) how can it create a
>> differential file of updates that have occured since the last full
>> backup?
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> This posting is provided "AS IS" with no warranties or rights.
>|||A differential backup copies only the extents that were changed since the
last full backup was made. The extents are copied onto a specified backup
device.
SQL Server can quickly tell which extents need to be backed up by examining
the bits on the Differential Change Map DCM pages for each data file in the
database. DCM pages are big bitmaps, with one bit representing an extent in a
file, just like the GAM and SGAM pages.
Each time a full backup is made, all the bits in the DCM are cleared to 0.
When any page in an extent is changed, its corresponding bit in the DCM page
is changed to 1.
Manu
"Paul Bergson" wrote:
> This is exactly what I was looking for.
> Thanks
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
>
> "Geoff N. Hiten" wrote:
> > The GAM (global allocation map) keeps track of which Extents have been
> > changed since the last full backup. Those are the ones written by a
> > differential backup.
> >
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> >
> >
> >
> >
> >
> > "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> > news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> > > Question
> > >
> > > How exactly does the technology of a differntial backup work. I have read
> > > msdn articles, etc... But nothing states in particular how the technology
> > > itself operates. It doesn't appear to use the log file.
> > >
> > > Since it is a single file (No secondaries used) how can it create a
> > > differential file of updates that have occured since the last full backup?
> > >
> > > --
> > > Paul Bergson
> > > MVP - Directory Services
> > > MCT, MCSE, MCSA, Security+, BS CSci
> > > 2003, 2000 (Early Achiever), NT
> > >
> > > http://www.pbbergs.com
> > >
> > > This posting is provided "AS IS" with no warranties or rights.
> >
> >

Differntial Backup

Question
How exactly does the technology of a differntial backup work. I have read
msdn articles, etc... But nothing states in particular how the technology
itself operates. It doesn't appear to use the log file.
Since it is a single file (No secondaries used) how can it create a
differential file of updates that have occured since the last full backup?
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.Hi
have you seen this link
http://msdn2.microsoft.com/en-us/library/ms345448(SQL.90).aspx
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||Paul
BOL says
differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.

> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
The sequence for restoring differential database backups is:
1.. Restore the most recent database backup.
2.. Restore the last differential database backup.
3.. Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||The GAM (global allocation map) keeps track of which Extents have been
changed since the last full backup. Those are the ones written by a
differential backup.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||This is exactly what I was looking for.
Thanks
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.
"Geoff N. Hiten" wrote:

> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>|||Actually it is the BCM (Bulk Changed Map) not the GAM but the same concept
applies. If a single bit on the extent was changed since the last full
backup this bit for that extent in the BCM will be flipped.
Andrew J. Kelly SQL MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23NkgfLosHHA.1168@.TK2MSFTNGP02.phx.gbl...
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>|||A differential backup copies only the extents that were changed since the
last full backup was made. The extents are copied onto a specified backup
device.
SQL Server can quickly tell which extents need to be backed up by examining
the bits on the Differential Change Map DCM pages for each data file in the
database. DCM pages are big bitmaps, with one bit representing an extent in
a
file, just like the GAM and SGAM pages.
Each time a full backup is made, all the bits in the DCM are cleared to 0.
When any page in an extent is changed, its corresponding bit in the DCM page
is changed to 1.
Manu
"Paul Bergson" wrote:
[vbcol=seagreen]
> This is exactly what I was looking for.
> Thanks
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
>
> "Geoff N. Hiten" wrote:
>

Sunday, February 19, 2012

Different between SQL Server 7.0 and SQK Server 2000

Dear all
Is there any articles descript the comparsion betwwen SQL Server 7.0 and 2000?
Many thanksCheck the MSDN articles what's new in SQL 2000, for example
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/whatsnew/wn_whatnew_7im0.asp?frame=true.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Kenny" <Kenny@.discussions.microsoft.com> wrote in message
news:DF9AA965-4550-431A-BB16-C6EC40352C63@.microsoft.com...
> Dear all
> Is there any articles descript the comparsion betwwen SQL Server 7.0 and
2000?
> Many thanks
>

Different between SQL Server 7.0 and SQK Server 2000

Dear all
Is there any articles descript the comparsion betwwen SQL Server 7.0 and 200
0?
Many thanksCheck the MSDN articles what's new in SQL 2000, for example
http://msdn.microsoft.com/library/d...asp?frame=true.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Kenny" <Kenny@.discussions.microsoft.com> wrote in message
news:DF9AA965-4550-431A-BB16-C6EC40352C63@.microsoft.com...
> Dear all
> Is there any articles descript the comparsion betwwen SQL Server 7.0 and
2000?
> Many thanks
>

Different between SQL Server 7.0 and SQK Server 2000

Dear all
Is there any articles descript the comparsion betwwen SQL Server 7.0 and 2000?
Many thanks
Check the MSDN articles what's new in SQL 2000, for example
http://msdn.microsoft.com/library/de...sp?frame=true.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Kenny" <Kenny@.discussions.microsoft.com> wrote in message
news:DF9AA965-4550-431A-BB16-C6EC40352C63@.microsoft.com...
> Dear all
> Is there any articles descript the comparsion betwwen SQL Server 7.0 and
2000?
> Many thanks
>

Tuesday, February 14, 2012

Differences between SQL Server 2005 x64 vs X86?

Hi there,

I'm trying to find what are the main differences between SQL Server 2005 64-bits and 32-bits. So far, I've found some articles about TPC-C performance but I would like to know some response time or execution time of a batch or SSIS packages.

Any information about this 2 versions is appreciated.

Thanks!

Here are some articles that you may find interesting:

http://www.microsoft.com/sql/editions/64bit/default.mspx

http://www.microsoft.com/sql/techinfo/whitepapers/advantages-64bit-environment.mspx

64-bit does not automatically mean better performance. The only way to get actual response time of your application is to test on the 2 different platforms. Every application will be a different so it is impossible to say how much better or worse it will be on 64-bit versus 32-bit.

Hope this helps,

Peter Saddow

|||Hi Peter,

Thanks for the links, I had already read those articles. The second one was the most relevant and I'm testing right now my application on SQL Server 2005 x64 and it's not performing better :(. So you were right, it depends on the application.

Once again, thanks for your reply.