用于从视图创建表的 MSSQL 动态 SQL 仅在通过作业执行时失败

Question

我在创建重复表时遇到问题。 我有一堆视图需要写入表中。

我的方法是，对于每个视图，都应该创建一个表，因此我使用动态SQL实现了它。这样我就不必在每次添加视图时都触摸作业。

我的问题是，只要我在SSSM中自己执行代码，代码就可以正常工作。 一旦我把它放在一个工作中，它就按计划或我自己执行，它就失败了。如果我将动态 SQL 替换为它生成的代码，则作业也会失败。我什至将代码放入存储过程中，然后从作业中执行它并产生相同的结果。

该错误指出它无法将 nvarchar 类型转换为日期时间类型。 我检查了每个视图并单独运行每个视图/表的代码，也没有一次运行所有代码，没有错误。

有谁知道这里出了什么问题？

以下是我使用的动态 SQL 代码：

DECLARE @SQL varchar(max);
SELECT @SQL = COALESCE(@SQL + ' ', '') + 'IF OBJECT_ID(''' + REPLACE(name, 'qry_', 'tbl_') + ''', ''U'') IS NOT NULL DROP TABLE ' + QUOTENAME(REPLACE(name, 'qry_', 'tbl_')) + ';  SELECT * INTO ' + QUOTENAME(REPLACE(name, 'qry_', 'tbl_')) + ' FROM ' + QUOTENAME(name) + ';'
FROM sys.views
WHERE LEFT(name, 4) = 'qry_'
EXEC (@sql);

Answer 1

此SQL未经测试，但是，这应该对您有很大帮助。请注意，我分别执行每个执行。首先，这(实际上(意味着如果动态SQL失败，它将不会传播出去(但是，这可能不是故意的(。不过，我也添加了PRINT语句，以便您可以检查日志并查看实际执行的操作;以及什么观点失败了。PRINT @SQL;被注释掉了，因为(如果我没记错的话(，代理日志只能容纳 8000 或 4000 个字符，因此打印@SQL的值会使其过度膨胀。

DECLARE @SQL nvarchar(MAX), @ViewName sysname;
DECLARE qry_views CURSOR FOR
SELECT [name]
FROM sys.views
WHERE [name] LIKE 'qry[_]%'
OPEN qry_views;
FETCH NEXT FROM qry_views
INTO @ViewName;
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT 'Creating table from View ' + QUOTENAME(@ViewName);
SET @SQL = N'IF OBJECT_ID(''' + REPLACE(@ViewName, N'qry_', N'tbl_') + N''', ''U'') IS NOT NULL DROP TABLE ' + QUOTENAME(REPLACE(@ViewName, N'qry_', N'tbl_')) + N';' + NCHAR(10) + 
N'SELECT *' + NCHAR(10) +
N'INTO ' + QUOTENAME(REPLACE(@ViewName, N'qry_', N'tbl_')) + NCHAR(10) +
N'FROM ' + QUOTENAME(@ViewName) + N';';
--PRINT @SQL;
EXEC sp_executesql @SQL;
FETCH NEXT FROM qry_views
INTO @ViewName;
END
CLOSE qry_views;
DEALLOCATE qry_views;

Answer 2

在作业中，是否使用日期文本调用存储的过程，例如：

exec my_proc @my_param='20180524'

？你不能这么做。首先将值构造为 var，然后使用 var：

declare @my_value date ='20180524'
exec my_proc @my_param=@my_value