Getting a SQL Database Error...any ideas SQL experts?

I am trying to insert a value into a field in a database named ASPNETDB.MDF. The table name is "profiles_BasicProperties" and the field name is "UserID". I get an error when I attempt to do this. See the code I am using to try to do this below...and then the error that I get which is further down in this post. Note...both the code and the database are on my laptop. I can connect to the database just fine using Server Explorer in MS VS 2005. Thanks in advance for any help anybody can offer...

Here is the code I am using:

<%@. Page Language="VB" MasterPageFile="~/Master02.master" Title="Create Your Free Account" Debug="true"%>
<%@. Import Namespace="System.Data.SqlClient" %>
<%@. Import Namespace="System.Web.Configuration" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder_Main" Runat="Server">

<script runat="server">

Sub CreateUserWizard_CreatedUser(ByVal sender As Object, ByVal e As EventArgs)

Dim CWZ As CreateUserWizard
CWZ = CType(Me.LoginView1.FindControl("CreateUserWizard"), Wizard)

CreateUserProfile(CWZ.UserName)

Private Sub CreateUserProfile(ByVal UserName As String)

Dim conString As String = WebConfigurationManager.ConnectionStrings("Main").ConnectionString
Dim con As New SqlConnection(conString)
Dim cmd As New SqlCommand("INSERT profiles_BasicProperties (UserName) VALUES (@.UserID)", con)
cmd.Parameters.AddWithValue("@.UserID", UserName)
Using con
con.Open()
cmd.ExecuteNonQuery()
End Using

End Sub

</script>

...and here is the error and stack trace (the offending Line 49 is in bold):

Server Error in '/Site_Dev' Application.
------------------------

An attempt to attach an auto-named database for file ~\App_Data\ASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file ~\App_Data\ASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Source Error:

Line 47: cmd.Parameters.AddWithValue("@.UserID", UserName)
Line 48: Using con
Line 49: con.Open()
Line 50: cmd.ExecuteNonQuery()
Line 51: End Using

Source File: C:\Users\mdcragg\Documents\Website\Site_Dev\User_Create.aspx Line: 49

Stack Trace:

[SqlException (0x80131904): An attempt to attach an auto-named database for file ~\App_Data\ASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736211
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +237
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +374
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +192
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
ASP.user_create_aspx.CreateUserProfile(String UserName) in C:\Users\Matthew\Documents\Group 02 - Politicore\Site_Dev\User_Create.aspx:49
ASP.user_create_aspx.CreateUserWizard_CreatedUser(Object sender, EventArgs e) in C:\Users\Matthew\Documents\Group 02 - Politicore\Site_Dev\User_Create.aspx:30
System.Web.UI.WebControls.CreateUserWizard.OnCreatedUser(EventArgs e) +105
System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() +341
System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) +105
System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +453
System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) +149
System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +17
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.312; ASP.NET Version:2.0.50727.312

Looking around potential solutions seem to be:

1. Open SQL Server Management Studio and attach the .mdf file manually to the SQL Server. (Select the 'Databases' node, Right click and select 'Attach')

Now change your web.config and change the connection string to point to the pre attached database instead of auto attach.

2. SQLExpress creates a directory per user in "c:\Documents and Settings\[user name]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS" to store some information. Delete this directory and try again.

3. The windows account "ASPNET" must have write permission on the folder. If it is a Windows 2003 machine, provide write access to the account "NETWORK SERVICE" on the folder, instead of account ASPNET.

4. You may want to disable the "User Instance=true" and try.

5. Are you using beta versions of VWD, Visual Studio 2005 or SQL Server 2005 Express? Uninstall them andinstall the released versions.

6. User Instance=True will work only if your SQL Server 2005 Express is using Windows Authentication mode. It will fail if you are using mixed mode. (Select the computer name in the SQL Server Management Studio, right click and select 'Properties'. Select the node 'Security' to view/change the authentication mode)

7. Make sure your connection string is correct. It must be something like below:

"Data Source=.\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=YourUserId_DatabaseName"

When you useIntegrated Secuiryt=SSPI in the connection string, make sure you are using<identity impersonate="true" /> in the<system.web> section of the web.config and also make sure to specify a valid windows account for the "Anonymous User" access section in IIS.

Getting @@ROWCOUNT from BULK INSERT with BATCHSIZE set

