Need login help to send files to

Question

Need login help to send files to

jonathanm 4432

SSChasing Mays

Points: 619
More actions
July 20, 2015 at 9:48 am

#310068

All,
I have the following SP below however I need to set up login and password. Any idea on how I can go about getting that to work?
ALTER PROCEDURE [dbo].[ESC_CreateWebFiles_sp]
AS
DECLARE
@server VARCHAR(100),
@DBNAME VARCHAR(100),
@FILENAME VARCHAR(255),
@FILEPATHOUT VARCHAR(2000),
@CMD VARCHAR(3000),
@Query VARCHAR(2000)
SELECT @server = @@ServerName
SELECT @DBNAME = 'myplace'
SELECT @FILEPATHOUT = '\\myserver\web_data\Weblinc Sent\'
-- Get WEBPRICE
if exists (Select 1 from WebPrice)
begin
SELECT @FILENAME = 'Web Pricing Data.CSV'
SELECT @Query = 'Select * from WebPrice'
SELECT @CMD = 'sqlcmd -S TS-Epicor74 -d '+ @DBNAME+' -E -Q "'+@Query+'" -o"'+@FilePathOut + @FileName+ '" -W -s"," -h-1'
--SELECT @CMD = 'BCP "select * from test..WEBPRICE '+'" queryout "'+ @FilePathOut + @FileName+ '" -c -t"," -T -S'+@Server
--SELECT @CMD
EXEC MASTER..XP_CMDSHELL @CMD, no_output
END
-- Get WEBXREF
if exists (Select 1 from webxref)
begin
SELECT @FILENAME = 'WEB Cross Reference.CSV'
SELECT @Query = 'Select * from WebXref'
SELECT @CMD = 'sqlcmd -S localhost -d '+ @DBNAME+' -E -Q "'+@Query+'" -o"'+@FilePathOut + @FileName+ '" -W -s"," -h-1'
--SELECT @CMD = 'BCP "select * from test..WEBXREF '+'" queryout "'+ @FilePathOut + @FileName+ '" -c -t"," -T -S'+@Server
-- SELECT @CMD
EXEC MASTER..XP_CMDSHELL @CMD, no_output
END
--Get WEBACCT
if exists (Select 1 from WebAcct)
begin
SELECT @FILENAME = 'Accounts.CSV'
SELECT @Query = 'Select * from WebAcct'
SELECT @CMD = 'sqlcmd -S localhost -d '+ @DBNAME+' -E -Q "'+@Query+'" -o"'+@FilePathOut + @FileName+ '" -W -s"," -h-1'
--SELECT @CMD = 'BCP "select * from test..WEBACCT '+'" queryout "'+ @FilePathOut + @FileName+ '" -c -t"," -T -S'+@Server
-- SELECT @CMD
EXEC MASTER..XP_CMDSHELL @CMD, no_output
END
GO

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Are you asking how to connect to the remote server? Use Windows authentication, assuming SQL Server runs under a domain account in the same domain as the remote server (or a trusted domain). But why are you using sqlcmd to connect to the local instance? Why not just run the query direct in the stored procedure?

John

jonathanm 4432 SSChasing Mays Points: 619 More actions · Answer 2

I believe so. What I need is for the script to have the username and password in it so that when the files are transferred to that server, it wont prompt for log-in information.

jonathanm 4432 SSChasing Mays Points: 619 More actions · Answer 3

John,

Maybe I am doing something wrong with it. I need to copy the files that each section outputs to a server which has a log on and I am not sure how to save the logon user name and password in the script. Hopefully this helps a little more.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 4

jonathanm 4432 (7/20/2015)
John,
Maybe I am doing something wrong with it. I need to copy the files that each section outputs to a server which has a log on and I am not sure how to save the logon user name and password in the script. Hopefully this helps a little more.

Is that server in the same domain as the one that you're issuing the BCP commands from???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jonathanm 4432 SSChasing Mays Points: 619 More actions · Answer 5

No, has to go to an ip address and need to use a user name and password.

andrew gothard SSChampion Points: 12315 More actions · Answer 6

If it's in a seperate domain, it may be easier using SSIS to do it, or output the file locally and use xp_ cmdshell to run a batch command to do the login and transfer the file (although this would not be my prefered option).

Although, another option could be to set up a Windows Task to run a sqlcmd query to output the file, log in and transfer. That way you don't need to enable xp_ cmdshell.

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.