SQL Server Bulk Insert Help


#1

In Lucee 5 I am unable to run a Bulk insert (from a CSV file) no matter what I try. Clearly I am overlooking something simple. I have my basic query as follows

qImpObj = new query(
				 name="qImport",
				 sql = "BULK INSERT my_sql_server_table_name
					FROM 'd:\csv\csv_import.csv'
					WITH
					  (
							 FIRSTROW = 2,
					     FIELDTERMINATOR ='|',
					     ROWTERMINATOR ='\n'
					  )
				 "
				 );

I get various version of the following file/permissions error whether I try to load the file after directly uploading to the SQL Server (so it has local direct access) or if I put it on a share (from the Lucee server). I even tried creating a Stored Procedure and running from Lucee and still same error. It is worth noting the stored procedure and all queries I have tried run without issue when ran directly on SQL Server, its just when Lucee calls them I get the permissions errors.

Operating system error code 3(The system cannot find the path specified.)

OR

Cannot bulk load because the file ""d:\csv\csv_import.csv"" could not be opened. Operating system error code 5(Access is denied.).;lucee.runtime.exp.DatabaseException:

Any guidance others have used in such situations would be greatly appreciated.


#2

You’re specifying Lucee 5.1, but not other versions in the stack. Did it work before with prior versions of Lucee? Railo? ACF?

Also, can you read the csv file with Lucee via fileRead() for example?


#3

I think that refers to a file on the db server?


#4

Thank you for your reply, I am using Lucee 5.2.4.37. I am not sure this is a Lucee issue, I have not done this for a while since maybe ACF 9. Anyhow yes Lucee can fileRead() the csv no problem. Unfortunately that is how I am using the file now (using fileRead() and then looping inserts in batches of 1000.


#5

Correct, as I mentioned I have tried that. I believe this is where the permissions/ file not found issue lies as SQL Server needs to read the file from a directory it has access to and for some reason so does Lucee. My thinking is that this should have nothing to do with Lucee needing to read the file as it is not doing any of the work - SQL Server is. However as mentioned, even with the file locally stored on the SQL Server machine and the insert query being in a Stored Procedure Lucee still throws up a file permissions error which indicates it is still trying to read the file for some reason.


#6

Also the SQL Server driver extension on Lucee is version 6.2.2.jre8


#7

what do the stacktraces look like?


#8

lucee.runtime.exp.DatabaseException: at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:845) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:752) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199) at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:1964) at lucee.runtime.type.util.QueryUtil.execute(QueryUtil.java:293) at lucee.runtime.type.QueryImpl.execute(QueryImpl.java:271) at lucee.runtime.type.QueryImpl.(QueryImpl.java:224) at lucee.runtime.tag.Query.executeDatasoure(Query.java:912) at lucee.runtime.tag.Query.doEndTag(Query.java:664) at org.lucee.cfml.base_cfc$cf.udfCall1(/org/lucee/cfml/base.cfc:110) at org.lucee.cfml.base_cfc$cf.udfCall(/org/lucee/cfml/base.cfc) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:108) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:367) at lucee.runtime.type.UDFImpl.call(UDFImpl.java:227) at lucee.runtime.type.scope.UndefinedImpl.call(UndefinedImpl.java:772) at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:758) at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1698) at org.lucee.cfml.query_cfc$cf.udfCall(/org/lucee/cfml/query.cfc:35) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:108) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:367) at lucee.runtime.type.UDFImpl.call(UDFImpl.java:227) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:697) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:580) at lucee.runtime.ComponentImpl.call(ComponentImpl.java:1918) at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:758) at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1698) at mls.mls_cfc$cf.udfCall(/mls/mls.cfc:144) at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:108) at lucee.runtime.type.UDFImpl._call(UDFImpl.java:367) at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:698) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:580) at lucee.runtime.ComponentImpl.callWithNamedValues(ComponentImpl.java:1935) at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:835) at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1719) at mls.mls_update_cfm180$cf.call(/mls/mls-update.cfm:22) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:939) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:833) at lucee.runtime.listener.ModernAppListener._onRequest(ModernAppListener.java:224) at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:43) at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2405) at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2395) at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2363) at lucee.runtime.engine.Request.exe(Request.java:46) at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1084) at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1032) at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:102) at lucee.loader.servlet.CFMLServlet.service(CFMLServlet.java:51) at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:486) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1455) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748)


#9

Absolutely.

Are Lucee and SQL Server on separate machines?

Are you sure that SQL Server can read that file (permission-wise)?


#10

Yes, both are on separate machines.
Yes, as mentioned in my original post I can run the exact request directly from SQL Server without issue. It is only when I make the exact same SQL request using cfquery that I get the permissions error (no other changes).


#11

Further as also mentioned if I used cfstoredproc it also gives me a permissions error (again running the stored procedure which simply contains the same SQL directly on SQL Server executes without issue).


#12

Is there another SQL Server in that environment (e.g. for Development/Staging/etc)?

Is it possible that you are connecting to the wrong SQL Server?


#13

OK, so I just tried this same query in one of my development environments and it ran without issue. Only difference was the Lucee server was running on Windows Server 2008 (instead of the production one above which is running on Server 2016) and the SQL Server (development) is SQL Server 2008 running on Windows Server 2003 (Production is SQL Server 2012 running on Windows Server 2012) so I can only surmise that the permissions issue are related to the Windows Server OS. To answer your question, no this is the only SQL Server in this environment unfortunately.


#14

OK, I just setup in another production environment very similar (actually almost identical in regards to OS/Lucee/SQL Server) and the query ran without issue so the issue is related specifically to this environment which I’ll need to troubleshoot further. I appreciate very much the feedback and apologize for the wasted time.


#15

Sounds like a permissions issue. Try turning off UAC for starters and if the process is running as a user, give it administrator group permissions.


#16

I resolved this by adding the SQLSERVER user with full permissions to the folder that contained the csv file. As mentioned in other environments this was not needed to work. Hopefully this helps someone else suffering form the same annoyance!


#17

Note, bulk insert is a distinct permission that can be checked.