February 20, 2008 at 7:54 am
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
February 20, 2008 at 12:42 pm
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
Change is inevitable... Change for the better is not.
February 20, 2008 at 12:44 pm
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?
February 20, 2008 at 1:09 pm
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
Change is inevitable... Change for the better is not.
February 21, 2008 at 12:49 am
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,
February 21, 2008 at 8:28 am
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?
February 21, 2008 at 8:59 am
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,
February 21, 2008 at 9:14 am
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?
February 21, 2008 at 11:50 am
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
Change is inevitable... Change for the better is not.
February 21, 2008 at 11:52 am
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
Change is inevitable... Change for the better is not.
February 22, 2008 at 3:33 am
Jeff,
Please let me know the remedy here.. Now that I got to know that I should give the permissions so that sql server should be able to see/recognize the path that I have given in this statement.
Please let me know clearly where and what I should do so that I can just get rid of this error and can execute this as I am novice to sql server 2005 security issues.
Thanks,
February 22, 2008 at 5:35 am
Ok... first, we have to determine precisely what the problem is... correct identification of the problem will be 90% of the solution....
1. Are you still using this exact syntax?
[font="Courier New"]bcp "Select LoanNo,PrepayVector1,DefaultVector1,RecoveryLag,LossSeverity from CurveTempDB.dbo.tLoanVectors where cusip='86359B6G1' " queryout C:\Sample.txt -T -c[/font]
If not, please post the exact syntax you are using (less any passwords, of course)
2. Please identify where you are trying to run the command from... are you running it from the server itself, or from SSMS on YOUR desktop?
3. Where is "C:\Sample.txt". What I mean by that, is that supposed to be on the server itself or on YOUR desktop?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2008 at 5:48 am
Jeff Moden (2/22/2008)
Ok... first, we have to determine precisely what the problem is... correct identification of the problem will be 90% of the solution....1. Are you still using this exact syntax?
[font="Courier New"]bcp "Select LoanNo,PrepayVector1,DefaultVector1,RecoveryLag,LossSeverity from CurveTempDB.dbo.tLoanVectors where cusip='86359B6G1' " queryout C:\Sample.txt -T -c[/font]
If not, please post the exact syntax you are using (less any passwords, of course)
2. Please identify where you are trying to run the command from... are you running it from the server itself, or from SSMS on YOUR desktop?
3. Where is "C:\Sample.txt". What I mean by that, is that supposed to be on the server itself or on YOUR desktop?
Fine Jeff,
1. I just have modified my select statement a bit like iam just taking all now
means...
bcp "Select * from CurveTempDB.dbo.tLoanVectors where cusip='86359B6G1' " queryout C:\Sample.txt -T -c
for which am still getting the error...
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
Note: Iam not passing any -S, -U and -P for now. am running this as it is now. Kindy let me know if this is of any cause for the error and should I have to mention them while at desktop too.
2. Right now i am just runing this on my machine but later I need to run this on a server.
3. Again, Sample.txt is right now on my desktop but yes it is supposed to be on the server.
Thanks,
February 22, 2008 at 6:04 am
Hey,
Have you checked permissions on file system to ensure you can create the file?
Try creating a sub-directory and then running utility (e.g. c:\test\sample.txt)
Thanks,
Phillip Cox
February 22, 2008 at 6:20 am
Phillip Cox (2/22/2008)
Hey,Have you checked permissions on file system to ensure you can create the file?
Try creating a sub-directory and then running utility (e.g. c:\test\sample.txt)
Thanks,
Phillip Cox
Philip,
FYI
I just tested it am able to create C:\sample.txt through cmd promp with bcp
where as am unable to create it in some folder there like (e.g. c:\test\sample.txt)
getting me the same error in cmd prompt
Unable to open BCP host data-file
If it is permission issue for file creation, how am I allowed to create a file in C:\Sample.txt?
but where as in QA all this fails and thros me thr error..
Unable to open BCP host data-file
Thanks,
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply