Welcome to Sign in | Help

October 2007 - Posts

Messengers and status messages - another kind of advertising. Another way of loosing time Cool

Looking at someone else's status message I found out about a new search engine, in fact not a new search engine as an interface for Live Search, Ms. Dewey

More details:

http://en.wikipedia.org/wiki/Ms._Dewey

http://en.wikipedia.org/wiki/Janina_Gavankar

You have install a SQL Server 2008 CTP and then another application or you've installed SQL Server 2008 without reading the readme file.

You try to use SQL Server Management Studio to connect to your server and you get this error: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

You try to connect using the TCP/IP protocol instead of Shared Memory and the result is another error:  A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)

In the event viewer you may see this error message:The Tabular Data Stream (TDS) version 0x73090003 of the client library used to open the connection is unsupported or unknown. The connection has been closed. [CLIENT: ]

SQLCMD connects to the server so the server is ok, then the network library is the problem and more specifically the SqlClient. How to detect exactly what caused the problem? See the next table (source):

Conectivity Matrix Table

[edit date="2007-12-19"]

A better image is provided by this article: Connecting to Pre-Release Versions of SQL Server 2008

[/edit]

I do not have a fixation on ring buffers but I keep finding them in my path. For example, recently I was writting about the new DMVs introduced by SQL Server 2008. One of the sys.dm_os_memory_brokers DMV returns information about memory brokers.

[quote]

Allocations that are internal to SQL Server use the SQL Server memory manager. Tracking the difference between process memory counters from sys.dm_os_process_memory and internal counters can indicate memory use from external components in the SQL Server memory space.

Memory brokers fairly distribute memory allocations between various components within SQL Server, based on current and projected usage. Memory brokers do not perform allocations. They only track allocations for computing distribution.

[/quote]

Is it the sys.dm_os_memory_brokers  just another name for sys.dm_os_ring_buffers where the buffer type is memory broker? The answer is I am not sure yet.

Why? I ran this two queries:

SELECT CAST(Record AS XML)
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type='RING_BUFFER_MEMORY_BROKER';

SELECT * FROM sys.dm_os_memory_brokers;

The result for the first one:

For the second formatted a little:

At first look it seems to be the same info. But at the second ... And look at the values. But then again ...

I asked Slava Oks and when I will find an answer I will get back to this post.

I liked this tool so much that I considered it's worth to do a screencast about it. So here it is:

Windows Media Video archive: Download

Format: wmv
Duration: 00:03:53

If you work with LINQ to SQL then you might want to take a look at this posts of Dinesh Kulkarni:

 Also you may wanna check this free lab MSDN Virtual Lab: Mapping Your Objects to Database Tables with LINQ to SQL.

[quote]

Event Overview

This lab shows how to access relational data using LINQ to SQL.  You will start by creating an object model for the Northwind database, and then use the object model to access the database using the new C# 3.0 query expressions and LINQ to SQL APIs.

You will next create an object model from an existing database using the LINQ to SQL Designer.  This part covers mapping relationships across tables and using Create, Update, and Delete operations. Use of the object model covers the use of transactions, object loading options, stored procedure integration and object identity with LINQ to SQL.

[/quote]

Finally you can download the DAT 200 Technet Session - New Thinking in Data with LINQ and Visual Studio 2008 from here.

[quote]

DAT-200: New Thinking in Data with LINQ and Visual Studio 2008
DAT-200 Demo 1: Going from C# 2.0 data access to LINQ
DAT-200 Demo 2: Exploring LINQ to SQL Data
DAT-200 Demo 3: Working with XML Data
DAT-200 Demo 4: Working with XML and ASP.NET
[/quote]

It's not a secret that Microsoft aquired Dundas Technology and Reporting Services will benefit from that. More specific, there's a chance that SQL Server 2008 will include these controls: 

  • Dundas Chart for Reporting Services (included in CTP4)
  • Dundas Gauge for Reporting Services (in work)
  • Dundas Calendar for Reporting Services (maybe in CTP6)

According to this post on the MSDN forums, the Dundas Calendar may not making it into SQL Server 2008. However take a look at these pages to see how your reports may look like in the near future:

What can you do in 5 minutes with the the new Report Designer? This (the visits report for this site per one month grouped by browser type):

i’m MAKING A DIFFERENCE 

[quote]

i’m is a new initiative from Windows Live Messenger™. Every time you start a conversation using i’m, Microsoft shares a portion of the program's advertising revenue with some of the world's most effective organizations dedicated to social causes. We've set no cap on the amount we'll donate to each organization. The sky's the limit.

 

[/quote]

After 2 weeks of working just fine, today my Virtual PC 2007 console started to play games with me, it appeared minimized in my taskbar or in the system tray, but no interface, no maximize option in the context menu (when the console was minimized).

I had to google this problem and the solution is very simple. In the %appdata%\Microsoft\Virtual PC folder, the Options.xml configuration file has to be modified.

The content in my case of the relevant section was:

<console>
   <height type="integer">246</height>
   <left_position type="integer">4294935296</left_position>
   <top_position type="integer">4294935296</top_position>
   <visible type="boolean">true</visible>
   <width type="integer">359</width>
  </console>

After changing the values everything work just fine:

<console>
   <height type="integer">246</height>
   <left_position type="integer">10</left_position>
   <top_position type="integer">10</top_position>
   <visible type="boolean">true</visible>
   <width type="integer">359</width>
  </console>

I was doing a screencast with the new options to track deprecated features in SQL Server 2008.
I launched a Profiler and I was about to run

SELECT DATABASEPROPERTY('master','IsAutoShrink');

Before I had the time to press Run, an event from Profiler itself was displayed:
"TEXT, and IMAGE data types will be removed in a future version of SQL Server. Avoid using them in new development work, and plan to modify applications that currently use them."

After a minute the SMSS and Report Server generated a similar warning, and so on. I deleted them and I restart the trace, press run again, run my statement quickly so I can complete my screencast.

Cool Cool

Do not expect this functionality from SQL Server 2008. Though it is mentioned here:SQL Server 2008 Overview: On-demand webcast and here: Online Transaction Processing in SQL Server 2008, the feature that was supposed to persist connection string information in the client's registry, won't make it in SQL Server 2008. Maybe in the next version.

Windows Media Video archive: Download

Format: wmv
Duration: 00:03:25

Personally I prefer NTFS but if you need FAT32 then you should take this survey: Take our survey about running SQL Server on a FAT32 environment! 

It would take you just one minute as the survey consists in 5 simple questions.

Recently I went out with Remus to eat some healthy food (pizza) and of course to drink some fruit juice (read beer). The subject of the evening was Service Broker. I won’t reproduce the whole discussion but I found interesting the following point:
Many people use replication not to copy or to transfer data but as a way of communication between applications. For example the customer pays something at a POS and the application communicates with a bank application. Using replication in this case is a poor choice compared to using Service Broker.
So app to app is better suited to Service Broker than Replication. On the other hand if all you need is data transfer then Replication may be what you need.

Just ponder that ...

Over the years from IT events or from various other sources I gather a lot of caps. Yesterday at a user group meeting I received a new one. The following 4 caps marks various stages of my involvement in IT:

  1. First one (from left to right) demonstrates that I'm a member of ITBoard (I'm also one of the founders). Good job Lore, I like this cap.
  2. The second one I received it when the MCSA certification was launched.
  3. Culminis - I was a council member for Culminis EMEA.
  4. Microsoft BI - this cap I got from my MVP lead when I became SQL Server MVP back in 2005.

Since the ITBoard cap is the last one here are two more photos with it:

 

And the bit of SQL. Returning from the meeting with Remus Rusanu, we talked about the Transparent Data Encryption feature of SQL Server 2008. There is a chance that some people will understand this feature as an Enterprise feature and this is not the case. The best scenario for TDE is the stolen laptop having confidential information stored on a local database. Do not imagine a scalable database running on a production server with TDE on top.

Read more about TDE on this sources:

Finishing with TDE, Service Broker was next. "Have you surfed today? Then there are big chances that some of your requests were managed by Service Broker..." Never thought of that!

 

Microsoft SQL Server is in the Leaders quadrant for the Gartner Magic Quadrant for Data Warehousing! Read about it here Magic Quadrant for Data Warehouse Database Management Systems, 2007 or in the official announcement from Microsoft: Microsoft Is Positioned in Leaders Quadrant of Latest Magic Quadrant for Data Warehousing.

Add to that (source Jack Bradham's What's New in SQL Server 2008 for Developers Webcast ), speaking about SQL Server 2005:

[quote]

 From product functionality we are continuing our leadership in security with a recent analyst report shows us that we are years ahead of Oracle in producing secure/reliable product. On the benchmarks – we are increasing the leaders the DW Benchmarket leader with the #1 Price/Performance Non Clustered Benchmark in the 3 TB range and #1 Performance Benchmark in the 1 TB range
In the application space – we have 9/ out of top 10 price performance benchmarks for TPC-C
We have also released the first TPC- E bechmarks which provide more realistic customer scenario and also negates the benefits of Oracle RAC

