Calling BCP From A Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calling BCP From A Stored Procedure

Author

Topic

triplehhh_10012
Starting Member

1 Post

Posted - 2006-03-28 : 15:18:57

Hi all, I'm new to SQL Server and I'm trying to call BCP from a stored procedure with a parameter passed in as the path to which to export the datafile. This parameter is also the name of a network PC. However, I keep getting this error:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
NULL

This is the stored procedure:
PROCEDURE DownloadLinkEvents
@localPath varchar(80)
AS
declare @bcpCommand varchar(200)
begin
set @bcpCommand = 'bcp <dbName> out ' + @localPath + '-c -t"|" -S<dbServer> -Usa -P<passowrd>'
exec master..xp_cmdshell @bcpCommand
end

Thanx.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-28 : 15:21:45

Run this to see if the database server can get to the file:

DECLARE @command varchar(5000), @localPath varchar(8)
SET @localPath = 'C:\Temp\' <-- change this of course
SET @command = 'dir ' + @localPath
EXEC master.dbo.xp_cmdshell @command

Tara Kizer
aka tduggan

xuw0
Starting Member

2 Posts

Posted - 2006-04-26 : 13:07:55

Using shared folder does work when the shared folder grant everyone full control.

The question is how I can know the UserID in which the bcp logon to the target machine when OS run bcp using master..xp_cmdshell?

The shared folder can specify this user to has full control or write permmision if you know this user id.

Thanks in advance!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-26 : 13:45:10

From SQL Server Books Online in the xp_cmdshell topic:

quote:

When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources