belle’s sql musings

Soaking in SQLServerPedia

Posted in DBA Toolbox / T-SQL Scripts, musings, Resources/References, Tutorials by belle on October 31, 2008

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

Advertisements
Tagged with:

Drop and Re-Add Server Registration When Renaming Computer

Posted in DBA Toolbox / T-SQL Scripts, Issues/Troubleshooting by belle on October 31, 2008

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

Tagged with:

SQL Server-Related Cheat Sheets That Can Save You Time

Posted in DBA Toolbox / T-SQL Scripts, Powershell by belle on October 17, 2008

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

Tagged with: ,

What to do if you accidentally lock yourself out of SQL Server (SINGLE_USER mode)?

Posted in DBA Toolbox / T-SQL Scripts, Issues/Troubleshooting by belle on October 17, 2008

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

Is a Low NULL Diet healthy?

Posted in musings by belle on October 16, 2008

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

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

How to Connect to an Instance Name Using SMO

Posted in SMO by belle on October 13, 2008

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;

Tagged with:

Valid SQLXML XSD Data Types, and Sample SQL Server XML Schemas

Posted in SQLXML, T-SQL Tips and Tricks by belle on October 13, 2008

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:

Tagged with: ,

Clustering SQL Server 2005 Videos … and some hacking

Posted in Resources/References by belle on October 11, 2008

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.

Order of Attribute-Centric Columns in FOR XML PATH matters

Posted in Issues/Troubleshooting, SQLXML by belle on October 11, 2008

Error:

Attribute-centric column ‘@Title’ must not come after a  non-attribute-centric sibling in XML hierarchy in FOR XML PATH.

Reason:

You are having this issue if you have attribute- and element-centric columns in your FOR XML PATH query that are interleaved, as such:

SELECT
    EmployeeID       AS '@EmployeeID',            -- attribute
    FirstName        AS 'Details/FirstName',      -- nested element
    LastName         AS 'Details/LastName',       -- nested element
    Title            AS '@Title'                  -- attribute
FROM
    dbo.Employee
FOR XML PATH('Employee')

Resolution:

Order of attribute-centric columns in your FOR XML PATH query matters … if you have nested elements.

To resolve the error above, you must specify all attributes first, then specify the nested elements

SELECT
    EmployeeID       AS '@EmployeeID',            -- attribute
    Title            AS '@Title',                 -- attribute
    FirstName        AS 'Details/FirstName',      -- nested element
    LastName         AS 'Details/LastName'        -- nested element
FROM
    dbo.Employee
FOR XML PATH('Employee')

/*
Sample Output:
 
<Employee EmployeeID="1" Title="Production Technician - WC60">
  <Details>
    <FirstName>Guy</FirstName>
    <LastName>Gilbert</LastName>
  </Details>
</Employee>
*/

Tagged with:

White Papers: Backup/Recovery, Performance Tuning

Posted in Issues/Troubleshooting, Resources/References by belle on October 11, 2008