473,795 Members | 3,457 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

1 New Member
create function dbo.fnc_varchar _columns_alltri m
()
returns @vysledek table
( tabulka sysname null,
sloupec sysname null,
pocet_zaznamu int null,
datovy_typ varchar(256) null
)
as

begin

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_objSelectC ountResult int,
@bint_SelectCou nt 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','dt properties') 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)
begin
select top 1 @table_name=tab le_name,@column _name=column_na me,@dat_typ=dat ovy_typ from @pom
delete from @cislo
exec @int_erros = sp_OACreate 'SQLDMO.SQLServ er', @int_objSQL OUTPUT
exec @int_erros = sp_OASetPropert y @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 = 'ExecuteWithRes ults("' + @sql_text + '")'
exec @int_erros = sp_OAMethod @int_objSQL, @sql_text, @int_objSelectC ountResult OUTPUT
exec @int_erros = sp_OAMethod @int_objSelectC ountResult, 'GetRangeString (1, 1)', @bint_SelectCou nt OUT
exec @int_erros = sp_OADestroy @int_objSQL
set @celkem = @bint_SelectCou nt
if @int_erros <> 0
exec sp_OAGetErrorIn fo @int_objSQL
if @celkem > 0
insert into @vysledek (tabulka,sloupe c,pocet_zaznamu ,datovy_typ) values (@table_name,@c olumn_name,@cel kem,@dat_typ)
delete top (1) from @pom
end

return

end

go
Sep 3 '09 #1
0 2012

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

Similar topics

7
3551
by: diroddi | last post by:
I am having a problem using a dynamic where clause. I have a feeling that I am overlooking something very simple, although I can't seem to figure it out. The error i'm getting is: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY full_name ASC LIMIT 0,25' Any help would be greatly appreciated.
5
1868
by: windandwaves | last post by:
Hi Folk I was wondering if it is possible to find out what people click on, using PHP. For example, I have a PHP page with an email link and some third party links (e.g. www.someone-else.com). I want to know where people go. My own solution would be to make the link not to an email or a third-party website, but to another page along the lines of
47
3651
by: Andrey Tatarinov | last post by:
Hi. It would be great to be able to reverse usage/definition parts in haskell-way with "where" keyword. Since Python 3 would miss lambda, that would be extremly useful for creating readable sources. Usage could be something like: >>> res = where: >>> def f(x):
3
37165
by: KathyB | last post by:
Hi, I have 2 tables: tblStations StationID Station tblStationUser RecordID UserName Station I'm trying to come up with a dataset that contains the
7
1392
by: John | last post by:
Hi, How do websites like google page through large numbers of records? For example; if I do a search on Google, get 187,000 matches, and go to the 22nd page of the results; I'm sure Google doesn't do a new SELECT statement and traverse through 440 records (22pgs x 20 per page). But I'm also pretty sure Google doesn't keep a hell of a lot of session data open either. I know there are all kinds of ways to do this, I've created hacks...
6
27863
by: Roy Gourgi | last post by:
Hi, I am trying to add a row to my table but I get the error message "invalid column name SOBN and BN1" on this statement. Basically, I am trying to add the row into the same table that I am searching if it does not find SOBN = 5 and BN1 =3. What is the problem? Do I have to create a primary key? Is it possible to have more than 1 primary key (i.e. secondary ....... ). strCommand = "INSERT INTO tblSQL_2 (SOBN, BN1) SELECT SOBN , BN1...
2
1910
by: Pavel Stehule | last post by:
Hello, Pg make query 1. and 2. very fast (use index), but for query 3. dont use index. I can solve its using select union, but I readed so pg 7.5 don't problem with OR operator. I use cvs pg. I used vacuum analyze first. table sp_op_product has 15K rows, sp_op_uct 37K rows; regards Pavel Stehule
5
2361
by: Silvio Matthes | last post by:
Hello, I'm new to the list and did not find a suitable answer to my question so here it is: I try to select the rows of a table where the content of a varchar-column is empty ('') and PostgresQL is doing a seqscan. I've tried this on a PostgresQL-Serverversion 7.3.4 and 8.0 beta1.
4
2231
by: Phillip Vong | last post by:
I'm a newbie using VS2005 to learn and test. I'm testing against the Northwind DB. I created a simple variable called "myVariable" and I assigned it the character "c". I want my SQL query to use this variable in the "Where" statement where it will pull up all names with the letter "c" in it. How do I pass this variable on the the WHERE statement? I thought all I had to do was use the <% %> with my variable inside and that would work...
9
19160
by: Emin | last post by:
Dear Experts, I have a fairly simple query in which adding a where clause slows things down by at least a factor of 100. The following is the slow version of the query ------------------------- SELECT * FROM ( Select x.event_date From x FULL OUTER JOIN y ON x.event_date = y.event_date
0
9519
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10001
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7538
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6780
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5437
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5563
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4113
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3723
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2920
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.