Unanswered: sp_addlinkedserver, sp_addlinkedsrvlogin problem
I have a problem in SQL 2000.
See if you can simulate this:
Create a new Excel Document on your SQL Server and insert the following data: (... represents another field)
Apples .... Oranges
a .... b
And do this in Query Analyzer:
EXEC sp_addlinkedserver 'blah',
'\\MySQLServer\MyDirectory\New Microsoft Excel Worksheet.xls',
EXEC sp_addlinkedsrvlogin 'blah', false, sa, 'admin', NULL
SELECT * FROM blah...Sheet1$
and you should see 2 column headers (Apples & Oranges) w/ one row of data, a & b.
Now go back into the Excel spreadsheet and add another row:
1 ..... 2
Save, close, run this in Query Analyzer:
exec sp_droplinkedsrvlogin 'blah', NULL
exec sp_dropserver 'blah'
then run stuff at the top again.
Do you now see this:
For some reason Alphanumeric columns work fine until I add an integer, it all of a sudden decides the column is now an Integer and throws away and fields with characters.
It seems that majority rules.
If I put 2 alphanumeric values and one number in a column, the number becomes null.
If I put 2 numberic vaules and one aphanumeric value in a column, the aphanumeric value becomes null.
Any chance I can change the params passed to the sps above to fix this?
There is a key named TypeGuessRows. You will need to modify it so it does more than the 8 rows. Go into regedit -> my computer -> hkey_local_machine\software\microsoft\jet\4.0(or whatever version)\engines\excel
You are correct - I left that out (about IMEX). I went back to my code to confirm and it was there. However, you may still have problems, as I did, that have to be corrected using TypeGuessRows.
The following is an excerpt from a ms article:
"NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric. "