This has propelled SQL Server into impressive growth – We have shipped more units that Oracle and IBM combined.
Our growth is not only limited to DB but also to BI where we are the fastest growing BI vendor – among the top 10 vendors.

From a breadth perspective – we have had over 8million SQL Server Express downloads and have built an impressive ecosystem of partners trained and applications certified.
[/quote]

 

Windows Media Video archive: Download

 

 

Format: wmv
Duration: 00:04:41

Sunday, today I will not open my comp. Oh yeah? Liar! My sister needed some help with a wirelless router so power on.

Then since I've just downloaded Camtasia Studio 5 I tried to see what I can do with it in 5 minutes. I tell you it's an amazing product. Here are the results and remember it took me just 5 minutes (you can say that because I pause sometimes when I don't know exactly what to say - For example instead of saying "Let's see" i said "Let's show" but, hey it's the first try. I bet than even Steven Spielberg made some mistakes in the beginning of his career).

Alternatively use the Windows Media Player control embeded in this post. Use double-click on the movie to see it in Full-Screen mode.

For downloading the movie use this link: Download.

 

 

 

Format: wmv
Duration: 00:04:12

I wrote recently an article about Logon Triggers for Simple-Talk.You can read it here: Logon Triggers.

While doing the research for this article I learned a few things:

  1. Common Criteria Certification does not guarantee that a product is secure but guarantees that a product has certain security functionalities. For example Windows 2000 Server is certified Common Critearia EAL4+ and I wouldn't call it exactly secure.
  2. What the + sign means in EAL4+ - read the article.
  3. That if you activate the common criteria compliance option in SQL Server 2005 SP2 you comply to the following requirements of the Common Criteria Certification:
  4. [quote]
    Criteria Description

    Residual Information Protection (RIP)

    RIP requires a memory allocation to be overwritten with a known pattern of bits before memory is reallocated to a new resource. Meeting the RIP standard can contribute to improved security; however, overwriting the memory allocation can slow performance. After the common criteria compliance enabled option is enabled, the overwriting occurs.

    The ability to view login statistics

    After the common criteria compliance enabled option is enabled, login auditing is enabled. Each time a user successfully logs in to SQL Server, information about the last successful login time, the last unsuccessful login time, and the number of attempts between the last successful and current login times is made available. These login statistics can be viewed by querying the sys.dm_exec_sessions dynamic management view.

    That column GRANT should not override table DENY

    After the common criteria compliance enabled option is enabled, a table-level DENY takes precedence over a column-level GRANT. When the option is not enabled, a column-level GRANT takes precedence over a table-level DENY.

    [/quote]
  5. I found 2 bugs in Logon Triggers:
    1. Logon Triggers close connections if an error with a severity of 14 occurs
    2. CLR Logon Triggers - SMO Script Generation
  6. I found out that the script necessary to make SQL Server 2005 SP2 compliant to Common Criteria EAL4+ is missing: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2113556&SiteID=1
  7. I learned the hard way that inside a stored procedure activated by an Event Notification you have the security context of an user and not of a login, so if you need to access different databases it can be a real pain. Fortunatelly Remus Rusanu came in to save my day: Why does feature ... not work under activation? , Signing an activated procedure.
  8. And more ...

 

If you are connected to the SQL Server blogosphere you've probably noticed lots of posts about the next version of SQL Server. One of the improvements of T-SQL is introducing the compound assignment operators (used in lots of programming languages). Most of the posts about this feature mention just the += operator. In fact SQL Server 2008 supports not just the += operator but also the following list of operators:

Subtract and assign -=
Multiply and assign *=
Divide and assign /=
Modulo and assign %=
Bitwise & and assign &=
Bitwise | and assign |=
Bitwise xor and assign ^=

Example:

DECLARE @I INT = 10;

SET @I*=10;

SELECT @I;

-- Result

100

 

Recently my article on Dynamic Management Views and Functions from AspToday became a featured articled which means that you can read it for free. I wrote it a year ago but it can still give you a good start with DMVs and DMFs.

If it's worth it you can decide by clicking this link: SQL Server 2005 Dynamic Management and Views

If you need to see the physical structure of your database you can use DBCC PAGE as described here:Read Index Structure

[quote]

CREATE TABLE #DBCCIND(
    PageFID INT,
    PagePID INT,
    IAMFID INT,
    IAMPID INT,
    ObjectID INT,
    IndexID INT,
    PartitionNumber INT,
    PartitionID BIGINT,
    iam_chain_type VARCHAR(100),
    PageType INT,
    IndexLevel INT,
    NextPageFID INT,
    NextPagePID INT,
    PrevPageFID INT,
    PrevPagePID INT);

