SELECT @@name = "A SQL Professional"

Free Brainbench certification

amitjethva — Fri, 02 Jul 2004 18:36:00 GMT

Brainbench.com is offering free exams during 1^st july 2004 to 15^th july 2004. I gave SQL Server Server Server 2000 exams for DBA and developer to examine my skillsets. It is worth taking a shot at your skills.

Currently I stand at 6^th in SQL Server Server ADMIN Scores !

My Results as on now: Brainbench transcript id 1118376

Certification Name: Master MS SQL Server Server Server Server Server 2000 Administration
Date:                2004-07-02
Authority:           Brainbench
Score:               4.61 (Scale of 1 - 5 where 5.0 = Best)
                     Higher than 99% of all previous test takers

--------------------------------------------------------------------
Certification Name: MS SQL Server Server Server Server Server 2000 Programming
Date:                2004-07-02
Authority:           Brainbench
Score:               3.72 (Scale of 1 - 5 where 5.0 = Best)
                     Higher than 97% of all previous test takers.
--------------------------------------------------------------------

How do I use SQL Server Express

amitjethva — Thu, 01 Jul 2004 19:24:00 GMT

I downloaded SQL Server 2005 Express Edition and managed to install it successfully. It took me some time to realise that the install did not gave me any particular GUI tool to use. !!!

I tried Enterprise manager / ISQLW (of sql 2000) and came across this message !!!

“A connection could not be established to TESTSERVER\SQLEXPRESS.

Reason: [SQL-DMO]You must use SQL Server Server Workbench or SQL Server Server Management Objects (SMO) to connect to this server..

Please verify SQL Server Server is running and check your SQL Server Server registration properties (by right-clicking on the TESTSERVER\SQLEXPRESS node) and try again.“

from the MS website, I came to know that “SQL Server Express Manager“ will be available August 2004.

Can anyone tell me how do I use this engine without a good front-end such as EM or ISQLW.

GMAIL first look

amitjethva — Mon, 21 Jun 2004 15:16:00 GMT

Finally, I also have a GMAIL account ( thanks manuj for this.)

My First Login attempt to gmail came up with this ..

Missing Numbers in a IDENTITY COLUMN

amitjethva — Fri, 18 Jun 2004 19:59:00 GMT

I came across a post in a forum related to finding out range of missing numbers in an identity column. (or a numeric column!)

There were a couple of solutions suggested by members. such as

SELECT T.IdCol + 1 AS IdCol, ( SELECT MIN(J.IdCol) FROM MyTable J WHERE J.IdCol > T.IdCol ) - IdCol - 1 AS Items
FROM MyTable T
WHERE ( SELECT MIN(J.IdCol) FROM MyTable J WHERE J.IdCol > T.IdCol) - T.IdCol >= 2
ORDER BY IdCol
GO

SELECT q.LowerMissing, q.UpperMissing, (q.UpperMissing - q.LowerMissing) AS [Difference] FROM
( SELECT (IdCol + 1) LowerMissing, (SELECT MIN(IdCol ) FROM MyTable WHERE IdCol > A.IdCol ) - 1 UpperMissing
FROM MyTable A WHERE
( SELECT MIN(IdCol ) FROM MyTable WHERE IdCol > A.IdCol ) - IdCol >= 2
) q
ORDER BY [Difference] desc

I worked out one for myself.

SELECT * , q.UpperMissing - q.LowerMissing + 1 TotalMissing FROM
(
SELECT A.IdCol + 1 LowerMissing , (SELECT MIN( IdCol ) FROM TableName WHERE IdCol > A.IdCol ) - 1 UpperMissing
FROM TableName A
) Q
WHERE (q.UpperMissing - q.LowerMissing) >= 0
ORDER BY 2

Tool for blog

amitjethva — Wed, 16 Jun 2004 19:10:00 GMT

Hi people,

I found another tool called "Sauce Reader". This piece of software allows me to read and write blogs from my desktop.

This is a cool feature combined together in a single software!! It is in a beta version. but worth a try.

w.bloggar v 3.00

amitjethva — Wed, 16 Jun 2004 16:33:00 GMT

Hi friends,

This is a post created to test a tool called w.bloggar.

It is a software that gives me a rich editor for publishing my blog post from my desktop (without using browser). looks great to use !!

Ever Tried This ???

amitjethva — Tue, 15 Jun 2004 19:22:00 GMT

Connect to any of your SQL Servers (I used 2000) and execute following command.

backup table authors to DISK ='c:\authors.bak'

Is this going to be implemented in YUKON ?

Non-Deterministic UDF's -- Custom Aggregate and Cumulative Functions

amitjethva — Fri, 04 Jun 2004 15:16:00 GMT

A beautiful use of UDFs and sp_Oa procedures.

http://weblogs.sqlteam.com/jeffs/articles/1490.aspx

Another approach to CROSSTAB queries.

amitjethva — Fri, 04 Jun 2004 12:28:00 GMT

http://www.stephenforte.net/owdasblog/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8

/* the way shown in that blog */

SELECT CompanyName, SUM((UnitPrice*Quantity)) As TotalAmt,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-1)))) ,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-2)))) ,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-3)))) ,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-4)))) ,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-5)))) ,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-6)))) ,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-7)))) ,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-8)))) ,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-9)))) ,
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-10)))),
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-11)))),
SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-12))))

FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
Group By Customers.CompanyName

/* MY WAY -- takes 1 less step compared to previous in Query Execution Plan */

SELECT CompanyName, SUM((UnitPrice*Quantity)) As TotalAmt,
SUM( CASE DatePart(mm,OrderDate) WHEN 1 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 2 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 3 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 4 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 5 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 6 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 7 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 8 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 9 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 10 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 11 THEN (UnitPrice*Quantity) ELSE 0 END ) ,
SUM( CASE DatePart(mm,OrderDate) WHEN 12 THEN (UnitPrice*Quantity) ELSE 0 END )

FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID

Group By Customers.CompanyName

Trackback

@@TIMETICKS ? ?

amitjethva — Thu, 27 May 2004 17:03:00 GMT

I was going through the BOL and i noticed the function @@TIMETICKS.

I wonder what is the practical use of this function! A quick search on GOOGLE did not gave me anything special on @@TIMETICKS except the defination.

@@TIMETICKS

Returns the number of microseconds per tick.

Syntax

@@TIMETICKS

Return Types

integer

Remarks

The amount of time per tick is computer-dependent. Each tick on the operating system is 31.25 milliseconds, or one thirty-second of a second.

Hi bloggers

amitjethva — Wed, 26 May 2004 15:18:00 GMT

Hi,

This is my first post as a blogger. Thanks to my friend Manuj (Enigma) for introducing me to the blogging.

I started my career as Developer on VB/PB/ASP/APT - SQL/Sybase/Oracle. Moved to SQL Admin first in Nov 2000 on SQL 7.0 and later on to SQL 2000, but with more of a developer workload.

Since last 2 years or so, I have been focusing more on SQL Admin stuff. and I have enjoyed working on SQL Server. My main areas of intrest in SQL are SQL Database Design, Scripting, troubleshooting and Perfomance Tuning.

Now, I am looking forward to SQL Server 2005 ~~Yukon~~ to arive.