Problem Statement: I am trying to Load data file from Linux- source to SQL server table- target (sql server installed Windows server). After many meetings with Microsoft we got resolve the connection issue.
Connection string:
[]$kinit windowsserverusername (Windows server AD Account) :- After this command system will prompt for password
[]$ sqlcmd -S servername,port -E -C :- After this command system will connect to sql server with ">"
BULK INSERT SQLSCHEMA.dbo.TABLENAME FROM '**/SOURCE/DataFile/INPUT_DATA.txt**' WITH (FIELDTERMINATOR=',',ROWTERMINATOR='\n');
go
And I got the following error:
Msg 4861, Level 16, State 1, Server SERVERNAME, Line 2
Cannot bulk load because the file '/SOURCE/DataFile/INPUT_DATA.txt' could not be opened. Operating system error code 3(The system cannot find the path specified.).
Issue:
Unable to use fetch the source data file path in sql statement. How to use the Linux server data file path in sql statement or in .sql file.
I have save the BULK load sql statement into .sql file and try to run the command & got same above error.
[]$ sqlcmd -S servername,port -E -C -i INPUTFILE.sql
How to use source file Linux path in sql server sql prompt or in .sql file ??
I have tried .py script by following connection string and we got trusted connection/ certificate issue and Microsoft suggested above sqlcmd method.
"pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)"
``