467,076 Members | 891 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,076 developers. It's quick & easy.

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

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 o.name as table_name, c.name as column_name, t.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 o.name not in ('cVykazPriloha','cVysledovka','cRozvaha','dtprope rties') and o.name not like 'rep%'
and c.user_type_id in (167,231) and c.name not in ('key')
and o.name = 'adm_DB_version' and c.name = 'version'
order by o.name

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
  • viewed: 1776

Post your reply

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

Similar topics

7 posts views Thread by diroddi | last post: by
5 posts views Thread by windandwaves | last post: by
47 posts views Thread by Andrey Tatarinov | last post: by
4 posts views Thread by Phillip Vong | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.