Importing from XML to SQL 2014

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  October 4, 2018 at 11:44 am

  #2008161

  TheFirstOne - Thursday, October 4, 2018 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'.'

  Code
  Private 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

  Not sure about this, but I think your code can't be multi-line without closing the quotes on each additional line and reopening them on the next, and the multiple lines may want to have VbCrLf added to each one, in addition to having to provide an _ or some such to indicate that the line of code continues on to the next line.  The ampersand symbol is used to concatenate strings in VB.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• TheFirstOne

  SSCommitted

  Points: 1659

  More actions

  October 4, 2018 at 12:30 pm

  #2008177

  Here is my work around. The only issue is the print statement does not translate to VS. I am not sure how to get this to work. So basically you can choose to delete the old data, use the old data or cancel which would use the old data. I need to use the print function to let the use know there is no table. The previous error was I did not have a ; after BEGIN I think.

  Private Sub ButtonWomenStartListDay1_Click(sender As Object, e As EventArgs) Handles ButtonWomenStartListDay1.Click
   Dim SQLCONN As New SqlClient.SqlConnection
   Dim SQLCMD As New SqlClient.SqlCommand

     Dim result As Integer = MessageBox.Show("Delete Old Data", "Yes, No or Cancel to exit", MessageBoxButtons.YesNoCancel)
   If result = DialogResult.Cancel Then
    MessageBox.Show("Cancel pressed")
    Exit Sub
   ElseIf result = DialogResult.No Then
    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('dbo.WomenStartListDay1') IS NULL
    BEGIN;
      Print N'Data does not exist'
    END;"
    SQLCMD.ExecuteNonQuery()
    SQLCONN.Close()
    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 = "If OBJECT_ID('dbo.WomenStartListDay1') IS NOT NULL
      DROP TABLE dbo.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-2 TABLE (ScoresID int, ScoresXML xml);
    INSERT INTO @test-2(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-2 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

• drew.allen

  SSC Guru

  Points: 76996

  More actions

  October 4, 2018 at 12:37 pm

  #2008179

  The problem appears to be here:
      SQLCMD.CommandText = "IF OBJECT_ID(N'dbo.WomenStartListDay1', N'U')

  IF requires a conditional expression and OBJECT_ID(...) does not return a conditional expression

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  October 4, 2018 at 1:03 pm

  #2008194

  If you want to operate on a result set, you can run a query that can return that result:
  SELECT
    CONVERT(bit,
        CASE
            WHEN OBJECT_ID('dbo.WomenStartListDay1', N'U') IS NOT NULL THEN 1
            ELSE 0
        END) AS TableExists;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• TheFirstOne

  SSCommitted

  Points: 1659

  More actions

  October 4, 2018 at 10:33 pm

  #2008250

  sgmunson - Thursday, October 4, 2018 1:03 PM

  If you want to operate on a result set, you can run a query that can return that result:
  SELECT
    CONVERT(bit,
        CASE
            WHEN OBJECT_ID('dbo.WomenStartListDay1', N'U') IS NOT NULL THEN 1
            ELSE 0
        END) AS TableExists;

  I was thinking about that option, will check it out tomorrow. Thanks

• TheFirstOne

  SSCommitted

  Points: 1659

  More actions

  October 4, 2018 at 10:53 pm

  #2008251

  TheFirstOne - Thursday, October 4, 2018 10:33 PM

  sgmunson - Thursday, October 4, 2018 1:03 PM

  If you want to operate on a result set, you can run a query that can return that result:
  SELECT
    CONVERT(bit,
        CASE
            WHEN OBJECT_ID('dbo.WomenStartListDay1', N'U') IS NOT NULL THEN 1
            ELSE 0
        END) AS TableExists;

  I was thinking about that option, will check it out tomorrow. Thanks

  How do I use the value in a VB message?

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  October 9, 2018 at 7:00 am

  #2008661

  TheFirstOne - Thursday, October 4, 2018 10:53 PM

  TheFirstOne - Thursday, October 4, 2018 10:33 PM

  sgmunson - Thursday, October 4, 2018 1:03 PM

  If you want to operate on a result set, you can run a query that can return that result:
  SELECT
    CONVERT(bit,
        CASE
            WHEN OBJECT_ID('dbo.WomenStartListDay1', N'U') IS NOT NULL THEN 1
            ELSE 0
        END) AS TableExists;

  I was thinking about that option, will check it out tomorrow. Thanks

  How do I use the value in a VB message?

  What do you mean by "the value in a VB message" ?   Messages that are error-driven don't have an inherent value.   Only the error itself has a numeric value.  There is an error trapping mechanism activated by an ON ERROR GOTO statement, so that you can actually trap errors as they occur.   You also have to code the target label with error-handling code that can handle any error that occurs in the area of code so protected.   I can't cover that entire topic in a forum post, so do please search for VB error handling and read up on the topic.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• TheFirstOne

  SSCommitted

  Points: 1659

  More actions

  October 9, 2018 at 10:55 am

  #2008714

  sgmunson - Tuesday, October 9, 2018 7:00 AM

  TheFirstOne - Thursday, October 4, 2018 10:53 PM

  TheFirstOne - Thursday, October 4, 2018 10:33 PM

  sgmunson - Thursday, October 4, 2018 1:03 PM

  If you want to operate on a result set, you can run a query that can return that result:
  SELECT
    CONVERT(bit,
        CASE
            WHEN OBJECT_ID('dbo.WomenStartListDay1', N'U') IS NOT NULL THEN 1
            ELSE 0
        END) AS TableExists;

  I was thinking about that option, will check it out tomorrow. Thanks

  How do I use the value in a VB message?

  What do you mean by "the value in a VB message" ?   Messages that are error-driven don't have an inherent value.   Only the error itself has a numeric value.  There is an error trapping mechanism activated by an ON ERROR GOTO statement, so that you can actually trap errors as they occur.   You also have to code the target label with error-handling code that can handle any error that occurs in the area of code so protected.   I can't cover that entire topic in a forum post, so do please search for VB error handling and read up on the topic.

  I need to pass the SQL value in your last solution (1 or 0) to visual studio so I can tell the user if a table exists. I want to us the VS message function to tell a user if the table exist and they can either use the file, generate a new one or cancel.

  I could not find any VS code that would directly validate if a table exists so I need to pass the SQL validation.

  Visual Studio Value (x) = SQL Value (y)

  IF x = 1 Athletes table exists.
  else import new Athletes

  When the user clicks on the import athletes button a message box will appear if the table already exists and ask if they want to keep it or delete it,  else a new table will be imported.

• TheFirstOne

  SSCommitted

  Points: 1659

  More actions

  October 9, 2018 at 1:35 pm

  #2008733

  I am getting so close. I am using the cursor function to loop through the age groups to output the different results. Everything works using the print function and the JSON output. All I need to do is output the results to a txt(JSON) file. This has been eluding me for the whole project and any incite would be appreciated.

  USE meetscoreslive
--Using Cursor
DECLARE @MyCursor CURSOR;

  --sample variables to hold each row's content
DECLARE @ItemID int;
DECLARE @ItemAgeGrp varchar(255);

  BEGIN
  SET @MyCursor = CURSOR FOR
  select top 10
  ID, AgeGrp
  from dbo.AgeGrpLvl3

    OPEN @MyCursor
  FETCH NEXT FROM @MyCursor
  INTO @ItemID, @ItemAgeGrp

    WHILE @@FETCH_STATUS = 0
  BEGIN
 
SELECT * FROM dbo.ResultsLvl3
WHERE AgeGrp = @ItemAgeGrp AND EVENT='1' ORDER BY AvgScore DESC
--FOR JSON AUTO, INCLUDE_NULL_VALUES;

  SELECT * FROM dbo.ResultsLvl3
WHERE AgeGrp = @ItemAgeGrp AND EVENT='2' ORDER BY AvgScore DESC
--FOR JSON AUTO, INCLUDE_NULL_VALUES;

  SELECT * FROM dbo.ResultsLvl3
WHERE AgeGrp = @ItemAgeGrp AND EVENT='3' ORDER BY AvgScore DESC
--FOR JSON AUTO, INCLUDE_NULL_VALUES;

  SELECT * FROM dbo.ResultsLvl3
WHERE AgeGrp = @ItemAgeGrp AND EVENT='4' ORDER BY AvgScore DESC
--FOR JSON AUTO, INCLUDE_NULL_VALUES;

  SELECT * FROM dbo.ResultsLvl3
WHERE AgeGrp = @ItemAgeGrp AND EVENT='AA' ORDER BY AvgScore DESC
--FOR JSON AUTO, INCLUDE_NULL_VALUES;

  print
 'ItemID=' + Cast(@ItemID as varchar(255)) +
 ', ItemCode=' + @ItemAgeGrp  
FETCH NEXT FROM @MyCursor
INTO @ItemID, @ItemAgeGrp  

    END;

    CLOSE @MyCursor ;
  DEALLOCATE @MyCursor;
END;

• TheFirstOne

  SSCommitted

  Points: 1659

  More actions

  October 9, 2018 at 1:37 pm

  #2008734

  TheFirstOne - Tuesday, October 9, 2018 1:35 PM

  I am getting so close. I am using the cursor function to loop through the age groups to output the different results. Everything works using the print function and the JSON output. All I need to do is output the results to a txt(JSON) file. This has been eluding me for the whole project and any incite would be appreciated.

  USE meetscoreslive
--Using Cursor
DECLARE @MyCursor CURSOR;

  --sample variables to hold each row's content
DECLARE @ItemID int;
DECLARE @ItemAgeGrp varchar(255);

  BEGIN
  SET @MyCursor = CURSOR FOR
  select top 10
  ID, AgeGrp
  from dbo.AgeGrpLvl3

    OPEN @MyCursor
  FETCH NEXT FROM @MyCursor
  INTO @ItemID, @ItemAgeGrp

    WHILE @@FETCH_STATUS = 0
  BEGIN
 
SELECT * FROM dbo.ResultsLvl3
WHERE AgeGrp = @ItemAgeGrp AND EVENT='1' ORDER BY AvgScore DESC
--FOR JSON AUTO, INCLUDE_NULL_VALUES;

  SELECT * FROM dbo.ResultsLvl3
WHERE AgeGrp = @ItemAgeGrp AND EVENT='2' ORDER BY AvgScore DESC
--FOR JSON AUTO, INCLUDE_NULL_VALUES;

  SELECT * FROM dbo.ResultsLvl3
WHERE AgeGrp = @ItemAgeGrp AND EVENT='3' ORDER BY AvgScore DESC
--FOR JSON AUTO, INCLUDE_NULL_VALUES;

  SELECT * FROM dbo.ResultsLvl3
WHERE AgeGrp = @ItemAgeGrp AND EVENT='4' ORDER BY AvgScore DESC
--FOR JSON AUTO, INCLUDE_NULL_VALUES;

  SELECT * FROM dbo.ResultsLvl3
WHERE AgeGrp = @ItemAgeGrp AND EVENT='AA' ORDER BY AvgScore DESC
--FOR JSON AUTO, INCLUDE_NULL_VALUES;

  print
 'ItemID=' + Cast(@ItemID as varchar(255)) +
 ', ItemCode=' + @ItemAgeGrp  
FETCH NEXT FROM @MyCursor
INTO @ItemID, @ItemAgeGrp  

    END;

    CLOSE @MyCursor ;
  DEALLOCATE @MyCursor;
END;

  I used this code to get the AgeGrp data and sort out the dupes and then add a ID record.
  SELECT *
   INTO dbo.AgeGrpLvl3
   FROM #AgeGrpLvl3 GROUP BY AgeGrp
   alter table AgegrpLvl3
   add ID int identity(1,1)

• TheFirstOne

  SSCommitted

  Points: 1659

  More actions

  October 10, 2018 at 12:26 pm

  #2008919

  I am using this code to try to output the query to .json. It crerate the file but errors on something.

  SELECT * FROM dbo.ResultsLvl3
WHERE AgeGrp = @ItemAgeGrp AND EVENT='1' ORDER BY AvgScore DESC
exec xp_cmdshell 'bcp "Select * FROM ResultsLvl3 for JSON AUTO" queryout c:\Proscore5\ResultsLvl3.json -T -c'

  NULL
  Starting copy...
  SQLState = 37000, NativeError = 102
  Error = [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near 'JSON'.
  SQLState = S1000, NativeError = 0
  Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to resolve column level collations
  NULL
  BCP copy out failed
  NULL

• TheFirstOne

  SSCommitted

  Points: 1659

  More actions

  October 10, 2018 at 1:17 pm

  #2008929

  Is there a way to execute a query from another query. I see that you have subqueries but I am not interested in returning the results. Instead of repeating multiple procedures in one large query how can I break it down better.

  do while
  Main Query - SqlQuery1
          Execute another query - SqlQuery2
  Loop

• TheFirstOne

  SSCommitted

  Points: 1659

  More actions

  October 11, 2018 at 3:19 pm

  #2009260

  So here is the code to get it to work about 90%. It creates the json file but with all the records and so not sorted by AgeGrp.
  USE meetscoreslive
--Using Cursor
DECLARE @MyCursor CURSOR;

  --sample variables to hold each row's content
DECLARE @ItemID int;
DECLARE @ItemAgeGrp varchar(255);
BEGIN
  SET @MyCursor = CURSOR FOR
  select all
  ID, AgeGrp
  from dbo.AgeGrpLvl3 

    OPEN @MyCursor
  FETCH NEXT FROM @MyCursor
  INTO @ItemID, @ItemAgeGrp

    WHILE @@FETCH_STATUS = 0
BEGIN

  --Event 1
declare @sql1 varchar(8000)
declare @file1 varchar(250)
declare @fileunique1 varchar(100)
Declare @Dir1 varchar (100)

  set @fileunique1 = Convert(varchar(100), Year(GETDATE()))
set @fileunique1 = @fileunique1 + Convert(varchar(100), Month(GETDATE()))
set @fileunique1 = @fileunique1 + Convert(varchar(100), Day(GETDATE()))
set @Dir1 = 'c:\Proscore5\json\'
Set @file1 = @Dir1 + @ItemAgeGrp + @fileunique1 +'.json'
select @sql1 = 'bcp ""SELECT (Select * FROM meetscoreslive.dbo.ResultsLvl3 FOR JSON AUTO)"" queryout '+ @file1 +' -c -t; -T -S' + @@servername
exec master..xp_cmdshell @sql1
print
 'ItemID=' + Cast(@ItemID as varchar(255)) +
 ', ItemCode=' + @ItemAgeGrp  
FETCH NEXT FROM @MyCursor
INTO @ItemID, @ItemAgeGrp 
END;
  CLOSE @MyCursor ;
  DEALLOCATE @MyCursor;
END;

  But when I add something other than a number it give an error. The double quotes on Select are for VS
  select @sql1 = 'bcp ""SELECT (Select * FROM meetscoreslive.dbo.ResultsLvl3 WHERE Event=1 AND AgeGrp=@ItemAgeGrp FOR JSON AUTO)""

• TheFirstOne

  SSCommitted

  Points: 1659

  More actions

  October 16, 2018 at 1:55 pm

  #2009953

  When I run this code in visual studio I get an expected end of statement on the SELECT part of the bcp utility. But If I run the same code without the SQLCONN parameters (just a basic query) it works just fine. What could the problem be?

       SQLCMD.ExecuteNonQuery()
      SQLCONN.Close()
      SQLCONN.ConnectionString = "Server=dell-laptop\sqlexpress01;Database=meetscoreslive;Integrated security=True"
      SQLCONN.Open()
      SQLCMD.Connection = SQLCONN
      SQLCMD.CommandType = CommandType.Text
      SQLCMD.CommandText = "

        --Temporarily enable xp_cmdshell
EXEC sp_configure 'show advanced options',1;
RECONFIGURE

  EXEC sp_configure 'xp_cmdshell',1;
RECONFIGURE

  --Declare parameters

  --Here you need to specify the SQL instance from which the data will be exported
DECLARE @instanceName VARCHAR(50)='.\SQL2K17'

  --Here you specify whether you are connecting to the SQL instance with a
--trusted connection (Windows Authentication) or not
DECLARE @isTrustedConnection BIT=1

  --If isTrustedConnection is set to 0 then you will need to
--add username and password for connecting to the SQL Server instance
DECLARE @userName VARCHAR(20)='dbo'
DECLARE @password VARCHAR(20)=''

  --Here you need to specify the output directory for the files to be created
DECLARE @outputDir VARCHAR(25)='c:\Proscore5\json\';

  --Using Cursor
DECLARE @MyCursor CURSOR;

  --sample variables to hold each row's content
DECLARE @ItemID int;
DECLARE @ItemAgeGrp varchar(20);

  BEGIN
  SET @MyCursor = CURSOR FOR
  select all
  ID, AgeGrp
  from dbo.AgeGrpLvl3
    

    OPEN @MyCursor
  FETCH NEXT FROM @MyCursor
  INTO @ItemID, @ItemAgeGrp

    WHILE @@FETCH_STATUS = 0
BEGIN

  --Event 1
declare @sql1 varchar(8000)
declare @file1 varchar(50)
declare @fileunique1 varchar(50)
declare @tempdata varchar(8000)

  set @fileunique1 = Convert(varchar(100), Year(GETDATE()))
set @fileunique1 = @fileunique1 + Convert(varchar(100), Month(GETDATE()))
set @fileunique1 = @fileunique1 + Convert(varchar(100), Day(GETDATE()))
Set @file1 = @outputDir + @ItemAgeGrp + @fileunique1 +'.json'

  SELECT *
INTO dbo.tempdata
FROM ResultsLvl3 WHERE AgeGrp=@ItemAgeGrp AND Event='1';

  SET @sql1 = 'bcp "Select * FROM meetscoreslive.dbo.tempdata FOR JSON AUTO" queryout '+ @file1 +' -c -t; -T -S' + @@servername

    --Execute the BCP command
  EXEC xp_cmdshell @sql1
    DROP TABLE dbo.tempdata
     
FETCH NEXT FROM @MyCursor
INTO @ItemID, @ItemAgeGrp  

  END;

    CLOSE @MyCursor ;
  DEALLOCATE @MyCursor;
END;

  --Disable xp_cmdshell
EXEC sp_configure 'xp_cmdshell',0
RECONFIGURE;

  EXEC sp_configure 'show advanced options',0
RECONFIGURE;"
      SQLCMD.ExecuteNonQuery()
      SQLCONN.Close()

• TheFirstOne

  SSCommitted

  Points: 1659

  More actions

  October 16, 2018 at 2:14 pm

  #2009957

  TheFirstOne - Tuesday, October 16, 2018 1:55 PM

  When I run this code in visual studio I get an expected end of statement on the SELECT part of the bcp utility. But If I run the same code without the SQLCONN parameters (just a basic query) it works just fine. What could the problem be?

       SQLCMD.ExecuteNonQuery()
      SQLCONN.Close()
      SQLCONN.ConnectionString = "Server=dell-laptop\sqlexpress01;Database=meetscoreslive;Integrated security=True"
      SQLCONN.Open()
      SQLCMD.Connection = SQLCONN
      SQLCMD.CommandType = CommandType.Text
      SQLCMD.CommandText = "

        --Temporarily enable xp_cmdshell
EXEC sp_configure 'show advanced options',1;
RECONFIGURE

  EXEC sp_configure 'xp_cmdshell',1;
RECONFIGURE

  --Declare parameters

  --Here you need to specify the SQL instance from which the data will be exported
DECLARE @instanceName VARCHAR(50)='.\SQL2K17'

  --Here you specify whether you are connecting to the SQL instance with a
--trusted connection (Windows Authentication) or not
DECLARE @isTrustedConnection BIT=1

  --If isTrustedConnection is set to 0 then you will need to
--add username and password for connecting to the SQL Server instance
DECLARE @userName VARCHAR(20)='dbo'
DECLARE @password VARCHAR(20)=''

  --Here you need to specify the output directory for the files to be created
DECLARE @outputDir VARCHAR(25)='c:\Proscore5\json\';

  --Using Cursor
DECLARE @MyCursor CURSOR;

  --sample variables to hold each row's content
DECLARE @ItemID int;
DECLARE @ItemAgeGrp varchar(20);

  BEGIN
  SET @MyCursor = CURSOR FOR
  select all
  ID, AgeGrp
  from dbo.AgeGrpLvl3
    

    OPEN @MyCursor
  FETCH NEXT FROM @MyCursor
  INTO @ItemID, @ItemAgeGrp

    WHILE @@FETCH_STATUS = 0
BEGIN

  --Event 1
declare @sql1 varchar(8000)
declare @file1 varchar(50)
declare @fileunique1 varchar(50)
declare @tempdata varchar(8000)

  set @fileunique1 = Convert(varchar(100), Year(GETDATE()))
set @fileunique1 = @fileunique1 + Convert(varchar(100), Month(GETDATE()))
set @fileunique1 = @fileunique1 + Convert(varchar(100), Day(GETDATE()))
Set @file1 = @outputDir + @ItemAgeGrp + @fileunique1 +'.json'

  SELECT *
INTO dbo.tempdata
FROM ResultsLvl3 WHERE AgeGrp=@ItemAgeGrp AND Event='1';

  SET @sql1 = 'bcp "Select * FROM meetscoreslive.dbo.tempdata FOR JSON AUTO" queryout '+ @file1 +' -c -t; -T -S' + @@servername

    --Execute the BCP command
  EXEC xp_cmdshell @sql1
    DROP TABLE dbo.tempdata
     
FETCH NEXT FROM @MyCursor
INTO @ItemID, @ItemAgeGrp  

  END;

    CLOSE @MyCursor ;
  DEALLOCATE @MyCursor;
END;

  --Disable xp_cmdshell
EXEC sp_configure 'xp_cmdshell',0
RECONFIGURE;

  EXEC sp_configure 'show advanced options',0
RECONFIGURE;"
      SQLCMD.ExecuteNonQuery()
      SQLCONN.Close()

  As an alternate way, can you call a query from inside VB script where it will self execute and not return any values?