CMD: Parse DATE to get a string for searching files

\ by

Sometimes we want to look for files with dates like yyyyMMdd in the name. So for given this code we can run in DOS CMD:

for /f "tokens=1-4 delims=/-. " %i in ('date /t') do @ECHO %i %j %k %l

Thu 02 21 2019

We can make our batch file to search for files with today’s date in name, using multiple %%s.

@ECHO OFF
 
rem First list everything sorted by date.
for /f "tokens=1-4 delims=/-. " %%i in ('date /t') do DIR  /OD "\\FileServer\?M*%%l%%j*.txt"

@ECHO.
@ECHO **SEARCHING for six ?M_ and one ?M_ files ...
for /f "tokens=1-4 delims=/-. " %%i in ('date /t') do @ECHO ...for date ?M*%%l%%j%%k*.txt && @ECHO. && DIR /OD "\\FileServer\?M*%%l%%j%%k*.txt"
Posted in: Coding, DOS, Files

Copy Files and SubDirectories and keep folder structure

\ by

Copies a set of files recursively and recreates subfolders and files in another location.

# Copy XML files from subfolders to another drive and create subfolders.

$SourcePath = 'G:\Prod\'
$TargetPath = 'E:\SQLScripts\'
$FileFilter = '*.xml'

Get-ChildItem $SourcePath -filter $FileFilter -recurse |
    ForEach-Object { 
        $targetFile = $TargetPath + $_.FullName.SubString($SourcePath.Length); 
        $DestFolder = Split-Path $targetFile
        if (-not (Test-Path $DestFolder)) {
            New-Item -ItemType Directory -Path $DestFolder #-WhatIf
        }

        Copy-Item $_.FullName -destination $targetFile #-WhatIf
}
Posted in: Files, PowerShell

SQL ERROR: ‘JOBID’ scripting variable not defined.

\ by

I often run scripts, especially SQL Agent job scripts using SQLCMD. Moving to SQL 2014 I ran into this error: ‘JOBID’ scripting variable not defined.

I see it is because I also use a token variable in the last job step. A quick solution for me was to add the -x parameter to SQLCMD ( -x (disable variable substitution) ) as suggested as one of the workarounds here.

 

Posted in: Files, SQL, SQLAgent

Hello World, Filesystemobject using sp_OA procedures

\ by

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

BCP out table data with Column Names

\ by

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 = 1 
            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: Files, SQL