I’ve just finished doing some maintenance on one of my development servers and encountered a problem where I could not restart the SQL Server. The development server was a virtual machine running on VMWare which had a second virtual HDD installed for SQL Server to store the database and log files. This disk had been provisioned at 200GB and over time had acquired a lot of files, such as database backups, which became unnecessary and had filled the 200GB.
I needed to get some of the space back on the VM host so I stopped the SQL Server, copied the SQL mdf and log files to a new HDD, deleted the SQL HDD and then set the drive letter of the new HDD to that of the old one.
I then tried to start SQL Server and received the following error:
Windows Event Viewer – FCB::Open failed: Could not open file E:\SQL208RTM\Live\DYNAMICS.mdf for file number 1. OS error: 5(failed to retrieve text for this error. Reason: 15105).
I’m currently involved with a couple of projects where we have needed to migrate databases from one SQL Server to another and needed to do so more than once.
Prior to the first copy, we used a script to load the users into SQL Server and then restored the databases and had no problems.
Later on, after changing some of the permissions to databases we had need to remigrate some of them over. However, we found that users could not access some databases and when we tried to add them we got the following error;
Msg 15023, Level 16, State 1, Line 1
User, group, or role 'pidev' already exists in the current database.
It was looking very like we would need to go through each database and remove access and then regrant it all; a labourious process. A quick Google found me a page from Julian Kuiters which introduced me to the ALTER USER command. The following code will run against the selected database and tie together the SQL Server user with the user in the database without any other work;
ALTER USER pidev WITH LOGIN = pidev
The first highlighted element needs to be changed to the database you want to update the user on and then the following two entries are the usernames which need to be linked (these should be set to the same value).
I was doing some work with a custom table yesterday and at one point needed to make a change to the table layout via SQL Server Management Studio. However, when I tried, I got the below error message;
Earlier this week I did a post on how to configure SQL Server Reporting Services for use with Microsoft Dynamics GP. This is the follow up post where I’m going to explain how to deploy the SSRS reports for GP.
To deploy the reports you need to open the Reporting Tools Setup window (Continue reading
The post following this will cover deploying SSRS reports within Microsoft Dynamics GP but I thought I’d cover configuring SSRS as a spearate post rather than assuming everyone has it already configured. A number of the clients I deal with have had GP for a while and never installed the SSRS reports but are now doing so.
When you install SQL Server Reporting Services at the same time as SQL Server you have the option to automatically configure SSRS with the default settings. In the case of someone who has SQL already installed but didn’t install SSRS at the same time, this is not an option.
This How To starts after SSRS has been added to the installation on the client machine at the point where you would start to configure it; if you’d like a walk through adding SSRS to an existing installation of SQL Server, let me know and I’ll sort one out.
To do the initial configuration of SSRS, or to change the configuration of an existing installation, you need to start the Reporting Services Configuration Manager (Continue reading
I periodically have problems when trying to restore a GP company database over the Test database as SQL reports that the database is currently in use.
One way of resolving is to restart the SQL Server but this is only possible on a stand alone test system which is not being used by other people but this approach is overkill.
The better solution is to change the database to single user, restore the database and change the database back to multiple user. This can be done manually through SQL Server Management Studio but is far easier done via a SQL script.
I have installed SQL Server into Virtual Machines many times in the past but I’ve recently seen an error when trying to do it again;
I had a think about the error and realised that I usually installed SQL from the VM HDD and not, as I was in this case, by attaching the iso image to the VM as external media. I uncompressed the iso onto the VM HDD and ran the installer again from this source and it installed without problem.
When you install Microsoft SQL Server you select the Data, Logs and Backup folder during the installation process. If you subsequently need to change these folders you can only change two of them through the SQL Server Management Studio UI. On the Server Properties dialog there are boxes for the Data and Logs default locations;
Like some other posts recently, this one is the result of a conversation with a colleague at my new company, where I found they were unaware of something I took for granted; the ability to easily rename a comuter which has a stand alone instance of SQL Server installed.
I do this fairly often as I use quite a lot of Virtual Machines for testing different configurations, but I don’t want to have to build a new one every time, so I make copies of a base VM and rename it each time I want a new one (there is often more than one running at any one time and I want to avoid conflicts).
As well as renaming the machine inside the VM, I also rename the SQL Server to make sure it matches the name of the machine; I’ve had problems before with Microsoft Dynamics GP when I’ve not done so.
Doing this is actually very simple. Continue reading
Backing up the a Microsoft Dynamics GP company to test is, unfortunately, not as simple as backing up one database and restoring it to the Test one. There are two scripts which need to be run after doing so; the first changed the INTERID and Company Name and the second changes the database owner to dynsa.
At my last company, we decided to make this process as easy as possible for clients, so we started creating a SQL Agent Job which would do the backup, restore and run the scripts with minimal effort or could even run on a scheduled basis. The basic purpose was to give the client a test or training system which was always, or could very quickly be, up to date. Continue reading