sp help!!!!

• SQLRep

  SSCrazy

  Points: 2052

  More actions

  November 4, 2005 at 8:35 am

  #112277

  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

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  November 4, 2005 at 9:03 am

  #602366

  Do all the servers have access to that drive?

  Do all the server have permission to write to that directory AND overwrite the file if needed?

• Sopheap Suy

  SSCertifiable

  Points: 7893

  More actions

  November 4, 2005 at 9:17 am

  #602375

  set @cmd = ' \\'+ @@servername + '\G$\Export'

  This is the correct file path.

  This is only possible if you want everything to go to the local server with the same drives and permission.

   

  mom