Gathering view from sysobjects

Is the statement below full-proof in gathering views defined in a sql server
7.0 database?
select a.name from sysobjects a where a.type = 'V' and a.status > 0
ThanksSELECT table_name FROM information_schema.views
is even easier. Microsoft advises not to access system tables directly, as
they might changed between versions and services packs. The
information_schema views are a set of ANSI standard views to represent
system information that are guaranteed not to change.
Jacco Schalkwijk
SQL Server MVP
"T" <anonymous@.discussions.microsoft.com> wrote in message
news:05B941B1-A86A-40C1-B2EB-CDDA459ED456@.microsoft.com...

quote:

---

> Is the statement below full-proof in gathering views defined in a sql

---

server 7.0 database?

quote:

---

> select a.name from sysobjects a where a.type = 'V' and a.status > 0
> Thanks

---

|||gotya! Thanks a lot!|||To add to Jacco's response, you can exclude system objects using the
OBJECTPROPERTY function like the example below.
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)
), 'IsMSShipped') = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"T" <anonymous@.discussions.microsoft.com> wrote in message
news:05B941B1-A86A-40C1-B2EB-CDDA459ED456@.microsoft.com...

quote:

---

> Is the statement below full-proof in gathering views defined in a sql

---

server 7.0 database?

quote:

---

> select a.name from sysobjects a where a.type = 'V' and a.status > 0
> Thanks

---

|||This is actually what I was looking for. Many thanks

Gathering view from sysobjects

Is the statement below full-proof in gathering views defined in a sql server 7.0 database
select a.name from sysobjects a where a.type = 'V' and a.status >
ThanksSELECT table_name FROM information_schema.views
is even easier. Microsoft advises not to access system tables directly, as
they might changed between versions and services packs. The
information_schema views are a set of ANSI standard views to represent
system information that are guaranteed not to change.
--
Jacco Schalkwijk
SQL Server MVP
"T" <anonymous@.discussions.microsoft.com> wrote in message
news:05B941B1-A86A-40C1-B2EB-CDDA459ED456@.microsoft.com...
> Is the statement below full-proof in gathering views defined in a sql
server 7.0 database?
> select a.name from sysobjects a where a.type = 'V' and a.status > 0
> Thanks|||gotya! Thanks a lot!|||To add to Jacco's response, you can exclude system objects using the
OBJECTPROPERTY function like the example below.
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)
), 'IsMSShipped') = 0
--
Hope this helps.
Dan Guzman
SQL Server MVP
"T" <anonymous@.discussions.microsoft.com> wrote in message
news:05B941B1-A86A-40C1-B2EB-CDDA459ED456@.microsoft.com...
> Is the statement below full-proof in gathering views defined in a sql
server 7.0 database?
> select a.name from sysobjects a where a.type = 'V' and a.status > 0
> Thanks|||This is actually what I was looking for. Many thanks

Gathering Field Descriptions From SAP

