使用SQL Server寫store procedure用SSIS排程執行，在某次排程執行出現錯誤訊息：
「錯誤:執行查詢 "exec dbhenry.dbo.SPprj01"失敗,發生下列錯誤:"資料行名稱或提供的數值數量與資料表定義不相符。"。可能的失敗原因:查詢發生問題、未正確設定"ResultSet" 屬性、未正確設定參數,或未正確建立連接。」
下面有store produre和SSIS內容：
我當時單純 drop 掉 TB_out01再重新執行排程就正常了，我大概知道如果執行整個store procedure時SQL Server會預設哪裡有錯誤就擋掉整個執行，但一段一段手動執行的話，可能就沒問題。
想請問是什麼原因造成這個狀況？可以查哪方面的內容，有這類的說明？
store procedure：
USE db_henry
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER PROCEDURE [dbo].[SP_prj01]
AS
BEGIN
declare @Cmd varchar(1000), @Date char(8), @Total varchar(10)
set @Date= Convert(char(8), getdate(), 112)
set @Total (select count(*) from TB_prj01)
drop table if exists TB_out01
create table TB_out01(s1 int identity(1,1), c1 varchar(20), c2 varchar(20))
insert into TB_out01 select 'CREATE' +@Date+'CREATE', ''
insert into TB_out01
select 'ID', '姓名'
insert into TB_out01
select * from TB_prj01
insert into TB_out01 select 'TOTAL' +@Total+'TOTAL', ''
alter table TB_out01 alter column c1 [char] (20)
alter table TB_out01 alter column c2 [char] (20)
set @Cmd = 'bcp "select c1, c2 from db_henry.dbo.TB_out01 order by s1 " queryout D:\prj\prj01\prj01.txt -S localhost -c -T -t, '
exec master..xp_cmdshell @Cmd
drop table if exists TB_out01
END
SSIS：
用一個「執行SQL工作」元件，
Connection Type：OLE DB
Connection：某Server
SQL Source Typ：直接輸入
SQL Statement：exec db_henry.dbo.SP_prj01
已邀請的邦友 {{ invite_list.length }}/5
資料行名稱或提供的數值數量與資料表定義不相符。
這段就是告訴你新增資料的資料欄位及對應的資料欄數不相符合~
所以你先檢查你的PROCEDURE裡面的SQL~
把SQL列印出來~檢查是否動態欄位異常~
可是我的錯誤出現在執行整個store procedure，如果只執行BEGIN...END裡面的程式，會是正常的。
我其他專案也都這樣寫，基本上都正常執行，偶爾會出現這次的問題。
這是 TB_out01 資料表的內容
恩@@...加上Go呢?
drop table if exists TB_out01
Go
create table TB_out01(s1 int identity(1,1), c1 varchar(20), c2 varchar(20))
Go
看起來GO不能這樣用
錯誤訊息：
程序SP_prj01接近TB_out01之處的語法不正確。
資料庫中已經有一個名為TB_out01的物件。
必須宣告純量變數"@Date"。
必須宣告純量變數"@Total"。
必須宣告純量變數"@Cmd"。
Go的指令是先執行再說~
你可以改成這樣@@
BEGIN
drop table if exists TB_out01
create table TB_out01(s1 int identity(1,1), c1 varchar(20), c2 varchar(20))
Go
declare @Cmd varchar(1000), @Date char(8), @Total varchar(10)
set @Date= Convert(char(8), getdate(), 112)
set @Total (select count(*) from TB_prj01)
insert into TB_out01 select 'CREATE' +@Date+'CREATE', ''
insert into TB_out01
select 'ID', '姓名'
insert into TB_out01
select * from TB_prj01
insert into TB_out01 select 'TOTAL' +@Total+'TOTAL', ''
alter table TB_out01 alter column c1 [char] (20)
alter table TB_out01 alter column c2 [char] (20)
set @Cmd = 'bcp "select c1, c2 from db_henry.dbo.TB_out01 order by s1 " queryout D:\prj\prj01\prj01.txt -S localhost -c -T -t, '
exec master..xp_cmdshell @Cmd
drop table if exists TB_out01
END
不行欸，出現跟我上一個回覆一樣的錯誤。
恩@@...
哪可以改成這樣嗎?
先建立好 TB_out01 資料表
用truncate table快速清除裡面的資料~
CREATE OR ALTER PROCEDURE [dbo].[SP_prj01]
AS
BEGIN
declare @Cmd varchar(1000), @Date char(8), @Total varchar(10)
set @Date= Convert(char(8), getdate(), 112)
set @Total (select count(*) from TB_prj01)
truncate table TB_out01
insert into TB_out01 select 'CREATE' +@Date+'CREATE', ''
insert into TB_out01
select 'ID', '姓名'
insert into TB_out01
select * from TB_prj01
insert into TB_out01 select 'TOTAL' +@Total+'TOTAL', ''
alter table TB_out01 alter column c1 [char] (20)
alter table TB_out01 alter column c2 [char] (20)
set @Cmd = 'bcp "select c1, c2 from db_henry.dbo.TB_out01 order by s1 " queryout D:\prj\prj01\prj01.txt -S localhost -c -T -t, '
exec master..xp_cmdshell @Cmd
truncate table TB_out01
END
感覺可以，不過我還是有點好奇原本出錯的原因，這個寫法也要等下次這個莫名的問題出現才能試，可惡..
我是猜不斷的刪除跟建立~導致系統防呆錯誤有誤判吧...
iThome鐵人賽
看影片追技術