Compairing updated data base.

I do not know SQL but learning fast and furious.

I am programming an agent and working with a group of existing databases.
I would like to able to compare the database before and after an update.
The testing databases are relatively small.
I have no problem programming some compare but how do I go about it.

Should I do this in SQL duplicating the database.
I would be happy to write some SQL and dump the databases and do the compare
externally.

I would appreciate any suggestion.

AndreIf you just want to compare data between similar tables you can do so
with a JOIN:

SELECT COALESCE(A.key_col, B.key_col),
COALESCE(A.col1, B.col1), COALESCE(A.col2, B.col2), ...
FROM TableA AS A
FULL JOIN TableB AS B
ON A.key_col = B.key_col
WHERE COALESCE(A.col1,'')<>COALESCE(A.col1,'')
AND COALESCE(A.col2,'')<>COALESCE(A.col2,'')

assuming key_col is the primary key in both tables.

--
David Portas
SQL Server MVP
--|||What I would like to do is probably
1) back up the data base
2) restore it under a different name
-- run my agent
3) create a difference database ( a new database with any table which is
different)

Step 1 and 2 are easy so can be ignored
now step 3
I can create a new temporary database but how can I fill the tables in this
database using SQL

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1111586542.235632.69940@.o13g2000cwo.googlegro ups.com...
> If you just want to compare data between similar tables you can do so
> with a JOIN:
> SELECT COALESCE(A.key_col, B.key_col),
> COALESCE(A.col1, B.col1), COALESCE(A.col2, B.col2), ...
> FROM TableA AS A
> FULL JOIN TableB AS B
> ON A.key_col = B.key_col
> WHERE COALESCE(A.col1,'')<>COALESCE(A.col1,'')
> AND COALESCE(A.col2,'')<>COALESCE(A.col2,'')
> assuming key_col is the primary key in both tables.
> --
> David Portas
> SQL Server MVP
> --|||Andre Arpin (arpin@.kingston.net) writes:
> What I would like to do is probably
> 1) back up the data base
> 2) restore it under a different name
> -- run my agent
> 3) create a difference database ( a new database with any table which is
> different)
> Step 1 and 2 are easy so can be ignored
> now step 3
> I can create a new temporary database but how can I fill the tables in
> this database using SQL

Red Gate has products for this, check out http://www.red-gate.com/.

If you would like to roll your own, you would have to write a query
like the one that David showed you for each table.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You can easily populate a table from another in a different database:

INSERT INTO DatabaseA.dbo.TableA (col1, col2, ...)
SELECT col1, col2, ...
FROM DatabaseB.dbo.TableB
WHERE ... ?

--
David Portas
SQL Server MVP
--

compacting / to repair base of data sql, using the msde

I am developing a project of the access for a friend, more I am with the
following doubts:
Does some exist it sorts things out of compacting / to repair base of data
sql, using the msde or the adp.?
MSDE databases do not require compacting. When updates or other changes are
made, the space freed when a row is deleted is reused automatically. For the
most part, MSDE/SQL Server databases are not subject to the file corruption
problems that are associated (from time-to-time ) with JET databases. There
are utilities (DBCC for one) that can be used to perform maintenance on the
database, but it's unusual to have to resort to these for smaller databases
as typically implemented with MSDE.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:%23ZUa0OKMEHA.2456@.TK2MSFTNGP12.phx.gbl...
> I am developing a project of the access for a friend, more I am with the
> following doubts:
> Does some exist it sorts things out of compacting / to repair base of data
> sql, using the msde or the adp.?
>

communication btwn website abd emulator

hi there,

I have developed a web base and emulator using VS 2005.the web site is connected to SQL server database 2005 and the emulator (smart device) is connected to the SQL Mobile Server. I have published, web sync and subscribe the SQL Server database to the SQL Server Mobile. My problem now is to display any immediate chages that take place in the SQL Server database on the emulator. How do I do that.. PLSSSS HELP ME....

regards,

Dilashani

Moving to SQL Server Compact Edition forum where it has got better chances of being answered.

-Thanks,

Mohit

command line parameters are invalid

I have a package that let me to import data from a excel book to a Sql server data base. When I try to run this package like a step into a SQL server Job it show me the next error.

"The command line parameters are invalid. The step failed."

the "command line" looks like this

/FILE "C:\Project\Package.dtsx" /CONNECTION ConexionExcel;"Provider=Microsoft.Jet.OLEDB.4.0;Da ta Source=;Extended Properties=""EXCEL 8.0;HDR=YES"";" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

and in my excel conexion is:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties="EXCEL 8.0;HDR=YES";

When i run the "Command Line" in the "Command window" the error is more expecific

"EXCEL 8.0;HDR=YES;" is not valid

So I chanded the "Command Line" in order to run it in de command window(look the double quote in the excel properties)

/FILE "C:\Project\Package.dtsx" /CONNECTION ConexionExcel;"Provider=Microsoft.Jet.OLEDB.4.0;Da ta Source=;Extended Properties="EXCEL 8.0;HDR=YES";" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

then the package RUN, but when i tried to do the same thing in the

sql wizzard, it just dont work and it lost the threat from de package and the errors says this time:

Couldn't find the package

Some one who knows the answer or has any idea to helpme please?

thanks

Use in the Import/Export wizard in SQL.

Adamus

|||According to current Books Online, the double quote is the correct method to escape the quotes inside the parameter. Are you running SP1? I believe they made some changes to the parsing of command lines in SP1, so perhaps that may help.|||

I have a windows 2003 server and the SP1 is only for windows vista, I have installed this version but, i don't know if it has some thing to do with that

|||

I have found the soluction,

I you have seen the command line, the excel connection doesn't have a Data Source because it change in a bucle, I modified connection string parameter in the package in oder to recive a variable with all the connection string, then when i programming my step in the job i don't need to especified the excel connection string.

Thanks Cesar for the aswer

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:
>