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 |
|
cat_jesus
Aged Yak Warrior
547 Posts |
 Posted - 2001-11-20 : 10:16:17
|
| In a large SP I am working on the final step in the process is multiple BCPs out to text files. To verify all the data went out ok, they BCP the data back in and compare record counts.This process now takes the bulk(no pun intended) of the time for this SP.I was thinking about writing a vb app that I can call from the SP that will count the records in the output file and then update a small table with the results. This way I can get rid of half the BCPs.Has anyone else run into the need for BCP output verification and come up with a different or better solution?CatSql. 7.0 using 6.5 format files |
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2002-02-07 : 13:30:50
|
| I found a really neat solution. use xp_cmdshell to call a batch file named reccount.cmd like soSELECT @REC_COUNT_STRING = @DIRECTORY_PATH + '\reccount ' + @DIRECTORY_PATH + '\' + @OUT_FILE_NAME + ' .'insert into #temp exec master..xp_cmdshell @REC_COUNT_STRINGthen compare the number in #temp to count(*) from the source table.the source for reccount.cmd follows:rem . (period) is an optional parameter and will return only the numbersetlocalfor /f %%a in ('find /v /c "" ^< %1') do set linecount=%%aif {%2}=={.} echo %linecount%if not {%2}=={.} echo the number of records in %1 is %linecount%The only drawback is that the cmd file must be present on the drive. I have toyed with the idea of doing a dir for it first and then puttin it where I need it if it isn't present.Cat |
 |
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2003-04-17 : 09:29:37
|
| I have found out the hard way that this script will not work from SQL server when it is on an NT 4.0 machine. For some reason SQL Server does not see the variable "linecount". Yet, when you run it from a native NT cmd shell it works fine.Does anyone know why this would be? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-17 : 10:34:38
|
| I'm just curious. Are the files for some one elses process? Do you wrap the file in header and trailer information...What you're trying to do is QA a utility...(I would imagine it's been qa'ed enough by the user community).BUT, with that said, I've seen FTP and the like trunc a file. Adding header and trailers (with dates counts, maybe some summation on amount columns) should take care care of it to insure everything is ok.It adds to the process, both on the way out, and back in, but it's worth it. Brett8-) |
|
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2003-04-17 : 10:43:24
|
quote:
I'm just curious. Are the files for some one elses process? Do you wrap the file in header and trailer information...What you're trying to do is QA a utility...(I would imagine it's been qa'ed enough by the user community).BUT, with that said, I've seen FTP and the like trunc a file. Adding header and trailers (with dates counts, maybe some summation on amount columns) should take care care of it to insure everything is ok.It adds to the process, both on the way out, and back in, but it's worth it.
No, it's my process and it has worked for a year now. But the powers that be decided to move this process to a different server in a production enviroment without testing it, over my objections. This is a utility cmd shell that gets called by a BCP and verify SP, this is the verify portion. |
|
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2003-04-17 : 10:45:15
|
| I forgot to add that I have narrowed the problem down to the line with the for command. |
|
|
|
|
|
|
|
|
BCP out record count verification