Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

BCP Command out/queryout Issue. Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2008 7:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 8, 2008 2:42 AM
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
Post #457995
Posted Wednesday, February 20, 2008 12:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #458176
Posted Wednesday, February 20, 2008 12:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:00 AM
Points: 7,105, Visits: 15,448
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?
Post #458178
Posted Wednesday, February 20, 2008 1:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #458190
Posted Thursday, February 21, 2008 12:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 8, 2008 2:42 AM
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,
Post #458381
Posted Thursday, February 21, 2008 8:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:00 AM
Points: 7,105, Visits: 15,448
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?
Post #458583
Posted Thursday, February 21, 2008 8:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 8, 2008 2:42 AM
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,
Post #458611
Posted Thursday, February 21, 2008 9:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:00 AM
Points: 7,105, Visits: 15,448
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?
Post #458622
Posted Thursday, February 21, 2008 11:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #458738
Posted Thursday, February 21, 2008 11:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #458740
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse