Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need to generate a custom footer by sqlcmd utility

Author  Topic 

Anurag Pareek
Starting Member

2 Posts

Posted - 2014-04-16 : 08:28:18
Essentially I need a solution that will;
1. Return the number of records in a given view
2. Print this
3. Print other fixed string values
4 Print a file sequence number that increments every time the view/footer process is run
5. Print some formatted date/time fields.

All this needs to be controlled by a client running sqlcmd or osql.

A sample of what is required is as follows;
TRAILER |52068|0000000389|USER DETAILS|20140416|165313|PEOPLE_DATA|END

I Originally had a stored procedure that the client passed a parameter/variable to denote the table or view to be used and the stored procedure would execute a count of all records in that view. This was then used in a print statement that outputted the record count as well as other fixed fields and some customized date / time fields.

I have since found that I cannot expand this stored procedure to return a sequence number from another table (stored procedure limitation I believe).
So I would need to know if this could be accomplished by the sqlcmd utility? And if yes, then how?

See below for an explanation of the fields.

Field 1: Identifier(Char 8): Record title, always hard-coded to 'TRAILER ' (note the trailing blank).

Field 2: Record count(Char (type=N) 10): Number of data records in the file.
The file must have exactly this many data records (excluding the Trailer record) or it shall be rejected on error.

Field 3: File_Seq_number(Char (type=N) 10): Each file shall be numbered sequentially, starting with 0000000001, then 0000000002, etc. When the maximum value, 9999999999 is reached, the next expected number shall wrap around back to 0000000001. The first generated file should contain 0000000001.
The sequence number field should be all numeric characters ('0' - '9') and right-justified and padded with zeroes on the left.

Field 4: Filename(Char 40): Always hard-coded to 'USER DETAILS'.

Field 5: Date Created(Char 8): The date when this file was created should in the format YYYYMMDD.

Field 6: Time Created(Char 6): The time when this file was created in the format HHMISS.

Field 7: File Origin(Char 30): Always hard-coded to 'PEOPLE_DATA'.

Field 7: End_Of_Data(Char 3): Send the value 'END' indicating the end of data. This should be the last value of each file.

Many Thanks in advance !!

Thanks & Regards,
Anurag Pareek

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-16 : 08:46:28
There is no stored procedure limitation to returning data from more than one table. You can either have multiple return sets or do a join in your main query to add the sequence number or .... lots of ways really.

Note, this is not about the sqlcmd utility at all. That's just a way to get your query executed. There are lots of other ways, e.g. a customer .NET program, PowerShell cmdlet, SSIS, SSRS, etc. If it were me, I'd develop the report in SSRS so I could separate the output format from the query producing the data.
Go to Top of Page

Anurag Pareek
Starting Member

2 Posts

Posted - 2014-04-16 : 22:25:29
Hello Gbritton,
I am not very much familiar with SSRS and Powershell cmdlets.
Could you help me out in generating the specified text string by using either of the tools.


Thanks & Regards,
Anurag Pareek
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-17 : 08:12:09
If you don't know SSRS or PowerShell, no problem (though learning SSRS would be a good idea). Post your DDL along with sample data to populate the tables; the query/proc you have at the moment and some sample output data.
Go to Top of Page
   

- Advertisement -