belle’s sql musings

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

Advertisements

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

Fixing Collation: The Experiments

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

We’re trying to solve a few collation issues we have been having, and I had collation overload this week.

The Scenario
•    Some of our SQL Server instanced were installed using the default collation SQL_Latin1_General_CP1_CI_AS
•    We need to have case insensitive, accent insensitive – Latin1_General_CI_AI
•    We changed some of our database collations to Latin1_General_CI_AI

The Problems

•    We want to change the model database’s collation, so that all new databases get the collation we want
•    We want to change the tempdb collation, so that all temporary objects don’t have to use explicit collation

We use temporary tables in some of our important stored procedures and UDFs. It will be an issue if we have different collations between our user databases and tempdb. Yup, trying to do a query that involved these two tables will result in an error similar to:

Cannot resolve the collation conflict between “Latin1_General_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation. (Microsoft SQL Server, Error: 468)

Background Check (more…)

Generating XSD from an XML File

Posted in SQLXML, Tools by belle on October 9, 2008

To create a schema (XSD) from an XML file, you can use a command line tool that comes with Visual Studio called xsd.exe (located in Install Directory\Microsoft Visual Studio 8\SDK\v2.0\Bin). If you’re running Visual Studio 2008, your path will be different.

Usage:

xsd.exe -
    Utility to generate schema or class files from given source.
xsd.exe <schema>.xsd /classes|dataset [/e:] [/l:] [/n:] [/o:] [/s] [/uri:]
xsd.exe <assembly>.dll|.exe [/outputdir:] [/type: [...]]
xsd.exe <instance>.xml [/outputdir:]
xsd.exe <schema>.xdr [/outputdir:]

Sample invocation:

C:\temp>xsd invoice.xml
Microsoft (R) Xml Schemas/DataTypes support utility
[Microsoft (R) .NET Framework, Version 2.0.50727.42]
Copyright (C) Microsoft Corporation. All rights reserved.
Writing file 'C:\temp\invoice.xsd'.
C:\temp>

The full options are as follows: (more…)

Tagged with: ,

SQL Server 2005 Cheat Sheet

Just came across this SQL Server Developer Fact Sheet made by Xander Zelders of DotNet4All. It’s a handy cheat sheet, with all the common stuff you need as a dev:

  • Data types, common string/system/date and time/math/cursor functions
  • Basic SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER syntax
  • SARGability
  • Concise checklist for creating indexes and fast queries
  • Execution plan icons (aptly color coded – red means no-no!)
  • … even connection strings!

Pretty handy and organized reference. Thanks Xander!

Tagged with:

What’s on your RSS Reader?

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

I love Google Reader. It feels like I’ve learned so much just by reading and skimming through blogs and articles that other people put out. It is also amazing how a lot of really smart, knowledgeable people write because they want to share their experiences and knowledge.

Anyway, these are some of the SQL Server sites and blogs I read and monitor (in no particular order):

I also really like reading

Fun Stuff!

I’m using Google Reader Discovery function to help me discover some more good blogs/sites on SQL Server, but it cannot keep up with me 😉

Follow Up:

I didn’t mean to forget – I also read Brad McGehee‘s articles a lot! He is the founder of SQLServerPerformance.com. Check out these articles : Brad’s SureDBA Checklist, Professional Certification for DBAs and the rest of his articles.