顯示具有 SQL Server 標籤的文章。 顯示所有文章
顯示具有 SQL Server 標籤的文章。 顯示所有文章

2013年2月26日 星期二

SQL Server: Restore from file


RESTORE DATABASE PSIWebV3
FROM DISK = 'C:\temp\PSIWebV3.dat'
WITH REPLACE

2011年3月11日 星期五

Truncate Log file in SQL Server 2008: Shrink database

BACKUP log with truncate_only

DBCC SHRINKfile(2, 1, truncateonly)

2009年6月11日 星期四

Set up SQL mail on SQL Server 2005

Solve the problem on "Enable the use of 'Database Mail XPs' by using sp_configure"

USE Master
GO
sp_configure 'show advanced options', 1
GO
reconfigure with override
GO
sp_configure 'Database Mail XPs', 1
GO
reconfigure
GO
sp_configure 'show advanced options', 0
GO

at 6/09/2009 02:00:00 PM

2009年6月9日 星期二

SQL Mail setup with exchange server in SQL Server 2000

1. Use the user who will be in the profile in Outlook log on to system.
2. Setup mail in Outlook with exchange server. Remeber the Profile name we setting in the Outlook (Default may be named "Outlook").
3. Go to SQL Mail Configuration under Support Service in SQL Server 2000
4. Type in the profile name you setting in the Outlook. (You will found Error occur)
5. Go to Administrative Tools/Service. Find two services call SQLAgent and MSSqlServer.
6. Right click on this two service. In the Logon tab. Check This account. Type in the user's username, password and comfirm password.
Done!
at 4/26/2007 03:59:00 PM

DUMP TRANSACTION LOG on SQL Server

dump transaction test with no_log
DBCC SHRINKFILE(PSIWeb_log, 20)
BACKUP LOG PSIWeb WITH TRUNCATE_ONLY
DBCC SHRINKFILE(PSIWeb_log, 1)
GO
exec sp_detach_db 'PSIWeb', 'true'
exec sp_attach_single_file_db @dbname = 'PSIWeb',
@physname = 'C:\MSSQL7\Data\PSIWeb_Data.MDF'
at 3/06/2007 10:23:00 AM

SQL Server: export Resultsets into Text file and Retrieve back

create table #errorlog(line varchar(2000))
execute master.dbo.xp_cmdshell 'osql -S10.1.1.6 -E -Q"execute PSIWebV3.dbo.sp_Test" -o"c:\temp\sp_out.txt" -s"" '
insert into #errorlog
execute master.dbo.xp_cmdshell 'type "c:\temp\sp_out.txt" '
select line from #errorlog
at 1/19/2007 05:18:00 PM