Comparing two databases for deleted records

hi ,
there is a tool that do compare between 2 databases and display deleted records an changes in schema and data ,
it's friendlly tool with beautiful gui for users.
it is called dbMaestro.
You can find it here:
http://www.extreme.co.il
You might want to check out the Red-Gate tools SQL Compare and SQL
DataCompare. I think these tools will do what you are requesting. Here is
their website: http://www.red-gate.com/
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"yaniv cohen" <yanivc@.extreme.co.il> wrote in message
news:30D974F8-F7EC-4AD7-BCC7-DDE5D9146096@.microsoft.com...
> hi ,
> there is a tool that do compare between 2 databases and display deleted
records an changes in schema and data ,
> it's friendlly tool with beautiful gui for users.
> it is called dbMaestro.
> You can find it here:
> http://www.extreme.co.il
>

Comparing table structures.

Hi all,

Could anyone let me know if there are any Scripts available for comparing the schema differences between two SQL server databases?

Thanks

DBAnalyst

I once made a script for that, feel free to contact me if I should send it over.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Please send it Jens, Thanks a lot.sqlsql

Compare sql (SQL DIFF)

hi,
I am looking for a program or utility that it could compare sql server table
schema, views, triggers and sps?
not sure which program is better. Suggestions please.
ThanksTry Red Gate SQL Compare (www.red-gate.com).
Ben Nevarez, MCDBA, OCP
Database Administrator
"mecn" wrote:
> hi,
> I am looking for a program or utility that it could compare sql server table
> schema, views, triggers and sps?
> not sure which program is better. Suggestions please.
> Thanks
>
>|||Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u90BoMRpGHA.4032@.TK2MSFTNGP03.phx.gbl...
> hi,
> I am looking for a program or utility that it could compare sql server
> table schema, views, triggers and sps?
> not sure which program is better. Suggestions please.
> Thanks
>|||Red-gate rules!
"mecn" wrote:
> Thanks
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:u90BoMRpGHA.4032@.TK2MSFTNGP03.phx.gbl...
> > hi,
> >
> > I am looking for a program or utility that it could compare sql server
> > table schema, views, triggers and sps?
> > not sure which program is better. Suggestions please.
> >
> > Thanks
> >
>
>|||Try ApexSQL's SQLDiff tool.
www.ApexSQL.com
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u90BoMRpGHA.4032@.TK2MSFTNGP03.phx.gbl...
> hi,
> I am looking for a program or utility that it could compare sql server
> table schema, views, triggers and sps?
> not sure which program is better. Suggestions please.
> Thanks
>|||mecn,
The free, open-source SchemaCrawler tool will do what you need.
SchemaCrawler outputs details of your schema (tables, views,
procedures, and more) in a diff-able plain-text format (text, CSV, or
XHTML). SchemaCrawler can also output data (including CLOBs and BLOBs)
in the same plain-text formats. You can use a standard diff program to
diff the current output with a reference version of the output.
SchemaCrawler can be run either from the command line, or as an ant
task. A lot of examples are available with the download to help you get
started.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party libraries are required.
Sualeh Fatehi.

Compare Schemas...XSD

Hey all,

I am currently researching ways to compare databases via an XSD schema.
I wrote a small app that creates a dataset from a database and exports
that dataset to XSD. This gives me an XSD file with tables and
relationships representing the entire database.

At this point, I am trying to find ways to compare these schemas. Does
anybody know of a way to do this easily and to record differences if
there are any?

Also, any information on comparing databases using any method would be
greatly appreciated.

Thanks,

--
ShockShock (no@.way.com) writes:
> I am currently researching ways to compare databases via an XSD schema.
> I wrote a small app that creates a dataset from a database and exports
> that dataset to XSD. This gives me an XSD file with tables and
> relationships representing the entire database.
> At this point, I am trying to find ways to compare these schemas. Does
> anybody know of a way to do this easily and to record differences if
> there are any?
> Also, any information on comparing databases using any method would be
> greatly appreciated.

My knowledge on XSD is weak, so I skip that question. For comparing
databases in general, a common recommendation is Red Gate's SQL Compare,
although I have never used it myself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Shock (no@.way.com) writes:
>>I am currently researching ways to compare databases via an XSD schema.
>> I wrote a small app that creates a dataset from a database and exports
>>that dataset to XSD. This gives me an XSD file with tables and
>>relationships representing the entire database.
>>
>>At this point, I am trying to find ways to compare these schemas. Does
>>anybody know of a way to do this easily and to record differences if
>>there are any?
>>
>>Also, any information on comparing databases using any method would be
>>greatly appreciated.
>
> My knowledge on XSD is weak, so I skip that question. For comparing
> databases in general, a common recommendation is Red Gate's SQL Compare,
> although I have never used it myself.

I actually tested their software and it was quite good, but we can't use
it because of their licensing. They even provide an API that can be
used in .NET, but their licensing methods are terrible...read the fine
print.

--
Shock|||I'd recommend you have a look at Innovartis DB Ghost at
http://www.innovartis.co.uk/

John McGrath
SQL Server DBA MCSE

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Compare data in tow users

Does anybody have a script that would be able to compare two users (oracle)
or dB (MSSQL) tables by table, assuming that schema is identical?
Thx
YuriWYou want a tool like SQL Data compare:
http://www.red-gate.com/sql/summary.htm
-- Keith
"Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message =news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> Does anybody have a script that would be able to compare two users =(oracle)
> or dB (MSSQL) tables by table, assuming that schema is identical?
> > Thx
> > YuriW
> >|||I'd rather prefer a sql script.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uG6cjlzkDHA.2964@.tk2msftngp13.phx.gbl...
You want a tool like SQL Data compare:
http://www.red-gate.com/sql/summary.htm
--
Keith
"Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> Does anybody have a script that would be able to compare two users
(oracle)
> or dB (MSSQL) tables by table, assuming that schema is identical?
> Thx
> YuriW
>|||Check the following article:
http://www.sql-server-performance.com/vg_database_comparison_sp.asp
-Sue
On Wed, 15 Oct 2003 17:13:13 GMT, "Yuri Weinstein"
<yuriw@.hotmail.com> wrote:
>I'd rather prefer a sql script.
>"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
>news:uG6cjlzkDHA.2964@.tk2msftngp13.phx.gbl...
>You want a tool like SQL Data compare:
>http://www.red-gate.com/sql/summary.htm|||in oracle, generate a script from the data dictionary, something like this:
select 'select * from user1.'||table_name|| ' minus select * from user2.' ||
table_name || ';'
from dba_tables where owner = 'USER1';
this show any rows in USER1 that don't exist in USER2 (or have slight
differences)
you could also generate a join or a more complex comparison statement,
depending on what you're looking for
either spool out the output in SQL*Plus, or but this in a PL/SQL block and
use EXECUTE IMMEDIATE to run the generated statements
note that this will not work for tables that have LONG columns
---
Mark C. Stock
www.enquery.com
(888) 512-2048
"Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> Does anybody have a script that would be able to compare two users
(oracle)
> or dB (MSSQL) tables by table, assuming that schema is identical?
> Thx
> YuriW
>|||SQL queries are not a terribly good tool for doing this type of comprison.
It would be easier to simply dump out the tables (BCP for MS SQL Server)
into two directories, one for each database. Then, you can compare the
tables -- i.e. their exported files -- using one of many file comparison
tools.
Also, there are commercial tools such as SQL Compare from Red Gate.
I do this myself with Perl scripts. If you are interested, email me and I'll
drop you a copy.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"mcstock" <mcstock@.enquery.com> wrote in message
news:KK6dnf9cybED0gWiRVn-jA@.comcast.com...
> in oracle, generate a script from the data dictionary, something like
this:
> select 'select * from user1.'||table_name|| ' minus select * from user2.'
||
> table_name || ';'
> from dba_tables where owner = 'USER1';
> this show any rows in USER1 that don't exist in USER2 (or have slight
> differences)
> you could also generate a join or a more complex comparison statement,
> depending on what you're looking for
> either spool out the output in SQL*Plus, or but this in a PL/SQL block and
> use EXECUTE IMMEDIATE to run the generated statements
> note that this will not work for tables that have LONG columns
> ---
> Mark C. Stock
> www.enquery.com
> (888) 512-2048
>
> "Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
> news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> > Does anybody have a script that would be able to compare two users
> (oracle)
> > or dB (MSSQL) tables by table, assuming that schema is identical?
> >
> > Thx
> >
> > YuriW
> >
> >
>|||thx, Mark.
"mcstock" <mcstock@.enquery.com> wrote in message
news:KK6dnf9cybED0gWiRVn-jA@.comcast.com...
> in oracle, generate a script from the data dictionary, something like
this:
> select 'select * from user1.'||table_name|| ' minus select * from user2.'
||
> table_name || ';'
> from dba_tables where owner = 'USER1';
> this show any rows in USER1 that don't exist in USER2 (or have slight
> differences)
> you could also generate a join or a more complex comparison statement,
> depending on what you're looking for
> either spool out the output in SQL*Plus, or but this in a PL/SQL block and
> use EXECUTE IMMEDIATE to run the generated statements
> note that this will not work for tables that have LONG columns
> ---
> Mark C. Stock
> www.enquery.com
> (888) 512-2048
>
> "Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
> news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> > Does anybody have a script that would be able to compare two users
> (oracle)
> > or dB (MSSQL) tables by table, assuming that schema is identical?
> >
> > Thx
> >
> > YuriW
> >
> >
>
>|||Hi Mark,
I am alomst there, but just not yet. Can you help me with this sql.
Here are exact steps:
1. set pages 5555 (trying to get all garbidge from temp.sql and it does not
do it?)
2. spool temp.sql
3. select 'select count(*) from magnetic.' || table_name ||';' from
dba_tables
where owner = 'MAGNETIC' ORDER BY table_name;
This statement works.
4. spool off
5. @.temp.sql (with an exception some errors like I said in #1).
Now if I run -
select 'select count(*) from magnetic.' || table_name ||';' from dba_tables
where owner = 'MAGNETIC' ORDER BY table_name||';'||select 'select count(*)
from
magnetic.' || table_name ||';' from dba_tables where owner = 'MAGNETIC'
ORDER B
Y table_name;
I get - ORA-00936: missing expression
Where is the error in sql?
Thanks in advance.
YuriW
"mcstock" <mcstock@.enquery.com> wrote in message
news:KK6dnf9cybED0gWiRVn-jA@.comcast.com...
> in oracle, generate a script from the data dictionary, something like
this:
> select 'select * from user1.'||table_name|| ' minus select * from user2.'
||
> table_name || ';'
> from dba_tables where owner = 'USER1';
> this show any rows in USER1 that don't exist in USER2 (or have slight
> differences)
> you could also generate a join or a more complex comparison statement,
> depending on what you're looking for
> either spool out the output in SQL*Plus, or but this in a PL/SQL block and
> use EXECUTE IMMEDIATE to run the generated statements
> note that this will not work for tables that have LONG columns
> ---
> Mark C. Stock
> www.enquery.com
> (888) 512-2048
>
> "Yuri Weinstein (HotMail)" <yuriw@.hotmail.com> wrote in message
> news:_5%ib.199$qH6.64@.newssvr29.news.prodigy.com...
> > Does anybody have a script that would be able to compare two users
> (oracle)
> > or dB (MSSQL) tables by table, assuming that schema is identical?
> >
> > Thx
> >
> > YuriW
> >
> >
>
>|||"Yuri Weinstein (HotMail)" wrote...
> Now if I run -
> select 'select count(*) from magnetic.' || table_name ||';' from
dba_tables
> where owner = 'MAGNETIC' ORDER BY table_name||';'||select 'select
count(*)
---^
> from
> magnetic.' || table_name ||';' from dba_tables where owner ='MAGNETIC'
> ORDER B
> Y table_name;
> I get - ORA-00936: missing expression
> Where is the error in sql?
>
Hi Yuri,
it seems to me that you try to construct a string of 2 selects
in a select statement. But if I'm right, you have some problems
with quotes. Your statement is quite ok until you try to put
a concatenation after the ORDER BY.
I believe you want to put the from and order by clauses into
to string.
Try to work from here.
hth,
Guido

Compare data in Tables

I am trying to determine the changes an application makes to a database.
The plan is to copy the existing schema (active) to a reference schema, run
the application and then diff the table data between the reference and the
a active schema. I have found one software vendor who has a tool to do
this, but it will only do one table at a time (interactively); I have more
then 300 and will run this a few times.

One other way of determining the changes, I guess, would be to log all sql
statements (in order), but I don't know how to do this (either).

Any pointers would be greatly appreciated.

LeoIf its changes you are looking for try running SQL Profiler against it.
Filter for where writes > 0. Another solution would to write a script to
doing the all tables comparison. Something like

Create a table with tablename, checksumbefore, checksumafter, rowsbefore,
rowsafter, numberofrowsdiff
Write a cursor of all user tables
For each table
Get count of rows with select count(*)
calc the CHECKSUM of each row and write to individual temp tables
select count(*) from checksumafter where checksum not in checksumbefore
insert/update the table

By the end of the script you should have indentified which tables change and
by how much.

"Leo" <leolist@.optushome.com.au> wrote in message
news:Xns9639579AFB3Bleolistoptushomecoma@.211.29.13 3.50...
>I am trying to determine the changes an application makes to a database.
> The plan is to copy the existing schema (active) to a reference schema,
> run
> the application and then diff the table data between the reference and the
> a active schema. I have found one software vendor who has a tool to do
> this, but it will only do one table at a time (interactively); I have more
> then 300 and will run this a few times.
> One other way of determining the changes, I guess, would be to log all sql
> statements (in order), but I don't know how to do this (either).
> Any pointers would be greatly appreciated.
> Leo|||There is a software tool that can do this for you called DB Ghost
(www.dbghost.com). Its very fast at comparing data and can be run from
the command line for a fully automated process. A single command will
do any number of tables that you desire.

It's also the cornerstone of a full change management solution for SQL
Server databases i.e. it can build, compare and synchronize the schema
AND data directly from drop/create scripts held in a source control
system.

I highly recommend you check it out.|||"Malcolm" <malcolm.leach@.innovartis.co.uk> wrote in
news:1113551521.457015.133450@.l41g2000cwc.googlegr oups.com:

> DB Ghost

DB Ghost did exactly what I needed.

Thanks for your advice

Leo

Command TExt

How do I best get the "entire" text of the commands being executed on my SQL
2000 server?
I would like to query the database tables or schema views, not run a trace.
Again, Profiler is not an option.
cmd column from master..sysprocesses does not provide, dbcc
inputbuffer(spid) returns a truncated value...
My goal is to have a vb client query sysprocesses for all "running"
processes, then for each running spid - what is the command it is executing...
TIA,
Chris
The only way is through a trace job. Sysprocesses, dbcc inputbuffer, or
even fn_get_sql() doesn't have a large enough buffer to show all large
commands.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:CD692AD8-2C0C-4475-9681-700CD03F4334@.microsoft.com...
> How do I best get the "entire" text of the commands being executed on my
> SQL
> 2000 server?
> I would like to query the database tables or schema views, not run a
> trace.
> Again, Profiler is not an option.
> cmd column from master..sysprocesses does not provide, dbcc
> inputbuffer(spid) returns a truncated value...
> My goal is to have a vb client query sysprocesses for all "running"
> processes, then for each running spid - what is the command it is
> executing...
> TIA,
> Chris
|||fn_get_sql() should be pretty close to get what you want (cached ones
anyway).
Linchi
"Danny" <someone@.nowhere.com> wrote in message
news:Co_2f.17216$at1.15123@.trnddc05...
> The only way is through a trace job. Sysprocesses, dbcc inputbuffer, or
> even fn_get_sql() doesn't have a large enough buffer to show all large
> commands.
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:CD692AD8-2C0C-4475-9681-700CD03F4334@.microsoft.com...
>
|||Hi,
SQL Profilor is ur last arm u can use.C2 trace level can do this for u
but i dont weather is it posible to trasfer that in a table.
from
Doller
Danny wrote:[vbcol=seagreen]
> The only way is through a trace job. Sysprocesses, dbcc inputbuffer, or
> even fn_get_sql() doesn't have a large enough buffer to show all large
> commands.
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:CD692AD8-2C0C-4475-9681-700CD03F4334@.microsoft.com...

Command TExt

How do I best get the "entire" text of the commands being executed on my SQL
2000 server?
I would like to query the database tables or schema views, not run a trace.
Again, Profiler is not an option.
cmd column from master..sysprocesses does not provide, dbcc
inputbuffer(spid) returns a truncated value...
My goal is to have a vb client query sysprocesses for all "running"
processes, then for each running spid - what is the command it is executing...
TIA,
ChrisThe only way is through a trace job. Sysprocesses, dbcc inputbuffer, or
even fn_get_sql() doesn't have a large enough buffer to show all large
commands.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:CD692AD8-2C0C-4475-9681-700CD03F4334@.microsoft.com...
> How do I best get the "entire" text of the commands being executed on my
> SQL
> 2000 server?
> I would like to query the database tables or schema views, not run a
> trace.
> Again, Profiler is not an option.
> cmd column from master..sysprocesses does not provide, dbcc
> inputbuffer(spid) returns a truncated value...
> My goal is to have a vb client query sysprocesses for all "running"
> processes, then for each running spid - what is the command it is
> executing...
> TIA,
> Chris|||fn_get_sql() should be pretty close to get what you want (cached ones
anyway).
Linchi
"Danny" <someone@.nowhere.com> wrote in message
news:Co_2f.17216$at1.15123@.trnddc05...
> The only way is through a trace job. Sysprocesses, dbcc inputbuffer, or
> even fn_get_sql() doesn't have a large enough buffer to show all large
> commands.
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:CD692AD8-2C0C-4475-9681-700CD03F4334@.microsoft.com...
>> How do I best get the "entire" text of the commands being executed on my
>> SQL
>> 2000 server?
>> I would like to query the database tables or schema views, not run a
>> trace.
>> Again, Profiler is not an option.
>> cmd column from master..sysprocesses does not provide, dbcc
>> inputbuffer(spid) returns a truncated value...
>> My goal is to have a vb client query sysprocesses for all "running"
>> processes, then for each running spid - what is the command it is
>> executing...
>> TIA,
>> Chris
>|||Hi,
SQL Profilor is ur last arm u can use.C2 trace level can do this for u
but i dont weather is it posible to trasfer that in a table.
from
Doller
Danny wrote:
> The only way is through a trace job. Sysprocesses, dbcc inputbuffer, or
> even fn_get_sql() doesn't have a large enough buffer to show all large
> commands.
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:CD692AD8-2C0C-4475-9681-700CD03F4334@.microsoft.com...
> >
> > How do I best get the "entire" text of the commands being executed on my
> > SQL
> > 2000 server?
> >
> > I would like to query the database tables or schema views, not run a
> > trace.
> > Again, Profiler is not an option.
> >
> > cmd column from master..sysprocesses does not provide, dbcc
> > inputbuffer(spid) returns a truncated value...
> >
> > My goal is to have a vb client query sysprocesses for all "running"
> > processes, then for each running spid - what is the command it is
> > executing...
> >
> > TIA,
> > Chris

Command TExt

How do I best get the "entire" text of the commands being executed on my SQL
2000 server?
I would like to query the database tables or schema views, not run a trace.
Again, Profiler is not an option.
cmd column from master..sysprocesses does not provide, dbcc
inputbuffer(spid) returns a truncated value...
My goal is to have a vb client query sysprocesses for all "running"
processes, then for each running spid - what is the command it is executing.
.
TIA,
ChrisThe only way is through a trace job. Sysprocesses, dbcc inputbuffer, or
even fn_get_sql() doesn't have a large enough buffer to show all large
commands.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:CD692AD8-2C0C-4475-9681-700CD03F4334@.microsoft.com...
> How do I best get the "entire" text of the commands being executed on my
> SQL
> 2000 server?
> I would like to query the database tables or schema views, not run a
> trace.
> Again, Profiler is not an option.
> cmd column from master..sysprocesses does not provide, dbcc
> inputbuffer(spid) returns a truncated value...
> My goal is to have a vb client query sysprocesses for all "running"
> processes, then for each running spid - what is the command it is
> executing...
> TIA,
> Chris|||fn_get_sql() should be pretty close to get what you want (cached ones
anyway).
Linchi
"Danny" <someone@.nowhere.com> wrote in message
news:Co_2f.17216$at1.15123@.trnddc05...
> The only way is through a trace job. Sysprocesses, dbcc inputbuffer, or
> even fn_get_sql() doesn't have a large enough buffer to show all large
> commands.
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:CD692AD8-2C0C-4475-9681-700CD03F4334@.microsoft.com...
>|||Hi,
SQL Profilor is ur last arm u can use.C2 trace level can do this for u
but i dont weather is it posible to trasfer that in a table.
from
Doller
Danny wrote:[vbcol=seagreen]
> The only way is through a trace job. Sysprocesses, dbcc inputbuffer, or
> even fn_get_sql() doesn't have a large enough buffer to show all large
> commands.
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:CD692AD8-2C0C-4475-9681-700CD03F4334@.microsoft.com...

COMException when execute SQLXMLBulkLoad3Class

I haven't been able to find any information on the error:
"Schema: multiple base for a derived type on when is not supported"
I am trying to run:
Dim bulkLoadObj As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class()
bulkLoadObj.Execute(".xsd", ".xml")
If this doesn't work I will need to work on finding a alternative way to get
an xml document into Sql Server.
Thank you for any help provided.
Here is the xsd code for when:
<xs:complexType name="COEvent">
<xs:sequence>
<xs:element name="user" type="COShortUser" minOccurs="0"/>
</xs:sequence>
<xs:attribute name="when" type="CODateOrDateTime"/>
</xs:complexType>
And then at the top of the file
<xs:simpleType name="CODateOrDateTime">
<xs:union memberTypes="xs:dateTime xs:date"/>
</xs:simpleType>
Thanks for any help.
"AshleyT" wrote:

> I haven't been able to find any information on the error:
> "Schema: multiple base for a derived type on when is not supported"
> I am trying to run:
> Dim bulkLoadObj As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class()
> bulkLoadObj.Execute(".xsd", ".xml")
> If this doesn't work I will need to work on finding a alternative way to get
> an xml document into Sql Server.
> Thank you for any help provided.
>
|||Hello,
Could you please send me the schema and the data file so i can give it a try?
Thanks,
Monica Frintu
"AshleyT" wrote:
[vbcol=seagreen]
> Here is the xsd code for when:
> <xs:complexType name="COEvent">
> <xs:sequence>
> <xs:element name="user" type="COShortUser" minOccurs="0"/>
> </xs:sequence>
> <xs:attribute name="when" type="CODateOrDateTime"/>
> </xs:complexType>
> And then at the top of the file
> <xs:simpleType name="CODateOrDateTime">
> <xs:union memberTypes="xs:dateTime xs:date"/>
> </xs:simpleType>
> Thanks for any help.
> "AshleyT" wrote:

COMException when execute SQLXMLBulkLoad3Class

I haven't been able to find any information on the error:
"Schema: multiple base for a derived type on when is not supported"
I am trying to run:
Dim bulkLoadObj As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class()
bulkLoadObj.Execute(".xsd", ".xml")
If this doesn't work I will need to work on finding a alternative way to get
an xml document into Sql Server.
Thank you for any help provided.Here is the xsd code for when:
<xs:complexType name="COEvent">
<xs:sequence>
<xs:element name="user" type="COShortUser" minOccurs="0"/>
</xs:sequence>
<xs:attribute name="when" type="CODateOrDateTime"/>
</xs:complexType>
And then at the top of the file
<xs:simpleType name="CODateOrDateTime">
<xs:union memberTypes="xs:dateTime xs:date"/>
</xs:simpleType>
Thanks for any help.
"AshleyT" wrote:

> I haven't been able to find any information on the error:
> "Schema: multiple base for a derived type on when is not supported"
> I am trying to run:
> Dim bulkLoadObj As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class()
> bulkLoadObj.Execute(".xsd", ".xml")
> If this doesn't work I will need to work on finding a alternative way to g
et
> an xml document into Sql Server.
> Thank you for any help provided.
>|||Hello,
Could you please send me the schema and the data file so i can give it a try
?
Thanks,
Monica Frintu
"AshleyT" wrote:
> Here is the xsd code for when:
> <xs:complexType name="COEvent">
> <xs:sequence>
> <xs:element name="user" type="COShortUser" minOccurs="0"/>
> </xs:sequence>
> <xs:attribute name="when" type="CODateOrDateTime"/>
> </xs:complexType>
> And then at the top of the file
> <xs:simpleType name="CODateOrDateTime">
> <xs:union memberTypes="xs:dateTime xs:date"/>
> </xs:simpleType>
> Thanks for any help.
> "AshleyT" wrote:
>

Combining XQuery and data paging in SQL Server 2005

Folks,
I have a table in SQL Server 2005 that contains a row of type "XML" (for now
untyped - no schema behind it).
I can easily query that table and retrieve values from the XML - way !
I can also easily query that table and use the data paging mechanism based
on the ROW_NUMBER() function - way , too!
But I can't seem to combine the two...
Imagine I have books stored in my XML column:
<books>
<book id='123'>
<author>Smith</author>
<yearpublished>1999</yearpublished>
</book>
<book id='177'>
<author>Black</author>
<yearpublished>2002</yearpublished>
</book>
... (lots more books) .....
</books>
To query the table, I use something like:
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') as 'Author',
BooksXML.value('(/book/yearpublished)[1]', 'int') as 'YearPublished'
FROM BooksTable
Works fine. Now what I'd like to do is add a ROW_NUMBER() function over the
e.g. "YearPublished" attribute (stored in the XML) to get my books by year
published, in batches of 10 or whatever:
WITH BooksList as
(
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') as 'Author',
BooksXML.value('(/book/yearpublished)[1]', 'int') as 'YearPublished',
ROW_NUMBER() OVER(ORDER BY YearPublished) AS 'rownum'
FROM BooksTable
)
SELECT * FROM BooksList
WHERE rownum BETWEEN 11 AND 20
Trouble is - this won't work, since MgmtStudio complains it doesn't know
about the "YearPublished" column.....
What am I missing? Can I still achieve this goal somehow?
Any hints are most welcome !!
Marc
(mscheuner -* AT *- gmail.com)Untested but try this
WITH BooksList as
(
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') as 'Author',
BooksXML.value('(/book/yearpublished)[1]', 'int') as
'YearPublished',
ROW_NUMBER() OVER(ORDER BY
BooksXML.value('(/book/yearpublished)[1]', 'int')) AS 'rownum'
FROM BooksTable
)
SELECT * FROM BooksList
WHERE rownum BETWEEN 11 AND 20|||... or :-)
WITH x AS
(
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') AS Author,
BooksXML.value('(/book/yearpublished)[1]', 'int') AS YearPublished,
FROM BooksTable
), BooksList AS (
SELECT
*
,ROW_NUMBER() OVER (ORDER BY YearPublished) AS RowNum
FROM x
)
SELECT * FROM BooksList
WHERE rownum BETWEEN 11 AND 20
Also, please consider not using single quotes (') for naming aliases, you
should use double quotes (") as that is the ANSI standard and using single
quotes will be debrecated.
HTH
/ Tobias