Hello!
I have a stored procedure that takes a file name as a parameter and
imports the data from there into the appropriate table using the
correct format file. I would like to save the number of rows inserted
at the end of my stored procedure. Since the file name is variable I
use EXEC (@.cmd) where the @.cmd is a BULK INSERT command that I have
generated.
Since some of these files are extremely large I have set the BATCHSIZE
for BULK INSERT to 1,000,000. However, this causes @.@.ROWCOUNT to only
show the number of rows inserted in the last batch. Since the table
name is variable I don't have an easy way of getting the count(*) from
it.
Any suggestions?
Thanks!
-Tom.Aardvark (tom_hummel@.hotmail.com) writes:
> I have a stored procedure that takes a file name as a parameter and
> imports the data from there into the appropriate table using the
> correct format file. I would like to save the number of rows inserted
> at the end of my stored procedure. Since the file name is variable I
> use EXEC (@.cmd) where the @.cmd is a BULK INSERT command that I have
> generated.
> Since some of these files are extremely large I have set the BATCHSIZE
> for BULK INSERT to 1,000,000. However, this causes @.@.ROWCOUNT to only
> show the number of rows inserted in the last batch. Since the table
> name is variable I don't have an easy way of getting the count(*) from
> it.
> Any suggestions?
You could run SELKCT COUNT(*) on the table before and after, but that's
of course ont very appealing on a large table. (The fact that the
table is dynamic should not be a problem. You do know sp_executesql,
don't you? Else read
http://www.sommarskog.se/dynamic_sq...#sp_executesql.
The other option would be to have the client to catch those rows
affected messages for each batch committed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> You could run SELKCT COUNT(*) on the table before and after, but that's
> of course ont very appealing on a large table. (The fact that the
> table is dynamic should not be a problem. You do know sp_executesql,
> don't you? Else read
> http://www.sommarskog.se/dynamic_sq...#sp_executesql.
> The other option would be to have the client to catch those rows
> affected messages for each batch committed.
Thanks for the advice Erland. I had forgotten that sp_executesql
allowed for output parameters, so that should give me what I need using
SELECT COUNT(*). Not the most elegant way and it will suffer a
performance hit since some of the tables can be quite large, but it's a
process that runs for up to 12 hours once a month, so a couple minutes
to get a count from a table shouldn't be a problem.
Thanks!
-Tom.

Gettin data fromdifferent database

Hi

My query syntax is such

select 'INSERT INTO UserCourse(Start_Date, User_ID) values
(Course_Date + ''', ''' + rtrim(convert(varchar(20),AppUser.ID)) + ''');'
FROM DB1..EMPLOYEE_TRAINING, DB2..User
WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

I run this query in the Query Analyser on the database DB1 and get the following error

The column prefix 'User' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'DB1..User' does not match with a table name or alias name used in the query.

What I need is to get the ID and Date from differnt tables in different databases
Any clues or suggestions
NimishaTry changing:

FROM DB1..EMPLOYEE_TRAINING, DB2..User

WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

to

FROM [DB1].dbo.EMPLOYEE_TRAINING, DB2..User

WHERE [DB2].dbo.User.Employee_Number = [DB1].dbo.EMPLOYEE_TRAINING.EMPLOYEE_NO

HTH

Ken|||Hi,
Try this
In clause replace the DB2..User
with servername.databasename.userid.tablename
and in where clause simply use the column name.

e.g

select *
from a, ndokech1.echi.dbo.tx_echi c
where a.myid = c.myid

i think it will work..

Cheers
Gola munjal

Originally posted by Nimisha
Hi

My query syntax is such

select 'INSERT INTO UserCourse(Start_Date, User_ID) values
(Course_Date + ''', ''' + rtrim(convert(varchar(20),AppUser.ID)) + ''');'
FROM DB1..EMPLOYEE_TRAINING, DB2..User
WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

I run this query in the Query Analyser on the database DB1 and get the following error

The column prefix 'User' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'DB1..User' does not match with a table name or alias name used in the query.

What I need is to get the ID and Date from differnt tables in different databases
Any clues or suggestions
Nimisha|||Sorry i thought u asked for getting data from different servers.
i think the problem is with table name user, bcause user is a keyword in sql.
give user table name with in [] this.
select *
from fscms..[user] ,fscms_backup..[user]
where fscms..[user].Employee_Number = fscms_backup..[user].Employee_Number

i tried this ..its working ..

Cheers
gola

Originally posted by GA_KEN
Try changing:

FROM DB1..EMPLOYEE_TRAINING, DB2..User

WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

to

FROM [DB1].dbo.EMPLOYEE_TRAINING, DB2..User

WHERE [DB2].dbo.User.Employee_Number = [DB1].dbo.EMPLOYEE_TRAINING.EMPLOYEE_NO

HTH

Ken|||Thanx guys for all the input apprecaite it.
Nimisha

geting timeout error in application when inserting record

i currently have more tha 3 million of records in my table in sql 7. i am getting timeout error in my web application when i try to insert a record in that table
what could be rhe reason for this? how to avoid this type of problem?
Thanks in advanceis it a single record insert or an insert select? how many indices do you have on the table? have you run a trace and looked at the duration column for the steps in the process? Do you want to post your code?sql

GetErrorDescription with oledb destination fast load mode

I have an OLE-DB Command transformation that inserts a row. If the insert SQL command fails for some reason, I use the "Redirect Row" option to send the row to a script component. Inthere, I get the error description into a string variable in order to log the error into an error table.

For example, if a primary key violation arises, I would like the error description to be "The data value violates integrity constraints". I get it using the ComponentMetadata.GetErrorDescription. When I use the "table or view mode", I get the error description above without any problem. But If I use the "table or view fast load", the description is something like "No status available". But, If I use the error output to fail the component, in the OnError, I get the right error description. Is there a way to have both behaviour, I mean, to be able to redirect error rows to an output and have the cotrrect error description (like the one in OnError event handler) using fast load mode?

Thank you!

Ccote

Hi ccote,

I am having exactly the same problem. Did you have yours resolved?

|||You can't get an error description using "Fast Load" as that is a bulk load operation.|||Even if I don't use the fast load option, the error description is vague. It returns something like "data violate integrity constraints". If I let the component fail and don't redirect the row, it gives the name of the constraint violated. It there a way to capture this detail error message?|||

I do not believe this information is available within the data flow. In any event, I've never been able to get more information than what you have described getting, above. Still, this is not necessarily a dead end.

Generally what I will do is set up an "error table" for each table into which I load data with an SSIS package. The error table will have a similar schema as the "real" target table, with a few exceptions:

It has much more lax integrity constraints - there are no foreign keys, and all columns allow NULLs, for example, so the odds of an INSERT failing are greatly reduced.

It has two additional columns, ErrorCode and ErrorDescription, into which I load the values added by the OLD DB Destination component's error output.

GetErrorDescription with oledb destination fast load mode

I have an OLE-DB Command transformation that inserts a row. If the insert SQL command fails for some reason, I use the "Redirect Row" option to send the row to a script component. Inthere, I get the error description into a string variable in order to log the error into an error table.

For example, if a primary key violation arises, I would like the error description to be "The data value violates integrity constraints". I get it using the ComponentMetadata.GetErrorDescription. When I use the "table or view mode", I get the error description above without any problem. But If I use the "table or view fast load", the description is something like "No status available". But, If I use the error output to fail the component, in the OnError, I get the right error description. Is there a way to have both behaviour, I mean, to be able to redirect error rows to an output and have the cotrrect error description (like the one in OnError event handler) using fast load mode?

Thank you!

Ccote

Hi ccote,

I am having exactly the same problem. Did you have yours resolved?

|||You can't get an error description using "Fast Load" as that is a bulk load operation.|||Even if I don't use the fast load option, the error description is vague. It returns something like "data violate integrity constraints". If I let the component fail and don't redirect the row, it gives the name of the constraint violated. It there a way to capture this detail error message?|||

I do not believe this information is available within the data flow. In any event, I've never been able to get more information than what you have described getting, above. Still, this is not necessarily a dead end.

Generally what I will do is set up an "error table" for each table into which I load data with an SSIS package. The error table will have a similar schema as the "real" target table, with a few exceptions:

It has much more lax integrity constraints - there are no foreign keys, and all columns allow NULLs, for example, so the odds of an INSERT failing are greatly reduced. It has two additional columns, ErrorCode and ErrorDescription, into which I load the values added by the OLD DB Destination component's error output.

GETDATE() to insert into a datetime field

Hi there, may seem like a really silly question, but i am new to SQL.
I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
record behaviour to upload information to the SQL Server. one of the fields
is DateRegisterred which in Dreamweaver is a hiddenfield with the value set
to GETDATE() called datereg so on the upload behaviour Dreamweaver should
upload GETDATE() from datereg to thedatetimefield DateRegisterred on the SQL
server. However on trying this i get the following error message -
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
datetime from character string.
/driverjobs/candregistration.asp, line 161
Please any advise as how to fix this would be really really appreciated.
Many thanks
GarethHi
It looks like your field is being taken as a string and you have issues with
the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
instead of getdate() on it's own. If you want to truncate the time use
CONVERT(char(8),GETDATE(),112)
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
> Hi there, may seem like a really silly question, but i am new to SQL.
> I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
> record behaviour to upload information to the SQL Server. one of the
> fields
> is DateRegisterred which in Dreamweaver is a hiddenfield with the value
> set
> to GETDATE() called datereg so on the upload behaviour Dreamweaver should
> upload GETDATE() from datereg to thedatetimefield DateRegisterred on the
> SQL
> server. However on trying this i get the following error message -
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
> datetime from character string.
> /driverjobs/candregistration.asp, line 161
> Please any advise as how to fix this would be really really appreciated.
> Many thanks
> Gareth|||Thanks John, should i change it within the hidden field, or somewhere in the
code? i've tried changing it in the hidden field but still get the same erro
r
message?
thanks for your help
Gareth
"John Bell" wrote:

> Hi
> It looks like your field is being taken as a string and you have issues wi
th
> the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
> instead of getdate() on it's own. If you want to truncate the time use
> CONVERT(char(8),GETDATE(),112)
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
>
>|||Hi again,
I've included the line of code - Thanks again.
<td><input name="DateRegistered" type="hidden" id="DateRegistered"
value="CONVERT(char(8),GETDATE(),112)"></td>
"John Bell" wrote:

> Hi
> It looks like your field is being taken as a string and you have issues wi
th
> the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
> instead of getdate() on it's own. If you want to truncate the time use
> CONVERT(char(8),GETDATE(),112)
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
>
>|||Hi
You can't put SQL into your code like that, it should be in the query that
you call and then populate the field from this value (in the same way you
populate others!). If you can use the datetime that the insert is made,
then it can be missed out completely and the column defined as not nullable
with GETDATE() as the default.
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:83A6E095-9200-4AA2-93F6-A40C78335320@.microsoft.com...
> Hi again,
> I've included the line of code - Thanks again.
> <td><input name="DateRegistered" type="hidden" id="DateRegistered"
> value="CONVERT(char(8),GETDATE(),112)"></td>
> "John Bell" wrote:
>|||Ahh, I think i understand, the Date function GETDATE() needs to run
automatically on the SQL server not in the ASP code. Thank you for this, it
only leaves me with one problem.
On another page users are allowed to post information for a period of time
which they chose from a drop down list -
where the item label is forinstance 7 days & the value is
DATEADD(d,7,GETDATE())
They can chose from 7,14,21 or 28 days - their choice populates a field
called expirydate.
My theory apparently wont work now, any ideas as to how i can get around
this one?
Onced again thanks for your help
Gareth
"John Bell" wrote:

> Hi
> You can't put SQL into your code like that, it should be in the query that
> you call and then populate the field from this value (in the same way you
> populate others!). If you can use the datetime that the insert is made,
> then it can be missed out completely and the column defined as not nullabl
e
> with GETDATE() as the default.
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:83A6E095-9200-4AA2-93F6-A40C78335320@.microsoft.com...
>
>|||Why not have the default value of the date field equal to getdate() in SQL
Server? If you're doing an INSERT it's a new record, set it then.
Jon
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
> Hi there, may seem like a really silly question, but i am new to SQL.
> I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
> record behaviour to upload information to the SQL Server. one of the
> fields
> is DateRegisterred which in Dreamweaver is a hiddenfield with the value
> set
> to GETDATE() called datereg so on the upload behaviour Dreamweaver should
> upload GETDATE() from datereg to thedatetimefield DateRegisterred on the
> SQL
> server. However on trying this i get the following error message -
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
> datetime from character string.
> /driverjobs/candregistration.asp, line 161
> Please any advise as how to fix this would be really really appreciated.
> Many thanks
> Gareth|||Hi
This may help with the previous issue http://www.aspfaq.com/show.asp?id=2347
Instead of giving a offset, you may want to use a date picker see
http://www.aspfaq.com/show.asp?id=2309, if you do go with the original
option then if you are building up a string for your update statement you
can do something like (untested!):
<%
SQL = "UPDATE table SET NextDate = DATEADD(dd," &
Request.Form("DateOffset") & ",GETDATE())"
response.write sql
%>
If you are calling a stored procedure then you just need to pass the
offset value.
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:EC001645-9C1D-492D-B1A7-83E41668FC8C@.microsoft.com...
> Ahh, I think i understand, the Date function GETDATE() needs to run
> automatically on the SQL server not in the ASP code. Thank you for this,
> it
> only leaves me with one problem.
> On another page users are allowed to post information for a period of time
> which they chose from a drop down list -
> where the item label is forinstance 7 days & the value is
> DATEADD(d,7,GETDATE())
> They can chose from 7,14,21 or 28 days - their choice populates a field
> called expirydate.
> My theory apparently wont work now, any ideas as to how i can get around
> this one?
> Onced again thanks for your help
> Gareth
> "John Bell" wrote:
>|||Thanks John, once again seems very useful, Just a quick question would
something like
CONVERT(char(8), DATEADD(dd," &
Request.Form("expirydate") & ",GETDATE(),112)
work? inserted into the default value?
Thanks
"John Bell" wrote:

> Hi
> This may help with the previous issue http://www.aspfaq.com/show.asp?id=2...ow.asp?id=2309, if you do go with the original
> option then if you are building up a string for your update statement you
> can do something like (untested!):
> <%
> SQL = "UPDATE table SET NextDate = DATEADD(dd," &
> Request.Form("DateOffset") & ",GETDATE())"
> response.write sql
> %>
> If you are calling a stored procedure then you just need to pass the
> offset value.
> John
>
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:EC001645-9C1D-492D-B1A7-83E41668FC8C@.microsoft.com...
>
>|||Hi
If you mean the default for column, then you don't need dateadd e.g
CREATE TABLE MyDates ( id int, dateval datetime not null default
convert(char(8),getdate(),112))
INSERT INTO MyDates (id, dateval ) VALUES ( 1,GETDATE())
INSERT INTO MyDates (id) VALUES ( 2)
INSERT INTO MyDates (id, dateval) VALUES ( 3, '20051225' )
INSERT INTO MyDates (id, dateval) VALUES ( 4, '20051224 23:59:59' )
SELECT * FROM MyDates
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:14E69D08-5318-4B52-9A1E-8DD3A7FB0F5E@.microsoft.com...
> Thanks John, once again seems very useful, Just a quick question would
> something like
> CONVERT(char(8), DATEADD(dd," &
> Request.Form("expirydate") & ",GETDATE(),112)
> work? inserted into the default value?
> Thanks
> "John Bell" wrote:
>sql

GETDATE() as default value is out of sync

I have a table which has several date fields (SessionStart, TransStart,
TransEnd, SessionClose). The SessionStart field is populated on insert
with the default value GETDATE(). After this record is inserted, I
update the other date fields in order as the transactions arrive.
What's happening is that the other date fields (TransStart, TransEnd
and SessionClose) have an earlier date value than the SessionStart
date. Nothing ever touches the SessionStart field; it is populated only
by its default value when the record is inserted. How can this happen?
For further clarification, this only happened to 4 records out of 500K.
It occurred while a backup was running on the server (not of this
database, but of another SQL database). All stored procedures are run
on the same server and are set with GETDATE(), so there's no issue with
clock variances between different servers. The differences in the dates
range from 400 milliseconds to 12 seconds.
Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
this application that matters and I need to understand what's happening
here. Could this be an issue with caching?Hi
I assume you UPDATE them with GETDATE() Function
Have you checked if exists a trigger on the table?
One more question , do you update them from the client or by stored
procedure?
"ccarson" <ccarson@.tpayment.com> wrote in message
news:1111586800.620105.36390@.f14g2000cwb.googlegroups.com...
> I have a table which has several date fields (SessionStart, TransStart,
> TransEnd, SessionClose). The SessionStart field is populated on insert
> with the default value GETDATE(). After this record is inserted, I
> update the other date fields in order as the transactions arrive.
> What's happening is that the other date fields (TransStart, TransEnd
> and SessionClose) have an earlier date value than the SessionStart
> date. Nothing ever touches the SessionStart field; it is populated only
> by its default value when the record is inserted. How can this happen?
> For further clarification, this only happened to 4 records out of 500K.
> It occurred while a backup was running on the server (not of this
> database, but of another SQL database). All stored procedures are run
> on the same server and are set with GETDATE(), so there's no issue with
> clock variances between different servers. The differences in the dates
> range from 400 milliseconds to 12 seconds.
> Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
> this application that matters and I need to understand what's happening
> here. Could this be an issue with caching?
>|||> I have a table which has several date fields
What is a "date field"? Do you mean a SMALLDATETIME column, or a DATETIME
column? Are they all either one or the other, or is there a mix?
SMALLDATETIME rounds to the nearest minute, so could easily explain away any
discrepancies between other columns with finer granularity.
Can you provide a repro that exhibits this behavior? This includes table
structure, sample data, stored procedure(s) and the calls to those procs
that exhibit the behavior.
Neither the OS nor SQL Server will go back in time, except for daylight
savings time. My guess is that either your columns are a mixture of
SMALLDATETIME and DATETIME, or your stored procedure accepts date/time
values from your application.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Can you post the sp code?
AMB
"Cassandra Carson" wrote:
> All fields are datetime fields. They are updated by stored procedures
> (other than StartSession which is updated by the default value of
> GETDATE on insert) using GETDATE on the sql server. The dates are not
> passed from the client.
> There are no triggers on the table.
> Thanks for responding.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>

GetDate not working

Hi,
I have a very simple query as follows. When I run, it returns no records. I
know there are records that should be in th result. If I insert today's
date instead of getdate() I get all the records for today's date. Getdate()
works if I use > or < instead of =. Any idea why is this behavior? Or is
there another way of accomplishing this.
Select COURSE_NBR as ItemValue
FROM ED_COURSE_CL_1
Where CLASS_DATE = GETDATE()Did you look at SELECT GETDATE() ? It is "working" just fine. Does it look
like just a date? Notice how it has HH:MM:SS.mmm as well. How many rows do
you think match the exact point in time when you run the query? The way to
do this is to use a range query, as you have already discovered. >= {date}
AND < {date + 1} ...
A
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:C0202406-5C2E-481C-B605-A3206B776161@.microsoft.com...
> Hi,
> I have a very simple query as follows. When I run, it returns no records.
> I
> know there are records that should be in th result. If I insert today's
> date instead of getdate() I get all the records for today's date.
> Getdate()
> works if I use > or < instead of =. Any idea why is this behavior? Or is
> there another way of accomplishing this.
> Select COURSE_NBR as ItemValue
> FROM ED_COURSE_CL_1
> Where CLASS_DATE = GETDATE()
>|||It is because datetime include both a date and a time portion. See
http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.karaszi.com/SQLServer/info_datetime.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:C0202406-5C2E-481C-B605-A3206B776161@.microsoft.com...
> Hi,
> I have a very simple query as follows. When I run, it returns no records. I
> know there are records that should be in th result. If I insert today's
> date instead of getdate() I get all the records for today's date. Getdate()
> works if I use > or < instead of =. Any idea why is this behavior? Or is
> there another way of accomplishing this.
> Select COURSE_NBR as ItemValue
> FROM ED_COURSE_CL_1
> Where CLASS_DATE = GETDATE()
>|||Aaron,
Select getdate() is working good and it returns todays date. For today's
date in my query should return one record, which I can verify it by inserting
today's date instead of getdate(). How can I build a range query to only get
records with today's date?
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:
> Did you look at SELECT GETDATE() ? It is "working" just fine. Does it look
> like just a date? Notice how it has HH:MM:SS.mmm as well. How many rows do
> you think match the exact point in time when you run the query? The way to
> do this is to use a range query, as you have already discovered. >= {date}
> AND < {date + 1} ...
> A
>
> "Shan" <Shan@.discussions.microsoft.com> wrote in message
> news:C0202406-5C2E-481C-B605-A3206B776161@.microsoft.com...
> > Hi,
> > I have a very simple query as follows. When I run, it returns no records.
> > I
> > know there are records that should be in th result. If I insert today's
> > date instead of getdate() I get all the records for today's date.
> > Getdate()
> > works if I use > or < instead of =. Any idea why is this behavior? Or is
> > there another way of accomplishing this.
> >
> > Select COURSE_NBR as ItemValue
> > FROM ED_COURSE_CL_1
> > Where CLASS_DATE = GETDATE()
> >
> >
>
>|||> Select getdate() is working good and it returns todays date.
Correction : it returns today's date AND TIME.
> How can I build a range query to only get
> records with today's date?
DECLARE @.today SMALLDATETIME;
SET @.today = DATEDIFF(DAY, 0, GETDATE());
SELECT
...
WHERE DateColumn >= @.today
AND DateColumn < (@.today + 1);|||Thanks Aaron it's working great.
Cheers!!!
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Select getdate() is working good and it returns todays date.
> Correction : it returns today's date AND TIME.
> > How can I build a range query to only get
> > records with today's date?
> DECLARE @.today SMALLDATETIME;
> SET @.today = DATEDIFF(DAY, 0, GETDATE());
> SELECT
> ...
> WHERE DateColumn >= @.today
> AND DateColumn < (@.today + 1);
>
>
>

Get value of parameters passed in stored procedure in a trigger

In updating my tables (insert/update), i use stored procedures.
Some of the values passed as parameters to the stored procedures
are only necessary for audit trail only and not for updating the tables.
How can i get hold of these parameter values while inside a trigger?Put the parameters in a permanent table or a local temp table.
David Portas
SQL Server MVP
--
"manK" <manK@.discussions.microsoft.com> wrote in message
news:EA0833BF-A070-4720-9E50-9C80EAE45FF9@.microsoft.com...
> In updating my tables (insert/update), i use stored procedures.
> Some of the values passed as parameters to the stored procedures
> are only necessary for audit trail only and not for updating the tables.
> How can i get hold of these parameter values while inside a trigger?
>

Get Value of IDENTITY

Hi,
I need get value of IDENTITY column after a insert (of the inserted item),
hava way to do this automatic, or same function that do this?
ThanksCheck out SCOPE_IDENTITY() in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23vrhmlOxFHA.3864@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need get value of IDENTITY column after a insert (of the inserted item),
> hava way to do this automatic, or same function that do this?
> Thanks
>|||3 ways
@.@.IDENTITY
IDENT_CURRENT
SCOPE_IDENTITY()
Read BOL
Rakesh
"ReTF" wrote:

> Hi,
> I need get value of IDENTITY column after a insert (of the inserted item),
> hava way to do this automatic, or same function that do this?
> Thanks
>
>|||@.@.Identity global variable should hold the value of the last generate
during an insert.
Martin
ReTF wrote:
> Hi,
> I need get value of IDENTITY column after a insert (of the inserted item),
> hava way to do this automatic, or same function that do this?
> Thanks
>|||You should use SCOPE_IDENTITY() because it is possible for a trigger to also
insert a row and generate an identity value. @.@.IDENTITY returns the last
IDENTITY value generated. IDENT_CURRENT returns the last generated IDENTITY
value for a table, but it's possible in a concurrent environment for
IDENT_CURRENT to change between the time that a row is inserted and the time
that IDENT_CURRENT is called. The best solution, therefore, is to use
SCOPE_IDENTITY() because it returns the last generated IDENTITY value within
the current scope, thus ignoring any IDENTITY values generated within
triggers.
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23vrhmlOxFHA.3864@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need get value of IDENTITY column after a insert (of the inserted item),
> hava way to do this automatic, or same function that do this?
> Thanks
>

get the number of days it has been since a record was inserted

Hi

when inserting records into a table one of the fields is a date field. I am using the GETDATE() function to insert the date as the record is being inserted.

when i retrieve an entire record from the table i want to be able to select this date, but also to get the number of days it has been since that record was inserted.
eg: 3 days

if the record was inserted less than one day ago (<24 hrs ago) i would like it to return the number of hours.
e.g. 22 hrs

i dont want hours to be displayed if the days is >= 1.

please can anyone guide me with this?

thanks!

use the query like this

Declare @.MyVarasDateTime

Set @.Myvar='22/05/2007'

Select'satya', MyTime=

CASE

WHENDATEDIFF(hh,@.Myvar,GetDate())> 23THENConvert(varchar(10),DATEDIFF(d,@.Myvar,GetDate()))+' days'

ELSE

Convert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate()))+' hours'

END

Use the appropriate fields according to your database and tables

|||

Thanks Satya, this was really useful. Can you help me modify this so that it returns 1 day and 1 hour instead of 1 days and 1 hours

Appreciate the help!

|||

Sure change the code where its + "days" or + "hours"

1Declare @.MyVaras DateTime23Set @.Myvar='22/05/2007'45Select'satya', MyTime=67CASE8WHENDATEDIFF(hh,@.Myvar,GetDate()) > 23THENConvert(varchar(10),DATEDIFF(d,@.Myvar,GetDate())) +' day'910ELSE1112Convert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate())) +' hour'1314END1516

|||

Sorry, i dont think i explained what i meant properly...

I need it to say 'days' and 'hours' all the time but the only exceptions are when days = 1 and when hours = 1...in them cases it should say 1 day and 1hour.

so as an example it could out the following :

11 days

21 days

1 day

...and

22 hours

6 hours

1 hour.

Thanks again!

|||

Declare @.MyVaras DateTime Set @.Myvar='05/22/2007'Select'satya', MyTime=CASEWHENDATEDIFF(hh,@.Myvar,GetDate()) < 2THENConvert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate())) +' hour'WHENDATEDIFF(hh,@.Myvar,GetDate()) < 23THENConvert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate())) +' hours'WHENDATEDIFF(d,@.Myvar,GetDate()) < 2THENConvert(varchar(10),DATEDIFF(d,@.Myvar,GetDate())) +' day'WHENDATEDIFF(d,@.Myvar,GetDate()) > 1THENConvert(varchar(10),DATEDIFF(dd,@.Myvar,GetDate())) +' days'END

|||

Great, worked perfectly :)

Thanks.

|||

You are welcome... Answer it if solved

Get the field value after INSERT

Hi,

Problem:
I need to get the value of auto-incremented field from just inserted
record

In Oracle this is INSERT .. RETURNING command.
In SQL Server there are @.@.IDENTITY, IDENT_CURRENT, SCOPE_IDENTITY

- @.@.IDENTITY returns the value from the very LAST insert on any table
involving in the insert process ( including triggers ) ,
so this value may have nothing to do with my table

- IDENT_CURRENT returns the last identity value generated for a
specific table in any session and any scope,
so this value may come not from my session

- SCOPE_IDENTITY returns the last identity value generated for any
table in the current session and the current scope , but from the very
LAST insert command ( that may be some INSERT in the audit tables)
so it may have nothing to do with my table

Question :
- Is there any trusted way I can get the value of auto-incremented
field
in my table and in the scope of my session?

Thanks, EugeneWhy doesn't SCOPE_IDENTITY() meet your requirements? Retrieve it
immediately after the INSERT in the current scope. If you also insert
to audit tables then you just need to retrieve SCOPE_IDENTITY() before
that insert rather than after. You can assign the value to a variable
and then return that variable as an output parameter from a stored
procedure if you need to.

--
David Portas
SQL Server MVP
--|||Eugene (ygorelik20@.hotmail.com) writes:
> - @.@.IDENTITY returns the value from the very LAST insert on any table
> involving in the insert process ( including triggers ) ,
> so this value may have nothing to do with my table

Not any table, only inserts to table that has an IDENTITY column matters.
Which may be problematic rnough.

> - IDENT_CURRENT returns the last identity value generated for a
> specific table in any session and any scope,
> so this value may come not from my session

Yes.

> - SCOPE_IDENTITY returns the last identity value generated for any
> table in the current session and the current scope , but from the very
> LAST insert command ( that may be some INSERT in the audit tables)
> so it may have nothing to do with my table

As David said, retrieve the value directly after the INSERT you are
interested in.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

GET STORED PROCEDURE RESULT SET TO TEMP TABLE

I wanted to insert stored procedure result into my temp table.
I tried 'SELECT * INTO FROM EXEC MYPROC @.MYPARAM = 1', but it does not work.
any information is great appreciated,Hi
You can try this way
INSERT INTO #tempTable EXEC MYPROC @.MYPARAM = 1
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Souris" wrote:

> I wanted to insert stored procedure result into my temp table.
> I tried 'SELECT * INTO FROM EXEC MYPROC @.MYPARAM = 1', but it does not wor
k.
> any information is great appreciated,
>

GET SCOPE_IDENTITY ON INSERCOMMAND

Hello,

I need to get the scope_identity value after the insert, but I couldn't see how when using the InsertCommand of SqlDataSource. I think I can try using ADO commands, doing it 'mannually', but how?

If I execute the insertcommand and after select, I don't have the value, it only exists 'when inserting'. So, I need to keep that value. It's possible to do it with a session on sql or how?

I'm really newba.

Thanks

Search the forums, this has been discussed a half dozen times already.|||

Motley wrote:

Search the forums, this has been discussed a half dozen times already.

I saw, but I need something like that:

sqlComm.CommandText ="INSERT INTO S_CUSTOMER(C_NAME, C_RECDEL) VALUES ('" & name.Text &"',0); DECLARE @.C_Customer_Id VARCHAR(50); SET @.C_Customer_Id = SCOPE_IDENTITY;"

I need to get the scope_identity after the insert. If I execute a select command after, I don't get anything. I want to know if I can get the @.C_Customer_Id, declarated on the sql command.

|||

JPope

I had your problem and realized that the only way to get the value back was to do it the "old fashioned" way! I actually use @.@.Indentity which I know is more prone to problems than scope_identity but below is some code that I have used many times and found it works fine for me.

By the way, I am not the world's best programmer - so you may be able to refine some of this down to make it a bit more glamorous!

'Set up the objects and variable

Dim connAsNew SqlConnection()

Dim commAsNew SqlCommand()

Dim adpAsNew SqlDataAdapter()

Dim datasetAsNew DataSet

Dim recordidentityAs String

'Create our INSERT command - do NOT put the identity line in here -

comm.CommandText ="INSERT INTO [Table] ([Field1], [Field2], etc...) VALUES (Some values)"

