|
|
Thread Tools | Rate Thread | Display Modes |
#4
|
|||
|
|||
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 |
|
|