INSERT INTO #DBCCIND
EXEC ('DBCC IND(''AdventureWorks'', ''Sales.SalesOrderHeader'',-1)');

WITH LinkedList AS
(
SELECT 1 AS RowNum, PageFID, PagePID FROM #DBCCIND WHERE IndexID 
    = 1 -- specify index id here
    AND IndexLevel = 0
    AND PrevPageFID = 0
    AND PrevPagePID = 0

UNION ALL

SELECT PrevLevel.RowNum + 1,
    CurLevel.PageFID, CurLevel.PagePID FROM LinkedList AS PrevLevel
    JOIN #DBCCIND AS CurLevel
    ON CurLevel.PrevPageFID = PrevLevel.PageFID
    AND CurLevel.PrevPagePID = PrevLevel.PagePID )

SELECT
CAST(PageFID AS VARCHAR(MAX)) + ':'
+ CAST(PagePID AS VARCHAR(MAX)) + ' ' AS [text()] FROM LinkedList 
ORDER BY RowNum FOR XML PATH('') OPTION (MAXRECURSION 0);

DROP TABLE #DBCCIND;

[/quote]

Or better, you can use this great tool: SQL Internals Viewer

SQL Server 2008 is at the door and the same stands for Visual Studio 2008. The data access strategy is changing and you don't know where to start. I gathered some starting resource that you may use for that:

  1. Whitepapers and blog posts
  2. Webcasts and screencasts:
  3. Virtual Labs:

 

A year and a half ago I wrote about the undocumented Dynamic Management View sys.dm_os_ring_buffers in SQL Server Professional.

Shortly the ring buffers expose internal server state information about memory, exceptions, or schedulers. Each ring buffer is giving you 64K of live information that you can get usually using a trace, by debugging or other time-expensive methods.

At that time there were 9 types of ring buffers:

  1. RING_BUFFER_RESOURCE_MONITOR - allows you to see memory state changes due to various types of memory pressure.
  2. RING_BUFFER_MEMORY_BROKER - the Memory Broker is responsible for distributing memory to each component that needs it. Each component will have an “optimal” amount of memory determined using the existing memory demand and consumption. The Memory Broker will send notification to components letting them adjust their behavior – grow, shrink or stay stable.
  3. RING_BUFFER_SINGLE_PAGE_ALLOCATOR - allow you to see when the Buffer Pool when BP, single page allocator, turns on/off internal memory pressure. More about it on the Slava's blog: SQLOS's memory manager: responding to memory pressure.
  4. RING_BUFFER_OOM - contains records about out-of-memory conditions.
  5. RING_BUFFER_BUFFER_POOL - contain records indicating severe buffer pool failures, including buffer pool out of memory conditions.
  6. RING_BUFFER_SCHEDULER - tells you what the schedulers have done in terms of I/O, the context switch order of workers and so on.
  7. RING_BUFFER_SCHEDULER_MONITOR - returns information about the health of the schedulers
  8. RING_BUFFER_EXCEPTION - contains 64K worth of exceptions.
  9. RING_BUFFER_CLRAPPDOMAIN - returns the state of AppDomains loaded in the system (More about it here:It’s 3 AM. Do you know where your appdomain is?

The Service Pack 2 for SQL Server 2005 comes with a new ring buffer - the RING_BUFFER_SECURITY_ERROR that contains Windows API failure information. You can read more about it in the post of Laurentiu: SQL Server 2005: Some new security features in SP2.

Do you know how to code gadgets for Windows Vista or Windows Live?

Do you want to win these?

Or these?

Then submit your code or judge the code of others here:

If you miss Query Analyzer you can still vote on the Connect site for this tool: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=297428&wa=wsignin1.0

However the answer from Microsoft is very clear:

[quote]

Thank you for your feedback regarding the desire to have a lightweight code editor provided by SQL Server. We have no plans to provide this capability for SQL Server 2008. In the meantime, there are several "freeware" solutions available. We will consider this for a future major release of SQL Server.
Thank you,
Bill Ramos

[/quote]

There's another solution besides the freeware tool or waiting for SQL Server vNext that will ship in 2010-2011 and that is to open the SSMS directly in a Query Window as Buck Woody shows in this blog post: Argh! Bring back Query Analyzer!.

[quote]

  1. Open SSMS
  2. Click Tools in the menu, then select Options
  3. In the At Startup button, click either Open new query window or Open Object Explorer and new query window
  4. Close SSMS

[/quote]

Also, to speed up the SSMS you may wanna read this post of Euan Garden: FAQ, Why does SSMS take 45s to start up?