Archive for October, 2008

Soaking in SQLServerPedia

One of the bloggers I follow is Brent Ozar. I tell you I’m a fan. I like the way he writes his stuff.

Anyway, he blogged about The Problem With SQL Server Training Today, and The Answer to SQL Server Training Problems a week or so ago. And this is the answer – SQLServerPedia  – SQL Server Knowledge and Advice Straight from the Experts.

So in between giving out candies to cute little Indiana Joneses, pirates of the Caribbean, vampires and dead cheerleaders, I’ve been soaking in SQLServerPedia (geeky ‘no?). So far, so good. I’ll be a frequent flyer.

 

PS – and if you’re a DBA, you’ll find great laughs in this post : Somebody Created a Halloween Monster #SQLputdowns

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Problem:

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, ‘SQLVM\SQL02’. (Replication.Utilities)

Scenario:

1. We configured database mirroring on 2 SQL Server instances.

2. We changed the computer name from SQLVM to SQL2K5.

3. We tried launching the Database Mirroring monitor, but we got the following error:

SQL Server replication requires the actual server name to make a connection 
to the server. Connections through a server alias, IP address, or any other 
alternate name are not supported. Specify the actual server name, 'SQLVM\SQL02'. 
(Replication.Utilities)

If you just look at the error, it sounds a bit misleading because we don’t have replication configured in either of the servers.

Resolution:

-- 1. drop old server/instance name
EXEC sp_dropserver [sqlvm\sql01] 
GO
 
-- 2. add newserver/instance name
-- local meaning we're adding the local machine
EXEC sp_addserver [mysql2k5\sql01], local 
GO
 
-- 3. Restart SQL Server service

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

SQL Server-Related Cheat Sheets That Can Save You Time

I previously posted about a SQL Server CheatSheet from DotNet4All that I thought was useful and comprehensive. If you already know what you need to do, but just forgot the syntax because you haven’t done it in eons, then  a well organized, concise, complete cheat sheet can save the day.

 

Here are a few other ones:

General SQL Server
DotNet4All

SQLAuthority
Scribd T-SQL

 

SQL Server Shortcuts
SQLArticles (really good, exhaustive list!)
SQLAuthority SSMS Shortcuts

 

Connection Strings
ConnectionStrings.com

 

Regex (especially when you’re working with SQLCLR/Regex)
.NET Regex Cheat Sheet
AddedBytes
(AddedBytes used to be known as ILoveJackDaniels, but that name has changed. Dave has a *lot* of useful cheat sheets in his site.)

 

SQL Injection
Ferruh Mavituna
ha.ckers

 

PowerShell
MSDN Blogs (PDF)
MSDN Blogs (MS Word)

 

SQL Server 2008
SQLAuthority

 

SQL Server DBA Interview Questions
SQLAuthority Interview Questions – Complete List

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Problem:

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database <your db name> cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed."

Scenario:

Sometimes you need to have "exclusive" access to the database, let’s say,

  • when you want to force a restore, or
  • when you want to alter the database and an exclusive lock is required

To get this exclusive access, you may need to set the database to SINGLE_USER mode:

USE [master]
GO
 
ALTER DATABASE [DBName]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

However, what if you accidentally lock yourself out of the database?

What if for some reason there is some other connection or user that is using the database, you will most probably get this error :

"Msg 5064, Level 16, State 1, Line 1

Changes to the state or options of database <your db name> cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed."

How do you get yourself back in?

Resolution:

1. Determine which connection is actually locking you out

-- look for the connection that uses your db and note the spid
EXEC sp_who

 

2. Kill the connection

KILL <spid>


3. Change the database back to MULTI_USER

ALTER DATABASE [DBName]
SET MULTI_USER;
GO

That should do it. Happy

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Is a Low NULL Diet healthy?

It’s funny. I wonder which database they are using Light bulb

http://thedailywtf.com/Articles/A-Low(null)-Diet.aspx

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

How to Connect to an Instance Name Using SMO

1. Create your Visual Studio Project

2. Add the following references in your project

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoEnum
  • Microsoft.SqlServer.SqlEnum

3. Include the following using statement:

using Microsoft.SqlServer.Management.Smo;

4. Add your server object. Normally, you would use the following format in your Server object:

String serverName  = "JUBILEE";
Server srv         = new Server(serverName);
Database db        = srv.Databases["AdventureWorks"];
txtServerInfo.Text = srv.Information.VersionString +
                     System.Environment.NewLine;

However, if you have an instance name, you have to specify the SERVER\INSTANCE name in your Server object. In C# code:

String serverName = “JUBILEE” + @”\” + “SQL01“;

Server srv         = new Server(serverName);
Database db        = srv.Databases["AdventureWorks"];
txtServerInfo.Text = srv.Information.VersionString +
                     System.Environment.NewLine;

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Here is a partial list of valid XSD data types for SQL Server 2005:

xsd:int or xsd:integer
xsd:decimal
xsd:date
xsd:dateTime
xsd:string
xsd:base64Binary

 

Note that in SQL Server 2005, the timezone needs to be included in the date or dateTime element or attribute value, for example:

<Authors>
    <Author AuthorID="1" FirstName="John" LastName="Doe" 
            IsIndependent="true" 
            DateJoined="1992-05-01Z" DateTimeJoined="1992-05-01T00:00:00Z"/>
</Authors>

Note the existence of the letter Z at the end of the date and datetime attributes.

 

Microsoft also provided a list of schemas in http://schemas.microsoft.com/sqlserver/. It includes the following AdventureWorks sample schemas:

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Clustering SQL Server 2005 Videos … and some hacking

Brian Knight has posted a lot of good videos on JumpStartTV, including the following clustering videos:

Plus one video on how to hack (read: SQL Injection), and prevent hacking, SQL Server:

These are good videos, some of them almost an hour long like the Hacking video, most in the 5-15 minute duration. What’s also good about the site is  on the sidebar, the “pre-requisite” videos are mentioned.

JumpStartTV also has videos on Administration, High Availability, Performance Tuning, SMO, Data Warehousing and SSIS (Brian Knight made a whole bunch on SSIS), to name a few.

The videos are free, but JumpStartTV requires registration.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
`