October 4, 2007 at 12:45 pm
Hi everyone,
I've developed a stored procedure that imports data from an excel sheet to a new sql table. When I first tested it in my local pc I got the following error:
"Server: Msg 7405, Level 16, State 1, Line 1
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query. "
Then I coded SET ANSI_NULLS ON and SET ANSI_WARNINGS ON in my stored procedure and worked. When I moved it to the sqlserver in the hosting I got same error again. I asked the database administrator to check if these both options were enabled in Query Analyzer and he answered YES, he even sent a screenshot which shows they are enabled. Either way, I'm still including both lines:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
in my stored procedure and not working on real server.
The following is the code, I hope anyone can help asap. The excel file has one sheet called 'Sheet1' and has 2 cols: 'rowid' and 'description'. Any soon reply will be well appreciated.
Thanks
CREATE PROCEDURE SP_LOAD_FILE
(
@tablename nvarchar(30),
@file nvarchar(500)
)
AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
DECLARE @strSql AS nvarchar(4000)
SET QUOTED_IDENTIFIER OFF
BEGIN
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tablename)
BEGIN
-- Drop de la tabla de Nomenclatura
SELECT @strSql = 'DROP TABLE '+@tablename
EXEC sp_executesql @strSql
END
SELECT @strSql = "SELECT rowid AS id,description INTO "+@tablename+" FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\webs\cig.org.ec\www\CO\Uploads\files\"+@file+"','SELECT * FROM [Sheet1$]') ORDER BY id"
EXEC sp_executesql @strSql
SET QUOTED_IDENTIFIER ON
END
GO
October 4, 2007 at 1:13 pm
October 4, 2007 at 1:21 pm
Yes,
but I got error on local testing before adding SET ANSI_NULLS ON and SET ANSI_WARNINGS ON in the script. After I added both settings then the error was gone on local pc. After that, same stored procedure (including SET ANSI_NULLS ON and SET ANSI_WARNINGS ON) was moved to production server, and got error again, so it seems SET ANSI_NULLS ON and SET ANSI_WARNINGS ON are being ignored or something.
October 4, 2007 at 1:58 pm
This is one of those confusing. In order to set ANSI NULLs or ANSI WARNINGS for a stored proc, you need to set them BEFORE the create procedure syntax. Setting them inside the procedure has no bearing on the setting while the procedure is running. So, you're right - it IS being ignored.
change to this:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE SP_LOAD_FILE
(
@tablename nvarchar(30),
@file nvarchar(500)
)
AS
DECLARE @strSql AS nvarchar(4000)
BEGIN
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tablename)
BEGIN
-- Drop de la tabla de Nomenclatura
SELECT @strSql = 'DROP TABLE '+@tablename
EXEC sp_executesql @strSql
END
SELECT @strSql = "SELECT rowid AS id,description INTO "+@tablename+" FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\webs\cig.org.ec\www\CO\Uploads\files\"+@file+"','SELECT * FROM [Sheet1$]') ORDER BY id"
EXEC sp_executesql @strSql
END
GO
SET QUOTED_IDENTIFIER ON
GO
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 4, 2007 at 2:19 pm
Hey... thanks for the help... it seemd to solve the problem.. but now I got another error:
"Server: Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'CustomOLEDBProvider' has been denied. You must access this provider through a linked server."
I read in other forums that above problem would be solved installing service pack 3 for sqlserver2000. Is this correct? otherwise, if the solution is to add a linked server, how do I add an excel sheet as linked server?
October 4, 2007 at 3:28 pm
your double quotes are messing you up. here's the line
SET @strSql = 'SELECT rowid AS id,description INTO '+@tablename+' FROM OPENROWSET( ''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=D:\webs\cig.org.ec\www\CO\Uploads\files\'+@file+'',''SELECT * FROM [Sheet1$]'') ORDER BY id'
Note that there are NO double quotes in my version, but multiple single quotes next to each other. Also - note that I used SET instead of SELECT (tends to be less whiny in this case).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 5, 2007 at 4:05 pm
Thank you guys for your help... both answers helped a lot!!.. after fixing the code with your both suggestions the script run but got a new error:
Server: Msg 7399, Level 16, State 1
OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any information about the error.
In some other forums they say this error is showing because excel file is opened. I'm sure the file is not opened as it is just uploaded on production server. Other forums say error shows when file is not found. I'm sure file is there because I can see it in my ftp client. Also the path to the file is correct. Any other idea?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply