[dba-SQLServer] Bulk insert and table names

You guys will love this one.  I am writing .Net stuff to do imports of these
huge text files.  I fill a directory with files to be imported, then the
code gets the file names in that directory and iterates through importing
the files.  I use the bulk copy object to do the insert.  

In this case I had files named using the two digit state code by the people
who generated the source files.

AK.txt
AL.txt
...
WY.txt

I simply stripped off the .txt and use the file name itself as the name of a
temp table into which I import the data.  Once imported and validated, I
then append the temp table to the permanent table and delete the temp table.
I log EVERYTHING from the start / stop time of each operation to the error
text, how many records successfully imported into the temp table etc.

With me so far?

My code takes off and runs.  I end up with TWO files - IN.txt and OR.txt -
that refuse to import.  The error message thrown by the Bulk Copy object is
"cannot access the destination table".  These tables - IN and OR do indeed
exist, nothing wrong with them.  I have code that builds the tables
on-the-fly and the tables are being built but the BulkCopy object cannot
append the data into them.

After an embarrassingly long period of head scratching, checking that I have
sufficient disk space (this is a terabyte array, I have enough space!),
compacting the database etc. it finally dawns on me that perhaps the table
names are verboten.  I changed the name of the input files from IN.txt to
IND.txt and OR.txt to ORE.txt and voila, she runs.

My best guess at this point is that the Bulk Copy object or something it
calls in SQL Server to get its job done views IN and OR as key words and
will not allow some operations to be performed on tables named that.  Notice
that I could execute, using the CMD object in .NET, SQL code to create the
tables and THAT worked.

Anyway, just another piece of trivia to remember - somewhere in the bowels
of SQL Server exists a distaste for table names (and field names as well?)
that are the same as key words.  Which of course leads us to the question,
where is the list of key words that we are not allowed to use?

On a related note, I also create and index a PKID in the tables.  In fact I
quite doing so in the temp tables since it served no useful purpose, however
while I was still doing so I ran into an interesting similar problem.  I was
naming the indexes IX & FieldName, whatever field name might be.  Because I
always used PKID as my field name, SQL Server complained about the index in
the second table I tried to create (I create the permanent and then the temp
table).  It turns out that the index names appear to be stored in a common
collection keyed on the index name and so the second time I tried to create
the same name for an index (in a DIFFERENT table) SQL Server threw an error.
I ended up using IX & TableName & FieldName as the index name and it worked
just fine of course.


John W. Colby
Colby Consulting
www.ColbyConsulting.com