How to supress output into the log window using Run SQL (ISQLW)

[BrandonG]

I have a problem with trying to get the SQL output to supress. I am using the standard Run SQL with the exception that I changed the EXE to isqlw due to the way that OSQL was processing some special characters in my scripts. Below are the 3 ways that I have tried configuring the step(s) (there are 20 or so) to supress output with no luck.

Can anyone offer any assistance

First:
<step action='Run SQL'>
<Abort type='11'>0</Abort>
<Database>C1DAT</Database>
<InputFile>%Script_Get_Location%\Installation\Veri fy Script\Val_DB.sql</InputFile>
<Location>isqlw</Location>
<Output type='3'>1</Output>
<OutputFile>%Script_Get_Location%\%SS_VERSION%Vali date_Central_Db.sql</OutputFile>
<Password>%validate_SA_Pass%</Password>
<SecType type='3'>1</SecType>
<Server>%COMPUTERNAME%</Server>
<UserName>sa</UserName>
<Width>10000</Width>
<indent type='3'>2</indent>
<name>Val_DB.sql</name>
<nologging type='11'>-1</nologging>
</step>

Second:
<step action='Run SQL'>
<Abort type='11'>0</Abort>
<Database>C1DAT</Database>
<InputFile>%Script_Get_Location%\Installation\Veri fy Script\Val_DB.sql</InputFile>
<Location>isqlw</Location>
<Output type='3'>1</Output>
<OutputFile>%Script_Get_Location%\%SS_VERSION%Vali date_Central_Db.sql</OutputFile>
<Password>%validate_SA_Pass%</Password>
<SecType type='3'>1</SecType>
<Server>%COMPUTERNAME%</Server>
<UserName>sa</UserName>
<Width>10000</Width>
<indent type='3'>2</indent>
<name>Val_DB.sql</name>
<nologging type='11'>0</nologging>
</step>

Third:
<step action='Run SQL'>
<Abort type='11'>0</Abort>
<Database>C1DAT</Database>
<InputFile>%Script_Get_Location%\Installation\Veri fy Script\Val_DB.sql</InputFile>
<Location>isqlw</Location>
<Output type='3'>1</Output>
<OutputFile>%Script_Get_Location%\%SS_VERSION%Vali date_Central_Db.sql</OutputFile>
<Password>%validate_SA_Pass%</Password>
<SecType type='3'>1</SecType>
<Server>%COMPUTERNAME%</Server>
<UserName>sa</UserName>
<Width>10000</Width>
<indent type='3'>2</indent>
<name>Val_DB.sql</name>
</step>

[kinook]

Checking the 'Disable logging of action output' suppresses all isql/osql output in our tests. Please ZIP and send or post:
1) The info from Help | About | Install Info
2) The .bld file
3) A build log file

Thanks.

[BrandonG]

Originally posted by kinook
Checking the 'Disable logging of action output' suppresses all isql/osql output in our tests. Please ZIP and send or post:
1) The info from Help | About | Install Info
2) The .bld file
3) A build log file

Thanks.

Help|about|Install Info

Visual Build Professional 6.3
Registered to: Mc Kesson Automation Inc. - Warrendale, PA (1-site license)
Windows Version: 5.1.2600.2.0
Install path: C:\Program Files\VisBuildPro6
SftTree_IX86_U_50.dll version 5.05
unins000.exe version 51.46.0.0
VisBuildCmd.exe version 6.3.0.0
VisBuildPro.exe version 6.3.0.0
VisBuildBld.dll version 6.3.0.0
VisBuildBurn.dll version 6.3.0.0
VisBuildCore.dll version 6.3.0.0
VisBuildDotNET.dll version 6.3.0.0
VisBuildExt.dll version 6.3.0.0
VisBuildLog.dll version 6.3.0.0
VisBuildMisc.dll version 6.3.0.1
VisBuildMS.dll version 6.3.0.0
VisBuildMS2.dll version 6.3.0.0
VisBuildNet.dll version 6.3.0.0
VisBuildSvr.dll version 6.3.0.0
VisBuildSvr.Interop.dll version 1.0.0.0
VisBuildVCS.dll version 6.3.0.0

The BLD file does contain information that I will have to review before I can send it to ensure that there are no company sensitive items.

The Log file certainly contains critical information and will need to be cleansed before sending.

I figure I would post the version info to start.

[BrandonG]

I believe it may have to do with the amount of data being outputted from the script.

I am running several scripts in a row. Most of the script output is around 1-10K. Some of the scripts output several MB worth of data. The one, that causes me the most grief, is a script that generates a data validation script. It produces 6.4MB of data.

This script outputs to the output window regardless of the selections.

Please test with a script that generates a LOT of output. As mentioned before, this script is a file that is being executed and outputting to another file. If I run the generated command line on the command line it produces no output.

Sample as:


set nocount on
PRINT 'SET NOCOUNT ON'
PRINT 'PRINT '' Validating...'' '
PRINT ' '
PRINT 'GO'

set nocount on
declare c_get_name CURSOR FOR
SELECT so.name, USR.NAME
FROM sysobjects so
JOIN SYSUSERS USR
ON SO.UID = USR.UID
WHERE so.type = 'U'
AND so.NAME <> 'DTPROPERTIES'
ORDER by 1,2
FOR READ ONLY
OPEN c_get_name
DECLARE @Table_Name varchar(255),
@Table_Owner VARCHAR(255)
PRINT 'PRINT ''Missing tables, objects that are not tables that should be, or wrong object owner:'''
PRINT 'GO'
FETCH c_get_name INTO @Table_Name, @Table_Owner
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'IF COALESCE(OBJECTPROPERTY(OBJECT_ID(''' + @Table_Owner+'.'+@Table_Name + '''), ''IsTable''),0)<>1 '
PRINT 'PRINT '' ' + @Table_Name + ''' '
PRINT 'GO'
FETCH c_get_name INTO @Table_Name, @Table_Owner
END
CLOSE c_get_name
DEALLOCATE c_get_name
GO
/***********************************
CHECK TABLE COLUMNS
***********************************/
IF OBJECT_ID('fn_TEMP_Validation_HELPER_COLUMNPLENGTH DATATYPE') IS NOT NULL
DROP FUNCTION fn_TEMP_Validation_HELPER_COLUMNPLENGTHDATATYPE
go
CREATE FUNCTION fn_TEMP_Validation_HELPER_COLUMNPLENGTHDATATYPE
(@Object_Owner VARCHAR(64),
@Table_Name VARCHAR(255),
@Column_Name VARCHAR(255)) RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @retCHAR VARCHAR(8000)
SELECT @retCHAR ='

IF NOT EXISTS (SELECT NULL FROM SYSCOLUMNS WHERE ID=OBJECT_ID('''+ @Object_Owner+'.'+@Table_Name +''') AND Name = ''' + @Column_Name + ''' '
+ ' AND xType = ' + CAST(SC.XType AS VARCHAR(32))+ ' '
+ ' AND Length = ' + CAST(SC.Length AS VARCHAR(32))
+ ' AND xprec = ' + CAST(SC.xprec AS VARCHAR(32))
+ ' AND xscale = ' + CAST(SC.xscale AS VARCHAR(32))
+')
BEGIN
DECLARE @MSG VARCHAR(8000)
SELECT @MSG =
'' *** DEFINITION: ' + @Table_Name +'.' + @Column_Name
+'

EXPECTED ACTUAL
' +' Type =' + UPPER(CAST(ST.NAME as CHAR(20))) +''' + UPPER(ST.Name) + ''
' +' Length =' + UPPER(CAST(SC.Length AS CHAR(20))) +''' + CAST(SC.Length AS VARCHAR(32)) + ''
' +' Precision =' + UPPER(CAST(SC.XPREC AS CHAR(20))) +''' + CAST(SC.XPREC AS VARCHAR(32)) + ''
' +' Length =' + UPPER(CAST(SC.XSCALE AS CHAR(20))) +''' + CAST(SC.XSCALE AS VARCHAR(32)) + ''
' +' Nullable =' + CAST(isnullable AS CHAR(20)) +''' + CAST(SC.isnullable AS VARCHAR(32))
'
+'
FROM SYSCOLUMNS SC JOIN SYSTYPES ST ON SC.XTYPE = ST.XTYPE WHERE SC.ID = OBJECT_ID(''' + @Object_Owner +'.' + @Table_Name + ''')
AND SC.NAME = ''' + @Column_Name + ''''
+'
PRINT @MSG
END
'
FROM SYSCOLUMNS SC
JOIN SYSTYPES ST
ON SC.xtype=ST.xtype
WHERE ID = OBJECT_ID(@Object_Owner+'.'+@Table_Name)
AND SC.NAME = @Column_Name

RETURN @retCHAR
END
GO
IF OBJECT_ID('fn_TEMP_Validation_HELPER_COLUMNPROPERT Y') IS NOT NULL
DROP FUNCTION fn_TEMP_Validation_HELPER_COLUMNPROPERTY
go
CREATE FUNCTION fn_TEMP_Validation_HELPER_COLUMNPROPERTY
(@Object_Owner VARCHAR(64),
@Table_Name VARCHAR(255),
@Column_Name VARCHAR(255),
@PropertyName VARCHAR(255)) RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @retCHAR VARCHAR(8000)
SET @retCHAR =' IF COLUMNPROPERTY(OBJECT_ID(''' + @Object_Owner+'.'+@Table_Name +'''),''' + @Column_Name + ''',''' + @PropertyName + ''') <> ' + CAST(COLUMNPROPERTY(objecT_id(@Object_Owner+'.'+@T able_Name),@Column_Name,@PropertyName) AS CHAR(1))
+'
PRINT '' DEFINITION: ' + @Table_Name +'.' + @Column_Name + '-' + @PropertyName + ' OPTION - Expected (' + CAST(COLUMNPROPERTY(objecT_id(@Object_Owner+'.'+@T able_Name),@Column_Name,@PropertyName) AS CHAR(1)) + ')' +
+ ' but got ('' + CAST(COLUMNPROPERTY(OBJECT_ID(''' + @Object_Owner+'.'+'''),''' + @Column_Name + ''',''' + @PropertyName + ''') AS CHAR(1)) +'')''
'
RETURN @retCHAR
END
GO



GO
set nocount on
declare c_get_name CURSOR FOR
SELECT obj.name, col.name, usr.name
from sysobjects obj
JOIN syscolumns col
ON obj.id = col.id
AND obj.type = 'U'
AND OBJ.NAME <> 'DTPROPERTIES'
JOIN SYSUSERS usr
ON obj.uid = usr.uid
ORDER by 1, 2
FOR READ ONLY
OPEN c_get_name
DECLARE @Table_Name varchar(255), @Column_Name varchar(255), @Object_Owner VARCHAR(64)
PRINT 'PRINT '' *** IF A TABLE WAS NOT FOUND EARLIER, YOU WILL NOT SEE MESSAGES ABOUT MISSING COLUMNS HERE. *** '''
PRINT 'PRINT ''Columns that are missing or have wrong definition:'' '
PRINT 'GO'
FETCH c_get_name INTO @Table_Name, @Column_Name, @Object_Owner
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'IF OBJECT_ID(''' + @Object_Owner + '.'+ @Table_Name +''') IS NOT NULL
BEGIN
IF COLUMNPROPERTY(OBJECT_ID(''' + @Object_Owner + '.'+ @Table_Name +'''),''' + @Column_Name + ''',''AllowsNull'') IS NOT NULL
BEGIN'
PRINT dbo.fn_TEMP_Validation_HELPER_COLUMNPROPERTY(@Obje ct_Owner, @Table_NAme, @Column_Name, 'IsIdentity')
PRINT dbo.fn_TEMP_Validation_HELPER_COLUMNPLENGTHDATATYP E (@Object_Owner, @Table_NAme, @Column_Name)


PRINT 'END
ELSE
PRINT '' MISSING: ' + @Table_Name + '.' + @Column_Name + ''' '

PRINT 'END'
PRINT 'GO'


FETCH c_get_name INTO @Table_Name, @Column_Name, @Object_Owner
END
CLOSE c_get_name
DEALLOCATE c_get_name
GO

[kinook]

You're right -- when the action sends output to a file and the file written is larger than 50K, most of the file contents would be logged even if logging of step output was disabled.

The main download at http://www.kinook.com/Download/VisBuildProEval.exe has been updated with a fix for this issue (VisBuildBld.dll v6.3.0.2 in Help | About | Install Info after installing).

[BrandonG]

I assume that this has been rolled into the main code stream of VBP and all future releases will contain this fix?

[kinook]

Yes.