Bulk import stored procedure

I am trying to devise the best way to perform the following:
Import/export word documents into and out of a table in SQL 2005 for a VB
program to access. The user of the program would be able to choose what doc
they want to import into the table thus the location of the file and name of
the file can change. I have made a simple inserst statement that works when I
explicitly state the file path, but I need a variable file path, but I can't
seem to find how to do this or if there is a better way. The following is
what I have come up with so far even thought I can't execute it to create the
stored procedure because it won't accept the @.doclink in the Bulk statement.
Anyone have ideas on how to do this or a better way?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DocInsert]
(
@.PermitID int,
@.DocLink char(255),
@.Title nchar(100)
)
AS
BEGIN
SET NOCOUNT ON;
Insert into documents(doclink,title,created,createdby,doc)
Select @.doclink,@.title,getdate(),'John Doe',* from openrowset(bulk
@.doclink,single_blob,codepage='raw') as doc
END
GO"Joe S." <joecrew@.news.postalias> wrote in message
news:4E3401D2-24BA-41B6-ACFE-144D7D15772B@.microsoft.com...
>I am trying to devise the best way to perform the following:
> Import/export word documents into and out of a table in SQL 2005 for a VB
> program to access. The user of the program would be able to choose what
> doc
> they want to import into the table thus the location of the file and name
> of
> the file can change. I have made a simple inserst statement that works
> when I
> explicitly state the file path, but I need a variable file path, but I
> can't
> seem to find how to do this or if there is a better way. The following is
> what I have come up with so far even thought I can't execute it to create
> the
> stored procedure because it won't accept the @.doclink in the Bulk
> statement.
> Anyone have ideas on how to do this or a better way?
>
This isn't going to work in the general case. For this to work the SQL
Server must be able to access the file location. If the file is on the
client location, the pathing would be different and the server's account
wouldn't be able to open the file.
Instead of passing the file path to the procedure, pass the file bits. Just
declare the procedure to take a parameter of type varbinary(max) and pass
the file bits from VB. From VB load the file into a byte array and pass it
to through a SqlParameter.
David