Michael J. Swart

October 28, 2008

‘SQL Server How To’ FAQ

Filed under: Technical Articles — Michael J. Swart @ 5:40 am

Every day I get tons of questions in my inbox asking about all things SQL Server. Just kidding. I usually get zilch. So I thought that I’d answer some questions suggested by Google. Using Google’s toolbar, I typed “sql server how to”. And then I took the top ten suggestions that Google gave me:

So here they are in the order that they were suggested:

1. sql server how to buy

Well Microsoft explains this better than I could: The first Google hit sends you to a web page (not surprisingly) called Microsoft SQL Server 2005: How to Buy.

There’s also a FAQ on Pricing and Licensing. The FAQ was written for SQL Server 2005, but the information applies to SQL Server 2008 as well.

2. sql server how to create a table

This is an important question for people starting out. I usually execute a script. Using Management Studio, open a new query window and then use the syntax described in the help for the CREATE TABLE command.

Here’s a simple example of what that would look like.

CREATE TABLE mytable (
column1 int not null primary key,
column2 nvarchar(max) null

You can also use Management Studio to design your table. In Object Explorer, click New Table as shown here and work with that:

Another technique involves storing the results of a select command and giving it a name. This is the INTO clause of the SELECT command. For example:

SELECT firstname, lastname
INTO newTableName

3. sql server how to backup table

Well, there is no BACKUP command that applies to tables alone (Don’t you want to back up the whole database?), but if you want to export the data contained in a table to an external location then I think the easiest thing to do is to go through the Export Data wizard in Management Studio and choose a flat file destination:

But my personal preference is to use the BCP utility. It’s a command line tool that is used something like:

>bcp dbname.dbo.USERS out c:\temp\users.dat -T -S SERVERNAME -n

4. sql server how to allow remote connections

This is a question where we should mentally step back a bit. Chances are you’ve received an error message like the following:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. Provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

In my experience, the problem is almost always that the server is not accessible. Try pinging the server. Make sure the server’s SQL Server Browser service is on. Checking spelling etc…

If you really want to ensure that remote connections are enabled use the following:

EXEC sp_configure 'remote access'

to view the setting and

EXEC sp_configure 'remote access', 1

to turn it on.

Another thing I sometimes forget is to check that the DB supports the TCP protocol. I do this using the SQL Server Configuration Manager. It’s important to remember because that protocol is not enabled by default.

5. sql server how to install

I don’t know how to answer this one really. Assuming that you’ve got a DVD or some other media containing the software, pop it in and follow the wizard. There’s some tricky stuff in there like choosing credentials for services. But when you get to a point you’re unsure of, hit F1.

Here are the official docs:

6. sql server how to lock a table

Again, take another mental step back. We should ask why you need to lock a table. Presumably you want to prevent access to the table to avoid:

  • Prying eyes?
  • Inconsistencies etc…?

If it’s prying eyes you want to avoid. Then SQL Server security is the thing you want to study. You may eventually find yourself issuing something like

DENY CONTROL ON [tablename] TO [public]

If it’s data inconsistencies, then you want to study transactions. Once you do, you have a better idea of what you want to do. Maybe start here.

7. sql server how to backup database

People have
written textbooks and given week long lecture series on this topic. But as a start look at the
BACKUP Database syntax

A short example for a full backup looks like:

BACKUP DATABASE mydatabase TO  DISK = N'C:\temp\mydatabase.bak'

Consider the above a kind of “Hello World” example.

8. sql server how to execute stored procedure

Say you’ve got the name of a stored procedure and hypothetically it’s called s_UpdateCalculations. It takes one integer parameter called @options and a bit parameter called @flag.

In a query window, the best way to execute that stored procedure is to type:

EXEC s_UpdateCalculations @options=1, @flag = 0

Or more simply

EXEC s_UpdateCalculations 1, 0

Then hit the Execute button.

s_UpdateCalculations 1, 0

will also work.

9. sql server how to index

Again, this is a huge topic. It’s also as much as an art as it is a science. But assuming that you’ve done the work and determined that you need an index on column A on table B. You would use:


Here’s the on-line help for CREATE INDEX.

10. sql server how to update identity column

Values in identity columns cannot be updated. This is by design. But if you really really must change the values in that column table, then one way is to use Management Studio to:

  • open the table designer
  • turn off the identity,
  • save
  • make your updates,
  • open the table designer again
  • turn on the identity again
  • and save

This can be time consuming, especially if there’s a lot of data in that table. This is because to change the table, Management Studio copies the entire table to a temporary one, drops the old one and renames the temporary table to the original name. It then repeats the whole process when adding the identity column back.

October 21, 2008

Reporting Services 2008 is even prettier…

Filed under: Technical Articles — Michael J. Swart @ 8:56 am

… with SSRS Report Builder 2.0 which RTM’d today. Downloadable here. If the collaboration with the Microsoft Office guys wasn’t obvious before it certainly is now.

October 15, 2008

Reporting Services is Fun Again…

Filed under: Technical Articles — Tags: , , , — Michael J. Swart @ 12:51 pm

… or at least fun for the first time. Earlier, I wrote about the limitations that I came across with SQL Server Reporting Services (SSRS) 2005 when trying to implement a Pareto chart in the exact way that I envisioned.

Well I’ve been trying out SSRS 2008 and I was surprised (in a good way). The things that should be easy are easy and straightforward. The things that are hard are at least possible.

In the past, Crystal Reports and earlier versions of SSRS have caused me to waste a few hours wrestling with different settings. But with SSRS 2008, for the first time, my experience with reporting tools was not an exercise in coping with frustration.

What’s changed?

One big change is the set of charts that are available out of the box. These charts seem to have a 1-1 mapping between the charts found in Excel 2007. There must have been collaboration between SQL Server folks and the Office folks. There’s also an overhaul in the UI and dialogs that are used to specify properties of the charts.

Example / Tutorial

I’ve included a video (2.5 minutes, 1.5 MB) of my experience with creating a Pareto chart to the way I like it:

Watch this video

I’ve also included a video (2.5 minutes, 1.5 MB) of what it takes to add a table underneath the chart in order to more closely match the reports that come with SSMS.

Watch this video

Find the query I used in the comments section.


You can’t include these reports (i.e. reports created with the 2008 report designer) into Management Studio’s custom reports. I guess SQL 2008 is still using 2005’s report control. The issue is described in the Microsoft Connect bug #356519.

October 6, 2008

Generating Server-Side Traces Should Be Easier

Filed under: Technical Articles — Michael J. Swart @ 4:50 am

SQL Server Magazine has an article this month by Andrew Kelly called Generating Server-Side Traces See how easily you can capture trace data without SQL Server Profiler. The article has four code listings (including one condensed code listing “for the sake of brevity”) After reading the article, I believe it’s not difficult, but I get the feeling that it could be easier.

This isn’t the first time I’ve struggled with server side traces. I talked about having an xml trace definition file on Microsoft’s connect site. And that in turn came from a discussion on the microsoft.public.sqlserver.server forum here.

Kelly’s article this month talks about how to export the trace definition from within Profiler by scripting the commands used to create the trace. It’s workable, but it’s not elegant. Code (the sp_trace* commands) seems to be getting mixed up with data (the trace definition) here. It would be nice to have a trace definition stored as data, not code.

Right now, in SQL Server 2008, there’s a hint of an xml trace definition, but it’s only used in the context of the SQL Server’s 2008 Data Collector. Here’s an example of what that trace definition looks like:

<ns:SqlTraceCollector xmlns:ns="DataCollectorType" use_default="0">
  <EventType name="Security Audit">
    <Event id="14" name="Audit Login" columnslist="1,9,6,10,14,11,12" />
    <Event id="15" name="Audit Logout" columnslist="15,16,9,17,6,10,14,18,11,12,13" />
  <EventType name="Sessions">
    <Event id="17" name="ExistingConnection" columnslist="1,9,6,10,14,11,12" />
  <EventType name="Stored Procedures">
    <Event id="10" name="RPC:Completed" columnslist="15,16,9,17,2,10,18,11,12,13,6,14" />
  <EventType name="TSQL">
    <Event id="12" name="SQL:BatchCompleted" columnslist="15,16,1,9,17,6,10,14,18,11,12,13" />
    <Event id="13" name="SQL:BatchStarting" columnslist="1,9,6,10,14,11,12" />
  <Filter columnid="10" columnname="ApplicationName" logical_operator="AND" comparison_operator="NOTLIKE" value="SQL Server Profiler - de0305e7-9fbf-4a52-8487-2aab91a78d76" />

You can get at these trace definitions by exporting from Profiler’s File menu as shown here:

You can use this trace definition with a custom procedure or app to build your trace. So far this is not one bit easier than the standard way described in SQL Magazine this month. In fact it’s a lot harder.

What Would Be Nice:
I’ve got a few ideas that would make things easier:

  1. Allow Profiler to export trace definition files in XML format
  2. Extend the system table sys.traces to include an xml field containing the trace definition.
  3. Create a new stored procedure (maybe called sp_trace_ApplyDefinition) which takes a trace definition and a trace handle and applies events and filters to it.
  4. Extend the trace definition to include extra trace information like tracefile, maxfilesize, stoptime, filecount etc…

If I had everything on my Christmas list, then Profiler trace management would be so much easier. Andrew Kelly’s article would have been shorter.

Visit Microsoft’s connect site here to vote for this suggestion.


Powered by WordPress