Kinook Software Forum

Go Back   Kinook Software Forum > Visual Build Professional > [VBP] Third Party Tools
Register FAQ Community Calendar Today's Posts Search

 
 
Thread Tools Rate Thread Display Modes
Prev Previous Post   Next Post Next
  #4  
Old 04-25-2007, 05:28 PM
BrandonG BrandonG is online now
Registered User
 
Join Date: 11-13-2006
Posts: 12
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
Reply With Quote
 


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



All times are GMT -5. The time now is 08:43 PM.


Copyright © 1999-2023 Kinook Software, Inc.