Daily Archives: December 13, 2004

A visit with John Robbins and wife, Pam

John Robbins is speaking tomorrow night in Montreal at GUVSM . He and his wife, Pam, decided to take advantage of being in that fantastic city and went up a little early, passing through Burlington. So we met up yesterday for lunch at the Burlington Pub and Brewery and then wandered around Church St for a while, visiting some of my favorite places I like to show off to out of towners: Frog Hollow State Craft Center and incredible shop featuring some of the finest crafts people (and even companies like Simon Pearce) in Vermont, Lake Champlain Chocolates (mmmmmmm…) and some others. I couldn’t lure them into Kiss The Cook, but Rich and I got to go afterwards.

It is really fun to hang out with fellow geeks and NOT talk shop! It’s kind of rude to geek out when 1/2 of the group (i.e. Pam and Rich) are not geeks. So we got to just have a normal time talking about normal things – food, travel, food, travel and beer, too. (just kidding).



Posted from BLInk!

It’s WORKING! SqlDependency!! Here’s how I did it.

read this and then this post to understand the problem.

Well … I started out trying this against pubs and it didn’t work and then I tried it with AdventureWOrks and it didn’t work. I focused on pubs and made a lot of changes without it working. Then I finally went back to AdventureWorks and it did work!! So I’m not sure exactly which thing I changed previously, but the to get pubs to work with query notification was in the DataBase, I needed to set the Database Properties/Options/Miscellaneous: “Database Compatibility Level” to Version90. It had been at Version80.

Note that all of the other Miscellaneous options for pubs were set to false and I did not have to change them for it to work.

Wally McClure and I talked about some of the issues about when to make particular calls. I found that the samples in the docs and Bob Beauchamin’s article were fine in terms of creating the connection, creating the command, opening the connection, creating the dependency, creating an event handler, etc worked fine.

Here are some things that I was unsure about.

All of the samples I saw showed doing a read on the SqlDataReader that was returned by the command that I attached the dependency to. So I started out with this code (which works):

            SqlDataReader rdr=cmd.ExecuteReader();
            while (rdr.Read())
                Console.WriteLine(rdr[0]);
            rdr.Close();

Once I had that working, I did what I really wanted to do which was return the SqlDataReader to my form (in real life, maybe a middle tier) which then loaded it into a DataTable and did something with the DataTable. So I changed the code to:

           SqlDataReader rdr=cmd.ExecuteReader();
           return rdr;

and that worked just fine. So according to this test it is NOT necessary to do a read on the SqlDataReader for the notification to fire.

The placement of the Open method of the connection did not seem to affect the notification query. Of course, just so long as it’s before you try to execute the command. I tried it before creating the dependency and I tried it after the dependency and event handlers were already created. This makes perfect sense to me. But it was something I was worrying about when things weren’t working. So that eases my mind to know that my instincts are okay.

So there were NOT a gazillion other things I had to do in SQL Server besides the frequently advertised ones.

1. Be sure the CLR is enabled in SQL Server
the tsql for that is

EXEC sp_configure ‘show advanced options’, ‘1’
go
RECONFIGURE
go
EXEC sp_configure ‘clr enabled’, 1
go
RECONFIGURE

2. Make sure that Enable_Broker is ON in your database
Wally explains how here

3. Make sure that SQLNotification will send messages with this TSQL statement
GRANT SEND send on service::SqlQueryNotificationService to guest
a) note that the word SqQueryNotificationService is case sensitive
b) you have to have admin priveleges in order to successfully run this command

4. The database user needs permission to subscribe to notification
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [the user]
 –that is, whatever user the calling application is that is making requests. I haven’t tried ASP.NET yet, but I’m guessing that will be the Network Service account if IIS6 is your webserver.

5. Follow the query rules.
You have to write a query that can be parsed and that needs to follow some very specific rules. The three most notable are
a) The projected columns in the SELECT statement must be explicitly stated (i.e. select id, name, lastname from mytable not select * from mytable)
b)Table names must be qualified with two-part names (eg dbo.authors or person.contact)
c) Due to the above one, this means that all tables referenced in the statement must be in the same database.
I posted a full list here

Even though I fiddled for 2 days with this, I think the above represents the right combination of things to attend to.

The last caveat is that this is still a beta. From reading Bob’s article, we can rest assured that MS is going to simplify this stuff. As for limiting your queries to “hello world“ applications (I’m exaggerating… but see Roman’s comment) maybe they’ll make it smarter or maybe the more full blown notification services will be the way to go. I don’t really know – that’s just a completely wild guess.

Posted from BLInk!

Query Notification SELECT QUERY rules

There are SO many caveats to using query notification wtih SQL Server. Things like the fact that it is not based on transactions (eg you will get a notifcation even if something is rolled back). You have to do a LOT of trolling around in the books online to find all of this stuff and frankly, I still do NOT have my notifications firing yet.

So here is a list from the Books Online for the October CTP of SQL Server 2005 of rules to follow when you are building a query with which you want to susbcribe to the service.

Query notifications are supported for SELECT statements that meet the following requirements:

  • The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.
  • The statement may not use the asterisk (*) or table_name.* syntax to specify columns.
  • The statement may use unnamed columns or duplicate column names.
  • The statement must reference a base table.
  • The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). However, SUM() may not be specified for a nullable column. The statement may not specify HAVING, CUBE, or ROLLUP.
  • A projected column in the SELECT statement that is used as a simple expression must not appear more than once.
  • The statement must not include PIVOT or UNPIVOT operators.
  • The statement must not include the INTERSECT or EXCEPT operators.
  • The statement must not reference a view.
  • The statement must not contain any of the following: DISTINCT, COMPUTE or COMPUTE BY, or INTO.
  • The statement must not reference server global variables (@@variable_name).
  • The statement must not reference derived tables, temporary tables, or table variables.
  • The statement must not reference tables or views from other databases or servers.
  • The statement must not contain subqueries, outer joins, or self-joins.
  • The statement must not reference the large object types: text, ntext, and image.
  • The statement must not use the CONTAINS or FREETEXT full-text predicates.
  • The statement must not use rowset functions, including OPENROWSET and OPENQUERY.
  • The statement must not use any of the following aggregate functions: AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP.
  • The statement must not use any nondeterministic functions, including ranking and windowing functions.
  • The statement must not contain user-defined aggregates.
  • The statement must not reference system tables or views, including catalog views and dynamic management views.
  • The statement must not include FOR BROWSE information.
  • The statement must not reference a queue.
  • The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0).

That’s just the query rules. There are also rules for the connection and a lot of setup to do on the database. I will eventually succeed at this! I swear I will. Of course, by then, I will probably have been fired from all of the other contract work I am supposed to be doing. (Not really…)

Posted from BLInk!

SQLDependency and Notification Services – I give UP! (not really)

UNCLE!

I have the right code from the documentation, from Bob B’s awesome article, from the whidbey.adodtnet newsgroup, these posts from Wally McClure (rules, enabling CLR in SQL) and he was kind enough to spend a good chunk of time with me on i.m., too. But I cannot freaking get a notification from my SQL Server (Oct CTP bits). I have set up everything I know of in SQL, even tried this with my firewall temporarily turned off. Apparently besides the code, besides doing all of these things:

  •  grant send on service::SqlQueryNotificationService to guest  in msdb
  • GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [the user] in the database
  • ensuring that Enable_Broker is true in the database
  • getting your calls in your code in the correct order
  • making a proper query statement

there seem to be some 400+ other rules to follow. I have been changing the order of events in my code, reading through the online books, investingating the sql logs, learning more about SQL Server 2005 than I really care to/want to/have time for right now, and on it goes…

Okay it’s a beta. But really. Eight hours. I freakin’ give up. But of course, that’s a lie. I will post this and then think of 24 other things to try and just keep at it until …..who knows…?



Posted from BLInk!

Burlington based MS Gold Partner says business is really looking up

As mentioned by Dave Burke, the local paper, Burlington Free Press has this story in their Business Monday section about Competitive Computing. This is where SQL guru Roman Rehak works. The Free Press does not persist its articles for over a week so I am copying and pasting it into my blog. All copyrights etc  Burlington Free Press. Interestingly, what C2 says kept it afloat after the dot com bubble burst was to focus on local (i.e. vermont state area) businesses.

Boom to bust and back again

 By Shawn Turner
Free Press Staff Writer

COLCHESTER — Competitive Computing Inc. is hoping the days of scrimping and saving are over.

The Web-site builder was forced back into its shell following the tech collapse in 2001. Forty percent of the 75-person staff was laid off. Salaries had to be cut. A paid internship program with the University of Vermont was halted. The company refrained from buying new equipment.

Now business appears to be picking up. This fall, the company — known as C2 — completed Web-site redesigns for Green Mountain Coffee Roasters Inc. and glassware and pottery seller Simon Pearce Inc.

C2 had not worked with either company before, said Steve Thurlow, C2’s vice president of business development. Green Mountain Coffee in Waterbury and Simon Pearce in Windsor wanted to work with a Vermont company, and each initiated contacted with C2. The companies also liked the technical knowledge of C2’s workers.

Half of the 54-person staff at C2 is Microsoft-certified, Thurlow said, “which puts them at the top of the Microsoft skill set.”

C2 President Carolyn Edwards said she has noticed a change in how companies are implementing technology. During the tech-boom years, companies knew they needed to be up to date tech-wise, but didn’t always know how to integrate technology into the business, she said. Today, companies take more time in planning how to integrate business plans with technology.

“There is pent-up demand for technology,” Edwards said.

She might be right. Former U.S. Secretary of Labor Robert Reich said in October that Vermont’s technology sector is likely to see a reawakening during the next 10 years.

“Tech is here to stay,” Edwards said.

The work C2 did in cementing its reputation in the local business community helped the company outlast the slowdown, Thurlow said.

“It’s a way of fueling growth,” Thurlow said. “There are a lot of opportunities in Vermont.
Surviving


C2 had its start in 1993 and began doing Web development in 1996. Since 2000, C2 has occupied 15,000 square feet of office space on Mountain View Drive, which provides stunning views of Burlington and Lake Champlain. It’s a far cry from Edwards’ South Burlington condo that the four founders — Melissa Dever, Todd Kelley and Martin Thieret and Edwards, executives at Digital Equipment Corp., which closed its Vermont manufacturing plant in 1993 — used in the first months following C2’s launch.

The company comprises four parts: Web-hosting; building infrastructure services for clients, including e-mail systems; building Web sites; and consulting with clients on how to use technology to support a business strategy.

 

The company survived the lean years, in part, by relying on its Vermont customers, Edwards said.

“Stay focused on the future and manage your expenses very carefully,” she said, when asked how best to get through rough economic times. “Focus on good quality.”

Community connections, like Edwards’ membership on the boards of the Greater Burlington Industrial Corp. and Vermont Business Roundtable, also helped.

Involvement with the local business community has helped the company’s visibility, Thurlow said. Most of C2’s 30 to 40 clients are in Vermont. The close-to-home clients aided in sustaining C2 during the slow economic years. C2’s reputation in Vermont helped it compete with out-of-state businesses that were looking at adding Vermont customers.

“You need to own your local market,” Thurlow said.

One of the local clients C2 has worked with is the University of Vermont’s College of Medicine. The company helped UVM develop a template that allowed groups within the university to build Web pages that would have a consistent look with UVM’s homepage, said Ted Winfield, who was senior associate dean for finance with the College of Medicine when the template was built.

C2 also helped develop the college’s COMET program that was put in place in 2003. COMET — which stands for College of Medicine Educational Tools — is an online system that provides medical students access to educational resources, said Michael Caputo, director of information systems at the College of Medicine.

“It’s been an effective partnership and Competitive Computing has been very helpful,” said Winfield, associate vice president for budget and resource management at UVM.
The future


Edwards, who declined to reveal company revenues, is “absolutely” optimistic about the company’s future.

C2, which began doing such work as helping bank branch offices network with a home office, will continue to adapt to whichever way the fast-changing world of technology goes next.

“We live in a world where our knowledge base turns over every nine to 12 months,” she said.

With the tech sector recovering, Thurlow said C2 is setting its sights beyond Vermont’s borders. The company has done business with out-of-state companies — including Hershey Foods Corp.’s Hershey Gifts — but would like to add to its list of non-Vermont clients.

Helping C2 expand nationally is Microsoft, of which C2 is a “Gold Partner.” This means the software giant helps C2 with marketing and filters referrals to C2. Hershey Gifts was referred to C2 through Microsoft.

The company doesn’t want to forget about its home state. Thurlow said C2 plans to host a series of business seminars that would advise businesses on how to intertwine technology with operations.

The company is beginning to hire again after employment dropped to 45 from a high-water employment mark of 75 in 2000.

“We could be hiring in the springtime,” Edwards said. “That could be a point for us. Our plan is to grow.”
Contact Shawn Turner at 660-1852 or
sturner@bfp.burlingtonfreepress.com 

Posted from BLInk!