Hi,
I am facing the error while calling the below sp from windows batch job using bcp command as below. I am using BCP utility of SQL 2000.
This batch job is working fine till now and suddenly it start giving this issue and I re run the job and it went fine.
Any help on this. Why it is occurring randomly..
Error:
SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
Code in Batch Job:
SET QUERY="set fmtonly off exec %LMDatabase%.dbo.SP_TMP_FLATFILE"
%bcp% %QUERY% queryout %NDMPATH%%File% -c -r -e%ErrorFile% -S%Server% -U%Username% -P%Password%
Stored Procedure
CREATE PROCEDURE [dbo].[SP_TMP_FLATFILE]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * into #TMP_FLATFILE from TMP_FLATFILE
SELECT * FROM #TMP_FLATFILE --This table contains only one column with length of 2000 char.
END
Also observed one major thing if #TMP_FLATFILE contains data as below then I am getting error and if it cotains simple data like 'ABCDEFFG' then no error and job went fine.
10~01~APLEGAL~APLEGAL_HKHKD_LEGALHK-FCY_20150730_114300_INV_FLAT.TXT~16670~49~20150730:114300~~~
Really appreciate you help on this. A bit urgent request.
sorry i copied wrongly ..I updated original post with correct SP.. and issue remains
Dunno if it helps but we use the following additional parameters for ALL BCP operations (never 
know when they might go wrong ...)
BCP Query/Importfile/Etc -e %MyPath%%1.ERR -o %MyPath%%1.OUT -S . -T -N -E -b 100000>>%MyPath%%1.ER2
in particular we catch the Output File with "-o" and redirect any screen output with ">>". When we get a problem they quite often contain a hint that leads us to the problem
I would also want to see exactly what is in each of the parameter variables - e.g. an unexpected space, or punctuation character which might cause the command line to be mis-interpreted
ECHO LMDatabase=]%LMDatabase%[
ECHO bcp=]%bcp%[
and so on.
By all means post the data here - I would certainly like to see it (but I expect you will have to obfuscate it, I would suggest change every lower case letter to "x", every uppercase letter to "X" and every digit to "9", that way any non-alphanumeric characters are preserved)
That said, your data may turn out to be "clean" and benign
You might need to "quote" database name, for example, i.e. change
SET QUERY="set fmtonly off exec %LMDatabase%.dbo.SP_TMP_FLATFILE"
to
SET QUERY="set fmtonly off exec [%LMDatabase%].dbo.SP_TMP_FLATFILE"
^ ^
ditto for %NDMPATH%, %File%, %ErrorFile%, %Server%, %Username% and %Password%
yes my command and everything is correct in batch job.
I just found more details.
This job run in following scenarios :
1.if my sp contains only select statement with out temp table like
select * from TMP_FLATFILE
- my sp contains temp table liek in sp and data in table contains first record and second record as below
--First Record
insert into TMP_FLATFILE values('0000010218')
--Second Record
INSERT INTO TMP_FLATFILE SELECT '10~01~APLEGAL~APLEGAL_HKHKD_LEGALHK-FCY_20150730_114300_INV_FLAT.TXT~16670~49~20150730:114300~~~'
2. my sp contains temp table and data in table contains first record and second record as below and if I removed "AT" from first row value (INV_FLAT.TXT)
--First Record
INSERT INTO TMP_FLATFILE SELECT '10~01~APLEGAL~APLEGAL_HKHKD_LEGALHK-FCY_20150730_114300_INV_FL.TXT~16670~49~20150730:114300~~~'
--Second Record
insert into TMP_FLATFILE values('0000010218')
This job NOT run in following scenarios :
- my sp contains temp table liek in sp and data in table contains first record and second record as below
--First Record
INSERT INTO TMP_FLATFILE SELECT '10~01~APLEGAL~APLEGAL_HKHKD_LEGALHK-FCY_20150730_114300_INV_FLAT.TXT~16670~49~20150730:114300~~~'
--Second Record
insert into TMP_FLATFILE values('0000010218')
So what did you get in the "-o" and ">>" files that I recommended?
Is every parameter JUST A-Z, a-z, 0-9 or "_"? Absolutely NO spaces or other punctuation characters? You have printed them all, as I showed, to be certain that there is no leading or trailing space?
Please post the DDL for TMP_FLATFILE. Saying "This table contains only one column with length of 2000 char." is very ambiguous and could mean all sorts of things, it also tells us nothing about primary keys, indexes foreign keys and so on; any of which might be causing some side effect
I don't really understand what you are describing about your datafile it might be better if you just post the first few lines of your file. You need to use the formatting toolbar here, otherwise they will just be reformated as plain-text and lose any multiple-spaces etc.
But if I have understood you correctly you are saying that the top line does not work, but the bottom line does
INSERT INTO TMP_FLATFILE SELECT '10~01~APLEGAL~APLEGAL_HKHKD_LEGALHK-FCY_20150730_114300_INV_FLAT.TXT~16670~49~20150730:114300~~~'
INSERT INTO TMP_FLATFILE SELECT '10~01~APLEGAL~APLEGAL_HKHKD_LEGALHK-FCY_20150730_114300_INV_FL.TXT~16670~49~20150730:114300~~~'
with is identical except for the "AT" in INV_FLAT.TXT
Presumably if you just execute either of those lines they both work just fine ... in which case the problem is not there, but later in the file, and its just a side effect of that data, at that position, in the file. Unless you have a GO terminator between each line the whole file is going to be executed as a single batch, so any error, anywhere in the file/batch, is going to terminate the whole batch.
Other thing that occurs to me is to "spy" on the server to see what is is being sent to do - i.e. suing SQL Profiler.
That will show the SQL that it is receiving, and you will be able to see exactly which lines have arrived and are attempting to be executed. Maybe that will give a better indication of where the error is.
If nothing is getting to the server this is an error in the ODBC transport to SQL, in which case worth checking you have the latest version of all the relevant drivers etc.