Aug 22

Today, I want to create an SQL Maintenance Plan in SQL 2005. In the folder three (under the SQL Server Instance name) I open the folder Management and right click on the folder Maintenance Plan and and select the option New Maintenance Plan.

image

After doing so I receive the following SQL error message:

image

 

Continue reading »

Jul 31

During an SQL database migration (from an physical to an virtual SQL server) I ran into the following problem.
I first created a SQL backup from the application database , let’s call the database app1_db, on the physical SQL (2005) server. On the next step I’ve created a new database

user (app1_dbuser) for this database on the virtual SQL (2005) server.
Next up I’ve restored the database on the virtual SQL server. When I tried to map the newly created user to app1_db, the database produced the following SQL error:

User, group or role ‘app1_dbuser’ already exists in the current database (Microsoft SQL Server, Error 15023)

Continue reading »

Jun 11

Author : Ingmar Verheij

Understanding-snapshot-management-te[2]

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”.

Continue reading »

May 30

I hit an exception when adding auto-generated commands from a CommandBuilder object to a DataAdapter object. Specifically, when assigning the return value of the CommandBuilder.GetDeleteCommand() method to the DeleteCommand property of the DataAdapter object. The exception message reads: “Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.”. The exception may also be thrown when adding a command to the UpdateCommand property of a DataAdapter object.

The exception message is not very helpful in finding the problem and fixing it. After some research I found that the reason this exception is thrown is because both the DeleteCommand and the UpdateCommand require a primary key in the table they are meant to modify. When the table does not have one, the DeleteCommand and UpdateCommand cannot accurately identify which row to delete or update, and they will throw the exception mentioned above.

The solution is simply to add a primary key to the table you want the DataAdapter object to connect to.

Jan 24

imagesThe available official help in using a Sql Server Compact edition in a .Net program is not very extensive. In fact, it does not mention some elemental aspects that you need in order to create a database. This post will show you how to create a new database or connect to an existing one.

I assume you have already downloaded and installed the latest version of the SQL Server Compact edition, and have added a reference to it in your VB.Net project. Also, that you have imported the right namespace (System.Data.SqlServerCe).

Continue reading »

Jan 20

For a customer I placed a  SQL server, which has a disk size of about 900 MB, on “cheap” iSCSI SAN with a dual 10 GB link. This was not the only Virtual Machine on the iSCSI hardware there where about 13 LUN defined with each about 6 virtual machines. I “doubted” if”  this machine was a candidate for this particular iSCSI solution but measurements of the environment showed me that it was possible.

It seems to went well but after a few days I got complaining users and i saw this graph (see below) ! It seems that something has changed on January the 17th. From that day the SQL server is bean heavily loaded with import jobs that will run every night. Because the customer want to have the latest production data for testing and training the virtual machine characteristic changed dramatically du to this adjustment! Veeam monitor write latency

Continue reading »

Jan 05

Author : Ingmar Verheij

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.

Continue reading »

Jan 05

Author : Ingmar Verheij

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.

Continue reading »

Jan 02

When installing ChangeAuditor form Quest (a next, next finish installation) I received an error when I tried to logon with the client but I did not get any error during or after the installation.  I got the following error:

Info","Could not connect to net.tcp://<FQDN>.local:49309/Service. The connection attempt lasted for a time span of 00:00:02.0779654. TCP error code 10061: No connection could be made because the target machine actively refused it <IPadress>:49309. "

Quest ChangeAuditor TCP error code 10061

 

Continue reading »

May 03

I needed to delete around 50 scheduled tasks from several machines in Altiris because something went wrong in on of the first jobs.

It would have better if the jobs were configured to fail on error and not continue but they weren’t.

Deleting the jobs from the Altiris console is very, very, slow. First the console asks for confirmation (after showing the hourglass for a long time):

image

Then the actual delete can take a few minutes and then the next server and so on.

I decided to delete the tasks directly from SQL.

I know this is not preferred but I think in the end it’s safe enough because I found a stored procedure called del_event_schedule which looks like this:

So al it does is a (transacted) delete from the table.

 

Continue reading »