Solved

SQL Bulk insert works with query analyser but no as passthrough query in Access front end

Posted on 2006-10-25
13
337 Views
Last Modified: 2008-01-09
I've got a Bulk insert sp in SQL Server 2000 which works fine but I get a Runtime error 3146 ODBC call failure when I try to run it as a passthrough query in Access.

The passthrough query works fine if I take the bulk insert out of the sp. I've added the login to the Bulk Insert administrators but no joy. Also, ran a trace while it was executing but couldn't see any problems - the batch appeared to complete without error

Here's the offending bulk insert snippet:

set @sql = "BULK INSERT #temp FROM '"
SET @sql = @sql + @filename
set @sql = @sql + "' WITH (DATAFILETYPE = 'char', FIRSTROW=1, ROWTERMINATOR = '\n') "




Help from experts please
0
Comment
Question by:chiii0ut
  • 6
  • 5
13 Comments
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17805289
So what is the actual content of the Passthrough's SQL?

Does the SP return any rows?  Any parameters?

(Someone will be along at some stage and say "Have you considered just executing the SP through code"?  but I've said it now so no-one can :-p )
Oh - have you considered executing the SP through code?
(Damn - even said it myself).

Even as a temporary test though I mean.  To see if it causes an error?
0
 
LVL 3

Author Comment

by:chiii0ut
ID: 17807784
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER         PROCEDURE sp_db_AddEAN
( @SpotID INT)
AS
      DECLARE @filename VARCHAR(200)
        DECLARE @SQL VARCHAR(2000)      

CREATE TABLE #Temp (EAN NVARCHAR(50))
SET @filename = 'm:\' + ltrim(rtrim(str(@SpotID))) + '.txt'
SET @SQL = "BULK INSERT #temp FROM '" + @filename + "' WITH (DATAFILETYPE = 'char', FIRSTROW=1, ROWTERMINATOR = '\n') "
exec (@SQL)
      RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Not sure what you mean about executing the sp through code?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18185726
Sorry about the Huuge delay - this one slipped through the cracks somehow (which is a shame - more interesting than most ;-)
If it's all done and dusted now then that's cool.  (And if ever things go silent again just ping reminder posts in the question to prompt reminder emails :-)

So does your passthrough look something like
EXEC sp_db_AddEAN

But when you run it it fails?

But if you create an ADO connection and run the SP there does it work?

    Dim cnn As New ADODB.Connection
   
    cnn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=YourServer;User ID=UserName;Password=PW;Initial Catalog=YourDBName;"
   
    cnn.Open
    cnn.Execute "EXEC sp_db_AddEAN"
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 3

Author Comment

by:chiii0ut
ID: 18624189
Hi LP

EXEC sp_db_AddEAN runs fine in query analyser on local and remote machines

in  access I get an odbc call error:
Public Const conn = "DRIVER=SQL Server;SERVER=MyServer;DATABASE=MyDatabase;Uid=Me;Pwd=MyPassword"

Dim dbs As DAO.Database
Dim sqlstr As String
sqlstr = "EXEC sp_db_AddEAN " & SpotID
    Set dbs = OpenDatabase("", 0, 0, conn)
    dbs.Execute sqlstr, dbSQLPassThrough
   
   
    dbs.Close
0
 
LVL 3

Author Comment

by:chiii0ut
ID: 18624204
I've tried your DAO method on a remote machine and get more info:

[Sql server]: The current user is not the database or object owner of table '#temp'. Cannot perform SET operation
0
 
LVL 3

Author Comment

by:chiii0ut
ID: 18709448
any ideas?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18709477
Hi sorry about the gap.

When you execute the passthrough - are you logged in using the exact same user credentials that you are when executing the SP from QA?
0
 
LVL 3

Author Comment

by:chiii0ut
ID: 18710426
That's it - works when logged on as sa in QA but not as other user which isn't a system admin. How can I get it to work with the other user?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18710679
I'm not sure why the login makes the difference just yet...
But am curious about why it's complaining about the use of Set.
("Cannot perform SET operation")

Try changing this line in your SP to:
SET @SQL = 'BULK INSERT #temp FROM ''' + @filename + ''' WITH (DATAFILETYPE = ''char'', FIRSTROW=1, ROWTERMINATOR = ''\n'')'

(i.e. copy paste that in instead)
0
 
LVL 3

Author Comment

by:chiii0ut
ID: 18710820
Hi - still the same with that instead
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 500 total points
ID: 18711221
Not surprised (as permissions shouldn't affect that - but the error message was seemingly complaining about it).

Well - permissions are surely your issue, regardless.
You need to either up the permissions of your logging in user - or use a different user.
I imagine there are a couple of things to consider.
Firstly when you're executing dynamic SQL (with EXEC here) there's no implied rights from the SP itself.  You need explicit permissions on the objects that you're operating upon in the dynamic SQL string.  Since you created the table - that part of it shouldn't be an issue.
Secondly - you're executing a BULK INSERT.
You'll need to have ADMINISTER BULK OPERATIONS permission on your user.
(In Server roles - it appears that this is bulkadmin).
You'll have to add that for your user's login.
See if that makes a difference.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now