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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-07 : 11:05:00
|
| Vagesh writes "Hi:I would like a T-SQL procedure that will allow me to import a table from a Access database to SQL Server 7.0 using a DSN. Thanks." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-07 : 11:46:16
|
| You can try a few different methods:1. Use Data Transformation Services (DTS) to transfer from Access to SQL Server.2. Use linked servers to link the Access database to SQL Server3. Conversely, use ODBC to link the SQL Server tables into your Access database, then write queries to update the linked SQL Server tables based on the Access tables4. Use the Upsize to SQL Server wizard included with Access 2000, or download the Access 97 version from [url]http://www.microsoft.com[/url].If this is a one-time import, using DTS is probably the easiest way to go, by using the Import/Export wizard. This can be accessed from the Start menu/Programs/Microsoft SQL Server menu, or from Enterprise Manager. Follow the directions and you should be fine. You don't even need a DSN to perform the import, just browse to the Access database file.You can find more information on DTS and linked servers in Books Online. The ODBC linking and Upsize wizard are documented in the Access help file. |
 |
|
|
oitsubob
Yak Posting Veteran
70 Posts |
Posted - 2002-11-18 : 20:02:30
|
| Hi All.Rather than start a new thread, I figured I'd just jump on this one. I'm working on a task similar to Vagesh's, but it's a little more compled -- or at least I think so :). Instead of importing data from an Access db, I'm trying to pull it out of a Teradata Warehouse. This won't be a one-time deal, but rather a daily sync up of some summarized data. It's probably not as difficult as it seems to me, but this is uncharted territory for me and not I'm exactly sure where to go from here.Let me tell you what I've done so far:· In the DTS Package Designer, I've added a connection to the Teradata ODBC DSN.· Next, I created a SQL Task that contains my query.· At the end of the Select statment, but prior to From I added the following: INTO ServerName.DatabaseName.dbo.TableName and it bombs after I parse the code.The exact error is as follows:[NCR][TERADATA RDBMS] SYNTAX ERROR, EXPECTED SOMETHING LIKE ":" BETWEEN THE "INTO" KEYWORD AND THE WORD "SERVERNAME"At the time, seemed simple enough, I'll just add the ":" as it must be a nuance of Teradata. Wrong... Instead it threw another error:[NCR][TERADATA RDBMS] SYNTAX ERROR: EXPECTED SOMETHING BETWEEN THE WORD "SERVERNAME" AND ".".Aside from the problems I'm having with the Insert part of this query, it executes just fine without the INTO statement. I'm not sure if my problem is with the Teradata Syntax or what, but if anyone has any suggestions, I'd certainly be open to trying them.Thanks in Advance,Bob WestonSBC Pacific Bell |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-18 : 20:37:58
|
| If you're trying to import the data from Teradata into SQL Server, the INTO syntax would screw that up. INTO would create a new table on the Teradata server, not the SQL Server, in addition to causing the syntax errors you're getting. When creating the data transformation, you'll have the option of creating a new SQL Server table to accept the data. Once that's done you can use a regular SELECT statement without INTO to load the SQL Server table, just like what you've experienced. |
|
|
|
oitsubob
Yak Posting Veteran
70 Posts |
Posted - 2002-11-19 : 16:54:15
|
| Thanks Rob...I got it now :) |
|
|
|
|
|
|
|
|
Using DSN to Import Data to SQL Server