Importing data from excel sheets is not difficult, but what is the sheet names are dynamic. here is a faq written by me for this.
Saturday, March 19, 2011
Creating logins is not a rocket science in SQL Server. When creating a user, you might have seen an option where you can select default database.
So here you are assigning SampleDB to the user sql_user1
By assigning this, when use is logged in, he will be taken to SampleDB so that he he doesn’t have to change the database.
But what if this database is dropped later or permission for the user for this database is revoked.
So you have problems of logging to the SQL Server and you can change the default database to got away with this error.
In the login page, you can change the connection properties to connect to any database.
So what is the best database you should select as default database.
I will go for the tempDB for few reasons.
- All users has access to tempdb, so that logins will not failed.
- If default database is master, there is a change that mistakenly you will create objects like tables etc in that and most of the time you won’t be dropping them. In case, those objects are created in Tempdb, they will be dropped when the SQL Server restarts again!
Saturday, March 12, 2011
SQL Server Management Studio (SSMS) is nice tool, but there are instances where it will mislead you. This is one of them I found.
Every DBA knows that @@SPID will returns the session ID of the current user process.
You can view the SPID by running following query.
Apart from this you can use the SSMS to view the SPID as well. If you look down the tool bar of the query window of the SSMS as shown below.
Now, theoretically @@SPID and above value should be same, which you can see from image below
Let me restart the SQL Server instance and let us check what will happen to the above query now.
Now then, it is a contradiction. Obviously, @@SPID cannot be wrong and it is the SSMS the culprit, where SSMS won’t refresh.
So the conclusion, don’t trust SSMS too much.
Friday, March 4, 2011
Main aim was this presentation is to make awareness among the SQL Server community about the extended events. It was level 100 session.
The presentation and sample scripts are available for downloading:
Tuesday, March 1, 2011
You can set the row count,
SET ROWCOUNT 50
When you execute following statment,
SELECT * FROM dbo.RowCountTable
you will get only 50 records regardless of number of rows you have.
But what if you execute a query like that.
SET ROWCOUNT 2147500000
When you execute this, you will get following error.
Msg 1080, Level 15, State 1, Line 1
The integer value 2147500000 is out of range.
Mind you, maximum value for interger, 2,147,483,647 since 2147500000 is more than the maximum of the integer and row count is expecting a value of a interger.
So when you are passing a value to a ROW COUNT, it has to be a integer value.
Do not use something like below,
DECLARE @rccount bigint
SET ROWCOUNT @rccount
Though there won't be any issues, if you pass values less than maximum of integer, but make sure those parameters are integer.