Getting a stored procedures return value -- URGENT !

We've got some code that has been using a SqlCommand with the commandtype
set to Text. It is running a stored procedure by building a StringBuilder
object to string together the parameters and then execute. The problem I am
running into is that if I add a parameter to the commands paramter
collection and designate it as the return value in the "direction"
parameter, I never get the value returned.
I'm assuming it is because when executing a stored proc in this manner
(instead of using commandtype of StoredProcedure) that the stored procedure
is actually considered to be nested within the "procedural" code I'm
executing as text. Does this make sense? I hope that explanation is clear
enough. I really need to be able to access these return codes without
rewriting the world. As it is now they have all their stored procs doing a
"select ##" to send a return code back to their C# code. This is ludicrous
and I cannot reuse any of these storedprocs from another stored proc. I
don't see anyway to get the select results of a nested stored proc...
I'm on a tight deadline here haven't much time to solve this before writing
it over would be faster.
Any help is greatly appreciated!Hi
Did you check out:
http://msdn.microsoft.com/library/d...r />
outas.asp
The return values are only available once all result sets have been
processed.
John
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ejnT8RHFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> We've got some code that has been using a SqlCommand with the commandtype
> set to Text. It is running a stored procedure by building a StringBuilder
> object to string together the parameters and then execute. The problem I
> am running into is that if I add a parameter to the commands paramter
> collection and designate it as the return value in the "direction"
> parameter, I never get the value returned.
> I'm assuming it is because when executing a stored proc in this manner
> (instead of using commandtype of StoredProcedure) that the stored
> procedure is actually considered to be nested within the "procedural" code
> I'm executing as text. Does this make sense? I hope that explanation is
> clear enough. I really need to be able to access these return codes
> without rewriting the world. As it is now they have all their stored
> procs doing a "select ##" to send a return code back to their C# code.
> This is ludicrous and I cannot reuse any of these storedprocs from another
> stored proc. I don't see anyway to get the select results of a nested
> stored proc...
> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
> Any help is greatly appreciated!
>|||> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
If you must stick with CommandType.Text for now, you might try passing the
return code value as an output parameter value. At least that will lessen
the immediate code changes needed.
As you probably know, it's generally a bad technique to build literal
strings instead of using parameterized procs and queries. When you get
around to converting to CommandType.StoredProcedure, ditch the StringBuilder
and use input parameters instead as well as the proper return value
parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ejnT8RHFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> We've got some code that has been using a SqlCommand with the commandtype
> set to Text. It is running a stored procedure by building a StringBuilder
> object to string together the parameters and then execute. The problem I
> am running into is that if I add a parameter to the commands paramter
> collection and designate it as the return value in the "direction"
> parameter, I never get the value returned.
> I'm assuming it is because when executing a stored proc in this manner
> (instead of using commandtype of StoredProcedure) that the stored
> procedure is actually considered to be nested within the "procedural" code
> I'm executing as text. Does this make sense? I hope that explanation is
> clear enough. I really need to be able to access these return codes
> without rewriting the world. As it is now they have all their stored
> procs doing a "select ##" to send a return code back to their C# code.
> This is ludicrous and I cannot reuse any of these storedprocs from another
> stored proc. I don't see anyway to get the select results of a nested
> stored proc...
> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
> Any help is greatly appreciated!
>

getting a stored procedures code

I don't know if this is possible. However, what i am attempting to do is
using C#'s window forms. I open up an excell sheet stored in my windows
form. The excel sheet stores names of the stored procedures in that
database. I want to know if it's possible to click on that stored
procedure to open up a link to display the code of that stored procedure of
course in a read only mode.

any suggestions...Two possible options: Query the syscomments table (See Books Online for
details) or use the Script method in the SQLDMO API (a COM API, not
..NET).

--
David Portas
SQL Server MVP
--|||using sp_helptext <Stored Procedure Name>
enables you to see the stored procedure code

best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***sql

Get XML node as 'text' data type

We are using XML to pump data into a SQ: Server 2005 database. We pass an XML document into a stored procedure, the stored procedures chunks out the data and inserts it into the appropriate tables. Fine. Works great, easy to maintain, excelent performance. Here's the problem. One of the columns we are pushing data into is of data type text and the .value fuction of the XML node does not support conversion of a node's data to the 'text' data type. For example:

CREATE PROCEDURE as MyProcedure @.myData xml
BEGIN
INSERT INTO MyTable (FirstName, LastName, Notes)
SELECT
MyNode.value('FirstName[1]','varchar(50)'),
MyNode.value('LastName[1]','varchar(100)'),
MyNode.value('Notes[1]','text')
FROM @.myData.Notes('Person') as R(MyNode)
END

The problem is with the notes field. The cast to the data type text fails with the following error:
The data type 'text' used in the VALUE method is invalid.

The workaround thus far has been to use varchar(8000), but it will result in truncation if the data is too long.

Any ideas?

Try using 'varchar(max)' instead of 'text'

|||Perfect. Thanks!|||What would be a datatype for the value for an image? Will varchar(max) work for it as well?

Get XML node as 'text' data type

We are using XML to pump data into a SQ: Server 2005 database. We pass an XML document into a stored procedure, the stored procedures chunks out the data and inserts it into the appropriate tables. Fine. Works great, easy to maintain, excelent performance. Here's the problem. One of the columns we are pushing data into is of data type text and the .value fuction of the XML node does not support conversion of a node's data to the 'text' data type. For example:

CREATE PROCEDURE as MyProcedure @.myData xml
BEGIN
INSERT INTO MyTable (FirstName, LastName, Notes)
SELECT
MyNode.value('FirstName[1]','varchar(50)'),
MyNode.value('LastName[1]','varchar(100)'),
MyNode.value('Notes[1]','text')
FROM @.myData.Notes('Person') as R(MyNode)
END

The problem is with the notes field. The cast to the data type text fails with the following error:
The data type 'text' used in the VALUE method is invalid.

The workaround thus far has been to use varchar(8000), but it will result in truncation if the data is too long.

Any ideas?

Try using 'varchar(max)' instead of 'text'

|||Perfect. Thanks!|||What would be a datatype for the value for an image? Will varchar(max) work for it as well?

Get value of parameters passed in stored procedure in a trigger

In updating my tables (insert/update), i use stored procedures.
Some of the values passed as parameters to the stored procedures
are only necessary for audit trail only and not for updating the tables.
How can i get hold of these parameter values while inside a trigger?Put the parameters in a permanent table or a local temp table.
David Portas
SQL Server MVP
--
"manK" <manK@.discussions.microsoft.com> wrote in message
news:EA0833BF-A070-4720-9E50-9C80EAE45FF9@.microsoft.com...
> In updating my tables (insert/update), i use stored procedures.
> Some of the values passed as parameters to the stored procedures
> are only necessary for audit trail only and not for updating the tables.
> How can i get hold of these parameter values while inside a trigger?
>

Get the name of all user tables in a database

I want to have a stored procedures which when I pass it the name of a
database it will return all the names of the user tables. I have tried
CREATE PROCEDURE sp_gettables
@.dbname char
AS
EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =
"'Table'"
it won't do it as it can only work in its own context. I have also tried
using the use command with a database name as a parameter to point it at the
database. It won't let me do that either. Any ideas, Regards.
How about this?
SELECT TABLE_SCHEMA, TABLE_NAME=20
FROM INFORMATION_SCHEMA.TABLES=20
WHERE TABLE_TYPE =3D 'BASE TABLE'
--=20
Keith
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message =
news:%23cDtEaoNEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I want to have a stored procedures which when I pass it the name of a
> database it will return all the names of the user tables. I have tried
>=20
> CREATE PROCEDURE sp_gettables
> @.dbname char
> AS
> EXEC sp_tables @.table_qualifier =3D "' + @.dbname + '", @.table_type =
=3D
> "'Table'"
>=20
> it won't do it as it can only work in its own context. I have also =
tried
> using the use command with a database name as a parameter to point it =
at the
> database. It won't let me do that either. Any ideas, Regards.
>=20
>
|||On Mon, 10 May 2004 12:56:58 +0100, Chris Kennedy wrote:

>I want to have a stored procedures which when I pass it the name of a
>database it will return all the names of the user tables. I have tried
>CREATE PROCEDURE sp_gettables
>@.dbname char
>AS
>EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =
>"'Table'"
>it won't do it as it can only work in its own context. I have also tried
>using the use command with a database name as a parameter to point it at the
>database. It won't let me do that either. Any ideas, Regards.
>
Hi Chris,
First, it's better not to prefix your stored procedures with sp_. This
prefix has a special meaning to SQL Server, possibly causing unwanted
effects.
Second, datatype char defaults to char(1). Unless your database names
are only one letter long, this will fail. Use nvarchar(128) or sysname
instead.
Third, it is generally preferred to query the INFORMATION_SCHEMA views
instead of the system tables or stored procedures. These views are
ANSI-standard, making your code more portable.
If you want to use sp_tables, use dynamic SQL to concatenate a USE
command and the EXEC sp_tables command. If you prefer to use
INFORMATION_SCHEMA, use the query below (that also uses dynamic SQL).
CREATE PROCEDURE gettables
@.dbname sysname
AS
execute ('select * from ' + @.dbname + '.INFORMATION_SCHEMA.TABLES'
+ ' where TABLE_CATALOG = ''' + @.dbname + '''')
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Get the name of all user tables in a database

I want to have a stored procedures which when I pass it the name of a
database it will return all the names of the user tables. I have tried
CREATE PROCEDURE sp_gettables
@.dbname char
AS
EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type = "'Table'"
it won't do it as it can only work in its own context. I have also tried
using the use command with a database name as a parameter to point it at the
database. It won't let me do that either. Any ideas, Regards.How about this?
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE =3D 'BASE TABLE'
-- Keith
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message =news:%23cDtEaoNEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I want to have a stored procedures which when I pass it the name of a
> database it will return all the names of the user tables. I have tried
> > CREATE PROCEDURE sp_gettables
> @.dbname char
> AS
> EXEC sp_tables @.table_qualifier =3D "' + @.dbname + '", @.table_type ==3D
> "'Table'"
> > it won't do it as it can only work in its own context. I have also =tried
> using the use command with a database name as a parameter to point it =at the
> database. It won't let me do that either. Any ideas, Regards.
> >|||On Mon, 10 May 2004 12:56:58 +0100, Chris Kennedy wrote:
>I want to have a stored procedures which when I pass it the name of a
>database it will return all the names of the user tables. I have tried
>CREATE PROCEDURE sp_gettables
>@.dbname char
>AS
>EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =>"'Table'"
>it won't do it as it can only work in its own context. I have also tried
>using the use command with a database name as a parameter to point it at the
>database. It won't let me do that either. Any ideas, Regards.
>
Hi Chris,
First, it's better not to prefix your stored procedures with sp_. This
prefix has a special meaning to SQL Server, possibly causing unwanted
effects.
Second, datatype char defaults to char(1). Unless your database names
are only one letter long, this will fail. Use nvarchar(128) or sysname
instead.
Third, it is generally preferred to query the INFORMATION_SCHEMA views
instead of the system tables or stored procedures. These views are
ANSI-standard, making your code more portable.
If you want to use sp_tables, use dynamic SQL to concatenate a USE
command and the EXEC sp_tables command. If you prefer to use
INFORMATION_SCHEMA, use the query below (that also uses dynamic SQL).
CREATE PROCEDURE gettables
@.dbname sysname
AS
execute ('select * from ' + @.dbname + '.INFORMATION_SCHEMA.TABLES'
+ ' where TABLE_CATALOG = ''' + @.dbname + '''')
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Get the name of all user tables in a database

I want to have a stored procedures which when I pass it the name of a
database it will return all the names of the user tables. I have tried
CREATE PROCEDURE sp_gettables
@.dbname char
AS
EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =
"'Table'"
it won't do it as it can only work in its own context. I have also tried
using the use command with a database name as a parameter to point it at the
database. It won't let me do that either. Any ideas, Regards.How about this?
SELECT TABLE_SCHEMA, TABLE_NAME=20
FROM INFORMATION_SCHEMA.TABLES=20
WHERE TABLE_TYPE =3D 'BASE TABLE'
--=20
Keith
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message =
news:%23cDtEaoNEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I want to have a stored procedures which when I pass it the name of a
> database it will return all the names of the user tables. I have tried
>=20
> CREATE PROCEDURE sp_gettables
> @.dbname char
> AS
> EXEC sp_tables @.table_qualifier =3D "' + @.dbname + '", @.table_type =
=3D
> "'Table'"
>=20
> it won't do it as it can only work in its own context. I have also =
tried
> using the use command with a database name as a parameter to point it =
at the
> database. It won't let me do that either. Any ideas, Regards.
>=20
>|||On Mon, 10 May 2004 12:56:58 +0100, Chris Kennedy wrote:

>I want to have a stored procedures which when I pass it the name of a
>database it will return all the names of the user tables. I have tried
>CREATE PROCEDURE sp_gettables
>@.dbname char
>AS
>EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =
>"'Table'"
>it won't do it as it can only work in its own context. I have also tried
>using the use command with a database name as a parameter to point it at th
e
>database. It won't let me do that either. Any ideas, Regards.
>
Hi Chris,
First, it's better not to prefix your stored procedures with sp_. This
prefix has a special meaning to SQL Server, possibly causing unwanted
effects.
Second, datatype char defaults to char(1). Unless your database names
are only one letter long, this will fail. Use nvarchar(128) or sysname
instead.
Third, it is generally preferred to query the INFORMATION_SCHEMA views
instead of the system tables or stored procedures. These views are
ANSI-standard, making your code more portable.
If you want to use sp_tables, use dynamic SQL to concatenate a USE
command and the EXEC sp_tables command. If you prefer to use
INFORMATION_SCHEMA, use the query below (that also uses dynamic SQL).
CREATE PROCEDURE gettables
@.dbname sysname
AS
execute ('select * from ' + @.dbname + '.INFORMATION_SCHEMA.TABLES'
+ ' where TABLE_CATALOG = ''' + @.dbname + '''')
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

get Stored Procedures Parameters, how?

hi
i make smal Application to get information from SQL Server 2000 by using
VB6.
now i can get Databases, Tables and Colomn, and Stored Procedures, but my
problem how i can get SP Parametres?
i am thinking to make small function to get Parametres From SP.Text, but i
think its not good solution.
Tarek M. SialaMaybe you could cross-post to a few more groups, or try this search engine
called google before casting such a wide net. Anyway, here is one page that
might help. Followups set accordingly.
http://www.aspfaq.com/2463
"Tark Siala" <tarksiala@.icc-libya.com> wrote in message
news:edmJJ3scGHA.3908@.TK2MSFTNGP04.phx.gbl...
> hi
> i make smal Application to get information from SQL Server 2000 by using
> VB6.
> now i can get Databases, Tables and Colomn, and Stored Procedures, but my
> problem how i can get SP Parametres?
> i am thinking to make small function to get Parametres From SP.Text, but i
> think its not good solution.
> --
> Tarek M. Siala
>

Get Stored Procedures parameter names and types...

Sorry if I haven't choose appropriate forum for this question.

I have MSSQL05 beta. I know how to list all stored procedures in selected database (everything is in localhost). I need to list parameter names and types for selected stored procedure(s).
How can I do that or anything that can return parameter names and types?

It's windows application.
You should move to a non-beta version as you are breaking the EULA with using the old version, but anyway, the parameters can be views with the

INFORMATION_SCHEMA.Parameters

view.

SELECT *
FROM INFORMATION_SCHEMA.Parameters

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||I will move to non-beta.

Thanks for the reply. This is what I wanted.

Get Stored Procedure

I know you can alter and create stored procedures, but is there a way to select a stored procedure? I rewrote some stored procedures for a database and now I want to go and update all the sites using that database(copies), but first I want to make sure that there were no other changes. So what I want to do is to write a page that will get the current stored procedure, compare it to what it should be, and if they are the same only them update it. Is this at all possible?[YODA MODE]
query on sysobjects and syscomments, and the answer to your question you shall find.
[/YODA MODE]|||This code returns a checksum value on each database object:select sysobjects.id,
sysobjects.name,
sysobjects.type,
sum(cast(checksum(syscomments.text) as bigint)) Object_Checksum
from sysobjects
inner join syscomments on sysobjects.id = syscomments.id
group by sysobjects.id,
sysobjects.name,
sysobjects.typeIf the checksum value of the old object does not match the checksum value of the new object, then the object definition has changed.

Get return value from stored procedure and trigger

Hello there,

I searched for answers to the above topic, but could not find what I want. My stored procedures and triggers are returning a message based on the result, mostly error messages. How can I get that message using ASP.Net? Should I use an output parameter?

Thank you for your help.

Use this type dataset generator it will do it for you. you select which sp to use with a gui and paf you get a nice typed dataset class.

Enjoy I love it greatly

SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET...

DBHelper is a small tool that will generate either a source file or a compiled... No problem forDBHelper, just a few clicks and you a have all the methods...
www.codeproject.com/cs/database/dbhelper.asp

|||

Joel,

Thank you for the reply and a looking great tool. I will try it. I am not sure thought if it will answer, but trust you.

Another way I just found is that using Exception class in an event in .Net. For example, if you delete a row in a gridview and trigger is fired for some reason and raises an error, it is passed to theGridViewDeletedEventArgs.

So you can that message withGridViewDeletedEventArgs.Exception.Message. You can do it GridView1_RowDeleted() event.

I hope this helps someone like me.

get properties of a relation with SQL - DMO

Hi,
I am going to gather information of the database objects and (after a bypass
– stored procedures) use the SQL-DMO.
What I get information of relations (constraints, foreign keys as you like) ?
I found only Chek and Key objects.
How can I get actions on insert and delete event (’no action’ or ’cascade’).
I get key objects (LPSQLDMOKEY) of a table object (LPSQLDMOTABLE) and it
have only functions
GetName(&bstrName);
GetReferencedTable(&bstrRefTable);
GetReferencedKey(&bstrRefKey);
GetKeyColumns(&lpKeyColumns);
GetReferencedColumns(&lpBaseColumns);
I have to know :
base table
referenced table
column in base table (foreign key column)
column in referenced table (primary key column)
action insert
action delete
The first way was stored procedures, I had give it up on column properties
(identity and pk property was not clear) .
Now I am come up to relations (through columns and indexes) and type more
code. :-)
One more way what I know is the OLEDB schemas.
Give me hope to exists a solution and not need to throw away my program! :-)
Thanks for any idea,
Imre
=?Utf-8?B?SW1yZSBBbWVudA==?= <ImreAment@.discussions.microsoft.com> wrote
in news:1DF30CA9-4855-482F-91AD-164E2C6BB621@.microsoft.com:

> Hi,
> I am going to gather information of the database objects and (after a
> bypass – stored procedures) use the SQL-DMO.
> What I get information of relations (constraints, foreign keys as you
> like) ? I found only Chek and Key objects.
> How can I get actions on insert and delete event (’no action’ or
> ’cascade’).
I assume you mean Insert and Delete Triggers. These are to be found in
the Triggers collection of the Table object.

> I get key objects (LPSQLDMOKEY) of a table object
> (LPSQLDMOTABLE) and it have only functions
> GetName(&bstrName);
> GetReferencedTable(&bstrRefTable);
> GetReferencedKey(&bstrRefKey);
> GetKeyColumns(&lpKeyColumns);
> GetReferencedColumns(&lpBaseColumns);
These are "property getters", e.g. GetName gets the Name property of the
object.

> I have to know :
> base table
> referenced table
> column in base table (foreign key column)
> column in referenced table (primary key column)
> action insert
> action delete
> The first way was stored procedures, I had give it up on column
> properties (identity and pk property was not clear) .
> Now I am come up to relations (through columns and indexes) and type
> more code. :-)
> One more way what I know is the OLEDB schemas.
> Give me hope to exists a solution and not need to throw away my
> program! :-)
I recommend that you look at the SQL-DMO object model in the SQL Server
2000 Books Online (which has active links) or
http://msdn2.microsoft.com/en-us/library/ms141100.aspx with no active
links :-(
However, you should note that DMO is deprecated in favour of SMO and may
be removed in some future version. However, SMO is not available in
versions prior to SQL Server 2005.

get properties of a relation with SQL - DMO

Hi,
I am going to gather information of the database objects and (after a bypass
– stored procedures) use the SQL-DMO.
What I get information of relations (constraints, foreign keys as you like)
?
I found only Chek and Key objects.
How can I get actions on insert and delete event (’no action’ or ’casc
ade’).
I get key objects (LPSQLDMOKEY) of a table object (LPSQLDMOTABLE) and it
have only functions
GetName(&bstrName);
GetReferencedTable(&bstrRefTable);
GetReferencedKey(&bstrRefKey);
GetKeyColumns(&lpKeyColumns);
GetReferencedColumns(&lpBaseColumns);
I have to know :
base table
referenced table
column in base table (foreign key column)
column in referenced table (primary key column)
action insert
action delete
The first way was stored procedures, I had give it up on column properties
(identity and pk property was not clear) .
Now I am come up to relations (through columns and indexes) and type more
code. :-)
One more way what I know is the OLEDB schemas.
Give me hope to exists a solution and not need to throw away my program! :-)
Thanks for any idea,
Imreexamnotes <ImreAment@.discussions.microsoft.com> wrote
in news:1DF30CA9-4855-482F-91AD-164E2C6BB621@.microsoft.com:

> Hi,
> I am going to gather information of the database objects and (after a
> bypass – stored procedures) use the SQL-DMO.
> What I get information of relations (constraints, foreign keys as you
> like) ? I found only Chek and Key objects.
> How can I get actions on insert and delete event (’no action’ or
> ’cascade’).
I assume you mean Insert and Delete Triggers. These are to be found in
the Triggers collection of the Table object.

> I get key objects (LPSQLDMOKEY) of a table object
> (LPSQLDMOTABLE) and it have only functions
> GetName(&bstrName);
> GetReferencedTable(&bstrRefTable);
> GetReferencedKey(&bstrRefKey);
> GetKeyColumns(&lpKeyColumns);
> GetReferencedColumns(&lpBaseColumns);
These are "property getters", e.g. GetName gets the Name property of the
object.

> I have to know :
> base table
> referenced table
> column in base table (foreign key column)
> column in referenced table (primary key column)
> action insert
> action delete
> The first way was stored procedures, I had give it up on column
> properties (identity and pk property was not clear) .
> Now I am come up to relations (through columns and indexes) and type
> more code. :-)
> One more way what I know is the OLEDB schemas.
> Give me hope to exists a solution and not need to throw away my
> program! :-)
I recommend that you look at the SQL-DMO object model in the SQL Server
2000 Books Online (which has active links) or
http://msdn2.microsoft.com/en-us/library/ms141100.aspx with no active
links :-(
However, you should note that DMO is deprecated in favour of SMO and may
be removed in some future version. However, SMO is not available in
versions prior to SQL Server 2005.

get properties of a relation with SQL - DMO

Hi,
I am going to gather information of the database objects and (after a bypass
â' stored procedures) use the SQL-DMO.
What I get information of relations (constraints, foreign keys as you like) ?
I found only Chek and Key objects.
How can I get actions on insert and delete event (â'no actionâ' or â'cascadeâ').
I get key objects (LPSQLDMOKEY) of a table object (LPSQLDMOTABLE) and it
have only functions
GetName(&bstrName);
GetReferencedTable(&bstrRefTable);
GetReferencedKey(&bstrRefKey);
GetKeyColumns(&lpKeyColumns);
GetReferencedColumns(&lpBaseColumns);
I have to know :
base table
referenced table
column in base table (foreign key column)
column in referenced table (primary key column)
action insert
action delete
The first way was stored procedures, I had give it up on column properties
(identity and pk property was not clear) .
Now I am come up to relations (through columns and indexes) and type more
code. :-)
One more way what I know is the OLEDB schemas.
Give me hope to exists a solution and not need to throw away my program! :-)
Thanks for any idea,
Imre=?Utf-8?B?SW1yZSBBbWVudA==?= <ImreAment@.discussions.microsoft.com> wrote
in news:1DF30CA9-4855-482F-91AD-164E2C6BB621@.microsoft.com:
> Hi,
> I am going to gather information of the database objects and (after a
> bypass â' stored procedures) use the SQL-DMO.
> What I get information of relations (constraints, foreign keys as you
> like) ? I found only Chek and Key objects.
> How can I get actions on insert and delete event (â'no actionâ' or
> â'cascadeâ').
I assume you mean Insert and Delete Triggers. These are to be found in
the Triggers collection of the Table object.
> I get key objects (LPSQLDMOKEY) of a table object
> (LPSQLDMOTABLE) and it have only functions
> GetName(&bstrName);
> GetReferencedTable(&bstrRefTable);
> GetReferencedKey(&bstrRefKey);
> GetKeyColumns(&lpKeyColumns);
> GetReferencedColumns(&lpBaseColumns);
These are "property getters", e.g. GetName gets the Name property of the
object.
> I have to know :
> base table
> referenced table
> column in base table (foreign key column)
> column in referenced table (primary key column)
> action insert
> action delete
> The first way was stored procedures, I had give it up on column
> properties (identity and pk property was not clear) .
> Now I am come up to relations (through columns and indexes) and type
> more code. :-)
> One more way what I know is the OLEDB schemas.
> Give me hope to exists a solution and not need to throw away my
> program! :-)
I recommend that you look at the SQL-DMO object model in the SQL Server
2000 Books Online (which has active links) or
http://msdn2.microsoft.com/en-us/library/ms141100.aspx with no active
links :-(
However, you should note that DMO is deprecated in favour of SMO and may
be removed in some future version. However, SMO is not available in
versions prior to SQL Server 2005.