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',
'Excel8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

To export data from Excel to existing SQL Server table,

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')



Generate SQL Script



--This procedure will generate the structure of the table

Create Procedure GenerateScript (           
@tableName varchar(100))           
as           
If exists (Select * from Information_Schema.COLUMNS where Table_Name= @tableName)           
Begin            
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         
  
End           

Else        


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) 
as   
Declare @s varchar(2000)   
set @s='' 
If @n>=0  
Begin 
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
End 
else 
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())+
convert(varchar,GETDATE(),112)+'.bak'''
Exec(@sql)

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)
as
Set Nocount On

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

While @min<=@max

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

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) 

as 
If exists(Select * from information_Schema.tables where table_name=@table)
    Begin
        Declare @str varchar(1000) 
        set @str='Exec Master..xp_Cmdshell ''bcp "Select * from '+db_name()+'..'+@table+'" queryout "'+@FileName+'" -c''' 
        Exec(@str) 
    end
else
    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))
as
 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
 else
  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

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx

http://support.microsoft.com/default.aspx?scid=kb;en-us;317016



Posted by Madhivanan with 1 comment(s)

SQLAnswers



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

Try it here

http://www.sqlanswers.com/Software/



More Posts Next page »