Harshal Mistry

SQL Server 2005 - September CTP

Download the Community Technology Preview (CTP) of Microsoft SQL Server 2005 :

http://www.microsoft.com/downloads/details.aspx?familyid=6e4aac3a-9d85-4734-b1fd-318fb83b0d29&displaylang=en

Download a stand-alone copy of the documentation and tutorials for the Microsoft SQL Server 2005 Community Technology Preview (CTP) September 2005 :

http://www.microsoft.com/downloads/details.aspx?familyid=adc75e35-7245-4038-9b8a-b8fabaec16da&displaylang=en

Truncating / Shrinking transaction log files

To Truncate the log file:

Backup the database (I always prefer to backup first to be on the safer side ;)).

Detach the database, either by using EM or by executing :

Sp_detachDB [DBName]

Delete the transaction log file. (I prefer to rename the file, just in case if something goes wrong)

Then attach the database again using:
Sp_AttachDB [DBName]

When the database is attached, it creates a new transaction log file.

To shrink the log first backup the log using:

backup log [DBName] with no_log

Using Enterprise manager:

Right click on the database, All tasks, Shrink database, Files, Select log file, OK.

Using T-SQL:

dbcc shrinkfile ([log_name])

Here [log_name] is the logical name of the log file as found from sp_helpdb

server connections history

once you connect to the sqlserver , the server details are maintained as a history and the next time you want to connect to the server you get a dropdown to select from the previously connected servers. Now if you want to delete this history, there is no GUI tool available for it. The trick to delete this history is to delete the registry entries: HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\Client\PrefServers clear the entries from prefservers.. this should work. since it is a registry modification, do it on your own risk.

"Not Associated with a trusted SQL Server Connection"

"Not Associated with a trusted SQL Server Connection" when I try to connect to an SQL database and use a valid SQL username?

This is a very common question I have come across.
This occurs typically when Microsoft Sql Server installation is configured to use only Windows authentication and not SQL Server authentication.
So when u try to login using sql server account this error occurs.

To solve the problem: Change the Authentication change the authentication mode to sql server authentication.
1. Start SQL Server Enterprise Manager (Start, Programs, Microsoft SQL Server, Enterprise Manager).
2. Navigate to the SQL Server system in the Microsoft Management Console (MMC) treeview pane by selecting Console Root,
Microsoft SQL Servers, SQL Server Group, (or ); right-click the server; and select Properties from the context menu.
3. In the SQL Server Properties dialog box, select the Security tab.
4. In the Security section, make sure that Authentication is set to "SQL Server and Windows" and not "Windows only" .

what is better constraint or Trigger??

what is better constraint or Trigger??
If it is a job of just referential integrity I would use a constraint, where as if more complex logic is to be performed, then trigger.
constraints are proactive - they prevent unwanted actions from happening.
Triggers are reactive - they rollback the damage.
While deleting a record from a table a constraint would tell not to delete a parent record if child records exist.
A trigger rolls back the delete operation if you try to delete a parent record having child records.
Constraints apply to underlying table.
Triggers can access multiple tables(even tables in other databases).
With a trigger you can generate more custom and understandable and user friendly error messages as compared to cryptic messages returned by the constraints.

These are a few points to compare.. more are welcome.

Security hole found in Gmail

An Israeli hacker reveals: A security hole in Gmail allows the compromise of users' email boxes – without the need of a password.

Link

Unit testing TSQL

All good programmers test their code. But can testing be easier? Can it be better? S. A. Miller shows how unit tests can help, along with an open source testing framework called TSQLUnit.

check the article here

Set ANSI_NULLS

Set ANIS_NULLS
For something like
select * from foo1 where a not in (select a from foo2)
If anis_nulls is ON and if the table foo2 is empty no records from foo1 are selected.
If this comparision statement exits inside a stored procedure then ANSI_NULLS setting should be done before the compilation of the stored procedure, if the set statement is inside the procedure, the ANSI_NULL setting is not affected.
From BOL:
For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its originally used value and takes effect. When invoked inside a stored procedure, the setting of SET ANSI_NULLS is not changed.

I never knew this. so when today there was a bug in a stored procedure I tried to debug it, I was not able to find anything wrong with it, since the same script was working on my local machine, after drilling it down to NULL comparision
found the above thing. so recompiled the sp with set options at the start before the create statement which solved the problem.
Also remember: The setting of SET ANSI_NULLS is set at execute or run time and not at parse time.

New features in sql server 2005 beta3

have a read here

Google Desktop search

Google has launched the desktop search engine Google Desktop search beta yesterday.
Google says that it is a desktop search application that provides full text search over all the files.
Seems to be a cool app, but I do have a few doubts about it... like it tracks all the activity done on the machine right from the normal work done to the emails recieved it tracks all the websites visited and stuff..... so how secure is it anyway?
anyone who get access to my machine knows what I was been doing what sites I have browsed ... is that the case??
I have downloaded the installable but not yet installed it cause it says that after installing it, it needs to index all the files on the hard disk which might take hours... so planning to install it tonight while leaving ... lets see how it comes up.
you can download it here

know more about google desktop search beta

calculating the row size

Is anyone aware how to calculate the occupied size of a row in a table?

Gmail invitiees are back

It seems the Gmail invitations are back... I got a coupla them, if anyone is interested write me at harshalmistry@gmail.com

MS Skill's Assessment

Recently I came across something called SQL Server Reporting Services Skills Assessment after diggin in found that MS offers of assessments on a range of MS products also the site says “By completing a Microsoft Skills Assessment, you will receive a Personalized Learning Plan to help you understand if you are ready to implement specific business solutions. Your Personalized Learning Plan will include the Microsoft Official Curriculum courses, Microsoft Press books, and Microsoft TechNet resources that will help you with your preparation.”

So give it a try here.

Whats he upto?

The wipro CEO Azim Premji wants the Techie taxed!!!
Read the whole story here .

SNAPSHOT Isolation

Yukon introduces a new isolation level called SNAPSHOT which allows you to work in a mode where writers do not block readers, providing readers with a committed version of the data they request. SQL Server Yukon maintains a linked list in tempdb that tracks changes to rows and constructs an older, committed version of data for readers. This isolation is useful for optimistic locking, where UPDATE conflicts are not common. If Process 1 retrieves data and later attempts to modify it, and if Process 2 has modified the same data between the retrieval and modification, then SQL Server produces an error upon Process 1's modification attempt because of the conflict. Then, Process 1 can try to reissue the transaction. This mode can be efficient in situations where update conflicts are not common.
In order to allow working in a SNAPSHOT isolation level, you must turn on the database option ALLOW_SNAPSHOT_ISOLATION.