dotnet-developer.de

I have a MS SQL Table containing user data, e.g. Username and ID. This list should be compared with another list which is available as XML Document. Fortunately MS SQL 2005 is able to process XML so I searched around and found a post from Divya Agrawal on MSSQLTips.com. In Divyas tip 2 XML files are compared, so I modified it to compare XML and SQL row.

Read the rest of this entry

I have a MS SQL Table containing user data, e.g. Username and ID. This list should be compared with another list which is available as XML Document. Fortunately MS SQL 2005 is able to process XML so I searched around and found a post from Divya Agrawal on MSSQLTips.com. In Divyas tip 2 XML files are compared, so I modified it to compare XML and SQL row. For this example, my SQL Table ‘Users’ has just 2 rows: “Name” (varchar(100)) and “ID” (int). My XML File looks like this: 1: <root> 2: <data> 3: <name>Thorsten Test</name> 4: <id>123</id> 5: </data> 6: <data> 7: <name>Peter Pommes</name> 8: <id>124</id> 9: </data> 10: <data> 11: <name>Wiki Pedia</name> 12: <id>125</id> 13: </data> 14: </root> .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } So I need to check whether ‘root/data/id’ from XML file is already in table column ‘ID’. If not, the name and the ID should be returned. This could be done with this little query: 1: CREATE PROCEDURE [dbo].[FindNewUsers] 2: @Userlist XML 3: AS 4: BEGIN 5: SET NOCOUNT ON; 6: 7: Select N.value('name[1]', 'varchar(100)') as Username, 8: N.value('id[1]', 'int') as ID 9: from @Userlist.nodes('/root/data') as X(N) 10: where N.value('id[1]', 'int') Not in (select ID from Users) 11: END .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } That’s it!

While migrating a SQL-Database from stage to live I searched for an easy way to identify all changes I made. Of course I wrote them all down in my update log, but it should not be too complicated for a tool to automatically compare 2 databases. And yes, there is at least one tool, and best of all: It’s free!! (Ah no, best of all is that it’s working fine, but it’s also great that it’s free….)

Read the rest of this entry

While migrating a SQL-Database from stage to live I searched for an easy way to identify all changes I made. Of course I wrote them all down in my update log, but it should not be too complicated for a tool to automatically compare 2 databases. And yes, there is at least one tool, and best of all: It’s free!! (Ah no, best of all is that it’s working fine, but it’s also great that it’s free….) Starinix Corporation has released a tool called ‘Starinix Database Compare’. Current version is 2.0.14. The tool supports lots of different sources, like MySQL, MS SQL and MS Access. I’ve only tested it with MS SQL 2000 and MS SQL 2005 and this works fine. If any modifications found the tool uses ExamDiff to show the difference in details. Additionally you could also create a snapshot. And if you like the tool don’t forget to donate. Make sure you also check out DbDiff from Codeplex. And I just noticed that there is also a Wikipedia Article about SQL Comparison tools.

As I’m no SQL DBA I wondered how to get details about my current databases, especially the size, because suddenly my database size increased a lot and I had no initial idea why. Microsoft SQL Server Management Studio (for MS SQL 2005) does not seem to have an easy solution for this.  So searching around I found a freeware tool from SQLDBTools.com called SQLDBSize.

Additionally a colleague informed me about a script from Mohit Nayyar he found at SQLServerCentral.com :

Read the rest of this entry

As I’m no SQL DBA I wondered how to get details about my current databases, especially the size, because suddenly my database size increased a lot and I had no initial idea why. Microsoft SQL Server Management Studio (for MS SQL 2005) does not seem to have an easy solution for this.  So searching around I found a freeware tool from SQLDBTools.com called SQLDBSize.   Additionally a colleague informed me about a script from Mohit Nayyar he found at SQLServerCentral.com : CREATE TABLE #temp (TableName NVARCHAR (128), RowsCnt VARCHAR (11), ReservedSpace VARCHAR(18), DataSpace VARCHAR(18), CombinedIndexSpace VARCHAR(18), UnusedSpace VARCHAR(18)) EXEC sp_MSforeachtable 'INSERT INTO #temp (TableName, RowsCnt, ReservedSpace, DataSpace, CombinedIndexSpace, UnusedSpace) EXEC sp_spaceused ''?'', FALSE' SELECT TableName, RowsCnt, ReservedSpace, DataSpace, CombinedIndexSpace, UnusedSpace FROM #temp ORDER BY TableName DROP TABLE #temp The script works fine with SQL2005, but it uses an undocumented SP (sp_MSforeachtable) so there is no guarantee that it will be available in future releases of MS SQL Server) Technorati Tags: MS SQL Table size