SQL Server -- Error = [Microsoft] [SQL Server Native Client 10.0][SQL Server] Invalid object name

Question

Context: a feature of our core application depends on an output file from a stored procedure in SQL Server. The file is supposed to be output via the BCP utility.

Question: errors related to an invalid object name seem to be the culprit. The procedure is generating the errors below. I'm very unclear as to how/why the global temp table referenced in the procedure is using an invalid name. I tried replacing the temp tables with standard tables which doesn't fix the problem. Can someone please offer some tips/tricks?

Stored Procedure:

IF OBJECT_ID('tempdb..##jsondump_personnel') IS NOT NULL 
     DROP TABLE ##jsondump_personnel
IF OBJECT_ID('tempdb..##jsonparsed_personnel') IS NOT NULL 
     DROP TABLE ##jsonparsed_personnel
IF OBJECT_ID('tempdb..##json_loop_personnel') IS NOT NULL 
     DROP TABLE ##json_loop_personnel

CREATE TABLE ##jsondump_personnel
(
    [my_json] [NVARCHAR](MAX) NULL
) 

CREATE TABLE ##jsonparsed_personnel 
(
    [name] [VARCHAR](255) NULL,
    title [VARCHAR](255) NULL,
    [path] [VARCHAR](255) NULL
)

TRUNCATE TABLE ##jsondump_personnel;
TRUNCATE TABLE ##jsonparsed_personnel;

BULK INSERT ##jsondump_personnel
FROM 'C:\mattermark_etl_project\personnel_data.json'
WITH (
    ROWTERMINATOR = '\n'
);

SELECT my_json 
INTO ##json_loop_personnel
FROM ##jsondump_personnel;

--SELECT * FROM ##jsondump;

INSERT INTO ##jsonparsed_personnel ([name], title, [path])
    SELECT DISTINCT
        jsn.[name], jsn.[title], jsn.[path]
    FROM 
        ##json_loop_personnel
    OUTER APPLY 
        (SELECT * 
         FROM OPENJSON(##json_loop_personnel.my_json)
         WITH (
               [name] VARCHAR(255) '$.name',
               title VARCHAR(255) '$.title',
               [path] VARCHAR(255) '$.path'
              )) AS jsn

DECLARE @bcp_cmd4 VARCHAR(1000);
DECLARE @exe_path4 VARCHAR(200) = 
    ' cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn\ & ';

SET @bcp_cmd4 =  @exe_path4 + 
    ' BCP.EXE "SELECT ''Name'', ''Title'', ''Path'' UNION ALL SELECT DISTINCT name, title, path FROM ##jsonparsed_personnel" queryout ' +
    ' "C:\mattermark_etl_project\personnel_data.txt" -T -c -q -r\n';

PRINT @bcp_cmd4;
EXEC master..xp_cmdshell @bcp_cmd4

SELECT DISTINCT * 
FROM ##jsonparsed_personnel

DROP TABLE ##jsondump_personnel
DROP TABLE ##jsonparsed_personnel 
DROP TABLE ##json_loop_personnel 

EXEC xp_cmdshell 'C:\mattermark_etl_project\powershell "C:\mattermark_etl_project\open_personnel_file.ps1"'

Errors:

SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name '##jsonparsed_personnel'

SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

Can you identify which line contianing `##jsonparsed_personnel` triggers the error? Does the script work if you run it directly from SSMS instead of invoking a 'sproc? — vonPryz, Aug 01 '19 at 06:11
Its this line: ' BCP.EXE "SELECT ''Name'', ''Title'', ''Path'' UNION ALL SELECT DISTINCT name, title, path FROM ##jsonparsed_personnel" queryout ' + ' "C:\mattermark_etl_project\personnel_data.txt" -T -c -q -r\n'; The powershell script doesnt work because the output file isnt being created. — emalcolmb, Aug 01 '19 at 11:37
Learn to debug your dynamic sql. Post the actual content of that string in your question, not as a comment. Then take all the code that generates those global temp tables (up until the attempt to use bcp) and run it in a query window in ssms. Then take that dynamic bcp command and run it in a command window on the machine that is executing this code. Does that work? You don't supply an instance name to your bcp command - so which instance does it use and which instance has these global temp tables? — SMor, Aug 01 '19 at 12:08
@SMor I'd hazard a guess it's what's assigned as `@bcp_cmd4`. Missing server/instance parameter `-S` from `bcp` sure can be the culprit. — vonPryz, Aug 01 '19 at 12:14
Apologies, I'm not an expert with dynamic sql. Not sure what you mean by "content of the string" -- the results of the dynamic sql query? And are you asking if the global temp tables are created on the same server instance thats executing the BCP utility? Yes the commands are in a stored proc on the same machine so yeah its the same instance & spid. I can try to supply the -S parameter and see if that helps. — emalcolmb, Aug 01 '19 at 12:34

score 0 · Accepted Answer · answered Aug 01 '19 at 23:24

I found the problem can be solved by simplifying block a to block b:

Block a

DECLARE @bcp_cmd4 VARCHAR(1000);
DECLARE @exe_path4 VARCHAR(200) = 
    ' cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn\ & ';

SET @bcp_cmd4 =  @exe_path4 + 
    ' BCP.EXE "SELECT ''Name'', ''Title'', ''Path'' UNION ALL SELECT DISTINCT name, title, path FROM ##jsonparsed_personnel" queryout ' +
    ' "C:\mattermark_etl_project\personnel_data.txt" -T -c -q -r\n';

PRINT @bcp_cmd4;
EXEC master..xp_cmdshell @bcp_cmd4

Block b

--https://sqlwithmanoj.com/tag/bcp-queryout/
exec master..xp_cmdshell 'BCP "SELECT ''Name'', ''Title'', ''Path'' UNION ALL SELECT DISTINCT name, title, path FROM mattermark_sandbox.dbo.jsonparsed_personnel" QUERYOUT C:\mattermark_etl_project\personnel_data.txt -T -c'

You might also want to quote the folder path in `' cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn\ & '` as `' cd "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\" & '` — AlwaysLearning, Aug 01 '19 at 23:49

SQL Server -- Error = [Microsoft] [SQL Server Native Client 10.0][SQL Server] Invalid object name

1 Answers1