retriving data from a temporal table

Hi ,
I've created a stored procedure wich creates a temporal table (called
#results) , then i fill the table with data and finally at the end of the
procedure i make a "Select * FROM #Results" .
When i execute the procedure from the query analizer i can get the data
without any problem. But if i try to get the data into a visual basic ADO
recordset it always fails. I think the problem is because i'm using a
temporal table , but i need to use that solution.
If someone could give me one solution to get the data of a temporal table
into a recordset i'd been thankful.
Thanks in advance for you answers and pardon for my bad english.hi
u can use temp table when u call from VB program, but u need to do everythin
g
1. Create Table
2. Insert Data
3. Retrive data
in the same SP. else the data will be deleted / scope is lost
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Jorge Lozano" wrote:

> Hi ,
> I've created a stored procedure wich creates a temporal table (called
> #results) , then i fill the table with data and finally at the end of the
> procedure i make a "Select * FROM #Results" .
> When i execute the procedure from the query analizer i can get the data
> without any problem. But if i try to get the data into a visual basic ADO
> recordset it always fails. I think the problem is because i'm using a
> temporal table , but i need to use that solution.
> If someone could give me one solution to get the data of a temporal table
> into a recordset i'd been thankful.
> Thanks in advance for you answers and pardon for my bad english.|||My guess is that it will work if you add SET NOCOUNT ON in the beginning of
your stored procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jorge Lozano" <JorgeLozano@.discussions.microsoft.com> wrote in message
news:FED3495C-D379-457D-AE02-D9B188B156BC@.microsoft.com...
> Hi ,
> I've created a stored procedure wich creates a temporal table (called
> #results) , then i fill the table with data and finally at the end of the
> procedure i make a "Select * FROM #Results" .
> When i execute the procedure from the query analizer i can get the data
> without any problem. But if i try to get the data into a visual basic ADO
> recordset it always fails. I think the problem is because i'm using a
> temporal table , but i need to use that solution.
> If someone could give me one solution to get the data of a temporal table
> into a recordset i'd been thankful.
> Thanks in advance for you answers and pardon for my bad english.|||It worked Perfect!!!
Thanks a loot Tibor i owe you a very big beer.
"Tibor Karaszi" wrote:

> My guess is that it will work if you add SET NOCOUNT ON in the beginning o
f your stored procedure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jorge Lozano" <JorgeLozano@.discussions.microsoft.com> wrote in message
> news:FED3495C-D379-457D-AE02-D9B188B156BC@.microsoft.com...
>|||Watch out. I'm an excellent beer drinker ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jorge Lozano" <JorgeLozano@.discussions.microsoft.com> wrote in message
news:02F4D367-6A89-49C9-9971-BE5BA4927491@.microsoft.com...
> It worked Perfect!!!
> Thanks a loot Tibor i owe you a very big beer.
> "Tibor Karaszi" wrote:
>

Retrieve the Identifier of the newly added row

Howdie y'all,

I'm quit new to SQL server and I'm getting there finally, but it's quit hard to find some good info on how to create stored procedures... But I've got the following one...

CREATE PROCEDURE [dbo].[spAddUser]
@.UserEmail VARCHAR(255),
@.UserPassword VARCHAR(16),
@.UserName VARCHAR(32)
AS
INSERT INTO [dbo].[tblUsers](UserEmail, UserPassword, UserName)
VALUES (@.UserEmail, @.UserPassword, @.UserName)
GO

I actually would like to get value of the UserId column for the newly added record.

Can anyone of you folks help me with this?

Cheers,

Wes

UserID being a primary key and identity column? For example modifying the proc as follows

CREATE PROCEDURE [dbo].[spAddUser]
@.UserEmail VARCHAR(255),
@.UserPassword VARCHAR(16),
@.UserName VARCHAR(32),
@.NewID int OUTPUT
AS
INSERT INTO [dbo].[tblUsers](UserEmail, UserPassword, UserName)
VALUES (@.UserEmail, @.UserPassword, @.UserName)
SET @.NewID = SCOPE_IDENTITY()
GO

This way you can get it via output parameter after the query is executed.

|||

Thank for this fast answer! It works like a charm now!

Cheers,

Wes

|||And to tag onto Teemu's response...
You might find a function called @.@.IDENTITY that seems to return thesame kind of information. Don't be tempted to use it, however. Itreturns the last identity value added with a wider scope thanSCOPE_IDENTIY(). It's appropriate only in very rare situations, andnever in the scenario you are using.
Don
|||

Funny you mention the @.@.Identity... I first tried that. I found it on msdn but for some reason I didn't trust it so I decided to ask the question to people who know what they are doing instead of just copy paste the code.

I'll start my mcsd course soon now and will follow the path of sql-server. I think I'll start a website where people can get extensive info on how to create and enter stored procedures on sql-server to become an MVP. I've found it hard to get some good information. I see a lot of differences in approach and syntax( should I use OUTPUT or just OUT or are they the same?) and no real good tutorial on where to start and how to learn with explanation.

Thanks again you both,

Wesley