Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BCP Command out/queryout Issue.


BCP Command out/queryout Issue.

Author
Message
Shiv-645170
Shiv-645170
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 57
Can you please let me know the Answer

ALTER Procedure BCP_Text_File
(
@table varchar(100),
@Cusip varchar(100),
@direction varchar(15),
@FileName varchar(100)
)
as

If exists(Select * from information_Schema.tables where table_name=@table)
Begin
--Declare @str varchar(1000)
Declare @cmd varchar(1000)
set @cmd = 'bcp "Select LoanNo,PrepayVector1,DefaultVector1,RecoveryLag,LossSeverity from CurveTempDB.dbo.tLoanVectors where cusip=''' + @Cusip + ''' " '+@direction+' '+@FileName+' -T -c'
print @cmd
exec master..xp_cmdshell @cmd
--Exec(@str)
end
else
Select 'The table '+@table+' does not exist in the database'

Now here when i execute the bcp command in command prompt

bcp "Select LoanNo,PrepayVector1,DefaultVector1,RecoveryLag,LossSeverity from CurveTempDB.dbo.tLoanVectors where cusip='86359B6G1' " queryout C:\Sample.txt -T -c
and am able to get the output file in the given path...

where as when I take queryout as direction in query analyzer, i am getting the error for the above stored procedure as...

Copy direction must be either 'in', 'out' or 'format'.

but when i give the direction as out instead of queryout, even in command prompt am getting error like ...

An Error occurred while processing the command prompt

Also I am getting the same error in Query Analyzer too...


Please help me in this...

Thanks
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45047 Visits: 39898
I don't know what it is... everything in the command you printed looks fine. I even tried it with substitution of colums, etc, and it works just fine on my machine. Sorry.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7641 Visits: 18070
Besides this being a double-post.... Isn't the Server and DB setting missing? (don't you have to tell it which server to talk to?)

----------------------------------------------------------------------------------
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?
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45047 Visits: 39898
No... if you don't specify the database or the server, it assumes the "default" database for the user (which has been nicely overcome by the 3 part naming convention) and the current "instance" if a connection already exists like it would for Query Analyzer.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Shiv-645170
Shiv-645170
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 57
Hi

I am using sql server 2005 and the database is my is my own database. I think this is clear.
we can try this any other data base that you have.
but all my concern was about the command there. because, the " out " parameter there is giving the same error even in command prompt.

But if i try using queryout in the command prompt am able to get the out put file in the path that i have given. hope you understood.

Thanks,
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7641 Visits: 18070
Again - being tentative here. My understanding is that "out" is only to be used when the first argument is a view or table. Anything T-SQL there will result in an error (with direction set to 'OUT').

given the proc you have below - the only valid value for the @direction parameter is 'queryout'. Which kind of begs the question - why is it a parameter at all?

Or - did I miss the question entirely again?


And Jeff - there's something missing to the "existing connection" explanation. I ran that from within QA, and it fails without the -S parameter. Maybe because I'm on a named instance on a machine with several instances (I'm ONLY on named instances - no default instance)?

I use bcp so little, I'm very rusty. Well - I actually use it a LOT - my jobs run it bunches of times a day - but I haven't had to tweak them in so long - I forget~!

----------------------------------------------------------------------------------
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?
Shiv-645170
Shiv-645170
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 57
Ok,

I have given that direction as parameter bcos i want use it like a condition there. That's not problem I can just keep 'queryout' there directly instead of direction as parameter.

But today Iam getting a diferent error when i try queryout in QA

Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file

Please let me know the solution for this error. Is this any thing related to the permissions?

I am using SQL Server 2005 Management studio.

(Do I need to set any settings in the configurations like that ? bcos, I am just runing this in my local machine now . Or Do I need to Install SQL Server Express Edition?)

or is it some thing other than these settings ?

Your help could be of great use.

Thanks,
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7641 Visits: 18070
From googling that error - that seems to be a permissions error (outside of SQL Server). Meaning - more likely than not the SQL Server Service account OR the SQL Agent proxy account (which if I understand xp_cmdshell correctly, are the two possibilities for the external security context under which this is running) don't have permissions to the root directory.

Actually - if you're running this on a "real server", and not a workstation - you might care to try testing to just about anywhere other than the root of a drive (root directories tend to get locked down more stringently than subs).

Edit: 2005 is a little security-wise. Take this on:


xp_cmdshell Proxy Account
When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password. For example, the following command creates a proxy credential for Windows domain user SHIPPING\KobeR that has the Windows password sdfh%dkc93vcMt0.

Copy CodeEXEC sp_xp_cmdshell_proxy_account 'SHIPPING\KobeR','sdfh%dkc93vcMt0'
For more information, see sp_xp_cmdshell_proxy_account (Transact-SQL).



So - that proxy account or the SQL Server servie account, if I'm understanding that right.

----------------------------------------------------------------------------------
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?
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45047 Visits: 39898
Matt Miller (2/21/2008)
Again - being tentative here. My understanding is that "out" is only to be used when the first argument is a view or table. Anything T-SQL there will result in an error (with direction set to 'OUT').

given the proc you have below - the only valid value for the @direction parameter is 'queryout'. Which kind of begs the question - why is it a parameter at all?

Or - did I miss the question entirely again?


And Jeff - there's something missing to the "existing connection" explanation. I ran that from within QA, and it fails without the -S parameter. Maybe because I'm on a named instance on a machine with several instances (I'm ONLY on named instances - no default instance)?

I use bcp so little, I'm very rusty. Well - I actually use it a LOT - my jobs run it bunches of times a day - but I haven't had to tweak them in so long - I forget~!


I agree... always better to include the -S parameter, mostly for the reason you've stated.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45047 Visits: 39898
Shiv (2/21/2008)
Ok,

I have given that direction as parameter bcos i want use it like a condition there. That's not problem I can just keep 'queryout' there directly instead of direction as parameter.

But today Iam getting a diferent error when i try queryout in QA

Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file

Please let me know the solution for this error. Is this any thing related to the permissions?

I am using SQL Server 2005 Management studio.

(Do I need to set any settings in the configurations like that ? bcos, I am just runing this in my local machine now . Or Do I need to Install SQL Server Express Edition?)

or is it some thing other than these settings ?

Your help could be of great use.

Thanks,


The SQL Service login must be able to "see" the path for this to work. In other words, the login that the SQL Server logs in as must have the privs to see the directory.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search