Simons SQL Blog

UNfortunately you canot connect from SQL2005 to SQL 2008 (Katmai) from management studio, or can you. It appears the validation code is in the main connection dialog. However if you use SQLCMD mode you will be fine.

So connect to you SQL2005 server and then issue a

:connect MyKatmaiServer

Anything after that line will be executed against your katmai server.

Ok so you can't use the UI but at least you can run scripts.

We have now opened the full registration for www.SQLBits.com

You will need to register on the site and then select the 10 sessions (http://www.sqlbits.com/information/PublicSessions.aspx)  you would most like to see. We will use this voting to help decide which sessions are to be run. We have 36 sessions and only 20 slots. Even I can do those maths.

Once you have selected and saved your selection you will be given the registration URL.

Please don't just click any 10 because then you won't get the sessions you want to see.

We only have 300 places so make sure you register quickly.

Simon

Do you have multiple screens attached to your computer.

If you want a remote desktop connection to open and cover all the screens then you can use the /span option. To do this create a shortcut or edit the existing one for remoe desktop and add /span after %systemroot%\system32\mstsc.exe.

Another great tip is to use the /console option. This opens a connection to the console of the server, just as though you were logged on at the server. This enables you to have 3 open sessions to your server when running in administration mode.

Also if you are going to restart a server make sure you log on to the console. This menas that you get the warnings about other users being logged in and allows you to respond. If you don't log into the console then a restart when users are logged in results in your servers hanging because your remote desktop sessions are closed, but a pop up is waiting to be dismissed. You can't log back in because the server is shutting down and it won't allow the TS application to start.

I'm sure glad I moved to SQLBlogcasts.com. SQLJunkies has been a poor relation of DotnetJunkies for a long time and needs to be either shutdown or someone take ownership of it.

I did wonder why my stats for today were almost 0.

The simple answer is there isn't one.

If you insert 1000 rows into a table and then want to get back the 43rd row you inserted you can't. You can read through all rows and return the 43rd one that the database reads from the table but that is not the same thing.

Why is it so?

Relational databases are all about the data in the tables. The only guarantee is that data, the order of the data being inserted is not a physical piece of data and so you can't access it.

If this was possible what would you expect? Imagine a situation where you have 100 users inserting data, what order would you expect the data to be returned. The order it was inserted you answer. But when was the data inserted? When the insert statement is called or when the transaction commits, its not simple is it.  Therefore its down to you to store the data which you can use to define the order in which you want to return the data

So how do you get round this? Well you need to store the orderof insert in the table. This can be done in a number of ways

Identity Column

create table myTable

    (id               int identity(1,1)

    ,someOtherColumn ....

    )

With an identity column the value of the column is generated sequentially for each row inserted. With an identity column you can then query based on this column to get the Nth row inserted.

select

  from myTable

 where id = @NthRow

Date Column

The second option is to put a data column on the table. This way you can order the data in the order it was inserted and then return the Nth row.

create table myTable

    (pk etc...

    ,InsertedDate      datetime

    ,someOtherColumn ....

    )

In SQL 2005 you can use the ROW_NUMBER() function and in SQL 2000 you can use the TOP feature. (In SQL 2000 If want the row to be dynamic you need to use dynamic SQL)

If you need the 1099^th row then in SQL 2000 use

select top 1 <some columns>

  from (

        select top 1099 insertsedDate

             , <some columns>

          from myTable

      order by insertsedDate asc)topData

  order by insertsedDate  desc

and in SQL 2005 use

select <some columns>

  from (

        select row_number() over (order by insertsedDate) row

             , <some columns>

          from myTable

      order by insertsedDate asc)topData

  where row=1099

We ave now published the sessions tht have been proposed, we have yet to finalise the agenda but at least you can see whats might be going on.

The event registration site won't be available for a few days, but in the mean time if you register on the site then we will email you the registration link first so you can make sure you get your place.

2 months away and with no details, we already have 50 signed up so I expect it to fill up quickly.

Its going to be a great day.

Folowing on from my previous post about BOL URLs I thought it would be a good idea for there to be an option in BOL to display the web URL for a page as these are much easier to provide to people.

So I asked my friend Alan and it seems he'd had the same idea. I've you also think it is a good idea then vote here https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=293703.

If you want a web URL now then you can follow Alans tip

"There is a relatively simple way to find the topic online if you're looking at a topic in the local BOL. The MSDN (and TechNet) Libraries support both URLs with their 8-digit topic identifiers and URLs with our topic GUIDs. Using your topic example below, all of these URLs go to the same copy of the topic:

http://msdn2.microsoft.com/en-us/library/ms142509.aspx

http://msdn2.microsoft.com/en-us/library/d4bdd16b-a2db-4101-a946-583d1c674229.aspx

http://technet.microsoft.com/en-us/library/ms142509.aspx

http://technet.microsoft.com/en-us/library/d4bdd16b-a2db-4101-a946-583d1c674229.aspx

FYI, I just found an interesting bug. If you click the last of the four links, you get a BOL page wrapped in the Exchange Tech Center templates.

So, if I'm sitting on a local BOL topic, I:

1. Copy the GUID from the URL displayed by DExplore.

2. Open any MSDN Library BOL topic in my browser.

3. Paste the GUID over the 8-digit identifier.

4. Click the link to verify it takes me to the right online topic.

5. Sometimes I just paste the GUID-based URL in my emails. Other times I use the TOC to flip to another topic and then back to the one I want so that MSDN flips the URL to the one with their 8-digit identifier and paste that URL into my email."

In using BOLyou will have noticed that the links in BOL contain some lovely guids. I often want to reference BOL in documents, forum posts etc but was concerned that the guids might change. So I asked Alan Brewer whether they change or not. He gave me a great response which I've got his permission to publish here.

"There are two issues with GUID reliability, both should be solid. 

First, when I asked a year ago, the MSDN publishing teams said they plan to keep supporting both GUID-based and asset ID-based URL forms.

Second, in our authoring system, once a topic is created it retains the same GUID across releases. This is independent of where in any TOC the topic is surfaced. So all SQL Server 2005 topics that still exist in SQL Server 2008 have the same GUID in both environments. We started this policy when we forked the SQL Server 2008 BOL source from the SQL Server 2005 BOL, so does not apply back to the SQL Server 2000 BOL. We plan to carry it forward to future releases.

MSDN/TechNet support many different formats of URLs, including URLs with version identifiers. These URLs take you to the SQL 2005 and SQL 2008 versions of a topic shared between the two BOLs, even though the topic is at different locations in the two different TOC's:

SQL Server 2005

http://msdn2.microsoft.com/en-us/library/ms189084.aspx

http://msdn2.microsoft.com/en-us/library/ms189084(SQL.90).aspx

http://msdn2.microsoft.com/en-us/library/82d7819c-b801-4309-a849-baa63083e83f.aspx

http://msdn2.microsoft.com/en-us/library/82d7819c-b801-4309-a849-baa63083e83f(SQL.90).aspx

SQL Server 2008

http://msdn2.microsoft.com/en-us/library/ms189084(SQL.100).aspx

http://msdn2.microsoft.com/en-us/library/82d7819c-b801-4309-a849-baa63083e83f(SQL.100).aspx

If you include the (SQL.90) or (SQL.100) version identifier in the URL you are taken to the specific version's copy of the topic. If you do not include the version identifier, you are taken to the copy of the topic in whatever is considered the most current shipping version of the product. Today that is SQL Server 2005. As part of the SQL Server 2008 RTM process (and I mean the real RTM, not the "launch"), MSDN/TechNet will flip a switch so the SQL 2008 BOL becomes the one you get with URLs that don't have a version identifier."

If you want to document your database you've got to see Gert Drapers on Channel9. Data Dude is answering so many of those questions/challenges I've been trying to solve for database builds over the past decade. I am so pleased to see the database references in the service release and the dependecy tool just rocks.

I really need to get more into data dude.

Thansk to a few that pointed this out

http://blogs.msdn.com/briankel/archive/2007/08/14/new-channel-9-video-dbpro-service-release-and-power-tools.aspx

http://blogs.msdn.com/camerons/archive/2007/08/14/new-channel-9-video-on-power-tools.aspx

Read about the service release http://blogs.msdn.com/gertd/archive/2007/07/23/service-release-1-is-here.aspx

Download gerts channel 9 vid http://download.microsoft.com/download/8/c/1/8c18d316-d95e-4068-a333-37972897b282/GertD_DBPro_SR_PowerTools_Interviewcast.wmv or watch it on channel 9 http://channel9.msdn.com/showpost.aspx?postid=334261

There have been continuing reports by most people I speak to about theire procedure cache becoming huge. Especially on 64 bit systems with lots of memory. Memory that you want for your data.

The issue is due to the storinging of compiled adhoc plans. They just aren't getting aged out of the cache.

We are lobbying for there to be a way of limitiing the size of the procedure cache. If I have 8Gb of memory I don't want 2 Gb to be used by the procedure cache. If you agree please vote on it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=293188

So you don't think it applies to you. I bet you will be suprised, run

select name, SUM(single_pages_kb + multi_pages_kb)/1024.0 MBUsed

from sys.dm_os_memory_clerks

group by name

order by 2 desc

and see how many MB is used by SQLPlans

Generate the scripts for a database and see what the difference is, I am sure you will be suprised.

You will also find that many of Microsoft tools that don't provide parameterised queries also cause the problem

Vote now your database needs you

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=293188