Bulk Insert on a Virtual SQL Server

Bulk Insert on a Virtual SQL Server

Post by Hans Nydah » Thu, 16 Dec 1999 04:00:00

I have installed MS SQL Server 7.0 Enterprise Edition on a raided (raid
5) system with 2 Compaq servers with fail-over support. Both servers run
NT Server 4.0 Enterprise edition. A cluster have been set up through
Cluster administrator and SQL Server is installed as a Virtual Server
with its own IP address (Active/Passive configuration).
Everything is working fine except a transact sql function that worked
fine on an ordinary SQL Server 7.0 before upgrading.

I am accessing a physical path using bulk insert

BULK INSERT ITstores.dbo.Imp_Articles
FROM 'F:\Distributers\C2000\articles.txt'
       ROWS_PER_BATCH = 1,
       ROWTERMINATOR   = '\n' )

or extended stored procedure xp_cmdshell

-c -q'

...but it does not work any more. The error message looks like this:

Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'STREAM'.

I guess it means that I can't use an explicit path any more. I can
access the harddrives with Windows Explorer using the Virtual servers
IP-address or name, but not through transact sql any more.

What to do??

For now I replicate the data that must be read into the database or
written to a file to a third server on which these functions still work.

Does anyone knows if this is a bug or...
has anyone succeded to use to use the mentioned functions on an virtual

Hans Nydahl


Bulk Insert on a Virtual SQL Server

Post by Neil Pik » Thu, 16 Dec 1999 04:00:00

Hans - because the mssqlserver (virtual) service issues these then the drives
have to be the letters that you would use locally on the machine.  As long as
this is the case then it should work ok.

Q.  I am having problems with SQL Server running bcp from xp_cmdshell - why
does it not run or see the files I want it to?
(v1.1 1999.11.03)

A.  First make sure that you have the rights to run xp_cmdshell - do an
xp_cmdshell 'dir' and check you get a resultset of filenames back.

The MSSQLSERVER service is running under a separate set of NT credentials.  It
doesn't matter who YOU are logged on as (after all SQL runs quite happily when
no-one is logged on to the console doesn't it).  Therefore your logon account
and any mapped drives are irrelevant.  It is SQL Server running the program
(e.g. bcp) not you.

If you're logged on as "sa" or are still running SQL 4.x then xp_cmdshell runs
with the NT account that the mssqlserver service is configured to run under.  
The default set of NT credentials used by MSSQLSERVER is the Localsystem
account.  You can check what userid that MSSQLSERVER is running under by
looking at control panel/services highlighting MSSQLSERVER and choosing the
start-up option.  If no username is present then you are using the localsystem
account - this account has no access to shares on the network as it isn't an
authenticated network account.  

If you're not logged on as "sa" then another account may be used :-

   With 6.5 and below it is used if the option (in SQL setup)  'xp_cmdshell -
simulates Client' is checked.  

   With SQL 7.0 the other account is always used for non "sa" users.  

This account is SQLExecutiveCmdExec for SQL 6.x and SQLAgentCmdExec for SQL
7.0.  These accounts are only in the local user group by default.
So, if you want bcp running under xp_cmdshell to access a network resource you
have two choices :-

1.  Change the account the MSSQLSERVER service runs under to a user account
with the relevant network rights.


2.  Amend the following registry value on the TARGET server and add the
sharename you want to access - the share does not then authenticate who is
coming in and so a Localsystem account will work.  The server service on the
target server must be re-started before the change takes effect.  Note that
this effectively removes security on that share, so you need to be careful
about what is in the share.


Whichever method you use, you MUST use a UNC name to reference the resources
required and not a drive letter.

i.e.  xp_cmdshell 'bcp servername..tablename out \\server01\share\bcp.fil

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please reply only to newsgroups)
 SQL FAQ (374 Entries) see

 or www.ntfaq.com/sql.html (+ ntfaq download)
 or http://www.sql-server.co.uk


1. Error when bulk insert follows another large bulk insert

I am running a job out of MS SQL Enterprise Manager where the first step
turns off transaction logging, the 2nd step is a large bulk insert step,
and the 3rd step is another large bulk insert step.  I receive the
following error when executing the 3rd step.  Is this error occurring
because the database is still comitting data from the previous large bulk
insert at the same time as it is performing the next bulk insert?  If so,
is there a workaround?  Thank you for any help you can provide.

Error message from Step 3 (2nd bulk insert):
Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation
(such as CREATE FILE) operations on a database must be serialized.
Reissue the statement after the current backup, CHECKALLOC, or
file manipulation operation is completed. [SQLSTATE 42000] (Error 3023)  
The statement has been terminated. [SQLSTATE 01000] (Error 3621).  
The step failed.

Posted via CNET Help.com

2. printing over Novell/Netware from sqlplus on winNT

3. SQL Server Bulk insert problem

4. FM 4.1 and MSWord

5. Bulk insert SQL Server 7


7. Bulk insert in SQL Server 7.0 SP4

8. Funny display problems, refresh broken somehow

9. SQL Server bulk insert error

10. Bulk Insert and loop (SQL Server 2000)

11. Bulk inserts into SQL Server

12. Bulk Insert SQL Server in C#

13. sql server 2000 memory issues when bulk inserting