August 26, 2005 at 9:47 am
When I run the script below (#1), I receive an error (#2). All seems fine regarding the proper syntax and data types. I must have checked and double checked sixteen times. Whe I substitute '1' as integer for the @PackageID variable, it works, but does not include the whole string, only about 360 characters. Loosing hair fast, please help ... All I want is for the @SQLString to include the whole string. Thank you.
-- #1
declare
@SQLString varchar(1000),
@TableName varchar(30),
@PackageID int
set @TableName = 'INDP'
set @PackageID = 1
set @SQLString = 'insert into DTSAudit (ComboID, RecordCount)
select combo.CID, rc.RcCnt
from
(select ''' + @TableName + ''' as TblNm, count(*) as RcCnt
from ' + @TableName + ') rc
inner join
(select t.tablename as TblNm, c.comboid as cid from DTSAuditCombo c
inner join DTSAuditTables t on c.tableid = t.tableid
where c.PackageID = ' + @PackageID + '
and t.tablename = ''' + @TableName + ''') combo
on rc.tblnm = combo.tblnm'
-- #2 - The error I receive:
Server: Msg 245, Level 16, State 1, Line 10
Syntax error converting the varchar value 'insert into DTSAudit (ComboID, RecordCount)
select combo.CID, rc.RcCnt
from
(select 'INDP' as TblNm, count(*) as RcCnt
from INDP) rc
inner join
(select t.tablename as TblNm, c.comboid as cid from DTSAuditCombo c
inner join DTSAuditTables t on c.tableid = t.tableid
where c.PackageID = ' to a column of data type int.
August 26, 2005 at 9:59 am
declare
@SQLString varchar(1000),
@TableName varchar(30),
@PackageID int
set @TableName = 'INDP'
set @PackageID = 1
set @SQLString = 'insert into DTSAudit (ComboID, RecordCount)
select combo.CID, rc.RcCnt
from
(select ''' + @TableName + ''' as TblNm, count(*) as RcCnt
from ' + @TableName + ') rc
inner join
(select t.tablename as TblNm, c.comboid as cid from DTSAuditCombo c
inner join DTSAuditTables t on c.tableid = t.tableid
where c.PackageID = ' + convert(Varchar, @PackageID) + '
and t.tablename = ''' + @TableName + ''') combo
on rc.tblnm = combo.tblnm'
select @SQLString
Regards,
gova
August 26, 2005 at 10:05 am
OK, not all with data types was fine. Thank you govinn, I'm going home now. It works great.
Jan S.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply