Plase HELP ! SQL ERROR =>> An I/O error occurred while receiving the TDS prelogin response

i get this error when i try to connect to sql server 2005 developer edition with the latest JDBC.

sql string =>> jdbc:sqlserver://localhost:1434;databaseName=c1 Elvis elvis
SQL STATE: 08S01
ERROR CODE: 0
MESSAGE: An I/O error occurred while receiving the TDS prelogin response.

And the stacktrace is:

com.microsoft.sqlserver.jdbc.SQLServerException: An I/O error occurred while receiving the TDS prelogin response.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.Prelogin(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.<init>(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:171)

and is called :connection=DriverManager.getConnection(con, user, password);

Please tell me what is wrong ? Because in other part of the code it works fine !

What is this "I/O error occurred while receiving the TDS prelogin response" ?

There is a limitation about the number of simultaneous connections to the database in the SQL Server 2005 Developer Edition DVD ?

Can be that error because of that ? What is TDS prelogin response ? Any ideea Microsoft guys ? Pls ...

|||I'm also getting the same error! Did you ever find anything out, Elvis? Somebody please respond. Anything will help.

Thanks,|||Hi all,

I fixed it! Well I don't know if this fixes it, but it's working now by some intervention. I changed the url from 127.0.0.1 to my intranet ip 172.22.x.x (not my external 64.72.x.x). I then telneted 172.22.x.x port 1434 (which works for 127.0.0.1) and it didn't work. Then I tried 172.22.x.x 1433 and it did (even though the server logs say it's listening on 1434 like 127.0.0.1). Again, this may just be a coincedense, but at least I'm up and running now.

Thanks,|||

Yes, the error message could stand to be more useful. I will make sure we address that.

But it looks like you've solved the problem though. By default, SQL Server listens for TCP connections on port 1433. Port 1434 is normally used by SQL Browser to respond to UDP queries to map a SQL Server instance name to IP address & port number.

Thank you for your feedback,

--David Olix

JDBC Development

|||

The problem was posted on 23 Jan 2006 . It took 7 moths for anyone from mirosoft to figure it out. I've foud the problem the day before but you in an increddible time of 7 moths.

You are the best, with "your messages" and "increddible fast work" :))

|||

The problem was posted on 23 Jan 2006 . It took 7 moths for anyone from mirosoft to figure it out. I've found the problem the day after i've posted the problem, but you in an increddible time of 7 months.

You are the best, with "your messages" and "increddible fast work" :))

|||

ProgrammerJ,

I sincerely apologize for not responding to your question sooner. We do try our best to respond to all questions posted on this forum within two business days. In this case we did not. Indeed, the only reason I saw your post at all is that someone else followed up to it with a post about the same problem, which brought the thread back to the top of the list. However, I hope this has not dissuaded you from using the Microsoft SQL Server 2005 JDBC driver.

Again, my apologies, and thank you for your feedback,

--David Olix

JDBC Development

|||http://msdn.microsoft.com/newsgroups/default.aspx?&guid=&sloc=en-us&dg=microsoft.public.sqlserver.jdbcdriver&p=1&tid=f0f1c794-a805-4370-aca7-4f633c460104&mid=f0f1c794-a805-4370-aca7-4f633c460104

I have posted a similar problem on the jdbc discussion newsgroup. We are using the production 1.1 version of the JDBC driver. To repeat that post:

The stack trace is

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown
Source)
at com.microsoft.sqlserver.jdbc.TDSWriter.flush(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSWriter.endRequest(Unknown Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown
Source)

This is happening during a performance stress test, and only happens very
sporadically: two or three times during a multi-hour test of several
simultaneous users pounding on the database. There are no events on the DB
server, and nothing in the SQL Server 2005 log. The Tomcat server is running
on Linux. We are using a commons-DBCP connection pool that validates the
connection on borrow with "select 1", so we know the connection is good when
we get it for the pool. The network error happens right after a prepare
statement, so the connection is working, and then breaks on the execute of
the prepared statement. The query is not a long running query. We've also
checked the network health and it looks fine. We start some background batch
jobs against the server before the errors start to occur.

We are pretty much out of ideas. There are some relevant threads we can see
by Googling this, but neither IE nor Firefox can get the msdn.forums,
complaining the URL will never complete or something, which can be caused by
not accepting cookies, but that's not the problem.

Plase HELP ! SQL ERROR =>> An I/O error occurred while receiving the TDS prelogin response

i get this error when i try to connect to sql server 2005 developer edition with the latest JDBC.

sql string =>> jdbc:sqlserver://localhost:1434;databaseName=c1 Elvis elvis
SQL STATE: 08S01
ERROR CODE: 0
MESSAGE: An I/O error occurred while receiving the TDS prelogin response.

And the stacktrace is:

com.microsoft.sqlserver.jdbc.SQLServerException: An I/O error occurred while receiving the TDS prelogin response.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.Prelogin(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.<init>(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:171)

and is called :connection=DriverManager.getConnection(con, user, password);

Please tell me what is wrong ? Because in other part of the code it works fine !

What is this "I/O error occurred while receiving the TDS prelogin response" ?

There is a limitation about the number of simultaneous connections to the database in the SQL Server 2005 Developer Edition DVD ?

Can be that error because of that ? What is TDS prelogin response ? Any ideea Microsoft guys ? Pls ...

|||I'm also getting the same error! Did you ever find anything out, Elvis? Somebody please respond. Anything will help.

Thanks,|||Hi all,

I fixed it! Well I don't know if this fixes it, but it's working now by some intervention. I changed the url from 127.0.0.1 to my intranet ip 172.22.x.x (not my external 64.72.x.x). I then telneted 172.22.x.x port 1434 (which works for 127.0.0.1) and it didn't work. Then I tried 172.22.x.x 1433 and it did (even though the server logs say it's listening on 1434 like 127.0.0.1). Again, this may just be a coincedense, but at least I'm up and running now.

Thanks,|||

Yes, the error message could stand to be more useful. I will make sure we address that.

But it looks like you've solved the problem though. By default, SQL Server listens for TCP connections on port 1433. Port 1434 is normally used by SQL Browser to respond to UDP queries to map a SQL Server instance name to IP address & port number.

Thank you for your feedback,

--David Olix

JDBC Development

|||

The problem was posted on 23 Jan 2006 . It took 7 moths for anyone from mirosoft to figure it out. I've foud the problem the day before but you in an increddible time of 7 moths.

You are the best, with "your messages" and "increddible fast work" :))

|||

The problem was posted on 23 Jan 2006 . It took 7 moths for anyone from mirosoft to figure it out. I've found the problem the day after i've posted the problem, but you in an increddible time of 7 months.

You are the best, with "your messages" and "increddible fast work" :))

|||

ProgrammerJ,

I sincerely apologize for not responding to your question sooner. We do try our best to respond to all questions posted on this forum within two business days. In this case we did not. Indeed, the only reason I saw your post at all is that someone else followed up to it with a post about the same problem, which brought the thread back to the top of the list. However, I hope this has not dissuaded you from using the Microsoft SQL Server 2005 JDBC driver.

Again, my apologies, and thank you for your feedback,

--David Olix

JDBC Development

|||http://msdn.microsoft.com/newsgroups/default.aspx?&guid=&sloc=en-us&dg=microsoft.public.sqlserver.jdbcdriver&p=1&tid=f0f1c794-a805-4370-aca7-4f633c460104&mid=f0f1c794-a805-4370-aca7-4f633c460104

I have posted a similar problem on the jdbc discussion newsgroup. We are using the production 1.1 version of the JDBC driver. To repeat that post:

The stack trace is

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown
Source)
at com.microsoft.sqlserver.jdbc.TDSWriter.flush(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSWriter.endRequest(Unknown Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown
Source)

This is happening during a performance stress test, and only happens very
sporadically: two or three times during a multi-hour test of several
simultaneous users pounding on the database. There are no events on the DB
server, and nothing in the SQL Server 2005 log. The Tomcat server is running
on Linux. We are using a commons-DBCP connection pool that validates the
connection on borrow with "select 1", so we know the connection is good when
we get it for the pool. The network error happens right after a prepare
statement, so the connection is working, and then breaks on the execute of
the prepared statement. The query is not a long running query. We've also
checked the network health and it looks fine. We start some background batch
jobs against the server before the errors start to occur.

We are pretty much out of ideas. There are some relevant threads we can see
by Googling this, but neither IE nor Firefox can get the msdn.forums,
complaining the URL will never complete or something, which can be caused by
not accepting cookies, but that's not the problem.

sql

Plase HELP ! SQL ERROR =>> An I/O error occurred while receiving the TDS prelogin resp

i get this error when i try to connect to sql server 2005 developer edition with the latest JDBC.

sql string =>> jdbc:sqlserver://localhost:1434;databaseName=c1 Elvis elvis
SQL STATE: 08S01
ERROR CODE: 0
MESSAGE: An I/O error occurred while receiving the TDS prelogin response.

And the stacktrace is:

com.microsoft.sqlserver.jdbc.SQLServerException: An I/O error occurred while receiving the TDS prelogin response.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.Prelogin(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.<init>(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:171)

and is called :connection=DriverManager.getConnection(con, user, password);

Please tell me what is wrong ? Because in other part of the code it works fine !

What is this "I/O error occurred while receiving the TDS prelogin response" ?

There is a limitation about the number of simultaneous connections to the database in the SQL Server 2005 Developer Edition DVD ?

Can be that error because of that ? What is TDS prelogin response ? Any ideea Microsoft guys ? Pls ...

|||I'm also getting the same error! Did you ever find anything out, Elvis? Somebody please respond. Anything will help.

Thanks,|||Hi all,

I fixed it! Well I don't know if this fixes it, but it's working now by some intervention. I changed the url from 127.0.0.1 to my intranet ip 172.22.x.x (not my external 64.72.x.x). I then telneted 172.22.x.x port 1434 (which works for 127.0.0.1) and it didn't work. Then I tried 172.22.x.x 1433 and it did (even though the server logs say it's listening on 1434 like 127.0.0.1). Again, this may just be a coincedense, but at least I'm up and running now.

Thanks,|||

Yes, the error message could stand to be more useful. I will make sure we address that.

But it looks like you've solved the problem though. By default, SQL Server listens for TCP connections on port 1433. Port 1434 is normally used by SQL Browser to respond to UDP queries to map a SQL Server instance name to IP address & port number.

Thank you for your feedback,

--David Olix

JDBC Development

|||

The problem was posted on 23 Jan 2006 . It took 7 moths for anyone from mirosoft to figure it out. I've foud the problem the day before but you in an increddible time of 7 moths.

You are the best, with "your messages" and "increddible fast work" :))

|||

The problem was posted on 23 Jan 2006 . It took 7 moths for anyone from mirosoft to figure it out. I've found the problem the day after i've posted the problem, but you in an increddible time of 7 months.

You are the best, with "your messages" and "increddible fast work" :))

|||

ProgrammerJ,

I sincerely apologize for not responding to your question sooner. We do try our best to respond to all questions posted on this forum within two business days. In this case we did not. Indeed, the only reason I saw your post at all is that someone else followed up to it with a post about the same problem, which brought the thread back to the top of the list. However, I hope this has not dissuaded you from using the Microsoft SQL Server 2005 JDBC driver.

Again, my apologies, and thank you for your feedback,

--David Olix

JDBC Development

|||http://msdn.microsoft.com/newsgroups/default.aspx?&guid=&sloc=en-us&dg=microsoft.public.sqlserver.jdbcdriver&p=1&tid=f0f1c794-a805-4370-aca7-4f633c460104&mid=f0f1c794-a805-4370-aca7-4f633c460104

I have posted a similar problem on the jdbc discussion newsgroup. We are using the production 1.1 version of the JDBC driver. To repeat that post:

The stack trace is

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown
Source)
at com.microsoft.sqlserver.jdbc.TDSWriter.flush(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSWriter.endRequest(Unknown Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown
Source)

This is happening during a performance stress test, and only happens very
sporadically: two or three times during a multi-hour test of several
simultaneous users pounding on the database. There are no events on the DB
server, and nothing in the SQL Server 2005 log. The Tomcat server is running
on Linux. We are using a commons-DBCP connection pool that validates the
connection on borrow with "select 1", so we know the connection is good when
we get it for the pool. The network error happens right after a prepare
statement, so the connection is working, and then breaks on the execute of
the prepared statement. The query is not a long running query. We've also
checked the network health and it looks fine. We start some background batch
jobs against the server before the errors start to occur.

We are pretty much out of ideas. There are some relevant threads we can see
by Googling this, but neither IE nor Firefox can get the msdn.forums,
complaining the URL will never complete or something, which can be caused by
not accepting cookies, but that's not the problem.

PLASE HELP - remote connectivity to named instance of 2005

Hi all,
I just installed version 2005, and it is co-existing with
an instance of 2000. I am trying to test the remote connection string from
an ASP .Net page which works fine already in 2000. However it does not work
in 2005.
In 2005, I went to configuration Manager/Protocols/TCP IP, and entered 7544
as the default port, and for port value in IP1, IP2, and IP3. Then, in my
connection string I just included the port number, and changed the
databasename to point to 'AdventureWorks' instead of the DB that I used for
2K. The connection could not be openned. I also configured the user id, and
password the same way as I have in 2K, and re-started the engine.
This is a sample of my connection string:
this.sqlConnection1.ConnectionString =
"Server=xxx.xx.xx.xx\\<myNamedServerInstance>,7544;Database=AdventureWorks;U
ser
ID=myUID;Password=myPWD;Trusted_Connecti
on=True";
I tested the local connection to this server/Database, and it works fine,
however, there is a problem with the remote connection.
Any help will be appreciated.
Thanks in advance,
CarlosNever mind.. I got it Thanks.
"Carlos" <chsanin@.earthlink.net> wrote in message
news:uaKDKFB5FHA.2560@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I just installed version 2005, and it is co-existing with
> an instance of 2000. I am trying to test the remote connection string from
> an ASP .Net page which works fine already in 2000. However it does not
> work in 2005.
> In 2005, I went to configuration Manager/Protocols/TCP IP, and entered
> 7544 as the default port, and for port value in IP1, IP2, and IP3. Then,
> in my connection string I just included the port number, and changed the
> databasename to point to 'AdventureWorks' instead of the DB that I used
> for 2K. The connection could not be openned. I also configured the user
> id, and password the same way as I have in 2K, and re-started the engine.
> This is a sample of my connection string:
> this.sqlConnection1.ConnectionString =
> "Server=xxx.xx.xx.xx\\<myNamedServerInstance>,7544;Database=AdventureWorks
;User
> ID=myUID;Password=myPWD;Trusted_Connecti
on=True";
> I tested the local connection to this server/Database, and it works fine,
> however, there is a problem with the remote connection.
> Any help will be appreciated.
> Thanks in advance,
> Carlos
>

PLASE HELP - remote connectivity to named instance of 2005

Hi all,
I just installed version 2005, and it is co-existing with
an instance of 2000. I am trying to test the remote connection string from
an ASP .Net page which works fine already in 2000. However it does not work
in 2005.
In 2005, I went to configuration Manager/Protocols/TCP IP, and entered 7544
as the default port, and for port value in IP1, IP2, and IP3. Then, in my
connection string I just included the port number, and changed the
databasename to point to 'AdventureWorks' instead of the DB that I used for
2K. The connection could not be openned. I also configured the user id, and
password the same way as I have in 2K, and re-started the engine.
This is a sample of my connection string:
this.sqlConnection1.ConnectionString =
"Server=xxx.xx.xx.xx\\<myNamedServerInstance>,7544 ;Database=AdventureWorks;User
ID=myUID;Password=myPWD;Trusted_Connection=True";
I tested the local connection to this server/Database, and it works fine,
however, there is a problem with the remote connection.
Any help will be appreciated.
Thanks in advance,
Carlos
Never mind.. I got it Thanks.
"Carlos" <chsanin@.earthlink.net> wrote in message
news:uaKDKFB5FHA.2560@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I just installed version 2005, and it is co-existing with
> an instance of 2000. I am trying to test the remote connection string from
> an ASP .Net page which works fine already in 2000. However it does not
> work in 2005.
> In 2005, I went to configuration Manager/Protocols/TCP IP, and entered
> 7544 as the default port, and for port value in IP1, IP2, and IP3. Then,
> in my connection string I just included the port number, and changed the
> databasename to point to 'AdventureWorks' instead of the DB that I used
> for 2K. The connection could not be openned. I also configured the user
> id, and password the same way as I have in 2K, and re-started the engine.
> This is a sample of my connection string:
> this.sqlConnection1.ConnectionString =
> "Server=xxx.xx.xx.xx\\<myNamedServerInstance>,7544 ;Database=AdventureWorks;User
> ID=myUID;Password=myPWD;Trusted_Connection=True";
> I tested the local connection to this server/Database, and it works fine,
> however, there is a problem with the remote connection.
> Any help will be appreciated.
> Thanks in advance,
> Carlos
>

Plase help

Hello,
I have a developer who developed the following stored procedure.
My question is since it started with a Begin Transaction and if the record
exists in the EXISTS statement and it will return an ID without commit /
rollback, does he need to either commit or rollback the transaction?
begin transaction
if EXISTS(select OpportunityId from Opportunity where OTN = @.OTN and
CustomerName = @.CustomerName and ResponseDate = @.ResponseDate and CreateDate
= convert(char(11),getdate(),106))
Begin
Select @.OpportunityId = OpportunityId from Opportunity where OTN = @.OTN
and CustomerName = @.CustomerName and ResponseDate = @.ResponseDate and
CreateDate = convert(char(11),getdate(),106)
Return @.OpportunityId
End
else
Begin
Insert into Opportunity (OTN, CustomerName, ResponseDate) VALUES (@.OTN,
@.CustomerName, @.ResponseDate)
If @.@.Error <> 0
Begin
Rollback Transaction
Return -1
End
RETURN SCOPE_IDENTITY( )
Commit Transaction
End
GOEd,
Try moving the BEGIN TRAN down in the code to just prior the DML (INSERT)
statment.
HTH
Jerry
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:BA6A49DF-BC78-412D-BC86-9D456BFDDBE1@.microsoft.com...
> Hello,
> I have a developer who developed the following stored procedure.
> My question is since it started with a Begin Transaction and if the
> record
> exists in the EXISTS statement and it will return an ID without commit /
> rollback, does he need to either commit or rollback the transaction?
>
>
> begin transaction
> if EXISTS(select OpportunityId from Opportunity where OTN = @.OTN and
> CustomerName = @.CustomerName and ResponseDate = @.ResponseDate and
> CreateDate
> = convert(char(11),getdate(),106))
> Begin
> Select @.OpportunityId = OpportunityId from Opportunity where OTN = @.OTN
> and CustomerName = @.CustomerName and ResponseDate = @.ResponseDate and
> CreateDate = convert(char(11),getdate(),106)
> Return @.OpportunityId
> End
> else
> Begin
> Insert into Opportunity (OTN, CustomerName, ResponseDate) VALUES (@.OTN,
> @.CustomerName, @.ResponseDate)
> If @.@.Error <> 0
> Begin
> Rollback Transaction
> Return -1
> End
> RETURN SCOPE_IDENTITY( )
> Commit Transaction
> End
>
> GO|||There is only one insert statement that I can see. Transaction is not
necessary at all in this case.
It is also not necessary to perform the same query two times in a row...
select the value once, then check to see if it is null.
SELECT @.OpportunityId = OpportunityId FROM Opportunity WHERE OTN = @.OTN
AND CustomerName = @.CustomerName AND ResponseDate = @.ResponseDate AND
CreateDate = convert(char(11),getdate(),106)
IF @.OpportunityID IS NOT NULL Return @.OpportunityId
ELSE
BEGIN
INSERT INTO Opportunity (OTN, CustomerName, ResponseDate) VALUES (@.OTN,
@.CustomerName, @.ResponseDate)
IF @.@.Error <> 0 RETURN -1
ELSE RETURN SCOPE_IDENTITY()
END
END
GO
John Scragg
"Ed" wrote:

> Hello,
> I have a developer who developed the following stored procedure.
> My question is since it started with a Begin Transaction and if the reco
rd
> exists in the EXISTS statement and it will return an ID without commit /
> rollback, does he need to either commit or rollback the transaction?
>
>
> begin transaction
> if EXISTS(select OpportunityId from Opportunity where OTN = @.OTN and
> CustomerName = @.CustomerName and ResponseDate = @.ResponseDate and CreateDa
te
> = convert(char(11),getdate(),106))
> Begin
> Select @.OpportunityId = OpportunityId from Opportunity where OTN = @.OTN
> and CustomerName = @.CustomerName and ResponseDate = @.ResponseDate and
> CreateDate = convert(char(11),getdate(),106)
> Return @.OpportunityId
> End
> else
> Begin
> Insert into Opportunity (OTN, CustomerName, ResponseDate) VALUES (@.OTN,
> @.CustomerName, @.ResponseDate)
> If @.@.Error <> 0
> Begin
> Rollback Transaction
> Return -1
> End
> RETURN SCOPE_IDENTITY( )
> Commit Transaction
> End
>
> GO|||However, the is a risk that the row is deleted after the SELECT and before t
he INSERT. To eliminate
that risk, isolation SERIALIZABLE should be used, which requires a transacti
on (to do its work)...
:-)
Also, the code doesn't catch if the SELECT returns several rows. This is why
I prefer SET @.var =
(SELECT ...). A run-time error will be raised if SELECT returns > 1 row.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:969D558F-CCA2-4E38-A87F-2B52073A707D@.microsoft.com...
> There is only one insert statement that I can see. Transaction is not
> necessary at all in this case.
> It is also not necessary to perform the same query two times in a row...
> select the value once, then check to see if it is null.
> SELECT @.OpportunityId = OpportunityId FROM Opportunity WHERE OTN = @.OTN
> AND CustomerName = @.CustomerName AND ResponseDate = @.ResponseDate AND
> CreateDate = convert(char(11),getdate(),106)
> IF @.OpportunityID IS NOT NULL Return @.OpportunityId
> ELSE
> BEGIN
> INSERT INTO Opportunity (OTN, CustomerName, ResponseDate) VALUES (@.OTN,
> @.CustomerName, @.ResponseDate)
> IF @.@.Error <> 0 RETURN -1
> ELSE RETURN SCOPE_IDENTITY()
> END
> END
> GO
> John Scragg
> "Ed" wrote:
>|||Tibor, you make some good points. I am afraid I was not as clear and the
formatting of SQL in the newsgroup does not help :-) Let me try to be more
clear.
The original proc was apparently designed to perform 1 of 2 action. Either
(1) return the ID of an existing row, or (2) in the absance of a row, insert
a new row and return the ID of the new row.
If the row is deleted after the select it should not matter (as far as the
proc is concerned) since there is nothing else happening. If the select
returns a value, the proc returns the coulmn value selected and the INSERT
statement is ignored. The way it was written originally there were two
identical selects. First to check IF EXISTS and then the same query to get
the value. This did provide a tiny window for a row to be deleted between
the two selects, but my opinion was that it is simply ineffcient to query
twice for the same result :-)
As for the possible error on multiple rows, you are absolutely right. It
can error. It is the same as the developer's original code, and so I am
assuming that they have confirmed the values in the where clause will provid
e
a unique row (through constraints, rules, etc.). If not, then he has this
problem in his original code as well.
John
"Tibor Karaszi" wrote:

> However, the is a risk that the row is deleted after the SELECT and before
the INSERT. To eliminate
> that risk, isolation SERIALIZABLE should be used, which requires a transac
tion (to do its work)...
> :-)
> Also, the code doesn't catch if the SELECT returns several rows. This is w
hy I prefer SET @.var =
> (SELECT ...). A run-time error will be raised if SELECT returns > 1 row.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
> news:969D558F-CCA2-4E38-A87F-2B52073A707D@.microsoft.com...
>|||That is a good point that I never think about...
How am I able to control/put the Transaction to even lock the Select
statement to make sure when the record/ID is returned, there is no one else
delete the record already?
Thanks
Ed
"Tibor Karaszi" wrote:

> However, the is a risk that the row is deleted after the SELECT and before
the INSERT. To eliminate
> that risk, isolation SERIALIZABLE should be used, which requires a transac
tion (to do its work)...
> :-)
> Also, the code doesn't catch if the SELECT returns several rows. This is w
hy I prefer SET @.var =
> (SELECT ...). A run-time error will be raised if SELECT returns > 1 row.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
> news:969D558F-CCA2-4E38-A87F-2B52073A707D@.microsoft.com...
>|||Sorry, John. I misinterpreted the logic. It looked like me as if the row exi
sts, get the values
*and* do an insert. I didn't read the original code carefully enough. I catc
hed that you picked up
on the "read twice" issue, but thought that a hole was left as I assumed ano
ther logic. My bad...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:7C58CD9A-0D47-4D4D-A68B-15F5D64D65B2@.microsoft.com...
> Tibor, you make some good points. I am afraid I was not as clear and the
> formatting of SQL in the newsgroup does not help :-) Let me try to be mor
e
> clear.
> The original proc was apparently designed to perform 1 of 2 action. Eithe
r
> (1) return the ID of an existing row, or (2) in the absance of a row, inse
rt
> a new row and return the ID of the new row.
> If the row is deleted after the select it should not matter (as far as the
> proc is concerned) since there is nothing else happening. If the select
> returns a value, the proc returns the coulmn value selected and the INSERT
> statement is ignored. The way it was written originally there were two
> identical selects. First to check IF EXISTS and then the same query to ge
t
> the value. This did provide a tiny window for a row to be deleted between
> the two selects, but my opinion was that it is simply ineffcient to query
> twice for the same result :-)
> As for the possible error on multiple rows, you are absolutely right. It
> can error. It is the same as the developer's original code, and so I am
> assuming that they have confirmed the values in the where clause will prov
ide
> a unique row (through constraints, rules, etc.). If not, then he has this
> problem in his original code as well.
> John
> "Tibor Karaszi" wrote:
>