Ftp upload from sql server 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Ftp upload from sql server 2005

Author

Topic

tejicen
Starting Member

17 Posts

Posted - 2009-07-14 : 04:53:25

Hi,

When I am trying to execute this sp I get the message "The specified schema name "Ftp" either does not exist or you do not have permission to use it."

Can someone help me.

//tejicen

IF NOT EXISTS(SELECT SCHEMA_ID('Ftp'))
EXEC('CREATE SCHEMA Ftp') -- CREATE SCHEMA needs to be the first statement of a batch

IF EXISTS (select * from sysobjects where id = object_id(N'Ftp.[PutFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [Ftp].[PutFile]

GO

CREATE PROCEDURE [Ftp].[PutFile]
(

@FTPServer nvarchar(128) ,
@FTPUser nvarchar(128) ,
@FTPPWD nvarchar(128) ,
@FTPPath nvarchar(128) ,
@FTPFileName nvarchar(128) ,
@SourcePath nvarchar(128) ,
@SourceFile nvarchar(128) ,
@WorkDir nvarchar(128)
)
AS
SET NOCOUNT ON;

exec [FTP].[PutFile]
@FTPServer = 'www.mobill.se' ,
@FTPUser = 'mobill' ,
@FTPPWD = 'djek49' ,
@FTPPath = '/ftp_sql/' ,
@FTPFileName = 'from_sql_2005.txt' ,
@SourcePath = 'c:\sqlsource\' ,
@SourceFile = 'sqlsource.txt' ,
@workdir = 'c:\temp\'

declare @cmd varchar(1000)
declare @workfilename varchar(128)
DECLARE @returncode int

/* We insert a 4 character random string to the temporary ftp script - Otherwise two concurrent calls would interfere w/ eachother */
select @workfilename = 'ftpcmd_{rand}.txt'
SELECT @workfilename = REPLACE(@workfilename,'{rand}', LEFT(CAST(NEWID() AS nvarchar(max)),4))

-- 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, NO_OUTPUT
select @cmd = 'echo ' + @FTPUser + '>> ' + @workdir + @workfilename

exec master..xp_cmdshell @cmd, NO_OUTPUT
select @cmd = 'echo ' + @FTPPWD + '>> ' + @workdir + @workfilename

exec master..xp_cmdshell @cmd, NO_OUTPUT
select @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName + ' >> ' + @workdir + @workfilename

exec master..xp_cmdshell @cmd, NO_OUTPUT
select @cmd = 'echo ' + 'quit' + ' >> ' + @workdir + @workfilename

exec master..xp_cmdshell @cmd, NO_OUTPUT
select @cmd = 'ftp -s:' + @workdir + @workfilename

DECLARE @result TABLE (id int identity(1,1), s varchar(1000))
insert @result
exec @returncode = master..xp_cmdshell @cmd

IF(@returncode = 0)
INSERT @result SELECT 'FTP command successfully executed'
select [output] = s from @result WHERE s IS NOT NULL AND s NOT LIKE 'quit'

/* Remove the ftp cmd afterwards*/
select @cmd = 'delete ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd, NO_OUTPUT
go

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-14 : 04:58:23

"SELECT SCHEMA_ID('Ftp')" will always return 1 row so the not exists() will always be false

quote:
IF NOT EXISTS(SELECT SCHEMA_ID('Ftp'))
EXEC('CREATE SCHEMA Ftp') -- CREATE SCHEMA needs to be the first statement of a batch

check for is null instead

IF schema_id('Ftp')) is null

KH
[spoiler]Time is always against us[/spoiler]

tejicen
Starting Member

17 Posts

Posted - 2009-07-14 : 06:00:18

Hi,

Now I get this message when I executed th sp

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

kind regards, tejicen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-14 : 06:03:12

the following section is it mean to be there ?

exec [FTP].[PutFile]
@FTPServer = 'www.mobill.se' ,
@FTPUser = 'mobill' ,
@FTPPWD = 'djek49' ,
@FTPPath = '/ftp_sql/' ,
@FTPFileName = 'from_sql_2005.txt' ,
@SourcePath = 'c:\sqlsource\' ,
@SourceFile = 'sqlsource.txt' ,
@workdir = 'c:\temp\'

KH
[spoiler]Time is always against us[/spoiler]

tejicen
Starting Member

17 Posts

Posted - 2009-07-14 : 06:24:37

Yes,

//tejicen

quote:
Originally posted by khtan

the following section is it mean to be there ?
exec [FTP].[PutFile]
@FTPServer = 'www.mobill.se' ,
@FTPUser = 'ftpmobill' ,
@FTPPWD = 'Ftp@dmin' ,
@FTPPath = '/ftp_sql/' ,
@FTPFileName = 'from_sql_2005.txt' ,
@SourcePath = 'c:\sqlsource\' ,
@SourceFile = 'sqlsource.txt' ,
@workdir = 'c:\temp\'

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-14 : 06:26:22

quote:
Originally posted by tejicen

Yes,

//tejicen

quote:
Originally posted by khtan

the following section is it mean to be there ?
exec [FTP].[PutFile]
@FTPServer = 'www.mobill.se' ,
@FTPUser = 'ftpmobill' ,
@FTPPWD = 'Ftp@dmin' ,
@FTPPath = '/ftp_sql/' ,
@FTPFileName = 'from_sql_2005.txt' ,
@SourcePath = 'c:\sqlsource\' ,
@SourceFile = 'sqlsource.txt' ,
@workdir = 'c:\temp\'

KH
[spoiler]Time is always against us[/spoiler]

what's the purpose ? you are executing the SP recursively

KH
[spoiler]Time is always against us[/spoiler]

tejicen
Starting Member

17 Posts

Posted - 2009-07-14 : 06:33:41

What I want is create a file with som data in it and send it to another location via ftp client.

I am not sure that you understand me.

//tejicen

quote:
Originally posted by khtan

quote:
Originally posted by tejicen

Yes,

//tejicen

quote:
Originally posted by khtan

the following section is it mean to be there ?
exec [FTP].[PutFile]
@FTPServer = 'www.mobill.se' ,
@FTPUser = 'ftpmobill' ,
@FTPPWD = 'Ftp@dmin' ,
@FTPPath = '/ftp_sql/' ,
@FTPFileName = 'from_sql_2005.txt' ,
@SourcePath = 'c:\sqlsource\' ,
@SourceFile = 'sqlsource.txt' ,
@workdir = 'c:\temp\'

KH
[spoiler]Time is always against us[/spoiler]

what's the purpose ? you are executing the SP recursively

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-14 : 06:36:14

remove that section and try

KH
[spoiler]Time is always against us[/spoiler]

tejicen
Starting Member

17 Posts

Posted - 2009-07-14 : 07:06:42

quote:
Originally posted by khtan

remove that section and try

KH
[spoiler]Time is always against us[/spoiler]

Hi again,

I saw that I tried to put the same value twice.

thanks for the help, tejicen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources