如何从Excel VBA代码执行.SQL文件？

* Update to below * I decided to give up on executing the file and decided to pass a long query string via ADO.Execute with UseTransaction set to True and no recordset returned. I am running into a new problem where only some of the records are inserted although the error code is 0. Total execution time is under a second. Command timeout is 30, Connection timeout is 15.

*更新到下面*我决定放弃执行文件并决定通过ADO.Execute传递一个长查询字符串,并将UseTransaction设置为True并且不返回记录集。我遇到了一个新问题,虽然错误代码为0,但只插入了一些记录。总执行时间不到一秒。命令超时为30,连接超时为15。

Within the query, I am running 6 deletes and then usually over 54 inserts. About 32 inserted rows actually appear in the DB, and the remainder of the query is ignored, again with no error returned. Maybe there is a timeout on the SQL server side, a max string length, or max number of commands it can accept from ADO? If I paste the entire query directly into the SQL Server Management Studio, the whole thing executes no matter how long it is. There seems to be a limitation on the SQL Server side. Ideas? * End Update *

在查询中,我运行6次删除,然后通常超过54次插入。大约有32个插入的行实际出现在DB中,并且忽略了查询的其余部分,同样没有返回错误。也许SQL服务器端存在超时,最大字符串长度或它可以从ADO接受的最大命令数?如果我将整个查询直接粘贴到SQL Server Management Studio中,无论多长时间,都会执行整个查询。 SQL Server端似乎存在限制。想法? *结束更新*

I am using my Excel data to generate a complex query, sending it to a file. The query locks the DB, deletes records, generates a new key using @ variables and uses the @ variables in INSERT statements. Finally, it runs a COMMIT statement. There are at least 100 statements in the .sql file.

我正在使用我的Excel数据生成复杂查询,并将其发送到文件。查询锁定DB,删除记录,使用@变量生成新密钥,并在INSERT语句中使用@变量。最后,它运行COMMIT语句。 .sql文件中至少有100个语句。

After generating the .sql file, I would like to execute the file from the code and receive the success status. I would like to do this in one shot, instead of hitting the DB 100 or more separate times and checking the status of every statement.

生成.sql文件后,我想从代码中执行该文件并获得成功状态。我想一次性执行此操作,而不是单独敲击DB 100或更多次并检查每个语句的状态。

I have tried searching the web for an answer, but no luck tracking it down.

我已经尝试在网上搜索答案,但没有运气跟踪它。

How can I do that?

我怎样才能做到这一点?

If I can't trigger the file, I guess I will have to run each statement individually (lock DB, run individual deletes, run individual inserts), checking success after each. If all is well I COMMIT or if not, do ROLLBACK. Will that work?

如果我无法触发该文件,我想我将必须单独运行每个语句(锁定DB,运行单独删除,运行单个插入),在每个语句之后检查成功。如果一切顺利,我是COMMIT,如果没有,请做ROLLBACK。那会有用吗?

Thank you.

If it helps, here is a sample of my SQL file to get an idea what kinds of statements are included.

如果它有帮助,这里是我的SQL文件的示例,以了解包含哪些类型的语句。

use MY_DB_NAME; 
declare @i_xseq int;
declare @deleteBeforeInsert int;
set @deleteBeforeInsert = 1;

begin transaction MyTransaction;


-- ***********************************   DELETE FUTURE EVENTS
-- Delete all future records using application ID 12 and having a start date after the start date:

if (@deleteBeforeInsert = 1)
BEGIN

  delete from dbo.TableA
where EVENT_XSEQ in (select X_SEQ from dbo.TAbleB where START > CAST('2014-03-26 14:22:01' as datetime) and APPLICATION_ID = 12);
END

-- ************************************ GET NEXT SEQUENCE #
-- begin retrieve new @i_xseq
select @i_xseq=LAST_ID from [dbo].[AKey] WITH (XLOCK,READPAST) where TABLE_NAME = 'TableB';
Update [dbo].[AKey]
Set LAST_ID = LAST_ID + 1
where TABLE_NAME = 'TableB';

select @i_xseq=LAST_ID from [dbo].[AKey] where TABLE_NAME = 'TableB';
-- end retrieve new @i_xseq

-- RUN Inserts (many)
insert into dbo.TableB
  (VAL1, VAL2,X_SEQ)
Values
  (1110, 37673.70, @i_xseq);

-- *************************************  END AND COMMIT
insert into dbo.SCHEDULE_LOG
(DESCRIPTION, X_UPDATED, X_BY)
Values
('Close_Scheduling', CURRENT_TIMESTAMP, 'MY_ID');

COMMIT transaction MyTransaction;

1 个解决方案

#1

In a separate module create a Function or Procedure that writes all the lines in your SQL statement into a string. Than pass that string into an open database connection. You will need to include "Mircosoft ActiveX Data Objects Library" in your project.

在单独的模块中,创建一个函数或过程,将SQL语句中的所有行写入字符串。将该字符串传递给打开的数据库连接。您需要在项目中包含“Mircosoft ActiveX数据对象库”。

Public Function rstSomeName()

Dim sSql As String

sSql = ""
sSql = sSql & "use MY_DB_NAME;                               " & vbCrLf
sSql = sSql & "declare @i_xseq int;                          " & vbCrLf
sSql = sSql & " etc.. to the end of you sql statement        " & vbCrLf


Set rstSomeName= New ADODB.Recordset
rstSomeName.Open sSql, "Your Database Connection String"

End Function