Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2003-02-25 : 11:22:04
|
| I'm using ASP scripts, which hit stored procedures, which in turn go out and query Excel linked servers. These are SELECT queries only at this point, no updates.I have tested successfully from my machine, as db owner, but when I send the URL to my first-line testers, they all get the following IE error --The page cannot be displayedHTTP 500.100 - Internal Server Error - ASP errorInternet Information ServicesError Type:Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: Unspecified error]/scripts/test_action.asp, line 26 Of course, line 26 is the Execute command to run the SPROC.Now, I've drilled down a bit and it appears that linked server security is the issue. However, the linked server security options are very confusing to me. I can't figure out which step I need to take to get my users access to the linked Excel file. The "Impersonate" choice is especially confusing as one can deploy it one of two ways. All I want to do is pass my users through to the linked server -- these people have already been authenticated by Windows and their Windows accounts have been setup as logins on SQL Server.My basic setup:Server:WIN 2kSQL Server 7.0 SP4IIS 5.0Client(s)Win 98, 2k, XPIE 5-6Security:-- NT Challenge/response is enabled for all websites-- Windows authentication only is enabled for SQL Server-- Linked excel "servers" reside on shared directory on same machine as SQL Server-- All users have at least read only permissions to excel linked servers-- All users have Execute permissions to SPROCs-- All users have Read/Execute permissions to web pagesThxEdited by - steelkilt on 02/25/2003 11:24:04 |
|
|
precept
Starting Member
5 Posts |
Posted - 2003-02-25 : 11:51:23
|
| Execl uses the jet provider syntax provided my Microsofthttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_6a44.aspsp_addlinkedserver N'Excel', N'Jet 4.0', N'Microsoft.Jet.OLEDB.4.0', N'c:\data\MySheet.xls', NULL, N'Excel 5.0'GOsp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULLGOi believe the problem lies in the linked server setup this syntax is used to setup the excel linked server to a sql server account...N'ADMIN seems to be a reserved word for getting to excel via Jet and sql server. Just change the sa part to a sql server account you have defined or a domain account that has access to the spreadsheets...get your asp code to open a connection under this account....i believe this may help |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-25 : 12:02:40
|
quote:
I'm using ASP scripts, which hit stored procedures, which in turn go out and query Excel linked servers.
I know you don't want to hear this but it is very unlikely that you WON'T have problems with this setup. The sooner you move the data to SQL Server tables, the better. You can always provide an Excel interface for your users for them to update and add data, but you will encounter relentless heartache if you try to make Excel the base foundation for your data. I know, I've had it happen to me, and it NEVER worked. And even if you get the security issues worked out, an Excel file cannot have two people editing at the same time. |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2003-02-25 : 12:14:55
|
| thx, will look into that.btw, my provider string is Excel 8.0. Can that be causing the problem? |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2003-02-25 : 12:39:43
|
| Rob,What's the best way for moving this data over to SQL in a way that ensures it will be an EXACT mirror of the original XLS? The reason I've stayed with Excel linked server is b/c using DTS to move data over results in some cells converting to NULLs on the SQL side when SQL detects a data type problem, i.e. an excel number field formatted as text. If I can clear this hurdle, then I'll definitely move to SQL tables!thx |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-25 : 13:32:18
|
| If you need to, you can always make the columns varchar's, and then alter them to the proper types after the data has been cleaned up. Or you can use such a structure as a staging area for the data and transfer it to a final table with the proper datatypes. The thing is, if that column is supposed to have only numbers in it, then you need to get non-numeric data out of there. Just because Excel doesn't throw an error doesn't mean it's valid data. If it does indeed need to store alpha data then just leave it as varchar. |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2003-02-25 : 14:59:09
|
| Rob,Could you provide examples of these techniques. My specific problems: "number formatted as text" which excel is green-flagging. Excel wants me to convert all these to valid numbers. If I don't, NULLs in the resulting SQL table.The other, more serious problem is an Excel field that has ID data which takes formats like "808", "568b", "345(1)". Though one would think DTS would just see this field as a text/nvarchar field, in my case it keeps wanting to make it a FLOAT field. Is there hidden code in excel that's telling DTS to use FLOAT?thx |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-25 : 16:20:16
|
If you are creating a new table when importing the Excel sheet, make sure you go into the create table statement and adjust the column names and datatypes to match their Excel equivalents, or to force them to be the type you want. If in doubt, use varchar(255) for any column. Once the table is created it should import fine.You do have to look at the data that these worksheets have before you can force a particular datatype in SQL Server. What I meant earlier about valid or invalid data is that the users who are updating these worksheets need to know that non-numeric data cannot be entered in a numeric column. Now, if those ID values are truly "568b" or "345(1)", then you need to store them in SQL Server as varchar. But if they were mistyped, then you have to have someone clean them up before you import them.I'm not entirely sure about what your circumstances are (can you post them?), but I think you have to look at what you're trying to provide to your users from a 10,000 ft. level. If they have data in Excel sheets, and they want to query them from an ASP script, I don't think that linking them through SQL Server is a great way to do it, especially if the Excel data is not well-structured. By the same token, if they can't (or won't) move the data out of Excel and edit it another way, again I don't think bringing SQL Server into the picture will help. The fact remains that SQL Server expects some kind of format/structure/rules to the data source, which Excel simply doesn't provide or enforce. Having Excel by itself is OK, having SQL Server by itself is OK, but if you want to take advantage of SQL Server here you really have to make it the master data store and have all other data access subservient to it. Excel would therefore have to query the SQL Server and dump the data into a worksheet, and editing that data in Excel would probably have to stop. People cannot just open the Excel sheet and type away at it. If that's what they're used to and won't give it up then I think you'll be better off leaving SQL Server out of the picture. If however you're trying to rope them in to put some structure on these worksheets, then you should probably cut Excel out of the picture, except perhaps as a reporting-only tool. Editing would have to be done elsewhere, like linked Access tables and forms. All the Excel data would be scrubbed, cleansed and then imported one time only into SQL Server.In my case, I had to design an Access DB/app that tracked sales orders through a fulfillment process. The original logging was a series of Excel sheets that were updated once a day with order status and emailed to the appropriate people. A lot of TLC went into these sheets to make it easy for the end users, like color-coding held orders, sorting by date, customer name, shoe size, etc. and a lot of other bullshit that was a lot of extra work for the people logging the orders; none of it affected the data anyway. When it moved to Access everyone bitched because their Excel reports were flat, unformatted, unsorted, and had no colors (I absolutely COULD NOT believe how many people complained about the lack of color). I replied with various renditions of "Tough shit, you've got two hands. Instead of jerking off use them to sort and color it any way you want."  Yeah, they were unhappy, but they gained the ability to immediately see the status of an order, instead of having to wait a day to get an update. Things were spelled properly too, data was more accurate, they didn't have to save copies, lots of other benefits (no email at all...man, was I a hero for that). And they learned how to use Excel better. If you find you have to sell the idea of losing some ease of use, point out the advantages they'll gain by migrating to SQL Server. |
|
|
|
|
|
|
|
|
Excel linked server security is killing me