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 |
|
nethervoid
Starting Member
12 Posts |
 Posted - 2006-06-27 : 15:19:36
|
| I'm having a problem finding step by step instructions in the few books I have on how to upload to your new table and database once those are set up.I've got some data in an excel file, and I try to run the DTS wizard to create a package to upload the data from excel, but I can't seem to straiten out my data type problems.How do I get my excel data types to match my table data types? It shouldn't be this hard to upload something so rudimentary. =( |
|
|
nethervoid
Starting Member
12 Posts |
Posted - 2006-06-27 : 15:31:29
|
| Oh, the error I'm getting is exactly like the one found in the 'how to import from Excel using DTS' from the FAQ:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=18632[/url]The error is:Error Code 0Error Source=Microsoft VBScript runtime errorError Description:=Type mismatch 'DTSSource |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
nethervoid
Starting Member
12 Posts |
Posted - 2006-06-28 : 11:31:12
|
| That's a pretty good read.I think I've figured out how to get the data in, but now I need to figure out how to take in a row from the excel file, select one field from that row, query for it's ID in another table, replace the field with the ID, then stuff that into a table (where the ID is a Foreign Key of the table - primary for another). And I'd like to do all that in a script inside a stored procedure.I'm not sure if that makes sense. |
|
|
|
nethervoid
Starting Member
12 Posts |
Posted - 2006-06-28 : 11:48:29
|
| I guess I could load the data into a temp table, and pull records one at a time from there. Delete each row as I process it? Sounds like it would take forever for big uploads. |
|
|
|
nethervoid
Starting Member
12 Posts |
Posted - 2006-06-28 : 17:50:48
|
| I was going to try and use OPENRECORDSET, but the server here at work has DisallowAdhocAccess enabled or it does not exist, therefore I can't use this function of TSql.The error when I try to use--code--OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:xxxx\xxxx.xls;HDR=YES',Import_Data)--code--is--code--Add hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.--code--I googled the error, and it's part of some vulnerability in SQL Server, so most SAs just disable it.I was going to use this to populate a table varaible, but it looks like I'm stuck doing temp tables now (because I need to alter the data before I upload it). |
|
|
|
|
|
|
|
|
Inserting records from excel