Re: VBA - Create a recordset with no records for inserting

"Bob Barrows [MVP]" wrote:
What about putting the data into a text file and using BULK INSERT (look
it up in BOL) to bring it into sql?

Actually, that is what I will be doing.

The issue I was trying to get around was integrated security. If I log
directly into the server, I can run a PROC that gets files from my desktop.
But if I log into my desktop, I get back an error talking about NULL logins,
and the network people here tell me it's related to security ... I can't log
into my box and run a PROC on the server that comes back to my box; my
credentials won't make the last jump.

I know I can make an invisible share on my database server, that way I know
the BULK INSERT command will be able to get to the file. Excel will call a
PROC that writes a row into a FileImport table, then push the ID of the row
into a queue the ServiceBroker will manage, then return immediately to Excel.
The ServiceBroker will construct the dynamic SQL statement to BULK INSERT the
file (Because the file name has to be a string, it can't be an expression).
But the file needs to be on the database box.

I thought I could use ADO to move the data into my BULK INSERT table so I
wouldn't have to make that FileShare on my server box. Since I own this box,
I can do that ... but if and when this database moves to a box I don't own,
this could be a problem.

Either way, the file is now in my BULK INSERT table. I call the lookup &
complex validate PROCs, and if all that is OK, move the data into the "real"
tables

Bob

.

Relevant Pages

  • Re: Variable server names
    ... My initial desire was to do this in a single stored proc. ... Then I realized that mixing dynamic SQL and a table variable was going ... executing the remote from the local. ... but I understood it as management wanted to connect to a server ...
    (microsoft.public.sqlserver.programming)
  • ANN: CsvReader 2.0 Released
    ... One of the most common tasks involving CSV data in a Microsoft ... environment is to bulk insert data originating from a CSV file into SQL ... another datasource directly into SQL Server at almost DTS speeds. ...
    (microsoft.public.dotnet.general)
  • Re: Ignoring duplicates in BULK INSERT with ADO and SQL Server 200
    ... I am using bulk insert to transfer hugh data from a .dbf file to my sql ... from SQL Server. ... Because for SQL Server to do BULK INSERT that is what it demands. ...
    (microsoft.public.data.ado)
  • Import mit Bulk Insert Probleme mit Umlauten
    ... Ich bin mit meiner Applikation von SQL 2000 Server auf SQL 2005 Server ... Jetzt habe ich mit den Bulk Inserts mein Problem ... In der zu importierenden Textdatei stehen die Umlaute korrekt ...
    (microsoft.public.de.sqlserver)
  • Need macro to drag values down to blank cells
    ... I have a spreadsheet that I use to upload data to a SQL ... Server using bulk insert. ...
    (microsoft.public.excel.programming)