Nadine's Posts : SQL Server 2005

Error Reporting Service - Report cannot generate

Nadine Storandt — Wed, 24 Jan 2007 13:32:00 GMT

Hey guys,

i have found a nice bug in Reporting Services.

If you have configured a DataSource and add a field from another DataSet to the Data Source and try to preview your Report in Visual Studio you get an exception that the report cannot generate.

Then Visual Studio make a restart.

The Error in the Event Viewer looks as follows:

Event Id: 1000

Event Source: Microsoft Visual Studio

Faulting application devenv.exe, version 8.0.50727.762, stamp 45716759, faulting module kernel32.dll, version 5.1.2600.2945, stamp 44ab9a84, debug? 0, fault address 0x00012a5b.

I don't have a resolution for this problem yet, but if I get one I will post it here later.

Or if someone knows the problem i would be glad when they sent me the resolution for this problem.

How to Insert or Update records in SSIS DataFlow

Nadine Storandt — Thu, 07 Dec 2006 10:53:00 GMT

If you import Data from a specified Source periodically in an Integration Service Project and you need to update existing data in the SQL Destination the best workaround for this is as following:

First define a Source in the data flow e.g. Ole DB Source, make sure that your Columns does not contain NULL Values, this you can do with the Derived Column Shape then add a Script Component to the Data Flow - now your Data Flow should look like the following picture:

In the Script Component you have to define the input source and the output source. Right click on the Script Component and click 'Edit', then go to 'Inputs and Outputs' you can rename the Input and Output Name. In the Output you should set the Exclusion Group to 1 and the SynchronousInputID to the ID of the Input in this case the ID is '243' per default the first output has the ID. In the following Picture you see the configuration.

The next step:

Go to the option Script and click the button Design Script, now Visual Studio will be open where you can define your source code, the default language is VB:

Here is a little code snippet for a Insert Update procedure:

Dim reader As SqlClient.SqlDataReader

sqlConn = New SqlClient.SqlConnection("Data Source=Servername;Initial Catalog=Databse;Integrated Security=True")

sqlConn.Open()

sqlCmd = New SqlClient.SqlCommand("SELECT * FROM Tabelle WHERE(ID = @ID)", sqlConn)

sqlParam = New SqlClient.SqlParameter("@ID", SqlDbType.NVarChar, 50)

sqlCmd.Parameters.Add(sqlParam)

sqlCmd.Parameters("@ID").Value = Row.Bestellnummer

reader = sqlCmd.ExecuteReader()

If reader.Read() Then

'do all field comparisons here to determine if

' the record changed since the last ETL.

If (reader("AmountOfHardware").ToString() <> Row.Summe) Then

reader.Close()

sqlCmd = New SqlClient.SqlCommand("Update TBestellmassnahme Set AmountOfHardware = @Sum Where ID = @ID ", sqlConn)

sqlParam = New SqlClient.SqlParameter("@Sum", SqlDbType.NVarChar, 50)

sqlCmd.Parameters.Add(sqlParam)

sqlCmd.Parameters("@Sum").Value = Row.Summe

sqlParam = New SqlClient.SqlParameter("@ID", SqlDbType.NVarChar, 50)

sqlCmd.Parameters.Add(sqlParam)

sqlCmd.Parameters("@ID").Value = Row.Bestellnummer

sqlCmd.ExecuteNonQuery()

Else

End If

Else

'if the reader contains no data the row will be redirect to 'the output source which could be the Insert Statement

Row.DirectRowToOutputInsert()

End If

reader.Close()

sqlConn.Close()

After you have insert the script you should add an OLE DB Command Shape to the Output of the Script Component. In this Command Shape you could define the Insert Statement as you need.

Error: There is already an open DataReader associated with this Command which must be closed first

Nadine Storandt — Thu, 07 Dec 2006 10:40:00 GMT

If you receive the following error message There is already an open DataReader associated with this Command which must be closed first

and you are sure that all dataReader are closed the following workaround can help you.

The MARS default settings has changed at release RC1, so multiple Result Sets are off. To turn it on only add the following string to your Connection String.

MultipleActiveResultSets=True

SQL 2005 SSIS Excel Source Insert NULL Values for all Records

Nadine Storandt — Tue, 05 Dec 2006 11:11:00 GMT

Hi,

I'm just getting confused today, because I tried to import Data from a Excel Source, but one column contains only NULL Values, but in the Excel Source there are a few entries for this column.

So the problem was SSIS Excel Source only look at the first 8 Rows if they contain the same value SSIS decide to use the same data type for all entries.

To fix this problem you have to add the following property to the Connection String of your Excel Connection Manager:

Excel 8.0;HDR=YES;IMEX=1

The property IMEX = 1 specifies that all datatypes should be import which are defined in the Excel Source.

SQL 2005 SP1 Problem

Nadine Storandt — Thu, 09 Nov 2006 13:27:00 GMT

I run today in many problems. I have installed the Service Pack 1 for SQL Server 2005.

The first problem was the Service Pack was not installed correct in Event Log I received the following error:

Installation Failure: Windows failed to install the following update with error 0x80070643: Microsoft SQL Server 2005 Service Pack 1 (KB 913090).

This error append to the next error I receive in the EventLog:

The SQL Server (SQL2005) service terminated with service-specific error 17058 (0x42A2).

The SQL Server 2005 Service was terminated and I can't start him again.

The resolution of this problem is as follows:

1. Uninstall all SQL Servers 2000/2005

2. Reinstall SQL 2005 first then run the Service Pack 1

3. Reinstall SQL 2000