October 10, 2016 at 9:17 am
These commands below were copies directly from a website. I changed the path.
SELECT BulkColumn
INTO #temp
FROM OPENROWSET (BULK 'C:\Users\moody\Desktop\api.json', SINGLE_CLOB) as j
SELECT BulkColumn
INTO #temp
FROM OPENROWSET (BULK 'S:\MIS\memphis\api.json', SINGLE_CLOB) as j
On both occasions SQl server comes up with a stupid excuse.
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "C:\Users\moody\Desktop\api.json" does not exist.
THE FILE IS THERE ( I KNOW FOR SURE ).
Is there another method to load this to SQL Sever
October 10, 2016 at 9:21 am
What do you get if you run this?EXEC xp_cmdshell 'dir C:\Users\moody\Desktop\api.json'John
October 10, 2016 at 9:22 am
John:
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.
October 10, 2016 at 9:32 am
My guess would be the SQL Server service account not having access to that location. Can't see the location / file => file doesn't exist
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
October 10, 2016 at 9:37 am
andrew gothard (10/10/2016)
My guess would be the SQL Server service account not having access to that location. Can't see the location / file => file doesn't exist
Agreed. C:\Users\username\{anything} is all private folders.
move the file to c:\Data\MyFiles or something, and you'll have better luck. don't try to use my documents/desktop or anything for file locationn that SQLis going to access
Lowell
October 10, 2016 at 9:44 am
SELECT BulkColumn
INTO #temp
FROM OPENROWSET (BULK 'H:\api.json', SINGLE_CLOB) as j
--I moved the file to a NETWORK SHARE.
gives this error.....
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "H:\api.json" could not be opened. Operating system error code 3(The system cannot find the path specified.).
Question: How do know which drives are visible to SSMS ?
October 10, 2016 at 9:50 am
H:\ isn't a network share - it's a mapped drive. Drives that you have mapped are visible only to you, not to the SQL Server service account. Try using a UNC path instead.
John
October 10, 2016 at 9:55 am
Lowell:
Gave it a few places but had no luck
SELECT BulkColumn
INTO #temp
FROM OPENROWSET (BULK 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\api.json', SINGLE_CLOB) as j
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\api.json" does not exist.
SELECT BulkColumn
INTO #temp
FROM OPENROWSET (BULK 'C:\Users\MSSQLSERVER\Desktop\api.json', SINGLE_CLOB) as j
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "C:\Users\MSSQLSERVER\Desktop\api.json" does not exist.
October 10, 2016 at 9:57 am
Not sure what a UNC path is Are you saying withot using S:\ or H:\ you want me to use the full path ( hard coded server name, directory name etc )
October 10, 2016 at 10:02 am
John M:
Still this is what I get if I used the fully qualified path...
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "\acorp1.xxx.org\integrations\api.json" does not exist.
October 10, 2016 at 10:03 am
mw112009 (10/10/2016)
John:Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.
This message means that xp_cmdshell is disabled on your server, and to be honest, rightly so. Some info here[/url]. It's not that the server is being denied access to the directory, it simply not allowed to use the function.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 10, 2016 at 10:17 am
A UNC path starts with a double backslash.
John
October 10, 2016 at 10:44 am
Thom A
Agreed! But our challenge is no xp_CMDSHELL
The access issues were reported because the OPENROWSET was not able to identify the JSON file
October 10, 2016 at 11:07 am
ok bulk has to references files ON THE SERVER or a fully qualified UNC path
so unless the SQL server is on your local machine, you need to move the file, or use a UNCPath:
if the file exists on your local workstation, it has to be
SELECT BulkColumn
INTO #temp
FROM OPENROWSET (BULK '\\MWDMachine\c$\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\api.json', SINGLE_CLOB) as j
or a file that was copied to the server itself. mapped drives are invalid, as you have to think what the service account has access to.
SELECT BulkColumn
INTO #temp
FROM OPENROWSET (BULK 'C:\Data\api.json', SINGLE_CLOB) as j
Lowell
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy