SQL Server with Mr. Denny


June 16, 2008  7:12 PM

Replay URL for my GeekSpeak Session

Denny Cherry Denny Cherry Profile: Denny Cherry

For those who were unable to attend my Geekspeak session last week the session was recorded and that recording has been posted.

June 16, 2008  11:00 AM

Back To Basics: Service Broker Services

Denny Cherry Denny Cherry Profile: Denny Cherry

Services are used to bind contracts to queues.  They are also used to database to database, or server to server routing of messages via routes (we’ll talk about them later).  Unlike most other objects within SQL Server, the name of the service is case sensitive, no matter what collation you have set for your database.  When using server to server queueing the service names must be identical, including case as SQL Servers uses a hash of the service name to locate the correct service.

(Service names are not actually case sensitive if you arne’t using routing, but if you are going to start routing the messages later it’s better to start off getting them in the correct case.)

The syntax to create the service is pretty straight forward.

CREATE SERVICE ServiceName
ON QUEUE QueueName (ContractName)

If the service will only be the initiator then the contract name can be omited if preferred.  If you want to create a multi-user contact you can specify additional contracts by simply making a comma seperated list of contract names.

CREATE SERVICE ServiceName
ON QUEUE QueueName;

CREATE SERVICE ServiceName
ON QUEUE QueueName (ContractName1, ContractName2);

Denny


June 12, 2008  5:24 AM

Use caution when upgrading SQL 2008 CTP SSIS packages to RC0

Denny Cherry Denny Cherry Profile: Denny Cherry

A ran across a bit of a problem when upgrading my SQL 2008  CTP 5 (November 2007) SSIS Packages to SQL 2008 RC0.  Apparently Microsoft has changed the way that they handle the script tasks within the SSIS packages.  Because of this when I edit all of the script tasks within my SSIS package the scripts were all blank.

 The official fix from Microsoft is to install the older CTP version that you edited the scripts in on another machine and open the un-updated version of the SSIS package on that machine, and copy the code for the scripts into the RC0 version of the SSIS package.

The only object which I’ve had to do this on was a .NET Script Task.  I had this problem when going from CTP 5 to CTP 6 as well as from CTP 5 to RC0.  This will not effect migrating from SQL 2005 to SQL 2008 as SQL 2005 and SQL 2008 CTP 5 do not use the same scripting engine in the back end.

Denny


June 10, 2008  7:32 PM

SQL Server 2008 RC 0 Released

Denny Cherry Denny Cherry Profile: Denny Cherry

Microsoft has just released SQL Server 2008 RC 0.  This release should be pretty close to the final version expected out later this year.

Denny


June 10, 2008  6:39 PM

Article: SQL Server memory configurations for procedure cache and buffer cache

Denny Cherry Denny Cherry Profile: Denny Cherry

I’ve just published another tip over on SearchSQLServer.com.  This one is titled SQL Server memory configurations for procedure cache and buffer cache.

To determine how much memory your SQL Server database uses for buffer cache and procedure cache, factor in the SQL version you’re using, whether it’s x86, x64 or Itanium and the amount of memory allocated to the SQL Server instance. I talk about how the system configures memory and how it allocates the amount of buffer and procedure cache that’s available.Denny


June 9, 2008  11:00 AM

SQL 2005 Internal Query Error 8623 still unfixed

Denny Cherry Denny Cherry Profile: Denny Cherry

While working on a new part of our production system  we begun getting the following error message back from the SQL Server.

Server: Msg 8623, Level 16, State 1, Line 1
Internal Query Processor Error: The query processor could not produce a query plan. Contact your primary support provider for more information.

Now I’ve seen this error before, but it was supposed to have been fixed in Cumulative Update 3 for SQL Server 2005 SP 2 but apparently it hasn’t been.  I’m currently running build 3239 which is Cumulative Update 7.  (I’m running the x64 edition.)

The only difference between my query and what’s documented in the KB article is that I was doing a basic insert statement.

 INSERT INTO Table3
 (Table1Id, Table2Id)
 VALUES
 (@Table1Id, @Table2Id)

There are obviously no complex queries being run here.  However I did have relational integrity enabled through foreign keys.  I ended up having to remove the foreign key constraints from this table for the insert statement to work correctly.

Hopefully someone finds this info useful.

You can follow along with the Microsoft development teams progress on Microsoft Connect.  It’s feedback number 332815.

Denny


June 5, 2008  11:00 AM

Back To Basics: Service Broker Queues

Denny Cherry Denny Cherry Profile: Denny Cherry

The service broker queue is where the messages are stored after they are delivered, but before they are processed.  They are kind of like tables as you insert data into them (sending the message) and you view the data in them (receiving the message).  While tables can have triggers, queues can have what are called activated procedures.  If a queue has an activated procedure defined when the message arrives in the queue the queue runs the procedure.  No data is passed to the procedure, that procedure still needs to do a receive against the procedure (if that’s what actually needs to be done).

Setting up an activation procedure is a bit tricky as you have to have the queue to create the procedure, and you have to have the procedure to set it as an activation procedure.  So what I like to do is create the queue, then the procedure, then alter the queue to setup the activation.  This way I can send messages to the queue right away without worrying about getting errors because the procedure doesn’t exist.

Unlike tables, you do not get to define the structure of the queue.  All data is stored within the message_body field, so if you need to send more than one value it’s recommend that you use an XML document to send the data.  The syntax to create the queue is very simply.

CREATE QUEUE [QueueName]

Because the queue does hold the messages you can define what file group you would like the data within the queue to be stored in.  If you have a business need to keep the messages around after processing you can specify the RETENTION keyword setting it to ON.  By default queues are enabled when they are created.  If you do not want to allow messages to be delivered to the queue specify the STATUS keyword setting it to OFF.

Denny


June 2, 2008  11:00 AM

Back To Basics: Service Broker Contracts

Denny Cherry Denny Cherry Profile: Denny Cherry

Every Service Broker conversation requires a contract.  The contract defines which message type or types will be used, as well as who (sender or receiver) can use which message types.  The contract is specified when the conversation is created.

The syntax for creating the contract is pretty straight forward.

CREATE CONTRACT [ContractName]
(MessageType SENT BY ANY)

The SEND BY portion can be used as many times as is needed for each Message Type which is defined on the contract.  It must be listed at least once as you can not have a contract with no message types.  You can specify that the Message Type be used by the INITIATOR, the TARGET or by both (by using the ANY keyword).

A more complex Contract would look something like this.

CREATE CONTRACT [ContractName]
(MessageType_I SENT BY INITIATOR,
MessageType_T SENT BY TARGET)

Denny


May 29, 2008  11:00 AM

geekSpeak: Spatial Data in SQL Server 2008 with Denny Cherry

Denny Cherry Denny Cherry Profile: Denny Cherry

On 6/4/08 at noon PST (3pm EST) I’ll be presenting a MSDN Geek Speak session entitled “Spatial Data in SQL Server 2008 with Denny Cherry”. In the session we will explore the new support for spatial data in Microsoft SQL Server 2008. I will also be talking about managing huge SQL Server databases, and answer your questions about SQL Server. The Microsoft hosts will be Mike Benkovich and Mithun Dhar.

It’s a very free form session where we rely on Audience Q&A to really drive the direction of the session.

Here is the URL to register for the session.  Just click the URL and register, you’ll get an email reminder, etc from the Microsoft Events system.

I hope everyone can attend.  I hope to make my first geekSpeak a huge success.

Denny


May 26, 2008  12:00 PM

SoCal Code Camp is coming up

Denny Cherry Denny Cherry Profile: Denny Cherry

The SoCal Code Camp is coming up shortly (June 28th and 29th).  I’ve just finished posting my abstracts on the site for all to see.  I’ve tagged them all with SQL Server to make them easier to find.  I’ve currently got about 6 sessions on there. 

I’m crazy I know.

If you aren’t in Southern California this might be a great way to get the boss to pay for a weekend in San Diego, California.

There’s also a free dinner which is called the Geek Dinner.

I hope to see you all there.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: