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

Build SQL query from dynamic Checkbox List

Not sure if this is the place to post this, but here goes.

I need to setup an options screen where my customers can customize which locations will be stored for their user id when pulling reports. I have checkbox list that dynamically loads their locations. I need to store the selected checkbox items in my table and then each time they login in to run a report, it will use the stored Location values in my SQL query.

Synopsis:
Selected locations stored in table. When the report is ran, the location values are pulled and added to my queries WHERE clause.

Thanks.There are 101 ways to skin this cat. And there are a few parts to it. I'm not sure which part you're looking for the help, but since you've posted this in the SQL forum, I'm going to assume you have the asp side of it down.

Some thoughts and assumptions first.

Assumptions
1) You don't have too many locations for each user. (hopefully less than a hundred)
2) You can have 0 to many locations per user.
3) There is a user table and a location table (exact definition is up to you)
4) You know how to loop through arrays in your web code

Thoughts
1) You probably will want to query on which users belong to a particular location
2) You probably want to have a query return which locations belong to particular user (not just from this particular checkboxed web page either)
3) You will want to have the retrieval and update happen in a single connection across the wire from web server to db server.

With the above thoughts, it eliminates the concept of storing the list of locations as a delimited list in a field. It's too hard to query, search, index, join, etc. That leaves you with creating a many to many join table such as:
Table Name: CustomerLocation
Fields:
CustomerID (PK)
LocationID (PK)

The trick you now have to figure out is how to get the array of id's from the web page into a normalized table such as this, and vice versa.

If you had a stored procedure that basically took the input of CustomerID and a delimited list of LocationID's, you could then loop through the list, dynamically create insert statements into the relationship table. For an example, look at http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
This article is using the array as primarily a where clause, but if you used the Method 1 to create an insert statement (with appropriate handling for when the values already exist) then you could accomplish your insert/update/delete scenarios.

For retrieval there's a sneaky way to get a delimited list of values. See this article and look basically at the last sql statement concept.
http://www.sqlteam.com/item.asp?ItemID=2368

David