使用ColdFusion将Access数据导入SQL Server

This should be simple. I'm trying to import data from Access into SQL Server. I don't have direct access to the SQL Server database - it's on GoDaddy and they only allow web access. So I can't use the Management Studio tools, or other third-party Access upsizing programs that require remote access to the database.

这应该很简单。我正在尝试将数据从Access导入SQL Server。我没有直接访问SQL Server数据库 - 它在GoDaddy上,它们只允许Web访问。因此,我无法使用Management Studio工具或其他需要远程访问数据库的第三方Access升迁程序。

I wrote a query on the Access database and I'm trying to loop through and insert each record into the corresponding SQL Server table. But it keeps erroring out. I'm fairly certain it's because of the HTML and God knows what other weird characters are in one of the Access text fields. I tried using CFQUERYPARAM but that doesn't seem to help either.

我在Access数据库上写了一个查询,我试图循环并将每条记录插入相应的SQL Server表中。但它不断出错。我很确定这是因为HTML和上帝知道其中一个Access文本字段中有哪些奇怪的字符。我尝试使用CFQUERYPARAM,但似乎也没有帮助。

Any ideas would be helpful. Thanks.

任何想法都会有所帮助。谢谢。

4 个解决方案

#1

Try using the GoDaddy SQL backup/restore tool to get a local copy of the database. At that point, use the SQL Server DTS tool to import the data. It's an easy to use, drag-and-drop graphical interface.

尝试使用GoDaddy SQL备份/还原工具获取数据库的本地副本。此时,使用SQL Server DTS工具导入数据。它是一个易于使用的拖放式图形界面。

#2

What error(s) get(s) thrown? What odd characters are you using? Are you referring to HTML markup, or extended (eg UTF-8) characters?

抛出什么错误?你用的是什么奇怪的字符?您是指HTML标记还是扩展(例如UTF-8)字符?

If possible, turn on Robust Error Reporting.

如果可能,请启用“稳健错误报告”。

If the problem is the page timing out, you can either increase the timeout using the Admin, using the cfsetting tag, or rewrite your script to run a certain number of lines, and then forward to itself at the next start point.

如果问题是页面超时,您可以使用管理员,使用cfsetting标记增加超时,或者重写脚本以运行一定数量的行,然后在下一个起始点转发给自己。

#3

You should be able to execute saved DTS packages in MS SQL Server from the application server's command line. Since this is the case, you can use <cfexecute> to issue a request to DTSRUNNUI.EXE. (See example) This is of course assuming you are on a server where the command is available.

您应该能够从应用程序服务器的命令行在MS SQL Server中执行已保存的DTS包。因为这种情况,您可以使用向DTSRUNNUI.EXE发出请求。 (参见示例)这当然是假设您在命令可用的服务器上。

#4

-1

It's never advisable to loop through records when a SQL Update can be used.

在可以使用SQL Update时,永远不建议循环记录。

It's not clear from your question what database interface layer you are using, but it is possible with the right interfaces to insert data from a source outside a database if the interface being used supports both types of databases. This can be done in the FROM clause of your SQL statement by specifying not just the table name, but the connect string for the database. Assuming that your web host has ODBC drivers for Jet data (you're not actually using Access, which is the app development part -- you're only using the Jet database engine), the connect string should be sufficient.

从您的问题中不清楚您正在使用什么数据库接口层,但如果使用的接口支持两种类型的数据库,则可以使用正确的接口从数据库外部的源插入数据。这可以在SQL语句的FROM子句中完成,不仅指定表名,还指定数据库的连接字符串。假设您的Web主机具有Jet数据的ODBC驱动程序(您实际上并未使用Access,这是应用程序开发部分 - 您只使用Jet数据库引擎),连接字符串应该足够了。

EDIT: If you use the Jet database engine to do this, you should be able to specify the source table something like this (where tblSQLServer is a table in your Jet MDB that is linked via ODBC to your SQL Server):

编辑:如果您使用Jet数据库引擎执行此操作,您应该能够指定类似于此的源表(其中tblSQLServer是Jet MDB中通过ODBC链接到SQL Server的表):

INSERT INTO tblSQLServer (ID, OtherField ) 
SELECT ID, OtherField
FROM [c:\MyDBs\Access.mdb].tblSQLServer

The key point is that you are leveraging the Jet db engine here to do all the heavy lifting for you.

关键是你在这里利用Jet数据库引擎为你做所有繁重的工作。

#1