Author Archives: jeffprom

Renaming SQL Server

Posted on by jeffprom

When you setup a server for replication you may find unexpectedly that SQL complains about your server name. The most likely reason is that the SQL Server name doesn’t match your machine name.
Run the following command to take a look:
SELECT @@SERVERNAME, SERVERPROPERTY(‘ServerName’)

The results should be identical. If they are different, you will need to rename your SQL Server name to match the machine name. To do this use the following:

use master
go
-- Remove old Server Name
exec sp_dropserver ‘OLDNAME’
GO
-- Add New Server Name
exec sp_addserver ‘NEWNAME’,’LOCAL’

You then need to restart SQL Server. Now open a new query and run the following again:
SELECT @@SERVERNAME, SERVERPROPERTY(‘ServerName’)
They should now be the same.

Null values when importing from Excel with SSIS

Posted on by jeffprom

I’ve written SSIS packages that import data from Excel spreadsheets and load them into SQL. However, I recently came across a situation where half the data in one of my columns were having their data values ignored and was written to the DB as Nulls. I tried changing the excel column format, etc with no luck. Perplexed I did some searching and came across an interesting little quirk. Basically when you load a column it reads the first 8 rows and tries to determine what it should load the data values as. Now if your data has a mix of characters and numbers, you need to make sure you reflect this in the first 8 rows or it will not load correctly. The official explanation and workarounds can be found here.

Database Mail

Posted on by jeffprom

One great way to keep track of failed jobs is to setup email notifications. In SQL 2000 the email system was called SQL Mail. In SQL 2005 it is called Database Mail and is very different. Here are some instructions to setup Database Mail and some nifty queries to manage it.

Setup Database Mail:
1. In Management Studio expand the Management folder.
2. Right click on Database Mail, Configure Database Mail.
3. From the main menu you can add and edit profiles and accounts.
4. For a new system select the top option of ‘Set up Database Mail by performing the following tasks:’
5. Click Yes to enable the mail feature.
6. Fill in the appropriate fields with your mail info, choose defaults for the rest and make your way to the finish page.
7. Now right click on SQL server Agent, Properties, Alert System.
8. Check the box ‘Enable mail profile’. Your mail profile name should show up.
9. Restart the SQL Agent Service from within windows.
10. You can send a test by right clicking Database Mail, Send Test E-mail.

Setup an Operator:
1. Under SQL Server Agent right click Operators, New Operator.
2. Pick a name, and fill in the E-mail name (address).

Setup Job Failure Notifications:
1. Now under Jobs, select a job and right click properites, Notifications.
2. Check the E-mail box, select your Operator from the drop down, and select ‘When the job fails’.

Now your job will notify you via email if it fails. But what’s happening behind the scenes with the mail system? Here are some queries that let you see what’s going on.

USE msdb
GO

-- Show Mail Event Log
SELECT event_type AS [Type], log_date AS LogDate, [Description]
FROM sysmail_event_log
ORDER BY log_date DESC

-- Show Sent Messages
SELECT recipients AS [To], [Subject], Body, sent_status AS [Status], sent_date AS DateSent
FROM sysmail_sentitems
ORDER BY sent_date DESC

-- Show Failed Messages
SELECT recipients AS [To], [Subject], Body, sent_status AS [Status], sent_date AS DateSent
FROM sysmail_faileditems
ORDER BY sent_date DESC

-- Show All Items
SELECT recipients AS [To], [Subject], Body, sent_status AS [Status], sent_date AS DateSent
FROM sysmail_allitems
ORDER BY sent_date DESC

-- Show Unsent Messages
SELECT recipients AS [To], [Subject], Body, sent_status AS [Status], sent_date AS DateSent
FROM sysmail_unsentitems
ORDER BY sent_date DESC

And there you have it. You can now setup email notifications and see what the email system is doing.

SSRS Failed Subscription Notifications

Posted on by jeffprom

Reporting Services can email reports to end users on a regular basis without allowing them access to the web portal. This gives you a little more control over the report and can make it easier for the end user. One problem is that with numerous reports scheduled with numerous parameters to numerous recipients it can be difficult to know if/when a report failed to be sent. It looks bad when the VP keeps asking why he didn’t get his report today.

SSRS subscriptions are very picky. If you have one invalid email address in a subscription with multiple recipients, the whole subscription fails and nobody gets the report. Likewise, if one of your report parameters changed the report will not be sent. Surprisingly there isn’t an out of the box solution to see what reports failed to run.

Here is a query I wrote that checks SSRS subscriptions and returns any failed reports. Use the database you setup for Reporting Services. (default is ReportServer)

SELECT C.Name, S.LastRunTime, S.LastStatus, S.Description
FROM Subscriptions AS S
LEFT OUTER JOIN [Catalog] AS C
ON C.ItemID = S.Report_OID
WHERE LEFT (S.LastStatus, 12) != ‘Mail sent to’
AND LEFT (S.LastStatus, 12) != ‘New Subscrip’

The first time I ran this we had 4 reports that had failed and we weren’t aware of it! I’ve set this up as an SSRS report and view it daily, and have it emailed to me so I know all my reports were sent.

Setting up Transactional Replication

Posted on by jeffprom

While this method does seem to have some security concerns, it should get you up and running. This was written to be used with SQL 2005 and Windows Server 2003.

From the remote server:
Configure Server policy and permissions:
1. Share a folder. In our case we’ll call it E:\Replicator (under Sharing, Permissions)(also under Security tab)
add anonymous and everyone, and give full control
2. In management studio right click your DB to replicate, properties, permissions, sa (or whatever your sysadmin account is)
set connect, and connect replication = Grant
3. From windows, Start, Run, gpedit.msc, Windows Settings, Security Settings, Local Policies, Security Options
Network access: Shares that can be access anonymously (add the word Replicator at the end)
Network access: Restric anonymous access to Named Pipes and Shares: Disabled

Configure Distribution:
1. Replication -> Local Publications
right click, configure distribution
2. server will act as it’s own distributor
3. snapshot folder = \\serverIPAddress\Replicator (use the UNC and NOT E:\Replicator)
4. Distribution database name: distribution
keep default locations
5. Next, next, finish

Configure Publication:
1. New Publication
2. DBname
3. Transactional Replication
4. Pick all tables and stored procedures if you like.
5. skip filter. next
6. create snapshot immediately (only need to do a snapshot initially)
7. snapshot agent security settings:
Run under the SQL Server Agent service account
By impersonating the process account
8. Next
9. Create the publication, Next
10. publication name: someUniqueName, Finish

From the main server:
Configure Subscription:
1. New Subscription
2. Publisher, Find SQL Server Publisher, choose remote server and login as sa
3. select publictation, next
4. Run each agent at its Subscriber (pull subscriptions)
5. Subscriber (main server)
6. Subscription Database = NEW DATABASE (not default!!!)
Database Name: someUniqueName
7. OK, next
8. Click the … on the far right to set security
Run under the SQL Server Agent service account.
Using the following SQL Server login: sa account
9. Agent Schedule: Define schedule (in our case we set it up for daily, every 5 minutes)
10. Initialize Immediately
11. Create the subscription
12. Go to your jobs and find the new scheduled job. you can run the job and view history to see if it’s working.

Fixing Torn Pages

Posted on by jeffprom

Recently I was approached to help fix an accounting server problem. Now keep in mind I wasn’t managing this server and hadn’t touched it before. The DB was on SQL Server 2000. After attempting to reindex some tables it was apparent there were some serious issues. Looking in the server’s Event Viewer, the Application Log showed the following:

Error: 823, Severity: 24, State: 2 I/O error (torn page) detected during read at offset 0x00000z030b0000 in file ‘D:\dbFileName.MDF’

This means there were some corruption issues. Reading a lot of posts on the web, people said it wasn’t possible to fix and should just restore from the last backup. Now suppose there wasn’t a good backup to restore from… let’s just PRETEND that could happen. :/

Here is what I did to FIX the server. There were 944 tables on their database. I detached the DB and copied it to a SQL 2005 server and re-attached. In management studio I did a properties on the DB and went to options. I changed the Compatibility level to SQL Server 2005 (90). Then right clicked the DB, Reports, Standard Reports, Disk Usage by Top Tables. This gave me a report of the tables most used. Turns out There were only about 25 tables with a significant amount of data.

I then did a DBCC CHECKTABLE (“tablename”) on the top 25 and found out which ones had issues. I had 21 that needed to be fixed. Now off of to fix them.

Back on the SQL 2000 server I right clicked on the DB, All Tasks, Generate SQL Script, Options, and checked everything, General Tab, Preview. I copied this to a separate text file and saved it for later.

I renamed the corrupt database to something else. I did this by detaching the DB, and reattached using a new name. I then took my DB script and ran select portions to recreate the shell for the clean DB with the original name. I right clicked the corrupt database and selected All Tasks, export data. The trick is to copy all the data and objects from the old database to the new one. On the specify Table Copy or Query screen choose the 3rd option of Copy objects and data between SQL Server databases. This will take all the data and indexes and put it into the new database. After everything is copied and verified, you can eventually detach the corrupt DB and delete the files.

It’s been a week now and the accounting system is working great.

What causes torn page errors?
If the TORN_PAGE_DETECTION option is turned on, it flips a bit for each 512-byte sector written to disk. This allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages. A torn page can occur if the system crashes between the time the operating system writes the first 512-byte sector to the disk and the completion of the I/O operation. If the actual value read from disk is different than what’s stored in the page header, it results in a torn page error.

If you are using SQL 2005 you can run the following query which shows pages suspected as being bad with an 823 or 824 error:

USE msdb
GO
SELECT * FROM dbo.suspect_pages