Category Archives: SQL Server

Adding a linked 2000 server to SQL 2012

I installed SQL 2012 and did my usual “add a linked server” to get to my other one (a 2000 server), and it would not allow the connection. As part of Microsoft’s “moving on'” directive, the latest SQL Client on 2012 won’t talk to 2000.

You can add it alternatively using the sp_addlinkedserver stored procedure:

EXEC master.dbo.sp_addlinkedserver @server=N’YourServerHere’,@srvproduct=N”””,@provider=N’MSDASQL’,@provstr=N’DRIVER={SQL Server};SERVER=YourServerHere;Trusted_Connection=yes;’

I’m not doing anything fancy, just querying tables and inserting data and this appears to work fine.

Advertisements

Installing Small Business Server 2008 and SQL 2005 Part 2

So now that SBS 2008 is installed, we move on to installing SQL 2005.  Note that a version of SQL 2005 is already installed for a SBSMonitoring, so you are actually installing a DEFAULT server instance.

Before you do the install, however, you need to make a registry change per this link:
http://technet.microsoft.com/en-us/library/cc794697(WS.10).aspx

Installing SQL Server 2005

If you plan to install SQL Server 2005 on the original server running Windows SBS 2008, do the following before you run the installation.

  1. Click Start, click All Programs, click Accessories, and then click Run.
  2. Type regedit, and then click OK.
  3. At the User Account Control prompt, click OK.
  4. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Machines.
  5. Double-click OriginalMachineName, and then change the OriginalMachineName value name to the current server name.
  6. Click OK.

On the Instance Name page of the original server, do not select SBSMonitoring or Microsoft##SSEE as the instance name because the setup will not upgrade existing instances, which could result in Windows SBS 2008 functionality issues.

Also, you need to keep in mind that if this is a Domain Server machine, you cannot use the local system account for SQL Server, you need to use another account.

Lucky for me, these “hints” helped.  However, I wasn’t able to install the client tools during the original install.

I tried installing the client tools on my machine – a CLIENT, and I was also unable to install.  It turns out that this has to do with the fact that I had VS 2008 installed on my machine, which also had installed SQL 2005.  There is a method for handling this:

First go to Add/Remove Programs in Windows XP or Uninstall a program in Vista and select to uninstal SQL Server 2005. It will start the uninstall process but will let you choose what you want to uninstall. So select only workstation components. Uninstall them and then reinstall them from whereever you want. I had the same problem running SQL Server Management Studio Express and wanted to install the fully functional SQL Server Management Studio from SQL DVD and it was blocked. Removed everything from SQL Native client to workstation components, leaving only SQL Server 2005 database on the computer because I had online databases there. Install went on without a problem.

via this link: http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/117454be-67c5-4458-8429-08eccd6f82f6

This worked on the client workstation – I’ll try it on the server next.

Installing Small Business Server 2008 and SQL 2005 Part 1

I’m in the middle of an SBS 2008 premium installation – I say “in the middle”, but I’m actually about to start the fourth version of the install.  The first two fell over into the swamp.  The third caught on fire and THEN fell it he swamp – the fourth one – AH – it’s going to be right.  I looked all over the internet for “hints” about the install as Microsoft only provided me with the media.  I took said media and plowed forward much like the original adventurer in Zork I – but the troll just got me.  Something to do with SQL and Domain servers, etc – in any case, I can’t install the tools I need and I think it’s because I botched the install so I’ve decided to start documenting my endeavor just in case someone else might run into similar problems.  There is a LOT of information out there, it’s just not always easy to find.

So – to start off, I am going to install SBS 2008 Premium on a (as far as you know) generic computer.  This is actually the easy part, but there is one thing to remember – the good old “administrator” account gets disabled after the installation.  I take it that this is being done for security reasons – so during the process you will actually make your own “custom” administrator account.  Don’t lose the password, whatever you do.

I will report back after this step is complete.

Small Business Server 2008

I’m in the middle of an SBS 2008 installation.  How fun is that?  It’s the second time.  Maybe 3rd time will be a charm, I don’t know yet.  The first one fell over and sank into the swamp.   It all comes down to which part of “Install in the highlighted partition” didn’t you understand?

Later, after I realized I had a 1.7 TB system drive, I knew I was in trouble.

Not much else to report.  Once SBS is up, I’ll install SQL 2005 and be off and running.

How to move TEMPDB

Sometimes you need to move TEMPDB in SQL Server. Either because you need more space, or if you just don’t like where it is located. To do that, you need to run the following statements replacing “G:\” with the drive and directory location where you want the TEMPDB files. Once you run these statements, you must stop and restart the SQL Server service and it will create the new files. You should manually remove the old fields after you verify that the change took place.

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = ‘G:\TEMPDB.MDF’)
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = ‘G:\TEMPLOG.LDF’)

SQL Server / Using BCP to create a format file

So you have a database table and you want to make a format file, but who wants to go through and make one by hand?  You can get a head start by using the BCP (Bulk Copy Program) utility to create one for you.

You can use any existing table to do this.  Open up a command window (DOS) and use the following syntax:

BCP database.dbo.tablename format nul -f FORMATNAME.FMT -c -SServerName -UUserName -PPassword

Substituting database, tablename, FORMATNAME.FMT, ServerName, UserName, and Password with your information.

This will generate a format file for use with a tab delimited file.  You can then alter the delimiters if necessary from tab to something else.

Run a query on all of the tables in your database