Need help with a BCP statement

Question

Post reply

Need help with a BCP statement

itmasterw 60042

SSCrazy

Points: 2858

May 27, 2020 at 2:50 pm

#3757741

Hi,

The code below produces a file and the view used here has rows.

The problem is that is generated is blank.

Does anyone see what I am doing wrong that may produce a blank file?

Thank you

DECLARE @RecallDateTime as datetime
SET @RecallDateTime = GetDate()
DECLARE @FileExtension  varchar(4) = 'ckr'
DECLARE @FileDateTimeStamp varchar(14) = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(23), @RecallDateTime, 120),'-',''),' ',''),':','')

-- working variables
DECLARE @CmdShellText varchar(8000)
DECLARE @current_agy_id varchar(20) = 'CDG'
DECLARE @ReturnedRows as int
DECLARE @InhouseFileRoot varchar(100) 
       
SET @InhouseFileRoot =   '\\domainJH\gamf\Recalls & Recons'
 
SET @CmdShellText = 'bcp "[dbo].[TestView] + @FileExtension'
+ '" queryout "' 
+ @InHouseFileRoot 
+ '\' + @current_agy_id 
+ '_' + @FileDateTimeStamp 
+ '.' + @FileExtension 
+ '" -T -c -t"|"'

print 'CommandShellText = ' + @CmdShellText
                           
EXEC xp_cmdshell @CmdShellText

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

What does the print statement yield?

--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)

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 2

HI,

first thanks for your help.

I may not understand how to use that but this is what it give me. I mean the @FileExtension should be just ckr, but what do I about it; and is that the only thing?

CommandShellText = bcp "[dbo].[TestView] + @FileExtension" queryout "\\domainJH\gamf\Recalls & Recons\CDG_20200527113227.ckr" -T -c -t"|"

Thank you

David Burrows SSC Guru Points: 65138 More actions · Answer 3

I can't see how it can run at all, what is the purpose of @FileExtension in the first line, if it is to extend the name of the view then you need to do this

'bcp "[dbo].[TestView' + @FileExtension + ']'

or if @FileExtension is the view name then

'bcp "[dbo].[' + @FileExtension + ']'

Far away is close at hand in the images of elsewhere.
Anon.

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 4

HI,

I took it out all together and it was the same result.

This reply was modified 3 years, 11 months ago by itmasterw 60042.

David Burrows SSC Guru Points: 65138 More actions · Answer 5

queryout is for queries (sql statements)

out is for tables/views

what was output in the results tab?

fyi

xp_cmdshell returns an errorcode as an integer if you want check if it worked or not

Far away is close at hand in the images of elsewhere.
Anon.

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 6

Hi,

So I changed the location and made this as bare bones as I can and it still will not go.

The result show this:

output

'"' is not recognized as an internal or external command,

operable program or batch file.

NULL

And the print message shows this:

CommandShellText = bcp "Select * from [dbo].[TestView]"queryout "\\dmpdomain2\namg\Recalls & Recons"\CDG_20200528101142.ckr" -T -c -t"|"

DECLARE @RecallDateTime as datetime
SET @RecallDateTime = GetDate()
DECLARE @FileExtension  varchar(4) = 'ckr'
DECLARE @FileDateTimeStamp varchar(14) = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(23), @RecallDateTime, 120),'-',''),' ',''),':','')

-- working variables
DECLARE @CmdShellText varchar(8000)
DECLARE @current_agy_id varchar(20) = 'CDG'
DECLARE @ReturnedRows as int
DECLARE @InhouseFileRoot varchar(100) 
       
SET @InhouseFileRoot =  '\\dmpdomain2\namg\Recalls & Recons'
 
SET @CmdShellText = 'bcp "Select * from [dbo].[TestView]"' 
+  'queryout  "\\dmpdomain2\namg\Recalls & Recons"'
+ '\' + @current_agy_id 
+ '_' + @FileDateTimeStamp 
+ '.' + @FileExtension 
+ '" -T -c -t"|"'

print 'CommandShellText = ' + @CmdShellText
                           
EXEC xp_cmdshell @CmdShellText

David Burrows SSC Guru Points: 65138 More actions · Answer 7

You have an extra double quote, try this

DECLARE @RecallDateTime as datetime
SET @RecallDateTime = GetDate()
DECLARE @FileExtension  varchar(4) = 'ckr'
DECLARE @FileDateTimeStamp varchar(14) = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(23), @RecallDateTime, 120),'-',''),' ',''),':','')

-- working variables
DECLARE @CmdShellText varchar(8000)
DECLARE @current_agy_id varchar(20) = 'CDG'
DECLARE @ReturnedRows as int
DECLARE @InhouseFileRoot varchar(100) 
       
SET @InhouseFileRoot =  '\\dmpdomain2\namg\Recalls & Recons'
 
SET @CmdShellText = 'bcp "Select * from [dbo].[TestView]"' 
+ ' queryout "\\dmpdomain2\namg\Recalls & Recons'
+ '\' + @current_agy_id 
+ '_' + @FileDateTimeStamp 
+ '.' + @FileExtension 
+ '" -T -c -t"|"'

print 'CommandShellText = ' + @CmdShellText

Far away is close at hand in the images of elsewhere.
Anon.

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 8

Tanks for your help, but while that did create the file but it is blank.

David Burrows SSC Guru Points: 65138 More actions · Answer 9

What was output in the results tab?

What does Select * from [dbo].[TestView] return?

Far away is close at hand in the images of elsewhere.
Anon.

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 10

Yes, I checked that a few times, and I tried other tables as well. They all return data but just not in the file.

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 11

I would take a step back - and try executing the code in a command/powershell window to test and validate it first. Then, once you have it working you should be able to figure out what needs to be done to get it to work from a dynamically built command.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs