SQL-Articles

Couple of days back I saw a question on MSDN forums where a user is not getting any warnings when using isql (commandline utility) and he is getting warnings when using isqlw (query analyzer GUI) for the same query. I’m just writing this article because many of them face this problem when they do batch processing data’s through isql.

Example:
Consider you are having a table with column char(3) and if you insert a value which has 4 characters then sql client utility should throw a error. When you connect SQL Server using query analyzer, then Ansi_warnings are on by default and you will receive this error & the transaction will not happen. However if you connect SQL server using isql command line utility then you won’t get this error, instead it will truncate the last character and insert it into the table. Lets check it out! Read more…

General Administration, SQL Server

Introduction

In most of the forums I’m seeing a thread how to move databases from one server to another server. Seeing those things I’ve written this article to simple the task. Here in this article I’m going to provide you some steps to move all the databases (including system db) from one server to another server (hope no error should happen).

Steps to Move the database

Step1: Before detaching the db you need to script default database for all the logins, because if the user db is default db for logins then after detaching the db the login will point NULL and hence when the user logs in he will be in trouble. You can use the script “Script Default Database” to script all the default databases for the logins. Execute the script and save the output.

Step2: Once the above step completed you can detach all the user databases except system db’s (master, msdb, tempdb & model) in the source server. You can use the script “Detach User Databases” to detach all the user databases in the source server.

Step3: Stop SQL Service in the source server and copy all physical files (mdf, ldf & ndf) of the dbs to the destination server

Step4: Once the above step completed successfully, you need to first attach the system databases databases from the new path (i.e the path where you have copied the physical files). You can make use the articles “Moving System Databases - SQL Server 2005” or “Moving system databases — SQL Server 2000” to attach the system databases from new path.

NOTE: For moving system dbs you need to have same SQL edition, version and build in both source and destination servers. If you are not planning to move system db’s just skip this step.

Step5: Attach all the databases in the destination server from the new path.
Step6: Once the above steps completed successfully, start sql services, check errorlog for any errors.
Step7: Once you find everything perfect execute the script (saved in step1) to map all the logins back to its default databases and start using the applications with the respective FIDs.

Discussion

All your healthy discussion in this topic are most welcomed @ FORUMS section.

General Administration, SQL Server

Introduction

Services uptime can be checked through WMI scripts and other methods also. As a DBA most of us would like to know the uptime of SQL Server, i.e how much time is SQL Server running till the server is on. You can do almost all the stuffs in T-SQL, hence I’m writing the script in T-SQL to find out SQL Server Uptime. You can also use this script to check SQLServer service and SQLAgent server are running status!

SQLScript:

SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE NAME=‘tempdb’
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
PRINT ‘SQL Server “‘ + CONVERT(VARCHAR(20),SERVERPROPERTY(‘SERVERNAME’))+‘” is Online for the past ‘+@hr+‘ hours & ‘+@min+‘ minutes’
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N’SQLAgent - Generic Refresher’)
BEGIN
PRINT ‘SQL Server is running but SQL Server Agent <<NOT>> running’
END
ELSE BEGIN
PRINT ‘SQL Server and SQL Server Agent both are running’
END

Output:

SQL Server “SAGARSYS” is Online for the past 2 hours & 48 minutes
SQL Server and SQL Server Agent both are running

Scripts, T-SQL SQL Server, T-SQL

I’m writing this script since most of them are looking a way to find total diskspace available in a drives through sql server. I hope there is no extended procedure for this. I’ve used WMI script to do this, download the vbs script and save it to a location and use this location in the sql script to get the result.

SQL Script

SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME =‘##tmp’)
DROP TABLE ##tmp
CREATE TABLE ##tmp(diskspace VARCHAR(200))
INSERT ##tmp
EXEC master.dbo.xp_cmdshell ‘cscript C:\diskspace.vbs’ – change the path here
SET ROWCOUNT 3
DELETE ##tmp
SET ROWCOUNT 0
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME =‘##tmp2′)
DROP TABLE ##tmp2
CREATE TABLE ##tmp2(Driveletter VARCHAR(2),TotalDiskSpace_in_MB FLOAT, Freespace_in_MB FLOAT)
INSERT ##tmp2
SELECT SUBSTRING(diskspace,1,3) , CONVERT(FLOAT,SUBSTRING(diskspace,4,10)),
CONVERT(FLOAT,SUBSTRING(diskspace,15,10)) FROM ##tmp WHERE diskspace IS NOT NULL
SELECT * FROM ##tmp2

Sample Output

Download diskspace.vbs

Scripts, T-SQL, Uncategorized Diskspace, SQL Server, T-SQL