August 2007 - Posts

Import/Export to Excel

Apart from using DTS and Import/Export wizard, we can also use this query to export data from SQL Server2000 to Excel and vice versa

To export data from SQL Server table to Excel file, create an Excel file named testing having the headers same as that of table columns and use this query

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

To export data from Excel to new SQL Server table,

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'SELECT * FROM [Sheet1$]')

To export data from Excel to existing SQL Server table,

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'SELECT * FROM [SheetName$]')

Generate SQL Script

--This procedure will generate the structure of the table

Create Procedure GenerateScript (           
@tableName varchar(100))           
If exists (Select * from Information_Schema.COLUMNS where Table_Name= @tableName)           
declare @sql varchar(8000)            
declare @table varchar(100)            
declare @cols table (datatype varchar(50))          
insert into @cols values('bit')          
insert into @cols values('binary')          
insert into @cols values('bigint')          
insert into @cols values('int')          
insert into @cols values('float')          
insert into @cols values('datetime')          
insert into @cols values('text')          
insert into @cols values('image')          
insert into @cols values('uniqueidentifier')          
insert into @cols values('smalldatetime')          
insert into @cols values('tinyint')          
insert into @cols values('smallint')          
insert into @cols values('sql_variant')          
set @sql=''            
Select @sql=@sql+             
case when charindex('(',@sql,1)<=0 then '(' else '' end +Column_Name + ' ' +Data_Type +             
case when Data_Type in (Select datatype from @cols) then '' else  '(' end+
case when data_type in ('real','money','decimal','numeric')  then cast(isnull(numeric_precision,'') as varchar)+','+
case when data_type in ('real','money','decimal','numeric') then cast(isnull(Numeric_Scale,'') as varchar) end
when data_type in ('char','nvarchar','varchar','nchar') then cast(isnull(Character_Maximum_Length,'') as varchar)       else '' end+
case when Data_Type in (Select datatype from @cols)then '' else  ')' end+
case when Is_Nullable='No' then ' Not null,' else ' null,' end           
from Information_Schema.COLUMNS where Table_Name=@tableName            
select  @table=  'Create table ' + table_Name from Information_Schema.COLUMNS where table_Name=@tableName           
select @sql=@table + substring(@sql,1,len(@sql)-1) +' )'           
select @sql  as DDL         


Select 'The table '+@tableName + ' does not exist'           

Select Data from Top N Columns

Suppose you have table with many columns and often you need data from first 15 or 20 columns. You have to specify all the columns in your select statement. This procedure will select top N columns you want. All you have to do is to supply table name and number of columns you want

Here is the procedure

CREATE procedure TopNcolumns (@tableName varchar(100),@n int) 
Declare @s varchar(2000)   
set @s='' 
If @n>=0  
set rowcount @n 
Select @s=@s+','+ column_name from information_schema.columns  
where table_name=@tablename order by ordinal_position 
Set rowcount 0 
Set @s=substring(@s,2,len(@s)-1) 
Exec('Select '+@s+' from '+@tablename
Select 'Negative values are not allowed' as Error 


If you execute TopNColumns 'Mytable',12  then first 12 columns with data from the table Mytable will be displayed

Posted by Madhivanan with no comments
Filed under: ,

Backup Database

This query will backup the current database

declare @sql varchar(1000)
select @sql = 'BACKUP DATABASE '+(select db_name())+' TO DISK = ''E:\'+(select db_name())+

If the database name is Test then executing this query will backup Test database and name of that backup file is Test20050817.bak. Current date with the format yyyymmdd will be suffixed to the DBName

If you schedule this query to run daily, then backup will be generated daily as a seperate file

Posted by Madhivanan with no comments

Generate Random Numbers


This procedure will display n random numbers selected from specified range

Pass Starting Number, Ending Number and the number of numbers you want to select randomly

Create Procedure RandomNumbers (@min bigint, @max bigint, @count int)
Set Nocount On

Declare @t table(num bigint)
Declare @start bigint
Declare @End bigint

While @min<=@max

Insert into @t values(@min)
set @min=@min+1

Set Rowcount @count
Select num from @t order by newid()
Set Rowcount 0


If you run the below code

Exec RandomNumbers 100,300,10

10 random numbers between 100 and 300 will be displayed


Posted by Madhivanan with no comments

Scripts Stored Procedures and Functions through query

This query will script the Procedures and Functions

All you have to do is replace DBname by the actual Database Name

exec master..xp_cmdshell
'bcp "Select routine_definition from DBname.information_Schema.routines order by routine_name" queryout "C:\scripts.sql" -c'

After it runs successfully, the file C:\scripts.sql will have the scripts


Posted by Madhivanan with 4 comment(s)

BCP - Export data to Text File

Here is a simple method of exporting all the data from SQL Server table to a Text File

CREATE Procedure BCP_Text_File

@table varchar(100), 
@FileName varchar(100) 

If exists(Select * from information_Schema.tables where table_name=@table)
        Declare @str varchar(1000) 
        set @str='Exec Master..xp_Cmdshell ''bcp "Select * from '+db_name()+'..'+@table+'" queryout "'+@FileName+'" -c''' 
    Select 'The table '+@table+' does not exist in the database'


Execute this procedure by giving Table Name and the File Name

EXEC BCP_Text_File 'Employee','C:\emp.txt'

Now all the data from Employee table will be exported to the text file which will be located at C:\emp.txt


Posted by Madhivanan with 1 comment(s)

Get IP Address

Here is a method of getting the IP Address of a system using Stored Procedure

Create Procedure Get_IPAddress
(@sysName varchar(30))
 Set Nocount On

 Create table #t (data varchar(100))
 Declare @sql varchar(100)
 Set @sql='Ping -n 1 '+@sysname

 Insert into #t
 Exec Master..xp_cmdShell @sql
 If exists (select * from #t where data like '%Unknown%')
  Select 'The system '+@sysName+' doesnt exist' as Error
  Select Substring(data,charindex('[',data,1)+1,charindex(']',data,1)-(charindex('[',data,1)+1))
  as IPAddress from #t where data like '%bytes of data%' 
 drop table #t
 Set Nocount Off

Run this procedure as

EXEC Get_IPAddress 'systemName'

Posted by Madhivanan with no comments
Filed under: ,

Storing Images

I have read many questions in the Forums asking "How do I store images in the table?". The simplest answer would be to store only the physical path of the images in the table and storing the images in the server's physical directory. To retrieve the image use Presentation layer's File System object. This makes easy the process of storing and retrieving the images.Otherwise AppendChunk and GetChunk methods should be used in the Front End Application.

Here are the complete explanations on how to handle images;en-us;317016

Posted by Madhivanan with 1 comment(s)


This freeware tool is used to execute sql,procedures,etc and export the result to EXCEL,PDF,HTML,etc format

Try it here

More Posts Next page »