John posted on February 1, 2010 10:49

Posted in: Files , SQL Server , WINDOWS  Tags:

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

A further example from MSDN reference of a VB function, testing the behavior of the READ method, for different lengths, repeated.

SET nocount ON 
DECLARE @ForReading INT , 
@ForWriting INT , 
@ForAppending INT 
SELECT    @ForReading = 1, 
@ForWriting = 2, 
@ForAppending = 8 
DECLARE @fso INT, @file INT, @hr INT 
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT 
EXEC @hr = sp_OAMethod @fso, 'opentextfile', @file out, 'c:\testfile.txt', @ForWriting, 1 
EXEC @hr = sp_OAMethod @file, 'Write', NULL, '123456789 Hello My world!' 
EXEC @hr = sp_OADestroy @file 
EXEC master..xp_cmdshell 'dir c:\testfile.txt' 
DECLARE @vcOut VARCHAR(100) 
DECLARE @vcOut1 VARCHAR(100) 
EXEC @hr = sp_OAMethod @fso, 'opentextfile', @file out, 'c:\testfile.txt', @ForReading, 1 
EXEC @hr = sp_OAMethod @file, 'Read', @vcOut out , 7 
EXEC @hr = sp_OAMethod @file, 'Read', @vcOut1 out , 2 
EXEC @hr = sp_OADestroy @file 
EXEC @hr = sp_OADestroy @fso 
PRINT REPLICATE('-',100) 
PRINT @vcOUT 
PRINT @vcOUT1   

Posted in: Files , SQL  Tags:

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
John posted on October 22, 2008 04:26

Here is one way that can be easily made into a procedure.

-- 
-- Working example for @vcTable. 
-- 
USE pubs
GO
DECLARE @vcTable VARCHAR(128)
    
SET @vcTable 'authors'
DECLARE @vcColumn VARCHAR(8000),
    
@vcINSERT VARCHAR(8000)
DECLARE
    
@vcSQL VARCHAR(8000),
    
@vcFormatting VARCHAR(2),
    
@bDatesAsText bit
SELECT    @vcFormatting = CHAR(10)+ CHAR(9),
    
@bDatesAsText 1
SELECT @vcColumn COALESCE@vcColumn ' VARCHAR(128),''CREATE TABLE ##x (') + COLUMN_NAME,
    
@vcINSERT COALESCE(@vcINSERT ''',''INSERT ##x VALUES (') + '''' COLUMN_NAME,
    
@vcSQL =
            
COALESCE(@vcSQL ', ' @vcFormatting 'SELECT ' ) +
    
CASE
                
WHEN @bDatesAsText 
            
AND DATA_TYPE IN ('datetime','smalldatetime'THEN
        
'char(34) + convert(varchar(23), ' QUOTENAME(COLUMN_NAME) + ', 121) + char(34)'
                
WHEN DATA_TYPE IN ('varchar','nvarchar','char','nchar','text','ntext'THEN
        
'char(34) + ' QUOTENAME(COLUMN_NAME) + '+ char(34)'
    
ELSE
                
QUOTENAME(COLUMN_NAME)
    
END ' AS ' QUOTENAME(COLUMN_NAME)
    
FROM  INFORMATION_SCHEMA.Columns
    
WHERE TABLE_NAME @vcTable
    
ORDER BY
            
ORDINAL_POSITION
SELECT    @vcColumn @vcColumn ' VARCHAR(8000))',
    
@vcINSERT @vcINSERT ''')',
    
@vcSQL @vcSQL '
FROM ' 
DB_NAME() + '.dbo.' @vcTable
            
PRINT @vcColumn
            
PRINT @vcINSERT
    
-- This must be in one statement or the optimizer thinks the table doesn't exist.
EXEC (@vcColumn ';' @vcINSERT)
-- add data here ...
PRINT @vcSQL
EXEC ('INSERT ##x ' @vcSQL)
SELECT 
    
FROM ##x
DECLARE @vcCmd VARCHAR(8000),
    
@vcOutFile VARCHAR(4000),
    
@vcFieldParameter VARCHAR(2)
SELECT    @vcOutFile 'E:\MSSQL\REPORTS\test.bcp',
    
@vcFieldParameter ',',
    
@vcCmd 'bcp "SELECT * FROM ##x" queryout "' @vcOutFile +
    
'" -S' @@SERVERNAME  ' -T -t ' @vcFieldParameter ' -c '
            
PRINT @vcCmd
EXEC master.dbo.xp_cmdshell @vcCmd
    
-- very important
DROP TABLE ##x
    
--
    --
    --
            


Posted in: SQL , Files  Tags:

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2010 The SQL DBA