bcp returns only part of recordset
-
I face very strange problem.
I run this from xp_cmdshell:
bcp "EXEC [My_database].dbo.MyProcedure @param1 = 'param1', @Param2 = 222, @OtherParams = NULL" queryout "\\ServerName\Folder\FileName.csv" -c -q -t"," -k -S"Server" -T
Problem is it records in output file just 2 columns from the recordset returned by SP.
When I run the query itself from QA:
EXEC [My_database].dbo.MyProcedure @param1 = 'param1', @Param2 = 2, @OtherParams = NULL
it returns full recordset.
Does anybody have a clue what could be possibly wrong here?
I use to use such commands for file reports all the time, and I never had problems with it.
_____________
Code for TallyGenerator -
Since we don't know what your code does, this is just in case it's simply a typo causing the problem. You are passing two different values for @Param2 between the two examples. Unless your proc has dynamic SQL, I doubt that this is the cause, but thought I'd bring it up just in case. Additionally, what can you tell us about the third column (i.e. type, size, includes commas in data, column level permissions, etc.)? Also, have you tried running it without the -k parameter, and when you run it locally, have you tried running it in a database that is different from where the stored proc/data resides? I'd also try it without the -q option, just in case.
You're right that it seems strange.
-
Most of the times, wierd stuff like this is due to the data in some way...
Like things that makes a difference between writing a TDS stream to a result window compared to writing a stream to disk to create a file. (long shot, I'll admit)
Do you have any funky chars in the data that would mess upp for a file?
Is there any concatenations going on in the proc?
(even longer wag, but I was thinking concat_null_yields_null settings..)
Another guess... since this is in essence a 'result', could it be any of the SET settings that does affect result be different?
http://msdn2.microsoft.com/en-us/library/Aa933126(SQL.80).aspx
..but it's strange.. Don't think I've ever have this happen to me.
/Kenneth
-
No, it's not a typo.
Strings in the post are not actual ones, of course.
In actual thing I just used Copy-Paste, so strings were identical.
3rd cilumn does not matter.
I changed the order of columns in recordset returned by SP, it used to be long strings (about 150 characters in average), dates, char(5), resunt was still the same - only 2 columns sent to file.
Yes, I ran it without -k - no effect. Actually, there are no nulls in resultset, so it does not really matter.
-q was not there originally. I added it when was trying to identify the cause of the issue.
Procedure does have dynamic SQL. It actually returnes one of reports depending on parameter supplied to SP.
Is there a problem?
_____________
Code for TallyGenerator -
Does not look like data problem.
I selected different ranges of data for the report - same result.
But about SET options - have to give it a shot.
There is a possibility that connection from QA and connection from command shell have different settings...
Not a big chance, but who knows?
Cannot check it now. Will try tomorrow.
Thanks.
_____________
Code for TallyGenerator -
I'd try running it locally with SET FMTONLY ON and as noted earlier, from a different database on the server, especially since it's dynamic SQL. The next step, since you've ruled out switches, data, and by that point, SET options, would probably be to see the code.
-
in addtion I'll add SET NOCOUNT ON as the first line in the procedure. When Temporary tables are involved all bets ar off
Not sure if you are using temp tables but that is another thing to check.
* Noel -
SET NOCOUNT ON is run-time option, and it's set inside of that SP.
Yes, there # tables inside. Moreover, they are inside of dynamic SQL executed by SP at the end.
There were some problems related to temp tables, but I cannot make any sense from returning 2 columns. Why 2? Why not just 1st column?
I would expect rows cut off, but all rows are returned.
It's just unfair.
_____________
Code for TallyGenerator -
I believe you need an additional batch command...
mode con[:] [cols=c] [lines=n]
--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) -
Nah... that wasn't it... set mine to 40 and still worked on Authors table...
Sorry for the bum info
--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) -
Without specifying a row terminator - it's assumed to be carriage return. Are you returning text fields that might have a carriage return in them? The first one it runs into (even in the middle of a field) would be assumed to be the end of the line.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
I'm thinking that probably won't matter on BCP that uses "QueryOut"
--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) -
It's not the case.
I managed to get right file end in the end, but only when I eliminated dynamic SQL and # table inside of invoked procedure.
Now trying to find the work-around to generate the file without creating separate SP for each instance of several customised reports.
_____________
Code for TallyGenerator
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply