| MSSQL (6) |
| 1 |
I can not connect to my database through Enterprise Manager |
| 2 |
FTP through a stored procedure |
| |
This stored procedure takes four parameters:
1. file to ftp
2. server name or IP
3. ftp login
4. ftp password
SP will ensure that local file exists before continuing,
it will create and execute a batch file in order to push the file to the remote FTP server.
--exec example
EXEC dbo.up_FTPPushFile 'c:\temp\test.txt', 'server',
'user', 'password'
________________________________________________________________________________________________
create proc up_FTPPushFile
@file_to_push varchar(255),
@ftp_to_server varchar(255),
@ftp_login varchar(255),
@ftp_pwd varchar(255)
as
Set Nocount On
--STEP 0
--Ensure we can find the file we want to send.
Create table #FileExists (FileExists int, FileIsDir int, ParentDirExists int)
Insert #FileExists EXEC master.dbo.xp_fileexist @file_to_push
IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
BEGIN
Drop table #FileExists
RAISERROR ('File %s does not exist. FTP process aborted.', 16, 1, @file_to_push)
RETURN 1
END
--STEP 1
--Create xxx.bat batch file using bcp utility, file path/name is the same as @file_to_push
--batch file will hold 4 records:
--1) login
--2) password
--3) ftp command and file to push
--4) exit command
declare @sql varchar(255), @cmd varchar(255), @batch_ftp varchar(255), @ret int
set @sql = '"SELECT ftp_batch FROM ##temp_ftp_bat WHERE file_to_push = '''+ @file_to_push+'''"'
set @batch_ftp = Left(@file_to_push, Len(@file_to_push)-4) +'.bat'
set @cmd = 'BCP '+ @sql +' queryout '+ @batch_ftp +' /T /c'
Create table ##temp_ftp_bat(ftp_batch varchar(255), file_to_push varchar(255))
Insert into ##temp_ftp_bat values (@ftp_login, @file_to_push)
Insert into ##temp_ftp_bat values (@ftp_pwd, @file_to_push)
Insert into ##temp_ftp_bat values ('put '+@file_to_push, @file_to_push)
Insert into ##temp_ftp_bat values ('bye', @file_to_push)
EXEC master.dbo.xp_cmdshell @cmd
Drop table ##temp_ftp_bat
--STEP 2
--Ensure we can find the batch file we just created.
Delete #FileExists
Insert #FileExists EXEC master.dbo.xp_fileexist @batch_ftp
IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
BEGIN
Drop table #FileExists
RAISERROR ('Unable to create FTP batch file %s. FTP process aborted.', 16, 1, @batch_ftp)
RETURN 1
END
Drop table #FileExists
--STEP 3
--Execute newly created .bat file, save results of execution
Create table #temp_ftp_results (ftp_output varchar(255))
set @cmd = 'ftp -s:'+@batch_ftp+' '+@ftp_to_server
Insert #temp_ftp_results Exec master.dbo.xp_cmdshell @cmd
IF EXISTS (SELECT * FROM #temp_ftp_results WHERE (ftp_output like '%Login failed%' or ftp_output like '%Access is denied%'))
BEGIN
Drop table #temp_ftp_results
RAISERROR ('Unable to FTP file %s. Login failed or access denied. FTP process aborted.', 16, 1, @file_to_push)
RETURN 1
END
Drop table #temp_ftp_results
--STEP 3
--delete batch file
set @cmd = 'del '+@batch_ftp
EXEC master.dbo.xp_cmdshell @cmd
go |
| 3 |
Change ownership of a table through SQL |
| 4 |
How do a write a stored procedure? |
| 5 |
Is there a simple way to edit MSAccess databases without having to download, edit, then reupload it? |
| 6 |
How do I make an encrypted connection to the mssql 2005 server through sql studio express? |