'set the connection up and open it - use the connection name from web.config - in this case I have called it MyConnection, subsitute yours in its place

conn.ConnectionString = ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString

conn.Open()

'set the insert command up

adp.InsertCommand =New SqlCommand(comm.CommandText, conn)

'execute the insert command

adp.InsertCommand.ExecuteNonQuery()

'now that we have completed the insert command but NOT closed the connection we can set up the select command - in your case it will be SELECT scope_identity as indent

comm.CommandText ="SELECT @.@.Identity as Ident"

adp.SelectCommand =New SqlCommand(comm.CommandText, conn)

adp.Fill(dataset,"Ident")

'Create a datatable to get the data from the first table in the dataset - referred to as "0"

Dim IdentityTableAs DataTable = dataset.Tables(0)

'create a reader for use with the loading in of data

Dim readerAs DataTableReader = dataset.CreateDataReader

'load in data

IdentityTable.Load(reader)

'get a row that we can use - once again the first row referred to as "0"

Dim rowAs DataRow

row = IdentityTable.Rows.Item(0)

'get the inserted record id back as a string so we can use it in another command if required

RecordIdentity = row(0).ToString()

Hope this works - let me know!

Stuart

Get result from EXEC()

I currently do this:
INSERT INTO #tbl2 EXEC(@.sql)
IF @.@.ROWCOUNT = 1
INSERT INTO @.tbl3 SELECT userID FROM #tbl2
DELETE FROM #tbl2
Is there any other way to recieve the result from column userid from the @.sql-query?Hi,

try to use sp_executesql (see BOL):

sp_executesql [@.stmt =] stmt
[
{, [@.params =] N'@.parameter_name data_type [,...n]' }
{, [@.param1 =] 'value1' [,...n] }
]

Markus|||How do you mean?|||Hi,

i don't know what you really want to query, but here an (quick and dirty) example:

-- testing enviroment
create table usernames( userid int, username varchar(100) )
insert into usernames (userid,username) values ( 123, 'moby' )
insert into usernames (userid,username) values ( 986, 'lars' )
-- select * from usernames
--
-- vars
declare @.Stmt nvarchar(200), @.UserID int, @.UserName varchar(100)
-- SQL-Stattement to get result from
select @.Stmt='select @.P1= userid from usernames where username=@.P2'
-- "input" parameter
select @.Username='moby'
-- query result
exec sp_executesql @.Stmt, N'@.P1 int output, @.P2 varchar(100)', @.P1=@.UserID output, @.P2=@.Username
-- show result
print @.Username
print @.UserID

-- get rid of testdata
drop table usernames|||Thank you!

Get RecordNumber with output

Hi everyone,
How can get the record number as column with my query output.
I dont want to insert the values in #temp table with IDENTITY function.
Any other trick...
RiyazYou don't state which version of SQL Server you are using. If you are using SQL Server 2005 you can use the new ROW_NUMBER() (http://msdn2.microsoft.com/en-us/library/ms186734.aspx) function.|||You don't state which version of SQL Server you are using. If you are using SQL Server 2005 you can use the new ROW_NUMBER() (http://msdn2.microsoft.com/en-us/library/ms186734.aspx) function.

Sorry for that

I am using SQL Server 2000

Get one row from detail/child table

Hi,

-- ddl
/*
create table #tmp (col1 int);
insert into #tmp values(1);
insert into #tmp values(2);
insert into #tmp values(3);

create table #tmpChild (col1 int, fkCol int, Num int);
insert into #tmpChild values(1,1,3);
insert into #tmpChild values(2,1,2);
insert into #tmpChild values(3,2,1);
*/

-- get parent and child data (outer join)
select *
from #tmp t Left Outer JOIN #tmpChild tC
ON t.col1 = tC.fkCol

-- resultset
/*
/col1 col1 fkCol Num
---- ---- ---- ----
1 1 1 3
1 2 1 2
2 3 2 1
3 NULL NULL NULL

(4 row(s) affected)
*/

-- desired resultset
/*
/col1 col1 fkCol Num
---- ---- ---- ----
1 1 1 3
-- eleminate next row, want only Max(Num) row from the child tbl with
same FK (parentID)
-- 1 2 1 2
2 3 2 1
3 NULL NULL NULL
*/

In other words, desired results is as follows:
/*
/col1 col1 fkCol Num
---- ---- ---- ----
1 1 1 3
2 3 2 1
3 NULL NULL NULL
*/

How to accomplish this task? ENV: MS SQL Server 2000

TIAAdd the following to the end of your query:

...
AND tC.Num = ( SELECT MAX( t2.Num )
FROM #tmpChild t2
WHERE t2.fkCol = tC.fkCol ) ;

--
Anith

Get next unique ID from a table before insert @@identity / Sequence

How do I get the next int value for a column before I do an insert in
MY SQL Server 2000? I'm currently using Oracle sequence and doing
something like:

select seq.nextval from dual;

Then I do my insert into 3 different table all using the same uniqueID.

I can't use the @.@.identity function because my application uses a
connection pool and it's not garanteed that a connection won't be used
by another request so under a lot of load there could be major problems
and this doens't work:

insert into <table>;
select @.@.identity;

This doesn't work because the select @.@.identity might give me the value
of an insert from someone else's request.

Thanks,

BrentOn 16 Mar 2005 14:58:25 -0800, brent.ryan@.gmail.com wrote:

>How do I get the next int value for a column before I do an insert in
>MY SQL Server 2000? I'm currently using Oracle sequence and doing
>something like:
>select seq.nextval from dual;
>Then I do my insert into 3 different table all using the same uniqueID.
>I can't use the @.@.identity function because my application uses a
>connection pool and it's not garanteed that a connection won't be used
>by another request so under a lot of load there could be major problems
>and this doens't work:
>insert into <table>;
>select @.@.identity;
>This doesn't work because the select @.@.identity might give me the value
>of an insert from someone else's request.
>Thanks,
>Brent

Hi Brent,

Create a stored procedure that starts a transaction, inserts into the
first table, retrieves the identity value used (with SCOPE_IDENTITY, the
recommended method in SQL Server 2000), uses it to insert data into the
other two table, then commits the transaction (or rolls it back if
anything went wrong).

Calling the server three times for three inserts is not only incurring
the overhead of more roundtrips then necessary, you also run the risk of
getting corrupted data: if one insert fails and the others succeed,
you'll have incomplete data in your database. Always include related
modifications in a transaction. And if each call to the database can use
a different connection, then the complete operation, from start to end
of transaction, needs to be done in one call, as transactions are tied
to the connection.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(brent.ryan@.gmail.com) writes:
> insert into <table>;
> select @.@.identity;
> This doesn't work because the select @.@.identity might give me the value
> of an insert from someone else's request.

No, @.@.identity is local to the connection, so it cannot be someone
else's value. Well, if you submit to queries and close your connection
in between, it won't work, but that would be poor practice anyway.

Hugo's suggestion of using a stored procedure is an excellent idea.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Thu, 17 Mar 2005 22:57:45 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

> (brent.ryan@.gmail.com) writes:
>> insert into <table>;
>> select @.@.identity;
>>
>> This doesn't work because the select @.@.identity might give me the value
>> of an insert from someone else's request.
>No, @.@.identity is local to the connection, so it cannot be someone
>else's value. Well, if you submit to queries and close your connection
>in between, it won't work, but that would be poor practice anyway.
>Hugo's suggestion of using a stored procedure is an excellent idea.

Excuse me for butting in here, Erland, but there is one 'little'
problem that I have found with @.@.IDENTITY that I can't see referred to
anywhere, and that anyone relying on it should know about, and that is
that @.@.IDENTITY can return unexpected values in certain circumstances.

In the supplied example:

insert into <table>
select @.@.identity

BEAWRE!
If there is a trigger fired during the insert on <table>, and the
trigger performs an insert itself, then @.@.IDENTITY will return the ID
from the Trigger's insert, not the <table> insert.

This caused me many to lose much more hair than I can afford!

It behaves this way in SQL Server 7, and 2000.

Here is a script to create a test data base:
(Make a new blank database, I called it "Test")

=============================
/****** Object: Table [dbo].[MainTable] Script Date: 18/03/2005
3:10:38 PM ******/
CREATE TABLE [dbo].[MainTable] (
[MainTableId] [int] IDENTITY (1, 1) NOT NULL ,
[LongName] [nvarchar] (255) NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[TriggerTable] Script Date: 18/03/2005
3:10:39 PM ******/
CREATE TABLE [dbo].[TriggerTable] (
[TriggerTableId] [int] IDENTITY (666, 1) NOT NULL ,
[TriggerRowLongName] [nvarchar] (255) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TriggerTable] WITH NOCHECK ADD
CONSTRAINT [PK_TriggerTable] PRIMARY KEY CLUSTERED
(
[TriggerTableId]
) ON [PRIMARY]
GO
/****** Object: Stored Procedure dbo.Test_sp Script Date:
18/03/2005 3:10:39 PM ******/
CREATE PROCEDURE dbo.Test_sp
AS
INSERT INTO MainTable (LongName) VALUES ('TestLongName')
SELECT @.@.IDENTITY
GO
/****** Object: Trigger dbo.MainTable_Trigger1 Script Date:
18/03/2005 3:10:39 PM ******/
CREATE TRIGGER MainTable_Trigger1
ON dbo.MainTable
FOR INSERT,UPDATE,DELETE
AS
INSERT INTO TriggerTable (TriggerRowLongName) VALUES ('Stuff')
GO
=============================

Then, if one executes [Test_sp] in Query Analyser,

EXEC Test_sp

the returned @.@.IDENTITY is not 1, as you would expect, (this is ID of
the new MainTable row), but 666, which is the ID of the row inserted
via the trigger!
(I seeded this table's identity to begin at 666, in order to show up
clearly)

I would be interested if you were aware of this tiny problemette.|||Michael Gray (fleetg@.newsguy.spam.com) writes:
> Excuse me for butting in here, Erland, but there is one 'little'
> problem that I have found with @.@.IDENTITY that I can't see referred to
> anywhere, and that anyone relying on it should know about, and that is
> that @.@.IDENTITY can return unexpected values in certain circumstances.
> In the supplied example:
> insert into <table>
> select @.@.identity
> BEAWRE!
> If there is a trigger fired during the insert on <table>, and the
> trigger performs an insert itself, then @.@.IDENTITY will return the ID
> from the Trigger's insert, not the <table> insert.

Yes, this is a correct observation. For this reason, you should use
scope_identity() instead. This function was introduced in SQL 2000.

scope_identity() returns the most recently generated IDENTITY in the
current scope, that is a trigger, stored procedure, block of dynamic
SQL etc.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp