Exporting Data from SQL Server to Excel ....

There are different ways with which we can achive this ...Few of them to mention are as follows :

1) BCP ( Bulk Copy Program )-This ships with sql server
2) DTS ( Data Transformation Services )-Comes with SQL server 2000
3) SSIS ( Sql Server Integration Services ) - Sql server 2005 and above !
4) SQLCMD-command line utility - sql server 2005 and above
5) osql-command line utility - sql server 2000
6) .Bat scripts using one or more combination of above
( example :- .bat and bcp in it )

How to make bcp utility to append to a file ? ( Scheduling Tasks )

1) Create path to hold the running scripts

here in our example C:\testlab\s2

2) Create a batch script by name memory_in_use.bat

Code is as follows :

bcp "select object_name,counter_name,cntr_value/1024,getdate() from sys.dm_os_performance_counters where object_name='MSSQL$CSFSPD3TEST2:Memory Manager' and counter_name in ('Target Server Memory (KB)','Total Server Memory (KB)')" queryout C:\testlab\s2\dup_mem_res.xls -T -c -S CSCINDAE744358\CSFSPD3TEST2
cd C:\testlab\s2
copy mem_res.xls+dup_mem_res.xls tempfile.xls
del mem_res.xls
ren tempfile.xls mem_res.xls


3) Create a mem_res.xls excel sheet.Open the sheet and in the first row manually enter the columns one by one
Here for our example :

Counter_Name Object_Name ValueinMB Date


4) Create a dup_mem_res.xls excel sheet.

5) Now create a scheduled tasks from os as shown in the below figure and configure it to run every x minutes or hours as per your requirement :

Auditing user actions ( DML ) on a table !

Please find below the scripts that I have created to implement solution for logging DML user actions(Insert,Update and Delete) on a particular table.

I have thoroughly tested them and its error-free.( This works on sql server 2000,2005 and 2008 )

Step-1) Create a sample table called "Table1"

CREATE TABLE [dbo].[table1](
[id] [bigint] NULL
)

Step-2) Create a table called "Magic" to hold the user actions performed on the table "Table1"

create table magic
(
i bigint primary key identity(1,1),
action_taken varchar(25),
id bigint not null,
username varchar(50),
rw datetime default(getdate())
)

Step-3) Create a Trigger to fire upon Insert,Update or Delete operations on table "Table1"

create trigger trig1_table1
on table1
for insert,update,delete
as
begin
set nocount on

declare @in bigint
declare @de bigint
select @in=count(*) from inserted
select @de=count(*) from deleted
if(@in=@de)
begin
insert into dbo.magic(action_taken,id,username)
select 'updated',i.id,current_user
from inserted i
end
else if(@in>@de)
begin
insert into dbo.magic(action_taken,id,username)
select 'inserted',i.id,current_user
from inserted i
end
else if(@in<@de)
begin
insert into dbo.magic(action_taken,id,username)
select 'deleted',d.id,current_user
from deleted d
end

end

Step-4) Simulate the environmental values and know the results by fetching * from table "Magic"

insert into table1
values(258)

insert into table1
values(100)

update table1
set id=347
where id=258

select * from table1

delete from table1
where id=100

select * from magic--Magic is the table that has the log information of all the DML actions performed on the table called "Table1"

How to find the port no of a particular sql server instance !

Script-1
-------------
For default instance use below code :

DECLARE @tcp_port nvarchar(5)

EXEC xp_regread
@rootkey = ‘HKEY_LOCAL_MACHINE’,
@key = ‘SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP’,
@value_name = ‘TcpPort’,
@value = @tcp_port OUTPUT

select @tcp_port

For a named instance use the below code :
------------------------------------
DECLARE @tcp_port nvarchar(5)

EXEC xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\MICROSOFT\Microsoft sql server\CSFSPD3TEST2\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name = 'TcpPort',
@value = @tcp_port OUTPUT

select @tcp_port

Script to backup users in a db along with their db roles in Sql Server 2000 !

if exists(select * from tempdb.dbo.sysobjects where name like '#users%')
drop table #users

create table #users
(
uname nvarchar(150),
gname nvarchar(150),
lname nvarchar(150),
dbname nvarchar(150),
uid tinyint,
sid nvarchar(150)
)

insert into #users
exec sp_helpuser

declare @un sysname
declare @ln sysname
declare @rn sysname
declare @cmd1 nvarchar(500)
declare @cmd2 nvarchar(500)
declare @cu cursor
set @cu=cursor for
select uname,lname,gname from #users where uname!='dbo'

open @cu
fetch next from @cu
into @un,@ln,@rn
while(@@fetch_status=0)
begin
set @cmd1='exec sp_adduser @loginame='''+@ln+''', @name_in_db='''+@un+''''
print @cmd1
set @cmd2='exec sp_addrolemember @rolename='''+@rn+''',@membername='''+@un+''''
print @cmd2
print '--------'
fetch next from @cu
into @un,@ln,@rn
end

close @cu
deallocate @cu

Scripts to add logins !

create database test1

use test1

create table table1
(
id bigint
)

declare @i bigint
set @i=1
while(@i<=100)
begin
insert into table1
values(@i)
set @i=@i+1
end

select * from table1

sp_help sp_addlogin
sp_addlogin @loginame='sqlcat1',@passwd='sqlcat1',@defdb='master',@deflanguage='us_english'
sp_addlogin @loginame='sqlcat2',@passwd='sqlcat2',@defdb='master',@deflanguage='us_english'
sp_addlogin @loginame='sqlcat3',@passwd='sqlcat3',@defdb='master',@deflanguage='us_english'
sp_help sp_grantlogin
sp_grantlogin @loginame='asiapac\smadhavan7'


sp_help sp_adduser
sp_help sp_grantdbaccess
sp_helprole
sp_help sp_addrolemember

sp_adduser @loginame='sqlcat1',@name_in_db='sqlcat1',@grpname='public'
sp_adduser @loginame='sqlcat3',@name_in_db='sqlcat3',@grpname='db_owner'

sp_grantdbaccess @loginame='sqlcat2',@name_in_db='sqlcat2'
sp_addrolemember @rolename='db_owner',@membername='sqlcat2'

--using adduser or grantdbaccess+addrolemember are same

select * from sysusers

sp_helpuser

backup database test1
to disk='C:\backups\full_test1.bak'

sp_helpdb 'test1'

Capacity Planning - Calculating db growth % of a specific db !

select
bf.file_size/1048576 as "File Size in MB",
bf.backup_set_id,
bf.file_type,
(case bf.file_type
when 'D' then 'Database'
when 'I' then 'Differential'
when 'L' then 'Log'
when 'F' then 'File/Filegroup'
when 'G' then 'File Differential'
when 'P' then 'Partial'
else
'Partial Differential'
end) as 'Type_Desc',
bf.logical_name,
bf.physical_drive,
bf.state_desc,

bs.user_name as "backed up by user",
bs.backup_start_date,
bs.backup_finish_date,
(convert(varchar,datediff(hh,bs.backup_start_date,bs.backup_finish_date))+':'+convert(varchar,datediff(mi,bs.backup_start_date,bs.backup_finish_date))+':'+convert(varchar,datediff(ss,bs.backup_start_date,bs.backup_finish_date))) as "Backup Window Time",
bs.compatibility_level,
bs.database_version,
bs.database_name,
bs.server_name,
bs.machine_name,
bs.collation_name,
bs.is_password_protected,
bs.recovery_model

into kabul

from
msdb.dbo.backupfile bf
join
msdb.dbo.backupset bs
on bf.backup_set_id=bs.backup_set_id

where bf.file_type='D'
and
bs.database_name='dumbo'
order by bf.backup_set_id

--with this a new table kabul is populated with statistical data
--The following tweaked query from table kabul will display the growth %
select
a.database_name,
(convert(numeric(5,2),(
(a.[file size in mb]/(select b.[file size in mb] from kabul b where b.backup_set_id=
(select max(x.backup_set_id)
from kabul x
join kabul y
on x.backup_set_id=y.backup_set_id
where x.backup_set_id<
a.backup_set_id))*100)-100))) "Growth %",
a.[Backup Window Time],
a.backup_set_id,
a.backup_start_date,
a.backup_finish_date,
a.[File Size in MB],
a.file_type,
a.Type_Desc,
a.logical_name,
a.physical_drive,
a.state_desc,
a.[backed up by user],
a.compatibility_level,
a.database_version,
a.server_name,
a.machine_name,
a.collation_name,
a.is_password_protected,
a.recovery_model
from
kabul a