André Krijnen

So after some time Microsoft just released there first Service Pack for SQL Server 2008 R2. This package contains alot of improvements regarding to performances for Analysis Services.

You can download SP1 here: http://www.microsoft.com/download/en/details.aspx?id=20302

Also when you’ve running SharePoint Server 2010 with Reporting Services integrated you should update your Reporting Services to the latest Service Pack. Also an important notice to this, you should also download the SQL Server 2008 R2 SP1 Feature Pack to accomplish some other improvements.

You can download SP1 Feature Packs here: http://www.microsoft.com/download/en/details.aspx?id=26728

When you’ve downloaded the Feature Packs you should also install the following components to all SharePoint Servers:

• rsSharePoint.msi
• sqlncli.msi
• SQLSERVER2008_ASADOMD10.msi

The first one is the SQL Server 2008 r2 Reporting Services Add-In for SharePoint Server 2010

The second is the SQL Server Client 2008 which you also have to install when you install the prerequisites for SharePoint Server 2010.

The third is also a component which is used in the prerequisite for SharePoint Server 2010.

When I started to work with SharePoint 2010 I’ve noticed that there are alot of issues regarding the User Profile Synchronization. Alot of people mentioned the following event viewer problems:

Microsoft.ResourceManagement.ServiceHealthSource
Event ID: 22
Level: Error

The Forefront Identity Manager Service cannot connect to the SQL Database Server.

The SQL Server could not be contacted. The connection failure may be due to a network failure, firewall configuration error, or other connection issue. Additionally, the SQL Server connection information could be configured incorrectly.

Verify that the SQL Server is reachable from the Forefront Identity Manager Service computer. Ensure that SQL Server is running, that the network connection is active, and that the firewall is configured properly. Last, verify the connection information has been configured properly. This configuration is stored in the Windows Registry.

ILM Web Service Configuration
Event ID: 234
Level: Warning

ILM Certificate could not be created: netsh http error:netsh http add urlacl url=http://+:5726/ user=MSSDEVIA\MSSFARM sddl=D:(A;;GA;;;S-1-5-21-3647457175-3930976156-3381717532-1106)

ForeFront Identity Manager
Event ID: 3
Level: Error

.Net SqlClient Data Provider: System.Data.SqlClient.SqlException: Cannot open database “User Profile Service Application_SyncDB_1d4b979635654411b18ce834c5c0a76a” requested by the login. The login failed.
Login failed for user ‘MSSDEVIA\mssfarm’.
at Microsoft.ResourceManagement.Data.Exception.DataAccessExceptionManager.ThrowException(SqlException innerException)
at Microsoft.ResourceManagement.Data.DatabaseConnection.Open(SqlConnection connection)
at Microsoft.ResourceManagement.Data.DatabaseConnection.Open(DataStore store)
at Microsoft.ResourceManagement.Data.TransactionAndConnectionScope..ctor(Boolean createTransaction, IsolationLevel isolationLevel, DataStore dataStore)
at Microsoft.ResourceManagement.Data.TransactionAndConnectionScope..ctor(Boolean createTransaction)
at Microsoft.ResourceManagement.Data.DataAccess.RegisterService(String hostName)
at Microsoft.ResourceManagement.Workflow.Hosting.HostActivator.RegisterService(String hostName)
at Microsoft.ResourceManagement.Workflow.Hosting.HostActivator.Initialize()
at Microsoft.ResourceManagement.WebServices.ResourceManagementServiceHostFactory.CreateServiceHost(String constructorString, Uri[] baseAddresses)
at Microsoft.ResourceManagement.WindowsHostService.OnStart(String[] args)

ForeFront Identity Manager
Event ID: 3
Level: Error

.Net SqlClient Data Provider: System.Data.SqlClient.SqlException: HostId is not registered
at Microsoft.ResourceManagement.Data.Exception.DataAccessExceptionManager.ThrowException(SqlException innerException)
at Microsoft.ResourceManagement.Data.DataAccess.RetrieveWorkflowDataForHostActivator(Int16 hostId, Int16 pingIntervalSecs, Int32 activeHostedWorkflowDefinitionsSequenceNumber, Int16 workflowControlMessagesMaxPerMinute, Int16 requestRecoveryMaxPerMinute, Int16 requestCleanupMaxPerMinute, Boolean runRequestRecoveryScan, Boolean& doPolicyApplicationDispatch, ReadOnlyCollection`1& activeHostedWorkflowDefinitions, ReadOnlyCollection`1& workflowControlMessages, List`1& requestsToRedispatch)
at Microsoft.ResourceManagement.Workflow.Hosting.HostActivator.RetrieveWorkflowDataForHostActivator()
at Microsoft.ResourceManagement.Workflow.Hosting.HostActivator.ActivateHosts(Object source, ElapsedEventArgs e)

Of course I’ve watched the different blog posts from different MVP’s and others. But there still seems to be alot of different issues regarding the UPS of SharePoint 2010. Yet, I’ve found out that there are more issues as they speak of.
In my case the scenario was alot different then rights on the local machine or on the domain. The users had all the rights to perform Replicate Directory Changes on the domain, and yet I’ve had these errors.

I’d Visual Studio 2010 installed and it came up with the Jit-In-Time debugger with errors.

An unhandled exception (‘System.ServiceModel.ProtocolException’) occurred in OWSTIMER.exe [5630]

Application Error
Event ID: 1000
Level: Error

Faulting Application name: OWSTIMER.exe, version: 14.0.4762.1000, time stamp: 0x4bad920c
Faulting module name: KERNELBASE.dll, version: 6.1.7600.16385, time stamp: 04xa5bdfe0
Exception code: 0xe0434f4d
Fault offset: 0x000000000aa7d
Faulting Process id: 0xa58
Faulting application start time: 0x01cb2cacfed18e83
Faulting application path: C:\Program Files\Common Files\Microsoft shared\Web Server Extensions\14\Bin\OWSTIMER.exe
Faulting module path: C:\Windows\System32\KERNELBASE.dll
Report id: f3b92d5f-98a3-11df-b4fd-000c29240fea

So, I’ve opened a new instance of Visual Studio 2010 Debugger, and it came up that the internal Proxy Server needed authentication. I did get a 407: Proxy Authentication Required.

If you think you’ve done everything to the different blog posts you’ve writtend, and you can’t find any issues, it can be that something else blocking your way to use the different SharePoint Application Services.

MS is not helping you with these kind of issues, but when you need some help, try to use Visual Studio on your application server, or server that is going to host the UPS service to identify which errors it comes up with.

If you can’t use Visual Studio because it’s a production environment, try to get a test environment in the same environment your production environment is in, and install there Visual Studio.

Today I was doing some tasks on SQL Server 2005 with SP2. I had some problems with adding jobs and getting the following error message:

Unable to cast object of type ‘Microsoft.SqlServer.Management.Smo.SimpleObjectKey’ to type ‘Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey’.

The issue was using client tools without SP2. You can’t add jobs to a SQL Server 2005 with SP2 with SQL Server 2005 RTM Client Tools.

Timestamp in SQL Server:

1) The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.

2) It further states Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.

For a customer we made some changes. And the situation is as follows. We exported the data to excel. Multiple tabels are exported. We changed the data in the excel file and the customer validated the data. We also did export the timetamp with the data. Why? Because we had to import it again to the database. If a Timestamp changed, we should not update the data from the excel file. That’s why.

Problem situation:

Because a timestamp isn’t a varchar, or numeric value, but a binary(8) value we had to convert it. Because we import the data by SSIS back into the database. But the data we imported is from a flat text file. So every column was read as a DT_STR by SSIS, and timestamp in the stored procedure was a datatype of Timestamp. So we did a comparison of a varchar against a timestamp. Well that situation didn’t work out that well.

So what we did was to change the parameter of the stored procedure to varchar. Well SQL Server can’t handle a comparison of varchar against timestamp inside the stored procedure. Of course, so we did try it to convert it to a varbinary(8) or binary(8) and then validate it against the timestamp. Well that didn’t work out that well.

declare @timestamp varbinary(8)
set @timestamp = CONVERT(varbinary(8), '0x0000000001F4AD88')
print @timestamp

returned: 0x3078303030303030

declare @timestamp varbinary(8)
set @timestamp = CONVERT(varbinary(8), 0x0000000001F4AD88)

print @timestamp

returned: 0x0000000001F4AD88

That comparison didn’t work well. So I had to figure an other way to validate on timestamp. When I almost lost my hope I wrote a function in SQL Server.

create function [dbo].[sp_hexadecimal] ( @var varbinary(255) )
returns varchar(255)
as

begin
      declare @charval varchar(255)
      declare @i int
      declare @length int
      declare @hex char(16)

      select @charval = '0x'
      select @i = 1
      select @length = datalength(@var)
      select @hex = '0123456789abcdef'

      while (@i <= @length)
      begin
            declare @tempint int
            declare @firstint int
            declare @secondint int

            select @tempint = convert(int, substring(@var,@i,1))
            select @firstint = floor(@tempint/16)
            select @secondint = @tempint - (@firstint*16)
            select @charval = @charval +
            substring(@hex, @firstint+1, 1) +
            substring(@hex, @secondint+1, 1)

            select @i = @i + 1
      end

return ( @charval )
end

So what I did was the other. I managed to validate the data on the other way.

it did the trick...

I’ve been working for the first time with a DataFlow of updating data in a SQL Database with SSIS. The only problem is that a colleaque did made it, but he forget something with it. After reading some documentation about the DataFlow in SSIS and the using of a OLE DB command to execute a Stored Procedure I came to a certain problem.

The problem is that a OLE DB Command can execute a Stored Procedure, but the error handling is really a issue. Why, you can give a output parameter with it, but for some reason it doesn’t work well. The question is why they didn’t use a SQL Task to execute a Stored Procedure. Now I’m searching why my stored procedure isn’t fired well.

Well the problem is that the stored procedure contains a error handling not supported by the OLE DB Command. And when you have error handling handled by you SSIS you get a Error Number 0 returned. So your searching for a problem with Error number 0? That’s really crap. So I get rid of the Error handling in the stored procedure, and well the funny thing is that the stored procedure really is fired well and with filled parameters. But another problem is now that there are not enough parameters filled to execute the Stored Procedure properly.

So I have to search for that issue to solve the problem….

Well I was searching for a Stored Procedure that you can use for reindexing and updating stats, etc.

I didn’t find any nice procedures that gave me the solution I needed, so I’ve read some posts by google, and made the following procedure 🙂

This procedure will loop through all the tables and all indexes and shall Reindex them. When every index on every table is reindexed it will update all the stats on the database.

Of course you can choose to change the fill index from 80 to any other valid percentage.

Well alot of database administrators forget to truncate their database daily before backing the database up. Well I don’t understand why they are forgetting it. Because it’s a simple procedure you can add before you backup the database. Today I received a database of 5 gigabytes and with a log file of yes you don’t how of 21 gigabytes. So I was using the standard features of SQL Server 2000, but this tool does not his work with the normal truncating options of the database.

After a good search I found out it isn’t that much fun to truncate the database with a procedure. After a search on google it was easy to find the simplest script for database maintenance. I used this script and it really did work. Below you can find out yourself:

Voila, fill in the database name and the logfile name and you’re done. Run the script within query analyser or other usefull tool. I used this script with SQL Server 2000 and SQL Server 2005. This script can also be used with SQL Server 7. So with the major databases of Microsoft you can use this script. Have fun!!