September 14, 2018 at 3:21 pm
So I am trying to output the query to a .txt(.json) file and am really having a hard time. I have used BCP, xp_cmdshell and various others. They all seem to want to export the table and not the output. This is the best I have come up with but it has errors. use meetscoreslive
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @FileName = REPLACE('c:\Proscore5\results\child_event1_'+CONVERT(char(8),GETDATE(),1)+'.json','/','-')
SET @bcpCommand = 'bcp "SELECT * FROM meetscoreslive.Scores ORDER BY AG" queryout -T"'
SET @bcpCommand = @bcpCommand + @FileName + '" -T -c'
EXEC master..xp_cmdshell @bcpCommand
The error
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to open BCP host data-file
NULL
September 14, 2018 at 3:52 pm
TheFirstOne - Friday, September 14, 2018 3:21 PMSo I am trying to output the query to a .txt(.json) file and am really having a hard time. I have used BCP, xp_cmdshell and various others. They all seem to want to export the table and not the output. This is the best I have come up with but it has errors.use meetscoreslive
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)SET @FileName = REPLACE('c:\Proscore5\results\child_event1_'+CONVERT(char(8),GETDATE(),1)+'.json','/','-')
SET @bcpCommand = 'bcp "SELECT * FROM meetscoreslive.Scores ORDER BY AG" queryout -T"'
SET @bcpCommand = @bcpCommand + @FileName + '" -T -c'EXEC master..xp_cmdshell @bcpCommand
The error
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to open BCP host data-file
NULL
What is the best way to output the results to a .txt fileSELECT * FROM dbo.Scores
WHERE AG='CHILD' AND EVENT='1' ORDER BY AvgScore DESC
FOR JSON AUTO, INCLUDE_NULL_VALUES;
September 22, 2018 at 11:38 am
Can someone recommend the best way to output my query's to a .json(txt) file. There are the ways I have been reading about. My project is a simple database which imports xml files which then creates JSON outputs depending on the search queries. These queries will be in a loop controlled by some program (VB etc, any recommendations). My output needs to be .json(txt) files which our app will read. Everything will be run on a local laptop. I am using 1027 SQL Express because of cost and simplicity. Thank you in advanced.
By Daniel Calbimonte, 2017/10/06 (first published: 2016/10/26)
Introduction
This article will show eight ways to export rows from a T-SQL query to a txt file. We will show the following options:
September 22, 2018 at 1:36 pm
If you're writing a program anyway, I'd just have it call your stored procedures and write the output wherever you want it.
September 26, 2018 at 10:48 pm
I am importing the session data and am having trouble understanding. Here is the code. -- Start Place
Select
M.A.value('@Type','varchar(10)') As [Type],
M.A.value('@EventID','varchar(10)') As EventID,
M.A.value('@Session','varchar(10)') As [Session],
SC.S.value('@CompNum','varchar(10)') As CompNum,
ST.S.value('@event', 'varchar(10)') As [Event],
ST.S.value('@rotation', 'varchar(10)') As Rotation,
ST.S.value('@order', 'varchar(10)') As [Order]
Into #WomenStartListDay1
From @Test T
Cross Apply T.ScoresXML.nodes('/StartListMsg') M(A)
Cross Apply M.A.nodes('/StartListMsg/AthleteList/Athlete') SC(S)
Outer Apply SC.S.nodes('/StartList/Start') ST(S)
Input File
<?xml version="1.0" encoding="ISO-8859-1"?>
<StartListMsg Type="ARTW3" EventID="" Session="1" >
<AthleteList>
<Athlete CompNum="301">
<StartList>
<Start event="1" rotation="3" order="1" />
<Start event="2" rotation="4" order="1" />
<Start event="3" rotation="1" order="1" />
<Start event="4" rotation="2" order="1" />
</StartList>
</Athlete>
Table output
Type EventID Session CompNum Event Rotation Order
---------- ---------- ---------- ---------- ---------- ---------- ----------
ARTW3 1 357 NULL NULL NULL
ARTW3 1 355 NULL NULL NULL
ARTW3 1 354 NULL NULL NULL
ARTW3 1 353 NULL NULL NULL
ARTW3 1 352 NULL NULL NULL
ARTW3 1 351 NULL NULL NULL
ARTW3 1 350 NULL NULL NULL
ARTW3 1 349 NULL NULL NULL
ARTW3 1 348 NULL NULL NULL
ARTW3 1 347 NULL NULL NULL
ARTW3 1 346 NULL NULL NULL
ARTW3 1 344 NULL NULL NULL
I am having trouble understanding the outer apply and cross apply and how the MA.A.value,ST.S.value, ST.S work?
September 27, 2018 at 12:34 am
You're resetting the XPath in your Apply statements and going back to the root of the XML (because the paths start with '/'). What you want to do is work relative to where you are in the XML - something like:
Cross Apply T.ScoresXML.nodes('/StartListMsg') M(A)
Cross Apply M.A.nodes('AthleteList/Athlete') SC(S)
Outer Apply SC.S.nodes('StartList/Start') ST(S)
This makes M.A all the StartListMsg nodes, SC.S all the Athelete nodes beneath that and finally SC.S all the Start nodes underneath that.
September 27, 2018 at 10:30 am
So is there meaning for the M.A.nodes etc or can you use what ever combo letters you want as long as the format is the same. ie X.X.nodes
September 27, 2018 at 11:30 am
The letters are just table and column aliases they could be FISH(CAT) and they'd still work fine.
September 27, 2018 at 11:49 am
andycadley - Thursday, September 27, 2018 11:30 AMThe letters are just table and column aliases they could be FISH(CAT) and they'd still work fine.
Ok, thank you
September 27, 2018 at 12:26 pm
I would probably use minimalist aliases myself, but it might make more sense to you if the element names were used for aliases. Also, the .query() function can be useful for debugging XML shredding queries to see what the .nodes() functions are returning.
SELECT StartListMsg_nodevalue = [root].StartListMsg.query('.'), -- The entire XML value, repeated 12 times
Athlete_nodevalue = AthleteList.Athlete.query('.'), -- The value of the three <Athlete> nodes, each repeating 4 times
Start_nodevalue = Athlete.[Start].query('.') -- The value of the individual <Start> nodes
FROM @scores.nodes('/StartListMsg') [root](StartListMsg)
CROSS APPLY [root].StartListMsg.nodes('AthleteList/Athlete') AthleteList(Athlete)
CROSS APPLY AthleteList.Athlete.nodes('StartList/Start') Athlete([Start]);
If it is possible to have an <Athlete> node that contains no <Start> nodes, but you still want to see the athlete in the results, you would use OUTER APPLY in the last line.
That query makes it easier to see what XML text each .values() function is parsing.
SELECT EventType = [root].StartListMsg.value('@Type', 'varchar(50)'),
EventID = [root].StartListMsg.value('@EventID', 'varchar(50)'),
[Session] = [root].StartListMsg.value('@Session', 'int'),
CompNum = AthleteList.Athlete.value('@CompNum', 'int'),
[Event] = Athlete.[Start].value('@event', 'int'),
Rotation = Athlete.[Start].value('@rotation', 'int'),
[order] = Athlete.[Start].value('@order', 'int')
FROM @scores.nodes('/StartListMsg') [root](StartListMsg)
CROSS APPLY [root].StartListMsg.nodes('AthleteList/Athlete') AthleteList(Athlete)
CROSS APPLY AthleteList.Athlete.nodes('StartList/Start') Athlete([Start]);
September 27, 2018 at 5:43 pm
NA
September 28, 2018 at 2:47 pm
TheFirstOne - Thursday, September 27, 2018 5:43 PMOk, I'll look at it . I am having a coding error I am spinning my head on. I have 3 tables and I am making one SELECT. Each table has the correct data but for some reason the AgeGrp is NULL even though it is in the table.SELECT Athletes.FirstName, ScoresLvl2.AgeGrp, ScoresLvl2.AvgScore, ScoresLvl2.BScore, ScoresLvl2.Judge1Score
--INTO ResultsLvl2
FROM Athletes, AthletesLvl2, ScoresLvl2
WHERE Athletes.CompNum = AthletesLvl2.CompNum
AND Athletes.CompNum = ScoresLvl2.CompNum
ORDER BY AgeGrpFirstName CompNum AgeGrp AvgScore BScore Judge1Score
------------------------------ ----------- ---------- --------------------------------------- ---------- -----------
Lucie 204 NULL 7.850 7.850 7.850
Lucie 204 NULL 8.900 8.900 8.900
Lucie 204 NULL 8.000 8.100 8.100
Lucie 204 NULL 8.500 8.500 8.500
Lucie 204 NULL 33.250 NULL NULL
Maddie 205 NULL 7.700 7.700 7.700
Maddie 205 NULL 0.000 0.000 0.000
Maddie 205 NULL 8.700 8.800 8.800
Maddie 205 NULL 8.250 8.250 8.250
Maddie 205 NULL 24.650 NULL NULL[dbo].[Athletes]
[dbo].[AthletesLvl2][dbo].[ScoresLvl2]
Cruz Kayla 4014 CHILD 1 9.350 9.350 9.350 Cruz Kayla 4014 CHILD 2 9.350 9.350 9.350 Cruz Kayla 4014 CHILD 3 9.450 9.450 9.450 Cruz Kayla 4014 CHILD 4 9.500 9.500 9.500 Cruz Kayla 4014 CHILD AA 37.650 NULL NULL Cruz Kayla 4014 CHILD 1 9.200 9.200 9.200 Cruz Kayla 4014 CHILD 2 9.550 9.550 9.550 Cruz Kayla 4014 CHILD 3 9.650 9.650 9.650
FIXED!!
October 2, 2018 at 2:08 pm
I have now started to insert the working queries into VS 2017. It is going well but I am having a hard time with one aspect. I am not sure if this is the right forum so if not could someone point me in the right direction. I am checking to see if a table exists in the database and every example I have seen does not work. Here is what I am trying to do. I need to check for the existing table and if exist use the message box to execute some query code.
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim SQLCONN As New SqlClient.SqlConnection
Dim SQLCMD As New SqlClient.SqlCommand
Dim result As Integer = MessageBox.Show("Table exists", "Click Yes to Delete, No to use Table or Cancel to exit", MessageBoxButtons.YesNoCancel)
If result = DialogResult.Cancel Then
MessageBox.Show("Cancel pressed")
Exit Sub
ElseIf result = DialogResult.No Then
Exit Sub
ElseIf result = DialogResult.Yes Then
SQLCONN.ConnectionString = "Server=dell-laptop\sqlexpress01;Database=meetscoreslive;Integrated security=True"
SQLCONN.Open()
SQLCMD.Connection = SQLCONN
SQLCMD.CommandType = CommandType.Text
SQLCMD.CommandText = "DROP TABLE WomenStartListDay1;"
SQLCMD.ExecuteNonQuery()
SQLCONN.Close()
End If
SQLCONN.ConnectionString = "Server=dell-laptop\sqlexpress01;Database=meetscoreslive;Integrated security=True"
SQLCONN.Open()
SQLCMD.Connection = SQLCONN
SQLCMD.CommandType = CommandType.Text
SQLCMD.CommandText = "-- Temp tables for example, these would be real tables you INSERT INTO
IF OBJECT_ID('tempdb..#WomenStartListDay1') IS NOT NULL
DROP TABLE #WomenStartListDay1;
SET NOCOUNT ON
Declare @JSON varchar(MAX) = '';
DECLARE @Test TABLE (ScoresID int, ScoresXML xml);
INSERT INTO @Test(ScoresXML)
SELECT * FROM OPENROWSET(
BULK 'C:\proscore5\WomenStartListDay1.xml',
SINGLE_BLOB) AS x;
-- Start Place
Select
M.A.value('@Type','varchar(10)') As [Type],
M.A.value('@EventID','varchar(10)') As EventID,
M.A.value('@Session','varchar(10)') As [Session],
SC.S.value('@CompNum','varchar(10)') As CompNum,
ST.S.value('@event', 'varchar(10)') As [Event],
ST.S.value('@rotation', 'varchar(10)') As Rotation,
ST.S.value('@order', 'varchar(10)') As [Order]
Into #WomenStartListDay1
From @Test T
Cross Apply T.ScoresXML.nodes('/StartListMsg') M(A)
Cross Apply M.A.nodes('AthleteList/Athlete') SC(S)
Outer Apply SC.S.nodes('StartList/Start') ST(S)
-- Move data from temp tables to real tables
SELECT *
INTO dbo.WomenStartListDay1
FROM #WomenStartListDay1 ORDER BY CompNum;"
SQLCMD.ExecuteNonQuery()
SQLCONN.Close()
End Sub
October 3, 2018 at 2:23 pm
Within SQL Server, you can run a query to check for the existence of a table:IF OBJECT_ID(N'dbo.SomeTable', N'U')
BEGIN
PRINT 'Starting T-SQL actions...'
--Your T-SQL actions go here
END;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 4, 2018 at 11:31 am
I am getting this error. Also I wanted to check for the table and then ask the user if they want to delete it or not. That is why I thought it would be best to check for the table in VS?System.Data.SqlClient.SqlException: 'An expression of non-boolean type specified in a context where a condition is expected, near 'BEGIN'.'
CodePrivate Sub ButtonWomenStartListDay1_Click(sender As Object, e As EventArgs) Handles ButtonWomenStartListDay1.Click
Dim SQLCONN As New SqlClient.SqlConnection
Dim SQLCMD As New SqlClient.SqlCommand
SQLCONN.ConnectionString = "Server=dell-laptop\sqlexpress01;Database=meetscoreslive;Integrated security=True"
SQLCONN.Open()
SQLCMD.Connection = SQLCONN
SQLCMD.CommandType = CommandType.Text
SQLCMD.CommandText = "IF OBJECT_ID(N'dbo.WomenStartListDay1', N'U')
BEGIN
PRINT 'Starting T-SQL actions...'
--Your T-SQL actions go here
END;"
SQLCMD.ExecuteNonQuery()
SQLCONN.Close()
Dim result As Integer = MessageBox.Show("Check For Existing Data", "Click Yes to Check, No to use Table or Cancel to exit", MessageBoxButtons.YesNoCancel)
If result = DialogResult.Cancel Then
MessageBox.Show("Cancel pressed")
Exit Sub
ElseIf result = DialogResult.No Then
Exit Sub
ElseIf result = DialogResult.Yes Then
SQLCONN.ConnectionString = "Server=dell-laptop\sqlexpress01;Database=meetscoreslive;Integrated security=True"
SQLCONN.Open()
SQLCMD.Connection = SQLCONN
SQLCMD.CommandType = CommandType.Text
SQLCMD.CommandText = "DROP TABLE WomenStartListDay1;"
SQLCMD.ExecuteNonQuery()
SQLCONN.Close()
End If
Viewing 15 posts - 76 through 90 (of 128 total)
You must be logged in to reply to this topic. Login to reply