Restoring SQL Server 2005 Suspect Database

I had a SQL Server 2005 database in suspect mode,so i couldn't work on transaction until i repaired,while i am searching on the internet to resolve this problem i found an new Database status called Emergency introduced in SQL Server 2005.
This mode can change the database from Suspect mode to Emergency mode, so that you can retrieve the data in read only mode.
Please follow the following steps to change the database status from suspect to emergency than to it's normal state:

EXEC sp_resetstatus 'DBname'

ALTER DATABASE DBname SET EMERGENCY

DBCC checkdb('DBname')

ALTER DATABASE DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('DBname', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE DBname SET MULTI_USER

I hope that will help you to get your database working

FTP Put file using t-sql

I have to use a stored procedure which will send/Receive text file
from/to a server by using FTP.

So i googled on the net and i found a very interesting stored procedure written by Nigel Rivett
that will upload a file from the database.
But before using this stored procedure on SQL Server 2005,XP_CMDSHELL must be enabled,
because it's disabled by default on SQL Server 2005 for security reasons.
There are two ways to enable this option: Surface Area Configuration tool or sp_configure.
Enabling this option via Surface Area Configuration tool is rather straight forward.
Here is the instructions to enable xp_cmdshell in SQL Server 2005 via Surface Area Configuration tool:

1. Click the Start button.

2. Select All Programs.

3. Navigate to the Microsoft SQL Server 2005 folder.

4. On the flyout, mouseover Configuration Tools.

5. Select SQL Server Surface Area Configuration.

6. The SQL Server 2005 Surface Area Configuration window will appear.

7. At the bottom of the window, select Surface Area Configuration for Features.

8. In the left pane, under Database Engine, select xp_cmdshell.

9. Check the Enable xp_cmdshell checkbox.

10. Click OK.

11. Close the SQL Server 2005 Surface Area Configuration window.

Here is the script to enable xp_cmdshell in SQL Server 2005 via sp_configure:

EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE


-- FTP Put file using t-sql.
-- Author Nigel Rivett

This needs a work directory to create a FTP command file to execute.
In a multi-user system include the spid in the filename to make it unique for the connection.


if exists (select * from sysobjects where id = object_id(N'[dbo].[s_ftp_PutFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ftp_PutFile]
GO


Create procedure s_ftp_putfile
@FTPServer varchar(128) ,
@FTPUser varchar(128) ,
@FTPPWD varchar(128) ,
@FTPPath varchar(128) ,
@FTPFileName varchar(128) ,
@SourcePath varchar(128) ,
@SourceFile varchar(128) ,
@workdir varchar(128)
as


declare @cmd varchar(1000)
declare @workfilename varchar(128)

/* usage:
exec s_ftp_putfile
@FTPServer = 'ftpserver' ,
@FTPUser = 'user' ,
@FTPPWD = 'password' ,
@FTPPath = 'path/' ,
@FTPFileName = 'test.txt' ,
@SourcePath = 'c:\' ,
@SourceFile = 'test.txt' ,
@workdir = 'c:\temp\'
*/

select @workfilename = 'ftpcmd.txt'

-- deal with special characters for echo commands
select @FTPServer = replace(replace(replace(@FTPServer, '', '^'),'<','^<'),'>','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '', '^'),'<','^<'),'>','^>')
select @FTPPWD = replace(replace(replace(@FTPPWD, '', '^'),'<','^<'),'>','^>')
select @FTPPath = replace(replace(replace(@FTPPath, '', '^'),'<','^<'),'>','^>')

select @cmd = 'echo ' + 'open ' + @FTPServer
+ ' > ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPUser
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPPWD
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'quit'
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd

select @cmd = 'ftp -s:' + @workdir + @workfilename

create table #a (id int identity(1,1), s varchar(1000))
insert #a
exec master..xp_cmdshell @cmd

select id, ouputtmp = s from #a

go