I am at the documentation stage of our project and I need Field Descriptions. They have been defined in SAP R/3 (DB2) and I can view them using SE11 command line parameter (It may change fom version to version, I don't know. It is the place where you view tables).
Lets say table X have 10 columns and every column's description has already been entered in SAP. In my situation there is a total amount of 200 tables which comes out appr. 2000 descriptions in return.
My question is as you may guess, Is there a way to gather this descriptions in a form of table(s)? So I can easily use this info for documentation. It maybe a third party tool or a command or a script wahatever. I really need this.

Thanks in advance.I have forgotten to tell that I am using SQL SERVER 2000 and connecting to DB2 via ODBC driver (though it is so slow...).

Garbage appearing when displaying Oracle character fields

Hi,
I am having troubles with character fields being displayed as garbage characters in Reporting Services. I have a shared data source defined as "Oracle" not "OLE DB" going against an Oracle 9i database. When I run a query in the Wizard Query Builder (which I hear uses the OLE DB connection to Oracle) the characters display correctly without any garbage characters. When I run the query in the Generic Query Builder (which I hear uses the Oracle data processing extension) the character fields display with garbage in them. Also, when I view the data in the data layout under the Preview tab, I get the garbage characters. The garbage characters appear on the second or following rows, in which the prior row had the same value in that field. The fields can either be defined as varchar2 or char in Oracle of more than 1 character in length. varchar2(1) and char(1) data types seem to display correctly without the garbage. The problem also shows up when I deploy the report to the ReportServer and view it in the ReportManager.
Does anyone have any ideas?
Thanks!
MelindaI found out that our issue was that we were using the Oracle 8.1.5 client to connect to an Oracle 9i database. This was causing garbage characters to appear in our reports. Once I upgraded to use the Oracle 9.1 client, it worked as expected. I did some digging and found a small line in the documentation that suggests the minimum client is Oracle 8.1.7 or later.
"Melinda" wrote:
> Hi,
> I am having troubles with character fields being displayed as garbage characters in Reporting Services. I have a shared data source defined as "Oracle" not "OLE DB" going against an Oracle 9i database. When I run a query in the Wizard Query Builder (which I hear uses the OLE DB connection to Oracle) the characters display correctly without any garbage characters. When I run the query in the Generic Query Builder (which I hear uses the Oracle data processing extension) the character fields display with garbage in them. Also, when I view the data in the data layout under the Preview tab, I get the garbage characters. The garbage characters appear on the second or following rows, in which the prior row had the same value in that field. The fields can either be defined as varchar2 or char in Oracle of more than 1 character in length. varchar2(1) and char(1) data types seem to display correctly without the garbage. The problem also shows up when I deploy the report to the ReportServer and view it in the ReportManager.
> Does anyone have any ideas?
> Thanks!
> Melinda

Functions don't show dependencies?

I use a user defined function in several stored procedures. However, only
tables show up when I look at the function's dependencies. How do I also
have the stored procedures that are using this function show up as
dependencies?
If that cannot be done, how else do I keep track of the stored procedures
which are using a certain function?
Thanks,
BrettThis works for me. Try recreating the sps.
use northwind
go
create function dbo.ufn_f1 ()
returns int
as
begin
return (1)
end
go
create procedure dbo.usp_p1
as
select dbo.ufn_f1()
go
exec sp_depends ufn_f1
go
drop procedure dbo.usp_p1
go
drop function dbo.ufn_f1
go
AMB
"Brett" wrote:

> I use a user defined function in several stored procedures. However, only
> tables show up when I look at the function's dependencies. How do I also
> have the stored procedures that are using this function show up as
> dependencies?
> If that cannot be done, how else do I keep track of the stored procedures
> which are using a certain function?
> Thanks,
> Brett
>
>|||Also,
How do I find a stored procedure containing <text>?
http://www.aspfaq.com/show.asp?id=2037
AMB
"Alejandro Mesa" wrote:
> This works for me. Try recreating the sps.
> use northwind
> go
> create function dbo.ufn_f1 ()
> returns int
> as
> begin
> return (1)
> end
> go
> create procedure dbo.usp_p1
> as
> select dbo.ufn_f1()
> go
> exec sp_depends ufn_f1
> go
> drop procedure dbo.usp_p1
> go
> drop function dbo.ufn_f1
> go
>
> AMB
>
> "Brett" wrote:
>

Function performances

I've made a user defined frunction that calculate a percentage reading data
from tables in db.
If i run the function with that code
set dateformat dmy
declare @.aaa DECIMAL(5,2)
set @.aaa=dbo.fn_name()
print @.aaa
or with that code
select dbo.fn_name()
it takes 21 seconds...too long
If i copy the code of the function and execute it in query analyzer, it
takes less than 1 second.
How is it possible?Function is not necessarily good for all kind of operation especially
for manipulating data in sets.
If your need to manipulate data in sets, you might as well do it in
stored proc to give you better performance and try to avoid cursor as
much as possible.
Roberto Lo Baido wrote:
> I've made a user defined frunction that calculate a percentage reading data
> from tables in db.
> If i run the function with that code
> set dateformat dmy
> declare @.aaa DECIMAL(5,2)
> set @.aaa=dbo.fn_name()
> print @.aaa
> or with that code
> select dbo.fn_name()
> it takes 21 seconds...too long
> If i copy the code of the function and execute it in query analyzer, it
> takes less than 1 second.
> How is it possible?

Function performances

I've made a user defined frunction that calculate a percentage reading data
from tables in db.
If i run the function with that code
set dateformat dmy
declare @.aaa DECIMAL(5,2)
set @.aaa=dbo.fn_name()
print @.aaa
or with that code
select dbo.fn_name()
it takes 21 seconds...too long
If i copy the code of the function and execute it in query analyzer, it
takes less than 1 second.
How is it possible?
Function is not necessarily good for all kind of operation especially
for manipulating data in sets.
If your need to manipulate data in sets, you might as well do it in
stored proc to give you better performance and try to avoid cursor as
much as possible.
Roberto Lo Baido wrote:
> I've made a user defined frunction that calculate a percentage reading data
> from tables in db.
> If i run the function with that code
> set dateformat dmy
> declare @.aaa DECIMAL(5,2)
> set @.aaa=dbo.fn_name()
> print @.aaa
> or with that code
> select dbo.fn_name()
> it takes 21 seconds...too long
> If i copy the code of the function and execute it in query analyzer, it
> takes less than 1 second.
> How is it possible?

Function Call - Syntax error

Hi All,
I have defined a function and want to call the same in a stored procedure.
The function call is giving me syntax error! Here is the function call.
function_name(@.param)
What is the correct syntax to call the function?
kdkd
SELECT * FROM dbo.yourfunction('param')
OR
SELECT dbo.Yourfunction('param')
"kd" <kd@.discussions.microsoft.com> wrote in message
news:F46EFF26-10A3-49CF-BF2E-5A5D6C31DE42@.microsoft.com...
> Hi All,
> I have defined a function and want to call the same in a stored procedure.
> The function call is giving me syntax error! Here is the function call.
> function_name(@.param)
> What is the correct syntax to call the function?
> kd|||Here is another example for you.
create function dbo.testf()
returns int
as
begin
return(33)
end
go
print dbo.testf()
go
drop function dbo.testf
HTH, Thanks
ZULFIQAR SYED
"kd" wrote:

> Hi All,
> I have defined a function and want to call the same in a stored procedure.
> The function call is giving me syntax error! Here is the function call.
> function_name(@.param)
> What is the correct syntax to call the function?
> kd|||You have to preface the functionname with "dbo.", as in
dbo.function_name(@.param)
If the function returns a scalar value (integer, float, varchar() etc.. Then
just usethe expression anywhere you would use the intrinsic datatype using
the same syntax
Select dbo.function_name(@.param), ColA, ColB
from table ...
or if it returns a table then use it exactly as you would a table...
Select <stuff> From dbo.function_name(@.param)
or...
Select <stuff>
From Table As T
Join dbo.function_name(@.param) As F
On F.ColA = T.ColA
"kd" wrote:

> Hi All,
> I have defined a function and want to call the same in a stored procedure.
> The function call is giving me syntax error! Here is the function call.
> function_name(@.param)
> What is the correct syntax to call the function?
> kd

function

hi..
i have one holiday master where all holiday are defined..now in below function i want to add payoutday to my trade_day.and this date will insert into my funding table.but before that i have to check after adding payoutday to the trade_day, this date not fall in holiday list.if that day will holidaty than i have to take next working day.and if again next day also holiday than i want to skip that day also and take next working day...and again check for holiday so on.
how to do this?

[code]

ALTER FUNCTION dbo.NewPayOutDate(@.id numeric(9))
Returns datetime
as
Begin

DECLARE @.CALDATE int
DECLARE @.ACTDATE DATETIME
Declare @.adddate DATETIME
declare @.moreCALDATE int
Begin
SELECT @.CALDATE=payoutday ,@.ACTDATE=trade_date
FROM pruamc.Tbl_GroupMst GM
INNER JOIN pruamc.Tbl_Redemption_UploadDetails (NOLOCK)
ON
GM.Group_Name = pruamc.Tbl_Redemption_UploadDetails.scheme_group
WHERE pruamc.Tbl_Redemption_UploadDetails.RedemptionUploadMaster_Id=@.id
SET @.adddate = dateadd(dd,@.CALDATE,@.ACTDATE)
SELECT @.moreCALDATE=count(*) from pruamc.Tbl_holidaymst where holiday_date between @.ACTDATE and @.adddate
END
set @.adddate = dateadd(dd,@.moreCALDATE,@.adddate)
RETURN @.adddate
END

[/code]

Moving to the Transact-SQL forum.|||

i m sorry ...thanx for

Moving to the Transact-SQL forum.

but any solution about my problem?

thanx a lot

|||

The following function will be help you to get the next working day...

Code Snippet

Create Function GetWorkingDay(@.Date as DateTime)

Returns DateTime

as

Begin
Declare @.Holiday as DateTime;
Select @.Holiday = holiday_date From Tbl_holidaymst Where holiday_date = @.Date;

If @.Holiday is Null
return @.Date;

return dbo.GetWorkingDay(DateAdd(DD, 1, @.Date));

End

Output:

if 1/1/2007 & 1/2/2007 is holiday

Select dbo.GetWorkingDay('1/1/2007') => 1/3/2007

Select dbo.GetWorkingDay('12/31/2006') => 12/31/2007

|||great thanx a lot..manid