x

File operation used in stored procedure

How can we use file operation in this stored procedure?

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON

go

-- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- =============================================

ALTER PROCEDURE [dbo].[SALES_DATA] AS BEGIN

SET NOCOUNT ON;

TRUNCATE TABLE salesdata

BULK INSERT salesdata FROM 'c:\salesdata.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )

END
more ▼

asked Oct 22, 2009 at 04:49 AM in Default

neha gravatar image

neha
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You could use the xp_cmdshell to execute the bcp comamnd.

MSDN XP_CMDSHELL

http://msdn.microsoft.com/en-us/library/aa260689%28SQL.80%29.aspx

more ▼

answered Oct 22, 2009 at 05:15 AM

sp_lock gravatar image

sp_lock
9.3k 25 28 31

(comments are locked)
10|1200 characters needed characters left

I dont see any problems using bulk insert. Just remember the security context, if you run by SSMS the login must be able to access the path.

If by JOB, Simply:

Owner of the Job is Sysadmin ? Security context is the service account SQL Server Agent. This means that this account must have permission on the folder that is the csv file

Owner of Job is not Sysadmin, but it is a Windows login? Security context is the account of this owner This means that this account must have permission on the folder that is the csv file

Owner of Job is not Sysadmin and is not a Windows account? There is a need to create a credential (proxy) and apply the permissions in this proxy account. This means that this account must have permission on the folder that is the csv file

But what is the problem ?

more ▼

answered Oct 22, 2009 at 06:57 AM

Laerte Junior gravatar image

Laerte Junior
488 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x415

asked: Oct 22, 2009 at 04:49 AM

Seen: 1936 times

Last Updated: Oct 22, 2009 at 06:43 AM