October 16, 2018 at 2:27 pm
TheFirstOne - Tuesday, October 16, 2018 2:14 PMTheFirstOne - Tuesday, October 16, 2018 1:55 PMWhen 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;
RECONFIGUREEXEC 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, @ItemAgeGrpWHILE @@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, @ItemAgeGrpEND;
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?
If i put it under "" it creates the file but no data.""select * from dbo.tempdata for json path""
October 17, 2018 at 6:45 am
TheFirstOne - Tuesday, October 16, 2018 2:27 PMTheFirstOne - Tuesday, October 16, 2018 2:14 PMTheFirstOne - Tuesday, October 16, 2018 1:55 PMWhen 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;
RECONFIGUREEXEC 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, @ItemAgeGrpWHILE @@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, @ItemAgeGrpEND;
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?
If i put it under "" it creates the file but no data.
""select * from dbo.tempdata for json path""
You ARE using SQL Server 2016 or above, right?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 17, 2018 at 11:02 am
So it looks like the " is the issue. I have tried different ways to change this " ' " etc but nothing works. Is there a way to have double quotes in a SQLCMD statement?
October 18, 2018 at 7:36 am
TheFirstOne - Wednesday, October 17, 2018 11:02 AMSo it looks like the " is the issue. I have tried different ways to change this " ' " etc but nothing works. Is there a way to have double quotes in a SQLCMD statement?
Take a look at the BCP utility's syntax here:
https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2016
It specifies a single double-quote mark at each end of the query. Only a single-quote mark would need to be doubled here, based on you being in the situation of populating a string variable and are thus already within a pair of single-quote marks that delimit the entire string.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 18, 2018 at 12:45 pm
sgmunson - Wednesday, October 17, 2018 6:45 AMTheFirstOne - Tuesday, October 16, 2018 2:27 PMTheFirstOne - Tuesday, October 16, 2018 2:14 PMTheFirstOne - Tuesday, October 16, 2018 1:55 PMWhen 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;
RECONFIGUREEXEC 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, @ItemAgeGrpWHILE @@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, @ItemAgeGrpEND;
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?
If i put it under "" it creates the file but no data.
""select * from dbo.tempdata for json path""You ARE using SQL Server 2016 or above, right?
Yes, 2017. I made it work using exec. It probably is not the best way but it works. One thing I question is will the first call finish before the second? If not there could be lag between the files updating?If CheckBoxLvl3.Checked = True Then
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 = "exec SqlProcedureResultsLvl3
exec SqlProcedureAgeGrpLvl3"
SQLCMD.ExecuteNonQuery()
SQLCONN.Close()
End If
So basically I will have a bunch of procedures that create the tables and then output the JSON files and they will be called depending on which check boxes are cheeked
October 18, 2018 at 3:29 pm
TheFirstOne - Thursday, October 18, 2018 12:45 PMYes, 2017. I made it work using exec. It probably is not the best way but it works. One thing I question is will the first call finish before the second? If not there could be lag between the files updating?
If CheckBoxLvl3.Checked = True Then
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 = "exec SqlProcedureResultsLvl3
exec SqlProcedureAgeGrpLvl3"
SQLCMD.ExecuteNonQuery()
SQLCONN.Close()End If
So basically I will have a bunch of procedures that create the tables and then output the JSON files and they will be called depending on which check boxes are cheeked
Sounds good to me. And yes, a T-SQL script that executes a series of stored procedures will run them one at a time, in the order specified.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 19, 2018 at 12:55 pm
I would suggest opening the connection once and reusing it, rather than creating and opening a new connection in each IF section.
Also, I don't see anything in your code to dispose of the connection and command objects. They should be initialized in a USING statement rather than DIM, so they are disposed of automatically.
Using SQLCONN As New SqlClient.SqlConnection
SQLCONN.ConnectionString =
SQLCONN.Open
Using SQLCMD As New SqlClient.SqlCommand
SQLCMD.Connection = SQLCONN
SQLCMD.CommandType = CommandType.Text
If CheckBoxLvl3.Checked = True Then
SQLCMD.CommandText = "exec SqlProcedureResultsLvl3
exec SqlProcedureAgeGrpLvl3"
SQLCMD.ExecuteNonQuery
End If
If OtherCheckBox.Checked = True Then
SQLCMD.CommandText = "exec OtherStoredProc"
SQLCMD.ExecuteNonQuery
End If
End Using ' Disposes of SQLCMD object
End Using ' Closes connection, disposes of SQLCONN object
October 19, 2018 at 1:22 pm
Scott Coleman - Friday, October 19, 2018 12:55 PMI would suggest opening the connection once and reusing it, rather than creating and opening a new connection in each IF section.Also, I don't see anything in your code to dispose of the connection and command objects. They should be initialized in a USING statement rather than DIM, so they are disposed of automatically.
Using SQLCONN As New SqlClient.SqlConnection
SQLCONN.ConnectionString =
SQLCONN.Open
Using SQLCMD As New SqlClient.SqlCommand
SQLCMD.Connection = SQLCONN
SQLCMD.CommandType = CommandType.Text
If CheckBoxLvl3.Checked = True Then
SQLCMD.CommandText = "exec SqlProcedureResultsLvl3
exec SqlProcedureAgeGrpLvl3"
SQLCMD.ExecuteNonQuery
End If
If OtherCheckBox.Checked = True Then
SQLCMD.CommandText = "exec OtherStoredProc"
SQLCMD.ExecuteNonQuery
End If
End Using ' Disposes of SQLCMD object
End Using ' Closes connection, disposes of SQLCONN object
Ok, I see. My goal is to get a beta version working so I can test it. Once I think I have all the bases covered I will look to hire someone to clean up the code a little to cover the major errors. If the product is successful I will look for someone to rewrite any bad code and manage updates.
October 19, 2018 at 1:36 pm
You seemed to be concerned about how many seconds it would take to run all the commands, so you should realize that reconnecting to the database each time can be pretty time consuming. Depending on the commands, your program could spend more time opening and closing connections that it does running the commands. I think realizing that connections and commands are not one-time-use objects is a pretty fundamental concept.
October 19, 2018 at 4:07 pm
Scott Coleman - Friday, October 19, 2018 1:36 PMYou seemed to be concerned about how many seconds it would take to run all the commands, so you should realize that reconnecting to the database each time can be pretty time consuming. Depending on the commands, your program could spend more time opening and closing connections that it does running the commands. I think realizing that connections and commands are not one-time-use objects is a pretty fundamental concept.
The program that scores the gymnasts updates on the average of 5 sec. Spectators can get the updates every 30 seconds to a minute from competing vendors so we need to compete with this. Our advantage is we are local in the building and spectators need a cell connection and go to an outside address.
October 19, 2018 at 5:19 pm
Ok, So I changed to open the database once and run my loop. Everything works although I am not sure I am disposing of the object. Is the exit sub bypassing the end using?
If CheckBoxXD.Checked = True Then
SQLCMD.CommandText = ""
SQLCMD.ExecuteNonQuery()
End If
If StopSession = True Then
Exit Sub
Else
Continue While
End If
End While
End Using ' Disposes of SQLCMD object
End Using ' Closes connection, disposes of SQLCONN object
End Sub
October 22, 2018 at 12:18 pm
I added SQLCMD.Dispose() to the stop button which exits the sub. Is this correct. If StopSession = True Then
SQLCMD.CommandText = "EXEC sp_configure 'xp_cmdshell',0
RECONFIGURE
EXEC sp_configure 'show advanced options',0
RECONFIGURE"
SQLCMD.ExecuteNonQuery()
SQLCMD.Dispose()
Exit Sub
Else
Continue While
End If
End While
SQLCMD.CommandText = "EXEC sp_configure 'xp_cmdshell',0
RECONFIGURE
EXEC sp_configure 'show advanced options',0
RECONFIGURE"
SQLCMD.ExecuteNonQuery()
End Using ' Disposes of SQLCMD object
End Using ' Closes connection, disposes of SQLCONN object
End Sub
October 25, 2018 at 1:48 pm
I am having trouble getting the row data from multiple rows into a single row and only show one name. USE meetscoreslive
DECLARE @test-2 varchar(8000)
select LastName,[Level],CompNum,Rotation,
max(iif(Rotation = 1, 'Bars', NULL)) as Bars,
max(iif(Rotation = 2, 'Beam', NULL)) as Beam,
max(iif(Rotation = 3, 'Vault', NULL)) as Vault,
max(iif(Rotation = 4, 'Floor', NULL)) as [Floor]
-- into test
from StartListSession
group by LastName,[Level],CompNum,Rotation
order by LastName;
Results
Belceto XP 1401 1 Bars NULL NULL NULL
Belceto XP 1401 2 NULL Beam NULL NULL
Belceto XP 1401 3 NULL NULL Vault NULL
Belceto XP 1401 4 NULL NULL NULL Floor
Berryessa XP 1429 1 Bars NULL NULL NULL
Berryessa XP 1429 2 NULL Beam NULL NULL
Berryessa XP 1429 3 NULL NULL Vault NULL
Berryessa XP 1429 4 NULL NULL NULL Floor
Bowers XP 1405 1 Bars NULL NULL NULL
Bowers XP 1405 2 NULL Beam NULL NULL
Bowers XP 1405 3 NULL NULL Vault NULL
Bowers XP 1405 4 NULL NULL NULL Floor
Brewer XD 1203 1 Bars NULL NULL NULL
October 25, 2018 at 8:20 pm
TheFirstOne - Thursday, October 25, 2018 1:48 PMI am having trouble getting the row data from multiple rows into a single row and only show one name.USE meetscoreslive
DECLARE @test-2 varchar(8000)select LastName,[Level],CompNum,Rotation,
max(iif(Rotation = 1, 'Bars', NULL)) as Bars,
max(iif(Rotation = 2, 'Beam', NULL)) as Beam,
max(iif(Rotation = 3, 'Vault', NULL)) as Vault,
max(iif(Rotation = 4, 'Floor', NULL)) as [Floor]
-- into test
from StartListSession
group by LastName,[Level],CompNum,Rotation
order by LastName;Results
Belceto XP 1401 1 Bars NULL NULL NULL
Belceto XP 1401 2 NULL Beam NULL NULL
Belceto XP 1401 3 NULL NULL Vault NULL
Belceto XP 1401 4 NULL NULL NULL Floor
Berryessa XP 1429 1 Bars NULL NULL NULL
Berryessa XP 1429 2 NULL Beam NULL NULL
Berryessa XP 1429 3 NULL NULL Vault NULL
Berryessa XP 1429 4 NULL NULL NULL Floor
Bowers XP 1405 1 Bars NULL NULL NULL
Bowers XP 1405 2 NULL Beam NULL NULL
Bowers XP 1405 3 NULL NULL Vault NULL
Bowers XP 1405 4 NULL NULL NULL Floor
Brewer XD 1203 1 Bars NULL NULL NULL
After looking at the original import from the xml file it might be easier to make columns from the different Start events like
CompNum Event1 Event2 Event3 Event4
1201 Rotation1 Rotation2 Rotation3 Rotation4
<Athlete CompNum="1201">
<StartList>
<Start event="1" rotation="1" order="1" />
<Start event="2" rotation="2" order="1" />
<Start event="3" rotation="3" order="1" />
<Start event="4" rotation="4" order="1" />
</StartList>
</Athlete>
how would i modify the code.
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)
October 25, 2018 at 9:03 pm
TheFirstOne - Friday, October 19, 2018 5:19 PMOk, So I changed to open the database once and run my loop. Everything works although I am not sure I am disposing of the object. Is the exit sub bypassing the end using?
A using block always calls dispose on the object when the code leaves the block, no matter how that happens. So you're certain to be correctly disposing of the object in this case.
Viewing 15 posts - 106 through 120 (of 127 total)
You must be logged in to reply to this topic. Login to reply