Posts

Exception has been thrown by the target of an invocation (mscorlib)

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

 

Read more

SQL Error: User group or role already exists in the current database

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)

Read more

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

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

Read more

Reverse and forward engineering databases in Visual Studio 2010

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.

Read more

Reverse and forward engineering databases in Visio 2010

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.

Read more

TCP Error 10061 when Login into ChangeAuditor

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

 

Read more

Deleting scheduled Altiris tasks from SQL

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:

ALTER procedure [dbo].[del_event_schedule]
   @schedule_id         int
as
   begin transaction

   delete from event_schedule where schedule_id = @schedule_id

   if (@@error != 0)
      rollback transaction
   else
      commit transaction

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

 

Read more

Apply IP Configuration from a Database

I am currently deploying 64 Citrix XenApp servers with Altiris. The deployment consists of an OS Image, OS Configuration and finally Citrix XenApp and Applications.

In the OS Configuration part the IP configuration needs to be applied and I decided to do this with a database.

The database consists of 2 tables; one table with the per host settings and one table with the global settings (such as DNS).

In the Altiris job both tables are read from an embedded VBScript and assigned to the NIC.

Database configuration

I created a database (SQL Server) called IPManagement with 2 tables:

image

 

Read more

Exporting data from SQL to XML

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

Firewall 2008 R2 blocking SQL traffic by default

After spending some time trying to install Microsoft OpsManager and Virtual Machine Manager I got stuck after the installations try to connect the SQL database which was installed on another server in the domain.

It’s really important to open some firewall ports when SQL is installed on a Windows 2008 R2 host.

Create a script on the SQL server and execute it, after that all works like a charm 🙂

@echo =========  SQL Server Ports  ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 “SQLServer”
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 “SQL Admin Connection”
@echo Enabling conventional SQL Server Service Broker port 4022 
netsh firewall set portopening TCP 4022 “SQL Service Broker”
@echo Enabling Transact-SQL Debugger/RPC port 135
netsh firewall set portopening TCP 135 “SQL Debugger/RPC”
@echo =========  Analysis Services Ports  ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 “Analysis Services”
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 “SQL Browser”
@echo =========  Misc Applications  ==============
@echo Enabling HTTP port 80
netsh firewall set portopening TCP 80 “HTTP”
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 “SSL”
@echo Enabling port for SQL Server Browser Service’s ‘Browse’ Button
netsh firewall set portopening UDP 1434 “SQL Browser”
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE

Also see this KB article @ Microsoft support.

Daniel Nikolic