New class on 2780B

I started another class for course 2780B - Maintaining a Microsoft SQL Server 2005 database. Though I wanted to do this on 2008 (6231A), this has been selected by the institute for participants because most of them are using 2005. In a way, it is good, because, doing another class on 2008 (6231A). Doing three classes per week is bit difficult, but I like it, getting more invitations, planing to have more classes.... this might stop me, attaching permanent to a company.

Microsoft XPS Document Writer cannot be found

I am experiencing this error with Microsoft PowerPoint 2007, when the hibernated machine which had PowerPoint presentation opened, is started. Searched for a solution but no luck so far, and many face this it seems. Anyone know the solution?

Are you using "Windows Services window" for changing the SQL Server Service Account?

If you are using the "Windows Services" window for changing the assigned account for SQL Server service, you may face some problems later. When required, we should use SQL Server Configuration Manager, instead Windows Services because the Configuration Manager performs required operations such as configuring SQL Server related configurations, re-generating Service Master Key and decrypts and encrypts all objects that had been encrypted by Service Master Key. If you do not use Configuration Manager, you need to execute ALTER SERVICE MASTER KEY statement manually for completing the operation.

PerformancePoint error: Unable to connect to Server - Part II

Once I blogged about this error with this link. Today I faced the same, but the reason was different. My new machine is installed with Vista-Ultimate 64-bit. I started installing all the prerequtities for PerformancePoint, did everything. Once installed, tried to connect to http://localhost:40000/WebService/PmService.asmx through Dashboard Designer (Options box), but no luck. It gave an error saying "You have not admin privileges.". I had logged in as "Dinesh" and the account "Dinesh" is in Administrators group. I made sure again, that "Dinesh" is in Administrators group and "Dinesh" has permissions for the PerformancePoint web service too. I still do not know, why "Dinesh" could not make the connection..... he is in the Administrator group. I solved the problem by logging to the system as an Administrator. Once logged in, I was able to connect and then I used the "Permission" box for adding "Dinesh" as an Admin to the PerformancePoint. When I log in with "Dinesh" now, it allows to make the connection now. I think, by default, Administrator is added as the Admin of the PerformancePoint, so, he is the only person who can give permission to others. Keep this in mind, if you use some other account without using Administrator.

Connecting to Analysis Services with the Port Number

First of all, let me tell you that why I need to connect to Analysis Services with the Port number; because the Analysis Services 2008 is running as a named instance and SQL Server Brower needs to be disabled as a security measure. Why do we need SQL Server Browser Services? By default, Analysis Services uses the port 2383. When you connect to the default instance (in my case, it is 2005), it connects to the SSAS without any problems, even though you have not specified the port with the connection string. If you try to connect to a named instance of Analysis Services (again, in my case, it is 2008), without specifying the port, you will still be able to connect, as long as the SQL Server Browser is running. Named instances of the SSAS use port numbers that are dynamically assigned, therefore they are varies. When you restart the service, it assigns a new one. Assigned port number can be seen with msmdredir.ini file in the C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig folder. Though we do not specify the port in the connection string (even with SSMS), SQL Server Browser handles the request and directs us to the correct instance, because it knows the port numbers assigned. Now my issue was, unavailability of SQL Server Browser hence cannot connect to the named instance. When we try to connect to the named instance with SSMS or BIDS, it says that Browser is not running. Since the Browser cannot be enabled (as a security measure), all we have to do is, find the port number (from the ini file specified above) and specify with the connection string. One thing you have to remember is, when specifying, it should be {MachineName}:{PortNumber}, NOT like {InstanceName}:{PortNumber}. If my machine name is DINESH-PC and named instance is SQL2008; When connecting to the default instance: DINESH-PC:2383 When connecting to the named instance: DINESH-PC:50019, NOT DINESH-PC\SQL2008:50019 If need to fix a port number, use the ini file or Port property in Analysis Services, for changing.

Foreign Keys with TinyInt do not recongnize by SSAS

This is still there, with SQL Server 2008 too. I usually use TinyInt for tables' primary key when the number of records to be held are less than hundred, so applied the same rule for my newest Data Warehousing project, completely forgetting the fact that Analysis Services cannot recognize the relationships between Fact and Dimensions that have TinyInt as the primary key. As usual, had to change all my Dimensions tables, changing primary keys to Smallint. Remember this, if you are designing a data warehouse.

Restoring one File Group

Today, I came across a situation where I had to explain the FILEGROUP restore of SQL Server. Since I have not done this for a long time, I brushed my knowledge with Preethi's help and thought to blog it too. If you have a database that has more than one file group, you may face to a situation where you need to restore only one file group, without restoring the entire database, saving time. One thing we have to remember is, secondary file group (or file) restore needs Primary file group restore. If your Primary file group contains a lot of tables, again, it takes time for restore operation (Usually, database that contains secondary file groups does not use primary for user-defined tables). So, make sure that you do not use Primary file group for your tables. Here is an example: Assume that we have a database that has three file group: Primary, FG1, and FG2. In order to restore file groups, you should have backups of all file groups (both full and differential are possible). Here are steps for restoring a file group (FG1); -- Backup tail-log if require BACKUP LOG MyDatabase TO DISK = 'D:\Backups\TailLog.bak' WITH NO_TRUNCATE, NORECOVERY -- Restore the Primary file group RESTORE DATABASE MyDatabase FILEGROUP = 'Primary' TO DISK = 'D:\Backups\MyDatabase_FG1.bak' WITH NORECOVERY -- Restore the FG1 file group RESTORE DATABASE MyDatabase FILEGROUP = 'FG1' TO DISK = 'D:\Backups\MyDatabase_FG1.bak' WITH NORECOVERY -- Restore the log (if need, can be restored to a specific time) RESTORE LOG MyDatabase FROM DISK = 'D:\Backups\TailLog.bak' WITH RECOVERY