getting SQL instance name in extended stored proc

I have a extended stored procedure that I have been using
for a long time and it works fine. During its normal processing
it has to go back into the calling server/database and retrieve
additonal information. We are now working with multiple named
instances of MsSQL on the same box so I do not know the instance
of the server that has called me.
I already use "srv_rpcdb" to get the "database name" but how can
I discover the "instance name" of the server that has called me
so I can properly open a ADO/ODBC connection back into the same
server/database?See srv_pfieldex,
http://msdn.microsoft.com/library/d.../>
07_3320.asp
Inside an XP you can not retrieve the user database context, so what ever
you retrieve with srv_rpcdb it will not be correct and probably always
return master.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Dennis Passmore" <DennisP@.dpassmore.com> wrote in message
news:uTp9m7xIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> I have a extended stored procedure that I have been using
> for a long time and it works fine. During its normal processing
> it has to go back into the calling server/database and retrieve
> additonal information. We are now working with multiple named
> instances of MsSQL on the same box so I do not know the instance
> of the server that has called me.
> I already use "srv_rpcdb" to get the "database name" but how can
> I discover the "instance name" of the server that has called me
> so I can properly open a ADO/ODBC connection back into the same
> server/database?
>
>

getting SQL instance name in extended stored proc

I have a extended stored procedure that I have been using
for a long time and it works fine. During its normal processing
it has to go back into the calling server/database and retrieve
additonal information. We are now working with multiple named
instances of MsSQL on the same box so I do not know the instance
of the server that has called me.
I already use "srv_rpcdb" to get the "database name" but how can
I discover the "instance name" of the server that has called me
so I can properly open a ADO/ODBC connection back into the same
server/database?See srv_pfieldex,
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_ref_07_3320.asp
Inside an XP you can not retrieve the user database context, so what ever
you retrieve with srv_rpcdb it will not be correct and probably always
return master.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Dennis Passmore" <DennisP@.dpassmore.com> wrote in message
news:uTp9m7xIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> I have a extended stored procedure that I have been using
> for a long time and it works fine. During its normal processing
> it has to go back into the calling server/database and retrieve
> additonal information. We are now working with multiple named
> instances of MsSQL on the same box so I do not know the instance
> of the server that has called me.
> I already use "srv_rpcdb" to get the "database name" but how can
> I discover the "instance name" of the server that has called me
> so I can properly open a ADO/ODBC connection back into the same
> server/database?
>
>

getting SQL instance name in extended stored proc

I have a extended stored procedure that I have been using
for a long time and it works fine. During its normal processing
it has to go back into the calling server/database and retrieve
additonal information. We are now working with multiple named
instances of MsSQL on the same box so I do not know the instance
of the server that has called me.
I already use "srv_rpcdb" to get the "database name" but how can
I discover the "instance name" of the server that has called me
so I can properly open a ADO/ODBC connection back into the same
server/database?
See srv_pfieldex,
http://msdn.microsoft.com/library/de...ef_07_3320.asp
Inside an XP you can not retrieve the user database context, so what ever
you retrieve with srv_rpcdb it will not be correct and probably always
return master.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Dennis Passmore" <DennisP@.dpassmore.com> wrote in message
news:uTp9m7xIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> I have a extended stored procedure that I have been using
> for a long time and it works fine. During its normal processing
> it has to go back into the calling server/database and retrieve
> additonal information. We are now working with multiple named
> instances of MsSQL on the same box so I do not know the instance
> of the server that has called me.
> I already use "srv_rpcdb" to get the "database name" but how can
> I discover the "instance name" of the server that has called me
> so I can properly open a ADO/ODBC connection back into the same
> server/database?
>
>

Getting Sprocs that are not MS Shipped

Awhile back somebody showed me a neat little trick to filter out the stupid
dt tables and etc. I am wondering if there is a similar but different trick
for procs because I tried running this same query but changed U to P and it
did not filter the dt. and other useless sprocs.
@."SELECT name
FROM sysobjects
WHERE (xtype = 'U') AND (OBJECTPROPERTY(id, 'IsMSShipped') = 0)
ORDER BY category DESC, name";
dt_ procs are created when you open the diagrams pane in Enterprise Manager.
For some reason they are not marked as IsMsShipped = 1, so the only other
way to filter them out (other than deleting them, and resisting the urge to
open the Diagrams folder again) is to add a filter on the procedure name.
Also, you should use INFORMATION_SCHEMA views, not sysobjects.
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
AND ROUTINE_NAME NOT LIKE 'dt[_]%'
Of course this assumes you haven't created your own procs named dt_whatever.
(In fact, when I use INFORMATION_SCHEMA.ROUTINES instead of sysobjects, the
dt_ procs are not included, even without the additional WHERE clause. Maybe
this change alone will resolve the situation for you.)
http://www.aspfaq.com/
(Reverse address to reply.)
<recoil@.community.nospam> wrote in message
news:ulSRIWbHFHA.2276@.TK2MSFTNGP15.phx.gbl...
> Awhile back somebody showed me a neat little trick to filter out the
stupid
> dt tables and etc. I am wondering if there is a similar but different
trick
> for procs because I tried running this same query but changed U to P and
it
> did not filter the dt. and other useless sprocs.
>
> @."SELECT name
> FROM sysobjects
> WHERE (xtype = 'U') AND (OBJECTPROPERTY(id, 'IsMSShipped') =
0)
> ORDER BY category DESC, name";
>
>
|||Thanks. I will give that a shot when I get time. Are you aware of any
method to get the extra dt tables and dt sprocs "if" i wanted them. or
does the ISMSShipped work AS expected when extracting form
information_schema, because I have another project where I want to get
both I just want them to be under 2 different nodes. One that is for
System and one is for users - for sprocs and tables.
|||Thanks. I will give that a shot when I get time. Are you aware of any
method to get the extra dt tables and dt sprocs "if" i wanted them. or
does the ISMSShipped work AS expected when extracting form
information_schema, because I have another project where I want to get
both I just want them to be under 2 different nodes. One that is for
System and one is for users - for sprocs and tables.
sql

Getting Sprocs that are not MS Shipped

Awhile back somebody showed me a neat little trick to filter out the stupid
dt tables and etc. I am wondering if there is a similar but different trick
for procs because I tried running this same query but changed U to P and it
did not filter the dt. and other useless sprocs.
@."SELECT name
FROM sysobjects
WHERE (xtype = 'U') AND (OBJECTPROPERTY(id, 'IsMSShipped') = 0)
ORDER BY category DESC, name";dt_ procs are created when you open the diagrams pane in Enterprise Manager.
For some reason they are not marked as IsMsShipped = 1, so the only other
way to filter them out (other than deleting them, and resisting the urge to
open the Diagrams folder again) is to add a filter on the procedure name.
Also, you should use INFORMATION_SCHEMA views, not sysobjects.
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
AND ROUTINE_NAME NOT LIKE 'dt[_]%'
Of course this assumes you haven't created your own procs named dt_whatever.
(In fact, when I use INFORMATION_SCHEMA.ROUTINES instead of sysobjects, the
dt_ procs are not included, even without the additional WHERE clause. Maybe
this change alone will resolve the situation for you.)
--
http://www.aspfaq.com/
(Reverse address to reply.)
<recoil@.community.nospam> wrote in message
news:ulSRIWbHFHA.2276@.TK2MSFTNGP15.phx.gbl...
> Awhile back somebody showed me a neat little trick to filter out the
stupid
> dt tables and etc. I am wondering if there is a similar but different
trick
> for procs because I tried running this same query but changed U to P and
it
> did not filter the dt. and other useless sprocs.
>
> @."SELECT name
> FROM sysobjects
> WHERE (xtype = 'U') AND (OBJECTPROPERTY(id, 'IsMSShipped') =0)
> ORDER BY category DESC, name";
>
>|||Thanks. I will give that a shot when I get time. Are you aware of any
method to get the extra dt tables and dt sprocs "if" i wanted them. or
does the ISMSShipped work AS expected when extracting form
information_schema, because I have another project where I want to get
both I just want them to be under 2 different nodes. One that is for
System and one is for users - for sprocs and tables.|||Thanks. I will give that a shot when I get time. Are you aware of any
method to get the extra dt tables and dt sprocs "if" i wanted them. or
does the ISMSShipped work AS expected when extracting form
information_schema, because I have another project where I want to get
both I just want them to be under 2 different nodes. One that is for
System and one is for users - for sprocs and tables.

Getting Sprocs that are not MS Shipped

Awhile back somebody showed me a neat little trick to filter out the stupid
dt tables and etc. I am wondering if there is a similar but different trick
for procs because I tried running this same query but changed U to P and it
did not filter the dt. and other useless sprocs.
@."SELECT name
FROM sysobjects
WHERE (xtype = 'U') AND (OBJECTPROPERTY(id, 'IsMSShipped') = 0)
ORDER BY category DESC, name";dt_ procs are created when you open the diagrams pane in Enterprise Manager.
For some reason they are not marked as IsMsShipped = 1, so the only other
way to filter them out (other than deleting them, and resisting the urge to
open the Diagrams folder again) is to add a filter on the procedure name.
Also, you should use INFORMATION_SCHEMA views, not sysobjects.
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
AND ROUTINE_NAME NOT LIKE 'dt[_]%'
Of course this assumes you haven't created your own procs named dt_whatever.
(In fact, when I use INFORMATION_SCHEMA.ROUTINES instead of sysobjects, the
dt_ procs are not included, even without the additional WHERE clause. Maybe
this change alone will resolve the situation for you.)
http://www.aspfaq.com/
(Reverse address to reply.)
<recoil@.community.nospam> wrote in message
news:ulSRIWbHFHA.2276@.TK2MSFTNGP15.phx.gbl...
> Awhile back somebody showed me a neat little trick to filter out the
stupid
> dt tables and etc. I am wondering if there is a similar but different
trick
> for procs because I tried running this same query but changed U to P and
it
> did not filter the dt. and other useless sprocs.
>
> @."SELECT name
> FROM sysobjects
> WHERE (xtype = 'U') AND (OBJECTPROPERTY(id, 'IsMSShipped') =
0)
> ORDER BY category DESC, name";
>
>|||Thanks. I will give that a shot when I get time. Are you aware of any
method to get the extra dt tables and dt sprocs "if" i wanted them. or
does the ISMSShipped work AS expected when extracting form
information_schema, because I have another project where I want to get
both I just want them to be under 2 different nodes. One that is for
System and one is for users - for sprocs and tables.|||Thanks. I will give that a shot when I get time. Are you aware of any
method to get the extra dt tables and dt sprocs "if" i wanted them. or
does the ISMSShipped work AS expected when extracting form
information_schema, because I have another project where I want to get
both I just want them to be under 2 different nodes. One that is for
System and one is for users - for sprocs and tables.

getting only the first occurance of a sql select statement

I am making a sql query and its bring back hundreds of results but I only need the first one. I am aware that getting the first one will be faster as well. Is there an efficient way to do this?Assuming you are using SQL 2000 or earlier:SELECT TOP 1 *
FROM dbo.sysobjects-PatP|||Assuming you are using SQL 2000 or earlier:SELECT TOP 1 *
FROM dbo.sysobjects-PatP

Don't forget an ORDER BY clause or your results may be unpredictable.

Regards,

hmscott

getting list of users who have access to db

What is the procedure for getting a list of users that have rights to access
a database? I'd like to get a listing back of anyone who has any rights to a
specific db (sorry still learning the backend dbo type commands in sql
server)
also any good articles on security permission settings i could look at to
make sure I am starting to learn these correctly? thanks!Brian,
Do 'sp_helpuser' to get a list of users for the current database.
I also suggest you take a look at this article, especially the security
section.
http://msdn.microsoft.com/library/e..._sp_00_519s.asp
-oj
"Brian Henry" <nospam@.newsgroups.microsoft> wrote in message
news:%23G25XKBpFHA.3408@.tk2msftngp13.phx.gbl...
> What is the procedure for getting a list of users that have rights to
> access a database? I'd like to get a listing back of anyone who has any
> rights to a specific db (sorry still learning the backend dbo type
> commands in sql server)
> also any good articles on security permission settings i could look at to
> make sure I am starting to learn these correctly? thanks!
>

Getting larger drive array

We are planning to install more disk space and need to temporarily move the data and log files and move them back after the disk space has been added.

Is Detach/Attach the best way to handle this?

You have to detach the database and then copy the .bak files to the directory and then attach it.

Steve Russell, MCDBA

|||

Steve,

Thanks for the response. What will I need the .Bak file for? I thought I would just be working with the .mdf & .ldf files. In other words, I was planning to do a Detach, then move the .mdf and .ldf to another server, uninstall the current 30 GB drives then restall 200 GB drives and finally Attach the Db files...

Will this work or am I missing something?

David

|||Your absolutely correct, my bad; the .ldf and .mdf files.

Getting Job Info

Hi,
Is there a stored procedure which will return all jobs on a server along
with their current status (I am especially interested in getting back the
Last Run Status)? If not, does anybody have a query that would do this?
I need to run this on SQL Server 7 and 2000.
Thanks,
James
Check out sp_help_Job in BooksOnLine.
Andrew J. Kelly SQL MVP
"JW" <nospam@.hotmail.com> wrote in message
news:uwDnmtPNEHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there a stored procedure which will return all jobs on a server along
> with their current status (I am especially interested in getting back the
> Last Run Status)? If not, does anybody have a query that would do this?
> I need to run this on SQL Server 7 and 2000.
> Thanks,
> James
>

Getting Job Info

Hi,
Is there a stored procedure which will return all jobs on a server along
with their current status (I am especially interested in getting back the
Last Run Status)? If not, does anybody have a query that would do this?
I need to run this on SQL Server 7 and 2000.
Thanks,
JamesCheck out sp_help_Job in BooksOnLine.
Andrew J. Kelly SQL MVP
"JW" <nospam@.hotmail.com> wrote in message
news:uwDnmtPNEHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there a stored procedure which will return all jobs on a server along
> with their current status (I am especially interested in getting back the
> Last Run Status)? If not, does anybody have a query that would do this?
> I need to run this on SQL Server 7 and 2000.
> Thanks,
> James
>

Getting Job Info

Hi,
Is there a stored procedure which will return all jobs on a server along
with their current status (I am especially interested in getting back the
Last Run Status)? If not, does anybody have a query that would do this?
I need to run this on SQL Server 7 and 2000.
Thanks,
JamesCheck out sp_help_Job in BooksOnLine.
--
Andrew J. Kelly SQL MVP
"JW" <nospam@.hotmail.com> wrote in message
news:uwDnmtPNEHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there a stored procedure which will return all jobs on a server along
> with their current status (I am especially interested in getting back the
> Last Run Status)? If not, does anybody have a query that would do this?
> I need to run this on SQL Server 7 and 2000.
> Thanks,
> James
>

Getting Identity back

in my VB.NET program, when I do the following, how can I get the value of
the primary key back
which is an Identity column?
Thanks,
T
connPO.Open()
Dim strSQL As String
strSQL = "INSERT INTO Orders " & _
"(JobID, Description, Notes, Status)" & _
"VALUES (@.JobID, @.Description, @.Notes, @.Status)"
Dim mycommand As New SqlCommand(strSQL, connPO)
mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
Try
rowsAffected = mycommand.ExecuteNonQuery()
If rowsAffected = 0 Then
Return "Rows Updated were Zero - Update was not effective"
End If
Return ""
Catch db As SqlException
If db.Number <> 2627 Then '2627 means dup add
Return db.Number & " " & db.Message
End If
Catch ex As System.Exception
Return ex.Message
Finally
connPO.Close()
End Try
(I notice you also posted in the dotnet forum as I answered there also)
Tina,
Right before you Return "" enter the following code
'now get the identity back
strSQL = "Select @.@.IDENTITY as 'Identity'"
Dim GetIDCommand As New SqlCommand(strSQL, connPO)
Dim myReturn as integer = GetIDCommand.ExecuteScalar
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>
|||"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>
You need to call SCOPE_IDENTITY in the same batch that does the INSERT:
strSQL = "INSERT INTO Orders "
...
strSQL = strSQL + "; SELECT SCOPE_IDENTITY()"
The result set is the IDENTITY value. There are a couple of points to note.
Firstly, SCOPE_IDENTITY is SQL Server 2000 / 2005 only. In SQL Server 7
you'll have to use @.@.IDENTITY. Using @.@.IDENTITY means that the return value
will reflect any INSERT done in a trigger if one exists on your table.
That's generally not what is wanted, so in the case of SQL Server 7 on a
table with a trigger you'll have to use a different technique: Issue a
SELECT with a WHERE clause based on alternate key values from among those
you inserted. IDENTITY should never be the only key of a table so it should
always be possible to retrieve the value without using either SCOPE_IDENTITY
or @.@.IDENTITY. For single row inserts however, SCOPE_IDENTITY is more
efficient.
Secondly, the above is bad advice :-). Any INSERT should be done with a
parameterized stored proc rather than dynamically in client code unless you
have an exceptional reason to do otherwise. Since your code is parameterized
anyway I don't know why you wouldn't use a proc here.
I notice you posted independently to at least two other groups.
Multi-posting is very inconsiderate and devalues the newsgroup experience
for everyone. If you really must hit several different groups with your
question then it's better to cross-post - i.e. the same message copied to
multiple groups so that there is just a single thread to continue the
discussion in. Don't cross-post excessively, but 1 or 2 well-chosen groups
in a cross-post is generally acceptable whereas pretty everyone hates
multi-posting.
Hope this helps.
David Portas
SQL Server MVP
|||> I notice you posted independently to at least two other groups.
Sorry. I think you posted twice to the SAME group actually. It was still a
multi-post to this one though.
David Portas
SQL Server MVP
|||To top it off, I gave Tina the same answer as you YESTERDAY in the adonet
group. Go figure.
Greg
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>
|||David,
I posted to one other group - the adonet group. The groups behave
differently so I do that to try to get best answers. The SQL Server group
always replys and very quickly but they are not always ADO oriented - I
ususally get T_SQL type answers. The adonet group doesn't answer so fast or
so well but they are ado oriented.
I didn't know that was against the rules. Sorry.
T
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>

