Bulk insert woes... 1

[Neil Toulouse]

Hi folks!

I have touched on this problem over time but have not really found a solution to it so am hoping someone could point me in the right direction!

SCENARIO:
VFP front end application linking to SQL Server database. The 'user' in SQL for the application is set to db_owner, and no server roles, which is fine for 99% of the workings of the App.

However, in one instance it needs to import data from an external source. From testing the quickest way of achieving this is via Bulk Import, but to do that requires the 'user' to have server roles of bulkadmin and sysadmin. Our clients are not overly happy about setting the roles permanently for this 'user', so they are having to get the DBAs involved to set the roles so they can run the process, and then set them back afterwards. This process is causing them grief!

What I would like to do is somehow (progammatically) give the 'user' the roles required to complete the process, ie add the roles before the process, and remove them afterwards. The only level of access the program will have is as the 'user'.

Can anyone advise anything I can do to achieve this?

TIA
Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[gmmastros]

I think you are mistaken about one point. "server roles of bulkadmin and sysadmin"

You used AND but should have used OR instead. They need one or the other, but they don't need both.

In order to grant a user the sysadmin role, you need to have the sysadmin role. Stated another way, only sysadmins can grant another user sysadmin permissions. Unless I am mistaken, a user needs to be sysadmin OR securityadmin in order to grant another user bulkadmin.

Your idea of setting the user to bulkadmin or sysadmin programatically will probably not work.

What might work...

If you are doing the bulk loading in a stored procedure, you could use the "EXECUTE AS" clause so that the user with lower permissions can execute the stored procedure but the stored procedure would execute as though it were run with a user that has higher permissions.

You should probably do a little research on "EXECUTE AS" and then discuss with your clients to determine if this is an acceptable process.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Neil Toulouse]

Hi George

Many thanks for your response!

I think I must have another issue going but have always assumed (obviously incorretly) it was the above, so many thanks for pushing me in another direction.

So now, after very carefull scrutiny, it appears that the error is coming in from a check to ensure that folder in which the files are stored (for insertion) exists!

This is done by using the proc:

exec master..xp_fileexist <foldername>

...and checking the File_Is_A_Directory field. If it's 1 it exists and a folder, otherwise it's a 0. If SYSADMIN is set, the field returns 1. Without SYSADMIN it returns 0, and therefore the app says "can't do the process" (which I have always incorrectly assumed was the BULKADMIN rights).

So many thanks for making me look harder at this complex routine

I will see what I can sort out with it now, but I may be back ;-)


I like work. It fascinates me. I can sit and look at it for hours...

 

[gmmastros]

Just curious... what version of SQL Server are you using?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Neil Toulouse]

I am specifically testing this on 2008, but it's the same issue on 2005.

But researching the proc exec master..xp_fileexist <foldername> clearly shows now that this is the problem!



I like work. It fascinates me. I can sit and look at it for hours...

 

[gmmastros]

This data that you are importing.... where is it coming from? Is it in another SQL Server database, a different type of database, or some sort of file? If file, what kind of file?

Also... using the Bulk Insert method, how long does it take currently.

I'm thinking that there may be other acceptable ways to import the data that does not require sysadmin.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Neil Toulouse]

It is load of CSV files from a government agency. I originally parsed out all the records of these files and created the INSERT statements and sent them to the server. This worked well but took some time (upto 30 mins in some instances), and is a process that needs to be run quite reguarly (daily at certain times of the year).

So I looked into Bulk Admin, and with that the process took no more than a couple of minutes so was an ideal solution.

However we were then wrongly advising our clients they needed to set the application's SQL account to be SYSADMIN for this process to work! They (quite rightly) were not impressed about this, but accepted it.

But I have fixed it now by removing the specific file checking which requires the SYSADMIN, and just checking at the end if the process has completed OK.

So now they just need BULKADMIN which they are happy about

I like work. It fascinates me. I can sit and look at it for hours...

 

[gmmastros]

I see.

If the CSV file always has the same structure, and always has the same file name and is always in the same folder....

You could create a linked server in the SQL Database that points to the CSV file. With this method, you wouldn't even need BULK ADMIN permissions. You could then import your data like this:

Insert Into YourPermanentTable(Col1, Col2)
Select Col1, Col2
From   LinkedServerName.dbName.dbo.TableName

I've never set up a linked server to a CSV file before, so you'll want to play with this a bit.

You could also use OpenQuery or OpenRowset,something like this:

Insert Into YourPermanentTable(Col1, Col2)
Select Col1, Col2
FROM   OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\YourFolderName;', 'SELECT * from YourFileName.csv');

The openrowset method requires "Ad Hoc Distributed Queries" to be enabled.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Neil Toulouse]

Thanks George looks interesting!

I look into it!

I like work. It fascinates me. I can sit and look at it for hours...