September 11, 2018 at 5:19 pm
Here is what I think it should be since the @score is the last columnSelect *
From #Event_Total
REPLACE(@score, CHAR(13)+CHAR(10), ;'');
FOR JSON PATH, Include_Null_Values;
gives me Msg 102, Level 15, State 1, Line 109
Incorrect syntax near '@score'.
I triedREPLACE('Judge2Score', CHAR(13)+CHAR(10), ;'');
September 11, 2018 at 11:54 pm
This is what I have but I am still getting an error. I am slowly understanding it.
Msg 208, Level 16, State 1, Line 106
Invalid object name 'dbo.Event_Total'.
SELECT *
INTO dbo.Event_Total
FROM #Event_Total
ORDER BY CompNum
SELECT REPLACE (REPLACE ( [Type], CHAR(13) , '' ) , CHAR(10) , ', ' ) FROM #Event_Total
GO
Select *
From #Event_Total
FOR JSON PATH, Include_Null_Values;
September 12, 2018 at 10:34 am
I was able to execute the code column by column as I have not figured how to include all column in one statement. It is still not removing the characters though.
SELECT *
INTO dbo.Event_Total
FROM #Event_Total
ORDER BY CompNum
SELECT REPLACE (REPLACE ( Judge1Score, CHAR(13) , '' ) , CHAR(10) , '' ) FROM #Event_Total
GO
SELECT REPLACE (REPLACE ( Judge2Score, CHAR(13) , '' ) , CHAR(10) , '' ) FROM #Event_Total
GO
SELECT REPLACE (REPLACE ( BScore, CHAR(13) , '' ) , CHAR(10) , '' ) FROM #Event_Total
GO
SELECT REPLACE (REPLACE ( AvgScore, CHAR(13) , '' ) , CHAR(10) , '' ) FROM #Event_Total
GO
SELECT REPLACE (REPLACE ( [Order], CHAR(13) , '' ) , CHAR(10) , '' ) FROM #Event_Total
GO
SELECT REPLACE (REPLACE ( [Rank], CHAR(13) , '' ) , CHAR(10) , '' ) FROM #Event_Total
GO
SELECT REPLACE (REPLACE ( [Event], CHAR(13) , '' ) , CHAR(10) , '' ) FROM #Event_Total
GO
SELECT REPLACE (REPLACE ( AG, CHAR(13) , '' ) , CHAR(10) , '' ) FROM #Event_Total
GO
SELECT REPLACE (REPLACE ( FirstName, CHAR(13) , '' ) , CHAR(10) , '' ) FROM #Event_Total
GO
SELECT REPLACE (REPLACE ( LastName, CHAR(13) , '' ) , CHAR(10) , '' ) FROM #Event_Total
GO
SELECT REPLACE (REPLACE ( CompNum, CHAR(13) , '' ) , CHAR(10) , '' ) FROM #Event_Total
GO
SELECT REPLACE (REPLACE ( [Type], CHAR(13) , '' ) , CHAR(10) , '' ) FROM #Event_Total
GO
Select *
From #Event_Total
FOR JSON PATH, Include_Null_Values;
September 12, 2018 at 10:58 am
So what it is doing is inserting the line break every 2033 characters. I am trying some work arrounds but no solution yet.
September 12, 2018 at 11:21 am
I tried this but no change. I am running out of options. UPDATE #Event_Total SET Judge1Score = REPLACE(Judge1Score,CHAR(13),'')
UPDATE #Event_Total SET Judge2Score = REPLACE(Judge2Score,CHAR(13),'')
UPDATE #Event_Total SET BScore = REPLACE(BScore,CHAR(13),'')
UPDATE #Event_Total SET AvgScore = REPLACE(AvgScore,CHAR(13),'')
UPDATE #Event_Total SET [Order] = REPLACE([Order],CHAR(13),'')
UPDATE #Event_Total SET [Rank] = REPLACE([Rank],CHAR(13),'')
UPDATE #Event_Total SET [Event] = REPLACE([Event],CHAR(13),'')
UPDATE #Event_Total SET AG = REPLACE(AG,CHAR(13),'')
UPDATE #Event_Total SET FirstName = REPLACE(FirstName,CHAR(13),'')
UPDATE #Event_Total SET LastName = REPLACE(LastName,CHAR(13),'')
UPDATE #Event_Total SET CompNum = REPLACE(CompNum,CHAR(13),'')
UPDATE #Event_Total SET [Type] = REPLACE([Type],CHAR(13),'')
I think because it is adding after every 2033 characters it is not in any column. So I need a function that replaces the character after the file is created like a find/replace in excel.
September 12, 2018 at 12:36 pm
Do the replace after converting to JSON, i.e.:
Select
Replace(
Replace(
(Select * from #Event_Total for json path),
Char(10),
''),
Char(13),
''
)
Although I don't think SQL Server is putting line breaks in like this, are you sure it isn't something you're doing after generating the data in SQL Server?
September 12, 2018 at 1:31 pm
That worked but now it is in one line and it gets cut off after the max 8192 char count for a column. The JSON output is the last code. I found online someone that had the same issue but could not relate their fix to my code.
From an article https://stackoverflow.com/questions/50892792/how-to-output-json-file-without-carriage-return-in-column-name-generating-json-w
There is a problem in JSON file generated by bcp in a stored procedure.
Column names have a carriage return. So web server does not understand the JSON.
Like this:
1. "leo_profit":-0.0748},{"ID":28977,"annual_profit":-0.84,"ttp":-455.8405,
2."leo_profit":-0.0748},{"ID":28977,"annual_profit":-0.84,"ttp":-455.8405,
The first one is generated by bcp in stored procedure of SQL Server 2017, as you see, there is a CR between column name "ann" and "ual_profit". annual_profit is column name.
Second one is JSON format that web server can understand.
How to make bcp in stored procedure generate without any CR between a column name?
DECLARE @command VARCHAR(1000)
SET @command = 'BCP "EXEC mdrcoin.dbo.SP_Get_ANNUAL_PROFIT_FOR_JSON " queryout "D:\inetpub\json\SP_Get_ANNUAL_PROFIT.json" -c -T -t "|" 'EXEC xp_cmdshell @command
bcp is running in stored procedure shown above.
September 12, 2018 at 1:33 pm
andycadley - Wednesday, September 12, 2018 12:36 PMDo the replace after converting to JSON, i.e.:
Select
Replace(
Replace(
(Select * from #Event_Total for json path),
Char(10),
''),
Char(13),
''
)Although I don't think SQL Server is putting line breaks in like this, are you sure it isn't something you're doing after generating the data in SQL Server?
SELECT *
INTO dbo.Event_Total
FROM #Event_Total
ORDER BY CompNum
Select
Replace(
Replace(
(Select * from #Event_Total for json path),
Char(10),
''),
Char(13),
''
)
September 13, 2018 at 1:06 pm
Ok, I am getting somewhere. I will post the whole solution but I am having trouble in one spot. I am sorting on the Avgscore column and since it is varchar(100) is sorts on the first position and not the whole number. When I try to convert I get a error somewhere down the line.
Msg 245, Level 16, State 1, Line 64
Conversion failed when converting the nvarchar value '9.200' to data type int.
SELECT * FROM dbo.Event_Total
WHERE AG='JRA' AND EVENT='AA' ORDER BY convert(int,AvgScore) DESC
FOR JSON AUTO, INCLUDE_NULL_VALUES;
If change Avgscore from varchar to integer in the declaration I get the same error.
PL.P.value('@order', 'varchar(100)') As [Order],
SC.S.value('@avgscore', 'integer' ) As AvgScore,
SC.S.value('@bscore', 'varchar(100)') As BScore,
JS1.S.value('@score', 'varchar(100)') As Judge1Score,
September 13, 2018 at 1:48 pm
9.2 isn't an int, use a suitable decimal or float type for an average.
September 13, 2018 at 2:13 pm
andycadley - Thursday, September 13, 2018 1:48 PM9.2 isn't an int, use a suitable decimal or float type for an average.
I feel embarrassed:laugh:
September 13, 2018 at 2:16 pm
TheFirstOne - Thursday, September 13, 2018 2:13 PMandycadley - Thursday, September 13, 2018 1:48 PM9.2 isn't an int, use a suitable decimal or float type for an average.I feel embarrassed:laugh:
Worked like a charm!!
September 13, 2018 at 2:26 pm
Ok so here is the meat and potatoes. I have 4 tables which are Athletes, Scores, Places and Event_Total. I am using Event Total table as it has all the info plus extra.
SELECT *
INTO dbo.Event_Total
FROM #Event_Total
ORDER BY [Event]
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=500)
BEGIN
SELECT * FROM dbo.Event_Total
WHERE AG='CHILD' AND EVENT='1' ORDER BY AvgScore DESC
FOR JSON AUTO, INCLUDE_NULL_VALUES;
SELECT * FROM dbo.Event_Total
WHERE AG='CHILD' AND EVENT='2' ORDER BY AvgScore DESC
FOR JSON AUTO, INCLUDE_NULL_VALUES;
SELECT * FROM dbo.Event_Total
WHERE AG='CHILD' AND EVENT='3' ORDER BY AvgScore DESC
FOR JSON AUTO, INCLUDE_NULL_VALUES;
SELECT * FROM dbo.Event_Total
WHERE AG='CHILD' AND EVENT='4' ORDER BY AvgScore DESC
FOR JSON AUTO, INCLUDE_NULL_VALUES;
SELECT * FROM dbo.Event_Total
WHERE AG='CHILD' AND EVENT='AA' ORDER BY AvgScore DESC
FOR JSON AUTO, INCLUDE_NULL_VALUES;
SET @intFlag = @intFlag + 1
IF @intFlag = 500
BREAK;
END
GO
Now this is just for the CHILD age group and I need to add JRA/JRB/JRC/JRD/SRA/SRB/SRC etc, about 14 or so query's. So my question now is what program do I need to make this an executable and call the loop with a button. I used to write in VBA in the 90s but I am way behind the times. I need the database to write the JSON file to disk and the only examples I have seen are .txt, .csv etc. or do I just output to text with the correct file extension?
September 14, 2018 at 10:28 am
I changed it to get 3 decimal places, SC.S.value('@avgscore', 'decimal(10,3)' ) As AvgScore,
One thing I noticed is the Event_Total table has extra data in it so I am thinking I made a mistake creating it. I am assuming I should join the columns I need into a new table from the original 3. At the minimum I need CompNum/Firstname/Lastname/AG/Event/Avgscore/Judge1score/Judge2score
Athletes
Scores
Places
-- Event_Total
Select
M.A.value('@Type','varchar(10)') As [Type],
M.A.value('@CompNum','varchar(10)') As CompNum,
M.A.value('@LastName','varchar(50)') As LastName,
M.A.value('@FirstName','varchar(30)') As FirstName,
M.A.value('@AG','varchar(10)') As AG,
PL.P.value('@event', 'varchar(10)') As [Event],
PL.P.value('@rank', 'varchar(10)') As [Rank],
PL.P.value('@order', 'varchar(10)') As [Order],
SC.S.value('@avgscore', 'decimal(10,3)' ) As AvgScore,
SC.S.value('@bscore', 'varchar(10)') As BScore,
JS1.S.value('@score', 'varchar(10)') As Judge1Score,
JS2.S.value('@score', 'varchar(10)') As Judge2Score
Into #Event_Total
From @test-2 T
Cross Apply T.ScoresXML.nodes('/MeetResultsMsg/AthleteResults/Athlete') M(A)
Cross Apply M.A.nodes('Scores/Score') SC(S)
Cross Apply M.A.nodes('Places/Place') PL(P)
Outer Apply SC.S.nodes('JudgeScore[@id=1]') JS1(S)
Outer Apply SC.S.nodes('JudgeScore[@id=2]') JS2(S)
September 14, 2018 at 11:24 am
Instead of joining two tables data with a JOIN statement I just added the firstname, lastname and AG to the scores table. Would that be the best way?
Viewing 15 posts - 61 through 75 (of 127 total)
You must be logged in to reply to this topic. Login to reply