August 3, 2011 at 7:40 am
I have narrow a complicated problem down to one stored procedure that is giving me a real headache.
The whole process involves importing a delimited file to clean free text fields of commas, tabs and anything else that would affect the process later on and it is BCP'd out to another folder. I am however seeing what I can only describe as an echo of another import. To test this I have removed one record from the source file, deleted the output file and run the process only to see the deleted record in the output file.
I have over simplified things and now just import the file in then BCP it out again - no manipulations and the two files are different. I have tried clearing the query cache in case that would have an effect but it didn't.
Does anyone else have any ideas on what the problem could be?
Below is the code being run:-
declare @Cmd varchar(500)
,@Filename char(40)
,@FileNameAndPath varchar(80)
,@BcpDataCmd varchar(2000)
select @FileNameAndPath = 'd:\imports\hrsd\temp\mdata_wrsl_150_20110626-154947-351.txt'
truncate table WholeData
select @Filename = reverse(left(reverse (ltrim(rtrim(@FileNameAndPath))),charindex('\',reverse (ltrim(rtrim(@FileNameAndPath))))-1))
select @Cmd = 'bulk insert WholeData from '+char(39) + ltrim(rtrim(@FileNameAndPath)) + char(39) +' with (datafiletype = ' + char(39) +'char' + char(39)
select @cmd = @cmd + ', rowterminator= '+char(39)+ char(10) + char(39) +')'
exec (@Cmd)
select @BcpDataCmd = 'bcp HRSD.dbo.WholeData out "d:\imports\hrsd\'+ltrim(rtrim(@Filename)) + + '" -c -T'
exec master..xp_cmdshell @BcpDataCmd
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
August 4, 2011 at 1:20 am
Case closed.
I am a klutz and didn't read my code properly. The BCP out command was coming from the prodution database and I was running the code in the development one.
What I was asking SQL to do wasn't what I wanted it to do.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
August 4, 2011 at 5:47 am
Now we understand the avatar. A couple like this one will do that to ya ;-).
August 4, 2011 at 9:02 am
Thanks!
I'm only 25 as well 😛
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
August 4, 2011 at 9:06 am
Stuart Davies (8/4/2011)
Thanks!I'm only 25 as well 😛
Give it another couple months and it'll get ya ;-).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply