Azure Sql Services – let’s try!

I decided to ask for a test account in the preview phase of Azure sql services, and today I’m trying to connect to it.

The signup process is quite easy and simple; once logged, I reach the web administration console for my databases.

I created my first db, 1gb maximum size. Now I want to connect to it using SSMS (Sql Server Management Studio) from my laptop. In the console there is a useful link to ready made “connection strings”, I use them to fill the SSMS connection dialog – but hey! I cannot connect…

After some clicking around I notice that I can setup the “firewall rules” in order to gain access from my IP. Let’s try.

The connection error actually states that I cannot connect from 78.14.234.1, so I fill the form to setup a “grant access” rule, as in the screenshot below:

Now I can connect successfully! And I can launch a New Query against my db, and run some (useless, I know) statements like:

The statements are quite silly, just to prove that I can do DDL.

Now I see that I cannot use the SSMS native “Object Explorer”, and looks like it’s impossible.

Looks like there are some alternative tool in this field, with some limitation (it’s a brave new world…); for example http://hanssens.org/tools/sqlazuremanager/ – Sql Azure Manager; is a tool developed to connect to Azure. It’s a ClickOnce setup, around 3.76 Mb in size. Once downloaded and installed, it prompts for your database connection parameter and you’re in.

There is also a web-based tool, Omega.MSSQL, at https://onlinedemo.cerebrata.com/SQLAzureClient/default.aspx. I cannot connect through this, but I found good opinions on it, so maybe it’s a problem on my side.

Another tool: “Gem Query Tool for Sql Azure”, here: http://microguru.com/gem/ – it’s very simple and lacks a lot of features… but again, I think that all these tools are too young to tell.

So far so good. I’ll keep you informed on this subject as I understand better. Bye!

Top 50 – SQL

Some time ago I had to select the users from a table that reached the first 50 scores in a particular game. Not 50 users, but the users that made the first top 50 scores. Here’s my solution, for my future memory :-)

CREATE PROCEDURE [dbo].[GetTop50]
AS
DECLARE @minscore int
CREATE TABLE #top50
    (userid INT PRIMARY KEY,
     score int)

INSERT INTO #top50 (userid, score)
    SELECT top 50 userid, TotalScore 
    FROM UserList 
    order by TotalScore desc

select @minscore = min(score) from #top50

delete from #top50 where score = @minscore

INSERT INTO #top50 (userid, score)
    SELECT userid, TotalScore 
    FROM UserList where TotalScore = @minscore

select * from UserList 
    where userid in (select userid from #top50)
    order by TotalScore desc

Hope this helps

Andrea

EDIT: what about a simple one like

select userid, TotalScore
from UserList
where TotalScore in
(select distinct top 50 TotalScore from UserList order by TotalScore desc)

uff... :-)

Survival: sql, update a table with values from another table

Often I have to update a table column with values taken from another table.

Since everytime I write this from scratch, and I'm lazy, I write it down here!

UPDATE table_one
SET column_one = ( SELECT table_two.column_two
 FROM table_two
 WHERE table_one.id = table_two.id)
WHERE EXISTS
  ( SELECT table_two.id
    FROM table_two
    WHERE table_two.id = table_one.id);

Survival series - Sql Server, Stored Procedures with non mandatory parameters

With this post I begin the "Survival Series", i.e. some post with answers to little recurring problems that recurringly make many people waste a lot of times "reinventing the wheel".
Really little things, but useful, that I encounter in my programming life :-).

Let's start with this: How can I have a stored procedure with sime parameters that are actually "non-mandatory" ?
Well, this is really easy... but often I see people crash their heads on this "easy" subjects.
Let's see an example: I have the need to call a "search" stored procedure, that accepts 3 parameters (2 varchars and 1 integer) and based on these 3 values must query some tables and return the corresponding resultset; but from the calling app I can have only one, or two, or three values on these parameters.
What I write is (say we have a "Pubs" table):

CREATE PROCEDURE dbo.SearchPub
(
@txtCriteria VARCHAR(30),
@txtYear VARCHAR(4),
@idArgument INT
)
AS

SELECT *
FROM Pubs
where 1=1
AND (@txtCriteria IS NULL OR Content like '%' + @txtCriteria + '%')
AND (@txtYear IS NULL OR PubYear = @txtYear)
AND (@idArgument IS NULL OR Argument = @idArgument)
ORDER BY PubYear desc

GO


As you can see in the Where part, with the three "OR" I can easily manage calls like:

EXEC dbo.SearchPub NULL, NULL, NULL
EXEC dbo.SearchPub NULL, '2006', NULL
EXEC dbo.SearchPub "searching for this", NULL, 3


Hope this can be useful... Bye