Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Posts
    4

    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',
    'Jet 4.0',
    'Microsoft.Jet.OLEDB.4.0',
    '\\MySQLServer\MyDirectory\New Microsoft Excel Worksheet.xls',
    NULL,
    'Excel 8.0'
    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:
    Apples Bananas
    NULL NULL
    1.0 2.0

    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.

    Anybody have any ideas?

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    You have stumbled across a problem with how, in this case, data types are determined in excel files. Add another 20 lines of alpha only and let me know if you still have the problem.

  3. #3
    Join Date
    Dec 2002
    Posts
    4
    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.

    This sux!!!

    Any chance I can change the params passed to the sps above to fix this?

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    You have to modify a registry setting. Let me know if you want specifics.

  5. #5
    Join Date
    Dec 2002
    Posts
    4
    yes please

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    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

    Let me know if you need additional information.

  7. #7
    Join Date
    Dec 2002
    Posts
    4
    rnealejr,

    I appreciate your help, but I found another solution:

    change

    'Excel 8.0'

    to

    'Excel 8.0;HDR=YES;IMEX=1'


    Once again, thanks for your help

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    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. "

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •