USE master GO CREATE PROCEDURE dbo.sp_SaveFixedColumn @PCWrite varchar(1000) = NULL, @DBFetch varchar(4000), @DBField varchar(1000), @DBWhere varchar(2000) = NULL, @DBThere varchar(2000) = NULL, @DBUltra bit = 0 AS SET NOCOUNT ON DECLARE @Return int DECLARE @Retain int DECLARE @Status int SET @Status = 0 DECLARE @TPre varchar(10) DECLARE @TDo3 tinyint DECLARE @TDo4 tinyint SET @TPre = '' SET @TDo3 = LEN(@TPre) SET @TDo4 = LEN(@TPre) + 1 DECLARE @Task varchar(4000) DECLARE @Bank varchar(2000) DECLARE @Cash varchar(2000) DECLARE @Risk varchar(2000) DECLARE @Next varchar(8000) DECLARE @Save varchar(8000) DECLARE @Work varchar(8000) DECLARE @Wish varchar(8000) DECLARE @Name varchar(100) DECLARE @Same varchar(100) DECLARE @DBAI varchar(2000) DECLARE @DBAO varchar(8000) DECLARE @DBAU varchar(2000) DECLARE @Fuse int DECLARE @File int SET @Bank = @TPre + @DBFetch IF NOT EXISTS (SELECT * FROM sysobjects WHERE RTRIM(type) = 'U' AND name = @Bank) BEGIN SET @Bank = CASE WHEN LEFT(LTRIM(@DBFetch),6) = 'SELECT' THEN '(' + @DBFetch + ')' ELSE @DBFetch END SET @Bank = REPLACE(@Bank, CHAR(94),CHAR(39)) SET @Bank = REPLACE(@Bank,CHAR(45)+CHAR(45),CHAR(32)) SET @Bank = REPLACE(@Bank,CHAR(47)+CHAR(42),CHAR(32)) END IF @DBWhere IS NOT NULL BEGIN SET @Cash = REPLACE(@DBWhere,'WHERE' ,CHAR(32)) SET @Cash = REPLACE(@Cash, CHAR(94),CHAR(39)) SET @Cash = REPLACE(@Cash,CHAR(45)+CHAR(45),CHAR(32)) SET @Cash = REPLACE(@Cash,CHAR(47)+CHAR(42),CHAR(32)) END IF @DBThere IS NOT NULL BEGIN SET @Risk = REPLACE(@DBThere,'ORDER BY' ,CHAR(32)) SET @Risk = REPLACE(@Risk, CHAR(94),CHAR(39)) SET @Risk = REPLACE(@Risk,CHAR(45)+CHAR(45),CHAR(32)) SET @Risk = REPLACE(@Risk,CHAR(47)+CHAR(42),CHAR(32)) END SET @DBAU = REPLACE(@DBField,CHAR(32),CHAR(95)) SET @DBAI = ' SELECT ' SET @DBAO = ' FROM ' + @Bank + ' AS T' + CASE WHEN @DBWhere IS NULL THEN '' ELSE ' WHERE (' + @Cash + ') AND 0 = 0' END + CASE WHEN @DBThere IS NULL THEN '' ELSE ' ORDER BY ' + @Risk + ',' + CHAR(39) + 'DBA' + CHAR(39) END IF LEN(ISNULL(@PCWrite,'*')) > 7 AND @DBUltra = 0 BEGIN SET @Wish = 'USE ' + DB_NAME() + @DBAI + @DBAU + @DBAO SET @Work = 'bcp "' + @Wish + '" queryout "' + @PCWrite + '" -c -T' EXECUTE @Return = master.dbo.xp_cmdshell @Work, NO_OUTPUT SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain IF @Status = 0 SET @Status = @Return GOTO ABORT END IF LEN(ISNULL(@PCWrite,'*')) > 7 BEGIN IF @Status = 0 EXECUTE @Return = sp_OACreate 'Scripting.FileSystemObject', @Fuse OUTPUT SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain IF @Status = 0 SET @Status = @Return IF @Status = 0 EXECUTE @Return = sp_OAMethod @Fuse, 'CreateTextFile', @File OUTPUT, @PCWrite, -1 SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain IF @Status = 0 SET @Status = @Return IF @Status <> 0 GOTO ABORT END SET @DBAI = 'DECLARE Records CURSOR GLOBAL FAST_FORWARD FOR' + @DBAI IF @Status = 0 EXECUTE (@DBAI+@DBAU+@DBAO) SET @Return = @@ERROR IF @Status = 0 SET @Status = @Return OPEN Records SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain FETCH NEXT FROM Records INTO @Next SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain WHILE @@FETCH_STATUS = 0 AND @Status = 0 BEGIN IF ISNULL(@File,0) = 0 BEGIN PRINT @Next END ELSE BEGIN SET @Save = @Next + CHAR(13) + CHAR(10) IF @Status = 0 EXECUTE @Return = sp_OAMethod @File, 'Write', NULL, @Save IF @Status = 0 SET @Status = @Return END FETCH NEXT FROM Records INTO @Next SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain END CLOSE Records DEALLOCATE Records IF ISNULL(@File,0) <> 0 BEGIN EXECUTE @Return = sp_OAMethod @File, 'Close', NULL IF @Status = 0 SET @Status = @Return EXECUTE @Return = sp_OADestroy @File IF @Status = 0 SET @Status = @Return EXECUTE @Return = sp_OADestroy @Fuse IF @Status = 0 SET @Status = @Return END ABORT: -- This label is referenced when OLE automation fails. IF @Status = 1 OR @Status NOT BETWEEN 0 AND 50000 RAISERROR ('Windows error [%d]',16,1,@Status) SET NOCOUNT OFF RETURN (@Status) GO