Tutorials/Tips

Restore SQL Server 2005 Master Database

[SQL Server 2005 master Database Download 700 KB]

You are running SQL Server 2005, your master database has got corrupted because of sudden power failure or other reasons. The SQL Server will not start. You have tried registry setting, playing with changing protocol, disabled via protocol but nothing worked. Do do not have backup of the masters, there is no way you can start your server, you are on the versge of loosing all you precious data. Do not panic, you can recover it by copying a fresh master.mdf file but there are tricks so please read on.

1. The first hurdle is you do not have a backup of SQL Server 2005 master database. Well I have attached on master db SQL Server 2005. It is from fresh install and contains master.mdf mastlog.ldf files (both are required). When you copy this to the default location, all you existing logins will be lost which you can recreated. Also all the database will be detached which you will need to re-attached.

2. Depending on which version of OS are you using (32 bit or 64 bit), appropriate changes need to be made. If  your SQL Server folder is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data that means you are using 32 bit  OS. In this case, copy the master and master log files in the folder and create another folder C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA (note Program Files (x86) folder. This is required because the mdf files I created was on 64bit OS so the default path for other databases is this folder. If you already have C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA as your current folder, simply copy the unzipped files and you should be all set.

3. If you were  using 32 bit OS (no x86 folder),  copy all the system databases into (x86) folder. They must be copied here so SQL Server can start. This step is required only for 32 bit OS.

4. Start SQL Configuration Manager and start SQL Server. It should not start.

5. In case of error,  got computer management, event viewer and see the error logs. If it is about missing a database etc, simply copy it to x86 folder.

6. In SQL Server Management Studio, attach  your database by right clicking on database and click attach. Select the mdf file of the database you want to attached.

7. Create new logins if needed.

Your database should be all set.

Please make sure to backup your database files just in case. Also note that I do not take any responsibility if anything goes wrong.


Error Messages  that I encountered  during trouble shoot

While troubleshooting my own problem, I received the following errors which I fixed by copying relevant data. The only way to find our the actual error is be looking at event viewer. I will leave the following error for reference purposes.

The first error that means my master database has gone corrupt. Most likely occurred because of power failure.

The log scan number (406:48:1) passed to log scan in database ‘master’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

After copying master.mdf and mastlog.ldf, I got this msg in event viewer

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mssqlsystemresource.ldf’. Diagnose and correct the operating system error, and retry the operation.

After creating x86 folder and copying the two files, I got errors about the other files

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf’. Diagnose and correct the operating system error, and retry the operation.

 

Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions.

How to Add SQL Server Connection in Crystal Report

Crystal Reports connection String SQL Server

I worked in Crystal Report for quite a while but even I got confused how to add SQL Connection when I installed Crystal Reports on a new sytem. Basically there were no existing connections under any of the options and it was not every intuitive how to add a connection string.

1. Simply double click OLE DB (ADO)

2. This will popup a dialog, go to the very bottom in the list and choose SQL Server Native Client.

That’s it! Check the checkbox for integrated security and select  your server. You should be all set.

2015-04-25 12_14_30-OLE DB (ADO)

 

Attendance Summary Report on LIMTON TIS

Here is quick SQL query that generates employee’s monthly attendance report on Limton TIS. Getting Department and Designation names was a bit tricky as they are not named as such. They are placed inside HRTCompanyLogicDetail table.

This report does contain on query from my own database. Basically were are combining attendance report from TIS and our own attendance system.

convert parameter to int in Stimulsoft mrt

You can use standard C# function in Stimulsoft .MRT report although I have not seen them in documents as such. For example if you want to add two parameter values in Stimulsoft MRT Report, you will have to convert them to integer first. By default they are of “Object” type.

In the following example, I am converting @Month object into an integer. It must first be converted to string using ToString() and then use Int32.Parse() function to convert it.

This variable can then be added to another variable (concatenation) as

Int32.Parse(DataSource1.Parameters[“@num1”].ParameterValue.ToString()) + Int32.Parse(DataSource1.Parameters[“@numb”].ParameterValue.ToString())

Error occured while loading attendance from database – Hanvon

Error loading attendance Hanvon
In Hanvon FaceID Manager, If you go to “Transaction View” and you select “from date” to “to date” when loading data it might spit out this error

Error occured while loading attendance from database

If you fire up SQL Server management studio and look at the logs (under Maintenance > SQL Server Log and double clicking the current log and then selecting “Windows NT” checkbox at left), you will see this error

Stack Trace: at DataAccess.clsDataAccessBaseForMSAccess.ExecuteDataTable()
at DataAccess.HanvonFaceIDPolling.clsPolling.GetAttendanceRecords(String rstrDateTimeFrom, String rstrDateTimeTo)
at HanvonFaceIDManager.frmListDotDatGenerator.LoadDataFromDB(enDateRange miDateRange)

The error message is clearly about ill formatted date. However my date format seems to be in standard format. So what else can be wrong? It is likely related to the corruption of data as I have explain in error while logging in to Hanvor FaceID.

To fix, open the culprit file in Microsoft Access, your .dbm file where Hanvor FACEID stores all its data ( Can be found by going to setting page in Hanvor FacedID). Click Menu > open and the double lick the very first table Attendance. Find out an entry (an invalid date) that might give you this problem. In my case it was the very first record where data was like this

AttendanceID MachineID PinNumber AttendanceDateTime Status MachineName AttendanceDate AttendanceTime AttendancePhoto
################ ################ ################ ################ ################ ################

Right click on the row and delete this record. Now fetch date based data and it should work!

Redirect in PHP not working

is generally enough to redirect your page but most of the time it does not work because it requires that no data should be sent to the client before the header, or rather header should be the first thing sent to the client. To fix that problem using PHP Buffering

Note that in some case even this might not work. The reason is likely something was already sent to the client before the header. Typical example would be an add-in in the browser which injects some javascript into the page. If that is the case, redirect will not occur. In that you give the user an option to manually redirect, like this

To better trouble shoot this problem, see page contents and you will like see something that has been spitted out. In my case it was

The problem was only in Safari probably because of extension. In Firefox the redirect was working fine.

Looping through gridview in C# always return empty

I had a gridview which was bounded to a datasource (datatable in my case) in code behind in C#. Code was something like this

This was always returning empty (”) no matter which column index I was pointing to. I tried different variations like

but it failed too. After pondering on it for quite a while and still not resolved, I decided to loop through the data source itself, in my case a datatable. That actually worked! That code looks like this

I am still not sure why it will not read the values from GridView in a loop but at least there is work around.