[TCP/IP Sockets]ConnectionCheckForData()) Error

Error was returned when we compiled a proc. The proc compiles under SP3a.
We're using Standard Edition SP4.
The offending statement is a sub-select in a where clause that uses a union:
and mv.INSTR_ID in
(
select @.INSTR_ID
union
select ridf.INSTR_ID
from FT_T_RIDF ridf
join FT_T_RISS riss on riss.RLD_ISS_FEAT_ID = ridf.RLD_ISS_FEAT_ID
and riss.PART_UNITS_TYP = 'Option' and riss.INSTR_ID = @.INSTR_ID
where ridf.REL_TYP = 'OPTION'
)change to:
( select @.INSTR_ID AS [ID]
union
select ridf.INSTR_ID
from FT_T_RIDF ridf
join FT_T_RISS riss on riss.RLD_ISS_FEAT_ID = ridf.RLD_ISS_FEAT_ID
and riss.PART_UNITS_TYP = 'Option' and riss.INSTR_ID = @.INSTR_ID
where ridf.REL_TYP = 'OPTION'
)
--
-oj
"davidhg" <davidhg@.discussions.microsoft.com> wrote in message
news:3255283F-3B9E-435B-BFC4-51CF9B5069BD@.microsoft.com...
> Error was returned when we compiled a proc. The proc compiles under SP3a.
> We're using Standard Edition SP4.
> The offending statement is a sub-select in a where clause that uses a
> union:
> and mv.INSTR_ID in
> (
> select @.INSTR_ID
> union
> select ridf.INSTR_ID
> from FT_T_RIDF ridf
> join FT_T_RISS riss on riss.RLD_ISS_FEAT_ID = ridf.RLD_ISS_FEAT_ID
> and riss.PART_UNITS_TYP = 'Option' and riss.INSTR_ID = @.INSTR_ID
> where ridf.REL_TYP = 'OPTION'
> )
>|||Hi oj,
Unfortunately, the error still occurs. To compile the proc, we created a
pseudo-talbe from the sub-select with the union statement and joined it
directly to the other tables. FYI, I opened a case with MS.
--
Dave
"oj" wrote:
> change to:
> ( select @.INSTR_ID AS [ID]
> union
> select ridf.INSTR_ID
> from FT_T_RIDF ridf
> join FT_T_RISS riss on riss.RLD_ISS_FEAT_ID = ridf.RLD_ISS_FEAT_ID
> and riss.PART_UNITS_TYP = 'Option' and riss.INSTR_ID = @.INSTR_ID
> where ridf.REL_TYP = 'OPTION'
> )
> --
> -oj
>
> "davidhg" <davidhg@.discussions.microsoft.com> wrote in message
> news:3255283F-3B9E-435B-BFC4-51CF9B5069BD@.microsoft.com...
> > Error was returned when we compiled a proc. The proc compiles under SP3a.
> > We're using Standard Edition SP4.
> >
> > The offending statement is a sub-select in a where clause that uses a
> > union:
> > and mv.INSTR_ID in
> > (
> > select @.INSTR_ID
> > union
> > select ridf.INSTR_ID
> > from FT_T_RIDF ridf
> > join FT_T_RISS riss on riss.RLD_ISS_FEAT_ID = ridf.RLD_ISS_FEAT_ID
> > and riss.PART_UNITS_TYP = 'Option' and riss.INSTR_ID = @.INSTR_ID
> > where ridf.REL_TYP = 'OPTION'
> > )
> >
> >
>
>|||Okay. Without seeing the *whole* thing I'm not sure what would be the case.
The syntax looks right to me.
Do post back if you hear anything.
--
-oj
"davidhg" <davidhg@.discussions.microsoft.com> wrote in message
news:21FFCFE8-EED9-47FA-821C-AE224C630FDC@.microsoft.com...
> Hi oj,
> Unfortunately, the error still occurs. To compile the proc, we created a
> pseudo-talbe from the sub-select with the union statement and joined it
> directly to the other tables. FYI, I opened a case with MS.
> --
> Dave
>
> "oj" wrote:
>> change to:
>> ( select @.INSTR_ID AS [ID]
>> union
>> select ridf.INSTR_ID
>> from FT_T_RIDF ridf
>> join FT_T_RISS riss on riss.RLD_ISS_FEAT_ID = ridf.RLD_ISS_FEAT_ID
>> and riss.PART_UNITS_TYP = 'Option' and riss.INSTR_ID = @.INSTR_ID
>> where ridf.REL_TYP = 'OPTION'
>> )
>> --
>> -oj
>>
>> "davidhg" <davidhg@.discussions.microsoft.com> wrote in message
>> news:3255283F-3B9E-435B-BFC4-51CF9B5069BD@.microsoft.com...
>> > Error was returned when we compiled a proc. The proc compiles under
>> > SP3a.
>> > We're using Standard Edition SP4.
>> >
>> > The offending statement is a sub-select in a where clause that uses a
>> > union:
>> > and mv.INSTR_ID in
>> > (
>> > select @.INSTR_ID
>> > union
>> > select ridf.INSTR_ID
>> > from FT_T_RIDF ridf
>> > join FT_T_RISS riss on riss.RLD_ISS_FEAT_ID = ridf.RLD_ISS_FEAT_ID
>> > and riss.PART_UNITS_TYP = 'Option' and riss.INSTR_ID = @.INSTR_ID
>> > where ridf.REL_TYP = 'OPTION'
>> > )
>> >
>> >
>>sql

[TCP/IP Sockets]ConnectionCheckForData()) Error

Error was returned when we compiled a proc. The proc compiles under SP3a.
We're using Standard Edition SP4.
The offending statement is a sub-select in a where clause that uses a union:
and mv.INSTR_ID in
(
select @.INSTR_ID
union
select ridf.INSTR_ID
from FT_T_RIDF ridf
join FT_T_RISS riss on riss.RLD_ISS_FEAT_ID = ridf.RLD_ISS_FEAT_ID
and riss.PART_UNITS_TYP = 'Option' and riss.INSTR_ID = @.INSTR_ID
where ridf.REL_TYP = 'OPTION'
)change to:
( select @.INSTR_ID AS [ID]
union
select ridf.INSTR_ID
from FT_T_RIDF ridf
join FT_T_RISS riss on riss.RLD_ISS_FEAT_ID = ridf.RLD_ISS_FEAT_ID
and riss.PART_UNITS_TYP = 'Option' and riss.INSTR_ID = @.INSTR_ID
where ridf.REL_TYP = 'OPTION'
)
-oj
"davidhg" <davidhg@.discussions.microsoft.com> wrote in message
news:3255283F-3B9E-435B-BFC4-51CF9B5069BD@.microsoft.com...
> Error was returned when we compiled a proc. The proc compiles under SP3a.
> We're using Standard Edition SP4.
> The offending statement is a sub-select in a where clause that uses a
> union:
> and mv.INSTR_ID in
> (
> select @.INSTR_ID
> union
> select ridf.INSTR_ID
> from FT_T_RIDF ridf
> join FT_T_RISS riss on riss.RLD_ISS_FEAT_ID = ridf.RLD_ISS_FEAT_ID
> and riss.PART_UNITS_TYP = 'Option' and riss.INSTR_ID = @.INSTR_ID
> where ridf.REL_TYP = 'OPTION'
> )
>|||Hi oj,
Unfortunately, the error still occurs. To compile the proc, we created a
pseudo-talbe from the sub-select with the union statement and joined it
directly to the other tables. FYI, I opened a case with MS.
--
Dave
"oj" wrote:

> change to:
> ( select @.INSTR_ID AS [ID]
> union
> select ridf.INSTR_ID
> from FT_T_RIDF ridf
> join FT_T_RISS riss on riss.RLD_ISS_FEAT_ID = ridf.RLD_ISS_FEAT_ID
> and riss.PART_UNITS_TYP = 'Option' and riss.INSTR_ID = @.INSTR_ID
> where ridf.REL_TYP = 'OPTION'
> )
> --
> -oj
>
> "davidhg" <davidhg@.discussions.microsoft.com> wrote in message
> news:3255283F-3B9E-435B-BFC4-51CF9B5069BD@.microsoft.com...
>
>|||Okay. Without seeing the *whole* thing I'm not sure what would be the case.
The syntax looks right to me.
Do post back if you hear anything.
-oj
"davidhg" <davidhg@.discussions.microsoft.com> wrote in message
news:21FFCFE8-EED9-47FA-821C-AE224C630FDC@.microsoft.com...[vbcol=seagreen]
> Hi oj,
> Unfortunately, the error still occurs. To compile the proc, we created a
> pseudo-talbe from the sub-select with the union statement and joined it
> directly to the other tables. FYI, I opened a case with MS.
> --
> Dave
>
> "oj" wrote:
>

[TCP/IP Sockets]ConnectionCheckForData()) Error

Error was returned when we compiled a proc. The proc compiles under SP3a.
We're using Standard Edition SP4.
The offending statement is a sub-select in a where clause that uses a union:
and mv.INSTR_ID in
(
select @.INSTR_ID
union
select ridf.INSTR_ID
from FT_T_RIDF ridf
join FT_T_RISS riss on riss.RLD_ISS_FEAT_ID = ridf.RLD_ISS_FEAT_ID
and riss.PART_UNITS_TYP = 'Option' and riss.INSTR_ID = @.INSTR_ID
where ridf.REL_TYP = 'OPTION'
)
change to:
( select @.INSTR_ID AS [ID]
union
select ridf.INSTR_ID
from FT_T_RIDF ridf
join FT_T_RISS riss on riss.RLD_ISS_FEAT_ID = ridf.RLD_ISS_FEAT_ID
and riss.PART_UNITS_TYP = 'Option' and riss.INSTR_ID = @.INSTR_ID
where ridf.REL_TYP = 'OPTION'
)
-oj
"davidhg" <davidhg@.discussions.microsoft.com> wrote in message
news:3255283F-3B9E-435B-BFC4-51CF9B5069BD@.microsoft.com...
> Error was returned when we compiled a proc. The proc compiles under SP3a.
> We're using Standard Edition SP4.
> The offending statement is a sub-select in a where clause that uses a
> union:
> and mv.INSTR_ID in
> (
> select @.INSTR_ID
> union
> select ridf.INSTR_ID
> from FT_T_RIDF ridf
> join FT_T_RISS riss on riss.RLD_ISS_FEAT_ID = ridf.RLD_ISS_FEAT_ID
> and riss.PART_UNITS_TYP = 'Option' and riss.INSTR_ID = @.INSTR_ID
> where ridf.REL_TYP = 'OPTION'
> )
>
|||Hi oj,
Unfortunately, the error still occurs. To compile the proc, we created a
pseudo-talbe from the sub-select with the union statement and joined it
directly to the other tables. FYI, I opened a case with MS.
Dave
"oj" wrote:

> change to:
> ( select @.INSTR_ID AS [ID]
> union
> select ridf.INSTR_ID
> from FT_T_RIDF ridf
> join FT_T_RISS riss on riss.RLD_ISS_FEAT_ID = ridf.RLD_ISS_FEAT_ID
> and riss.PART_UNITS_TYP = 'Option' and riss.INSTR_ID = @.INSTR_ID
> where ridf.REL_TYP = 'OPTION'
> )
> --
> -oj
>
> "davidhg" <davidhg@.discussions.microsoft.com> wrote in message
> news:3255283F-3B9E-435B-BFC4-51CF9B5069BD@.microsoft.com...
>
>
|||Okay. Without seeing the *whole* thing I'm not sure what would be the case.
The syntax looks right to me.
Do post back if you hear anything.
-oj
"davidhg" <davidhg@.discussions.microsoft.com> wrote in message
news:21FFCFE8-EED9-47FA-821C-AE224C630FDC@.microsoft.com...[vbcol=seagreen]
> Hi oj,
> Unfortunately, the error still occurs. To compile the proc, we created a
> pseudo-talbe from the sub-select with the union statement and joined it
> directly to the other tables. FYI, I opened a case with MS.
> --
> Dave
>
> "oj" wrote:

[rsInvalidExpressionDataType] The Value expression used in image ‘image1’ returned a data type t

[rsInvalidExpressionDataType] The Value expression used in image ‘image1’ returned a data type that is not valid.

[rsInvalidDatabaseImage] The Value expression for the image ‘image1’ did not evaluate to an image.

Hi How should i solve this problem

Regards

KAren

Try this out:

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

|||

thanks Greg,

got it to work.. Set the image as an external....

@@servername

Hello,
Can anybody tell me why when i "select @.@.servername" it=B4s returned NULL value?
Should i do an INSERT in the master..sysservers? or can you tell me if exists something that correct this situation?
Best RegardsMake a backup of master firs
do sp_dropserver 'servername
g
sp_addserver 'servername', loca
g
then stop and start your server
Then select @.@.servernam
Jef
MCDBA, MCSE+|||Thanks Jeff
>--Original Message--
>Make a backup of master first
>do sp_dropserver 'servername'
>go
>sp_addserver 'servername', local
>go
>then stop and start your server.
>Then select @.@.servername
>Jeff
>MCDBA, MCSE+I
>.
>

@@Rowcount or output parameters error

I have the following stored procedure working with an Access 2000 front end. The output parameters returned to Access are both Null when the record is successfully updated (ie when @.@.Rowcount = 1), but the correct parameters are returned when the update fails. I'm a bit new to using output parameters, but I have them working perfectly with an insert sproc, and they look basically the same. What bonehead error have I made here? The fact that the record is updated indicates to me that the Commit Trans line is being executed, so why aren't the 2 output parameters set?

TIA

EDIT: Solved, sort of. I found that dropping the "@.ResNum +" from "@.ResNum + ' Updated'" resolved the problem (@.ResNum is an input parameter). This implies that the variable lost its value between the SQL statement and the If/Then, since the SQL correctly updates only the appropriate record from the WHERE clause. Is this supposed to happen? I looked in BOL, and if it's addressed there I missed it.

CREATE PROCEDURE [procResUpdate]

Various input parameters here,

@.RetCode as int Output, @.RetResNum as nvarchar(15) Output

AS

Declare @.RowCounter int

Begin Tran

UPDATE tblReservations
SET Various set statements here, LastModified = @.LastModified + 1
WHERE ResNum = @.ResNum AND LastModified = @.LastModified

SELECT @.RowCounter = @.@.ROWCOUNT

If @.RowCounter = 1
Begin
Commit Tran
Select @.RetCode = 1
Select @.RetResNum = @.ResNum + ' Updated'
End
Else
Begin
Rollback Tran
Select @.RetCode = 0
Select @.RetResNum = 'Update Failed'
End
GOPost the complete DDL for the proc (including @.ResNum) and a sample of calling it that produces the problem you describe.

Regards,

hmscott|||Here's the complete sproc (I added back the offending part in red):

CREATE PROCEDURE [procResUpdate]

@.ReqDate as datetime, @.PassName as nvarchar(25), @.DispDate as datetime, @.PassPhone as nvarchar(25),
@.PassQuant as smallint, @.AuthBy as nvarchar(25), @.AcctID as smallint, @.PuLandmark as smallint, @.PuStreet as nvarchar(50),
@.PuCity as smallint, @.PuXStreet as nvarchar(50), @.PuPoint as nvarchar(50), @.DestLandmark as smallint, @.DestStreet as nvarchar(50),
@.DestCity as smallint, @.DestXStreet as nvarchar(50), @.DestPoint as nvarchar(50), @.Operator as smallint, @.Comments as nvarchar(250),
@.DriverReq as nvarchar(25), @.CarType as smallint, @.EstHours as money, @.EstPrice as money, @.CCType as nvarchar(25), @.CCnum as nvarchar(25),
@.CCAuthNum as nvarchar(25), @.CarQuant as tinyint, @.ResNum as int, @.Status as smallint, @.LastModified as tinyint,

@.RetCode as int = Null Output, @.RetResNum as nvarchar(15) = Null Output

AS

Declare @.RowCounter int

Begin Tran

UPDATE tblReservations
SET ReqDateTime = @.ReqDate, PassName = @.PassName, DispDateTime = @.DispDate, PassPhone = @.PassPhone, PassQuant = @.PassQuant, AuthBy = @.AuthBy, AcctID = @.AcctID,
PuLandmarkID = @.PuLandmark, PuStreet = @.PuStreet, PuCityID = @.PuCity, PuXStreet = @.PuXStreet, PuPoint = @.PuPoint, DestLandmarkID = @.DestLandmark, DestStreet = @.DestStreet,
DestCityID = @.DestCity, DestXStreet = @.DestXStreet, DestPoint = @.DestPoint, OperatorID = @.Operator, Comments = @.Comments, DriverReq = @.DriverReq, CarType = @.CarType,
EstHours = @.EstHours, EstPrice = @.EstPrice, CCType = @.CCType, CCNum = @.CCnum, CCAuthNum = @.CCAuthNum, CarQuant = @.CarQuant, Status = @.Status, LastModified = @.LastModified + 1
WHERE ResNum = @.ResNum AND LastModified = @.LastModified

SELECT @.RowCounter = @.@.ROWCOUNT

If @.RowCounter = 1
Begin
Commit Tran
Select @.RetCode = 1
Select @.RetResNum = ' Updated'
End
Else
Begin
Rollback Tran
Select @.RetCode = 0
Select @.RetResNum = @.ResNum + ' Update Failed'
End
GO


and the VBA that calls it. The 2 highlighted lines will be null if the "@.ResNum + " is included. Otherwise, it runs fine.

Public Sub cmdUpdate_Click()
Dim strSQL As String
Dim strField As String
Dim fOK As Integer
Dim intCount As Integer
Dim x As Integer
Dim strMsg As String
Dim cmd As ADODB.Command
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim dteReqDate As Date
Dim dteDispDate As Date

On Error GoTo ErrorHandler

dteReqDate = Me.txtReqDate & " " & Me.txtReqTime
dteDispDate = DateAdd("n", -Nz(Me.txtMinutesAhead, 0), dteReqDate)

Call EstablishConnection
Set db = CurrentDb()
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
Set tdf = db.TableDefs("tblReservations_Local")
rst.ActiveConnection = objConn

With cmd
.ActiveConnection = objConn
.CommandText = "procResUpdate"
.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("@.ReqDate", adDBTimeStamp, adParamInput, , dteReqDate)
.Parameters.Append .CreateParameter("@.PassName", adVarChar, adParamInput, 25, Me.txtPassName)
.Parameters.Append .CreateParameter("@.DispDate", adDBTimeStamp, adParamInput, , dteDispDate)
.Parameters.Append .CreateParameter("@.PassPhone", adVarChar, adParamInput, 25, Me.txtPassPhone)
.Parameters.Append .CreateParameter("@.PassQuant", adInteger, adParamInput, , Me.txtPassQuant)
.Parameters.Append .CreateParameter("@.AuthBy", adVarChar, adParamInput, 25, Me.txtAuthBy)
.Parameters.Append .CreateParameter("@.AcctID", adInteger, adParamInput, , Me.cboAcctNum)
.Parameters.Append .CreateParameter("@.PuLandmark", adInteger, adParamInput, , Me.cboLandmark)
.Parameters.Append .CreateParameter("@.PuStreet", adVarChar, adParamInput, 50, Me.txtPuAddress)
.Parameters.Append .CreateParameter("@.PuCity", adInteger, adParamInput, , Me.cboPuCity)
.Parameters.Append .CreateParameter("@.PuXStreet", adVarChar, adParamInput, 50, Me.txtPuXStreet)
.Parameters.Append .CreateParameter("@.PuPoint", adVarChar, adParamInput, 50, Me.txtPuPoint)
.Parameters.Append .CreateParameter("@.DestLandmark", adInteger, adParamInput, , Me.cboDestLandmark)
.Parameters.Append .CreateParameter("@.DestStreet", adVarChar, adParamInput, 50, Me.txtDestAddress)
.Parameters.Append .CreateParameter("@.DestCity", adInteger, adParamInput, , Me.cboDestCity)
.Parameters.Append .CreateParameter("@.DestXStreet", adVarChar, adParamInput, 50, Me.txtDestXStreet)
.Parameters.Append .CreateParameter("@.DestPoint", adVarChar, adParamInput, 50, Me.txtDestPoint)
.Parameters.Append .CreateParameter("@.Operator", adInteger, adParamInput, , Me.txtOperator)
.Parameters.Append .CreateParameter("@.Comments", adVarChar, adParamInput, 250, Me.txtComments)
.Parameters.Append .CreateParameter("@.DriverReq", adVarChar, adParamInput, 25, Me.txtDriverReq)
.Parameters.Append .CreateParameter("@.CarType", adInteger, adParamInput, , Me.cboCarType)
.Parameters.Append .CreateParameter("@.EstHours", adCurrency, adParamInput, , Me.txtEstHours)
.Parameters.Append .CreateParameter("@.EstPrice", adCurrency, adParamInput, , Me.txtEstPrice)
.Parameters.Append .CreateParameter("@.CCType", adVarChar, adParamInput, 25, Me.cboCCType)
.Parameters.Append .CreateParameter("@.CCnum", adVarChar, adParamInput, 25, Me.txtCCNum)
.Parameters.Append .CreateParameter("@.CCAuthNum", adVarChar, adParamInput, 25, Me.txtCCAuthNum)
.Parameters.Append .CreateParameter("@.CarQuant", adInteger, adParamInput, , Me.txtCarQuant)
.Parameters.Append .CreateParameter("@.ResNum", adInteger, adParamInput, , Me.txtResNum)
.Parameters.Append .CreateParameter("@.Status", adInteger, adParamInput, , Me.cboStatus)
.Parameters.Append .CreateParameter("@.LastModified", adInteger, adParamInput, , Me.txtLastModified)

.Parameters.Append .CreateParameter("@.RetCode", adInteger, adParamOutput)
.Parameters.Append .CreateParameter("@.RetResNum", adVarChar, adParamOutput, 15)

.Execute

End With

fOK = cmd.Parameters("@.RetCode")
strMsg = cmd.Parameters("@.RetResNum")|||Update for anyone interested. It seems that there was a type mismatch occurring. @.ResNum is a numeric value (data type int), and apparently in this line that caused a problem:

Select @.RetResNum = @.ResNum + ' Update Failed'

I assumed it would concatenate the two values. If I create a new varchar variable and populate it with the value of @.ResNum (converted to varchar) and use that, everything works fine. What threw me was that not only was that return parameter null, but the other one was too.

@@ROWCOUNT

How can I return the number of rows returned by a select statement such as
the following one that returns zero in @.Qty ?
Alter Procedure GetMonitors
(
@.Qty INT OUTPUT
)
As
SELECT a.Suburb, a.State FROM
(
SELECT Suburb, State FROM Monitors
UNION
SELECT Suburb, State FROM Properties
WHERE Delisted Is Null
) a
GROUP BY Suburb, State
SET @.Qty = @.@.ROWCOUNT
return
Thanks in advance...Below work just fine for me... Did you define the parameter as out when you
call the proc?
USE pubs
GO
ALTER PROC p
@.qty int OUT
AS
SELECT au_lname, au_fname FROM authors
SET @.qty = @.@.ROWCOUNT
GO
--Prove that it works:
DECLARE @.rows int
EXEC p @.qty = @.rows OUT
PRINT @.rows
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"McHenry" <mchenry@.mchenry.com> wrote in message
news:44350412$0$20647$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
> How can I return the number of rows returned by a select statement such as
the following one that
> returns zero in @.Qty ?
> Alter Procedure GetMonitors
> (
> @.Qty INT OUTPUT
> )
> As
> SELECT a.Suburb, a.State FROM
> (
> SELECT Suburb, State FROM Monitors
> UNION
> SELECT Suburb, State FROM Properties
> WHERE Delisted Is Null
> ) a
> GROUP BY Suburb, State
> SET @.Qty = @.@.ROWCOUNT
> return
>
> Thanks in advance...
>|||Thanks... my mistake !
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMCL8TXWGHA.1564@.TK2MSFTNGP03.phx.gbl...
> Below work just fine for me... Did you define the parameter as out when
> you call the proc?
> USE pubs
> GO
> ALTER PROC p
> @.qty int OUT
> AS
> SELECT au_lname, au_fname FROM authors
> SET @.qty = @.@.ROWCOUNT
> GO
> --Prove that it works:
> DECLARE @.rows int
> EXEC p @.qty = @.rows OUT
> PRINT @.rows
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "McHenry" <mchenry@.mchenry.com> wrote in message
> news:44350412$0$20647$5a62ac22@.per-qv1-newsreader-01.iinet.net.au...
>

@@rowcount

I want to do something like this.
SELECT *
FROM dbo.Contact [C]
-- If any rows are returned then return to front end.
IF @.@.rowcount > 0
Begin
GOTO FinishSuccessModule
End
-- But i had doubt here
We will never be sure that @.@.RowCount is giving the row count of the query
mentioned above since in a heavy concurrent scenario (as we have in our db
and its growing since our user base is also growing) before your query and
the checking of @.@.RowCount there might be another select fired by another
process or transaction which updates @.@.Rowcount which in turn give you
incorrect values.
how can we implement the same functionality using some other way, i dont
want If exists as there are around 6-7 conditions which are totally
select..if any one of them return record then come out else continue with
other sql st.
does @.@.rowcount create issues as mentioned above and second can we avoid
that using some other functionality.
Sanjay
Sanjay
No, rowcount is safe.
Bojidar Alexandrov
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:7276DE0A-F23B-403B-BEE5-C83E51FA71FC@.microsoft.com...
> I want to do something like this.
> SELECT *
> FROM dbo.Contact [C]
> -- If any rows are returned then return to front end.
> IF @.@.rowcount > 0
> Begin
> GOTO FinishSuccessModule
> End
> -- But i had doubt here
> We will never be sure that @.@.RowCount is giving the row count of the
query
> mentioned above since in a heavy concurrent scenario (as we have in our db
> and its growing since our user base is also growing) before your query and
> the checking of @.@.RowCount there might be another select fired by another
> process or transaction which updates @.@.Rowcount which in turn give you
> incorrect values.
> how can we implement the same functionality using some other way, i dont
> want If exists as there are around 6-7 conditions which are totally
> select..if any one of them return record then come out else continue with
> other sql st.
> does @.@.rowcount create issues as mentioned above and second can we avoid
> that using some other functionality.
> --
> Sanjay
> --
> Sanjay
|||If all you need to know is are "any" rows returned and don't care how many,
I would change the initial query to SELECT TOP 1 * FROM dbo.Contact (C). If
your contact table gets thousands of rows, why go thru and read them all
when you just need to know "do I get back at least 1 row"? And to be more
specific, you don't need a SELECT *. Pick one narrow column for your
select. Something like select top 1 ContactID from dbo.contact (c). This
is a lot more efficient.
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message
news:eWegGSspEHA.3244@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> No, rowcount is safe.
> Bojidar Alexandrov
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:7276DE0A-F23B-403B-BEE5-C83E51FA71FC@.microsoft.com...
> query
db[vbcol=seagreen]
and[vbcol=seagreen]
another[vbcol=seagreen]
with
>

@@rowcount

I want to do something like this.
SELECT *
FROM dbo.Contact [C]
-- If any rows are returned then return to front end.
IF @.@.rowcount > 0
Begin
GOTO FinishSuccessModule
End
-- But i had doubt here
We will never be sure that @.@.RowCount is giving the row count of the query
mentioned above since in a heavy concurrent scenario (as we have in our db
and its growing since our user base is also growing) before your query and
the checking of @.@.RowCount there might be another select fired by another
process or transaction which updates @.@.Rowcount which in turn give you
incorrect values.
how can we implement the same functionality using some other way, i dont
want If exists as there are around 6-7 conditions which are totally
select..if any one of them return record then come out else continue with
other sql st.
does @.@.rowcount create issues as mentioned above and second can we avoid
that using some other functionality.
--
Sanjay
--
SanjayNo, rowcount is safe.
Bojidar Alexandrov
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:7276DE0A-F23B-403B-BEE5-C83E51FA71FC@.microsoft.com...
> I want to do something like this.
> SELECT *
> FROM dbo.Contact [C]
> -- If any rows are returned then return to front end.
> IF @.@.rowcount > 0
> Begin
> GOTO FinishSuccessModule
> End
> -- But i had doubt here
> We will never be sure that @.@.RowCount is giving the row count of the
query
> mentioned above since in a heavy concurrent scenario (as we have in our db
> and its growing since our user base is also growing) before your query and
> the checking of @.@.RowCount there might be another select fired by another
> process or transaction which updates @.@.Rowcount which in turn give you
> incorrect values.
> how can we implement the same functionality using some other way, i dont
> want If exists as there are around 6-7 conditions which are totally
> select..if any one of them return record then come out else continue with
> other sql st.
> does @.@.rowcount create issues as mentioned above and second can we avoid
> that using some other functionality.
> --
> Sanjay
> --
> Sanjay|||If all you need to know is are "any" rows returned and don't care how many,
I would change the initial query to SELECT TOP 1 * FROM dbo.Contact (C). If
your contact table gets thousands of rows, why go thru and read them all
when you just need to know "do I get back at least 1 row"? And to be more
specific, you don't need a SELECT *. Pick one narrow column for your
select. Something like select top 1 ContactID from dbo.contact (c). This
is a lot more efficient.
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message
news:eWegGSspEHA.3244@.tk2msftngp13.phx.gbl...
> No, rowcount is safe.
> Bojidar Alexandrov
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:7276DE0A-F23B-403B-BEE5-C83E51FA71FC@.microsoft.com...
> > I want to do something like this.
> > SELECT *
> > FROM dbo.Contact [C]
> > -- If any rows are returned then return to front end.
> > IF @.@.rowcount > 0
> > Begin
> > GOTO FinishSuccessModule
> > End
> > -- But i had doubt here
> > We will never be sure that @.@.RowCount is giving the row count of the
> query
> > mentioned above since in a heavy concurrent scenario (as we have in our
db
> > and its growing since our user base is also growing) before your query
and
> > the checking of @.@.RowCount there might be another select fired by
another
> > process or transaction which updates @.@.Rowcount which in turn give you
> > incorrect values.
> >
> > how can we implement the same functionality using some other way, i dont
> > want If exists as there are around 6-7 conditions which are totally
> > select..if any one of them return record then come out else continue
with
> > other sql st.
> >
> > does @.@.rowcount create issues as mentioned above and second can we avoid
> > that using some other functionality.
> > --
> > Sanjay
> > --
> > Sanjay
>