August 28, 2006 at 12:09 pm
I am simply trying to export a dynamic sql statement to (that is over 8000 characters) to a pipe delimited flat file. I have exported using SSIS but the columns change every once and while and i do not want to have to update my package every time a change occurs. So i have been using BCP but it won't allow a sql string > 8000 characters. (sql statements a dynamic pivot with alot of columns).
Any suggestions? Can you do a bulk copy to a flat file in SSIS?
Thanks,
Les
August 29, 2006 at 8:27 am
I had to do something similar, except that I executed the dynamic sql. Actually I generated a stored proc, captured the sql in a table, then used the following snippet to keep the SQL smaller than 8K. It may help you:
--
-- Prepare Cursor for retrieval of generated code
--
DECLARE GeneratedObject CURSOR FOR
SELECT GeneratedCode
FROM dbo.ObjectGeneratedCode
WHERE ObjectID = @ObjectID
AND GeneratedCode IS NOT NULL
ORDER BY Sequence
OPEN GeneratedObject
FETCH NEXT FROM GeneratedObject INTO @GeneratedCode
--
-- Drop Object based on its ObjectType (9=SP, 10=DTS, 11=Job)
--
select @sql =
CASE @ObjectType
WHEN 9 THEN 'if exists (select * from dbo.sysobjects where id = object_id(N''' + @ObjectName + ''') and OBJECTPROPERTY(id, N''IsProcedure'') = 1) drop procedure ' + @ObjectName
END
execute(@SQL)
SELECT @sql = ''
SELECT @SQL1 = ''
SELECT @SQL2 = ''
SELECT @SQL3 = ''
SELECT @SQL4 = ''
SELECT @SQL5 = ''
SELECT @SQLFlag = 1
--------------------
-- Cursor Loop Start
--------------------
WHILE @@FETCH_STATUS = 0
BEGIN
if len(@SQL) + len(@GeneratedCode) > 8000
begin
IF @SQLFlag = 1 SELECT @SQL1 = @sql
IF @SQLFlag = 2 SELECT @SQL2 = @sql
IF @SQLFlag = 3 SELECT @SQL3 = @sql
IF @SQLFlag = 4 SELECT @SQL4 = @sql
IF @SQLFlag = 5 SELECT @SQL5 = @sql
select @sql = ''
select @SQLFlag = @SQLFlag + 1
end
SELECT @sql = @sql + @GeneratedCode + ' ' + char(10)
FETCH NEXT FROM GeneratedObject INTO @GeneratedCode
END
------------------
-- Cursor Loop End
------------------
close GeneratedObject
deallocate GeneratedObject
IF @SQLFlag = 1 SELECT @SQL1 = @sql
IF @SQLFlag = 2 SELECT @SQL2 = @sql
IF @SQLFlag = 3 SELECT @SQL3 = @sql
IF @SQLFlag = 4 SELECT @SQL4 = @sql
IF @SQLFlag = 5 SELECT @SQL5 = @sql
--print @SQL1 + @SQL2 + @SQL3 + @SQL4 + @SQL5
execute(@SQL1 + @SQL2 + @SQL3 + @SQL4 + @SQL5)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply