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 vs. Management Studio

Can anyone tell me how I can edit the data in a field under Management
Studio/SQL 2005 like I can do using Enterprise Manager/SQL 2000?
Under EM, I can use a 'SELECT * FROM table' query in the 'open table'
option (with any necessary criteria to limit the returned data) and
directly edit the results. While this is not the preferred method to
handle data, it works nicely is certain instances.
I can write an UPDATE query to do the same thing, but the EM method was
frequently much faster for certain updates.
Anyway: when I try to do what seems like the equivalent in MS, I keep
getting errors about "Data has changed since the Results pane was last
retrieved" even though I know that to be false (I'm the only user in the
database, and the table I'm editing is used for lookup by the app). When
I try to commit the changes, it complains that it cannot update the row.
Right click on the table in Object Explorer. Click "Open Table".
On Jul 12, 11:26 am, horseradish <millar...@.gmail.com> wrote:
> Can anyone tell me how I can edit the data in a field under Management
> Studio/SQL 2005 like I can do using Enterprise Manager/SQL 2000?
> Under EM, I can use a 'SELECT * FROM table' query in the 'open table'
> option (with any necessary criteria to limit the returned data) and
> directly edit the results. While this is not the preferred method to
> handle data, it works nicely is certain instances.
> I can write an UPDATE query to do the same thing, but the EM method was
> frequently much faster for certain updates.
> Anyway: when I try to do what seems like the equivalent in MS, I keep
> getting errors about "Data has changed since the Results pane was last
> retrieved" even though I know that to be false (I'm the only user in the
> database, and the table I'm editing is used for lookup by the app). When
> I try to commit the changes, it complains that it cannot update the row.
|||Jason Lepack wrote:
> Right click on the table in Object Explorer. Click "Open Table".
That is what I'm trying to do. Is there something I've got set in the
connection properties that is causing this? I haven't changed any of the
defaults, so I'm not sure what should be updated to get the behavior back.

> On Jul 12, 11:26 am, horseradish <millar...@.gmail.com> wrote:
>
|||On Jul 13, 2:39 pm, horseradish <millar...@.gmail.com> wrote:
> Jason Lepack wrote:
> That is what I'm trying to do. Is there something I've got set in the
> connection properties that is causing this? I haven't changed any of the
> defaults, so I'm not sure what should be updated to get the behavior back.
>
>
>
> - Show quoted text -
That is odd behavior, I just tested it here and the Open Table
functionality allows me to edit directly in the grid view, which is
what you are looking to do. Are you opening the same table in EM and
it is working? The only time I've seen it now allow updating directly
from the grid is if a Primary Key is not specified on the table. I'm
not an expert, but sounds like something is off, especially if opening
the same table in EM works but not in SSMS.
|||> That is odd behavior, I just tested it here and the Open Table
> functionality allows me to edit directly in the grid view, which is
> what you are looking to do. Are you opening the same table in EM and
> it is working? The only time I've seen it now allow updating directly
> from the grid is if a Primary Key is not specified on the table. I'm
> not an expert, but sounds like something is off, especially if opening
> the same table in EM works but not in SSMS.
>
I created a registration to a SQL2000 host/instance and tried to edit a
field in grid view using SSMS, and received the error posted (Data has
changed since the Results pane was last retrieved/Optimistic Concurrency
Control Error) earlier in the thread.
I then opened EM and was able to edit the _exact_ _same_ field
(server\instance\database\user\table) in 'grid view.'

Enterprise Manager vs. Management Studio

Can anyone tell me how I can edit the data in a field under Management
Studio/SQL 2005 like I can do using Enterprise Manager/SQL 2000?
Under EM, I can use a 'SELECT * FROM table' query in the 'open table'
option (with any necessary criteria to limit the returned data) and
directly edit the results. While this is not the preferred method to
handle data, it works nicely is certain instances.
I can write an UPDATE query to do the same thing, but the EM method was
frequently much faster for certain updates.
Anyway: when I try to do what seems like the equivalent in MS, I keep
getting errors about "Data has changed since the Results pane was last
retrieved" even though I know that to be false (I'm the only user in the
database, and the table I'm editing is used for lookup by the app). When
I try to commit the changes, it complains that it cannot update the row.Right click on the table in Object Explorer. Click "Open Table".
On Jul 12, 11:26 am, horseradish <millar...@.gmail.com> wrote:
> Can anyone tell me how I can edit the data in a field under Management
> Studio/SQL 2005 like I can do using Enterprise Manager/SQL 2000?
> Under EM, I can use a 'SELECT * FROM table' query in the 'open table'
> option (with any necessary criteria to limit the returned data) and
> directly edit the results. While this is not the preferred method to
> handle data, it works nicely is certain instances.
> I can write an UPDATE query to do the same thing, but the EM method was
> frequently much faster for certain updates.
> Anyway: when I try to do what seems like the equivalent in MS, I keep
> getting errors about "Data has changed since the Results pane was last
> retrieved" even though I know that to be false (I'm the only user in the
> database, and the table I'm editing is used for lookup by the app). When
> I try to commit the changes, it complains that it cannot update the row.|||Jason Lepack wrote:
> Right click on the table in Object Explorer. Click "Open Table".
That is what I'm trying to do. Is there something I've got set in the
connection properties that is causing this? I haven't changed any of the
defaults, so I'm not sure what should be updated to get the behavior back.

> On Jul 12, 11:26 am, horseradish <millar...@.gmail.com> wrote:
>|||On Jul 13, 2:39 pm, horseradish <millar...@.gmail.com> wrote:
> Jason Lepack wrote:
> That is what I'm trying to do. Is there something I've got set in the
> connection properties that is causing this? I haven't changed any of the
> defaults, so I'm not sure what should be updated to get the behavior back.
>
>
>
>
>
> - Show quoted text -
That is odd behavior, I just tested it here and the Open Table
functionality allows me to edit directly in the grid view, which is
what you are looking to do. Are you opening the same table in EM and
it is working? The only time I've seen it now allow updating directly
from the grid is if a Primary Key is not specified on the table. I'm
not an expert, but sounds like something is off, especially if opening
the same table in EM works but not in SSMS.|||> That is odd behavior, I just tested it here and the Open Table
> functionality allows me to edit directly in the grid view, which is
> what you are looking to do. Are you opening the same table in EM and
> it is working? The only time I've seen it now allow updating directly
> from the grid is if a Primary Key is not specified on the table. I'm
> not an expert, but sounds like something is off, especially if opening
> the same table in EM works but not in SSMS.
>
I created a registration to a SQL2000 host/instance and tried to edit a
field in grid view using SSMS, and received the error posted (Data has
changed since the Results pane was last retrieved/Optimistic Concurrency
Control Error) earlier in the thread.
I then opened EM and was able to edit the _exact_ _same_ field
(server\instance\database\user\table) in 'grid view.'sql

Enterprise Manager vs. Management Studio

Can anyone tell me how I can edit the data in a field under Management
Studio/SQL 2005 like I can do using Enterprise Manager/SQL 2000?
Under EM, I can use a 'SELECT * FROM table' query in the 'open table'
option (with any necessary criteria to limit the returned data) and
directly edit the results. While this is not the preferred method to
handle data, it works nicely is certain instances.
I can write an UPDATE query to do the same thing, but the EM method was
frequently much faster for certain updates.
Anyway: when I try to do what seems like the equivalent in MS, I keep
getting errors about "Data has changed since the Results pane was last
retrieved" even though I know that to be false (I'm the only user in the
database, and the table I'm editing is used for lookup by the app). When
I try to commit the changes, it complains that it cannot update the row.Right click on the table in Object Explorer. Click "Open Table".
On Jul 12, 11:26 am, horseradish <millar...@.gmail.com> wrote:
> Can anyone tell me how I can edit the data in a field under Management
> Studio/SQL 2005 like I can do using Enterprise Manager/SQL 2000?
> Under EM, I can use a 'SELECT * FROM table' query in the 'open table'
> option (with any necessary criteria to limit the returned data) and
> directly edit the results. While this is not the preferred method to
> handle data, it works nicely is certain instances.
> I can write an UPDATE query to do the same thing, but the EM method was
> frequently much faster for certain updates.
> Anyway: when I try to do what seems like the equivalent in MS, I keep
> getting errors about "Data has changed since the Results pane was last
> retrieved" even though I know that to be false (I'm the only user in the
> database, and the table I'm editing is used for lookup by the app). When
> I try to commit the changes, it complains that it cannot update the row.|||Jason Lepack wrote:
> Right click on the table in Object Explorer. Click "Open Table".
That is what I'm trying to do. Is there something I've got set in the
connection properties that is causing this? I haven't changed any of the
defaults, so I'm not sure what should be updated to get the behavior back.
> On Jul 12, 11:26 am, horseradish <millar...@.gmail.com> wrote:
>> Can anyone tell me how I can edit the data in a field under Management
>> Studio/SQL 2005 like I can do using Enterprise Manager/SQL 2000?
>> Under EM, I can use a 'SELECT * FROM table' query in the 'open table'
>> option (with any necessary criteria to limit the returned data) and
>> directly edit the results. While this is not the preferred method to
>> handle data, it works nicely is certain instances.
>> I can write an UPDATE query to do the same thing, but the EM method was
>> frequently much faster for certain updates.
>> Anyway: when I try to do what seems like the equivalent in MS, I keep
>> getting errors about "Data has changed since the Results pane was last
>> retrieved" even though I know that to be false (I'm the only user in the
>> database, and the table I'm editing is used for lookup by the app). When
>> I try to commit the changes, it complains that it cannot update the row.
>|||On Jul 13, 2:39 pm, horseradish <millar...@.gmail.com> wrote:
> Jason Lepack wrote:
> > Right click on the table in Object Explorer. Click "Open Table".
> That is what I'm trying to do. Is there something I've got set in the
> connection properties that is causing this? I haven't changed any of the
> defaults, so I'm not sure what should be updated to get the behavior back.
>
> > On Jul 12, 11:26 am, horseradish <millar...@.gmail.com> wrote:
> >> Can anyone tell me how I can edit the data in a field under Management
> >> Studio/SQL 2005 like I can do using Enterprise Manager/SQL 2000?
> >> Under EM, I can use a 'SELECT * FROM table' query in the 'open table'
> >> option (with any necessary criteria to limit the returned data) and
> >> directly edit the results. While this is not the preferred method to
> >> handle data, it works nicely is certain instances.
> >> I can write an UPDATE query to do the same thing, but the EM method was
> >> frequently much faster for certain updates.
> >> Anyway: when I try to do what seems like the equivalent in MS, I keep
> >> getting errors about "Data has changed since the Results pane was last
> >> retrieved" even though I know that to be false (I'm the only user in the
> >> database, and the table I'm editing is used for lookup by the app). When
> >> I try to commit the changes, it complains that it cannot update the row.- Hide quoted text -
> - Show quoted text -
That is odd behavior, I just tested it here and the Open Table
functionality allows me to edit directly in the grid view, which is
what you are looking to do. Are you opening the same table in EM and
it is working? The only time I've seen it now allow updating directly
from the grid is if a Primary Key is not specified on the table. I'm
not an expert, but sounds like something is off, especially if opening
the same table in EM works but not in SSMS.|||> That is odd behavior, I just tested it here and the Open Table
> functionality allows me to edit directly in the grid view, which is
> what you are looking to do. Are you opening the same table in EM and
> it is working? The only time I've seen it now allow updating directly
> from the grid is if a Primary Key is not specified on the table. I'm
> not an expert, but sounds like something is off, especially if opening
> the same table in EM works but not in SSMS.
>
I created a registration to a SQL2000 host/instance and tried to edit a
field in grid view using SSMS, and received the error posted (Data has
changed since the Results pane was last retrieved/Optimistic Concurrency
Control Error) earlier in the thread.
I then opened EM and was able to edit the _exact_ _same_ field
(server\instance\database\user\table) in 'grid view.'

Enterprise Manager for SQL server 2005

Hi,

i just installed sql 2k5 server and client tools.

When i run SQL ServerManagment Studio i dont find a way to create database.

Sajajd

Right click Databases. It will give you the option to create a new database.

Adamus

|||

First thing you have to do is to connect to the server so that you can view the system in the object explorer... once there expand the server so that you can view the database tab and as above right click and select create database or new database.

enterprise manager doesn't load

me bad - i installed visual studio (with sql express) then put the patches on
and sp2 for sql express.. then loaded the development version -- now
enterprise manager for sql 2005 won't load --
how do i make it - it doesn't give an error - just isn't there.
Do you mean Management Studio?
"Sandy Ryan" <SandyRyan@.discussions.microsoft.com> wrote in message
news:0E4A4E64-3F11-44AF-A302-0041D7E59135@.microsoft.com...
> me bad - i installed visual studio (with sql express) then put the patches
> on
> and sp2 for sql express.. then loaded the development version -- now
> enterprise manager for sql 2005 won't load --
> how do i make it - it doesn't give an error - just isn't there.
|||yes - it turns out that you have to have IIS installed to allow the
management studio to work - seems stupid to me - but it is what it is.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Do you mean Management Studio?
>
> "Sandy Ryan" <SandyRyan@.discussions.microsoft.com> wrote in message
> news:0E4A4E64-3F11-44AF-A302-0041D7E59135@.microsoft.com...
>
>
|||> yes - it turns out that you have to have IIS installed to allow the
> management studio to work - seems stupid to me - but it is what it is.
Well, that's not true because I use Management Studio on several systems
without IIS configured. But maybe you tripped something else when you
installed IIS. <shrug>
|||You don't need to have IIS installed to run SQL Server 05 or any of it's
tools.
Your issue may be caused by conflicts with .NET framework 2.0. Try
uninstalling the 2.0 framework and then reinstalling the latest copies from
Microsoft.
Search MS knowledge base and you'll find that many users have had problems
with sql server 05 and .NET 2.0
Sincerely,
John K
Knowledgy Consulting, LLC
www.knowledgy.org
Atlanta's Business Intelligence and Knowledge Management Experts
"Sandy Ryan" <SandyRyan@.discussions.microsoft.com> wrote in message
news:0E4A4E64-3F11-44AF-A302-0041D7E59135@.microsoft.com...
> me bad - i installed visual studio (with sql express) then put the patches
> on
> and sp2 for sql express.. then loaded the development version -- now
> enterprise manager for sql 2005 won't load --
> how do i make it - it doesn't give an error - just isn't there.

Entering data into tables

How does one enter data into the tables of a new database in the first place? I'm using SQL Server Management Studio Express. I can name the columns, but that seems to be as far as I get. Thanks for any Help.

I would recommend you use the T-SQL INSERT statement for adding data to the table.

Please take a look at the following tutorial titled "Inserting and Updating Data In a Table"

http://msdn2.microsoft.com/en-us/library/ms365309.aspx.

Kind Regards,
Jaaved

|||

Thanks a lot, as you can tell, I'm brand new to this SQL. Just trying to learn the ropes.

Thanks again.