September 11, 2006
Thumbs+ & SQL Server 2005 Express
Many of us who use Thumbs+ have found that the default 2GB limit of Microsoft Access to be a concern, if not an outright problem. There are several solutions to this - you can buy SQL Server 2000, which can be cost prohibitive, or install MySQL, which some users find more intimidating than others. A third option is available, now that Microsoft has made SQL Server 2005 Express (SSE) a free download.
To aid those interested in using SSE with Thumbs+, I have made the following brief tutorial available. I would appreciate any input into correcting errors, or of any bugs anyone finds in using SSE with Thumbs+.
For my installation, I installed the SQL Server 2005 Express Edition with Advanced Services. You may want to install just the SQL Server 2005 Express Edition for your needs. See this page for the differences between the two:
http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx
Download & install SQL Server 2005 Express:
http://www.microsoft.com/sql/editions/express/default.mspx
During my install, I opted for the mixed mode authentication schema as I like to use the database for web scripting applications. You should be able to install it using Windows Authentication only and have it work for your installation of Thumbs+. Please share any experiences with this you have that cause problems.
In order to have SQL Server 2005 Express with Thumbs+, you will need to use the SQL Server script available from Cerious. The default script throws an error but you can ignore this error. The script will successfully install and work.
Alternatively, you can use the attached script, which is an export from the working version I am using with SSE. If you do, you will need to create the database manually.
I created a test database called ThumbsExpress. This is done by starting the Microsoft SQL Server Management Studio Express application (Start -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio Express). Under the instance of your server installation (usually indicated by your computername\SQLEXPRESS) you will see a Databases tree option. Right click on Databases and select New Database. In general, the default options are fine. Advanced users may want to change some of the settings (such as file locations, access options, etc).
Next, import the sql file for creating the tables. Use the File -> Open File option from the toolbar of Microsoft SQL Server Management Studio Express application (Start -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio Express) and browse to your file.
Once you have loaded the file, click the Execute button on the toolbar. You will see the following error from the Thumbs+ script, but this does not affect the installation as far as I have discovered:
Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.
Msg 15007, Level 16, State 1, Procedure sp_addalias, Line 69
'ThumbsUser' is not a valid login or you do not have permission.
(1 row(s) affected)
As I stated above, I have found no repercussions to this error, but someone else might. I will update if there is a discovery.
Once you have installed the database, you will need to modify the default settings of the server instance in order for it to work with the ODBC connection. Thumbs+ connects to the SSE database via ODBC.
The default settings for SSE are designed for security, which is a good thing, but we need to change those settings. By default, connections to the server via Named Pipes and TCP/IP are disabled.
The setting you will need to change is the Named Pipes communication option. To update this setting, start the SQL Server Surface Area Configuration tool (Start ->Programs -> Microsoft SQL Server 2005 -> Configuration tools -> SQL Server Configuration Manager) You will see that by default the Named Pipes option is disabled. Right click it, and select Enable.
You will need to restart the SSE service before this change takes effect. You can do this by entering the Services snap-in under Administrative Tools.
Now you can setup an ODBC connection to the Thumbs+ database running on SSE.
Start up the ODBC connection tool. This tool can be found under Administrative Tools or by clicking Start -> Run -> odbcad32 - this will start the ODBC configuration tool.
Click System DSN - > Add -> SQL Server driver ->Finish.
Next, enter in the "Which server do you wish to conntect to?" the server instance:
.\SQLEXPRESS
Set your authentication Windows NT authentication.
Be sure to set the default database to the Thumbs+ database you created above.
Click Finish and be sure to test the connection.
You can now open Thumbs+ and use the File -> Database -> Connect option to connect to your new SSE database.
If you discover any bugs in the process outlined above, please let me know and I will make the corrections.
Enjoy!
To aid those interested in using SSE with Thumbs+, I have made the following brief tutorial available. I would appreciate any input into correcting errors, or of any bugs anyone finds in using SSE with Thumbs+.
For my installation, I installed the SQL Server 2005 Express Edition with Advanced Services. You may want to install just the SQL Server 2005 Express Edition for your needs. See this page for the differences between the two:
http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx
Download & install SQL Server 2005 Express:
http://www.microsoft.com/sql/editions/express/default.mspx
During my install, I opted for the mixed mode authentication schema as I like to use the database for web scripting applications. You should be able to install it using Windows Authentication only and have it work for your installation of Thumbs+. Please share any experiences with this you have that cause problems.
In order to have SQL Server 2005 Express with Thumbs+, you will need to use the SQL Server script available from Cerious. The default script throws an error but you can ignore this error. The script will successfully install and work.
Alternatively, you can use the attached script, which is an export from the working version I am using with SSE. If you do, you will need to create the database manually.
I created a test database called ThumbsExpress. This is done by starting the Microsoft SQL Server Management Studio Express application (Start -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio Express). Under the instance of your server installation (usually indicated by your computername\SQLEXPRESS) you will see a Databases tree option. Right click on Databases and select New Database. In general, the default options are fine. Advanced users may want to change some of the settings (such as file locations, access options, etc).
Next, import the sql file for creating the tables. Use the File -> Open File option from the toolbar of Microsoft SQL Server Management Studio Express application (Start -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio Express) and browse to your file.
Once you have loaded the file, click the Execute button on the toolbar. You will see the following error from the Thumbs+ script, but this does not affect the installation as far as I have discovered:
Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.
Msg 15007, Level 16, State 1, Procedure sp_addalias, Line 69
'ThumbsUser' is not a valid login or you do not have permission.
(1 row(s) affected)
As I stated above, I have found no repercussions to this error, but someone else might. I will update if there is a discovery.
Once you have installed the database, you will need to modify the default settings of the server instance in order for it to work with the ODBC connection. Thumbs+ connects to the SSE database via ODBC.
The default settings for SSE are designed for security, which is a good thing, but we need to change those settings. By default, connections to the server via Named Pipes and TCP/IP are disabled.
The setting you will need to change is the Named Pipes communication option. To update this setting, start the SQL Server Surface Area Configuration tool (Start ->Programs -> Microsoft SQL Server 2005 -> Configuration tools -> SQL Server Configuration Manager) You will see that by default the Named Pipes option is disabled. Right click it, and select Enable.
You will need to restart the SSE service before this change takes effect. You can do this by entering the Services snap-in under Administrative Tools.
Now you can setup an ODBC connection to the Thumbs+ database running on SSE.
Start up the ODBC connection tool. This tool can be found under Administrative Tools or by clicking Start -> Run -> odbcad32 - this will start the ODBC configuration tool.
Click System DSN - > Add -> SQL Server driver ->Finish.
Next, enter in the "Which server do you wish to conntect to?" the server instance:
.\SQLEXPRESS
Set your authentication Windows NT authentication.
Be sure to set the default database to the Thumbs+ database you created above.
Click Finish and be sure to test the connection.
You can now open Thumbs+ and use the File -> Database -> Connect option to connect to your new SSE database.
If you discover any bugs in the process outlined above, please let me know and I will make the corrections.
Enjoy!