October 31, 2018 at 1:14 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 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
Got it work when using JSON AUTO it skips the Null
October 31, 2018 at 1:16 pm
Ok, I am almost done and really appreciate all the help everyone has done for me. The only issue I have not figured out is to check if a table exists so I can use the old one or delete it in visual sudio. This code executes but does not work, it goes through the Else even though the table exists. ' Assumes that ds is declared as a Dataset
Dim ds As New DataSet()
If ds.Tables.Contains("meetscoreslive.dbo.StartListSession") = True Then
MessageBox.Show("Exists")
Else
MessageBox.Show("Not Exists")
End If
November 1, 2018 at 6:13 am
TheFirstOne - Wednesday, October 31, 2018 1:16 PMOk, I am almost done and really appreciate all the help everyone has done for me. The only issue I have not figured out is to check if a table exists so I can use the old one or delete it in visual sudio. This code executes but does not work, it goes through the Else even though the table exists.' Assumes that ds is declared as a Dataset
Dim ds As New DataSet()
If ds.Tables.Contains("meetscoreslive.dbo.StartListSession") = True Then
MessageBox.Show("Exists")
Else
MessageBox.Show("Not Exists")
End If
Your code does not populate that dataset, and I'm actually thinking it would be more practical to have a recordset. You would have to query something like sys.tables to get a list of table names. When you "run" the command (which is the query), the your result goes into said recordset, and then you see if there are any rows in it. The idea being that if you query sys.tables to find a table of that name, and you get 0 rows back, you know it doesn't exist. If you do get a row back, then you know it does exist. Either way, you can proceed accordingly.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 13, 2018 at 11:40 pm
I have been racking the brain for a few weeks and ran into an issue I cannot figure out. There are extra white spaces in the JSON output that are hidden and are causing the app to unsuccessfully import the data. I have tried everything but most of the fixes I see are for c# or mysql etc. Here is the code to generate the JSON output.USE meetscoreslive
GO
--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\';
--Global Variables
--Start List
declare @sql varchar(8000)
declare @file varchar(50)
declare @fileunique varchar(50)
set @fileunique = Convert(varchar(100), Year(GETDATE()))
set @fileunique = @fileunique + Convert(varchar(100), Month(GETDATE()))
set @fileunique = @fileunique + Convert(varchar(100), Day(GETDATE()))
Set @file = @outputDir + @fileunique + 'Athletes'+ '.json'
--Dynamically construct the BCP command
--If the user connects to the SQL instance using a trusted connection
IF (@isTrustedConnection=1)
SET @sql = 'bcp "Select * FROM meetscoreslive.dbo.Athletes FOR JSON PATH" queryout '+ @file +' -c -t; -T -S' + @@servername --If the user connects to the SQL instance using username/password
ELSE
SET @sql = 'bcp "Select * FROM meetscoreslive.dbo.Athletes FOR JSON PATH" queryout '+ @file +' -c -t; -U ' + @UserName + ' -P '+ @password + ' -S '+ @@servername
--Execute the BCP command
EXEC xp_cmdshell @sql
This create a JSON file, here is the first record
[{"Country":"","State":"","Hometown":"","ClubName":"CEGW","Gender":"F","LastName":"Angcaya","FirstName":"Jayda","USAGNum":"1269231","CompNum":201,"Level":"2","Session":7,"Type":"ARTW2"},
But it is adding spaces which turn into \ in the appery.io program.
{
"BODY": "[{\"Country\":\"\",\"State\":\"\",\"Hometown\":\"\",\"ClubName\":\"CEGW\",\"Gender\":\"F\",\"LastName\":\"Angcaya\",\"FirstName\":\"Jayda\",\"USAGNum\":\"1269231\",\"CompNum\":201,\"Level\":\"2\",\"Session\":7,\"Type\":\"ARTW2\"},{\"Country\":\"\",\"State\":\"\",\"Hometown\":\"\",\"ClubName\":\"CEGW\",\"Gender\":\"F\",\"LastName\
Really strugling with this one. Here are som of the things I triedJSON.stringify(JSON.parse(body))SET @sql = replace(@sql,' ','')
I cannot add any code to this statment as it crashesSelect * FROM meetscoreslive.dbo.Athletes FOR JSON PATH
Full codeSET @sql = 'bcp "Select * FROM meetscoreslive.dbo.Athletes FOR JSON PATH" queryout '+ @file +' -c -t; -T -S' + @@servername
November 19, 2018 at 2:13 pm
Don't know what appery.io is, but if it is adding spaces, you are going to need to know both the why and the how. If you have no control over that program, then you may need to see what working input looks like and be sure you see it NOT add spaces...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 19, 2018 at 3:25 pm
The bcp output seems to add them as if I open the json output in a web view and then compact it removing white spaces it import fine. The issue is I have to manually compact the json file. It looks like I will not get the appery.io program to ignore the spaces so I need a way for the bcp output to do it.
December 12, 2018 at 9:45 pm
Hey everyone. Been buys working on the upcoming meet but I always try to get a little work coding each week. Now that I have the program working the way I want I am trying to FTP the files to a web server. I am using xp_cmdshell because I am already using it to make the files. I know there are better ways but it is working. I am using off the shelf code that has been shared but it is not working properly. The first issue is the files Ftp-ed have no data in them, it copies the directory contents but they are empty. I do not understand the inner workings so I am stuck. The other issue is it takes 50 min to ftp about 20 files so I am not sure why this is happening. The x are for security.
USE [meetscoreslive]
GO
create procedure [dbo].[uploadfile_ftp]
AS
Begin
DECLARE @SourcePath varchar(128)
DECLARE @SourceFiles varchar(128)
DECLARE @DestPath varchar(128)
DECLARE @FTPServer varchar(128)
DECLARE @FTPUser varchar(128)
DECLARE @FTPPwd varchar(128)
DECLARE @FTPMode varchar(10)
-- FTP attributes.
SET @FTPServer = 'xxx.xxx.xxx.xxx
SET @FTPUser = 'xxxxxx'
SET @FTPPwd = 'xxxxxxxx'
SET @FTPMode = 'binary' -- ascii, binary or blank for default.
SET @SourcePath = 'C:\Proscore5\json\'
SET @SourceFiles = '*.json'
SET @DestPath = ''
DECLARE @cmd varchar(1000)
DECLARE @workfile varchar(128)
DECLARE @nowstr varchar(25)
-- Get the %TEMP% environment variable.
DECLARE @tempdir varchar(128)
CREATE TABLE #tempvartable(info VARCHAR(1000))
INSERT #tempvartable EXEC master..xp_cmdshell 'echo %temp%'
SET @tempdir = (SELECT top 1 info FROM #tempvartable)
IF RIGHT(@tempdir, 1) <> '\' SET @tempdir = @tempdir + '\'
DROP TABLE #tempvartable
-- Generate @workfile
SET @nowstr = replace(replace(convert(varchar(30), GETDATE(), 121), ' ', '_'), ':', '-')
SET @workfile = 'FTP_SPID' + convert(varchar(128), @@spid) + '_' + @nowstr + '.txt'
-- Deal with special chars for echo commands.
select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
select @FTPPwd = replace(replace(replace(@FTPPwd, '&', '^&'),'<','^<'),'>','^>')
select @DestPath = replace(replace(replace(@DestPath, '|', '^|'),'<','^<'),'>','^>')
IF RIGHT(@SourcePath, 1) <> '\' SET @SourcePath = @SourcePath + '\'
-- Build the FTP script file.
select @cmd = 'echo ' + 'open ' + @FTPServer + ' > ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
print @cmd;
select @cmd = 'echo ' + @FTPUser + '>> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
print @cmd;
select @cmd = 'echo ' + @FTPPwd + '>> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
print @cmd;
select @cmd = 'echo ' + 'prompt ' + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
print @cmd;
IF LEN(@FTPMode) > 0
BEGIN
select @cmd = 'echo ' + @FTPMode + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
END
IF LEN(@DestPath) > 0
BEGIN
select @cmd = 'echo ' + 'cd ' + @DestPath + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
END
select @cmd = 'echo ' + 'mput ' + @SourcePath + @SourceFiles + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'quit' + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
-- Execute the FTP command via script file.
select @cmd = 'ftp -s:' + @tempdir + @workfile
print @cmd;
create table #a (id int identity(1,1), s varchar(1000))
insert #a
EXEC master..xp_cmdshell @cmd
select id, ouputtmp = s from #a
-- Clean up.
drop table #a
select @cmd = 'del ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
End;
January 16, 2019 at 11:20 am
Good Morning all. Well it is just about finished, at least the database part. I want to thank everyone for all your help. I can now output a JSON file locally and to a web server address. Here is the only issue which was the line break problem I mentioned before.. Three of the JSON files are over the varchar(8000) limit. I cannot delete information to get the files smaller as one is 60k characters and the other are 15k characters. What would be the best way to get past the 8000 character limit.
USE meetscoreslive
Go
CREATE PROCEDURE SqlProcedureCurrentSessionAthletesLvl2
AS
BEGIN
--Level and Session Variables
DECLARE @Level varchar(10)
DECLARE @Session varchar(20)
--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
SELECT TOP 1 @isTrustedConnection = [SqlisTrustedConnection] FROM [dbo].[GlobalVariables];
--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)
DECLARE @password VARCHAR(20)
SELECT TOP 1 @userName = [SqluserName] FROM [dbo].[GlobalVariables];
SELECT TOP 1 @password = [Sqlpassword] FROM [dbo].[GlobalVariables];
--Here you need to specify the output directory for the files to be created
DECLARE @outputDir VARCHAR(50)
SELECT TOP 1 @outputDir = [JsonPath] FROM [dbo].[GlobalVariables];
--Global Variables
SELECT @Level = NULL
SELECT TOP 1 @Level = [Level] FROM ResultsLvl2
SELECT @Session = NULL
SELECT TOP 1 @Session = [Session] FROM ResultsLvl2
--Start List
declare @sql varchar(8000)
declare @file varchar(74)
declare @fileunique varchar(50)
set @fileunique = Convert(varchar(100), Year(GETDATE()))
set @fileunique = @fileunique + Convert(varchar(100), Month(GETDATE()))
set @fileunique = @fileunique + Convert(varchar(100), Day(GETDATE()))
Set @file = @outputDir + @Session + @Level + @fileunique + 'CurrentSessionAthletes' + '.json'
--Dynamically construct the BCP command
--If the user connects to the SQL instance using a trusted connection
IF (@isTrustedConnection=1)
SET @sql = 'bcp "Select * FROM meetscoreslive.dbo.ResultsLvl2 FOR JSON AUTO" queryout '+ @file +' -c -t; -T -S' + @@servername --If the user connects to the SQL instance using username/password
ELSE
SET @sql = 'bcp "Select * FROM meetscoreslive.dbo.ResultsLvl2 FOR JSON AUTO" queryout '+ @file +' -c -t; -U ' + @UserName + ' -P '+ @password + ' -S '+ @@servername
--Execute the BCP command
EXEC xp_cmdshell @sql
End;
Viewing 8 posts - 121 through 128 (of 128 total)
You must be logged in to reply to this topic. Login to reply