Best way of creating 50 excel based linked servers

Question

Best way of creating 50 excel based linked servers

Quinn-793124

SSCrazy

Points: 2785
More actions
September 19, 2008 at 8:34 am

#212895

I have 50 excel spreadsheets that need to be updated. I was able to create one linked server as a test and now I need to create the rest.
Here is how I did it.
sp_addlinkedserver EXCELTEST,
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'\\gisserver\mxdstmp\6322done.xls',
NULL,
'Excel 8.0;'
My idea is to put a list of the excel spreadsheets in a SQL table and create a cursor to that table and do the sp_Addlinkedserver for each row in the table substituting in the server name and data source.
insert into Directory
EXEC wsgis..xp_cmdshell 'dir \\gisserver\mxdstmp\*.xls /b /s'
Then I need to an the same update statement on the 50 spreadsheets. My idea there was to put a list of linked servers into another table using sp_linkedservers. I am not sure how to do that.
The last step would be to create another cursor for the table with the linked servers in and for every row execute this update command with different parameters each time.
update EXCELTEST...[DataSet] set school = a.SD
from EXCELTEST...[test] b inner join parcels a
on stuff(stuff(b.parcel_id, 3, 0, '-'), 7, 0, '-') = a.parcel_id
I created a data table in each spreadsheet called dataset. When I do a select * from it it says that there too many column defined. The updates on it work just fine.
I also need to update an SQL table from each spreadsheet.
I know this was long but I thought I would throw is out there to look at and get some input on what was the best way to do this. I know everyone says to steer clear of cursors so if there is a better way I would like to know.
Thanks much.
Quinn

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 1

The cursor thing isn't a big deal for adding these. It's a one time loop.

For the programming, you want to avoid them and just write out the statements. Not sure about the SELECT *, but what is the size of the spreadsheets?

Quinn-793124 SSCrazy Points: 2785 More actions · Answer 2

There are 29 columns in the defined area of the spreadsheet.

When I do a select on the name on the worksheet '6322missing$' I get all of the columns

exec sp_tables_ex'exceltest'

NULLNULL'6322missing$'TABLENULL

NULLNULL'6322missing$'Print_TitlesTABLENULL

NULLNULLDatabaseTABLENULL

NULLNULLTestTABLENULL

Here is the result of Select * from EXCELTEST...[test]

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCELTEST" returned message "Too many fields defined.".

Msg 7320, Level 16, State 2, Line 1

Cannot execute the query "SELECT `Tbl1002`.`PARCEL_ID` AS `Col1004`,`Tbl1002`.`TOWN` AS `Col1005`,`Tbl1002`.`RANG` AS `Col1006`,`Tbl1002`.`SECT` AS `Col1007`,`Tbl1002`.`DESC_` AS `Col1008`,`Tbl1002`.`ACRES` AS `Col1009`,`Tbl1002`.`PIDCODE` AS `Col1010`,`Tbl1002`.`PIDCODE2` AS `Col1011`,`Tbl1002`.`TWP/CITY` AS `Col1012`,`Tbl1002`.`SCHOOL` AS `Col1013`,`Tbl1002`.`WATER` AS `Col1014`,`Tbl1002`.`HOSP` AS `Col1015`,`Tbl1002`.`FD` AS `Col1016`,`Tbl1002`.`****` AS `Col1017`,`Tbl1002`.`COM DIS` AS `Col1018`,`Tbl1002`.`CC1` AS `Col1019`,`Tbl1002`.`CA1` AS `Col1020`,`Tbl1002`.`CR1` AS `Col1021`,`Tbl1002`.`CC2` AS `Col1022`,`Tbl1002`.`CA2` AS `Col1023`,`Tbl1002`.`CR2` AS `Col1024`,`Tbl1002`.`CC3` AS `Col1025`,`Tbl1002`.`CA3` AS `Col1026`,`Tbl1002`.`CR3` AS `Col1027`,`Tbl1002`.`CC4` AS `Col1028`,`Tbl1002`.`CA4` AS `Col1029`,`Tbl1002`.`CR4` AS `Col1030`,`Tbl1002`.`LAKE #` AS `Col1031`,`Tbl1002`.`F29` AS `Col1032`,`Tbl1002`.`F30` AS `Col1033`,`Tbl1002`.`F31` AS `Col1034`,`Tbl1002`.`F32` AS `Col1035`,`Tbl1002`.`F33` AS `Col1036`,`Tbl1002`.`F34` AS `Col1037`,`Tbl1002`.`F35` AS `Col1038`,`Tbl1002`.`F36` AS `Col1039`,`Tbl1002`.`F37` AS `Col1040`,`Tbl1002`.`F38` AS `Col1041`,`Tbl1002`.`F39` AS `Col1042`,`Tbl1002`.`F40` AS `Col1043`,`Tbl1002`.`F41` AS `Col1044`,`Tbl1002`.`F42` AS `Col1045`,`Tbl1002`.`F43` AS `Col1046`,`Tbl1002`.`F44` AS `Col1047`,`Tbl1002`.`F45` AS `Col1048`,`Tbl1002`.`F46` AS `Col1049`,`Tbl1002`.`F47` AS `Col1050`,`Tbl1002`.`F48` AS `Col1051`,`Tbl1002`.`F49` AS `Col1052`,`Tbl1002`.`F50` AS `Col1053`,`Tbl1002`.`F51` AS `Col1054`,`Tbl1002`.`F52` AS `Col1055`,`Tbl1002`.`F53` AS `Col1056`,`Tbl1002`.`F54` AS `Col1057`,`Tbl1002`.`F55` AS `Col1058`,`Tbl1002`.`F56` AS `Col1059`,`Tbl1002`.`F57` AS `Col1060`,`Tbl1002`.`F58` AS `Col1061`,`Tbl1002`.`F59` AS `Col1062`,`Tbl1002`.`F60` AS `Col1063`,`Tbl1002`.`F61` AS `Col1064`,`Tbl1002`.`F62` AS `Col1065`,`Tbl1002`.`F63` AS `Col1066`,`Tbl1002`.`F64` AS `Col1067`,`Tbl1002`.`F65` AS `Col1068`,`Tbl1002`.`F66` AS `Col1069`,`Tbl1002`.`F67` AS `Col1070`,`Tbl1002`.`F68` AS `Col1071`...

Thanks

Quinn

Ross McMicken SSCarpal Tunnel Points: 4438 More actions · Answer 3

You may need to specify the columns. The error message indicates you've exceeded the limit of the Jet driver. My guess is that select * from the spreadsheet tries to retrieve all the columns, not just the one's with data.