conflict with Novell - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Administration (2000)

conflict with Novell

Author

Topic

TorreyKite
Starting Member

40 Posts

Posted - 2003-07-30 : 09:34:16

Several symptoms, one problem (I think)

1) I've encountered SQL-DMO error 21776 saying that "dbo" is not in "Users collection"
this occured when I check the properties on my SA user account.

2) Bulk insert cannot be completed because SQL server cannot see the local C:. It says that the path does not exist. (this was working fine before).

The probable cause...Novell The files I need for the bulk insert are on a novell server so I loaded "Gateway service for netware" (and client) and had it login to the novell server. I did not enable gateway services. Preferably I want my bulk insert to grab files from the network drive but if need be I guess I can transfer them to the local drive.

so I've done some searches and checked out some FAQs but can't quite find a clear answer of how to remedy this.

Thanks in advance.

TK

PS the line up is
NT server 4.0 with SQL Server 7.0
And
Novell Netware 4.1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-30 : 12:44:32

1.The answer to your first question can be found on MS' knowledge base:
[url]http://support.microsoft.com/[/url]

Just search on "dbo" is not in "Users collection" and you'll see a bunch of articles returned. You'll have to go through them to see which fits your environment.

2.When you use the bulk insert, the file has to exist on the server. To test if it is, go to the server and go to start and then to run. Then type in the path and file name and see if the server can see it.

Tara

TorreyKite
Starting Member

40 Posts

Posted - 2003-07-30 : 15:48:51

Does running a bulk insert have anything to do with the dbo?

I read about people having problems with their sa accounts password being changed. and they transfer the dbo to another user login.

for my case I was methodicly piecing together this stored procedure
first :
EXEC sp_data_insert 'c:\data\db01.txt'
this woked fine, populating my table with no problems.

then logged into my novell server, mapped "t:\" to my NT box
then :
EXEC sp_data_insert 't:\db01.txt'
"Could not bulk insert. File 'PathFileName' does not exist."
note that the file is accessable via start-run aswell as explorer.

then backing up I tried:
EXEC sp_data_insert 'c:\data\db01.txt'
"Could not bulk insert. File 'PathFileName' does not exist."
and then i started having problems with my sa account.

I'm considering running "sp_ChangeDBOwner" and setting all DBOs to a username thats working properly, but I don't want to make things worse.
I found alot of articles in http://support.microsoft.com/
but none seemed to apply, aside from changing DBOs.

I hope this might give some more background to my problem. I guess i'm not sure how the different elements here relate to one another to know where to begin.

Thanks

TK

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-30 : 15:55:42

The bulk insert problems and the dbo thing are completely separate problems.

When you change the sa password, you have to go through all of the things that use the sa account and modify it there too. You do not transfer the dbo to another user login. sa must have dbo on all database. Additional accounts can be dbo as well though.

You can NOT use bulk insert with mapped network drives. I am not sure why the file on the C drive doesn't work though. You might want to post the code for sp_data_insert so that we can see if the problem is in there. Is C:\data\db01.txt a valid path on the server (not from the client but from the server)? To verify it, run this:

xp_cmdshell 'dir c:\data'

If the above returns db01.txt as one of the files in the data directory, then the command should work, so post your code to the stored procedure if it doesn't.

BTW, the error that you are getting for the sa account in EM doesn't really affect anything.

I do not know what you mean by changing DBOs.

Tara

TorreyKite
Starting Member

40 Posts

Posted - 2003-07-30 : 16:43:12

Here's my stored procedure:

 
CREATE PROCEDURE sp_data_insert 
@PathFileName varchar (100)
AS

BULK INSERT	TEMPDATA
FROM PathFileName
WITH (FIELDTERMINATOR ='"",""')

also the results from "EXEC xp_cmdshell 'dir c:\data'" are:

quote:

output
------------------------------------------------
Volume in drive C has no label.
Volume Serial Number is A487-1EC1
NULL
Directory of c:\data
NULL
07/30/03 04:12a <DIR> .
07/30/03 04:12a <DIR> ..
07/29/03 08:41a 13,595,662 DB01.TXT
07/28/03 08:43a 13,595,796 DB01old.TXT
07/29/03 12:20p 131 GetChiroData.bat
07/27/03 09:01p 894,638 POSADA.csv
07/25/03 01:16p 700,647 POSADA.txt
7 File(s) 28,786,874 bytes
2,782,320,128 bytes free
(14 row(s) affected)

seems to be connecting fine. but still says it doesn't exist when running sp_data_insert

***********
for "changing DBOs... " I was refering to changing the each databases ownershp to another user. (I'm still new at this stuff so please disregard my ideas if they make no sense

).

as far as the SA error in EM you're right it does not seem to disrupt any internal(SQL Server) activity. other VB apps can retrieve data just fine.

thanks for the help.

TK

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-30 : 16:48:29

Your stored procedure is incorrect. What you are trying to do requires dynamic sql. Run this in Query Analyzer:

BULK INSERT TEMPDATA
FROM 'c:\data\db01.txt'
WITH (FIELDTERMINATOR ='"",""')

Let me know if it works. If it does, then I will post the code that will work in a stored procedure using dynamic sql.

Changing the owner of the database will not fix anything. The only time that you need to do this is if you need to delete the user but it owns a database.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-30 : 16:55:15

Here's the stored procedure rewritten using dynamic sql:


CREATE PROCEDURE sp_data_insert 
@PathFileName varchar (100)
AS

SET NOCOUNT ON --I always put this at the top of a stored procedure, just good practice

DECLARE @SQL VARCHAR(7000)

SELECT @SQL = ''

SELECT @SQL = @SQL + 'BULK INSERT TEMPDATA '
SELECT @SQL = @SQL + 'FROM ' + @PathFileName + ' '
SELECT @SQL = @SQL + 'WITH (FIELDTERMINATOR =''"",""'')'

EXEC (@SQL)

RETURN

You have to build a string in order to do what you are trying to do. This is called dynamic sql. The variable that has the path and file name has been concatenated with the rest of the string. I had to add additional single quotes in the fieldterminator part. The additional single quotes are due to building string and are not really there. If you want you can add a PRINT statement to the stored procedure to see what it is executing:

PRINT @SQL --just add it right before the EXEC

I recommend only having the PRINT statements in there for testing and debugging purposes. Once the stored procedure works and you are happy with it, remove the PRINT statement

Tara

TorreyKite
Starting Member

40 Posts

Posted - 2003-07-30 : 17:14:58

ok cool...

I'm gonna check it out more tomorrow.
have a good night
TK

TorreyKite
Starting Member

40 Posts

Posted - 2003-07-31 : 10:09:51

YEEEHAAWW !!!!
he hem... please don't mind my enthusiasm.
Thanks Tara that code helped but i did have to edit it some.
I added 2 sets of single quotes on either side of + @pathfilename +
and took out a set of double qoutes by the field terminator.


CREATE PROCEDURE sp_data_insert 
@PathFileName varchar (100)
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(7000)
SELECT @SQL = ''
SELECT @SQL = @SQL + 'BULK INSERT TEMPDATA '
SELECT @SQL = @SQL + 'FROM ''' + @PathFileName + ''''  
SELECT @SQL = @SQL + ' WITH (FIELDTERMINATOR =''","'')'
PRINT @SQL
EXEC (@SQL)

RETURN

again thanks for the help.
I think I'll read-up on this dynamic SQL stuff.

TK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources