bcp -or- information_schema bug?

[RobKaratzas]

Hi Folks

I have written a generic import script, which crawls directories using
pattern matching on files/file types.

Recently, I've experienced an odd problem with either bcp -or- a real
information_schema bug?

My best guess is that this is an information schema bug (possibly some type
of a caching issue where things aren't flushed properly - there exists the
likelihood of there being as many as a dozen of these same files being
processed in the same cycle).

Here is my example:

bcp "exec TIC.dbo.XMLBOM
'XLSImport_PartsBOM_CD3SN','XLSImport_EngrsBOM_CD3SN'" QUERYOUT "C:\psplm
NEW\TIC\FileExists\XML\XLSImport_PartsBOM_CD3SN.TMP" -U "sa" -P "mypwd" -S
Karatzas14\PS -c -r -t

fails thinking that the fields: Series, Cab Style and Wheelbase exist

whereas SQL Query Analyzer:

exec TIC.dbo.XMLBOM 'XLSImport_PartsBOM_CD3SN','XLSImport_EngrsBOM_CD3SN'

works great (these 3 fields don't exist)

Here's the sproc:

CREATE PROCEDURE dbo.XMLBOM
@vPartTableName varchar(500),
@vEngTableName varchar(500)
AS
DECLARE @SQL nvarchar(4000)
--DECLARE @columnexists smallint
--SET @columnexists=0
SET @SQL= 'SELECT PartName=Case When len(Part.[Part Prefix])=0 then
Part.[Part Base]+Char(45)+Part.[Part Suffix] else Part.[Part
Prefix]+Char(45)+Part.[Part Base]+Char(45)+Part.[Part Suffix] end,'
SET @SQL=@SQL + 'Part.[Part Description] as PartDesc,Part.[Function
Activity] as FUNCUsageCode,Part.[Corp Prod Sys Class (CPSC)] as
CPSCUsageCode,Part.[CPSC-Seq] as SEQUsageCode,'
SET @SQL=@SQL + 'Part.[Notice Number] as NoticeNumber,Part.[Notice Status]
as NoticeStatus,Part.[Function Status] as FunctionStatus,Part.[Design Date]
as DesignDate,Part.[UCC 7 Digit XREF] as UCC7DigitXREF,'
SET @SQL=@SQL + 'Part.[Product Type] as ProductType,Part.[Vehicle Line] as
VehicleLine,'
IF EXISTS (select column_name from master.information_schema.columns WITH
(NOLOCK) where table_name=@vPartTableName and column_name='Series')
--SET @columnexists=(select count(*) from temp.information_schema.columns
WITH (NOLOCK) where table_name=@vPartTableName and column_name='Series')
--IF @columnexists=1
BEGIN
SET @SQL=@SQL + 'Part.Series,Part.[Cab Style] as CabStyle,Part.Wheelbase as
Wheelbase,'
END

SET @SQL=@SQL + 'Part.Engine as Engine,Part.Transmission as Transmission,'
SET @SQL=@SQL + 'Part.Emission as Emission,Part.[Air Cond] as
AirCond,Part.Driveline as Driveline,Part.[Front Seat] as
FrontSeat,Part.[Open] as [Open],Part.[Minor String] as MinorString,'
SET @SQL=@SQL + 'Part.[Function Engr Activity] as
FunctionEngrActivity,Part.[Function Engr Code] as
FunctionEngrCode,Part.[Design Activity] as DesignActivity,Part.[Design Engr
Activity] as DesignEngrActivity,'
SET @SQL=@SQL + 'Part.[Design Engr Code] as DesignEngrCode,Part.[Model Yr -
PMT - Module] as ModelYrPMTModule,Part.[Part Type] as PartType,Part.[Part
Class] as PartClass,Part.Authority,'
SET @SQL=@SQL + 'Part.[C/F] as CF,Part.Homologation,Part.[Safety Indicator]
as SafetyIndicator,Part.MRPI,Part.FRPI,Part.Prototype,Part.[Prototype Volume]
as PrototypeVolume,Part.[Procurement Activities] as ProcurementActivities,'
SET @SQL=@SQL + 'Part.[Source Plant String] as
SourcePlantString,Part.Buy,Part.Make,Part.[Using Plant String] as
UsingPlantString,Part.[Effective In Point] as
EffectiveInPoint,Part.[Effective In Date] as EffectiveInDate,'
SET @SQL=@SQL + 'Part.[Effective Out Point] as
EffectiveOutPoint,Part.[Effective Out Date] as EffectiveOutDate,Part.[Color
Indicator] as ColorIndicator,Part.[Color Suffixes] as
ColorSuffixes,Part.[Levels of Assembly] as LevelsofAssembly,'
SET @SQL=@SQL + 'Part.[Goes into] as Goesinto,Part.Quantity,Part.[Alpha
Quantity] as AlphaQuantity,Part.[English Weight] as
EnglishWeight,Part.[Weight Type] as WeightType,Part.[English UOM] as
EnglishUOM,'
SET @SQL=@SQL + 'Part.[Metric Weight UOM] as MetricWeightUOM,Part.[Metric
Weight] as MetricWeight,Part.[Material Gauge] as MaterialGauge,Part.[Assembly
Control] as AssemblyControl,Part.[Critical Torque] as CriticalTorque,'
SET @SQL=@SQL + 'Part.Open1,Part.[Torque Nom] as TorqueNom,Part.[Torque Var]
as TorqueVar,Part.[Torque STC] as TorqueSTC,Part.[Service Part No#] as
ServicePartNo,Part.[Stock Disp Service] as StockDispService,'
SET @SQL=@SQL + 'Part.[SAR Indicator] as SARIndicator,Part.[Continue
Service] as ContinueService,Part.[Finis Code] as FinisCode,Part.[Service
Indicator] as ServiceIndicator,Part.[Functional Remarks] as
FunctionalRemarks,'
SET @SQL=@SQL + 'Part.[Shown on Drawing Part No#] as
ShownonDrawingPartNo,Part.[CAD Data Collector] as CADDataCollector,Part.[CAD
Indicator] as CADIndicator,Part.[Drawing Date] as DrawingDate,'
SET @SQL=@SQL + 'Part.[Drawing Completed] as DrawingCompleted,Part.[Drawing
Affected] as DrawingAffected,Part.[Drawing Size] as DrawingSize,Part.[3D
Shown on Indicator] as ThreeDShownonIndicator,'
SET @SQL=@SQL + 'Part.[2D Shown on Indicator] as
TwoDShownonIndicator,Part.[Vehicle Lines] as VehicleLines,Part.[Partner
Part(s)] as PartnerParts,Part.[Usage Notice] as UsageNotice,Part.[Release
Date] as ReleaseDate,'
SET @SQL=@SQL + 'Part.[Release Analyst] as ReleaseAnalyst,Func.[Engr
Activity] as FuncEngrActivity, Func.[Engr Name] as FuncEngrName, Func.[Engr
Phone Number] as FuncBusinessPhone,'
SET @SQL=@SQL + 'Func.[WERS Last Change Date] as FuncWERSLastChangeDate,
Desgn.[Engr Activity] AS DesgnEngrActivity, Desgn.[Engr Name] AS
DesgnEngrName, Desgn.[Engr Phone Number] AS DesgnBusinessPhone, '
SET @SQL=@SQL + 'Desgn.[WERS Last Change Date] AS DesgnWERSLastChangeDate
FROM '
SET @SQL=@SQL + @vEngTableName + ' Desgn WITH (NOLOCK) LEFT OUTER JOIN '
SET @SQL=@SQL + @vPartTableName + ' Part WITH (NOLOCK) ON Desgn.[Engr
Code]=Part.[Design Engr Code] LEFT OUTER JOIN '
SET @SQL=@SQL + @vEngTableName + ' Func WITH (NOLOCK) ON Func.[Engr
Code]=Part.[Function Engr Code] for xml raw'
EXEC(@SQL)
GO

never seen anything like this ever (where query analyzer is correct, but
calling same sproc from BCP fails)

[RobKaratzas]

sorry, I forgot this on the 1st post, here's the err:

SQLState = S0022, NativeError = 207
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
'Series'.
SQLState = S0022, NativeError = 207
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
'Cab Style'.
SQLState = S0022, NativeError = 207
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
'Wheelbase'.

for some reason, calling information_schema repeatedly (for column info)
from bcp, the processing is mangled (again these fields really don't exist in
this instance of processing this file at this point in time)

rob

[Bonj]

Might not fix it, but just thought I'd let you know something that's tripped
me up before:
BCP absolutely *hates* column names with spaces in, and column names that
have to be surrounded by [] because they're keywords. So much so that it
flatly refuses to deal with them.

[RobKaratzas]

Thanks Bonj

I've seen this before. However, if you look at the sproc, I've taken care of
this.

Basically, this routine is new (for processing car info) and the truck
importer has been working great. Now, some of the car info doesn't have the 3
fields mentions and this is really tripping things up.

It's like: car 'ABC', file#1 has the 3 fields, processes OK. Then car 'ABC'
file#2 doesn't have these 3 fields, but information_schema still says yes it
does... (Almost like the bcp execution is getting the previous (in just ms)
definition of the table and not the latest.)

this is weird, I'm thinking maybe ODBC (used by bcp) is possibly caching the
definition as well.

[RobKaratzas]

beginning to believe this is a (really bad bug). But it appears to be a BCP
bug, since using sysobjects avoid information_schema.

replace the check in the sproc to:

IF EXISTS (SELECT * FROM TIC..syscolumns sc INNER JOIN TIC..sysobjects so ON
sc.id=so.id WHERE so.name=@vPartTableName and sc.name='Series')

BEGIN
SET @SQL=@SQL + 'Part.Series,Part.[Cab Style] as CabStyle,Part.Wheelbase as
Wheelbase,'
END

and problem persists (except in 1 case/file pick-up) it actually worked.

this is really bad... this means that something is caching info for bcp,
which is a major security issue in my book.

rob

[Vaxman]

BCP runs the procedure twice (once to get column counts and types I
assume) and again to get the data. The first time creates your columns,
the second time fails.

Example:

create table bcp_test(col1 int)
insert bcp_test select 1

Go

create procedure p_bcp_test as
begin
update bcp_test
set col1 = col1 + 1
select col1 from bcp_test
end

master..xp_cmdshell 'bcp "exec OD..p_bcp_test" queryout
"c:\temp\bcp_test.out" -c -T -S localhost'
master..xp_cmdshell 'type c:\temp\bcp_test.out'

output
--------
3

master..xp_cmdshell 'bcp "exec OD..p_bcp_test" queryout
"c:\temp\bcp_test.out" -c -T -S localhost'
master..xp_cmdshell 'type c:\temp\bcp_test.out'

output
------
5

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[RobKaratzas]

Hi Vaxman

I understand what you're saying here (and agree).

My issue is that the information from both information_schema AND syscolumns
appears to be totally UNRELIABLE and UNPREDICTABLE from BCP.

I'm totally stumped on this one. In my 28 yrs in this biz. working with
Oracle, MySQL, DB2, INGRESS, ACCESS, .... I've never seen anything so
baffling/screwed up in my life.

(Works absolutely correct in everything but bcp.)

Rob

[RobKaratzas]

Unfortunately, I think we have a legit bug (I'll have to try this solution in
the lab on 2005 as well, when I have time). It appears to be a timing or
caching related issue when you're altering table columns (REPEATEDLY). But I
have NOT figured out exactly what the root of the issue is.

After 6 various attempts, I finally found an accurate workaround.

In a separate call to bcp (prior to joining tables and generating xml raw
AND instead of querying information_schema or syscolumns during the bcp call
that generates the XML), I have to query (either syscolumns or
information_schema separately) WHICH is 100% accurate. QUERYING syscolumns or
information_schema during the SAME bcp call was only about 20% correct (AT
BEST), when a table changed from let's say 50 to 47 columns. It was ALWAYS
THIS transition which tripped bcp (but NOT SQL Query Analyzer, etc.).

Here's FRAGMENT of the (CSV, XML, XLS, etc.) grabbing script which WORKS:

(basically, this script chunk reads .Xls files, grabbing the worksheets
associated with what's needed to generate complex (parent-child) XML for
bills of material. In the end, you get XML files for Cars, Trucks, SUVs, etc.
to be INSERT INTO the DB via XML means.)

-----------------------------------
:
Case "BOM"
Dim TblNameParts, TblNameEngrs
TblNameParts = "XLSImport_Parts" & getName(LgName) & chr(Int((26 * Rnd)
+ 1) + 64)
mycmd = mycmd & TblNameParts
mycmd = mycmd & " FROM OPENROWSET (" & Chr(39) & Chr(39) &
"Microsoft.Jet.OLEDB.4.0" & Chr(39) & Chr(39) & ","
mycmd = mycmd & Chr(39) & Chr(39) & "Excel 8.0;Database=" & oFile.Path

mycmd = mycmd & Chr(39) & Chr(39) & "," & Chr(39) &
Chr(39) & "SELECT * FROM [Parts$]"
mycmd = mycmd & Chr(39) & Chr(39) & ")" & ";"
mycmd = mycmd & "SELECT * INTO "
TblNameEngrs = TblNameParts
TblNameEngrs = Replace(TblNameParts,"Parts","Engrs",1,-1,vbTextCompare)
mycmd = mycmd & TblNameEngrs
mycmd = mycmd & " FROM OPENROWSET (" & Chr(39) & Chr(39) &
"Microsoft.Jet.OLEDB.4.0" & Chr(39) & Chr(39) & ","
mycmd = mycmd & Chr(39) & Chr(39) & "Excel 8.0;Database=" & oFile.Path

mycmd = mycmd & Chr(39) & Chr(39) & "," & Chr(39) &
Chr(39) & "SELECT * FROM [Engineers$]"
mycmd = mycmd & Chr(39) & Chr(39) & ")" & ";"
mycmd = mycmd & "exec XLSImport_PartsBOM_DropCols "
& Chr(39) & Chr(39) & TblNameParts & Chr(39) & Chr(39)
mycmd = mycmd & Chr(39) &";"
mycmd = mycmd & "Exec (@str)" & Chr(34)
oLogFile.WriteLine("mycmd: " & mycmd)
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.LogEvent 4, "Started XLS script for " & LgName
WshShell.Run mycmd, 0, TRUE

'bcp it turns out, is TOTALLY UNRELIABLE for return values from
information_schema AND syscolumns, this info is accurate if dumped to a file
FIRST to determine field existence
'example: SELECT FIELDEXISTS=CASE WHEN COUNT(*)>0 THEN 1 ELSE 0 END FROM

TIC..syscolumns sc INNER JOIN TIC..sysobjects so ON sc.id=so.id WHERE

so.name='XLSImport_PartsBOM_S197AT' and sc.name='Wheelbase'
'
mycmd = "bcp " & Chr(34) & "SELECT FIELDEXISTS=CASE WHEN COUNT(*)>0
THEN 1 ELSE 0 END FROM TIC.dbo.syscolumns sc INNER JOIN TIC..sysobjects so ON
sc.id=so.id WHERE so.name=" & Chr(39) & TblNameParts & Chr(39) & " and
sc.name=" & Chr(39) & "Wheelbase" & Chr(39) & Chr(34) & " QUERYOUT " &
Chr(34) & sXPickupFile & TblNameParts & ".OUT" & Chr(34) & " -U " & Chr(34) &
"sa" & Chr(34) & " -P " & Chr(34) & "mypwd" & Chr(34) & " -S Karatzas14\PS -c
-r -t"
oLogFile.WriteLine("mycmd: " & mycmd)
WshShell.Run mycmd, 0, TRUE
Dim objFileSystem, objInputFile, inputData
Set objFileSystem = CreateObject("Scripting.fileSystemObject")
Set objInputFile = objFileSystem.OpenTextFile(sXPickupFile &
TblNameParts & ".OUT", FILE_FOR_READING)
inputData = objInputFile.ReadAll
oLogFile.WriteLine("FIELDEXISTS: " & inputData)
objInputFile.Close
Set objFileSystem = Nothing
Set objInputFile = Nothing
If inputData="1" Then
'
'example: mycmd = 'bcp "exec TIC..XMLBOM
'XLSImport_PartsBOM_2004_10_21_000001SN','XLSImport_EngrsBOM_2004_10_21_000001SN'"
QUERYOUT "C:\psplm NEW\TIC\FileExists\XLS\XLS2\ROB3.XML" -U "sa" -P "mypwd"

-S Karatzas14\PS -c -r -t

mycmd = "bcp " & Chr(34) & "exec TIC.dbo.XMLBOM " & Chr(39) &
TblNameParts & Chr(39) & "," & Chr(39) & TblNameEngrs & Chr(39) & Chr(34) & "
QUERYOUT " & Chr(34) & sXPickupFile & TblNameParts & ".TMP" & Chr(34) & " -U
" & Chr(34) & "sa" & Chr(34) & " -P " & Chr(34) & "mypwd" & Chr(34) & " -S

Karatzas14\PS -c -r -t"

oLogFile.WriteLine("mycmd: " & mycmd)
WshShell.Run mycmd, 0, TRUE
'example: mycmd = 'copy/b rootopen.txt+ROB3.TMP+rootcls.txt ROB4.XML
mycmd = "%COMSPEC% /c copy/b " & Chr(34) & sXPickupFile &
"rootopen.txt" & Chr(34) & "+"& Chr(34) & sXPickupFile & TblNameParts &
".TMP" & Chr(34) & "+" & Chr(34) & sXPickupFile & "rootcls.txt" & Chr(34) & "
" & Chr(34) & sXPickupFile & TblNameParts & ".XML" & Chr(34)
oLogFile.WriteLine("mycmd: " & mycmd)
WshShell.Run mycmd, 0, TRUE
Else
'
'example: mycmd = 'bcp "exec TIC..XMLBOM
'XLSImport_PartsBOM_2004_10_21_000001SN','XLSImport_EngrsBOM_2004_10_21_000001SN'"
QUERYOUT "C:\psplm NEW\TIC\FileExists\XLS\XLS2\ROB3.XML" -U "sa" -P "mypwd"

-S Karatzas14\PS -c -r -t

mycmd = "bcp " & Chr(34) & "exec TIC.dbo.XMLBOMn " & Chr(39) &
TblNameParts & Chr(39) & "," & Chr(39) & TblNameEngrs & Chr(39) & Chr(34) & "
QUERYOUT " & Chr(34) & sXPickupFile & TblNameParts & ".TMP" & Chr(34) & " -U
" & Chr(34) & "sa" & Chr(34) & " -P " & Chr(34) & "mypwd" & Chr(34) & " -S

Karatzas14\PS -c -r -t"

oLogFile.WriteLine("mycmd: " & mycmd)
WshShell.Run mycmd, 0, TRUE
'example: mycmd = 'copy/b rootopen.txt+ROB3.TMP+rootcls.txt ROB4.XML
mycmd = "%COMSPEC% /c copy/b " & Chr(34) & sXPickupFile &
"rootopen.txt" & Chr(34) & "+"& Chr(34) & sXPickupFile & TblNameParts &
".TMP" & Chr(34) & "+" & Chr(34) & sXPickupFile & "rootcls.txt" & Chr(34) & "
" & Chr(34) & sXPickupFile & TblNameParts & ".XML" & Chr(34)
oLogFile.WriteLine("mycmd: " & mycmd)
WshShell.Run mycmd, 0, TRUE
End If
WshShell.LogEvent 4, "Ended XLS script for " & LgName
Set TblNameParts = Nothing
Set TblNameEngrs = Nothing
Set inputData = Nothing
:
:

Anyways, I'd be happy to send the script off for anyones testing.

Unfortunately, the manufacturing data is confidential (+ there's dozens of
files being grabbed,each being about 20-30MB each).

Rob