Moving Replicated FullText Index in SQL Server

We needed to move the location of our full text index (FTI) on a subscriber. I thought this would be a pain because the replication, but it turns out to be pretty straightforward. Here's the system I am working with:

SQL Server 2005
Transactional Replication with an Updateable Subscriber.

Here are the steps I took:


0. Make sure you have appropriate backups.
1. Point all of the apps to the Publisher because we will need to take the Subscriber offline.
2. On the Subscriber, open the synchronization status by right-clicking on Replication->Local Subscriptions-> ans selecting View Synchronization Status.
3. Stop the Synchronization Service. (This really just pauses updates).
4. On the Subscriber run SELECT name FROM sys.database_files WHERE type_desc = 'FULLTEXT'; to get the name of the FTI.
5. On the Subscriber run ALTER DATABASE [DB_NAME] SET OFFLINE;
6. Move the FTI where to it's new home.
7. On the subscriber run ALTER DATABASE [DB_NAME] MODIFY FILE (Name=[FTI_NAME], Filename = "'new/location/on/disk/');
8. On the Subscriber run ALTER DATABASE [DB_NAME] SET ONLINE;
9. Start the Sync Service in View Synchronization Status.

That should be it. Hope this helps.

T-SQL Query: Tables Without a Primary Keys

I used this when I was setting up a Transactional Replication system on SQL Server. I needed to figure out which tables did not have primary keys assigned.


select s.name+'.'+ t.name
from
  sys.schemas s
  join sys.tables t on s.schema_id = t.schema_id
  left join sys.indexes i on i.object_id = t.object_id and
                             i.is_primary_key = 1
where i.name is null
order by (s.name+'.'+ t.name) asc;


Great Post on SQL Server Stored Procedure Variables

It took me a while to find this about using variables in the TOP clause of a MSSQL stored prodedure. This post answered all my questions:

http://sqlserver2000.databases.aspfaq.com/how-do-i-use-a-variable-in-a-top-clause-in-sql-server.html

Here's the best part


CREATE PROCEDURE dbo.getFoo
  @top INT 
AS
  BEGIN
    SET ROWCOUNT @top

    SELECT foo
      FROM blat
      ORDER BY foo DESC

    -- never forget to set it back to 0!
    SET ROWCOUNT 0
  END
GO


Programmaticly Changing the File Attribute of an App.config File.

The requirement for this assignment was to use a command line argument to change the settings of a C# console application. I didn't want to compile the settings into the app itself, but to use an app.config file instead. Here's how it works:


public static bool SetEnvironment(string env)
{
  string configFilePath = string.Empty;

  env = env.ToLowerInvariant();
  switch (env)
  {
    case "dev":
       configFilePath = @"Config\Dev.config";
       break;
    case "prod":
       configFilePath = @"Config\Prod.config";
       break;
    default:
       return false;
  }

  try
  {
    System.Configuration.Configuration config =
       ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);

    AppSettingsSection appSetSec = config.AppSettings;
    appSetSec.File = configFilePath;
    config.Save(ConfigurationSaveMode.Modified);
    ConfigurationManager.RefreshSection("appSettings");
  }
  catch (Exception ex)
  {
    OfflineDemoTool.WriteError(ex.Message);
    return false;
  }

  return true;
}



One thing that I forgot to do was to make sure that the Config\Dev.config and Config\Prod.config files were set to "Copy if newer" in their properties list, otherwise they won't be copied to the output directory.

Most of this comes straight from the MSDN article found here: http://msdn.microsoft.com/en-us/library/system.configuration.appsettingssection.aspx

Windows Commands with Arguments in Powershell

I have been trying to write a simple PowerShell script that runs (invokes?) MySqlDump.exe for two days. PowerShell syntax for running commands with arguments is not obvious and I wasn't able to find it documented anywhere. While this case is specific to mysqldump, it can be applied to any command line executable with multiple arguments. The trick is to create an array of the arguments and use that as the second "argument" for the ampersand (&) call operator. Here's what my final example looked like:



[string]$pathToExe = "C:\MySQL\MySQL Server 5.1\bin\mysqldump.exe";
[string]$user = "myUser";
[string]$password = "myPass";
[string]$dbName = "myDB";
[Array]$arguments = "-u", $user, "--password=$password", $dbName;

& $pathToExe $arguments | Out-File -FileName "out.sql";




So there you have it. That's how to run an executable with spaces and arguments from PowerShell.

While this article from PowerShell.com didn't answer my questions, I thought it was pretty useful and relevent:

http://powershell.com/cs/blogs/ebook/archive/2009/03/30/chapter-12-command-discovery-and-scriptblocks.aspx

Changing aspnet_wp user in IIS 5.1

It's possible that no one will ever have to work with IIS 5.1 in XP again, but here you go just in case:

We needed the asp.net worker process (aspnet_wp.exe) to access a share on a remote machine. By default the "Netowrk Service" runs aspnet_wp and that user doesn't have access to remote shares. The way to change this is by changing the "processModel" attribute in your machine.config file (c:\windows\microsoft.net\framework\v2*\config\machine.config).

Mine ended up looking something like this:

<system.web>
<processModel userName="DOMAIN\username" password="password" autoConfig="true">
</processModel>
...
</system.web>


Just make sure to restart IIS for the changes to take effect.

Powershell Diff Directory

I wanted to create a file diff between two different directories. Unfortunately, the Copy-Item CmdLt will not (as far as I know) create the directory structure while copying. This is a bummer. I wanted to keep all of the files in the pipe as opposed to to getting all the files first because of the number of files in the directory structure. Here's what I cam up with



$fromDir = "C:\FromDir";
$toDir = "C:\ToDir";
$diffDir = "C:\DiffDir";


Get-ChildItem -Recurse -Path $fromDir | % {
if ((Test-Path (Join-Path -Path $toDir -ChildPath ([string]$_.FullName).Replace($fromDir, ""))) -eq $false)
{
if ((Test-Path (Join-Path -Path $diffDir -ChildPath ([string]$_.Directory).Replace($fromDir, ""))) -eq $false)
{
New-Item -Type "Directory" -Path (Join-Path -Path $diffDir -ChildPath ([string]$_.Directory).Replace($fromDir, ""));
}
Copy-Item -Recurse -Force $_.FullName -Destination (Join-Path -Path $diffDir -ChildPath ([string]$_.FullName).Replace($fromDir, ""));
}
};



As always, if anyone know a better way to do this, let me know.