return a value from SQL server SP back to .net

I have a SP code:
select 'nothing' from tableA where userID = '123'
if @.@.rowcount = 0
return 0
else
return 1

.net code:
Dim myConnection As New SqlConnection("server=(local);database=pubs;Trusted_Connection=yes")
Dim myCommand As New SqlCommand("StoreP", myConnection)

myCommand.Connection.Open()
Returnvalue = myCommand.ExecuteNonQuery()
myCommand.Connection.Close()

Returnvalue shows -1, it doesnt show the return value from SP. how do I fix this problem? thanksHi,

The return value from the ExecuteNonQuery returns the number of rows affected by the query, not the return value from the stored procedure. For a SELECT statement like you're using, it always returns -1.

To get the return value, you have to add a parameter to the ADO.NET command object, and set its Direction property to ReturnValue. Something like this:

myCommand.Parameters.Add("@.RetVal", SqlType.Integer).Direction = _
 ParameterDirection.ReturnValue

Then you can read the value after you run the query:

Dim i as Int32 = myCommand.Parameters("@.RetVal").Value

I've typed the code from memory, so it may need some tweaking.

Don|||I still have two questions
1. If I want to return 2 value from SP to asp.net, how do I do?
2. when I want to insert same value into PK twice in SP, it will give me a error message 2627, and the code break. How do I do to let SP return me an error message WITHOUT hanging the code. In other word, I am trying to let SP return the error message, but I dont want the asp.net web page to stop.

Thank you|||Hi,

1. If I want to return 2 value from SP to asp.net, how do I do?

Then use output parameters. You can define as many of those as you want. For them, use ParameterDirection.Output for the Direction property.

2. when I want to insert same value into PK twice in SP, it will give me a error message 2627, and the code break. How do I do to let SP return me an error message WITHOUT hanging the code. In other word, I am trying to let SP return the error message, but I dont want the asp.net web page to stop.

Probably the best way is to raise an error from the SP using the RAISERROR statement. That will generate a SqlException that you can catch and handle in your page.

Another way is to return one or more output parameters from the SP, one for an error number and another for a message. I don't like this option because it's more work and doesn't hook into the natural exception infrastructure of .NET.

Don|||Can you tell me how exactly you do it? I try parameterdirection.output it gives me an error "too many argument specified." how do I code in SP to return 2 values? thank you|||It sounds like you haven't added the output parameters to the stored procedure, right? You have to do that as well as add the ADO.NET code.

Post the complete sp definition and we'll help you make the changes.

Don|||the SP code is

CREATE PROCEDURE test @.aaa as varchar(10) output, @.bbb as varchar(10) output AS
select @.aaa = '111'
select @.bbb = '222'
return
GO

asp.net code is

Dim objCOmmand As New SqlCommand(strSQL, objConnection)
objConnection.Open()
objCOmmand.CommandType = CommandType.StoredProcedure
objCOmmand.Parameters.Add(New SqlParameter("@.aaa", SqlDbType.VarChar))
objCOmmand.Parameters.Add("@.aaa", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
objCOmmand.Parameters("@.aaa").Value = "aaa"
objCOmmand.Parameters.Add(New SqlParameter("@.bbb", SqlDbType.VarChar))
objCOmmand.Parameters.Add("@.bbb", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
objCOmmand.Parameters("@.bbb").Value = "bbb"
objCOmmand.ExecuteNonQuery()
Label1.Text = objCOmmand.Parameters("@.aaa").Value
Label2.Text = objCOmmand.Parameters("@.bbb").Value
objConnection.Close()

after SP I should get 111 in stead of aaa in @.aaa and 222 instead of bbb in @.bbb, but I get aaa and bbb in the result. how do I get the value return from SP?|||Your are not using the correct ParameterDirection. ReturnValue is solely to return the value that appears after the RETURN keyword in your stored procedure. Valid ParameterDirection values for stored procedure parameters are:
Input
Output
InputOutput

In your case, you should be using InputOutput for @.aaa and @.bbb since you are supplying data to the stored procedure (input) and are new receiving data back from the stored procedure (output).

Terri|||I try:

objCOmmand.Parameters.Add("@.bbb", SqlDbType.VarChar).Direction = ParameterDirection.InputOutput

but it gives me an error:

Parameter 1: '@.aaa' of type: String, the property Size has an invalid size: 0

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Parameter 1: '@.aaa' of type: String, the property Size has an invalid size: 0|||Since you are using a VarChar datatype, you need to specify the length. And I don't know if it matters, but I usually take 2 lines to add a parameter and set the direction:

objCommand.Parameters.Add("@.aaa", SqlDbType.VarChar, 10)
objCommand.Parameters("@.aaa").Direction=ParameterDirection.InputOutput
objCommand.Parameters.Add("@.bbb", SqlDbType.VarChar, 10)
objCommand.Parameters("@.bbb").Direction=ParameterDirection.InputOutput

Terri

Retuning rows as a single string

Is there any way to do this?
Say I have SELECT MyCol FROM MyTable
MyCol is a nvarchar field, but instead of coming back like this in a table
ItemA
ItemB
ItemC
I want it to come back as a stingle string like this
ItemA, ItemB, ItemC
is this possible in T-SQL? thanks!http://www.aspfaq.com/show.asp?id=2529

> Is there any way to do this?
> Say I have SELECT MyCol FROM MyTable
> MyCol is a nvarchar field, but instead of coming back like this in a table
> ItemA
> ItemB
> ItemC
> I want it to come back as a stingle string like this
> ItemA, ItemB, ItemC|||Try this:
Declare @.Results varchar(2000)
SELECT @.Results =
COALESCE(@.Results + ', ', '') + CAST(MyCol AS
varchar(3))
FROM
MyTable
Select @.Results
HTH
Barry|||Sorry that should read:
Declare @.Results nvarchar(2000)
SELECT @.Results =
COALESCE(@.Results + ', ', '') + MyCol
FROM
MyTable
Select @.Results|||didn't know about coalesce, thanks!
"Barry" <barry.oconnor@.manx.net> wrote in message
news:1145990261.449558.38970@.y43g2000cwc.googlegroups.com...
> Sorry that should read:
> Declare @.Results nvarchar(2000)
>
> SELECT @.Results =
> COALESCE(@.Results + ', ', '') + MyCol
> FROM
> MyTable
>
> Select @.Results
>

Retriving Deleted record from database

Hi friends

I have a bit problem here

Just I want to get back all deleted record of database

How do I perform this task?
If It is possible then plz help me out?

Thanks in Advance

Khan

if you have the backup which was taken before the datas were deleted you can make use of it and restore it any other server and retrieve your records................|||

There is no built-in command to undelete. However, you could use Log Explorer.

http://lumigent.com/products/le_sql.html

Retrieving Scalar or Calculated Values from Stored Procedures with C#

I am trying to build an Sql page hit provider. I am having trouble getting a count back from the database. If I use ExecuteScalar it doesn't see any value in the returned R1C1. If I use ExecuteNonQuery with a @.ReturnValue, the return value parameter value is always zero. Ideally I would like to use a dynamic stored proceudre if there are any suggestions for using them with C#. My table has rvPathName, userName and a date. I have the AddWebPageHit method working so I know data connection and sql support code in provider is working. I think the problem is either in how I am writing the stored procedures or how I am trying to retrieve the data in C#. Any help with this will be greatly appreciated.

We're not going to be able to help without seeing the code, both the C# code and the stored procedure code.|||

Here you go. I worked on this for more than 8 hours so know that you help is greatly appreciated!!!

CREATE PROCEDURE dbo.WebPageHits_CountByWebPageVPathName @.webPageVPathName nvarchar(256)

AS

DECLARE @.count int

SELECT @.count = COUNT(WebPageHitId)

FROM dbo.WebPageHits

WHERE WebPageVPathName = @.webPageVPathName

RETURN (@.count)

GO

publicoverrideint GetWebPageHitCount(string webPageVPathName)

{

SecUtility.CheckParameter(ref webPageVPathName,true,false,true, 256,"webPageVPathName");

SqlConnectionHolder connectionHolder =null;

SqlConnection connection =null;

int webPageHitCount = 0;

try

{

try

{

connectionHolder =SqlConnectionHelper.GetConnection(_sqlConnectionString,true);

connection = connectionHolder.Connection;

CheckSchemaVersion(connectionHolder.Connection);

SqlCommand cmd =newSqlCommand("dbo.WebPageHits_CountByWebPageVPathName", connection);

cmd.CommandType =CommandType.StoredProcedure;

cmd.CommandTimeout = CommandTimeout;

SqlParameter p =newSqlParameter("@.ReturnValue",SqlDbType.Int);

p.Direction =ParameterDirection.ReturnValue;

cmd.Parameters.Add(p);

cmd.Parameters.Add(CreateInputParam("@.webPageVPathName",SqlDbType.VarChar, webPageVPathName));

cmd.ExecuteNonQuery();

webPageHitCount = GetReturnValue(cmd);

}

finally

{

if (connectionHolder !=null)

{

connectionHolder.Close();

connectionHolder =null;

}

}

}

catch

{

throw;

}

return webPageHitCount;

}

|||

It's a little tough, as you are using custom methods such as GetReturnValue and CreateInputParam.

My gut instinct is that you are having a problem because youare both using the wrong data type for the @.webPageVPathName as well asnot specifying the length. The lack of length specification is mostlikely causing the trouble; if you don't specify it then a length of 1is used. Use the nvarchar datatype and a length of 256 in your C#code, and I think you will have better luck.

I will tell you that it's a better practice to use Output parameters to return values from a stored procedure rather than a ReturnValue. Return values are typically used to communicate success or failure; using them in the manner you are attempting just because the value you want to communicate back to the calling code happens to be an integer can be seen as "cheating". Use an Output parameter instead.

|||

You are right about my cheating. Sometimes you have to hear things from somebody else to actually realize it even though it's right in front of your face.

I got this to work by following the aspnet profile provider code. The provider uses this stored procedure.

aspnet_Profile_GetCountOf...

If just does SELECT COUNT(*) FROM ... with no RETURN statement. Then the C# uses ExecuteScalar then dim o as object = cmd then if o <> null return cmd. My function returns an int so somehow the value gets from the cmd object to the function's return value. I don't know but it's easier.

|||

mtsonic:

If just does SELECT COUNT(*) FROM ... with no RETURN statement. Then the C# uses ExecuteScalar then dim o as object = cmd then if o <> null return cmd. My function returns an int so somehow the value gets from the cmd object to the function's return value. I don't know but it's easier.

ExecuteScalar is an OK way to accomplish what you need. Glad you got it working :-)

sql

Retrieving output parameter from stored proc

I have difficulty reading back the value of an output parameter that I use in a stored procedure. I searched through other posts and found that this is quite a common problem but couldn't find an answer to it. Maybe now there is a knowledgeable person who could help out many people with a good answer.

The problem is that

cmd.Parameters["@.UserExists"].Value evaluates to null. If I call the stored procedure externally from the Server Management Studio Express everything works fine.

Here is my code:

using (SqlConnection cn =new SqlConnection(this.ConnectionString)){ SqlCommand cmd =new SqlCommand("mys_ExistsPersonWithUserName", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@.UserName", SqlDbType.VarChar).Value = userName; cmd.Parameters.Add("@.UserExists", SqlDbType.Int); cmd.Parameters["@.UserExists"].Direction = ParameterDirection.Output; cn.Open();int x = (int)cmd.Parameters["@.UserExists"].Value; cn.Close();return (x>1);}

And the corresponding stored procedure:

ALTER PROCEDURE dbo.mys_Spieler_ExistsPersonWithUserName (@.UserNamevarchar(16),@.UserExistsint OUTPUT)ASSET NOCOUNT ONSELECT @.UserExists =count(*)FROM mys_ProfilesWHERE UserName = @.UserNameRETURN

Hey,

Not sure what the problem is, but as an alternative, you can use a return method and define it as return value, or you can just select the result, and in your code, use ExecuteScalar().

sql

Retrieving Identity after insert

Hey,

I've been having problems - when trying to insert a new row i've been trying to get back the unique ID for that row. I've added "SELECT @.MY_ID = SCOPE_IDENTITY();" to my query but I am unable get the data. If anyone has a better approach to this let me know because I am having lots of problems.

Thanks,
Lang

hi,

can you try using @.@.Identity please.

morever please put some code what exactly you've done.

regards,

satish.

|||

Scope Identity is safer than @.@.Identity. @.@.Identity could possibly give you the wrong ID back if your table has triggers that also insert records.

Is @.MY_ID being returned as an output parameter or is this something you are simply doing in a stored procedure with no object/class interaction ?

Retrieving data from an attached mdf file

I attach my SQL Server Express data file to my host. I would like to copy all of my member information back to my local computer. How can I do this? My host won't allow my to physically copy the data file over.

My host is discountasp.net.

Thanks,
Jeff

Port 80 is obviously open, so create a web service to read the data. Lock the site to respond only to your external IP address to avoid anybody else being able to get at the data.

Retrieving autonum / IDENTIFIER value from SQL table using DAO.

Hello,

I am in the midst of converting an Access back end to SQL Server Express.
The front end program (converted to Access 2003) uses DAO throughout. In
Access, when I use recordset.AddNew I can retrieve the autonum value for the
new record. This doesn't occur with SQL Server, which of course causes an
error (or at least in this code it does since there's an unhandled NULL
value). Is there any way to retrieve this value when I add a new record
from SQL server or will I have to do it programmatically in VB?

Any direction would be great.

Thanks!Try:

select
scope_identity()

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"Rico" <r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN
CAPS> wrote in message news:1fB_f.527$7a.323@.pd7tw1no...
Hello,

I am in the midst of converting an Access back end to SQL Server Express.
The front end program (converted to Access 2003) uses DAO throughout. In
Access, when I use recordset.AddNew I can retrieve the autonum value for the
new record. This doesn't occur with SQL Server, which of course causes an
error (or at least in this code it does since there's an unhandled NULL
value). Is there any way to retrieve this value when I add a new record
from SQL server or will I have to do it programmatically in VB?

Any direction would be great.

Thanks!|||Rico (r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN CAPS)
writes:
> I am in the midst of converting an Access back end to SQL Server
> Express. The front end program (converted to Access 2003) uses DAO
> throughout. In Access, when I use recordset.AddNew I can retrieve the
> autonum value for the new record. This doesn't occur with SQL Server,
> which of course causes an error (or at least in this code it does since
> there's an unhandled NULL value). Is there any way to retrieve this
> value when I add a new record from SQL server or will I have to do it
> programmatically in VB?

It's better to use stored procedures to add data, rather than relying on
ADO generating code behind your back. It's easy for the Jet provider
to populate the Autonumber for you, because all operations are in your
process space. But since SQL Server is on the other end of the wire,
there is an extra roundtrip to get the value.

Also, with SQL Server, make sure that all your cursors are client-side.

A sample stored procedure:

CREATE PROCEDURE insert_tbl @.a int,
@.b datetime,
@.c varchar(23),
@.id int AS
INSERT tbl (a, b, c)
VALUES (@.a, @.b, @.c)
SELECT @.id = scope_identity

--
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|||Thanks Tom and Erland,

I wound up researching Scope_Identity and that lead me to @.@.identity. I
wound up changing my DAO code as follows;

Instead of...

dim MyNewID as long
set rst = db.OpenRecordset("MyTable")
rst.AddNew
rst!MyTextfield="My New Text"
MyNewID=rst!IDfield ' (this is the autonum field from the previous Access
db)
rst.Update

I changed the code to

dim MyNewID as long
set rst = db.OpenRecordset("MyTable")
rst.AddNew
rst!MyTextfield="My New Text"
rst.Update

MyNewID=db.OpenRecorset("SELECT @.@.Identity").Fields(0)

This seems to work in every case, since the @.@.Identity line gets the last ID
created on your specific connection whether someone else updates the
database as the same time or not. In other words, if I update the database
at the same time another user updates the database, the @.@.Identity will
never pass me back the other users ID field since that wasn't created on my
connection.

Although my tests have proven successful, if anyone has exprience using this
with DAO and has had any failures, please let me know.

Erland, I wish I knew more about creating stored procedures, because I'd
like to centralize as much of this kind of thing as I can, but at this point
I have to stick with what I know. Thanks for the info.

Rick

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A2F243F7168Yazorman@.127.0.0.1...
> Rico (r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN
> CAPS)
> writes:
>> I am in the midst of converting an Access back end to SQL Server
>> Express. The front end program (converted to Access 2003) uses DAO
>> throughout. In Access, when I use recordset.AddNew I can retrieve the
>> autonum value for the new record. This doesn't occur with SQL Server,
>> which of course causes an error (or at least in this code it does since
>> there's an unhandled NULL value). Is there any way to retrieve this
>> value when I add a new record from SQL server or will I have to do it
>> programmatically in VB?
> It's better to use stored procedures to add data, rather than relying on
> ADO generating code behind your back. It's easy for the Jet provider
> to populate the Autonumber for you, because all operations are in your
> process space. But since SQL Server is on the other end of the wire,
> there is an extra roundtrip to get the value.
> Also, with SQL Server, make sure that all your cursors are client-side.
> A sample stored procedure:
> CREATE PROCEDURE insert_tbl @.a int,
> @.b datetime,
> @.c varchar(23),
> @.id int AS
> INSERT tbl (a, b, c)
> VALUES (@.a, @.b, @.c)
> SELECT @.id = scope_identity
>
>
> --
> 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|||Don't use @.@.IDENTITY. You can have incorrect results if your INSERT fires a
trigger which itself inserts into a table with an identity. Use
SCOPE_IDENTITY().

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"Rico" <r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN
CAPS> wrote in message news:sG9%f.5965$WI1.5577@.pd7tw2no...
Thanks Tom and Erland,

I wound up researching Scope_Identity and that lead me to @.@.identity. I
wound up changing my DAO code as follows;

Instead of...

dim MyNewID as long
set rst = db.OpenRecordset("MyTable")
rst.AddNew
rst!MyTextfield="My New Text"
MyNewID=rst!IDfield ' (this is the autonum field from the previous Access
db)
rst.Update

I changed the code to

dim MyNewID as long
set rst = db.OpenRecordset("MyTable")
rst.AddNew
rst!MyTextfield="My New Text"
rst.Update

MyNewID=db.OpenRecorset("SELECT @.@.Identity").Fields(0)

This seems to work in every case, since the @.@.Identity line gets the last ID
created on your specific connection whether someone else updates the
database as the same time or not. In other words, if I update the database
at the same time another user updates the database, the @.@.Identity will
never pass me back the other users ID field since that wasn't created on my
connection.

Although my tests have proven successful, if anyone has exprience using this
with DAO and has had any failures, please let me know.

Erland, I wish I knew more about creating stored procedures, because I'd
like to centralize as much of this kind of thing as I can, but at this point
I have to stick with what I know. Thanks for the info.

Rick

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A2F243F7168Yazorman@.127.0.0.1...
> Rico (r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN
> CAPS)
> writes:
>> I am in the midst of converting an Access back end to SQL Server
>> Express. The front end program (converted to Access 2003) uses DAO
>> throughout. In Access, when I use recordset.AddNew I can retrieve the
>> autonum value for the new record. This doesn't occur with SQL Server,
>> which of course causes an error (or at least in this code it does since
>> there's an unhandled NULL value). Is there any way to retrieve this
>> value when I add a new record from SQL server or will I have to do it
>> programmatically in VB?
> It's better to use stored procedures to add data, rather than relying on
> ADO generating code behind your back. It's easy for the Jet provider
> to populate the Autonumber for you, because all operations are in your
> process space. But since SQL Server is on the other end of the wire,
> there is an extra roundtrip to get the value.
> Also, with SQL Server, make sure that all your cursors are client-side.
> A sample stored procedure:
> CREATE PROCEDURE insert_tbl @.a int,
> @.b datetime,
> @.c varchar(23),
> @.id int AS
> INSERT tbl (a, b, c)
> VALUES (@.a, @.b, @.c)
> SELECT @.id = scope_identity
>
>
> --
> 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|||Tom Moreau (tom@.dont.spam.me.cips.ca) writes:
> Don't use @.@.IDENTITY. You can have incorrect results if your INSERT
> fires a trigger which itself inserts into a table with an identity. Use
> SCOPE_IDENTITY().

Then again, there are cases where @.@.identity will give you the correct
result, and scope_identity() will not.

Now, I don't know how DAO works, but the suggestion to use scope_identity()
relies on the somewhat risky assumption that .AddNew performs a straight
insert. If DAO sets up a prepared query, run sp_executesql, or runs some
temporary stored procedure, scope_identity will not work. Since DAO is
a fairly old API, I would not expect it to be too sophisticated. Then
again, using scope_identity() means that you rely on the implementation
of something that could change with a service pack or a new release. (Not
that such are bloodly likely for DAO.)

Using @.@.identity is better, because it relies at least only on your
own application and schema which you have more control over.

--
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|||Rico (r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN CAPS)
writes:
> I wound up researching Scope_Identity and that lead me to @.@.identity. I
> wound up changing my DAO code as follows;
>...
> Erland, I wish I knew more about creating stored procedures, because I'd
> like to centralize as much of this kind of thing as I can, but at this
> point I have to stick with what I know. Thanks for the info.

Not only that, DAO is an API that has been deprecated for a long time.
The recommended API for an Access application today, I guess still is
ADO. (Which, I will have to admit, is an API that I don't like very
much at all.)

--
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|||It is enormously absurd to use DAO with MS-SQL Server.
It is enormously absurd for the OP to say he will not learn about
Stored Procedures.
It is enormously absurd to use ODBC and DAO with MS-SQL.
I KNOW, knowledgeable insiders say that is the route to take.
I say the knowledgeable insiders say so because they want to promote
Access as a front end for MS-SQL to those who are too lazy or and or
too stupid to learn MS-SQL and ADO.
Moreover, to those who are offended by this I say, "Get off you ass and
learn your trade and then you won't be!"|||Hi Erland

> Then again, there are cases where @.@.identity will give you the correct
> result, and scope_identity() will not.

Could you give an example of when this might occur?

--
-Dick Christoph
dchristo@.mn.rr.com
612-724-9282
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A3F2A2F1723Yazorman@.127.0.0.1...
> Tom Moreau (tom@.dont.spam.me.cips.ca) writes:
>> Don't use @.@.IDENTITY. You can have incorrect results if your INSERT
>> fires a trigger which itself inserts into a table with an identity. Use
>> SCOPE_IDENTITY().
> Then again, there are cases where @.@.identity will give you the correct
> result, and scope_identity() will not.
> Now, I don't know how DAO works, but the suggestion to use
> scope_identity()
> relies on the somewhat risky assumption that .AddNew performs a straight
> insert. If DAO sets up a prepared query, run sp_executesql, or runs some
> temporary stored procedure, scope_identity will not work. Since DAO is
> a fairly old API, I would not expect it to be too sophisticated. Then
> again, using scope_identity() means that you rely on the implementation
> of something that could change with a service pack or a new release. (Not
> that such are bloodly likely for DAO.)
> Using @.@.identity is better, because it relies at least only on your
> own application and schema which you have more control over.
> --
> 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|||DickChristoph (dchristo99@.yahoo.com) writes:

>> Then again, there are cases where @.@.identity will give you the correct
>> result, and scope_identity() will not.
> Could you give an example of when this might occur?

CREATE TABLE #xyz(a int IDENTITY, b int NOT NULL)
go
EXEC sp_executesql N'INSERT #xyz(b) VALUES(@.b)', N'@.b int', 12
SELECT scope_identity(), @.@.identity
do
DROP TABLE #xyz

While the example may look contrived, many client API uses sp_executesql
or similar under the hood. scope_identity() returns the latest generated
identity value in the current scope, so if you call back a second time
from the client to get the value, you can only hope the both commands
excecuted in the top scope of the connection.

--
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|||I am in a similar situation to you and am trying the following:

theRecord.AddNew
' new data values
theRecord.Update
theRecord.Bookmark = theRecord.LastModified
theNewID = theRecord("ID")

I expect the experts will find this wanting but, so far, it seems to
work. I suppose that there might be a timing issue immediately after
the Update.|||Lyle, this isn't a ground up application, this is converting a clients
legacy application. The bean counters have better things to do with their
budget than build a new version of something they are already using.

I never said I wouldn't learn about stored procedures, but don't have the
time in this case.

"Lyle Fairfield" <lylefairfield@.aim.com> wrote in message
news:1144906536.025890.26030@.j33g2000cwa.googlegro ups.com...
> It is enormously absurd to use DAO with MS-SQL Server.
> It is enormously absurd for the OP to say he will not learn about
> Stored Procedures.
> It is enormously absurd to use ODBC and DAO with MS-SQL.
> I KNOW, knowledgeable insiders say that is the route to take.
> I say the knowledgeable insiders say so because they want to promote
> Access as a front end for MS-SQL to those who are too lazy or and or
> too stupid to learn MS-SQL and ADO.
> Moreover, to those who are offended by this I say, "Get off you ass and
> learn your trade and then you won't be!"|||Hi Tom,

Just so you know, triggers and other server side operations will not affect
the @.@.identity result and hence, will not return an incorrect result.

Rick

"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:Pwa%f.3830$L.26943@.news20.bellglobal.com...
> Don't use @.@.IDENTITY. You can have incorrect results if your INSERT fires
> a
> trigger which itself inserts into a table with an identity. Use
> SCOPE_IDENTITY().
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Rico" <r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN
> CAPS> wrote in message news:sG9%f.5965$WI1.5577@.pd7tw2no...
> Thanks Tom and Erland,
> I wound up researching Scope_Identity and that lead me to @.@.identity. I
> wound up changing my DAO code as follows;
> Instead of...
> dim MyNewID as long
> set rst = db.OpenRecordset("MyTable")
> rst.AddNew
> rst!MyTextfield="My New Text"
> MyNewID=rst!IDfield ' (this is the autonum field from the previous Access
> db)
> rst.Update
>
> I changed the code to
> dim MyNewID as long
> set rst = db.OpenRecordset("MyTable")
> rst.AddNew
> rst!MyTextfield="My New Text"
> rst.Update
> MyNewID=db.OpenRecorset("SELECT @.@.Identity").Fields(0)
> This seems to work in every case, since the @.@.Identity line gets the last
> ID
> created on your specific connection whether someone else updates the
> database as the same time or not. In other words, if I update the
> database
> at the same time another user updates the database, the @.@.Identity will
> never pass me back the other users ID field since that wasn't created on
> my
> connection.
> Although my tests have proven successful, if anyone has exprience using
> this
> with DAO and has had any failures, please let me know.
> Erland, I wish I knew more about creating stored procedures, because I'd
> like to centralize as much of this kind of thing as I can, but at this
> point
> I have to stick with what I know. Thanks for the info.
> Rick
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns97A2F243F7168Yazorman@.127.0.0.1...
>> Rico (r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN
>> CAPS)
>> writes:
>>> I am in the midst of converting an Access back end to SQL Server
>>> Express. The front end program (converted to Access 2003) uses DAO
>>> throughout. In Access, when I use recordset.AddNew I can retrieve the
>>> autonum value for the new record. This doesn't occur with SQL Server,
>>> which of course causes an error (or at least in this code it does since
>>> there's an unhandled NULL value). Is there any way to retrieve this
>>> value when I add a new record from SQL server or will I have to do it
>>> programmatically in VB?
>>
>> It's better to use stored procedures to add data, rather than relying on
>> ADO generating code behind your back. It's easy for the Jet provider
>> to populate the Autonumber for you, because all operations are in your
>> process space. But since SQL Server is on the other end of the wire,
>> there is an extra roundtrip to get the value.
>>
>> Also, with SQL Server, make sure that all your cursors are client-side.
>>
>> A sample stored procedure:
>>
>> CREATE PROCEDURE insert_tbl @.a int,
>> @.b datetime,
>> @.c varchar(23),
>> @.id int AS
>> INSERT tbl (a, b, c)
>> VALUES (@.a, @.b, @.c)
>> SELECT @.id = scope_identity
>>
>>
>>
>>
>> --
>> 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|||"Rico" <r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN
CAPS> wrote in news:caw%f.9156$P01.6110@.pd7tw3no:

> Hi Tom,
> Just so you know, triggers and other server side operations will not
> affect the @.@.identity result and hence, will not return an incorrect
> result.
> Rick

That seems to be the opposite of what this exert from SQL 2005 BOL says.
I have made two sections UpperCase.

----
"SCOPE_IDENTITY, IDENT_CURRENT, and @.@.IDENTITY are similar functions
because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a
specified table. IDENT_CURRENT returns the value generated for a specific
table in any session and any scope. For more information, see
IDENT_CURRENT (Transact-SQL).

SCOPE_IDENTITY and @.@.IDENTITY return the last identity values that are
generated in any table in the current session. However, SCOPE_IDENTITY
returns values inserted only within the current scope; @.@.IDENTITY is not
limited to a specific scope.

For example, there are two tables, T1 and T2, and an INSERT trigger is
defined on T1. WHEN A ROW IS INSERTED TO T1, THE TRIGGER FIRES AND
INSERTS A ROW IN T2. This scenario illustrates two scopes: the insert on
T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @.@.IDENTITY and
SCOPE_IDENTITY will return different values at the end of an INSERT
statement on T1. @.@.IDENTITY WILL RETURN THE LAST IDENTITY COLUMN VALUE
INSERTED ACROSS ANY SCOPE IN THE CURRENT SESSION. THIS IS THE VALUE
INSERTED IN T2. SCOPE_IDENTITY() will return the IDENTITY value inserted
in T1. This was the last insert that occurred in the same scope. The
SCOPE_IDENTITY() function will return the null value if the function is
invoked before any INSERT statements into an identity column occur in the
scope.

Failed statements and transactions can change the current identity for a
table and create gaps in the identity column values. The identity value
is never rolled back even though the transaction that tried to insert the
value into the table is not committed. For example, if an INSERT
statement fails because of an IGNORE_DUP_KEY violation, the current
identity value for the table is still incremented."

----

A session is described as:

By default, a session starts when a user logs in and ends when the user
logs off. All operations during a session are subject to permission
checks against that user.

--
Lyle Fairfield|||Hmmm,

My mistake. Never believe what you read the first time I guess. I got the
info from an MSDN forum page, but didn't bookmark the page, so I'll have to
find it again. I did find reference to something similar in the MSDN
library which mentions returning the expected Identity value after a trigger
has fired on a table without an identity field. Luckily there are no
triggers on this DB at this point, so that will at least buy me some time
until we can get something mapped out for the client.

Rick

"Lyle Fairfield" <lylefairfield@.aim.com> wrote in message
news:Xns97A48FC10EA65lylefairfieldaimcom@.216.221.8 1.119...
> "Rico" <r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN
> CAPS> wrote in news:caw%f.9156$P01.6110@.pd7tw3no:
>> Hi Tom,
>>
>> Just so you know, triggers and other server side operations will not
>> affect the @.@.identity result and hence, will not return an incorrect
>> result.
>>
>> Rick
> That seems to be the opposite of what this exert from SQL 2005 BOL says.
> I have made two sections UpperCase.
> ----
> "SCOPE_IDENTITY, IDENT_CURRENT, and @.@.IDENTITY are similar functions
> because they return values that are inserted into identity columns.
> IDENT_CURRENT is not limited by scope and session; it is limited to a
> specified table. IDENT_CURRENT returns the value generated for a specific
> table in any session and any scope. For more information, see
> IDENT_CURRENT (Transact-SQL).
> SCOPE_IDENTITY and @.@.IDENTITY return the last identity values that are
> generated in any table in the current session. However, SCOPE_IDENTITY
> returns values inserted only within the current scope; @.@.IDENTITY is not
> limited to a specific scope.
> For example, there are two tables, T1 and T2, and an INSERT trigger is
> defined on T1. WHEN A ROW IS INSERTED TO T1, THE TRIGGER FIRES AND
> INSERTS A ROW IN T2. This scenario illustrates two scopes: the insert on
> T1, and the insert on T2 by the trigger.
> Assuming that both T1 and T2 have identity columns, @.@.IDENTITY and
> SCOPE_IDENTITY will return different values at the end of an INSERT
> statement on T1. @.@.IDENTITY WILL RETURN THE LAST IDENTITY COLUMN VALUE
> INSERTED ACROSS ANY SCOPE IN THE CURRENT SESSION. THIS IS THE VALUE
> INSERTED IN T2. SCOPE_IDENTITY() will return the IDENTITY value inserted
> in T1. This was the last insert that occurred in the same scope. The
> SCOPE_IDENTITY() function will return the null value if the function is
> invoked before any INSERT statements into an identity column occur in the
> scope.
> Failed statements and transactions can change the current identity for a
> table and create gaps in the identity column values. The identity value
> is never rolled back even though the transaction that tried to insert the
> value into the table is not committed. For example, if an INSERT
> statement fails because of an IGNORE_DUP_KEY violation, the current
> identity value for the table is still incremented."
> ----
> A session is described as:
> By default, a session starts when a user logs in and ends when the user
> logs off. All operations during a session are subject to permission
> checks against that user.
>
> --
> Lyle Fairfield|||Rico (r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN CAPS)
writes:
> Lyle, this isn't a ground up application, this is converting a clients
> legacy application. The bean counters have better things to do with their
> budget than build a new version of something they are already using.

Nevermind the stored procedures, but not ripping out DAO while you're
at it, seems wrong to me. I don't know much about DAO, but since it is
a deprecated interface, there is risk that you will run into issues in
SQL Server that are not supported when you use DAO. (The most typical
example would be new data types.)

--
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|||Rico wrote:
> Thanks Tom and Erland,
> I wound up researching Scope_Identity and that lead me to @.@.identity. I
> wound up changing my DAO code as follows;
> Instead of...
> dim MyNewID as long
> set rst = db.OpenRecordset("MyTable")
> rst.AddNew
> rst!MyTextfield="My New Text"
> MyNewID=rst!IDfield ' (this is the autonum field from the previous Access
> db)
> rst.Update
>
> I changed the code to
> dim MyNewID as long
> set rst = db.OpenRecordset("MyTable")
> rst.AddNew
> rst!MyTextfield="My New Text"
> rst.Update
> MyNewID=db.OpenRecorset("SELECT @.@.Identity").Fields(0)

If you use an ADODB.Recordset with the correct property settings
the the new record will be added to the recordset you have open
and the newly added record will be the current record.|||!!!!!
LOL
What fun is there is you give good smart simple answers?

Retrieveing objects

Dears,
I created a table, then dropped it by mistake. Is there any way to retrieve it back using the log file? The lat DB backup was made before creating the table.
Thanks,1. Take a log backup
2. make another database and restore the previous full backup over there as you would not want to make any changes to the original db
3. restore transaction log to a time before you dropped the table
4. copy table onto original database

tell me if you need any other help|||When applying the backup, through the Enterprise Manager, I had the "Point in time restore" option disabled. Have an idea why? Do you think making the backup through the T-SQL better?

Originally posted by Enigma
1. Take a log backup
2. make another database and restore the previous full backup over there as you would not want to make any changes to the original db
3. restore transaction log to a time before you dropped the table
4. copy table onto original database

tell me if you need any other help|||Question : What is your database recovery model

Simple , Full or Bulk Logged

Give me a list of the things you have done till now ...

Retrieve Domain Name

Is there a way to pull back the domain name that a server is in from within
SQL?
ie @.@.servername returns the server/instance, something similar for the
domain is what I am looking for.
to capture the start time, end time, application name, client host name, NT
user name and the domain name.
DECLARE @.TraceID int, @.DB_ID int
EXEC CreateTrace
'C:\My SQL Traces\ProceduresCalledInMSDB',
@.OutputTraceID = @.TraceID OUT
EXEC AddEvent
@.TraceID,
'SP:Completed',
'TextData, StartTime, EndTime, ApplicationName, ClientHostName, NTUserName,
NTDomainName, DatabaseID'
SET @.DB_ID = DB_ID('msdb')
EXEC AddFilter
@.TraceID,
'DatabaseID',
@.DB_ID
EXEC StartTrace 1
GO
"Thom" wrote:

> Is there a way to pull back the domain name that a server is in from within
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.
|||Sorry all, this is not within a trace but just at a sql analyzer window or
within a stored proc?
"Thom" wrote:

> Is there a way to pull back the domain name that a server is in from within
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.
|||I don't think there is any direct statement in SQL to get the domain name.
I think these statement will give the domain information for a login or
associated to the server:
xp_loginconfig
xp_enumgroups
xp_logininfo
"Thom" wrote:

> Is there a way to pull back the domain name that a server is in from within
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.

Retrieve Domain Name

Is there a way to pull back the domain name that a server is in from within
SQL?
ie @.@.servername returns the server/instance, something similar for the
domain is what I am looking for.to capture the start time, end time, application name, client host name, NT
user name and the domain name.
DECLARE @.TraceID int, @.DB_ID int
EXEC CreateTrace
'C:\My SQL Traces\ProceduresCalledInMSDB',
@.OutputTraceID = @.TraceID OUT
EXEC AddEvent
@.TraceID,
'SP:Completed',
'TextData, StartTime, EndTime, ApplicationName, ClientHostName, NTUserName,
NTDomainName, DatabaseID'
SET @.DB_ID = DB_ID('msdb')
EXEC AddFilter
@.TraceID,
'DatabaseID',
@.DB_ID
EXEC StartTrace 1
GO
"Thom" wrote:
> Is there a way to pull back the domain name that a server is in from within
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.|||Sorry all, this is not within a trace but just at a sql analyzer window or
within a stored proc?
"Thom" wrote:
> Is there a way to pull back the domain name that a server is in from within
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.|||I don't think there is any direct statement in SQL to get the domain name.
I think these statement will give the domain information for a login or
associated to the server:
xp_loginconfig
xp_enumgroups
xp_logininfo
"Thom" wrote:
> Is there a way to pull back the domain name that a server is in from within
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.

Retrieve Domain Name

Is there a way to pull back the domain name that a server is in from within
SQL?
ie @.@.servername returns the server/instance, something similar for the
domain is what I am looking for.to capture the start time, end time, application name, client host name, NT
user name and the domain name.
DECLARE @.TraceID int, @.DB_ID int
EXEC CreateTrace
'C:\My SQL Traces\ProceduresCalledInMSDB',
@.OutputTraceID = @.TraceID OUT
EXEC AddEvent
@.TraceID,
'SP:Completed',
'TextData, StartTime, EndTime, ApplicationName, ClientHostName, NTUserName,
NTDomainName, DatabaseID'
SET @.DB_ID = DB_ID('msdb')
EXEC AddFilter
@.TraceID,
'DatabaseID',
@.DB_ID
EXEC StartTrace 1
GO
"Thom" wrote:

> Is there a way to pull back the domain name that a server is in from withi
n
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.|||Sorry all, this is not within a trace but just at a sql analyzer window or
within a stored proc?
"Thom" wrote:

> Is there a way to pull back the domain name that a server is in from withi
n
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.|||I don't think there is any direct statement in SQL to get the domain name.
I think these statement will give the domain information for a login or
associated to the server:
xp_loginconfig
xp_enumgroups
xp_logininfo
"Thom" wrote:

> Is there a way to pull back the domain name that a server is in from withi
n
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.

Retrieve and write

Hello,
I'm trying to retrieve data from Sql Server 2000 in XML format and HTTP it
to a gateway. And write the response in XML from gateway back to SQL Server.
Can anyone please advice me which is the best way to do it?
Thanks
ReddYou have three options with SQL Server 2000:
1. Take a look at the SQLXML component (version 3.0 is available for free
download from msd.microsoft.com). This provides you with an ISAPI extension.
2. Write your own web service and use the SQLXML component and the database
functionality (FOR XML, OpenXML and stored procs) using either ASP or
ASP.Net.
3. Use one of the third party tools.
Best regards
Michael
"Redd" <Redd@.discussions.microsoft.com> wrote in message
news:FD9F7B95-58E9-4955-B23B-3A8DC005512C@.microsoft.com...
> Hello,
> I'm trying to retrieve data from Sql Server 2000 in XML format and HTTP it
> to a gateway. And write the response in XML from gateway back to SQL
> Server.
> Can anyone please advice me which is the best way to do it?
> Thanks
> Redd|||Thanks Michael,
Your reply was very helpful. Since I'm new to SQL - XML mechanism, I have
couple more questions if you don't mind.
1. When I generate an XML output from SQLSERVER 2000 how can I HTTP it to
another server.
2. When I get the XML response from the server where would the XML file
reside and how can I access it to write data back into my database.
I would be very greatful to you I you can help me out with this.
Thanks
Redd
"Michael Rys [MSFT]" wrote:

> You have three options with SQL Server 2000:
> 1. Take a look at the SQLXML component (version 3.0 is available for free
> download from msd.microsoft.com). This provides you with an ISAPI extensio
n.
> 2. Write your own web service and use the SQLXML component and the databas
e
> functionality (FOR XML, OpenXML and stored procs) using either ASP or
> ASP.Net.
> 3. Use one of the third party tools.
> Best regards
> Michael
> "Redd" <Redd@.discussions.microsoft.com> wrote in message
> news:FD9F7B95-58E9-4955-B23B-3A8DC005512C@.microsoft.com...
>
>|||And also could you please let me know how to add root elements(which are not
in my database) to XML output using stored procedure.
For example:
my XML output file looks like this when I retrieve data from SQLSERVER 2000
using XML AUTO, ELEMENTS
<GHI>
<JKL>
<MNO>
.....
.....
</MNO>
</JKL>
</GHI>
Now I want to add:
<?XML VERSION=.........?>
<ABC>
<DEF>
<GHI>
.......
.......
</GHI>
</DEF>
</ABC>
How can I add <ABC>, <DEF> elements for each <GHI> element
Any idea would help
Thanks
Redd
"Redd" wrote:
> Thanks Michael,
> Your reply was very helpful. Since I'm new to SQL - XML mechanism, I have
> couple more questions if you don't mind.
> 1. When I generate an XML output from SQLSERVER 2000 how can I HTTP it to
> another server.
> 2. When I get the XML response from the server where would the XML file
> reside and how can I access it to write data back into my database.
> I would be very greatful to you I you can help me out with this.
> Thanks
> Redd
> "Michael Rys [MSFT]" wrote:
>|||The three options I give you below are the ways to "HTTP the XML to another
server". You need to make your database server accessible through HTTP by
using one of these 3 approaches (see their documentation for more details on
how to set up the ISAPI for example). Using the SQLXML 3.0 ISAPI and the
SQLXML templates is probably the approach that gives you the quickest way to
doing it.
Since HTTP is a stateless protocol that transport everything (well, in its
most basic form), the XML will be part of the response payload over HTTP
(similar to how you would get the HTML from a webserver). So you would get
the XML into your other application. You then do your changes and you will
have to communicate your changes back. Again, there are a couple of ways to
do it:
1. You can use the SQLXML 3.0 updategrams. This works if your model fits the
model of the updategrams.
2. You keep track of your changes and only send changes back against the
relational data.
3. You send the whole XML document back and use stored procs to perform the
change analysis (using OpenXML).
Again, you can expose this through an HTTP based service (e.g., a SQLXML
template or a stored proc).
Best regards
Michael
"Redd" <Redd@.discussions.microsoft.com> wrote in message
news:E468C8D6-730E-4576-98F8-C9456CB904AF@.microsoft.com...
> Thanks Michael,
> Your reply was very helpful. Since I'm new to SQL - XML mechanism, I have
> couple more questions if you don't mind.
> 1. When I generate an XML output from SQLSERVER 2000 how can I HTTP it to
> another server.
> 2. When I get the XML response from the server where would the XML file
> reside and how can I access it to write data back into my database.
> I would be very greatful to you I you can help me out with this.
> Thanks
> Redd
> "Michael Rys [MSFT]" wrote:
>|||Adding the XML declaration (<?xml ... ?> ) is not needed assuming the data
you are getting is in the right output encoding (which you can set on the
provider). Also you can set the root node property on the OLEDB, ADO and
ADO.Net providers that will add the root element around the fragment.
The property should be described in the SQLXML documentation.
Best regards
Michael
"Redd" <Redd@.discussions.microsoft.com> wrote in message
news:0C8C2554-C69E-4E29-992F-D02E2BC61FFF@.microsoft.com...
> And also could you please let me know how to add root elements(which are
> not
> in my database) to XML output using stored procedure.
> For example:
> my XML output file looks like this when I retrieve data from SQLSERVER
> 2000
> using XML AUTO, ELEMENTS
> <GHI>
> <JKL>
> <MNO>
> ......
> ......
> </MNO>
> </JKL>
> </GHI>
> Now I want to add:
> <?XML VERSION=.........?>
> <ABC>
> <DEF>
> <GHI>
> ........
> ........
> </GHI>
> </DEF>
> </ABC>
> How can I add <ABC>, <DEF> elements for each <GHI> element
> Any idea would help
> Thanks
> Redd
> "Redd" wrote:
>

Retrieve and write

Hello,
I'm trying to retrieve data from Sql Server 2000 in XML format and HTTP it
to a gateway. And write the response in XML from gateway back to SQL Server.
Can anyone please advice me which is the best way to do it?
Thanks
Redd
You have three options with SQL Server 2000:
1. Take a look at the SQLXML component (version 3.0 is available for free
download from msd.microsoft.com). This provides you with an ISAPI extension.
2. Write your own web service and use the SQLXML component and the database
functionality (FOR XML, OpenXML and stored procs) using either ASP or
ASP.Net.
3. Use one of the third party tools.
Best regards
Michael
"Redd" <Redd@.discussions.microsoft.com> wrote in message
news:FD9F7B95-58E9-4955-B23B-3A8DC005512C@.microsoft.com...
> Hello,
> I'm trying to retrieve data from Sql Server 2000 in XML format and HTTP it
> to a gateway. And write the response in XML from gateway back to SQL
> Server.
> Can anyone please advice me which is the best way to do it?
> Thanks
> Redd
|||Thanks Michael,
Your reply was very helpful. Since I'm new to SQL - XML mechanism, I have
couple more questions if you don't mind.
1. When I generate an XML output from SQLSERVER 2000 how can I HTTP it to
another server.
2. When I get the XML response from the server where would the XML file
reside and how can I access it to write data back into my database.
I would be very greatful to you I you can help me out with this.
Thanks
Redd
"Michael Rys [MSFT]" wrote:

> You have three options with SQL Server 2000:
> 1. Take a look at the SQLXML component (version 3.0 is available for free
> download from msd.microsoft.com). This provides you with an ISAPI extension.
> 2. Write your own web service and use the SQLXML component and the database
> functionality (FOR XML, OpenXML and stored procs) using either ASP or
> ASP.Net.
> 3. Use one of the third party tools.
> Best regards
> Michael
> "Redd" <Redd@.discussions.microsoft.com> wrote in message
> news:FD9F7B95-58E9-4955-B23B-3A8DC005512C@.microsoft.com...
>
>
|||And also could you please let me know how to add root elements(which are not
in my database) to XML output using stored procedure.
For example:
my XML output file looks like this when I retrieve data from SQLSERVER 2000
using XML AUTO, ELEMENTS
<GHI>
<JKL>
<MNO>
......
......
</MNO>
</JKL>
</GHI>
Now I want to add:
<?XML VERSION=.........?>
<ABC>
<DEF>
<GHI>
........
........
</GHI>
</DEF>
</ABC>
How can I add <ABC>, <DEF> elements for each <GHI> element
Any idea would help
Thanks
Redd
"Redd" wrote:
[vbcol=seagreen]
> Thanks Michael,
> Your reply was very helpful. Since I'm new to SQL - XML mechanism, I have
> couple more questions if you don't mind.
> 1. When I generate an XML output from SQLSERVER 2000 how can I HTTP it to
> another server.
> 2. When I get the XML response from the server where would the XML file
> reside and how can I access it to write data back into my database.
> I would be very greatful to you I you can help me out with this.
> Thanks
> Redd
> "Michael Rys [MSFT]" wrote:
|||The three options I give you below are the ways to "HTTP the XML to another
server". You need to make your database server accessible through HTTP by
using one of these 3 approaches (see their documentation for more details on
how to set up the ISAPI for example). Using the SQLXML 3.0 ISAPI and the
SQLXML templates is probably the approach that gives you the quickest way to
doing it.
Since HTTP is a stateless protocol that transport everything (well, in its
most basic form), the XML will be part of the response payload over HTTP
(similar to how you would get the HTML from a webserver). So you would get
the XML into your other application. You then do your changes and you will
have to communicate your changes back. Again, there are a couple of ways to
do it:
1. You can use the SQLXML 3.0 updategrams. This works if your model fits the
model of the updategrams.
2. You keep track of your changes and only send changes back against the
relational data.
3. You send the whole XML document back and use stored procs to perform the
change analysis (using OpenXML).
Again, you can expose this through an HTTP based service (e.g., a SQLXML
template or a stored proc).
Best regards
Michael
"Redd" <Redd@.discussions.microsoft.com> wrote in message
news:E468C8D6-730E-4576-98F8-C9456CB904AF@.microsoft.com...[vbcol=seagreen]
> Thanks Michael,
> Your reply was very helpful. Since I'm new to SQL - XML mechanism, I have
> couple more questions if you don't mind.
> 1. When I generate an XML output from SQLSERVER 2000 how can I HTTP it to
> another server.
> 2. When I get the XML response from the server where would the XML file
> reside and how can I access it to write data back into my database.
> I would be very greatful to you I you can help me out with this.
> Thanks
> Redd
> "Michael Rys [MSFT]" wrote:
|||Adding the XML declaration (<?xml ... ?>) is not needed assuming the data
you are getting is in the right output encoding (which you can set on the
provider). Also you can set the root node property on the OLEDB, ADO and
ADO.Net providers that will add the root element around the fragment.
The property should be described in the SQLXML documentation.
Best regards
Michael
"Redd" <Redd@.discussions.microsoft.com> wrote in message
news:0C8C2554-C69E-4E29-992F-D02E2BC61FFF@.microsoft.com...[vbcol=seagreen]
> And also could you please let me know how to add root elements(which are
> not
> in my database) to XML output using stored procedure.
> For example:
> my XML output file looks like this when I retrieve data from SQLSERVER
> 2000
> using XML AUTO, ELEMENTS
> <GHI>
> <JKL>
> <MNO>
> ......
> ......
> </MNO>
> </JKL>
> </GHI>
> Now I want to add:
> <?XML VERSION=.........?>
> <ABC>
> <DEF>
> <GHI>
> ........
> ........
> </GHI>
> </DEF>
> </ABC>
> How can I add <ABC>, <DEF> elements for each <GHI> element
> Any idea would help
> Thanks
> Redd
> "Redd" wrote: