chiii0ut
asked on
SQL Bulk insert works with query analyser but no as passthrough query in Access front end
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
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
ASKER
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?
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)))
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?
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;In itial Catalog=YourDBName;"
cnn.Open
cnn.Execute "EXEC sp_db_AddEAN"
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;In
cnn.Open
cnn.Execute "EXEC sp_db_AddEAN"
ASKER
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;DAT ABASE=MyDa tabase;Uid =Me;Pwd=My Password"
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
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;DAT
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
ASKER
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
[Sql server]: The current user is not the database or object owner of table '#temp'. Cannot perform SET operation
ASKER
any ideas?
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?
When you execute the passthrough - are you logged in using the exact same user credentials that you are when executing the SP from QA?
ASKER
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?
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)
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)
ASKER
Hi - still the same with that instead
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?