?? on xp_cmdshell not writing to file
-
Hi,
Below will not write to file. I did a similar test on a more basic query and it worked.
I'm assuming it's the " somewhere, but can't find it..
any ideas would be appreciated...
Thanks
Joe
declare @sql varchar(8000)
set @sql='bcp
“select
CAD19 as ContractNumber,
CAD65 as ClientID,
client.LName + ”, “ + CLIENT.FName as Name,
convert(varchar, client.BDate, 101)as DOB,
CAD300 as HSTSentDate,
CAD302 as MemberStartDate,
CAD18 as IntakeNotCompletedID,
CAD301 as IntakeNotEnrDate,
CAD754 as IntakeOtherReason
FroM test.dbo.USER_DEFINED_DATA INNER JOIN
test.dbo.ASSESSMENT ON test.dbo.USER_DEFINED_DATA.ASSESSMENT_MONIKER = test.dbo.ASSESSMENT.OID INNER JOIN
test.dbo.Client ON test.dbo.USER_DEFINED_DATA.ATTACHED_TO_OID = test.dbo.Client.OID
WHERE (test.dbo.ASSESSMENT.Code = “CH1”) AND (test.dbo.USER_DEFINED_DATA.CAD750 = “Final”) AND (test.dbo.USER_DEFINED_DATA.Expdate IS NULL)
and CAD700 = “Processed”
FOR XML PATH(“REFERRAL”), root(“CLUB”),type" queryout "c:\temp\' + REPLACE(CONVERT(VARCHAR,GETDATE(),101),'/','')+'Clubhouse.xml' + '" -c -T -SUCDB01 -Usa -Ppas'
exec master..xp_cmdshell @sql
-
probably permissions; when you go outside of a database, like to the command line, a user you do not expect(the startup account of the service) is used
does this code return the contents of the c:\temp directory, or do you get a n error for permissions?
DECLARE @Results table(
ID int identity(1,1) NOT NULL,
TheOutput varchar(1000))
insert into @Results (TheOutput)
exec master..xp_cmdshell 'whoami' --nt authority\system for example
insert into @Results (TheOutput)
exec master..xp_cmdshell 'cd %userprofile%' --NULL because nt authority\system is not a user...command fails.
insert into @Results (TheOutput)
exec master..xp_cmdshell 'dir c:\temp\*.*'
select * from @Results
if i right click on my temp folder, i can see for example, nt authority\system does NOT have permissions to that specific folder, and the same thing will happen if i try to use my documents or my Desktop; you might want to simply use a folder the start up account DOES have access to:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks for the info,
I can run this in the immediate window and it writes the file
declare @sql varchar(255)
set @sql='bcp "SELECT lname as last, fname as first FROM db.dbo.client where LName = ''wilson'' FOR XML PATH(''REFERRAL''), root(''CLUB''),type" queryout "c:\temp\' + REPLACE(CONVERT(VARCHAR,GETDATE(),101),'/','')+'Clubhouse.xml' + '" -c -T -SUCDB01 -Usa -Ppas'
exec master..xp_cmdshell @sql
Results...
output
1 NULL
2 Starting copy...
3 NULL
4 1 rows copied.
5 Network packet size (bytes): 4096
6 Clock Time (ms.) Total : 1 Average : (1000.00 rows per sec.)
7 NULL
When I run this ...
declare @sql varchar(3000)
set @sql='bcp
"select
CAD19 as ContractNumber,
CAD65 as ClientID,
client.LName + '','' + CLIENT.FName as Name,
convert(varchar, client.BDate, 101)as DOB,
CAD300 as HSTSentDate,
CAD302 as MemberStartDate,
CAD18 as IntakeNotCompletedID,
CAD301 as IntakeNotEnrDate,
CAD754 as IntakeOtherReason
FroM test.dbo.USER_DEFINED_DATA INNER JOIN
test.dbo.ASSESSMENT ON test.dbo.USER_DEFINED_DATA.ASSESSMENT_MONIKER = test.dbo.ASSESSMENT.OID INNER JOIN
test.dbo.Client ON test.dbo.USER_DEFINED_DATA.ATTACHED_TO_OID = test.dbo.Client.OID
WHERE (test.dbo.ASSESSMENT.Code = “CH1”) AND (test.dbo.USER_DEFINED_DATA.CAD750 = “Final”) AND (test.dbo.USER_DEFINED_DATA.Expdate IS NULL)
and CAD700 = ''Processed''
FOR XML PATH(''REFERRAL''), root(''CLUB''),type" queryout "c:\temp\' + REPLACE(CONVERT(VARCHAR,GETDATE(),101),'/','')+'Clubhouse.xml' + '" -c -T -SUCDB01 -Usa -Ppas'
exec master..xp_cmdshell @sql
I get this in the results with no file (Sorry for my ignorance...)
output
1 usage: bcp {dbtable | query} {in | out | queryout | format} datafile
2 [-m maxerrors] [-f formatfile] [-e errfile]
3 [-F firstrow] [-L lastrow] [-b batchsize]
4 [-n native type] [-c character type] [-w wide character type]
5 [-N keep non-text native] [-V file format version] [-q quoted identifier]
6 [-C code page specifier] [-t field terminator] [-r row terminator]
7 [-i inputfile] [-o outfile] [-a packetsize]
8 [-S server name] [-U username] [-P password]
9 [-T trusted connection] [-v version] [-R regional enable]
10 [-k keep null values] [-E keep identity values]
11 [-h "load hints"] [-x generate xml format file]
12 [-d database name]
13 NULL
-
I see the issue now.
bcp cannot take any input with carriage return line feeds.
your statement is easy to read, because it has the CrLf in it, but i'ts not valid for a command line argument.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks Lowell...
It now works fine....
I learned something new today and its only 10 AM!!!
Thanks Again
Joe
-
I like the idea of not having to concatenate lines or use long unreadable lines so I do thigs like the following...
DECLARE @Cmd VARCHAR(8000);SELECT @Cmd = '
echo This is
line one
&
echo This is
line two
'
, @Cmd = REPLACE(@Cmd,CHAR(10),' ')
;
EXEC xp_CmdShell @Cmd
;
--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) -
Jeff Moden (6/15/2013)
I like the idea of not having to concatenate lines or use long unreadable lines so I do thigs like the following...DECLARE @Cmd VARCHAR(8000);SELECT @Cmd = '
echo This is
line one
&
echo This is
line two
'
, @Cmd = REPLACE(@Cmd,CHAR(10),' ')
;
EXEC xp_CmdShell @Cmd
;
Oh, be careful now Jeff. Windows end of line characters are 0x0D 0x0A meaning you're technique leaves behind a control character.
DECLARE @Cmd VARCHAR(8000);SELECT @Cmd = '
echo This is
line one
&
echo This is
line two
',
@Cmd = REPLACE(@Cmd, CHAR(10), ' ');
SELECT CHARINDEX(CHAR(13), @cmd);
EXEC xp_CmdShell
@Cmd;
You're sample does not have trouble with that shortcoming, but some code may.
Consider this instead:
DECLARE @Cmd VARCHAR(8000);SELECT @Cmd = '
echo This is
line one
&
echo This is
line two
',
@Cmd = REPLACE(@Cmd, CHAR(13) + CHAR(10), ' ');
SELECT CHARINDEX(CHAR(13), @cmd);
EXEC xp_CmdShell
@Cmd;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply