DTS - 完全限定表名的问题
我有一个在 SQL Server 2005 数据库上运行的 SQL DTS 包，该包在大多数情况下都可以正常工作。然而，有时它会失败，我不明白为什么。
该包由一个 SQL 任务组成，该任务创建一堆临时表、一堆数据转换，最后还有另一个 SQL 任务来删除第一步中创建的临时表。
该包时不时会失败，因为无法找到转换数据任务中引用的临时表之一，尽管该表是第一步中创建的临时表之一。
我可以看到间歇性失败的任务和所有其他转换数据任务之间的唯一区别是目标的表名称属性 - 它是完全限定的，即。 MyDatabase.dbo.TempTable，而所有其他任务仅将表名称指定为 TempTable。我不知道为什么这个不同，或者如何解决它。我假设这就是问题所在，因为这是该任务与所有其他任务之间唯一不同的地方。
另外，如果我在完整执行包之前手动执行 DTS 的第一步来创建临时表，那么它总是有效。
任何人都可以阐明这里可能出现的问题或者我如何取消目标表名称的限定吗？
谢谢
I have a SQL DTS package running on a SQL Server 2005 database that, for the most part, works properly. However, sometimes it fails and I can't figure out why.
The package consists of an SQL task which creates a bunch of temp tables, a bunch of data transformations, then another SQL task at the end to drop the temp tables created in the first step.
The package fails every now and again because one of the temp tables referenced in a Transform Data Task can't be found, despite the table being one of the temp tables created in the first step.
The only difference I can see between the intermittently failing task and all the other Transform Data Tasks is the Table Name property for the Destination - it is fully qualified, ie. MyDatabase.dbo.TempTable, whereas all of the other tasks just have the table name specified as TempTable. I have no idea why this one is different, or how to fix it. I am assuming that this is the problem as it's the only thing that seems to be different between this one task and all of the others.
Also, if I manually execute the first step of the DTS to create the temp tables before executing the package in full, it always works.
Can anyone shed any light on what the problem may be here or how I can unqualify the destination table name?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您将问题表称为临时表，但看起来好像这是一个实际的表。我猜你打算稍后把桌子放在包裹里？打包失败后，你验证过该表是否存在吗？该表的语法是否正确？
另外，你现在是2005年。为什么不将包升级到SSIS？ SSIS 中的日志提供程序可以记录的不仅仅是错误消息，这可以帮助您了解真正的问题是什么。
You refer to the problem table as a temp table, but it looks as though this is an actual table. I guess that you plan on dropping the table later in the package? After the package fails, have you verified that the table exists? Is the syntax for the table correct?
Also, your on 2005 now. Why not upgrade the package to SSIS? A log provider in SSIS can record much more than just the error message which would help you get down to what the real issue is.
由于它间歇性地失败，因此它使用完全限定名称这一事实不太可能成为问题。我怀疑问题出在它上面的某个步骤中，因此该表永远不会被创建。或者该表是使用 select into 语句而不是 create table 创建的？如果是这样并且没有可供选择的记录，则可能会导致像这样的间歇性问题。
不同的人是否在不同的用户帐户下而不是在工作中运行该包？也许人们无权在该模式中创建表。
Since it fails intermittently, the fact that it uses a fully qualified name is unlikely to be the problem. I would suspect the problem lies in some step above it and thus the table never gets created. Or is this table created using a select into statement rather than a create table? If so and there are no records to select into, that could cause an intermittent problem like this.
Do different people run the package under different user accounts rather than in a job? Perhaps one doesn't have the rights to create a table in that schema.
我发现问题是什么了！事实证明，失败任务的工作流程属性不正确，并且没有将“创建临时表”任务指定为先决条件，因此该任务时不时地在创建关联的目标表之前运行。这种情况并不经常发生，因为该表是第二个创建的表，因此通常在执行下一个任务之前就存在。所有其他任务都具有正确的工作流程属性，但由于某种原因错过了这一任务。
I found out what the problem was! Turns out that the Workflow Properties for the failing task were incorrect and didn't specify the "Create Temp Tables" task as a prerequisite, so every now and again the task was running before the associated destination table had been created. This wasn't happening often as the table was the second one to be created, so was generally present before the next task executed. All of the other tasks had the correct workflow properties, but for some reason this one had been missed out.