|
Update
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
|