Moving a Table to Another Schema

ALTER SCHEMA CompCredSurvey --this is new schema
TRANSFER MinEducationValuesLookup --this is the table you're changing to the new schema
GO

ADD a Column, Integer with a NOT NULL and a DEFAULT value

ALTER TABLE [CompCredSurvey].[2004Survey] ADD SurveyYear int not null default (2004)

I tried this without the brackets like the examples I found on the web, but SQL wouldn't accept it. Once I bracketed it worked fine. You might have to bracket the column name a well [SurveyYear].

Change a Column to the Primary Key

ALTER TABLE tablename ADD PRIMARY KEY (columnname)

*the new primary key column cannot be set to accept nulls

Importing Excel: Why Can’t I Get My SSIS Package to Work?

I used the TechRepublic tutorial at http://blogs.techrepublic.com.com/datacenter/?p=205 to learn how to import an Excel dataset. It’s a good tutorial with pictures, which is what I needed since my DB skills are totally lame.

It worked perfectly on my test run. I easily imported a .xls that was sitting on my desktop to my local instance of SQL Server. Congratulations me.

But when I tried to use the tutorial today at the office, the same procedure threw me an exception error. And an ugly one at that:

Error: 0xC0202009 at Data Flow Task(infraction action), SQL Server Destination [3600]: An OLE DB error has occurred. Error code: 0x80040E14.

The problem is that the tutorial has you build a destination using the SQL Destination part. Well that works fine if you are importing local to local, but if you are sending something to a networked server it gives the error. You actually need to use the OLE DB Destination instead.

Thanks to the guys posting at the MS Forums who confirmed this.

Importing an Excel File: Error With XLSX

I'm learning how to make SSIS packages. My first three jobs are all data imports from Excel spreadsheets.

Two of the three files were created in Excel 2007. SQL Server Business Intelligence for SQL Server 2005 cannot recognize .xlsx files. In order to set up data sources and data connectiosn in BIDS, you will have to save the file as an older format .xls file.

Error - "Backup set holds a backup of a database other than the existing database."

I had trouble restoring a CONTACTS database onto a location on a different server than the one it was originally created on. The entire point was to get an instance of that database onto another box.

I copied the .BAK file for the CONTACTS database onto the new server and attempted to run the Restore wizard but got a 3154 error - "Backup set holds a backup of a database other than the existing database."

I tried making a empty, new CONTACTS database. Same error from the wizard.

The problems seems to be that the backup path for the original CONTACTS was on the C:\ drive on the orignial server, i.e. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backups\contacts.bak

While the backup path for the new server is D:\Program Files\MSSQL.1\MSSQL\Backup\

The wizard couldn't find the path, I think.

The solution was to script a RESTORE with a REPLACE. Here's the script I used. Thanks to Pinal Dave on this one.

RESTORE DATABASE CONTACTS
FROM DISK = D:\Program Files\MSSQL.1\MSSQL\Backup\contact_20080801.bak'
WITH REPLACE

Worked perfectly.

I Can’t Get My SQL Database to Backup to Another Server on the Network

We ran into a problem while trying to backup our development and user testing databases to a data storage share on another server in the network. We checked and double checked location information, the backup plan, user roles and permissions, and so forth and couldn’t find the solution. The answer has to do with shared folders.
Because the location for the backup files in on a remote server, the following must be completed for the maintenance plans to run correctly:
1. The File storage folder on the remote servers must be a shared folder.
2. To create the file storage folder, you must log on remotely to storage share and create the shared folder there. You cannot create the shared folder from another box.
3. You must add both a logon for backup job and a logon for the hardware box the data is coming from to the shared folder permissions.
4. Obtain the log on for the hardware box the data is coming from by remote logging onto the box and checking the Administrative Tools>Services > SQL Server Agent>Properties>Log On for the instance of SQL that is being backed up. CTRL+C this to the clipboard.
5. Remotely log on to the server receiving the backup and navigate to the shared folder created in step 2. Open the Properties on the shared folder and go to Sharing tab>Permissions button. Add the log on for the box obtained in step 4 and the log on used by the Maintenance Plan job (the sql admin account). Save and log off the storage share.
Run a test backup and check the shared folder to see if the BAK, TRN or other file is there.

*I take absolutely no credit for finding this solution. Thanks to the team.

Backups Quick Info From MSDB

I needed to find out if a backup was done, but didn't have the full name of the database because they all have similar names on that server. Here's script to figure that out fast.

select * from msdb.dbo.backupfile
Where logical_name like'%DATABASESEARCHTERM%'


--Where it says DATABASESEARCHTERM just put in something you think is close.

Transact-SQL to Create, Add Entries To, and Update a Table

Today we realized we need to change the entries in a reference table because the .ASPnet developers need to call one of the categories in the reference table something else. You can make entries right in the tables in SQL but its a better practice to script out such changes.

Well, I had to figure out how to do that because I've never had to change just one record column entry string into something else. Here's my practice on that.

--first I created a sample table to test against--

create table Managers
(FirstName varchar (100) not null,
LastName varchar (100) not null,
OfficeLocation varchar (50) null)

--then I inserted some content--

insert into Managers (FirstName, LastName, OfficeLocation)
values ('Jason', 'Peabody', 'New York')

insert into Managers (FirstName, LastName, OfficeLocation)
values ('Heather', 'Ace', 'Burbank')

insert into Managers (FirstName, LastName, OfficeLocation)
values ('John', 'Grass', 'Burbank')

select * from Managers


--now I want to go back and change all entries for Burbank to Beverly Hills because we've leased a new building and everyone moved into it this week. First I run a select all to see how many records there are to change.--

SELECT *
FROM Managers
WHERE OfficeLocation = 'Beverly Hills'

UPDATE Managers--the tablename
SET OfficeLocation = 'Beverly Hills'--the new entry
WHERE OfficeLocation = 'Burbank'--the old entry

select * from Managers --check it

XML Assembly Won’t Work – Possible Fix

One thing I forgot to mention on my entry about the XML assembly is that CLR integration must be turned on the SQL server. To turn on CLR:
Connect to the SQL Server

Go to SQL Server 2005, Configuration Tools.

Go to the Surface Area Configuration/Surface Area Configuration for Features.

Click on the box to Enable CLR

(And while you are in there also enable the xp_cmdshell. You will probably need it later.)

How to Find Which Service Pack SQL Server is Running

This is pretty simple:

SELECT @@VERSION
GO

Hey, that was easy.

Connection Failure When Setting Up Named Instances on Remote Servers

Here's an interesting thing I learned today.

We set up a named instance of SQL on our development box. The install was accomplished easily however, afterward we could only connect to the new instance NEWSQL directly from the hardware box. We could not get a connection to NEWSQL via SQL Server Management Studio. The NEWSQL instance didn't appear in SSMS through browsing for it etc. This problem persisted even though we were logged in as a system admin.

Here's is what caused the problem:

When a default instance of SQL is created on a box, the port is automatically set to static port 1433. But when named instances are created, the installation assigns the instance to dynamic ports.

In order for the instance to be "seen" and by SSMS and other applications you must go into the server box, open Adminstrative Tools, then Services.

Then click on the SQL Browser Agent and change the status to enabled. The Browser is the component that will find and track SQL instances on dynamic ports.

Adding Extended Properties Using the System Stored Proc

I'm working on adding extended properties to our database project. There are so many tables with huge numbers of columns and very few descriptions.

I found some code on the web for using the sp_addextendedproperty to extended properties without having to open each table or objectt. Its still tedious but is saving me about 30% on the time.

Here's the code snippet:

USE [NameOfDatabase]
GO

--Script to add an Extended Property to the Table
EXEC sys.sp_addextendedproperty
@name=N'NAMEOF_EXPROPERTY1',
@value=N'Used by CRM to support Account, Contact, Opportunity, Lead Entity Model, and Service Entity Model.' ,
@level0type=N'SCHEMA',
@level0name=N'dbo', --Schema Name
@level1type=N'TABLE',
@level1name=N'ActivityPointerBase' --Table Name
GO

EXEC sys.sp_addextendedproperty
@name=N'NAMEOF_EXPROPRTY2',
@value=N'Created by CRM application.' ,
@level0type=N'SCHEMA',
@level0name=N'dbo', --Schema Name
@level1type=N'TABLE',
@level1name=N'ActivityPointerBase' --Table Name
GO

EXEC sys.sp_addextendedproperty
@name=N'NAMEOF_EXPROPERTY3',
@value=N'Used by multiple entities.' ,
@level0type=N'SCHEMA',
@level0name=N'dbo', --Schema Name
@level1type=N'TABLE',
@level1name=N'ActivityPointerBase' --Table Name
GO

--COLUMN SECTION STARTS HERE
--Script to add an Extended Property to a columnEXEC EXEC sys.sp_addextendedproperty
@name=N'EXPROPERTY_Column',
@value=N'Uses as a FOREIGN KEY constraint.',
@level0type=N'SCHEMA',
@level0name=N'dbo', --Schema Name
@level1type=N'TABLE',
@level1name=N'ActivityPointerBase',--Table Name
@level2type=N'COLUMN',
@level2name=N'OwningBusinessUnit'--Column Name
GO

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.