INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

@@Rowcount trying to create a header row in text file

@@Rowcount trying to create a header row in text file

(OP)
I have been searching but can't seem to locate an answer for this. I have a file I need to create that has a header row with the count of the number of rows that will be following for checking. When the initial test sp was built there were only the TOP 10 records selected and when the SQL was run as text it worked perfectly. Now when the 'SELECT TOP 10' is replaced with simply SELECT, it ceases to work and I simply get count of the rows affected and the word 'record' Here is the query if someone can tell what is wrong. I have been able to get it to run up to 10999. Anything higher and it breaks.

CODE

IF OBJECT_ID('tempdb..#temp1')IS NOT NULL BEGIN
      drop table #temp1
END
IF OBJECT_ID('tempdb..#temp2')IS NOT NULL BEGIN
      drop table #temp2
END

declare @xRowCount numeric

CREATE TABLE #temp1
(record nvarchar(255)
)
INSERT INTO #temp1
SELECT --TOP 19000    
LEFT(CONVERT(VARCHAR,isnull(ie.ind_employee_id_ext, '')),10) +
Left(CONVERT(varchar,isnull(ie.ind_ssn_ext,''))+'000000000',9) +
substring(CAST (dbo.client_H2U_GetPaddedString(20,'R',' ',i.ind_first_name) as varchar(20)),1,20) +
substring(CAST (dbo.client_H2U_GetPaddedString(30,'R',' ',i.ind_last_name) as varchar(30)),1,30)
FROM
co_individual i
JOIN co_individual_ext ie (NOLOCK) ON i.ind_cst_key=ie.ind_cst_key_ext  
JOIN co_customer c  (NOLOCK)  ON c.cst_key=i.ind_cst_key and i.ind_delete_flag=0
join lab_screening_result lr (nolock) on lr.h14_cst_key = c.cst_key
and lr.h14_delete_flag = 0

WHERE h14_h15_key ='6C7CFD35-E293-4983-8C7B-B4242C1435AB'

Set @xRowCount = @@ROWCOUNT
     
CREATE TABLE #temp2
(record nvarchar(255)
)
Insert into #temp2     
SELECT dbo.client_GetPaddedString(9,'L','0',@xRowCount) + CONVERT(char(8), GETDATE(), 112) + dbo.client_GetPaddedString(51,'L',' ',' ')

SELECT  record
FROM #temp2
UNION
SELECT record
from #temp1

DROP TABLE #temp1
DROP TABLE #temp2

RE: @@Rowcount trying to create a header row in text file

(OP)
Never mind. I found that this is working, just the union is selecting it out of order so I am getting this kind of results :
0000440039231436629XiYcgrTy            TgnggTgXis          
0000440063454087231YgrX                TipscoYc            
0000450004486647461YichggT             YontgoYgry          
00004811420110914                                                    
0000500079261535371Jgnnifgr            Urcgn               
0000520012029423168JiTT                SchYgcX

instead of:
00004811420110914
0000440039231436629XiYcgrTy            TgnggTgXis          
0000440063454087231YgrX                TipscoYc            
0000450004486647461YichggT             YontgoYgry          
0000579261535371Jgnnifgr               Urcgn               
0000520012029423168JiTT                SchYgcX   

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close