October 10, 2016 at 9:35 am
hello,
I want to display a trailer record at the end of records :
the spec is as follows
Trailer Record:
Field Name LengthBegin End Type Mandatory/Optional Description
TYPE 21 2 AN M TR' = Trailer
RECORD COUNT123 14 N M Record count not including Trailer Record
PROCESS DATE815 22 N M (system date YYYYMMDD Format)
FILLER 27823 300 AN M Space Filled
I am displaying this trailer record in a text file with already existing record that has about 1500 records. I am not able to get this at the end of the row. And I am using Count(*) and getdate() functions but not getting and output in the text file when I run it through batch(.bat) file.
October 10, 2016 at 10:12 am
Hi, we will need more information, can you elaborate further?
😎
October 10, 2016 at 11:24 am
I want to display this trailer record using sql query at the end of the row in a text file. and this text file I am displaying using batch(.bat) file.
Its like I have one temp table already in a stored procedure which has 5 fields with fixed length which is working fine ( getting all fields in text file) when I execute it using .bat file. But now I want to add a trailer record with above specifications and fields(type, record count and date) using sql query and display the results in the same text file as a new row.
Thanks
October 10, 2016 at 1:31 pm
Yes, but are you having trouble getting the information to append to the file or do you have the information and you can't figure out how to append it?
We also don't know the internals of your batch file, so we don't know whether it is calling bcp or SSIS or some other ETL tool, so we can't tell you how to change it to do the append.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 10, 2016 at 1:42 pm
USE [St]
GO
/****** Object: StoredProcedure [dbo].[P_tnvin] Script Date: 10/10/2016 13:35:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[P_tnvin]
AS
BEGIN
SET NOCOUNT ON;
Create Table TmpTNVINInfo
(
VINRecord char(300)
)
INSERT INTO TmpTNVINInfo
SELECT
(
'VS'
+ '1266100'
+ CONVERT(CHAR(30),LEFT(P.policyNum, 30))
+ CONVERT(CHAR(8),P.effectiveDate, 112)
+ CONVERT(CHAR(25),LEFT(V.VIN, 25))
+ CONVERT(CHAR(40),LEFT(I.lname1, 40))
+ ' '
+ CONVERT(CHAR(20),LEFT(I.middle1, 20))
+ CONVERT(CHAR(40),LEFT(I.fname1, 40))
+ ' '
+ ' '
+ CONVERT(CHAR(50),LEFT(I.address1, 50))
+ CONVERT(CHAR(35),LEFT(I.city, 35))
+ CONVERT(CHAR(2),LEFT(I.state, 2))
+ CONVERT(CHAR(5),LEFT(I.zip, 5))
+ ' '
)storedprocedure
FROM
[st].[dbo].[Policy] P
JOIN
[St].[dbo].[Auto] A
ON
P.policyID = A.policyID
JOIN
[St].[dbo].[Vehicle] V
ON
A.autoID = V.autoID
JOIN
[St].[dbo].[Insured] I
ON
P.insuredID = I.insuredID
WHERE
P.policyType = 1 --1=Auto
AND
P.status = 1 --1=Active
AND
P.policyNum NOT LIKE 'Q%'
END
/*SELECT * FROM TmpTNVINInfo
/****** Cleanup Temp Table *******/
DROP TABLE TmpTNVINInfo*/
##### Batch file##########################################
set filedatetime=03/10/2016 11:03
set date=%filedatetime:~6,4%%filedatetime:~3,2%%filedatetime:~0,2%
set filename=tnvin_%date%.txt
set tnpath=C:\TestSQLCMD -S SQLSANDBOX -E -Q "[St].[dbo].[P_tnvin] "
bcp [St].[dbo].[TmpTNVINInfo] out "%tnpath%%filename%" -c -T -S SQLSANDBOX
SQLCMD -S SQLSANDBOX -E -Q "DROP TABLE [St].[dbo].[TmpTNVINInfo]"
#######################################################
The above sql stored procedure is the code which works to display my fields with batch file script(the above script I used to display the contents of tmp table in text file). Now I want to display the trailer record with the specifications(fixed length between columns) as in my question as a separate row below in text file.
October 10, 2016 at 3:44 pm
bcp doesn't allow you to append data to an existing file.
You have a couple of options. You can output the data to two separate files and then concatenate them in your batch file or you can add the data to your table. You may need to add a field to specify the order of output, but you probably don't want to include that in your output, so you would have to change it to a query.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 11, 2016 at 6:05 am
Okay Thanks.
October 11, 2016 at 9:02 am
I've done this before with headers and footers and it's a bit of a challenge. If you open a command prompt and enter HELP COPY, the last line (on my system) says "To append files, specify a single file for destination, but multiple files for source (using wildcards or file1+file2+file3 format)." It'll take some experimentation to get the command format correct, but it's doable.
Another way is to use > and >> at the OS command level to send output to a file or to append it to the end of a file respectively, but that might just be for ECHO commands. I'm not sure off-hand and I'm not in a position to test it.
So basically you write your footer to a file then invoke a batch file or command to append everything together then clean up the temp files.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 11, 2016 at 9:02 am
I've done this before with headers and footers and it's a bit of a challenge. If you open a command prompt and enter HELP COPY, the last line (on my system) says "To append files, specify a single file for destination, but multiple files for source (using wildcards or file1+file2+file3 format)." It'll take some experimentation to get the command format correct, but it's doable.
Another way is to use > and >> at the OS command level to send output to a file or to append it to the end of a file respectively, but that might just be for ECHO commands. I'm not sure off-hand and I'm not in a position to test it.
So basically you write your footer to a file then invoke a batch file or command to append everything together then clean up the temp files.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 11, 2016 at 9:09 am
Thank You!! I will try it.
October 11, 2016 at 11:23 pm
patilpallavi16 (10/10/2016)
USE [St]GO
/****** Object: StoredProcedure [dbo].[P_tnvin] Script Date: 10/10/2016 13:35:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[P_tnvin]
AS
BEGIN
SET NOCOUNT ON;
Create Table TmpTNVINInfo
(
VINRecord char(300)
)
INSERT INTO TmpTNVINInfo
SELECT
(
'VS'
+ '1266100'
+ CONVERT(CHAR(30),LEFT(P.policyNum, 30))
+ CONVERT(CHAR(8),P.effectiveDate, 112)
+ CONVERT(CHAR(25),LEFT(V.VIN, 25))
+ CONVERT(CHAR(40),LEFT(I.lname1, 40))
+ ' '
+ CONVERT(CHAR(20),LEFT(I.middle1, 20))
+ CONVERT(CHAR(40),LEFT(I.fname1, 40))
+ ' '
+ ' '
+ CONVERT(CHAR(50),LEFT(I.address1, 50))
+ CONVERT(CHAR(35),LEFT(I.city, 35))
+ CONVERT(CHAR(2),LEFT(I.state, 2))
+ CONVERT(CHAR(5),LEFT(I.zip, 5))
+ ' '
)storedprocedure
FROM
[st].[dbo].[Policy] P
JOIN
[St].[dbo].[Auto] A
ON
P.policyID = A.policyID
JOIN
[St].[dbo].[Vehicle] V
ON
A.autoID = V.autoID
JOIN
[St].[dbo].[Insured] I
ON
P.insuredID = I.insuredID
WHERE
P.policyType = 1 --1=Auto
AND
P.status = 1 --1=Active
AND
P.policyNum NOT LIKE 'Q%'
END
/*SELECT * FROM TmpTNVINInfo
/****** Cleanup Temp Table *******/
DROP TABLE TmpTNVINInfo*/
##### Batch file##########################################
set filedatetime=03/10/2016 11:03
set date=%filedatetime:~6,4%%filedatetime:~3,2%%filedatetime:~0,2%
set filename=tnvin_%date%.txt
set tnpath=C:\TestSQLCMD -S SQLSANDBOX -E -Q "[St].[dbo].[P_tnvin] "
bcp [St].[dbo].[TmpTNVINInfo] out "%tnpath%%filename%" -c -T -S SQLSANDBOX
SQLCMD -S SQLSANDBOX -E -Q "DROP TABLE [St].[dbo].[TmpTNVINInfo]"
#######################################################
The above sql stored procedure is the code which works to display my fields with batch file script(the above script I used to display the contents of tmp table in text file). Now I want to display the trailer record with the specifications(fixed length between columns) as in my question as a separate row below in text file.
There's no need to sweat the trailer. You've done most of the hard work by putting all of the data into a single column table. Just add the trailer into the stored procedure and Bob's your uncle. Also, note how I aligned columns of code vertically. That does two things. It makes it easy to read AND it allowed me to do a "vertical copy" of all the column lengths so I could paste them into EXCEL and easily do a total, where I find that [font="Arial Black"]you actually have 302 characters in width rather than the required 300[/font]. You need to figure that out because I don't know the actual record layout for this problem. A real convenience is using the SPACE() function instead of hardcoded spaces, which are a little bit more difficult to count between quotes (there WERE 23 in your final set of spaces). Please see the notes in the following code for more information.
I also took the liberty to impart some best practices such as adding semi-colons and calling all objects using the two part naming convention.
CREATE PROCEDURE dbo.P_tnvin
AS
SET NOCOUNT ON
;
CREATE TABLE dbo.TmpTNVINInfo
(
VINRecord CHAR(300)
)
;
INSERT INTO dbo.TmpTNVINInfo
(VINRecord)
SELECT VINRecord =
'VS' --( 2)
+ '1266100' --( 7)
+ CONVERT(CHAR(30),LEFT(P.policyNum , 30))
+ CONVERT(CHAR( 8), P.effectiveDate ,112)
+ CONVERT(CHAR(25),LEFT(V.VIN , 25))
+ CONVERT(CHAR(40),LEFT(I.lname1 , 40))
+ SPACE( 3)
+ CONVERT(CHAR(20),LEFT(I.middle1 , 20))
+ CONVERT(CHAR(40),LEFT(I.fname1 , 40))
+ SPACE( 3)
+ SPACE( 9)
+ CONVERT(CHAR(50),LEFT(I.address1 , 50))
+ CONVERT(CHAR(35),LEFT(I.city , 35))
+ CONVERT(CHAR( 2),LEFT(I.[state] , 2))
+ CONVERT(CHAR( 5),LEFT(I.zip , 5))
+ SPACE( 23) --I BELIEVE THIS SHOULD ACTUALLY BE ONLY 21 SPACES OR ONE OF THE OTHER GAPS IS INCORRECT (TOTAL IS 302)
FROM st.dbo.Policy P
JOIN St.dbo.Auto A ON P.policyID = A.policyID
JOIN St.dbo.Vehicle V ON A.autoID = V.autoID
JOIN St.dbo.Insured I ON P.insuredID = I.insuredID
WHERE P.policyType = 1 --1=Auto
AND P.status = 1 --1=Active
AND P.policyNum NOT LIKE 'Q%'
;
--===== Added this whole section for the trailer record.
-- Note that nothing should come between this section and the section above for @@ROWCOUNT.
INSERT INTO dbo.TmpTNVINInfo
(VINRecord)
SELECT VINRecord =
'TR'
+ RIGHT(SPACE(12)+CONVERT(VARCHAR(12),@@ROWCOUNT),12) --Record Count (Right Justified)
+ CONVERT(CHAR(8),GETDATE(),112) --Process Date (YYYYMMDD)
+ SPACE(278) --FILLER
;
As a bit of a sidebar, it's a real shame that many DBAs don't know how to allow for the secure use of xp_CmdShell. This problem would be a total cakewalk for scheduling and wouldn't need that nasty ol' batch file at all if you could use xp_CmdShell to execute a built up, single line BCP call.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2016 at 9:34 am
Thanks a lot! It does work.:-)
October 12, 2016 at 9:48 am
patilpallavi16 (10/12/2016)
Thanks a lot! It does work.:-)
Thanks for the feedback. The question now is... Did you work out the record length so that it's 300 instead of 302? It might not cause a problem now but could in the future.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2016 at 9:56 am
Yes I checked it. I changed the Naic code spaces since I needed to enter different code there. It gave me correct record count excluding the trailer record row.
October 12, 2016 at 3:18 pm
To do it all in one query you may use GROUP BY ... WITH ROLLUP.
In the row with GROUPING(Column1) = 1 you concatenate COUNT(*) , GETDATE() and whatever else you need to put there (after converting them all into varchars, of course.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply