Enumerate SQL Server built-in functions

Hi

I am trying to build a tree similar to the one in SQL Server Management Studio for the system functions in the SQL language.

I would like to group them by type (e.g. string functions) and display information about the parameters and return types, etc.

Is there a way to get these programmatically? I would like to avoid typing them out by hand.

Thanks

Chris

Hi Chris,

I'm not sure this is the right forum for this question. You can retrieve this information from the sytem views. E.g.:

select

ob.name as func,

parms.name as parm,

parms.parameter_id as pid,

ty.name as type

from

sys.all_objects ob,

sys.all_parameters parms,

sys.types as ty

where

ob.type = 'FN'

and ob.object_id = parms.object_id

and parms.user_type_id = ty.user_type_id

You can tweak this to get exactly what you're looking for.

Cheers,

-Isaac

|||I guess he means the really builtin functions like CHARINDEX and so on. Otherwise I would have suggested him to use the INFORMATION_SCHEMA views to retrieve the data.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens

that's right. I do mean the really built-in functions.

Thanks anyway.

Chris

|||

I asked some of the gurus here. Alas, it looks like there really isn't a way to do this.

Sorry,

-Isaac

|||

Thanks anyway Isaac.

Chris

Enumerate SQL Server built-in functions

Hi

I am trying to build a tree similar to the one in SQL Server Management Studio for the system functions in the SQL language.

I would like to group them by type (e.g. string functions) and display information about the parameters and return types, etc.

Is there a way to get these programmatically? I would like to avoid typing them out by hand.

Thanks

Chris

Hi Chris,

I'm not sure this is the right forum for this question. You can retrieve this information from the sytem views. E.g.:

select

ob.name as func,

parms.name as parm,

parms.parameter_id as pid,

ty.name as type

from

sys.all_objects ob,

sys.all_parameters parms,

sys.types as ty

where

ob.type = 'FN'

and ob.object_id = parms.object_id

and parms.user_type_id = ty.user_type_id

You can tweak this to get exactly what you're looking for.

Cheers,

-Isaac

|||I guess he means the really builtin functions like CHARINDEX and so on. Otherwise I would have suggested him to use the INFORMATION_SCHEMA views to retrieve the data.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens

that's right. I do mean the really built-in functions.

Thanks anyway.

Chris

|||

I asked some of the gurus here. Alas, it looks like there really isn't a way to do this.

Sorry,

-Isaac

|||

Thanks anyway Isaac.

Chris

Enterprise Manager on a workstation

Hi
I have a customer who uses SQL running on an SBS2000 server for several
applications. They have asked if
they can install SQL Enterprise manager on a workstation to manage an
Internet booking system they use.
Can anyone suggest any issues with this please? I'm assuming that there's a
licensing issue, but would there also be a network issue?
Regards
Nick
As long as the workstation has a licence to use the SQL Server it can also
use the managing tools.
There are no Networking issues as far as the EM is also only just a client
app with some more features than only quering or manipulating some data.
HTH, Jens Smeyer.
http://www.sqlserver2005.de
"Nick Hill" <please@.dont.use> schrieb im Newsbeitrag
news:d3jfu0$4ft$1$8302bc10@.news.demon.co.uk...
> Hi
> I have a customer who uses SQL running on an SBS2000 server for several
> applications. They have asked if
> they can install SQL Enterprise manager on a workstation to manage an
> Internet booking system they use.
> Can anyone suggest any issues with this please? I'm assuming that there's
> a licensing issue, but would there also be a network issue?
> Regards
> Nick
>
|||There is no problem in principle running Enterprise Manager or any of
the other client tools on a client PC - they are designed for that. You
need either a Client Access Licence or a Processor Licence for the
server.
If you are thinking about access over the Internet however, you won't
have the necessary ports open, except maybe via a secure VPN. You'll
most likely want some kind of remote access solution and then run EM
from there.
David Portas
SQL Server MVP
|||Doesn't MSDE come with the Enterprise Manager and as far as i know the MSDE
is free.
Does that mean that using the Enterprise Manager that MSDE comes with to
connect to another SQL server engine requires a licence?
-Ash
"David Portas" wrote:

> There is no problem in principle running Enterprise Manager or any of
> the other client tools on a client PC - they are designed for that. You
> need either a Client Access Licence or a Processor Licence for the
> server.
> If you are thinking about access over the Internet however, you won't
> have the necessary ports open, except maybe via a secure VPN. You'll
> most likely want some kind of remote access solution and then run EM
> from there.
> --
> David Portas
> SQL Server MVP
> --
>
|||> Doesn't MSDE come with the Enterprise Manager and as far as i know the
> MSDE
> is free.
MSDE comes with OSQL , no EM :-)

> Does that mean that using the Enterprise Manager that MSDE comes with to
> connect to another SQL server engine requires a licence?
But there is a webTool from MS to administer SQl Server Databases with basic
functionality:
http://www.microsoft.com/downloads/d...DisplayLang=en
HTH, Jens Smeyer
http://www.sqlserver2005.de
"Ash Metry" <AshMetry@.discussions.microsoft.com> schrieb im Newsbeitrag
news:033E925D-FB59-4211-BC67-7904A8686A89@.microsoft.com...[vbcol=seagreen]
> Doesn't MSDE come with the Enterprise Manager and as far as i know the
> MSDE
> is free.
> Does that mean that using the Enterprise Manager that MSDE comes with to
> connect to another SQL server engine requires a licence?
> -Ash
> "David Portas" wrote:
|||MSDE does not come with Enterprise Manager. The MSDE license does not cover
using Enterprise Manager.
Geoff N. Hiten
Microsoft SQL Server MVP
"Ash Metry" <AshMetry@.discussions.microsoft.com> wrote in message
news:033E925D-FB59-4211-BC67-7904A8686A89@.microsoft.com...[vbcol=seagreen]
> Doesn't MSDE come with the Enterprise Manager and as far as i know the
> MSDE
> is free.
> Does that mean that using the Enterprise Manager that MSDE comes with to
> connect to another SQL server engine requires a licence?
> -Ash
> "David Portas" wrote:
|||Ya i remember now.
Sorry about that, I posted without thinking.
"Geoff N. Hiten" wrote:

> MSDE does not come with Enterprise Manager. The MSDE license does not cover
> using Enterprise Manager.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "Ash Metry" <AshMetry@.discussions.microsoft.com> wrote in message
> news:033E925D-FB59-4211-BC67-7904A8686A89@.microsoft.com...
>
>

Enterprise Manager on a workstation

Hi
I have a customer who uses SQL running on an SBS2000 server for several
applications. They have asked if
they can install SQL Enterprise manager on a workstation to manage an
Internet booking system they use.
Can anyone suggest any issues with this please? I'm assuming that there's a
licensing issue, but would there also be a network issue?
Regards
NickAs long as the workstation has a licence to use the SQL Server it can also
use the managing tools.
There are no Networking issues as far as the EM is also only just a client
app with some more features than only quering or manipulating some data.
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Nick Hill" <please@.dont.use> schrieb im Newsbeitrag
news:d3jfu0$4ft$1$8302bc10@.news.demon.co.uk...
> Hi
> I have a customer who uses SQL running on an SBS2000 server for several
> applications. They have asked if
> they can install SQL Enterprise manager on a workstation to manage an
> Internet booking system they use.
> Can anyone suggest any issues with this please? I'm assuming that there's
> a licensing issue, but would there also be a network issue?
> Regards
> Nick
>|||There is no problem in principle running Enterprise Manager or any of
the other client tools on a client PC - they are designed for that. You
need either a Client Access Licence or a Processor Licence for the
server.
If you are thinking about access over the Internet however, you won't
have the necessary ports open, except maybe via a secure VPN. You'll
most likely want some kind of remote access solution and then run EM
from there.
David Portas
SQL Server MVP
--|||Doesn't MSDE come with the Enterprise Manager and as far as i know the MSDE
is free.
Does that mean that using the Enterprise Manager that MSDE comes with to
connect to another SQL server engine requires a licence?
-Ash
"David Portas" wrote:

> There is no problem in principle running Enterprise Manager or any of
> the other client tools on a client PC - they are designed for that. You
> need either a Client Access Licence or a Processor Licence for the
> server.
> If you are thinking about access over the Internet however, you won't
> have the necessary ports open, except maybe via a secure VPN. You'll
> most likely want some kind of remote access solution and then run EM
> from there.
> --
> David Portas
> SQL Server MVP
> --
>|||> Doesn't MSDE come with the Enterprise Manager and as far as i know the
> MSDE
> is free.
MSDE comes with OSQL , no EM :-)

> Does that mean that using the Enterprise Manager that MSDE comes with to
> connect to another SQL server engine requires a licence?
But there is a webTool from MS to administer SQl Server Databases with basic
functionality:
http://www.microsoft.com/downloads/...&DisplayLang=en
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Ash Metry" <AshMetry@.discussions.microsoft.com> schrieb im Newsbeitrag
news:033E925D-FB59-4211-BC67-7904A8686A89@.microsoft.com...[vbcol=seagreen]
> Doesn't MSDE come with the Enterprise Manager and as far as i know the
> MSDE
> is free.
> Does that mean that using the Enterprise Manager that MSDE comes with to
> connect to another SQL server engine requires a licence?
> -Ash
> "David Portas" wrote:
>|||MSDE does not come with Enterprise Manager. The MSDE license does not cover
using Enterprise Manager.
Geoff N. Hiten
Microsoft SQL Server MVP
"Ash Metry" <AshMetry@.discussions.microsoft.com> wrote in message
news:033E925D-FB59-4211-BC67-7904A8686A89@.microsoft.com...[vbcol=seagreen]
> Doesn't MSDE come with the Enterprise Manager and as far as i know the
> MSDE
> is free.
> Does that mean that using the Enterprise Manager that MSDE comes with to
> connect to another SQL server engine requires a licence?
> -Ash
> "David Portas" wrote:
>|||Ya i remember now.
Sorry about that, I posted without thinking.
"Geoff N. Hiten" wrote:

> MSDE does not come with Enterprise Manager. The MSDE license does not cov
er
> using Enterprise Manager.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "Ash Metry" <AshMetry@.discussions.microsoft.com> wrote in message
> news:033E925D-FB59-4211-BC67-7904A8686A89@.microsoft.com...
>
>

Enterprise manager compatibility

Hi
I trying register SQL Server 2005 Express Edition instance in Enterprise
Manager 2000 without success. Enterprise Manager 2000 do see instance and
lists its name in the list of available servers. But connection check
returns error: "SQL Server does not exist or access denied. ConnectionOpen
(Connect())".
Is Enterprise Manager 2000 compatible with SQL Server 2005 instances?Igor Solodovnikov (IgorSolodovnikov@.discussions.microsoft.com) writes:
> I trying register SQL Server 2005 Express Edition instance in Enterprise
> Manager 2000 without success. Enterprise Manager 2000 do see instance and
> lists its name in the list of available servers. But connection check
> returns error: "SQL Server does not exist or access denied. ConnectionOpen
> (Connect())".
> Is Enterprise Manager 2000 compatible with SQL Server 2005 instances?
No, you cannot connect to SQL 2005 with Enterprise Manager 2000. Query
Analyzer works though. (With some restrictions.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Igor Solodovnikov wrote:
> Hi
> I trying register SQL Server 2005 Express Edition instance in Enterprise
> Manager 2000 without success. Enterprise Manager 2000 do see instance and
> lists its name in the list of available servers. But connection check
> returns error: "SQL Server does not exist or access denied. ConnectionOpen
> (Connect())".
> Is Enterprise Manager 2000 compatible with SQL Server 2005 instances?
No. Use SQL Server Management Studio for 2005. SSMS can also manage SQL
Server 2000 instances.
David Portas
SQL Server MVP
--

entering data into sql database

hi

I am working on webforms i have to insert system date into sql database when a button is clicked.

I have have 3 fields on the webform and a submit button.

I have to insert the date along with the other fileds into the sql database

when I am trying to insert date using getdate() it is getting inserted but not into the same row as that of other fields.

Can you plzzz help me with the code for inserting system data into the database into the same row as that of other fields

myCommand2 =New SqlCommand("Insert into Items(Requestdate) values (getdate()) where Hospital= '" & DropDownList2.SelectedItem.Text & "' ", myConnection)

ra = myCommand2.ExecuteNonQuery()

myConnection.Close()

THE ABOVE CODE GIVES AN SYNTAX ERROR NEAR "WHERE"

please help

you can also post me ur replies onurs_forever_tanya2001@.yahoo.co.in

Thanks bye

You can't specify awhere clause on an insert statement as the whole point of an insert is to add a record (i.e. it doesn't already exist). If you want to update and existing record, you will have to use anupdatestatement.

|||

by using update statement will i be able to insert new record then?

because i have to take system date while inserting a new record...

please help

|||

tanya2001:

by using update statement will i be able to insert new record then?

No. If you want to insert a record you have to use an insert statement (but remove your where clause). If you want to update an existing record, use an update statement.

|||

Hey tanya ,

Thanks for your question .

let me descrive some essesntioal stuff for you first.

Insert Statement , we use this statement when we wantadd new record to our table , it means that record doesn't exist and we are going to add that , so in this case we wont usingwhere on this state ment becuase there is no any rows and we want to add that.

Update Statement , we use this statement when we already we have one or more records in our table and we want just change some of their value we use update statement and we use where to specify which rows we are going to change.

In you solution , if you are going to Insert New value to table that doesn't not exist at all use Insert Statement, if you are going to change some value in your database use update.

|||

thanks for your answer but i want to insert system date as well with the new record in the database

so how can i insert the system date as well as the new record by entering a single submit button

its actually like i have 3 fields in a webform so i have to insert those three fields as well as system date should be inserted when submit is clicked

please help

|||

thanks all i got the answer