Getting Identity back

in my VB.NET program, when I do the following, how can I get the value of
the primary key back
which is an Identity column?
Thanks,
T
connPO.Open()
Dim strSQL As String
strSQL = "INSERT INTO Orders " & _
"(JobID, Description, Notes, Status)" & _
"VALUES (@.JobID, @.Description, @.Notes, @.Status)"
Dim mycommand As New SqlCommand(strSQL, connPO)
mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
Try
rowsAffected = mycommand.ExecuteNonQuery()
If rowsAffected = 0 Then
Return "Rows Updated were Zero - Update was not effective"
End If
Return ""
Catch db As SqlException
If db.Number <> 2627 Then '2627 means dup add
Return db.Number & " " & db.Message
End If
Catch ex As System.Exception
Return ex.Message
Finally
connPO.Close()
End Try(I notice you also posted in the dotnet forum as I answered there also)
Tina,
Right before you Return "" enter the following code
'now get the identity back
strSQL = "Select @.@.IDENTITY as 'Identity'"
Dim GetIDCommand As New SqlCommand(strSQL, connPO)
Dim myReturn as integer = GetIDCommand.ExecuteScalar
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>|||"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>
You need to call SCOPE_IDENTITY in the same batch that does the INSERT:
strSQL = "INSERT INTO Orders "
...
strSQL = strSQL + "; SELECT SCOPE_IDENTITY()"
The result set is the IDENTITY value. There are a couple of points to note.
Firstly, SCOPE_IDENTITY is SQL Server 2000 / 2005 only. In SQL Server 7
you'll have to use @.@.IDENTITY. Using @.@.IDENTITY means that the return value
will reflect any INSERT done in a trigger if one exists on your table.
That's generally not what is wanted, so in the case of SQL Server 7 on a
table with a trigger you'll have to use a different technique: Issue a
SELECT with a WHERE clause based on alternate key values from among those
you inserted. IDENTITY should never be the only key of a table so it should
always be possible to retrieve the value without using either SCOPE_IDENTITY
or @.@.IDENTITY. For single row inserts however, SCOPE_IDENTITY is more
efficient.
Secondly, the above is bad advice :-). Any INSERT should be done with a
parameterized stored proc rather than dynamically in client code unless you
have an exceptional reason to do otherwise. Since your code is parameterized
anyway I don't know why you wouldn't use a proc here.
I notice you posted independently to at least two other groups.
Multi-posting is very inconsiderate and devalues the newsgroup experience
for everyone. If you really must hit several different groups with your
question then it's better to cross-post - i.e. the same message copied to
multiple groups so that there is just a single thread to continue the
discussion in. Don't cross-post excessively, but 1 or 2 well-chosen groups
in a cross-post is generally acceptable whereas pretty everyone hates
multi-posting.
Hope this helps.
David Portas
SQL Server MVP
--|||> I notice you posted independently to at least two other groups.
Sorry. I think you posted twice to the SAME group actually. It was still a
multi-post to this one though.
David Portas
SQL Server MVP
--|||To top it off, I gave Tina the same answer as you YESTERDAY in the adonet
group. Go figure.
Greg
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>|||David,
I posted to one other group - the adonet group. The groups behave
differently so I do that to try to get best answers. The SQL Server group
always replys and very quickly but they are not always ADO oriented - I
ususally get T_SQL type answers. The adonet group doesn't answer so fast or
so well but they are ado oriented.
I didn't know that was against the rules. Sorry.
T
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>

Getting Identity back

in my VB.NET program, when I do the following, how can I get the value of
the primary key back
which is an Identity column?
Thanks,
T
connPO.Open()
Dim strSQL As String
strSQL = "INSERT INTO Orders " & _
"(JobID, Description, Notes, Status)" & _
"VALUES (@.JobID, @.Description, @.Notes, @.Status)"
Dim mycommand As New SqlCommand(strSQL, connPO)
mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
Try
rowsAffected = mycommand.ExecuteNonQuery()
If rowsAffected = 0 Then
Return "Rows Updated were Zero - Update was not effective"
End If
Return ""
Catch db As SqlException
If db.Number <> 2627 Then '2627 means dup add
Return db.Number & " " & db.Message
End If
Catch ex As System.Exception
Return ex.Message
Finally
connPO.Close()
End Try(I notice you also posted in the dotnet forum as I answered there also)
Tina,
Right before you Return "" enter the following code
'now get the identity back
strSQL = "Select @.@.IDENTITY as 'Identity'"
Dim GetIDCommand As New SqlCommand(strSQL, connPO)
Dim myReturn as integer = GetIDCommand.ExecuteScalar
--
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>|||"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>
You need to call SCOPE_IDENTITY in the same batch that does the INSERT:
strSQL = "INSERT INTO Orders "
...
strSQL = strSQL + "; SELECT SCOPE_IDENTITY()"
The result set is the IDENTITY value. There are a couple of points to note.
Firstly, SCOPE_IDENTITY is SQL Server 2000 / 2005 only. In SQL Server 7
you'll have to use @.@.IDENTITY. Using @.@.IDENTITY means that the return value
will reflect any INSERT done in a trigger if one exists on your table.
That's generally not what is wanted, so in the case of SQL Server 7 on a
table with a trigger you'll have to use a different technique: Issue a
SELECT with a WHERE clause based on alternate key values from among those
you inserted. IDENTITY should never be the only key of a table so it should
always be possible to retrieve the value without using either SCOPE_IDENTITY
or @.@.IDENTITY. For single row inserts however, SCOPE_IDENTITY is more
efficient.
Secondly, the above is bad advice :-). Any INSERT should be done with a
parameterized stored proc rather than dynamically in client code unless you
have an exceptional reason to do otherwise. Since your code is parameterized
anyway I don't know why you wouldn't use a proc here.
I notice you posted independently to at least two other groups.
Multi-posting is very inconsiderate and devalues the newsgroup experience
for everyone. If you really must hit several different groups with your
question then it's better to cross-post - i.e. the same message copied to
multiple groups so that there is just a single thread to continue the
discussion in. Don't cross-post excessively, but 1 or 2 well-chosen groups
in a cross-post is generally acceptable whereas pretty everyone hates
multi-posting.
Hope this helps.
--
David Portas
SQL Server MVP
--|||> I notice you posted independently to at least two other groups.
Sorry. I think you posted twice to the SAME group actually. It was still a
multi-post to this one though.
--
David Portas
SQL Server MVP
--|||To top it off, I gave Tina the same answer as you YESTERDAY in the adonet
group. Go figure.
Greg
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
>> I notice you posted independently to at least two other groups.
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>|||David,
I posted to one other group - the adonet group. The groups behave
differently so I do that to try to get best answers. The SQL Server group
always replys and very quickly but they are not always ADO oriented - I
ususally get T_SQL type answers. The adonet group doesn't answer so fast or
so well but they are ado oriented.
I didn't know that was against the rules. Sorry.
T
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
>> I notice you posted independently to at least two other groups.
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>