Bulk Load with Parent / Child failing

I'm trying to bulk load an xml with parent child nodes.
I have setup an sql:relationship and I have also setup the two database
columns as Identity columns. However when I try to load the data, SQL is
telling me that the column can not be NULL ...
Any ideas please ?
Michael Tissington
http://www.oaklodge.com
http://www.sqlview.netHello Michael,
Thank you for using Microsoft MSDN newsgroup!
I understand you encountered the issue when importing xml with parent/child
nodes. To get a better idea of the exact issue, will you send the xml files
and script to create the tables in database to me so that I may reproduce
the issue on my side? You could send it to me at petery@.microsoft.com.
Also, you may want to refer to the following article for some know issue:
330582 PRB: "No Data Was Provided for Column" Error Message When You
Propagate Identity Column with SQLXMLBulkLoad
http://support.microsoft.com/defaul...kb;EN-US;330582
I look forward to your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

BULK INSERT FROM UTF-8 FILE

Hi there,

I have some text files saved using a UTF-8 encoding. The "BULK INSERT"
statment in Sql Server 2000 is failing with a column length error. Saving
the file as ASCII removes the problem. However, I would like to import files
in UTF-8 format. I understand that the BCP tool, when used from the command
line, can take an "-F UTF8" argument, which allows it to work. Can this be
done from the SQL> prompt with the BULK INSERT statement?

Cheers,

Tobin"Tobes (Breath)" <tobin_dont_spam_me@.breathemail.net> wrote in message
news:bpi91v$1osvna$1@.ID-131901.news.uni-berlin.de...
> Hi there,
> I have some text files saved using a UTF-8 encoding. The "BULK INSERT"
> statment in Sql Server 2000 is failing with a column length error. Saving
> the file as ASCII removes the problem. However, I would like to import
files
> in UTF-8 format. I understand that the BCP tool, when used from the
command
> line, can take an "-F UTF8" argument, which allows it to work. Can this be
> done from the SQL> prompt with the BULK INSERT statement?
> Cheers,
> Tobin

I believe that the -F argument is for the Sybase version of BCP. The closest
equivalent in MSSQL is probably -w, but it may not be functionally
equivalent. Using BULK INSERT, that would be "DATAFILETYPE = 'widechar'".

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3fbd17b2$1_2@.news.bluewin.ch...
> I believe that the -F argument is for the Sybase version of BCP. The
closest
> equivalent in MSSQL is probably -w, but it may not be functionally
> equivalent. Using BULK INSERT, that would be "DATAFILETYPE = 'widechar'".

Thanks for the response, Simon. Ah, I see, I must have found a Sybase
example for the -F argument! I actually discovered the 'widechar' thing in
the docs, but for some reason it was still falling over on the last column
of the first row. I tried the other options such as 'RAW' etc but with no
luck!

Currently I'm asking the developer to output the file as ASCII or ANSI, so
that should be solved (although it would be nice if the BULK INSERT
accommodated such variations). However, the files are gonna be UNIX files,
so my next trick is to have BCP understand how to read these!

Tobin

bulk insert fails

I'm setting up a new 2005 server and bulk insert from a client workstation (using windows authentication) is failing with:

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\FILESERVERNAME\sharedfolder\filename.txt" could not be opened. Operating system error code 5(Access is denied.).

Here's my BULK INSERT statement (though I'm pretty sure there's nothing wrong with it):

BULK INSERT #FIRSTROW FROM '\\FILESERVERNAME\sharedfolder\filename.txt'
WITH (
DATAFILETYPE = 'char',
ROWTERMINATOR = '\n',
LASTROW = 1
)

If I run the same transact SQL when remote desktopped into the new server (under the same login as that used in the client workstation), it imports the file without errors.

If I use the sa client login from the client workstation (sql server authentication) the bulk insert succeeds.

My old SQL 2000 server lets me bulk insert the file without errors even from my client workstations using windows authentication.

I have followed the instructions on this site: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=928173&SiteID=1
, but still no luck and same error.

I'm pretty sure it is being caused by the increased constraints on bulk insert in 2005. Hoping someone can help. The more specific the better. If you need more info, let me know.

Oh and I've also made sure that the SQL service uses a domain logon account rather than the local system account (this would work on the 2000 server, but not 2005).

Note that the file server (source file resides there) is a DIFFERENT machine than the 2005 SQL server. If I move the source file to the sql server machine the error goes away (not a preferred solution though).

I'm almost positive that what I need to do is make sure that the SQL server is setup for delegation when a windows authenticated user attempts to bulk load a file from a second server.

Can someone provide instructions?

Thanks!

Can you access the file using thew full path \\FILESERVERNAME\sharedfolder\filename.txt ?

of course, with the user who execute the bulk insert.

Did you alread verify the share and NTFS permition?

|||Yes, I can access file. I have given both that user and the sql service user account full access.

Note that this user has no problem running the same bulk load from my old 2000 server.

I'm pretty sure that my user is not being delegated through to the file server.
Is there a way to catch the user information that BULK INSERT is using to access a file? A SQL trace doesn't catch it.

I'm not a windows system admin, so forgive my ignorance, but what's an ntfs permission? I thought NTFS was just the hard drive file system format.
.
Do you know how to setup delegation for users?

|||

When a told NTFS permission I,d mean the file system permission.

|||Yes, the user has full network and local permission to the file.
|||

Can you try to make lower the authentication level of NTLM protocol?

I saw this posts, try to verify your solution: http://forums.microsoft.com/msdn/showpost.aspx?postid=270868&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

bulk insert fails

I'm setting up a new 2005 server and bulk insert from a client workstation (using windows authentication) is failing with:

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\FILESERVERNAME\sharedfolder\filename.txt" could not be opened. Operating system error code 5(Access is denied.).

Here's my BULK INSERT statement (though I'm pretty sure there's nothing wrong with it):

BULK INSERT #FIRSTROW FROM '\\FILESERVERNAME\sharedfolder\filename.txt'
WITH (
DATAFILETYPE = 'char',
ROWTERMINATOR = '\n',
LASTROW = 1
)

If I run the same transact SQL when remote desktopped into the new server (under the same login as that used in the client workstation), it imports the file without errors.

If I use the sa client login from the client workstation (sql server authentication) the bulk insert succeeds.

My old SQL 2000 server lets me bulk insert the file without errors even from my client workstations using windows authentication.

I have followed the instructions on this site: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=928173&SiteID=1
, but still no luck and same error.

I'm pretty sure it is being caused by the increased constraints on bulk insert in 2005. Hoping someone can help. The more specific the better. If you need more info, let me know.

Oh and I've also made sure that the SQL service uses a domain logon account rather than the local system account (this would work on the 2000 server, but not 2005).

Note that the file server (source file resides there) is a DIFFERENT machine than the 2005 SQL server. If I move the source file to the sql server machine the error goes away (not a preferred solution though).

I'm almost positive that what I need to do is make sure that the SQL server is setup for delegation when a windows authenticated user attempts to bulk load a file from a second server.

Can someone provide instructions?

Thanks!

Can you access the file using thew full path \\FILESERVERNAME\sharedfolder\filename.txt ?

of course, with the user who execute the bulk insert.

Did you alread verify the share and NTFS permition?

|||Yes, I can access file. I have given both that user and the sql service user account full access.

Note that this user has no problem running the same bulk load from my old 2000 server.

I'm pretty sure that my user is not being delegated through to the file server.
Is there a way to catch the user information that BULK INSERT is using to access a file? A SQL trace doesn't catch it.

I'm not a windows system admin, so forgive my ignorance, but what's an ntfs permission? I thought NTFS was just the hard drive file system format.
.
Do you know how to setup delegation for users?

|||

When a told NTFS permission I,d mean the file system permission.

|||Yes, the user has full network and local permission to the file.
|||

Can you try to make lower the authentication level of NTLM protocol?

I saw this posts, try to verify your solution: http://forums.microsoft.com/msdn/showpost.aspx?postid=270868&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

Bulk Grant Select failing

Hi there
I'm still finding my way in SQL server so the problem might be very simple (hopefully...).
Would anybody have any idea why:

grant select on table1 to ReadGroup

works fine, and

grant create table to ReadGroup

works fine, yet

grant select to ReadGroup

results in
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'to'.?

Any help would be immeasurably appreciated
Cheers!Hi
I think you need to specify what table they are being granted select ON...
ie.
GRANT SELECT
ON table
TO user
GO

add them to db_datareader role if you want database wide select ...
des|||if u are looking for granting select permissions to all the user tables
then :

declare @.table varchar(100)
select [name] into #temp from sysobjects where xtype='u'
while exists (select * from #temp)
begin
select top 1 @.table=[name] from #temp
exec ('Grant select on '+@.table+' to ReadGroup')
delete from #temp where [name]=@.Table

end
drop table #temp|||Hi

Thanks for this - Yep, wanted to set permissions to over 300 tbales in one go. Wondered if the failure was due to not specifying object types but wasn't sure how to - thanks for your help guys

Cheers