problem with SP to return last @@Identity

I need to retun the last inserted Identity value. - The SP below has a synta
x
error on the Set line. How can I fix it?
create procedure stp_GetIdentity
@.e int output
as
SET NOCOUNT ON
Set @.e = Select @.@.Identity
return
go
Thanks,
Rich"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:E3FF5947-5ED7-4E0A-BCD7-486D27C51375@.microsoft.com...
>I need to retun the last inserted Identity value. - The SP below has a
>syntax
> error on the Set line. How can I fix it?
> create procedure stp_GetIdentity
> @.e int output
> as
> SET NOCOUNT ON
> Set @.e = Select @.@.Identity
> return
> go
> Thanks,
> Rich
Set @.e = @.@.Identity
or
Select @.e = @.@.Identity
Option 1 is preferred for a single assignment.
Also look up scope_identity() in BOL.|||The offending line should be
SELECT @.e = @.@.IDENTITY
however, why create a stored procedure to get @.@.IDENTITY, when you can just
retrieve its value within a batch using SELECT @.@.IDENTITY?
"Rich" wrote:

> I need to retun the last inserted Identity value. - The SP below has a syn
tax
> error on the Set line. How can I fix it?
> create procedure stp_GetIdentity
> @.e int output
> as
> SET NOCOUNT ON
> Set @.e = Select @.@.Identity
> return
> go
> Thanks,
> Rich|||create procedure stp_GetIdentity
@.e int output
as
SET NOCOUNT ON
Select @.e = @.@.Identity
return
go
--This seems to work
"Rich" wrote:

> I need to retun the last inserted Identity value. - The SP below has a syn
tax
> error on the Set line. How can I fix it?
> create procedure stp_GetIdentity
> @.e int output
> as
> SET NOCOUNT ON
> Set @.e = Select @.@.Identity
> return
> go
> Thanks,
> Rich|||Thanks. From what I understand Scope_Identity works within a specified scop
e
which I interpret to mean if you insert a row into tbl1 which contains 10
rows in one procedure and also insert a row into tbl2 which contains 700 row
s
in another procedure and you only want to return the Identity value in tbl1
you could use Scope_Identity.
May I ask how Scope_Identity would be implemented in my SP to return the
Identity value of the last inserted row into tbl1?
?
"Raymond D'Anjou" wrote:

> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:E3FF5947-5ED7-4E0A-BCD7-486D27C51375@.microsoft.com...
> Set @.e = @.@.Identity
> or
> Select @.e = @.@.Identity
> Option 1 is preferred for a single assignment.
> Also look up scope_identity() in BOL.
>
>|||"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:6E159771-9CB3-45ED-95D1-7EF10E72DFFA@.microsoft.com...
> Thanks. From what I understand Scope_Identity works within a specified
> scope
> which I interpret to mean if you insert a row into tbl1 which contains 10
> rows in one procedure and also insert a row into tbl2 which contains 700
> rows
> in another procedure and you only want to return the Identity value in
> tbl1
> you could use Scope_Identity.
> May I ask how Scope_Identity would be implemented in my SP to return the
> Identity value of the last inserted row into tbl1?
>
set @.a = scope_identity()
scope_identity() has another advantage.
If you have a trigger on a table that inserts a row into another table with
an identity column.
@.@.identity in your stored procedure will return the ID of the last insert,
that is, the one in your trigger.
scope_identity() will return the ID you want.sql

Problem with Setting a variable in SQL String

Hi,

I am having problems setting the value of a variable in a SQL String
that I have to create dynamically in my procedure. The code that I
currently have is as follows:

set @.sqlStatement='Set @.compare_string=' + '(Select ' +
@.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'

exec(@.sqlStatement)

The error message that I get is as follows:

Must declare the variable '@.compare_string'.

Here @.compare_string has already been declared in the procedure and I
don't have a problem using the variable anywhere else but this SQL
Statement (when called using the EXEC function).

I am not sure why SQL Server can't see the variable declared when used
in a string in conjunction with EXEC. Is this a syntax issue? Any help
on this issue would be greatly appreciated!

Thanks in advance.You need a parms string and an exec string, like this:

SET @.Parms = `@.compare_string`

set @.sqlStatement='Set @.compare_string=' + '(Select ' +
@.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'

EXECUTE sp_executesql @.sqlStatement, @.Parms, @.compare_string

SET @.Error = COALESCE ( NULLIF ( @.Error, 0 ), @.@.ERROR )

> exec(@.sqlStatement)
> The error message that I get is as follows:
> Must declare the variable '@.compare_string'.
> Here @.compare_string has already been declared in the procedure and I
> don't have a problem using the variable anywhere else but this SQL
> Statement (when called using the EXEC function).
> I am not sure why SQL Server can't see the variable declared when used
> in a string in conjunction with EXEC. Is this a syntax issue? Any help
> on this issue would be greatly appreciated!
> Thanks in advance.|||Thanks for your reply. The sp_executesql procedure still doesn't give
the desired results. I am posting the updated piece of code and sample
output from the Query Analyzer.

------
set @.parameter_String=N'@.compare_string nvarchar(4000)'

set @.sqlStatement='Set @.compare_string=(Select ' +
@.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'

Print @.sqlStatement
EXECUTE sp_executesql @.sqlStatement,@.parameter_String,@.compare_string

Print @.compare_String
------

When I print the value of @.compare_String in the end its a NULL.
However, if I run the same query without the set @.compare_string
clause, it does work perfectly and returns the values of two columns
concatenated together. Any clues as to where I might be going wrong?

Thanks,

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bs9mbk$jt0$1$8300dec7@.news.demon.co.uk>...
> You need a parms string and an exec string, like this:
> SET @.Parms = `@.compare_string`
> set @.sqlStatement='Set @.compare_string=' + '(Select ' +
> @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> EXECUTE sp_executesql @.sqlStatement, @.Parms, @.compare_string
> SET @.Error = COALESCE ( NULLIF ( @.Error, 0 ), @.@.ERROR )
> > exec(@.sqlStatement)
> > The error message that I get is as follows:
> > Must declare the variable '@.compare_string'.
> > Here @.compare_string has already been declared in the procedure and I
> > don't have a problem using the variable anywhere else but this SQL
> > Statement (when called using the EXEC function).
> > I am not sure why SQL Server can't see the variable declared when used
> > in a string in conjunction with EXEC. Is this a syntax issue? Any help
> > on this issue would be greatly appreciated!
> > Thanks in advance.|||[posted and mailed, please reply in news]

Aamer Nazir (aamernazir_01@.hotmail.com) writes:
> I am having problems setting the value of a variable in a SQL String
> that I have to create dynamically in my procedure. The code that I
> currently have is as follows:
>
> set @.sqlStatement='Set @.compare_string=' + '(Select ' +
> @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> exec(@.sqlStatement)
> The error message that I get is as follows:
> Must declare the variable '@.compare_string'.
> Here @.compare_string has already been declared in the procedure and I
> don't have a problem using the variable anywhere else but this SQL
> Statement (when called using the EXEC function).

The EXEC() statement is another scope which is not part of your procedure.
Thus, @.compare_string is not defined in that example.

For better examples than the one posted, see
http://support.microsoft.com/?id=262499 and
http://www.sommarskog.se/dynamic_sql.html#sp_executesql.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes, that won't work. Sorry, I just focused on the parameter part.

You can just do this:

select @.compare_string = mytable.myfield FROM mytable where Identity_Column
= myvalue

or, in your specific case:

'Select @.compare_string=' + @.group_column_list_mod + ' from ' + @.Tbl_Name +
'_Sorted' + ' where Identity_Column=' + ltrim(rtrim(str @.loop_counter))'

At least this is the syntax you should use in this case. Otherwise, you are
effectively trying to bind @.compare_string to a recordset result, which
doesn't work.

Make sure you add in the error checking afterwards!! :)

"Aamer Nazir" <aamernazir_01@.hotmail.com> wrote in message
news:60b6d0a1.0312231058.14540a2c@.posting.google.c om...
> Thanks for your reply. The sp_executesql procedure still doesn't give
> the desired results. I am posting the updated piece of code and sample
> output from the Query Analyzer.
>
> ------
> set @.parameter_String=N'@.compare_string nvarchar(4000)'
> set @.sqlStatement='Set @.compare_string=(Select ' +
> @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> Print @.sqlStatement
> EXECUTE sp_executesql @.sqlStatement,@.parameter_String,@.compare_string
> Print @.compare_String
> ------
> When I print the value of @.compare_String in the end its a NULL.
> However, if I run the same query without the set @.compare_string
> clause, it does work perfectly and returns the values of two columns
> concatenated together. Any clues as to where I might be going wrong?
> Thanks,
>
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:<bs9mbk$jt0$1$8300dec7@.news.demon.co.uk>...
> > You need a parms string and an exec string, like this:
> > SET @.Parms = `@.compare_string`
> > set @.sqlStatement='Set @.compare_string=' + '(Select ' +
> > @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> > Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> > EXECUTE sp_executesql @.sqlStatement, @.Parms, @.compare_string
> > SET @.Error = COALESCE ( NULLIF ( @.Error, 0 ), @.@.ERROR )
> > > > exec(@.sqlStatement)
> > > > The error message that I get is as follows:
> > > > Must declare the variable '@.compare_string'.
> > > > Here @.compare_string has already been declared in the procedure and I
> > > don't have a problem using the variable anywhere else but this SQL
> > > Statement (when called using the EXEC function).
> > > > I am not sure why SQL Server can't see the variable declared when used
> > > in a string in conjunction with EXEC. Is this a syntax issue? Any help
> > > on this issue would be greatly appreciated!
> > > > Thanks in advance.|||Thanks for pointing me to the right direction. The code works
perfectly fine now. The problem was with the syntax that Erland
Sommarskog mentioned in his posting. You have to specify the parameter
type (input or output) in the parameter specification string (the
second argument to sp_executesql).

Best Regards,

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bsboku$o7p$1$8300dec7@.news.demon.co.uk>...
> Yes, that won't work. Sorry, I just focused on the parameter part.
> You can just do this:
> select @.compare_string = mytable.myfield FROM mytable where Identity_Column
> = myvalue
> or, in your specific case:
> 'Select @.compare_string=' + @.group_column_list_mod + ' from ' + @.Tbl_Name +
> '_Sorted' + ' where Identity_Column=' + ltrim(rtrim(str @.loop_counter))'
> At least this is the syntax you should use in this case. Otherwise, you are
> effectively trying to bind @.compare_string to a recordset result, which
> doesn't work.
> Make sure you add in the error checking afterwards!! :)
> "Aamer Nazir" <aamernazir_01@.hotmail.com> wrote in message
> news:60b6d0a1.0312231058.14540a2c@.posting.google.c om...
> > Thanks for your reply. The sp_executesql procedure still doesn't give
> > the desired results. I am posting the updated piece of code and sample
> > output from the Query Analyzer.
> > ------
> > set @.parameter_String=N'@.compare_string nvarchar(4000)'
> > set @.sqlStatement='Set @.compare_string=(Select ' +
> > @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> > Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> > Print @.sqlStatement
> > EXECUTE sp_executesql @.sqlStatement,@.parameter_String,@.compare_string
> > Print @.compare_String
> > ------
> > When I print the value of @.compare_String in the end its a NULL.
> > However, if I run the same query without the set @.compare_string
> > clause, it does work perfectly and returns the values of two columns
> > concatenated together. Any clues as to where I might be going wrong?
> > Thanks,
> > "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:<bs9mbk$jt0$1$8300dec7@.news.demon.co.uk>...
> > > You need a parms string and an exec string, like this:
> > > > SET @.Parms = `@.compare_string`
> > > > set @.sqlStatement='Set @.compare_string=' + '(Select ' +
> > > @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> > > Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> > > > EXECUTE sp_executesql @.sqlStatement, @.Parms, @.compare_string
> > > > SET @.Error = COALESCE ( NULLIF ( @.Error, 0 ), @.@.ERROR )
> > > > > > > exec(@.sqlStatement)
> > > > > > The error message that I get is as follows:
> > > > > > Must declare the variable '@.compare_string'.
> > > > > > Here @.compare_string has already been declared in the procedure and I
> > > > don't have a problem using the variable anywhere else but this SQL
> > > > Statement (when called using the EXEC function).
> > > > > > I am not sure why SQL Server can't see the variable declared when used
> > > > in a string in conjunction with EXEC. Is this a syntax issue? Any help
> > > > on this issue would be greatly appreciated!
> > > > > > Thanks in advance.

problem with scope_identity

Hi,
after inserting a record in a table (sql server), i need the last value of
the primary key of that table, which increments automatically, before
inserting that value in another table.
I did like this:
comd.CommandText = "insert into [mytable] (field1, field2) values(@.datbeg
,@.datend')"
comd.Parameters.Add("@.datbeg", SqlDbType.DateTime).Value = tda
comd.Parameters.Add("@.datend", SqlDbType.DateTime).Value = tda2
connection.Open()
comd.CommandText = "DECLARE @.orderid int"
comd.CommandText = "SET @.orderid = SCOPE_IDENTITY()"
comd.CommandText = "select @.orderid"
Dim x As Integer
x = Convert.ToInt32(comd.ExecuteScalar())
This giives an error:
"error: Must declare the scalar variable "@.orderid". "
Thanks
Dan
Hi Dan,
Your code and approach have a number of problems. Please stick with me; I'm
not being critical... just pointing out the facts:
1. In every line where you have [comd.CommandText = ...] you are completely
changing the value of comd.CommandText. That is, each line *overwrites* the
previous value of CommandText. So, when you finally get around to
comd.ExecuteScalar(), the value of comd.CommandText is simply "select
@.orderid".
2. The solution to the above problem (of overwriting the value of
CommandText in each successive line) is to concatenate the incremental
values, possibly via +=, and ensuring you add a blank space between each).
But you DON'T want to do that in your situation because of #3 below:
3. It appears that you are trying to create a stored procedure without
creating one (and instead putting all of the T-SQL in your CommandText.
There's no way that's going to work the way you are attempting.
What will work is to do the following:
1. Create a stored procedure that does the INSERT, followed immediately by
SET @.orderid = SCOPE_IDENTITY, and returning exactly one result set via
SELECT @.OrderID.
Then In your client code
2. Set CommandText = name of the stored procedure
3. Set ComandType = CommandType.StoredProcedure
4. Add to the Command.Parameters collection one SqlParameter object for each
of the parameters in the stored procedure.
5. Finally execute the stored procedure via the ExecuteScalar method (as you
were already trying to do).
The above assumes you have opened a connection etc..
-HTH
"Dan" <d@.er.df> wrote in message
news:OKUHPpawHHA.4568@.TK2MSFTNGP03.phx.gbl...
> Hi,
> after inserting a record in a table (sql server), i need the last value of
> the primary key of that table, which increments automatically, before
> inserting that value in another table.
> I did like this:
> comd.CommandText = "insert into [mytable] (field1, field2) values(@.datbeg
> ,@.datend')"
> comd.Parameters.Add("@.datbeg", SqlDbType.DateTime).Value = tda
> comd.Parameters.Add("@.datend", SqlDbType.DateTime).Value = tda2
> connection.Open()
> comd.CommandText = "DECLARE @.orderid int"
> comd.CommandText = "SET @.orderid = SCOPE_IDENTITY()"
> comd.CommandText = "select @.orderid"
> Dim x As Integer
> x = Convert.ToInt32(comd.ExecuteScalar())
> This giives an error:
> "error: Must declare the scalar variable "@.orderid". "
> Thanks
> Dan
>
|||Thanks, you're right of course with the concatenation.
But, instead of using a stored procedure (which i know is beter), would it
be posiible to do that in code-behind, more or less like this:
comd.CommandText = "DECLARE @.orderid int," _
& "SET @.orderid = SCOPE_IDENTITY()," _
& "select @.orderid"
Dim x As Integer
x = Convert.ToInt32(comd.ExecuteScalar())
because i get the error:
Incorrect syntax near the keyword 'SET'.
Incorrect syntax near ',
Thanks again
"Bob Johnson" <A@.B.COM> schreef in bericht
news:u$t07KbwHHA.4736@.TK2MSFTNGP04.phx.gbl...
> Hi Dan,
> Your code and approach have a number of problems. Please stick with me;
> I'm not being critical... just pointing out the facts:
> 1. In every line where you have [comd.CommandText = ...] you are
> completely changing the value of comd.CommandText. That is, each line
> *overwrites* the previous value of CommandText. So, when you finally get
> around to comd.ExecuteScalar(), the value of comd.CommandText is simply
> "select @.orderid".
> 2. The solution to the above problem (of overwriting the value of
> CommandText in each successive line) is to concatenate the incremental
> values, possibly via +=, and ensuring you add a blank space between each).
> But you DON'T want to do that in your situation because of #3 below:
> 3. It appears that you are trying to create a stored procedure without
> creating one (and instead putting all of the T-SQL in your CommandText.
> There's no way that's going to work the way you are attempting.
> What will work is to do the following:
> 1. Create a stored procedure that does the INSERT, followed immediately by
> SET @.orderid = SCOPE_IDENTITY, and returning exactly one result set via
> SELECT @.OrderID.
> Then In your client code
> 2. Set CommandText = name of the stored procedure
> 3. Set ComandType = CommandType.StoredProcedure
> 4. Add to the Command.Parameters collection one SqlParameter object for
> each of the parameters in the stored procedure.
> 5. Finally execute the stored procedure via the ExecuteScalar method (as
> you were already trying to do).
> The above assumes you have opened a connection etc..
> -HTH
>
>
> "Dan" <d@.er.df> wrote in message
> news:OKUHPpawHHA.4568@.TK2MSFTNGP03.phx.gbl...
>
|||Dan wrote:
> Thanks, you're right of course with the concatenation.
> But, instead of using a stored procedure (which i know is beter), would it
> be posiible to do that in code-behind, more or less like this:
> comd.CommandText = "DECLARE @.orderid int," _
> & "SET @.orderid = SCOPE_IDENTITY()," _
> & "select @.orderid"
> Dim x As Integer
> x = Convert.ToInt32(comd.ExecuteScalar())
> because i get the error:
> Incorrect syntax near the keyword 'SET'.
> Incorrect syntax near ',
Use semicolon to separate the SQL statements, then it might work.
comd.CommandText = "DECLARE @.orderid int;" _
& "SET @.orderid = SCOPE_IDENTITY();" _
& "select @.orderid"
But why not simply:
comd.CommandText = "select SCOPE_IDENTITY()"
Gran Andersson
_____
http://www.guffa.com
|||RE:
<< then it might work >>
Right - can you (op) please let us know if you get this to work? I'm
curious.
|||"Gran Andersson" <guffa@.guffa.com> wrote in message
news:O4wM4jbwHHA.4300@.TK2MSFTNGP04.phx.gbl...
> Dan wrote:
> Use semicolon to separate the SQL statements, then it might work.
> comd.CommandText = "DECLARE @.orderid int;" _
> & "SET @.orderid = SCOPE_IDENTITY();" _
> & "select @.orderid"
> But why not simply:
> comd.CommandText = "select SCOPE_IDENTITY()"
>
A couple of other thoughts:
1. set comd.CommandType = CommandType.Text
2. In all those strings you are concatenating for the .CommandText value, be
sure to add white space where appropriate.
3. to test this, first get the script to work in query analyzer (SS2K) or
Management Studio (2005). Once it works there, then move it to your client
code.
-HTH
|||Yes, it works like this:
comd.CommandText = "insert into [mytable] (field1, field2) values(@.datbeg
,@.datend');" _
& " select SCOPE_IDENTITY()"
Thanks
|||How are you populating @.datbeg and @.datend? That query won't work unless you
send parameters. Is that your actual query?
Just curious. Thanks!
"Dan" <d@.er.df> wrote in message
news:OPzU$SgwHHA.3444@.TK2MSFTNGP05.phx.gbl...
> Yes, it works like this:
> comd.CommandText = "insert into [mytable] (field1, field2) values(@.datbeg
> ,@.datend');" _
> & " select SCOPE_IDENTITY()"
> Thanks
>
>

problem with scope_identity

Hi,
after inserting a record in a table (sql server), i need the last value of
the primary key of that table, which increments automatically, before
inserting that value in another table.
I did like this:
comd.CommandText = "insert into [mytable] (field1, field2) values(@.datbe
g
,@.datend')"
comd.Parameters.Add("@.datbeg", SqlDbType.DateTime).Value = tda
comd.Parameters.Add("@.datend", SqlDbType.DateTime).Value = tda2
connection.Open()
comd.CommandText = "DECLARE @.orderid int"
comd.CommandText = "SET @.orderid = SCOPE_IDENTITY()"
comd.CommandText = "select @.orderid"
Dim x As Integer
x = Convert.ToInt32(comd.ExecuteScalar())
This giives an error:
"error: Must declare the scalar variable "@.orderid". "
Thanks
DanHi Dan,
Your code and approach have a number of problems. Please stick with me; I'm
not being critical... just pointing out the facts:
1. In every line where you have [comd.CommandText = ...] you are complet
ely
changing the value of comd.CommandText. That is, each line *overwrites* the
previous value of CommandText. So, when you finally get around to
comd.ExecuteScalar(), the value of comd.CommandText is simply "select
@.orderid".
2. The solution to the above problem (of overwriting the value of
CommandText in each successive line) is to concatenate the incremental
values, possibly via +=, and ensuring you add a blank space between each).
But you DON'T want to do that in your situation because of #3 below:
3. It appears that you are trying to create a stored procedure without
creating one (and instead putting all of the T-SQL in your CommandText.
There's no way that's going to work the way you are attempting.
What will work is to do the following:
1. Create a stored procedure that does the INSERT, followed immediately by
SET @.orderid = SCOPE_IDENTITY, and returning exactly one result set via
SELECT @.OrderID.
Then In your client code
2. Set CommandText = name of the stored procedure
3. Set ComandType = CommandType.StoredProcedure
4. Add to the Command.Parameters collection one SqlParameter object for each
of the parameters in the stored procedure.
5. Finally execute the stored procedure via the ExecuteScalar method (as you
were already trying to do).
The above assumes you have opened a connection etc..
-HTH
"Dan" <d@.er.df> wrote in message
news:OKUHPpawHHA.4568@.TK2MSFTNGP03.phx.gbl...
> Hi,
> after inserting a record in a table (sql server), i need the last value of
> the primary key of that table, which increments automatically, before
> inserting that value in another table.
> I did like this:
> comd.CommandText = "insert into [mytable] (field1, field2) values(@.dat
beg
> ,@.datend')"
> comd.Parameters.Add("@.datbeg", SqlDbType.DateTime).Value = tda
> comd.Parameters.Add("@.datend", SqlDbType.DateTime).Value = tda2
> connection.Open()
> comd.CommandText = "DECLARE @.orderid int"
> comd.CommandText = "SET @.orderid = SCOPE_IDENTITY()"
> comd.CommandText = "select @.orderid"
> Dim x As Integer
> x = Convert.ToInt32(comd.ExecuteScalar())
> This giives an error:
> "error: Must declare the scalar variable "@.orderid". "
> Thanks
> Dan
>|||Thanks, you're right of course with the concatenation.
But, instead of using a stored procedure (which i know is beter), would it
be posiible to do that in code-behind, more or less like this:
comd.CommandText = "DECLARE @.orderid int," _
& "SET @.orderid = SCOPE_IDENTITY()," _
& "select @.orderid"
Dim x As Integer
x = Convert.ToInt32(comd.ExecuteScalar())
because i get the error:
Incorrect syntax near the keyword 'SET'.
Incorrect syntax near ',
Thanks again
"Bob Johnson" <A@.B.COM> schreef in bericht
news:u$t07KbwHHA.4736@.TK2MSFTNGP04.phx.gbl...
> Hi Dan,
> Your code and approach have a number of problems. Please stick with me;
> I'm not being critical... just pointing out the facts:
> 1. In every line where you have [comd.CommandText = ...] you are
> completely changing the value of comd.CommandText. That is, each line
> *overwrites* the previous value of CommandText. So, when you finally get
> around to comd.ExecuteScalar(), the value of comd.CommandText is simply
> "select @.orderid".
> 2. The solution to the above problem (of overwriting the value of
> CommandText in each successive line) is to concatenate the incremental
> values, possibly via +=, and ensuring you add a blank space between each).
> But you DON'T want to do that in your situation because of #3 below:
> 3. It appears that you are trying to create a stored procedure without
> creating one (and instead putting all of the T-SQL in your CommandText.
> There's no way that's going to work the way you are attempting.
> What will work is to do the following:
> 1. Create a stored procedure that does the INSERT, followed immediately by
> SET @.orderid = SCOPE_IDENTITY, and returning exactly one result set via
> SELECT @.OrderID.
> Then In your client code
> 2. Set CommandText = name of the stored procedure
> 3. Set ComandType = CommandType.StoredProcedure
> 4. Add to the Command.Parameters collection one SqlParameter object for
> each of the parameters in the stored procedure.
> 5. Finally execute the stored procedure via the ExecuteScalar method (as
> you were already trying to do).
> The above assumes you have opened a connection etc..
> -HTH
>
>
> "Dan" <d@.er.df> wrote in message
> news:OKUHPpawHHA.4568@.TK2MSFTNGP03.phx.gbl...
>|||Dan wrote:
> Thanks, you're right of course with the concatenation.
> But, instead of using a stored procedure (which i know is beter), would it
> be posiible to do that in code-behind, more or less like this:
> comd.CommandText = "DECLARE @.orderid int," _
> & "SET @.orderid = SCOPE_IDENTITY()," _
> & "select @.orderid"
> Dim x As Integer
> x = Convert.ToInt32(comd.ExecuteScalar())
> because i get the error:
> Incorrect syntax near the keyword 'SET'.
> Incorrect syntax near ',
Use semicolon to separate the SQL statements, then it might work.
comd.CommandText = "DECLARE @.orderid int;" _
& "SET @.orderid = SCOPE_IDENTITY();" _
& "select @.orderid"
But why not simply:
comd.CommandText = "select SCOPE_IDENTITY()"
Gran Andersson
_____
http://www.guffa.com|||RE:
<< then it might work >>
Right - can you (op) please let us know if you get this to work? I'm
curious.|||"Gran Andersson" <guffa@.guffa.com> wrote in message
news:O4wM4jbwHHA.4300@.TK2MSFTNGP04.phx.gbl...
> Dan wrote:
> Use semicolon to separate the SQL statements, then it might work.
> comd.CommandText = "DECLARE @.orderid int;" _
> & "SET @.orderid = SCOPE_IDENTITY();" _
> & "select @.orderid"
> But why not simply:
> comd.CommandText = "select SCOPE_IDENTITY()"
>
A couple of other thoughts:
1. set comd.CommandType = CommandType.Text
2. In all those strings you are concatenating for the .CommandText value, be
sure to add white space where appropriate.
3. to test this, first get the script to work in query analyzer (SS2K) or
Management Studio (2005). Once it works there, then move it to your client
code.
-HTH|||Yes, it works like this:
comd.CommandText = "insert into [mytable] (field1, field2) values(@.datbe
g
,@.datend');" _
& " select SCOPE_IDENTITY()"
Thanks|||How are you populating @.datbeg and @.datend? That query won't work unless you
send parameters. Is that your actual query?
Just curious. Thanks!
"Dan" <d@.er.df> wrote in message
news:OPzU$SgwHHA.3444@.TK2MSFTNGP05.phx.gbl...
> Yes, it works like this:
> comd.CommandText = "insert into [mytable] (field1, field2) values(@.dat
beg
> ,@.datend');" _
> & " select SCOPE_IDENTITY()"
> Thanks
>
>

Problem with return value of stored procedure when using tableadapters

hello

Could you please help me with this problem?

I have a stored procedure like this:

ALTER PROCEDUREdbo.UniqueChannelName

(

@.UserNamenvarchar(50),

@.ChannelNamenvarchar(50)

)

AS

return5;

Then inside of my dataset, I added a new query(dataset1.QueriesTableAdapter) to handle above mentioned stored procedure. Properties window is showing that return type of this adapter is of type int32 as we expected to be.

now I want to use it inside of my code:

DataSet1TableAdapters.QueriesTableAdapter b =new DataSet1TableAdapters.QueriesTableAdapter();

int i;

i=Convert.ToInt32( b.UniqueChannelName("Ahmad","test"));

as you may guess, the return type of b.UniqueChannelName("Ahmad","test") is object and needs to be type-casted before assigning it's value to i; but even after explicit type casting, the value of i is always set to 0, not 5.

could you please show me the way?

many thanks in advance

There is a bug / undocumented feature / downright bad design in ADO.NET whereby a stored procedure with both output parameters and a dataset to return, will not populate the output parameter until all the dataset has been read.

Try reading the dataset to completion before reading the output parameter, otherwise you will have to split your stored procedure into an output parameter part and a dataset part.

|||

Thanks for your fast response.

I'm new to ado.net and couldn't understand what you mean by reading the dataset. Could you please refer me to an example?

thanks

|||

>I'm new to ado.net and couldn't understand what you mean by reading the dataset.

Try assigning the dataset to the control, databind it and then read the output parameter value.

|||

Thanks, Iwill try it tomorrow.

|||

My code is now like this:

DataSet1TableAdapters.QueriesTableAdapter b =new DataSet1TableAdapters.QueriesTableAdapter();

int i;

GridView2.DataSource = b.UniqueChannelName("Ahmad","test");

GridView2.DataBind();

i=Convert.ToInt32( b.UniqueChannelName("Ahmad","test"));

But the problem persists

|||

Please post your stored procedure.

|||ALTER PROCEDURE dbo.UniqueChannelName(@.UserName nvarchar(50),@.ChannelName nvarchar(50))ASdeclare @.Out intSET NOCOUNT ON;return 5;|||

ALTER PROCEDURE dbo.UniqueChannelName

(

@.UserName nvarchar(50),

@.ChannelName nvarchar(50)

)

AS

declare @.Out int

SET NOCOUNT ON;

//return 5;

SET @.OUT=5

// You can get it using AddOutParameter and getOutParameter method

Do not forget to mark as an answer on the post that helped you.

|||

You need to modify your stored procedure to
ALTER PROCEDURE dbo.UniqueChannelName
( @.UserName nvarchar(50),
@.ChannelName nvarchar(50),
@.Out INT OUTPUT -- To get output from a stored procedure parameter you must define it as such
)AS
SET NOCOUNT ON;
SET @.OUT=5

The calling code will need to change

|||

Thanks

Do you mean I must use Database classs and I can't use dataset for my purpose?Infact I prefred using datasets.

Plus

@.out was a local variable inside my code, can it send data out?

Plus

Later I want to change my stored procedure to a select statement like this:

select @.out=count(Autonumber) from ...

what should I do for returning the value?

again thanks for your incorporation

|||

Ok

nowUniqueChannelName method became like this:

UniqueChannelName(string UserName, string ChannelName, ref int? Out)

but unfortunately I don't know how to use the last parameter. could you please help?

thanks

|||

> Plus @.out was a local variable inside my code, can it send data out?

It has to become an output argument of the stored procedure. A local variable is purely local.

For small datasets selected by a SELECT A, B, C FROM FRED, they can be modified to SELECT @.out AS OUT, A, B, C FROM FRED

If the dataset is small the overhead is minimal, otherwise you need to formally define a Command object with all the arguments.

|||

Dear Allstar,

You gave too much of information to me. thanks alot.

I think if you give me a guide on using that reference variable, my questions will be finished.

Any how, thanks alot.

|||I am looking for an example in an old VS2003 project, but the search is taking longer than I anticipated.

Problem With Result Set in SQL Task

Hello,

I have a SQL Task configured to return a single row, and a single column value. The SQL Statement looks like this;

SELECT MAX(InvoiceDate) AS InvoiceDate
FROM dbo.DailySettlementData

The statement parses without a problem. In the 'Result Set' of the SQL Task I have the following;

Result Name; InvoiceDate, Variable Name; LatestTableDate

'LatestTableDate' is of type 'DateTime' (and I'm wondering now if this needs to be of type 'Object')

I need this MAX(InvoiceDate) value in a later step that checks this value against another date type variable.

I'm not getting the result I expect. Do I have the variable for the result set in the SQL Task set up correctly?

Thank you for your help!

cdun2

I assume that the variable name is "User::LatestTableDate", right?

Otherwise that looks fine. It doesn't need to be an object datatype. You can try a result name of "0" instead of "InvoiceDate" and see if that returns anything different.|||

Phil Brammer wrote:

You can try a result name of "0" instead of "InvoiceDate" and see if that returns anything different.

One of my variables needed to have 'EvaluateAsExpression' set to True. Now it works fine.

A couple of questions related to working with SSIS in Visual Studio;

I'm looking for some way to 'watch' my SSIS variables in the 'watch' window. I thought this would be available from the Debug or Windows menu in Visual Studio 2005, but I don't see it.

While I'm on the subject of Visual Studio, I've noticed that all of my packages related to a specific project will open when I try to debug just one package. Why does that happen? The packages are independent of each other.

Thank you again for your help.

cdun2

|||

cdun2 wrote:

While I'm on the subject of Visual Studio, I've noticed that all of my packages related to a specific project will open when I try to debug just one package. Why does that happen? The packages are independent of each other.

Close them before saving the project and closing it. It will open whatever you left open the previous time.|||

cdun2 wrote:

I'm looking for some way to 'watch' my SSIS variables in the 'watch' window. I thought this would be available from the Debug or Windows menu in Visual Studio 2005, but I don't see it.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=250573&SiteID=1

Problem with report.papersize when printing in crystal report

Hi, i have a problem with report.papersize when printing in crystal report.

at first i'm using crpaperfolio value for used with printer that has default folio setting..

but yesterday the printer was replaced by printer which don't have default folio setting..so when i print the result is not good (not full)...

i already change the report.papersize property value with crdefaultpapersize but it still the same (not good), but if i change the value to crPaperLegal then the result is good.

my question is how to make my report papersize to be dynamic and doesn't have to change the papersize property manually?? like my problem above.(folio -> legal).

thanks alot.please, any suggestion or information are needed..
thanks.

Problem with querystringparameter

Hi, I'm having problems with the querystring parameter in a SQLDataSource, I think the SelectCommand is not getting the value of the Querystringparameter, here is the code:

<asp:SqlDataSourceID="SqlDataSource1"

runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"

SelectCommand="SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE '%' + @.nombre + '%'">

<SelectParameters><asp:QueryStringParameterName="Nombre"QueryStringField="Nombre"/></SelectParameters></asp:SqlDataSource>

I've tried many things like...

"SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE @.nombre"

"SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE '%' & @.nombre & '%'"

"SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE '%' + @.nombre + '%'"

And nothing work, where's the problem?, I send the value of the SelectCommand of the DataSource and the @.name is not replaced by any value.

Use

"SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE @.nombre"

and set the value of @.nombre as

@.nombre = '%' + yourValue + '%'

|||The third option should work.

Problem with parenthesis in the default value of a column

I am having a problem with the format of default values in SQL Server 2005.
I
am using a legacy application that is not expecting the double parenthesis
around the default value that SQL Server adds. For instance, if
I set 0 (zero) as the default value for a column of type int as follows
ALTER TABLE Entity ADD CONSTRAINT [DF_Entity_Class] DEFAULT 0 FOR
Class)
then SQL Server will set the default value as ((0)). The problem is that the
legacy application is validating the default values and expecting just 0. I
own the database but have no control over the application. Therefore, I
cannot change the application to remove the parenthesis after reading the
value.
Is there any way to force SQL Server to store the default value without the
parenthesis or to return it without the parenthesis (note that the
application is reading the database tables directly).
Regards,
ArturHi
I don't think you can change the way it is stored but you could use the
REPLACE function to strip out the braces when you return it.
John
"artur" wrote:

> I am having a problem with the format of default values in SQL Server 2005
. I
> am using a legacy application that is not expecting the double parenthesis
> around the default value that SQL Server adds. For instance, if
> I set 0 (zero) as the default value for a column of type int as follows
> ALTER TABLE Entity ADD CONSTRAINT [DF_Entity_Class] DEFAULT 0 FOR
> Class)
> then SQL Server will set the default value as ((0)). The problem is that t
he
> legacy application is validating the default values and expecting just 0.
I
> own the database but have no control over the application. Therefore, I
> cannot change the application to remove the parenthesis after reading the
> value.
> Is there any way to force SQL Server to store the default value without th
e
> parenthesis or to return it without the parenthesis (note that the
> application is reading the database tables directly).
> Regards,
> Artur

Problem with parenthesis in the default value of a column

I am having a problem with the format of default values in SQL Server 2005. I
am using a legacy application that is not expecting the double parenthesis
around the default value that SQL Server adds. For instance, if
I set 0 (zero) as the default value for a column of type int as follows
ALTER TABLE Entity ADD CONSTRAINT [DF_Entity_Class] DEFAULT 0 FOR
Class)
then SQL Server will set the default value as ((0)). The problem is that the
legacy application is validating the default values and expecting just 0. I
own the database but have no control over the application. Therefore, I
cannot change the application to remove the parenthesis after reading the
value.
Is there any way to force SQL Server to store the default value without the
parenthesis or to return it without the parenthesis (note that the
application is reading the database tables directly).
Regards,
ArturHi
I don't think you can change the way it is stored but you could use the
REPLACE function to strip out the braces when you return it.
John
"artur" wrote:
> I am having a problem with the format of default values in SQL Server 2005. I
> am using a legacy application that is not expecting the double parenthesis
> around the default value that SQL Server adds. For instance, if
> I set 0 (zero) as the default value for a column of type int as follows
> ALTER TABLE Entity ADD CONSTRAINT [DF_Entity_Class] DEFAULT 0 FOR
> Class)
> then SQL Server will set the default value as ((0)). The problem is that the
> legacy application is validating the default values and expecting just 0. I
> own the database but have no control over the application. Therefore, I
> cannot change the application to remove the parenthesis after reading the
> value.
> Is there any way to force SQL Server to store the default value without the
> parenthesis or to return it without the parenthesis (note that the
> application is reading the database tables directly).
> Regards,
> Artur

problem with parameters in report url

I'm am trying to produce a report which has a parameter value in its url.
as I understand it
http://reports.server.local/Reports/Pages/Report.aspx?ItemPath=%2fdrift%2fjob_details&job_name=PAPERLESS
should produce the job_details report for the job named "PAPERLESS". but all
I get is the exact same page as I get if I use
http://reports.server.local/Reports/Pages/Report.aspx?ItemPath=%2fdrift%2fjob_details
I am using reporting servces 2005, can anyone see what I am doing wrong?
Thanks in advance
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200705/1update, I found out I should have user the following url
http://reports.server.local/Reportserver/Pages/ReportViewer.aspx?%2fdrift%2fjob_details&rs%3aCommand=Render&job_navn=PAPERLESS
tvb wrote:
>I'm am trying to produce a report which has a parameter value in its url.
>as I understand it
>http://reports.server.local/Reports/Pages/Report.aspx?ItemPath=%2fdrift%2fjob_details&job_name=PAPERLESS
>should produce the job_details report for the job named "PAPERLESS". but all
>I get is the exact same page as I get if I use
>http://reports.server.local/Reports/Pages/Report.aspx?ItemPath=%2fdrift%2fjob_details
>I am using reporting servces 2005, can anyone see what I am doing wrong?
>Thanks in advance
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200705/1

problem with output SP that takes Input

Hello,
I am trying to run/test an SP in Query Analyzer. The SP takes an input
param and outputs a value. How do I set this up in QA? Here is the SP
CREATE proc sp_Company_Workshop_Exists
@.RecordID int,
@.WorkshopExists bit output
as
if exists (
select *
from Workshop a
inner join Subscriber b
on ( a.CoID = b.CoID and a.SubscrID = b.SubscrID )
where b.RecordID = @.RecordID
)
set @.WorkshopExists = 1
else
set @.WorkshopExists = 0
return
I tried the following but getting error -- 15367 is my input param:
declare @.c int
declare @.WorkshopExists int
exec @.c = sp_Company_Workshop_Exists 15367 = @.WorkshopExists output
print @.WorkshopExists
Any suggestions appreciated
Thanks,
RichYou had a missing comma in the execution of the proc. The working code (conv
erted to pubs database)
below. A couple of comments:
Having sp_ in beginning of procedure name is considered bad practice.
I suggest you match the datatype of the out parameter to the one in the call
ing batch.
CREATE proc #sp_Company_Workshop_Exists
@.RecordID int,
@.WorkshopExists bit output
as
if exists (
select *
from authors)
set @.WorkshopExists = 1
else
set @.WorkshopExists = 0
return
GO
declare @.c int
declare @.WorkshopExists int
exec @.c = #sp_Company_Workshop_Exists 15367, @.WorkshopExists output
print @.WorkshopExists
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:ECECE8A6-31A8-49C0-AF2F-54F45D1918A3@.microsoft.com...
> Hello,
> I am trying to run/test an SP in Query Analyzer. The SP takes an input
> param and outputs a value. How do I set this up in QA? Here is the SP
> CREATE proc sp_Company_Workshop_Exists
> @.RecordID int,
> @.WorkshopExists bit output
> as
> if exists (
> select *
> from Workshop a
> inner join Subscriber b
> on ( a.CoID = b.CoID and a.SubscrID = b.SubscrID )
> where b.RecordID = @.RecordID
> )
> set @.WorkshopExists = 1
> else
> set @.WorkshopExists = 0
> return
> I tried the following but getting error -- 15367 is my input param:
> declare @.c int
> declare @.WorkshopExists int
> exec @.c = sp_Company_Workshop_Exists 15367 = @.WorkshopExists output
> print @.WorkshopExists
> Any suggestions appreciated
> Thanks,
> Rich|||OK. I changed the setup and now seems to work:
declare @.WorkshopExistsB bit
exec sp_Company_Workshop_Exists 15367, @.WorkshopExists = @.WorkshopExistsB
output
print @.WorkshopExistsB
"Rich" wrote:

> Hello,
> I am trying to run/test an SP in Query Analyzer. The SP takes an input
> param and outputs a value. How do I set this up in QA? Here is the SP
> CREATE proc sp_Company_Workshop_Exists
> @.RecordID int,
> @.WorkshopExists bit output
> as
> if exists (
> select *
> from Workshop a
> inner join Subscriber b
> on ( a.CoID = b.CoID and a.SubscrID = b.SubscrID )
> where b.RecordID = @.RecordID
> )
> set @.WorkshopExists = 1
> else
> set @.WorkshopExists = 0
> return
> I tried the following but getting error -- 15367 is my input param:
> declare @.c int
> declare @.WorkshopExists int
> exec @.c = sp_Company_Workshop_Exists 15367 = @.WorkshopExists output
> print @.WorkshopExists
> Any suggestions appreciated
> Thanks,
> Rich|||Yes, I am aware of the "Bad Practice". Not to pass the buck, but I am takin
g
over for a young man who has moved on to bigger and better things. So I wil
l
have to deal with his youthful exuberance, this being one of them. The kid
is very smart, just out of college. He just needs to refine a few things,
just like me :).
Anyway, thank you for your reply and example.
Rich
"Tibor Karaszi" wrote:

> You had a missing comma in the execution of the proc. The working code (co
nverted to pubs database)
> below. A couple of comments:
> Having sp_ in beginning of procedure name is considered bad practice.
> I suggest you match the datatype of the out parameter to the one in the ca
lling batch.
> CREATE proc #sp_Company_Workshop_Exists
> @.RecordID int,
> @.WorkshopExists bit output
> as
> if exists (
> select *
> from authors)
> set @.WorkshopExists = 1
> else
> set @.WorkshopExists = 0
> return
> GO
> declare @.c int
> declare @.WorkshopExists int
> exec @.c = #sp_Company_Workshop_Exists 15367, @.WorkshopExists output
> print @.WorkshopExists
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:ECECE8A6-31A8-49C0-AF2F-54F45D1918A3@.microsoft.com...
>

Problem with output parameter in SP

I am having problems returning the value of a parameter I have set in my stored procedure. Basically this is an authentication to check username and password for my login page. However, I am receiving the error:

Procedure 'DBAuthenticate' expects parameter '@.@.ID', which was not supplied.

This stored procedure is supposed to return a -1 if the username is not found, -2 if the password does not match, or the @.ID parameter, which is the user ID, if it is successful. How do i go about fixing this SP so that I am returning this output for @.ID?

CREATE PROCEDURE DBAuthenticate

(

@.UserName nVarChar (20),
@.Password nVarChar (20),
@.@.ID varchar(4) OUTPUT

)

AS
Declare @.ActualPassword nVarchar (20)

Select

@.@.ID = RegionID,

@.ActualPassword =regpassword

From dbo.Regions

Where Region = @.Username

If @.@.ID is not null
Begin
if @.Password =@.actualpassword

Select @.@.ID
Else

Select -2
End
Else

Select -1
GOMake sure that you specify OUTPUT in your EXECUTE call. If either the caller or the called routine fail to specify OUTPUT, the value isn't returned.

-PatP|||A couple of questions/things:

1. Why do you want to return something that your code already knows about? Return 1 instead.
2. Naming your parameter with @.@.xxx would result in server knowing it as @.xxx, not xxx as expected. And it doesn't make your parameter a "global" variable either.
3. Based on your logic @.ID variable will ALWAYS have whatever value was retrieved from Region table based on @.UserName or NULL, regardless of whether authentication was successful or not. You probably need to change the path of your authentication algorythm. How about setting it to NULL even if it exists but the password is wrong?