Set Trustworthy On

Assemblies usually require that the TRUSTWORTHY condition is set to ON. The default is OFF.

Here's the simple script to change it:

ALTER DATABASE MyDatabaseName
SET TRUSTWORTHY ON;
GO

Simple.

SQL XML Assembly Blows Up and How to Fix It

Our rather complicated SQL XML assembly blew up Friday after one of our developers did a backup/restore of our development database to make a testing database. The problem occured only when the original database was restored to make a new database

The lead developer and I tried to sort it out but didn't get anywhere. This morning I asked the data warehouse developer to look at this morning. He thought the problem was with permissions.

Here is what happened:

There are three stored procs associated closely with the assembly: usp_InsertExceptionLog, usp_getXMLReference, and usp_GenerateGroupReferenceXML.

Two of the procs, usp_InsertExcetionLog and usp_GetXMLReference contained hard coded references to the original database. Code example of usp_GetXMLReference:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [BANDS].[dbo].[usp_GetXMLReference]
(
@Group_ID INT,
@Name varchar(100),
@Desc varchar(500),
@Order int,
@Effective DATETIME,
@Active bit,
@XMLOut xml output
) AS

BEGIN TRY

INSERT INTO [dbo].[Reference]
([Group_ID]
,[Name]
,[Description]
,[Sort]
,[Effective]
,[Active])
VALUES
(@Group_ID, @Name, @Desc, @Order,
GETDATE(), @Active)

SET @XMLOut =
(
SELECT [Group_ID]
,[Name]
,[Description]
,[Sort]
,[Effective]
,[Active]
FROM [PALS].[dbo].[Reference] AS Reference
WHERE Group_ID = @Group_ID
FOR XML AUTO
)

END TRY
BEGIN CATCH
EXEC usp_InsertExceptionLog
END CATCH


So, the new Bands_Test database was trying to use the Bands assembly. That will not work.

All we had to do is go back and take all the hard coded database names out of the references.

Creating the Identity Column as the Primary Key

So, after my attempts at changing a currently existing column to the identity column blew up my table, I had to rescript the table. This time I included the identity column as the primary key. Here's the script:

GO
CREATE TABLE [dbo].[Managers](
[Manager_ID] [smallint] IDENTITY(100,100) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](5) NULL,
CONSTRAINT [PK_Manager_ID] PRIMARY KEY CLUSTERED
(
[Manager_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Getting a Stubborn Table to Truncate

We had a situation today where we need to take an unused column out of a table. The table had a foreign key that would not let us truncate. To get around the problem, our Lead Developer did this:

Did a CREATE TO script out for the foreign key
Dropped the foreign key from the table
Dropped the table from the data base.
Truncated the table
Reattached the table
Ran the CREATE script against the newly attached table to restore the foreign key.

It worked pretty easily. It seems to be a really quick way to do it without getting into more complicated methods.

Identity Property

Today I learned a few things about the Identity attribute.

Generally, it's good practice to put an Identity column on every table in the database so there's always a unique records in atleast one column, and because SQL is better at doing JOINs on integers than alphabetical columns. So says our senior database developer.

The first goofy thing I did was create a table with no identity column. I couldn't figure out how to add it after the fact so I dumped the table and started over.

This time I included Identity command in the table creation script.

USE Bands
CREATE TABLE [dbo].[Location](
[Location_ID] [int] IDENTITY(100,100) NOT NULL,
[Statement] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

The (100/100) is the amount of incrementation to be added to the base number and seed, or starting point, i.e. my identity will roll in increments of 100 and it will start with 100. If you don't have both an increment and a seed, the script won't run and the table won't be completed.

Problem With TATWP_JumpMenu Web Part Fixed

Yesterday our UI designer applied the fix I found to the SharePoint new master page and it worked on her machine just fine. But when she sent the test around the "I Need To..." still wasn't working.

All she forgot to do was "approve" the change in the SharePoint workflow then it worked perfectly.

I actually contributed to something. I'm so happy.

Sharepoint "I Need To..." Web Part

Well, today I didn't get to do any SQl because we were in discovery on our tasks, but I did try to solve a bug that popped up last week.

Our intranet home page keeps throwing an error on the "I Need To..." web part. It turns out to be a common problem as I found out by searching the web. There's little file called "portal.js" that doesn't get included when someone modifies the default master page. However, the action button on the "I Need To..." web part calls portal. js. So if it can be found, the web part "I Need To..." won't work.

Solutions can be found at:

Microsoft Forums-I Need To web part error

splash.hta

So I'm trying to install my 180 day free trial of SQL Server Enterprise edition that comes with the MCTS training kit on my home computer and I can't get the CD to autorun.

I do the obvious thing and explore the CD and find what looks like the executable file and click on that. It still won't open.

I dig around the internet and find out that the installation wizard actually has to start through a file on CD called splash.hta. Only I can't get my machine to recognize an .hta file. I keep getting this error:

Splash.hta
The parameter is incorrect.

Hey, thanks error message writer guy, for the cryptic note.

Turns out I need to associate the .hta with it's .exe file which happens to reside in Windows\system32. This coy file is named mshta.exe. So I associate it and it works.

The installation goes forth and prospers until the System Configuration Checker in the SQL Server Installation Wizard reminds me I forgot to enable IIS.

Easily done in add/remove Windows components. Voila. Now, we can get down the to the business of a full install.