Options to run BCP in SQL Server

Question

Options to run BCP in SQL Server

sqlenthu 89358

Hall of Fame

Points: 3069
More actions
October 9, 2017 at 11:52 am

#329144

Hi All, the question might be stupid but as a quick fix want to know if there is a way out. I want to do BCP load of a file to DB server. I have created format file and it's working fine when loading the file from my machine but I have to try the load from DB server as both DB server and file location are in separate region. Now I can't rdp to that DB server and also don't have permissions to xp_cmdshell command. Is there a way to run BCP from the SQL server ?

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

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

the other easy option that comes to mind then is a SQL Server Agent job that has a step of type:
Operating System (CmdExec)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

sqlenthu 89358 - Monday, October 9, 2017 11:52 AM
Hi All, the question might be stupid but as a quick fix want to know if there is a way out. I want to do BCP load of a file to DB server. I have created format file and it's working fine when loading the file from my machine but I have to try the load from DB server as both DB server and file location are in separate region. Now I can't rdp to that DB server and also don't have permissions to xp_cmdshell command. Is there a way to run BCP from the SQL server ?

You don't need BCP to load the file. BULK INSERT actually runs a bit faster than BCP and can use the same format file. That also means you don't need xp_CmdShell if they won't let you use it.

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

sqlenthu 89358 Hall of Fame Points: 3069 More actions · Answer 3

Seems the account doesn't have bulkadmin access. I am checking if I can get that. In between I have one architecture level question based on bulk uload of files. Creating new thread under SQL 2012 tsql.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

If BULK INSERT is not an option, you could try using SSIS or some othere ETL tool that your company is already using. I would still try to push to get the bulkadmin permission.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sqlenthu 89358 Hall of Fame Points: 3069 More actions · Answer 5

Guys, I am little confused here w.r.t. permissions. When I tried BCP load from my local disk using that login and password, data load happened. But when I am using bulk insert from ssms, then it's giving error "Msg 4834, Level 16, State 4, Line 8
You do not have permission to use bull load statement".

That particular user has db_owner rights at DB level in DB but associated login has dbcreator role at server level.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 6

sqlenthu 89358 - Tuesday, October 10, 2017 11:17 AM
Guys, I am little confused here w.r.t. permissions. When I tried BCP load from my local disk using that login and password, data load happened. But when I am using bulk insert from ssms, then it's giving error "Msg 4834, Level 16, State 4, Line 8
You do not have permission to use bull load statement".
That particular user has db_owner rights at DB level in DB but associated login has dbcreator role at server level.

BULK INSERT uses the privs of the user rather than privs of the SQL Server login like xp_CmdShell does. The user has to have privs to see the file or the stored procedure needs to use EXECUTE AS OWNER if the owner of the database has the privs.

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

sqlenthu 89358 Hall of Fame Points: 3069 More actions · Answer 7

Jeff Moden - Tuesday, October 10, 2017 11:22 AM
sqlenthu 89358 - Tuesday, October 10, 2017 11:17 AM
Guys, I am little confused here w.r.t. permissions. When I tried BCP load from my local disk using that login and password, data load happened. But when I am using bulk insert from ssms, then it's giving error "Msg 4834, Level 16, State 4, Line 8
You do not have permission to use bull load statement".
That particular user has db_owner rights at DB level in DB but associated login has dbcreator role at server level.
BULK INSERT uses the privs of the user rather than privs of the SQL Server login like xp_CmdShell does. The user has to have privs to see the file or the stored procedure needs to use EXECUTE AS OWNER if the owner of the database has the privs.

Jeff, what I got from that error message is that the user has no permissions to run bulk command. Then how did it succeed doing the same when I ran BCP from my local machine in cmd giving that specific user and it's credentials ? I know there are few dots but I am unable to connect them.

Sue_H SSC Guru Points: 90860 More actions · Answer 8

sqlenthu 89358 - Tuesday, October 10, 2017 11:34 AM
Jeff Moden - Tuesday, October 10, 2017 11:22 AM
sqlenthu 89358 - Tuesday, October 10, 2017 11:17 AM
Guys, I am little confused here w.r.t. permissions. When I tried BCP load from my local disk using that login and password, data load happened. But when I am using bulk insert from ssms, then it's giving error "Msg 4834, Level 16, State 4, Line 8
You do not have permission to use bull load statement".
That particular user has db_owner rights at DB level in DB but associated login has dbcreator role at server level.
BULK INSERT uses the privs of the user rather than privs of the SQL Server login like xp_CmdShell does. The user has to have privs to see the file or the stored procedure needs to use EXECUTE AS OWNER if the owner of the database has the privs.
Jeff, what I got from that error message is that the user has no permissions to run bulk command. Then how did it succeed doing the same when I ran BCP from my local machine in cmd giving that specific user and it's credentials ? I know there are few dots but I am unable to connect them.

Bulk insert statement also requires membership in the bulkadmin server role. BCP does not require this.

Sue

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 9

Permissions have changed. There is a Administer Bulk Operations permission that is available in SQL 2012.
https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql

Sue_H SSC Guru Points: 90860 More actions · Answer 10

Steve Jones - SSC Editor - Tuesday, October 10, 2017 2:38 PM
Permissions have changed. There is a Administer Bulk Operations permission that is available in SQL 2012.
https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql

I am terribly sorry and I apologize for forgetting this. I believe it's still needed for the bulk insert statement and not for BCP.

Sue