When installing SQL 2008 R2 November CTP I received the following error:
"The SQL Server 2005 Express Tools are installed. To continue, remove the SQL Server 2005 Express Tools."
I checked add/remove, I checked the "Program Files" directory, but could not find a trace of the SQL 2005 Express Tools installed.
After a little research I found this posting: http://sqlblogcasts.com/blogs/sqldbatips/archive/2008/08/14/sql-2008-install-blocked-on-express-tools-but-actually-due-to-sql-prompt.aspx
Jasper mentions that older versions of RedGate SQL Prompt can cause this issue. You can find that by digging through the registry and looking for dependencies. In addition to the issue with SQL Prompt (only version 3 and below) there is an issue with the newer version of RedGate SQL Search. I uninstalled boh and SQL 2008 R2 installed fine.
Showing posts with label MS SQL Server. Show all posts
Showing posts with label MS SQL Server. Show all posts
Since I have now run into this issue 3 times and each time had to look it up to find out to fix it, I decided to post about it. If you use SSMS 2008/2008 R2 you will find out very quickly that altering a table using the GUI will throw an error. This is because SQL has added a new “feature” to prevent us from doing this.
In order to turn this “feature” off you will need to go to tools > options. On this screen uncheck this option:
I do not think this is a bad “feature”, but it should be something that admins can set at the server level to prevent it in UAT/Production environments… makes little sense in a development environment.
A few days ago while in Greensboro, NC I ran into a few issues setting up SQL reporting services on the same machine that SQL Server 2008 is installed. Apparently if you install SQL reporting services on the same machine as the SQL server running as a non-default instance you need to first have the SQL Browser Service running. You then need to configure reporting services to point to [servername]\[instancename]. Typically I would use [servername],[instanceport]… but that does not work!
I always forget the code to do this because I do it so rarely. If you want to move the location of TempDB for SQL Server do this:
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = '{new location}\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '{new location}\templog.ldf')
GO
Then restart the SQL Server services.
Windows Internal Database (or SQL Server Embedded) can be used by WSS or Windows Software Update Services (WSUS). It is well hidden and annoyingly unmanageable. By default the data files reside in a folder under the root drive in the Windows directory. This can be a bad place to allow a potentially fast growing database to reside… especially if your drives are partitioned for a small “C” drive just to be used by the operating system. I was able to find out how to move these databases pretty easily.
1. Download and install SQL Server Management Studio Express (find it here: http://tiny.cc/msssmse)
2. Turn off any services that might be using the database (SharePoint, WSUS, etc)
3. Connect to \\.\pipe\mssql$microsoft##ssee\sql\query using SSMSE named pipes
4. Detach all databases that you wish to move (check box to kill connections)
5. Move the databases and associated log files (MDF & LDF) to the desired location
6. Reattach databases in there new location
Now the databases will not hog your boot drive.
I am a big supporter of a multi-tiered environment when developing large applications especially in a corporate environment. I don’t just mean having different class libraries for data access/business logic I mean true physical separation between the web server and the database server. When I installed a quick sandbox site of WSS for work I just let the installation steps guide me through the installation. Apparently with WSS 3.0 this creates an instance of Windows Internal Database. This is even more stripped down than SQL Server Express edition I guess! But, I was still able to connect to it using SSMS… so it wasn’t a total loss.
When creating the production environment I wanted to do it right. I wanted SQL to run on our corporate DB server tuned for database activity and WSS to have a dedicated web box configured specifically for serving up web applications in ASP.Net. A simple install proved that this was not easy… just specifying the database server on install was not enough, especially since this was not a default instance of SQL server but one of many. I found a document buried in the MSDN library indicating that I needed to create a “Pinhole” for SQL to be seen by WSS. Here is what I did:
First I needed to create a System DSN for the SQL server instance giving it simply the name of the server (no port number on it either):
Second on the next screen you will need to click the “Client Configuration…” button:
Third you will need to add the port number of the instance created (make sure TCP/IP is selected):
If you are unsure of the port number an SQL instance is using you will need to open up SQL Configuration Utility and browse to the TCP/IP configuration window and look at the second tab bottom. There are two fields for ports here “TCP Dynamic Port” and “TCP Port”. The Dynamic port is the one you want unless you need to set your own port… if so change the “TCP Port” field and click OK… you will need to restart the SQL services for this to take hold.
When done setting the port click OK and step through the rest of the DNS configuration wizard. Test the connection to make sure that it works. Now all you will need to do in WSS is specify the server name exactly how you did on the first screen on the DSN wizard and all should be good!
By default SQL Server Management Studio does not allow changes to be made to tables if they need to be dropped and recreated. This is frustrating in a development environment since changes are made quite often. Again, Microsoft enables a feature to protect a production environment and does not make a fix for it well known. Here is the error you will receive:
Here is the fix… go to “Tools > Options”:
Then expand “Designers” then click on “Table and Database Designers”. Uncheck the box next to “Prevent saving changes that require table re-creation”:
When trying to connect to a SQL 2005/2000 Server from SQL Server Management Studio 2008 you will get this error when trying to view the list of databases:
There is a very quick way to resolved this. Click on the “View” menu and select “Object Explorer Details”:
Right click the column header and deselect “Collation”:
Now refresh the database list and all should be good.
Subscribe to:
Posts (Atom)