bulk insert on remote computer

Hi there!
I am using bulk insert to enter data in tables. I try to bulk insert
data on a remote server but the bulk insert statement must access
files located on localhost computer (from where I query a bulk insert
with query analyser)..and I get a 'access denied' from sqlserver
the only way I found to prevent this, is to log on the remote computer
as the same user that the local computer, to give permissions...
is there any way to do this'
thanks a lot :)
++You need to put the file in some location accessible from the server.
If that location is on the network then make sure you specify the UNC
path name rather than use drive letters.
David Portas
SQL Server MVP
--|||On 26 Apr 2005 07:28:18 -0700, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:
yes, I use UNC path..but I guess this problem is due to a windows
configuration..I mean I must define the same user on both computers to
allow one to access to other whatever programs are used...
++
Vince

>You need to put the file in some location accessible from the server.
>If that location is on the network then make sure you specify the UNC
>path name rather than use drive letters.|||Just give the access rights to whatever account you use for the SQL
Server service. If you don't know what account that is then check the
Log On properties of MSSQLSERVER in the Services dialog under Control
Panel.
David Portas
SQL Server MVP
--

BULK INSERT from another server

Hi there!

I'm trying to do a BULK INSERT on my SQL Server with a file that resides on my webserver (two different locations). I am trying to run this BULK INSERT from a stored procedure, which is called from an ASP page. This does work when I'm running everything on my local machine (both SQL Server and local IIS).

When I try this, I get an error along the lines of <file> could not be opened.

When I try to send it the url, I get the following: <file> could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).

I still want to keep the files wherever they are, as I don't think I can write these files from my website.

Thank you for all and any help.The only thing I can tell you is that the bulk copy operation looks at the location of the file from the perspective of the database server, not whatever remote machine you happen to be executing from. Other than that, this sounds more like a network issue.

blindman

Bulk Insert and Decimal type

Hi there

I am trying to write a program which will bulk load data from a bcp file into a newly made database on the users PC.

I create the data from an existing DB using SQL-DMO BulkCopy.
I then load it into the users DB using "Bulk Insert " transact SQL.

It all works fine on SQL Server 2000. However on SQL Server 7.0 whenever the .bcp file is being loaded into a table with a field of type decimal, it throws an OLEDB stream error. Even when the .bcp file is empty.

I have tried exporting/importing the data as tab delimited and as native, but it seems to make no difference.

This has really got me stumped and I am running out of time. Can anyone help?

Thanks.

justinOK I just discovered from the Microsoft web site that there is a bug in SQL Server 7.0. Using Bulk Insert on a table that includes a default value for decimal or numeric data typed fields, throws an error.

There is no solution. It is incurable. The workaround is to "use bcp instead."

Programatically that would be an issue, so i will have to use DMO.

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