sp help! - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

sp help!

Author

Topic

PatDeV
Posting Yak Master

197 Posts

Posted - 2005-11-04 : 10:05:15

I have this provedure that it long so i have pasted the first and last part of the procedure.
Alter PROCEDURE outputexport
@Key varchar(20) = null
,@export int = 1
AS
BEGIN

CREATE TABLE test
/*Create the table for data*/
.
.
.
SELECT * FROM test

if @export = 1
begin
exec filetransferto 1, '"G:/export"', 'output', 'txt', 1, 0, @TableName='test'
exec filetransferto 1, '"G:/export"', 'output', 'txt', 1, 0, @TableName='test'

end
else select * from test

drop table #QRMDump

END
GO

I am trying to put this in such as that i can run from different server but the file will go to the same location that shown in('"G:/export"')
I am trying to declare @cmd that will do this
declare @cmd varchar(128)
set @cmd = (select @@ServerName) + '"G:/export"'
and calling the
exec filetransferto 1, @cmd, 'output', 'txt', 1, 0, @TableName='test'

but it give invalid file path error.

hope someone can give me answer!!

thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-04 : 11:23:44

Does the SQL Server have the drive mapped as G, or is the G drive mapped from your client?

It has to be the mapping as known to the server.

And as an aside, you're better off dumping the file locally to the server the copying the file to the location you want.

I would also use UNC filepath names as well...if the file is huge, I'd use robocopy

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

PatDeV
Posting Yak Master

197 Posts

Posted - 2005-11-04 : 11:51:38

yeah the server has the mapped drive but the instead of using the name of the server for example, //testserver/g$/test. I want to user declared variable such as
@cmd that will take the path name so i can run it at on any server because we will be moving soon so want to do that for the purpose of moving.

declare @cmd varchar(128)
set @cmd = (select @@servername)+'/g$/test'
is it right? where @@servername will take where it is running from?

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-04 : 12:06:31

I think you want


declare @cmd varchar(128)
SELECT @cmd = '\\'+@@servername+'\g$\test'
SELECT @cmd

And it will take the name of the server AND instance that you are running on

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

PatDeV
Posting Yak Master

197 Posts

Posted - 2005-11-04 : 12:38:24

ok i will try that.
so if do this
declare @cmd varchar(128)
SELECT @cmd = '\\'+@@servername+'\g$\test'
SELECT @cmd

then is this right?
exec filetransferto 1, @cmd, 'output', 'txt', 1, 0, @TableName='test'

thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-04 : 13:16:36

I don't know what that is....gotta be a home grown sproc....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources