Search code examples
sqlsql-servercsvimportrds

Amazon RDS w/ SQL Server wont allow bulk insert from CSV source

I've tried two methods and both fall flat...

BULK INSERT TEMPUSERIMPORT1357081926 
FROM 'C:\uploads\19E0E1.csv' 
WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')

You do not have permission to use the bulk load statement.

but you cannot enable that SQL Role with Amazon RDS?

So I tried... using openrowset but it requires AdHoc Queries to be enabled which I don't have permission to do!

Solution

  • You can enable ad hoc distributed queries via heading to your Amazon Management Console, navigating to your RDS menu and then creating a DB Parameter group with ad hoc distributed queries set to 1, and then attaching this parameter group to your DB instance.

    Don't forget to reboot your DB once you have made these changes.

    Here is the source of my information:

    http://blogs.lessthandot.com/index.php/datamgmt/dbadmin/turning-on-optimize-for-ad/

    Hope this helps you.