By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Where is error-I need exec in function - result is without error,but not return data

P: 1
create function dbo.fnc_varchar_columns_alltrim
returns @vysledek table
( tabulka sysname null,
sloupec sysname null,
pocet_zaznamu int null,
datovy_typ varchar(256) null


declare @sql_text nvarchar(2000),
@table_name varchar(256),
@column_name varchar(256),
@dat_typ varchar(256),
@celkem int,
@int_objSQL int,
@int_erros int,
@int_objSelectCountResult int,
@bint_SelectCount bigint

declare @cislo table
( pocet int

declare @pom table
( table_name sysname,
column_name sysname,
datovy_typ varchar(256)

insert into @pom
select as table_name, as column_name, as datovy_typ
from sys.objects o
left join sys.columns c on c.object_id = o.object_id
left join sys.types t on t.user_type_id = c.user_type_id
where o.type = 'U' and not in ('cVykazPriloha','cVysledovka','cRozvaha','dtprope rties') and not like 'rep%'
and c.user_type_id in (167,231) and not in ('key')
and = 'adm_DB_version' and = 'version'
order by

while exists (select 1 from @pom)
select top 1 @table_name=table_name,@column_name=column_name,@d at_typ=datovy_typ from @pom
delete from @cislo
exec @int_erros = sp_OACreate 'SQLDMO.SQLServer', @int_objSQL OUTPUT
exec @int_erros = sp_OASetProperty @int_objSQL, 'LoginSecure', TRUE
exec @int_erros = sp_OAMethod @int_objSQL, 'Connect', null, '.'
set @sql_text = 'select count(*) from dbo.' + @table_name + ' where len(' + @column_name+ ') <> len(ltrim(rtrim(' + @column_name+ ')))'
set @sql_text = 'ExecuteWithResults("' + @sql_text + '")'
exec @int_erros = sp_OAMethod @int_objSQL, @sql_text, @int_objSelectCountResult OUTPUT
exec @int_erros = sp_OAMethod @int_objSelectCountResult, 'GetRangeString(1, 1)', @bint_SelectCount OUT
exec @int_erros = sp_OADestroy @int_objSQL
set @celkem = @bint_SelectCount
if @int_erros <> 0
exec sp_OAGetErrorInfo @int_objSQL
if @celkem > 0
insert into @vysledek (tabulka,sloupec,pocet_zaznamu,datovy_typ) values (@table_name,@column_name,@celkem,@dat_typ)
delete top (1) from @pom



Sep 3 '09 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.