As mentioned in earlier posts, as time permits I’m migrating an old SQL Server 7 installation over to a new SQL Server 2005 installation. One of the things I came across was that even though I had Users, Roles and Permissions set up an looking OK, I was unable to run even a simple SELECT query against the database without an error stating that the user didn’t have permission. After checking all of the users/roles/permissions for the database and its tables and finding nothing wrong, I noticed that the properties for the user did not show a login name and any attempt to change the properties would demand a valid login name even though the field was greyed out and not editable!
The problem appears to be that when restoring an older database into SQL Server 2005, the login name can be assigned a blank entry. The easy fix if your user names map to logins of the same name, i.e. login user1 maps to user user1, is to run the following query for each database user:
USE [database_name];
GO
EXEC sp_change_users_login ‘Auto_Fix’, ‘Username’;
GO
You should get the following response:
The row for user ‘Username’ will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.
More info can be found here.
Yes, I know I’m way behind the curve here but I don’t normally used SQL Server as wherever possible I use MySQL instead. However, we have one application which uses an old SQL Server 7.0 installation which is installed on a slowly failing server. Since this application has become low traffic, I’m being tight and setting up a new server on a basic HP ML115 with SQL Server 2005 Express as it suits these specific demands easily and cheaply.
Anyway, I was having trouble connecting to the SQL Server 2005 service from a client machine and couldn’t see a reason. No log files were being created on the server so I did a bit of research. I came across Rick Strahl’s blog entry from 2006 (I did say I was working well behind the curve!) which explains that by default, SQL Server 2005 does not allow remote connections, which is fine if the setting is easy to find. Unfortunately it’s in a completely different application called the SQL Server Surface Area Configuration Tool (what a great name, very intuitive). Activate remote connections and away you go.
In my case, no actually. I still couldn’t connect remotely and, as it turned out, I couldn’t connect through ODBC on the server either. Finally, I discovered the solution which was to set the connection to server_name\sql_instance_name not just the server_name. For example, whereas on SQL Server 7 you can just connect to SERVER1, using SQL Server 2005 Express you need to connect to SERVER1\SQLEXPRESS because SQLEXPRESS is the default instance created during installation. A simple difference but confusing at first!
I found a great JavaScript routine yesterday which allows you to zoom a linked image in a web page to full size without it opening in a new page or window. It’s called FancyZoom and it’s written by Cabel Maxfield Sasser. You can use it free for non-commercial websites and he offers a low-cost, one-off license fee for commercial use.
Using it is as simple as copying two folders into the root of your website and then inserting two lines of script into the header and an onload statement inside the body tag on the pages you want it to work on. You can selectively disable the zoom on particular images and it can show a title for the image when it zooms.
Nice work Cabel!
I’m in the process of migrating two Management Information Systems, written in VB6 from Microsoft SQL Server to MySQL. The main reason for the migration is that the SQL Server is sitting on a large tower server which runs Microsoft Small Business Server 4.5 and dates back a number of years when MS SBS looked like a good idea for SMB’s like us. As it turned out, the software turned out to be an utter pile of garbage which consistently had significant performance and reliability issues. After persevering for about 18 months, I dumped it in favour of three separate linux servers running file sharing, mail and intranet. Now the tower is old, everything other than MSSQL and WINS is shut down and it’s taking up a significant amount of the available space in the systems cupboard!
I’ve done a couple of SQL Server to MySQL migrations before with VB6 applications and thought I’d share some of the database and coding issues you might come up against.
Continue reading ‘SQL Migration’
I wrote a small VB application to sit in my system tray and keep me up to date with sales progress within the company and I had used the Calibri TrueType font as the screen font. I wanted to install it on the PCs of the other Directors and I knew that the Calibri font was only available on my system because I had Office 2007 installed, so I set the VB Package Deployment utility to install the font on the client system.
I was surprised when I ran the program on the first installation and none of the text would appear. I opened the c:\windows\fonts folder and when I double-clicked on Calibri the standard test response was blank yet other fonts were working fine.
The solution turned out to be simple. The PC in question was not using ClearType to render and tidy the fonts and the Calibri font will not work without ClearType enabled!
To switch ClearType on, either right-click on the desktop and select properties then the appearance tab or go to Control Panel>Display>Appearance and where it says “Use the following method to smooth edges of screen fonts”, select “ClearType”. After a restart, this cured the problem.
One of my recent projects has been the transfer of a company’s data from an old Merlin accounts package (DOS-based would you believe?) into Sage Line 50. I had discovered by checking the contents of a few data files that the database back end for Merlin was FlexData so I located an ODBC connector for it called, appropriately enough FlexODBC. I purchased a copy and started working on my scripts to migrate the data. After side-stepping the issues with FlexODBC under Access 2007 I wrote about a while ago, I had my scripts ready so I left the project on the back burner until the company was ready for the switch-over.
The switch-over was scheduled for 1st November so I ordered all the server, client and networking hardware and set it up ready to go. I still had 10 days left so I thought for better flexibility, I could do with installing FlexODBC on my laptop instead of the office machine. I contacted Data Access in the Netherlands and was advised that I either had to purchase a second license or deactivate it on the desktop, wait three days and then activate it on the laptop. I chose the latter route and after waiting four days tried activating on the laptop. The software refused saying that the activation count was exhausted! I contacted Data Access again who had to email head office in the states. Next day the reply came back that I had correctly deactivated the installation on one PC but it was activated on another IP address which I didn’t recognise. I did a reverse DNS lookup and found the IP address allocated to a web design outfit in California, a good few miles away from me in the midlands, UK!
To cut a long story short, it took FIVE DAYS to convince Data Access to that I had NO installations of the software and that my deadline was looming for a project which required me to use it. In the end, and two days too late, they reset their activation system and I was able to activate the laptop installation. The deadline was missed and the whole migration project has had to be put back to 1st December.
Maybe I was over-confident that with 10 days to go, I could switch the license to the laptop, but technology should make these things quick and easy instead of standing in the way.
I’ve been using Microsoft Office 2000 since it was released and refrained from upgrading all the way through versions 2002, 2003 and XP since I saw no point in paying all that money just for feature-creep, particlularly when I have a network of machines to upgrade, it’s just too expensive for too little gain. I decided a few weeks ago to try an install of Office 2007 since the presentation of the documents seems to have taken a more significant jump and the ribbon bars were intriguing.
Of all the applications, Access 2007 is by far the most buggy. I’ve had numerous crashes and lock-ups from simply editing a field of data and have have hit a brick wall with one particular project I’m working on. I’d go so far as to say that Access 2007 is still beta and should not yet be released.
Continue reading ‘Access 2007 Troubles.’
Having started using Visual Sudio 2005 recently, I ran into problems when I tried to move my projects from the local drive (VS2005’s default location) to a network share where they would get backed up automatically. When attempting to run my application, Visual Studio reported a permissions error and would not run the program. Some research later and I discovered that I needed to set my network share to trusted status within the .Net Framework.
More info on how to do this here.
Recent Comments