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.
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 view2. Print this3. Print other fixed string values4 Print a file sequence number that increments every time the view/footer process is run5. 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|ENDI 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. |
|
|
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 |
|
|
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. |
|
|
|
|
|
|
|