How do you import a JSON file to SQL sever

Question

Post reply

How do you import a JSON file to SQL sever

mw_sql_developer

SSCoach

Points: 19445
More actions
October 10, 2016 at 9:17 am

#318017

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

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

What do you get if you run this?EXEC xp_cmdshell 'dir C:\Users\moody\Desktop\api.json'John

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

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.

andrew gothard SSChampion Points: 12314 More actions · Answer 3

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.

Lowell SSC Guru Points: 323518 More actions · Answer 4

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mw_sql_developer SSCoach Points: 19445 More actions · Answer 5

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 ?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

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

mw_sql_developer SSCoach Points: 19445 More actions · Answer 7

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.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 8

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 )

mw_sql_developer SSCoach Points: 19445 More actions · Answer 9

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.

Thom A SSC Guru Points: 99130 More actions · Answer 10

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 11

John Mitchell-245523

SSC Guru

Points: 148809

October 10, 2016 at 10:17 am

#1905705

A UNC path starts with a double backslash.

John

mw_sql_developer SSCoach Points: 19445 More actions · Answer 12

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

Lowell SSC Guru Points: 323518 More actions · Answer 13

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!