Problem returning two values from stored procedures

Hi, i am trying to return two values from SQL 2000 using a single stored procedure. The stored working fine in Query Analyser and returns the two values and two grids in the results window.

My problem is that when i execute the stored procedure using ADO.Net the dataset only has one of the values. e.g TId : 2, where it should read 'TId' : 2, 'ConfigPath': 'C:\blah'

Please could anyone shed ligth on this problem?

here the code for the stored procedure:

CREATE PROCEDURE dbo.GetTillInfo
(
@.TillIdR varchar(50),
@.Password varchar(50)
)
AS

declare @.TillId int
declare @.configpath varchar(150)

IF Exists (SELECT Id FROM Tills WHERE TillRef=@.TillId and TillPassword=@.Password)
BEGIN

set @.TillIdR = (SELECT Id FROM Tills WHERE TillRef=@.TillId and TillPassword=@.Password)
select @.TillIdR as 'TId'

set @.configpath = (SELECT configpath from customer,tills where
tills.customerid = customer.id and tills.id = @.login)
select @.configpath as 'ConfigPath'
END
ELSE
BEGIN
set @.TillIdR = 0
select @.TillIdR as 'TId'
set @.configpath =''
select @.configpath as 'ConfigPath'
END
GOOff the top of my head, the two results may be returned but in two tables as you are performing two selects.

To get round this you could change your select query to return the two values like:-

IF ...
 set @.TillIdR = (SELECT Id FROM Tills WHERE TillRef=@.TillId and TillPassword=@.Password)
 set @.configpath = (SELECT configpath from customer,tills where
 tills.customerid = customer.id and tills.id = @.login)
 select @.TillIdR as 'TId', @.configpath as 'ConfigPath'
END
ELSE
BEGIN
 set @.TillIdR = 0
 set @.configpath =''
 select @.TillIdR as 'TId', @.configpath as 'ConfigPath'
END
GO

This is off the top of my head at work - you may have to play with the stored proc.

Rob

problem returning IDENTITY

Hi all,
I have a sp where I only do an insert, and am trying to return the
identity value created. Here's what my sp resemble :
...
AS
SET NOCOUNT ON
-- do the insert
DECLARE @.ret
SELECT @.ret = SCOPE_IDENTITY()
RETURN @.ret
I tried to return directly SCOPE_IDENTITY(), I also tried to change
SCOPE_IDENTITY() with @.@.IDENTITY, with and without SET NOCOUNT ON...I
don't know what to do anymore. A few times it returned always 1 and at
other times, it was always returning -1. This depended on what options
I've tried, I don't remember what situation returned what value...but
for now, written as above, it is returning -1.
thanks for your help!
ibiza wrote:

> Hi all,
> I have a sp where I only do an insert, and am trying to return the
> identity value created. Here's what my sp resemble :
> ...
> AS
> SET NOCOUNT ON
> -- do the insert
> DECLARE @.ret
> SELECT @.ret = SCOPE_IDENTITY()
> RETURN @.ret
> I tried to return directly SCOPE_IDENTITY(), I also tried to change
> SCOPE_IDENTITY() with @.@.IDENTITY, with and without SET NOCOUNT ON...I
> don't know what to do anymore. A few times it returned always 1 and at
> other times, it was always returning -1. This depended on what options
> I've tried, I don't remember what situation returned what value...but
> for now, written as above, it is returning -1.
> thanks for your help!
It isn't a good idea to use RETURN to return data from a proc. Use
RETURN for error status only: zero = OK, non-zero = error. To return
other values use an output parameter or a result set:
CREATE PROC usp_x
(@.param1 INTEGER, @.ret INTEGER OUTPUT)
AS
SET NOCOUNT ON;
SET @.ret = 123;
RETURN
GO
DECLARE @.r INTEGER;
EXEC usp_x @.param1 = 1, @.ret = @.r OUTPUT;
SELECT @.r;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||thank you very much for your reply. Well, it does work with an output
parameter!
And I will take note of your remark for my upcoming posts.
Thanks again!

problem returning IDENTITY

Hi all,
I have a sp where I only do an insert, and am trying to return the
identity value created. Here's what my sp resemble :
...
AS
SET NOCOUNT ON
-- do the insert
DECLARE @.ret
SELECT @.ret = SCOPE_IDENTITY()
RETURN @.ret
I tried to return directly SCOPE_IDENTITY(), I also tried to change
SCOPE_IDENTITY() with @.@.IDENTITY, with and without SET NOCOUNT ON...I
don't know what to do anymore. A few times it returned always 1 and at
other times, it was always returning -1. This depended on what options
I've tried, I don't remember what situation returned what value...but
for now, written as above, it is returning -1.
thanks for your help!ibiza wrote:
> Hi all,
> I have a sp where I only do an insert, and am trying to return the
> identity value created. Here's what my sp resemble :
> ...
> AS
> SET NOCOUNT ON
> -- do the insert
> DECLARE @.ret
> SELECT @.ret = SCOPE_IDENTITY()
> RETURN @.ret
> I tried to return directly SCOPE_IDENTITY(), I also tried to change
> SCOPE_IDENTITY() with @.@.IDENTITY, with and without SET NOCOUNT ON...I
> don't know what to do anymore. A few times it returned always 1 and at
> other times, it was always returning -1. This depended on what options
> I've tried, I don't remember what situation returned what value...but
> for now, written as above, it is returning -1.
> thanks for your help!
It isn't a good idea to use RETURN to return data from a proc. Use
RETURN for error status only: zero = OK, non-zero = error. To return
other values use an output parameter or a result set:
CREATE PROC usp_x
(@.param1 INTEGER, @.ret INTEGER OUTPUT)
AS
SET NOCOUNT ON;
SET @.ret = 123;
RETURN
GO
DECLARE @.r INTEGER;
EXEC usp_x @.param1 = 1, @.ret = @.r OUTPUT;
SELECT @.r;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||thank you very much for your reply. Well, it does work with an output
parameter!
And I will take note of your remark for my upcoming posts.
Thanks again! :)

problem returning IDENTITY

Hi all,
I have a sp where I only do an insert, and am trying to return the
identity value created. Here's what my sp resemble :
...
AS
SET NOCOUNT ON
-- do the insert
DECLARE @.ret
SELECT @.ret = SCOPE_IDENTITY()
RETURN @.ret
I tried to return directly SCOPE_IDENTITY(), I also tried to change
SCOPE_IDENTITY() with @.@.IDENTITY, with and without SET NOCOUNT ON...I
don't know what to do anymore. A few times it returned always 1 and at
other times, it was always returning -1. This depended on what options
I've tried, I don't remember what situation returned what value...but
for now, written as above, it is returning -1.
thanks for your help!ibiza wrote:

> Hi all,
> I have a sp where I only do an insert, and am trying to return the
> identity value created. Here's what my sp resemble :
> ...
> AS
> SET NOCOUNT ON
> -- do the insert
> DECLARE @.ret
> SELECT @.ret = SCOPE_IDENTITY()
> RETURN @.ret
> I tried to return directly SCOPE_IDENTITY(), I also tried to change
> SCOPE_IDENTITY() with @.@.IDENTITY, with and without SET NOCOUNT ON...I
> don't know what to do anymore. A few times it returned always 1 and at
> other times, it was always returning -1. This depended on what options
> I've tried, I don't remember what situation returned what value...but
> for now, written as above, it is returning -1.
> thanks for your help!
It isn't a good idea to use RETURN to return data from a proc. Use
RETURN for error status only: zero = OK, non-zero = error. To return
other values use an output parameter or a result set:
CREATE PROC usp_x
(@.param1 INTEGER, @.ret INTEGER OUTPUT)
AS
SET NOCOUNT ON;
SET @.ret = 123;
RETURN
GO
DECLARE @.r INTEGER;
EXEC usp_x @.param1 = 1, @.ret = @.r OUTPUT;
SELECT @.r;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||thank you very much for your reply. Well, it does work with an output
parameter!
And I will take note of your remark for my upcoming posts.
Thanks again!

Problem returning HTMLEncoded text.

I am storing HTML inside a text field already HTMLEncoded. (example.
<body>) When I use the FOR XML EXPLICIT to return this field I get
&lt;body&gt; My text is being HTMLEncoded again.
Is there a way to prevent this from happening?
Thanks,
Ryan
Not to my knowledge, FOR XML automatically does the encoding.
Irwin Dolobowsky
Program Manager - SqlXml
http://weblogs.asp.net/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan Fiorini" <ryan.fiorini@.mutualofamerica.com> wrote in message
news:#o5U#iyFEHA.684@.tk2msftngp13.phx.gbl...
> I am storing HTML inside a text field already HTMLEncoded. (example.
> <body>) When I use the FOR XML EXPLICIT to return this field I get
> &lt;body&gt; My text is being HTMLEncoded again.
> Is there a way to prevent this from happening?
> Thanks,
> Ryan
>
|||Try to use the !xml directive in your FOR XML explicit query. This should
avoid entitization.
Best regards
Michael
"Irwin Dolobowsky[MS]" <irwind@.mail.microsoft.com> wrote in message
news:%233U1zw3FEHA.1600@.tk2msftngp13.phx.gbl...
> Not to my knowledge, FOR XML automatically does the encoding.
> --
> Irwin Dolobowsky
> Program Manager - SqlXml
> http://weblogs.asp.net/irwando
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Ryan Fiorini" <ryan.fiorini@.mutualofamerica.com> wrote in message
> news:#o5U#iyFEHA.684@.tk2msftngp13.phx.gbl...
get
>
sql

Problem populating temp table from linked server.

Hello,

I have a 2000 sql server linked to a 2005 sql server an I am trying to return data across the link. If I just run the sp I get the data back fine but if I try to Insert the data into a temp table the process just hangs and has to be killed.

This works fine:

EXEC [MyLink].[MyDocs].[dbo].[spGetSearchWrapper] -- (returns 156k records in about 2 sec.)

However inserting the results into a local temp table never returns. In fact the process never really runs.

CREATE TABLE #tmpOrgResult
(
intObjectID INT NOT NULL,
intObjectTypeCodeID INT NOT NULL

)
GO

-- Insert org records that match the search.
INSERT INTO #tmpOrgResult
(
intObjectID,
intObjectTypeCodeID
)
EXEC [MyLink].[MyDocs].[dbo].[spGetSearchWrapper] -- (This statement just hangs)

Try with

SET REMOTE_PROC_TRANSACTIONS OFF

before the INSERT statement. What I suspect happens is the local transaction is promoted to a distributed one and something gets messed up.

Zlatko

|||

You are correct it is getting promoted to a distributed transaction. Turns out MSDTC was off by default on the server. The following article showed me how to enable it. Works like a champ now.

http://support.microsoft.com/?kbid=873160

|||You are welcome.

problem on grant permission to user

I have two kinds of functions in the database that need to grant exec permission to user appUser.
The first kind of function return ordinary datatype, let's call it funcReturnDataType here.
The second kind of function return table datatype, let's call it funcReturnTable
When I issued the folloing command to appUser, no problem.
grant exec on funcReturnDataType to appUser

However when I issued:
grant exec on funcReturnTable to appUser

I got the following error message:
Server: Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.

Any suggestions to resolve this problem?

Thank you!I'm having the same problem. Can anyone help.|||

Maybe this will help you:

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

from the article:

....

permission
Specifies a permission that can be granted on a schema-contained object. For a list of the permissions, see the Remarks section later in this topic.

ALL
Granting ALL does not grant all possible permissions. Granting ALL is equivalent to granting all ANSI-92 permissions applicable to the specified object. The meaning of ALL varies as follows:

Scalar function permissions: EXECUTE, REFERENCES.
Table-valued function permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
Stored procedure permissions: EXECUTE, SYNONYM, DELETE, INSERT, SELECT, UPDATE.
Table permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
View permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.

....

problem on grant permission to user

I have two kinds of functions in the database that need to grant exec permission to user appUser.
The first kind of function return ordinary datatype, let's call it funcReturnDataType here.
The second kind of function return table datatype, let's call it funcReturnTable
When I issued the folloing command to appUser, no problem.
grant exec on funcReturnDataType to appUser

However when I issued:
grant exec on funcReturnTable to appUser

I got the following error message:
Server: Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.

Any suggestions to resolve this problem?

Thank you!I'm having the same problem. Can anyone help.|||

Maybe this will help you:

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

from the article:

....

permission
Specifies a permission that can be granted on a schema-contained object. For a list of the permissions, see the Remarks section later in this topic.

ALL
Granting ALL does not grant all possible permissions. Granting ALL is equivalent to granting all ANSI-92 permissions applicable to the specified object. The meaning of ALL varies as follows:

Scalar function permissions: EXECUTE, REFERENCES.
Table-valued function permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
Stored procedure permissions: EXECUTE, SYNONYM, DELETE, INSERT, SELECT, UPDATE.
Table permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
View permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.

....

problem maybe with IN

Hi all,

I've a SP that retrieve a list of users. The problem is that on this SP i want return a list of all users or only based on a specific filter gived by the customer.

I don't want ot use IF statements to see if for example the variables @.username contains something or is null.

Ok, so.. there is a method that return all the users if the variables is null and a specific user is the variable contain something without use condition?

Please note also that is not only one variable but can be more (ie, filter by password and email too)

Thanks for the help!

Crisif you dynamically build your query
and then execute it

will that do ?|||The query can be

select ...
from ...
where (field1 = @.param1 or @.param1 is null)
and (field2 = @.param2 or @.param2 is null)

Originally posted by bortolo
Hi all,

I've a SP that retrieve a list of users. The problem is that on this SP i want return a list of all users or only based on a specific filter gived by the customer.

I don't want ot use IF statements to see if for example the variables @.username contains something or is null.

Ok, so.. there is a method that return all the users if the variables is null and a specific user is the variable contain something without use condition?

Please note also that is not only one variable but can be more (ie, filter by password and email too)

Thanks for the help!

Cris|||select ...
from ...
where (field1 = @.param1 or @.param1 is null)
and (field2 = @.param2 or @.param2 is null)

nice solution !|||create proc sp_return_users (
@.uid char(10) = null,
@.pwd varchar(16) = null,
@.eml varchar(128) = null)
as
declare @.tbl table (
uid char(10) null, @.pwd varchar(16) null, @.eml varchar(128) null)

insert @.tbl values (isnull(@.uid, '%'), isnull(@.pwd, '%'), isull(@.eml, '%'))

select u.* from dbo.tblusers u
inner join @.tbl t
on (u.uid like t.uid
and u.pwd like t.pwd
and u.eml like t.eml)
go