If you are a big fan of using virtual machines like I am, then knowing this trick for SQL Server 2005 really saves time by allowing you to easily provision SQL instances from a base server image. Whether you add the following SQL script to your provisioning scripts or do this by hand, the basics for renaming the default SQL instance to the same name as your new server are very simple -- by utilizing sp_dropserver and sp_addserver, you can reconfigure the name of your default instance. Here's how to do it:

1. Run: select * from sys.servers. If you are like me, you probably have more than one record return given linked servers, ADSI connections, etc. Server ID 0 is the one we are concerned about. Confirm the name for this server as this is the one we want to match your new server name
2. Run: sp_dropserver YourServerNameforIDZero
3. Run: sp_addserver YourNewServerName, LOCAL
4. Run: select * from sys.servers. Your new server name will be populated properly

Note: It is important to use the LOCAL parameter in step 3 as this tells the command that you are renaming the default instance. It's pretty simple, recommended mostly for test virtual machines as these steps only take care of getting the correct name set for the SQL Server instance and does not cover any AD accounts, domain renaming(if your server image was part of a previous domain and needs to be something different), etc.

I noticed that the current site template for my blog has some quirks if you are using Internet Explorer 6.0 -- mostly related to the column formatting and width settings (posts look a little squeezed). Mozilla 2.0 seems to work just fine, but I'll play with the CSS to see if I can make it a little backwards compatible for those using IE 6.0.

Thanks!

I know there are quite a few people out there struggling with just the basics using Oracle in a Microsoft world. There are several ways to configure your environment whether you plan on doing an MS SQL project, .NET, SSIS or any or all of the above, however the basic requirement is having the Oracle service setup and configured on your machine. Below are the steps to configure that service with one of the recent .NET packages posted by Oracle (You'll have to create a login with them to download it -- I don't make the rules); the latter half of this post shows how to utilize the Oracle client service to then create a linked server in SQL 2005 to connect to an Oracle server.

Software Pre-Requisites:Oracle Data Provider for .NET 10.2.0.2.20  

• http://www.oracle.com/technology/software/tech/windows/odpnet/index.html  
• Click on the link: “Oracle Developer Tools for Visual Studio .NET with ODAC 10.2.0.2.21”

After this package is installed, you should getsomething similar added to your menu: Programs> Oracle - ODACHome1 > Configuration and Migration Tools > Net Configuration Assistant 

• Start the Net Configuration Assistant
• On the Welcome screen, choose Local Net Service Name configuration
• After clicking next, choose Add on the next screen
• In the Service Name box, type in the name of the service that the Oracle database runs under (usually something like OracleDB.world)
• After clicking next, confirm the protocol you will be using; it is usually TCP if you are unsure
• Enter the name of the server and the port number you will use to connect on the next screen
• Confirm that you will do a test and then change the user name and password to a valid user name and password for your target database
• After testing, give your new Oracle service a name

Part Two: Setting the Properties on the SQL Server 

Your first step will be to open Microsoft SQL Server Management Studio and connect to the SQL Database server and instance that you will be creating the Linked Server on. 

1.  

   • Navigate to the Server Objects folder and then to Linked Servers. Right Click on the Linked Servers folder and click “New Linked Server”

   • You will receive this dialogue screen. Note that you can create multiple linked server connections to the same server and database by giving different names in the “Linked Server” box. One reason for doing this would be to utilize the same TNS entry, but use different logins given the schemas/objects those logins will have access to.

   • For Provider, select Microsoft OLE DB Provider for Oracle. Product Name will be Oracle. Your Data Source will be the TNS name you saved from the Section One steps.

   • Next go to the security tab and fill in the login you have been granted to the Oracle server

   • Go to the server options and set the properties to the following values:

     • Collation Compatible = True
     • Data Access = True
     • Rpc = False
     • Rpc Out = False
     • Use Remote Collation = True
     • Collation Name leave blank
     • Connection Timeout leave default value
     • Query Timeout leave default value

 And that’s it!  You have now done the basic steps necessary to configure SQL Server 2005 to connect to an Oracle database via a linked server.

I ran across an interesting error in using SSIS to pull data from an Oracle database that I am posting screenshots for both the error and the fix; it's quite simple, but could cause some problems for anyone if they are either not aware nor know how to resolve the warning (it shows as just a warning and will allow you to continue building and using the package, but you will be exposed to issues down the road if not resolved here).

Here is a screenshot of the error, in this example, my datasource for my OLE DB Source in my Data Flow task in SSIS is an Oracle database:

To resolve this issue, go to the properties window for the OLE DB Source data flow object and set "AlwaysUseDefaultCodePage" to True.

Why does this happen? SSIS cannot read the character set used by the Oracle database and thus gives you this warning. In most cases in your U.S. based projects, you will be fine assumming 1252 which is the Western Alphabet character set -- if this is not the case, then you will need to determine what character set the source database is using and be sure to set your values appropriately. Here is an Oracle script you can execute against the database to try and determine this value (not guaranteed to work against all versions of Oracle): select * from sys.props$ where name = 'NLS_CHARACTERSET'

Working on a particular MOSS implementation project, we ran across an issue with performance when new sites were provisioned. The act of creating a new site within the site collection literally brought the site performance to its knees and it didn't recover for several minutes. User experience little to no response from sites in the collection.

I am working on a detailed document to show how to create the issue in a test environment, but here is the nitty gritty detail on what happened: After monitoring all the servers, we noticed disk I/O on the SQL server looked high, not abnormal, but high. We ran SQL Profiler during a site creation event and pulled the results into ClearTrace, a free .NET SQL log analyzer.

We immediately noticed that the stored procedure: proc_GetTpWebMetaDataAndListMetaData was called a large number of times during the site creation process (in this case due to the site collection size, it was in the thousands per site created). The average for the number of calls to this procedure held no matter the site template chosen.

After reporting this issue to Microsoft, the workaround was the following: change the setting in the master page for the site collection to MaximumDynamicDisplayLevels="1". This client had the setting on 3. It appears that the dynamic display level of the top menu navigation drove how many lists and web pieces that the site creation event was looking at during it's build process. I do hope that this is an issue that will be fixed in a service release, however no messaging to that effect has been given. For the time being, if you are facing a similar performance issue during site creation within a site collection, this is one area you can look to in trying to find a root cause.

Look for me to post the details on how to recreate this issue. Thanks!

Allison and I finally hit the road again on our bikes after being off the road since the Tour De Cure back in May( for those unfamiliar, it is a charity ride, tons of fun: http://tour.diabetes.org ). Our route yesterday took us up from Leon Springs, out to Boerne (with a stop at a bakery) and then back down to Leon Springs -- about 30 miles for the whole trip.  We have been waiting for the weather to cool down since neither of us is fond of biking in the south Texas heat, particularly with the humidity we have experience recently. Yesterday started out nice, around 74 by the time we hit the road, but it quickly climbed -- so waiting for the cooler weather still.

Here is the web page of the group that we ride with: http://www.hcbtc.org/. They are a wonderful group of cyclists and highly recommended for anyone getting started or looking for a low pressure group to get started with -- they have beginner's classes and more, the group has 300+ members, so it supports various levels of riding.