Posts Tagged ‘SQL’

Virtual (SQL)server impaired by residual snapshot after Veeam backup

Written by Ingmar Verheij on June 11th, 2012. Posted in Performance testing

Recently I had to troubleshoot a SQL server that performed nightly batch jobs for a management information system. Under normal conditions this required 6.5  hours but this was suddenly increased to 11.5 hours. An increase of 75%!

Because of this delay the information wasn’t presented on time with a lot of implications.  Several departments where asked what has changed in the past days, of course the answer was “nothing”.

Reverse and forward engineering databases in Visual Studio 2010

Written by Ingmar Verheij on January 5th, 2012. Posted in Visual Studio

In a previous post I’ve described how you can reverse and forward engineer a database from a Microsoft SQL server to (and from Microsoft Office Visio 2010. But what if you’re using Typed Datasets in Microsoft Visual Studio?

Same as with Visio, reverse engineering an existing database from a SQL database is built-in and therefore fairly easy. So a best-practice is to design the database model in Visio, forward engineer it to a SQL database and import it in Visual Studio.

If you want to forward engineer a Microsoft ADO.NET compatible DataSet Schema File (XSD) to a (Microsoft SQL) database you can use the XSD2DB tool created by Alexsis Smirnov.

In this blog post I will demonstrate how to reverse engineer a database from Microsoft SQL server, and how to forward engineer a XSD back to a Microsoft SQL server.

Reverse and forward engineering databases in Visio 2010

Written by Ingmar Verheij on January 5th, 2012. Posted in Microsoft SQL

A great built-in feature in Microsoft Office Visio 2010 is the ‘Reverse Engineer’ feature. With this feature you can create a database model from an existing database or a Microsoft Excel worksheet. This makes it very easy to document the structure of a database, redistribute or publish It on Microsoft SharePoint.

Unfortunately Microsoft has removed the ability to forward engineer the database model to a database, which did exist in Microsoft Visio 2003 Enterprise Architect-edition.

Fortunately Alberto Ferrari created an Visio 2010 plugin that can forward engineer an data model to a T-SQL script. The Visio Forward Engineer project is on CodePlex and can be found here.

In this blog post I will demonstrate how to reverse engineer a database from Microsoft SQL server, and how to forward engineer a database model diagram back to a Microsoft SQL server.

Remove all tables from database

Written by Ingmar Verheij on June 10th, 2011. Posted in Microsoft SQL

SQL logo

During the setup of a LoadTest with the DeNamiK LoadGen I created loads of tables in a SQL database. The content of the tables was irrelevant, pure for testing purposes, and needed to be cleaned.

Deleting them all by hand, one by one, would be tremendous job. As an IT guy I dislike repeating jobs, so I searched for an automated solution.

Since I have limited permissions I don’t want to delete the database and recreate it. This would require more permissions than available, requires me to reconfigure the permissions (etc) and would raise questions by the DBA’s.

There is an undocumented stored procedure called ‘sp_MSforeachtable’. This stored procedure executes a command for each table in the database, for instance ‘DROP TABLE’.

The following command will delete all tables in the active database (use with caution!)

Exporting data from SQL to XML

Written by Ingmar Verheij on May 8th, 2010. Posted in Batch

Exporting data from a SQL database to a XML file is very easy, if you know how to.
Using the bcp command (bulk copy program), a tool supplied by Microsoft, you can easily export all the data you want.
– Create a new text file and type the following text:

<root>
</root>

– Save the text file with a .xml extension, for instance data.xml;
– Open a new Command Prompt (as Administrator in Vista/Win7/2008 R2);
– Execute the following command : bcp.exe “SELECT * FROM Table AS XML RAW” queryout data.xml -c -r -t -T.
If you want to script the process with batch (we’re a big fan of batch files) you can use the following script:

@Echo Off

REM Create an empty XML file
ECHO ^<root^> >data.xml
ECHO ^</root^> >>data.xml
REM Export the data using BCP
bcp.exe "SELECT * FROM Table AS XML RAW" queryout data.xml -c -r -t -T.

Ingmar Verheij & Daniel Nikolic

Donate