Odd issue with bound columns when fetching data.

I'm using ODBC 3.0 in code written in C.

I have an service that connects using a system DSN using the SQL Server 2000 driver. On my development system with SQL Server 2005 Express installed, the queries work fine. I prepare a statement and then bind the columns that will be in the result set. On my system, I get all the data as it should be but on a live system, I do not get the proper data for the last three rows. I don't get any error messages from the query or the column binding and there are also no extra information messages that I can retrieve.

The table is something like the following. The names are changed to protect the guilty.

CREATE TABLE DOWNLOAD

(

FIELD_1 NUMERIC( 6,0 ),

ID CHAR( 15),

FNAME( 30 ),

LNAME( 40 ),

CLIENT_ID NUMERIC( 9,0),

CLIENT_NAME CHAR( 50 ),

CODE NUMERIC( 4,0 ),

PHONE CHAR( 10 )

)

go

On my system, running the exact same binary code as the client machine, I get all of the data from all of the columns as it should be. The problem is when I run on a client's system, the CLIENT_NAME, CODE, and PHONE columns return null values even when there is data there. On my development system the SQL Server instance runs on the same machine. On the client's sytem I am connecting to a remote instance of SQL Server 2000 on another system on the network.

My quandry is what could be different between the two systems that is causing me the problems?

Can you ran SQL Profiler on both machines and see if there is any difference?|||I've turned the logging on at the client side. The logs don't show any errors and it looks like the column binding in SQLBindCol is correct. I haven't seen the server side logs but the gurus on the server side say that they can't see anything wrong there. I've played around with the SQL statement and tried using CONVERT() to change the CHAR to VARCHAR but that doesn't seem to help.

I'm at a total loss why the same code running on my development system runs fine but fails on another system. My guess is that there is some configuration difference but I don't know what that is because I am using the samve version of the SQL Server 2000 ODBC driver on both machines. Its not like there is anything odd or non standard in the table definition either.

|||Just as an update and possibly more information.

I've got it working now but I don't like the solution. What I ended up doing is not binding the columns and calling SQLGetData() after the fetch to get the column data. I don't like this because it costs me about 500 ms for the each time I call the function.

I'd still like to find a solution for why the bound columns don't work.
|||1) I would still recommend you to run SQL Profiler on the servers.
2) How do you bind exactly? Is it possible for you to write (and post the source here) a small ODBC application which demonstrates this problem?|||This is the code I use to bind the columns:

BOOL BindRecord( SQLHSTMT hStmt, PACCDATA_FIELD *pRecord, PINT piRecCount )
{
BOOL bRval = FALSE;
PACCDATA_FIELD pField;
SQLRETURN r;
INT iCnt;
CHAR szBuf[128];
SQLINTEGER len;

if( pRecord && *pRecord )
{
for( iCnt = 0; iCnt < *piRecCount; iCnt++ )
{
pField = *pRecord + iCnt;
len = pField->nLen;

switch( pField->iType )
{
case SQL_CHAR:
r = SQLBindCol( hStmt, pField->iColNum, SQL_C_CHAR, pField->pcVal, pField->nDataSize, &len );
break;

case SQL_DATETIME:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->ucVal, pField->nDataSize, &len );
break;

case SQL_DECIMAL:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->ucVal, pField->nDataSize, &len );
break;

case SQL_NUMERIC:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->lVal, pField->nDataSize, &len );
break;

case SQL_INTEGER:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->lVal, pField->nDataSize, &len );
break;

case SQL_SMALLINT:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->sVal, pField->nDataSize, &len );
break;

case SQL_DOUBLE:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->dVal, pField->nDataSize, &len );
break;

case SQL_FLOAT:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->dVal, pField->nDataSize, &len );
break;

case SQL_REAL:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->fVal, pField->nDataSize, &len );
break;

case SQL_VARCHAR:
r = SQLBindCol( hStmt, pField->iColNum, SQL_C_CHAR, pField->pvcVal, pField->nDataSize + 1, &len );
break;

case SQL_UNKNOWN_TYPE:
ST_WriteLog( "st_ctimpact", "BindRecord", "Field data type is SQL_UNKNOWN_TYPE", ST_LOG_DEBUG );
default:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, pField->ptr, pField->nDataSize, &len );
break;
}

if( r == SQL_SUCCESS_WITH_INFO )
{
lstrcpy( szBuf, "Field name: " );
lstrcat( szBuf, pField->szColName );
ST_WriteLog( "st_AcceleratedData", "BindRecord", szBuf, ST_LOG_WARNING );
ShowSQLMessages( "BindRecord", SQL_HANDLE_STMT, hStmt );
bRval = TRUE;
}
else if( r != SQL_SUCCESS )
{
lstrcpy( szBuf, "Field name: " );
lstrcat( szBuf, pField->szColName );
ST_WriteLog( "st_AcceleratedData", "BindRecord", szBuf, ST_LOG_WARNING );
ShowSQLErrors( "BindRecord", SQL_HANDLE_STMT, hStmt );
}
else
bRval = TRUE;
}
}
return( bRval );
}

A key to remember is that other columns that are bound in this way, and have the same data types, are returning data just fine. I've looked at the logs on the client side where this code works, and there are no errors and the prameters look correct as far as I can tell. Also this code works as advertized on my system but not on the live system. Right now, I don't think my customer will be willing to let me mess around on their system trying to come up with an application that would recreate the problem. Also note that I cannot recreate the problem on my own system. I had hoped that it would be broken on my system so I could debug and find out what was the problem but because it works, I can't.
|||I do not think you have to mess around with your customer system.
I would suggest you write a straightforward ODBC application from scratch just going against this very table.
Use some static arrays and bind directly with SQLBindCol, so to exclude any dependecies on how PACCDATA_FIELD or other parts of your custom code are written.
Then when you are sure it works as supposed on your machine, try it against your customer's.|||Then I guess you didn't read the thread all that well. I don't have to write a test app. The live code works fine on my system on the same table definition in the same database name as the live system. The problem is when I deployed the code, some of the bound columns don't return data. Since I can't recreate the problem on my system, I'm at a loss as to what the problem is.
|||I'm not sure if it is possilbe to resolve your issue without actual experimentation. Your system is not identical to the live system, is it? So your argument that the code works on your system is not helpful to you. If you really want to find out where the problem is you might try and start narrowing down on the issue. That is what I suggested to you.|||It just seemed to me that you asking me to make a test case that is exactly what I have running. I think I might have it working now and it must have something to do with my adding RTRIM() and CONVERT() functions in the select statement. I removed them and it seems to work better now.

Odd issue with bound columns when fetching data.

I'm using ODBC 3.0 in code written in C.

I have an service that connects using a system DSN using the SQL Server 2000 driver. On my development system with SQL Server 2005 Express installed, the queries work fine. I prepare a statement and then bind the columns that will be in the result set. On my system, I get all the data as it should be but on a live system, I do not get the proper data for the last three rows. I don't get any error messages from the query or the column binding and there are also no extra information messages that I can retrieve.

The table is something like the following. The names are changed to protect the guilty.

CREATE TABLE DOWNLOAD

(

FIELD_1 NUMERIC( 6,0 ),

ID CHAR( 15),

FNAME( 30 ),

LNAME( 40 ),

CLIENT_ID NUMERIC( 9,0),

CLIENT_NAME CHAR( 50 ),

CODE NUMERIC( 4,0 ),

PHONE CHAR( 10 )

)

go

On my system, running the exact same binary code as the client machine, I get all of the data from all of the columns as it should be. The problem is when I run on a client's system, the CLIENT_NAME, CODE, and PHONE columns return null values even when there is data there. On my development system the SQL Server instance runs on the same machine. On the client's sytem I am connecting to a remote instance of SQL Server 2000 on another system on the network.

My quandry is what could be different between the two systems that is causing me the problems?

Can you ran SQL Profiler on both machines and see if there is any difference?|||I've turned the logging on at the client side. The logs don't show any errors and it looks like the column binding in SQLBindCol is correct. I haven't seen the server side logs but the gurus on the server side say that they can't see anything wrong there. I've played around with the SQL statement and tried using CONVERT() to change the CHAR to VARCHAR but that doesn't seem to help.

I'm at a total loss why the same code running on my development system runs fine but fails on another system. My guess is that there is some configuration difference but I don't know what that is because I am using the samve version of the SQL Server 2000 ODBC driver on both machines. Its not like there is anything odd or non standard in the table definition either.

|||Just as an update and possibly more information.

I've got it working now but I don't like the solution. What I ended up doing is not binding the columns and calling SQLGetData() after the fetch to get the column data. I don't like this because it costs me about 500 ms for the each time I call the function.

I'd still like to find a solution for why the bound columns don't work.
|||1) I would still recommend you to run SQL Profiler on the servers.
2) How do you bind exactly? Is it possible for you to write (and post the source here) a small ODBC application which demonstrates this problem?|||This is the code I use to bind the columns:

BOOL BindRecord( SQLHSTMT hStmt, PACCDATA_FIELD *pRecord, PINT piRecCount )
{
BOOL bRval = FALSE;
PACCDATA_FIELD pField;
SQLRETURN r;
INT iCnt;
CHAR szBuf[128];
SQLINTEGER len;

if( pRecord && *pRecord )
{
for( iCnt = 0; iCnt < *piRecCount; iCnt++ )
{
pField = *pRecord + iCnt;
len = pField->nLen;

switch( pField->iType )
{
case SQL_CHAR:
r = SQLBindCol( hStmt, pField->iColNum, SQL_C_CHAR, pField->pcVal, pField->nDataSize, &len );
break;

case SQL_DATETIME:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->ucVal, pField->nDataSize, &len );
break;

case SQL_DECIMAL:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->ucVal, pField->nDataSize, &len );
break;

case SQL_NUMERIC:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->lVal, pField->nDataSize, &len );
break;

case SQL_INTEGER:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->lVal, pField->nDataSize, &len );
break;

case SQL_SMALLINT:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->sVal, pField->nDataSize, &len );
break;

case SQL_DOUBLE:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->dVal, pField->nDataSize, &len );
break;

case SQL_FLOAT:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->dVal, pField->nDataSize, &len );
break;

case SQL_REAL:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->fVal, pField->nDataSize, &len );
break;

case SQL_VARCHAR:
r = SQLBindCol( hStmt, pField->iColNum, SQL_C_CHAR, pField->pvcVal, pField->nDataSize + 1, &len );
break;

case SQL_UNKNOWN_TYPE:
ST_WriteLog( "st_ctimpact", "BindRecord", "Field data type is SQL_UNKNOWN_TYPE", ST_LOG_DEBUG );
default:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, pField->ptr, pField->nDataSize, &len );
break;
}

if( r == SQL_SUCCESS_WITH_INFO )
{
lstrcpy( szBuf, "Field name: " );
lstrcat( szBuf, pField->szColName );
ST_WriteLog( "st_AcceleratedData", "BindRecord", szBuf, ST_LOG_WARNING );
ShowSQLMessages( "BindRecord", SQL_HANDLE_STMT, hStmt );
bRval = TRUE;
}
else if( r != SQL_SUCCESS )
{
lstrcpy( szBuf, "Field name: " );
lstrcat( szBuf, pField->szColName );
ST_WriteLog( "st_AcceleratedData", "BindRecord", szBuf, ST_LOG_WARNING );
ShowSQLErrors( "BindRecord", SQL_HANDLE_STMT, hStmt );
}
else
bRval = TRUE;
}
}
return( bRval );
}

A key to remember is that other columns that are bound in this way, and have the same data types, are returning data just fine. I've looked at the logs on the client side where this code works, and there are no errors and the prameters look correct as far as I can tell. Also this code works as advertized on my system but not on the live system. Right now, I don't think my customer will be willing to let me mess around on their system trying to come up with an application that would recreate the problem. Also note that I cannot recreate the problem on my own system. I had hoped that it would be broken on my system so I could debug and find out what was the problem but because it works, I can't.
|||I do not think you have to mess around with your customer system.
I would suggest you write a straightforward ODBC application from scratch just going against this very table.
Use some static arrays and bind directly with SQLBindCol, so to exclude any dependecies on how PACCDATA_FIELD or other parts of your custom code are written.
Then when you are sure it works as supposed on your machine, try it against your customer's.|||Then I guess you didn't read the thread all that well. I don't have to write a test app. The live code works fine on my system on the same table definition in the same database name as the live system. The problem is when I deployed the code, some of the bound columns don't return data. Since I can't recreate the problem on my system, I'm at a loss as to what the problem is.
|||I'm not sure if it is possilbe to resolve your issue without actual experimentation. Your system is not identical to the live system, is it? So your argument that the code works on your system is not helpful to you. If you really want to find out where the problem is you might try and start narrowing down on the issue. That is what I suggested to you.|||It just seemed to me that you asking me to make a test case that is exactly what I have running. I think I might have it working now and it must have something to do with my adding RTRIM() and CONVERT() functions in the select statement. I removed them and it seems to work better now.

Odd issue with bound columns when fetching data.

I'm using ODBC 3.0 in code written in C.

I have an service that connects using a system DSN using the SQL Server 2000 driver. On my development system with SQL Server 2005 Express installed, the queries work fine. I prepare a statement and then bind the columns that will be in the result set. On my system, I get all the data as it should be but on a live system, I do not get the proper data for the last three rows. I don't get any error messages from the query or the column binding and there are also no extra information messages that I can retrieve.

The table is something like the following. The names are changed to protect the guilty.

CREATE TABLE DOWNLOAD

(

FIELD_1 NUMERIC( 6,0 ),

ID CHAR( 15),

FNAME( 30 ),

LNAME( 40 ),

CLIENT_ID NUMERIC( 9,0),

CLIENT_NAME CHAR( 50 ),

CODE NUMERIC( 4,0 ),

PHONE CHAR( 10 )

)

go

On my system, running the exact same binary code as the client machine, I get all of the data from all of the columns as it should be. The problem is when I run on a client's system, the CLIENT_NAME, CODE, and PHONE columns return null values even when there is data there. On my development system the SQL Server instance runs on the same machine. On the client's sytem I am connecting to a remote instance of SQL Server 2000 on another system on the network.

My quandry is what could be different between the two systems that is causing me the problems?

Can you ran SQL Profiler on both machines and see if there is any difference?|||I've turned the logging on at the client side. The logs don't show any errors and it looks like the column binding in SQLBindCol is correct. I haven't seen the server side logs but the gurus on the server side say that they can't see anything wrong there. I've played around with the SQL statement and tried using CONVERT() to change the CHAR to VARCHAR but that doesn't seem to help.

I'm at a total loss why the same code running on my development system runs fine but fails on another system. My guess is that there is some configuration difference but I don't know what that is because I am using the samve version of the SQL Server 2000 ODBC driver on both machines. Its not like there is anything odd or non standard in the table definition either.

|||Just as an update and possibly more information.

I've got it working now but I don't like the solution. What I ended up doing is not binding the columns and calling SQLGetData() after the fetch to get the column data. I don't like this because it costs me about 500 ms for the each time I call the function.

I'd still like to find a solution for why the bound columns don't work.
|||1) I would still recommend you to run SQL Profiler on the servers.
2) How do you bind exactly? Is it possible for you to write (and post the source here) a small ODBC application which demonstrates this problem?|||This is the code I use to bind the columns:

BOOL BindRecord( SQLHSTMT hStmt, PACCDATA_FIELD *pRecord, PINT piRecCount )
{
BOOL bRval = FALSE;
PACCDATA_FIELD pField;
SQLRETURN r;
INT iCnt;
CHAR szBuf[128];
SQLINTEGER len;

if( pRecord && *pRecord )
{
for( iCnt = 0; iCnt < *piRecCount; iCnt++ )
{
pField = *pRecord + iCnt;
len = pField->nLen;

switch( pField->iType )
{
case SQL_CHAR:
r = SQLBindCol( hStmt, pField->iColNum, SQL_C_CHAR, pField->pcVal, pField->nDataSize, &len );
break;

case SQL_DATETIME:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->ucVal, pField->nDataSize, &len );
break;

case SQL_DECIMAL:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->ucVal, pField->nDataSize, &len );
break;

case SQL_NUMERIC:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->lVal, pField->nDataSize, &len );
break;

case SQL_INTEGER:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->lVal, pField->nDataSize, &len );
break;

case SQL_SMALLINT:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->sVal, pField->nDataSize, &len );
break;

case SQL_DOUBLE:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->dVal, pField->nDataSize, &len );
break;

case SQL_FLOAT:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->dVal, pField->nDataSize, &len );
break;

case SQL_REAL:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, &pField->fVal, pField->nDataSize, &len );
break;

case SQL_VARCHAR:
r = SQLBindCol( hStmt, pField->iColNum, SQL_C_CHAR, pField->pvcVal, pField->nDataSize + 1, &len );
break;

case SQL_UNKNOWN_TYPE:
ST_WriteLog( "st_ctimpact", "BindRecord", "Field data type is SQL_UNKNOWN_TYPE", ST_LOG_DEBUG );
default:
r = SQLBindCol( hStmt, pField->iColNum, pField->iType, pField->ptr, pField->nDataSize, &len );
break;
}

if( r == SQL_SUCCESS_WITH_INFO )
{
lstrcpy( szBuf, "Field name: " );
lstrcat( szBuf, pField->szColName );
ST_WriteLog( "st_AcceleratedData", "BindRecord", szBuf, ST_LOG_WARNING );
ShowSQLMessages( "BindRecord", SQL_HANDLE_STMT, hStmt );
bRval = TRUE;
}
else if( r != SQL_SUCCESS )
{
lstrcpy( szBuf, "Field name: " );
lstrcat( szBuf, pField->szColName );
ST_WriteLog( "st_AcceleratedData", "BindRecord", szBuf, ST_LOG_WARNING );
ShowSQLErrors( "BindRecord", SQL_HANDLE_STMT, hStmt );
}
else
bRval = TRUE;
}
}
return( bRval );
}

A key to remember is that other columns that are bound in this way, and have the same data types, are returning data just fine. I've looked at the logs on the client side where this code works, and there are no errors and the prameters look correct as far as I can tell. Also this code works as advertized on my system but not on the live system. Right now, I don't think my customer will be willing to let me mess around on their system trying to come up with an application that would recreate the problem. Also note that I cannot recreate the problem on my own system. I had hoped that it would be broken on my system so I could debug and find out what was the problem but because it works, I can't.
|||I do not think you have to mess around with your customer system.
I would suggest you write a straightforward ODBC application from scratch just going against this very table.
Use some static arrays and bind directly with SQLBindCol, so to exclude any dependecies on how PACCDATA_FIELD or other parts of your custom code are written.
Then when you are sure it works as supposed on your machine, try it against your customer's.|||Then I guess you didn't read the thread all that well. I don't have to write a test app. The live code works fine on my system on the same table definition in the same database name as the live system. The problem is when I deployed the code, some of the bound columns don't return data. Since I can't recreate the problem on my system, I'm at a loss as to what the problem is.
|||I'm not sure if it is possilbe to resolve your issue without actual experimentation. Your system is not identical to the live system, is it? So your argument that the code works on your system is not helpful to you. If you really want to find out where the problem is you might try and start narrowing down on the issue. That is what I suggested to you.|||It just seemed to me that you asking me to make a test case that is exactly what I have running. I think I might have it working now and it must have something to do with my adding RTRIM() and CONVERT() functions in the select statement. I removed them and it seems to work better now.

odd deadlocking behaviour

I am running SQL Server 2000 SP3a on a single processor computer and:
I have a table with the following columns
CREATE TABLE [dbo].[SP148_JOB_AHO_PERIODS] (
[JOB_NO] [int] NOT NULL ,
[OVERRIDDEN_PERIOD_START] [datetime] NULL ,
[OVERRIDDEN_PERIOD_END] [datetime] NULL
) ON [PRIMARY]
GO
That has the following data:
1,06/01/2003 17:00:00,NULL
2,NULL,13/01/2003 08:00:00
2,13/01/2003 17:00:00,NULL
3,NULL,20/01/2003 08:00:00
3,20/01/2003 17:00:00,NULL
4,NULL, 27/01/2003 08:00:00
4,27/01/2003 17:00:00,NULL
If I now try deleting these rows from two database sessions in the order as
seen below I end up with a deadlock:
Firstly on SPID 56:
set implicit_transactions on
delete from SP148_JOB_AHO_PERIODS where job_no = 2
Secondly on SPID 57:
set implicit_transactions on
delete from SP148_JOB_AHO_PERIODS where job_no = 1
... This then blocks ? Which I am not sure about.
Thirdly back on SPID 56:
delete from SP148_JOB_AHO_PERIODS where job_no = 6
Creates a deadlock victimising SPID 57.
I accept that this table does not have a primary key but when I add one it
still deadlocks.
Why, on step 2, does SPID 57 lock when I delete the row where job_no = 1?
Why does a deadlock occur when SPID 56 subsequently deletes the row where
job_no is 6 i.e. no rows?
When I looked at sp_lock output it appeared that both sessions were waiting
on the same RID, does this mean that a RID is not necessarily 1 database
table row?
Thanks- What kind of lock triggered the deadlock? row, index, page, extent, table,
database
- Do you have an index by "job_no"?
AMB
"Tony Jones" wrote:
> I am running SQL Server 2000 SP3a on a single processor computer and:
> I have a table with the following columns
> CREATE TABLE [dbo].[SP148_JOB_AHO_PERIODS] (
> [JOB_NO] [int] NOT NULL ,
> [OVERRIDDEN_PERIOD_START] [datetime] NULL ,
> [OVERRIDDEN_PERIOD_END] [datetime] NULL
> ) ON [PRIMARY]
> GO
> That has the following data:
> 1,06/01/2003 17:00:00,NULL
> 2,NULL,13/01/2003 08:00:00
> 2,13/01/2003 17:00:00,NULL
> 3,NULL,20/01/2003 08:00:00
> 3,20/01/2003 17:00:00,NULL
> 4,NULL, 27/01/2003 08:00:00
> 4,27/01/2003 17:00:00,NULL
> If I now try deleting these rows from two database sessions in the order as
> seen below I end up with a deadlock:
> Firstly on SPID 56:
> set implicit_transactions on
> delete from SP148_JOB_AHO_PERIODS where job_no = 2
> Secondly on SPID 57:
> set implicit_transactions on
> delete from SP148_JOB_AHO_PERIODS where job_no = 1
> ... This then blocks ? Which I am not sure about.
> Thirdly back on SPID 56:
> delete from SP148_JOB_AHO_PERIODS where job_no = 6
> Creates a deadlock victimising SPID 57.
> I accept that this table does not have a primary key but when I add one it
> still deadlocks.
> Why, on step 2, does SPID 57 lock when I delete the row where job_no = 1?
> Why does a deadlock occur when SPID 56 subsequently deletes the row where
> job_no is 6 i.e. no rows?
> When I looked at sp_lock output it appeared that both sessions were waiting
> on the same RID, does this mean that a RID is not necessarily 1 database
> table row?
> Thanks|||Turn on trace flag 1204 and -1. Then your answers will be in the errorlog.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5E921D65-22E3-40D0-AE4C-BEBF76DF8FC7@.microsoft.com...
>- What kind of lock triggered the deadlock? row, index, page, extent,
>table,
> database
> - Do you have an index by "job_no"?
> AMB
> "Tony Jones" wrote:
>> I am running SQL Server 2000 SP3a on a single processor computer and:
>> I have a table with the following columns
>> CREATE TABLE [dbo].[SP148_JOB_AHO_PERIODS] (
>> [JOB_NO] [int] NOT NULL ,
>> [OVERRIDDEN_PERIOD_START] [datetime] NULL ,
>> [OVERRIDDEN_PERIOD_END] [datetime] NULL
>> ) ON [PRIMARY]
>> GO
>> That has the following data:
>> 1,06/01/2003 17:00:00,NULL
>> 2,NULL,13/01/2003 08:00:00
>> 2,13/01/2003 17:00:00,NULL
>> 3,NULL,20/01/2003 08:00:00
>> 3,20/01/2003 17:00:00,NULL
>> 4,NULL, 27/01/2003 08:00:00
>> 4,27/01/2003 17:00:00,NULL
>> If I now try deleting these rows from two database sessions in the order
>> as
>> seen below I end up with a deadlock:
>> Firstly on SPID 56:
>> set implicit_transactions on
>> delete from SP148_JOB_AHO_PERIODS where job_no = 2
>> Secondly on SPID 57:
>> set implicit_transactions on
>> delete from SP148_JOB_AHO_PERIODS where job_no = 1
>> ... This then blocks ? Which I am not sure about.
>> Thirdly back on SPID 56:
>> delete from SP148_JOB_AHO_PERIODS where job_no = 6
>> Creates a deadlock victimising SPID 57.
>> I accept that this table does not have a primary key but when I add one
>> it
>> still deadlocks.
>> Why, on step 2, does SPID 57 lock when I delete the row where job_no = 1?
>> Why does a deadlock occur when SPID 56 subsequently deletes the row where
>> job_no is 6 i.e. no rows?
>> When I looked at sp_lock output it appeared that both sessions were
>> waiting
>> on the same RID, does this mean that a RID is not necessarily 1 database
>> table row?
>> Thanks|||The blocked SPID 57 is waiting on a RID (so a row lock) both SPIDs are
waiting on the same rid i.e. the same fileid:pageid:slot, hence the question
is the RID locking more than one row?
No I have not had a index on job_no I have tried a primary kry on a new
column defined to be the Identity column. This produced the same deadlock
behaviour.
"Alejandro Mesa" wrote:
> - What kind of lock triggered the deadlock? row, index, page, extent, table,
> database
> - Do you have an index by "job_no"?
> AMB
> "Tony Jones" wrote:
> > I am running SQL Server 2000 SP3a on a single processor computer and:
> >
> > I have a table with the following columns
> > CREATE TABLE [dbo].[SP148_JOB_AHO_PERIODS] (
> > [JOB_NO] [int] NOT NULL ,
> > [OVERRIDDEN_PERIOD_START] [datetime] NULL ,
> > [OVERRIDDEN_PERIOD_END] [datetime] NULL
> > ) ON [PRIMARY]
> > GO
> >
> > That has the following data:
> >
> > 1,06/01/2003 17:00:00,NULL
> > 2,NULL,13/01/2003 08:00:00
> > 2,13/01/2003 17:00:00,NULL
> > 3,NULL,20/01/2003 08:00:00
> > 3,20/01/2003 17:00:00,NULL
> > 4,NULL, 27/01/2003 08:00:00
> > 4,27/01/2003 17:00:00,NULL
> >
> > If I now try deleting these rows from two database sessions in the order as
> > seen below I end up with a deadlock:
> >
> > Firstly on SPID 56:
> > set implicit_transactions on
> > delete from SP148_JOB_AHO_PERIODS where job_no = 2
> >
> > Secondly on SPID 57:
> > set implicit_transactions on
> > delete from SP148_JOB_AHO_PERIODS where job_no = 1
> > ... This then blocks ? Which I am not sure about.
> >
> > Thirdly back on SPID 56:
> > delete from SP148_JOB_AHO_PERIODS where job_no = 6
> >
> > Creates a deadlock victimising SPID 57.
> >
> > I accept that this table does not have a primary key but when I add one it
> > still deadlocks.
> >
> > Why, on step 2, does SPID 57 lock when I delete the row where job_no = 1?
> > Why does a deadlock occur when SPID 56 subsequently deletes the row where
> > job_no is 6 i.e. no rows?
> >
> > When I looked at sp_lock output it appeared that both sessions were waiting
> > on the same RID, does this mean that a RID is not necessarily 1 database
> > table row?
> >
> > Thanks

ODBC SQLFetch problem

Hi,

I have ODBC code reading data from a XLS file.

It opens the file, reads the sheets and columns available, then presents a dialog to allow the user to select which columns are to be used to load the data.

All of this works, almost.

It is a simple "Select A,B,C FROM Sheet1" using SQLExecDirect

When using SQLFetch it always skips the first row of data.

Thanks, any ideas would be great.

Keith

Keith,

If you are using Fast Forward Only AutoFetch cursors, then as an optimization the driver might fill the SQL_ATTR_ROW_ARRAY_SIZE number of rows to your application buffers as part of SQLExecDirect execution. I do know that this is indeed the case with SQL Native Client (SQLNCLI) ODBC driver, not sure about the excel driver though. Can you check to see if that is the case in your scenario. Hope this helps.

Thanks

Waseem

|||

Thanks for the response,

I have played with different row sizes does not change the results.

I have also tried different files with different row counts, It always miss only the first row.

I have also added blank rows above the first and it still misses the first row of data.

Keith

|||

Does SQLExecDirect fetch the 1st row for you? Can you check the bind buffers to see they are filled with column data from the 1st row after SQLExecDirect before calling SQLFetch?

One more thing you might try is to move SQLBindCol after SQLExecDirect to see if that changes anything.

|||

Hi,

SQLExecDirect does not change the bind buffers.

Have moved the SQLBindCol calls before and and after the SQLExecDirect call both have the same effect. I also used SQLGetData instead of SQLBindCol and they also always miss the first row of data.

Keith

|||

Turns out that the Excel driver expects the first row to be the column names. There is supposed to be an option to turn this behavior off, but it has not worked in the past. I am not sure if it has been fixed. Here is the link to more information. http://support.microsoft.com/kb/288343/

If you have a row with column names in it, you can just include that in your range and you will be all set, otherwise, you should try the option, if that doesn't work, use the Jet engine as suggested in the above link.

Thanks

|||Thank you, adding headers does make it read all the lines. I had tried blank rows that has no effect. This is a reader for a user supplied file I have no control over.

ODBC SQLFetch problem

Hi,

I have ODBC code reading data from a XLS file.

It opens the file, reads the sheets and columns available, then presents a dialog to allow the user to select which columns are to be used to load the data.

All of this works, almost.

It is a simple "Select A,B,C FROM Sheet1" using SQLExecDirect

When using SQLFetch it always skips the first row of data.

Thanks, any ideas would be great.

Keith

Keith,

If you are using Fast Forward Only AutoFetch cursors, then as an optimization the driver might fill the SQL_ATTR_ROW_ARRAY_SIZE number of rows to your application buffers as part of SQLExecDirect execution. I do know that this is indeed the case with SQL Native Client (SQLNCLI) ODBC driver, not sure about the excel driver though. Can you check to see if that is the case in your scenario. Hope this helps.

Thanks

Waseem

|||

Thanks for the response,

I have played with different row sizes does not change the results.

I have also tried different files with different row counts, It always miss only the first row.

I have also added blank rows above the first and it still misses the first row of data.

Keith

|||

Does SQLExecDirect fetch the 1st row for you? Can you check the bind buffers to see they are filled with column data from the 1st row after SQLExecDirect before calling SQLFetch?

One more thing you might try is to move SQLBindCol after SQLExecDirect to see if that changes anything.

|||

Hi,

SQLExecDirect does not change the bind buffers.

Have moved the SQLBindCol calls before and and after the SQLExecDirect call both have the same effect. I also used SQLGetData instead of SQLBindCol and they also always miss the first row of data.

Keith

|||

Turns out that the Excel driver expects the first row to be the column names. There is supposed to be an option to turn this behavior off, but it has not worked in the past. I am not sure if it has been fixed. Here is the link to more information. http://support.microsoft.com/kb/288343/

If you have a row with column names in it, you can just include that in your range and you will be all set, otherwise, you should try the option, if that doesn't work, use the Jet engine as suggested in the above link.

Thanks

|||Thank you, adding headers does make it read all the lines. I had tried blank rows that has no effect. This is a reader for a user supplied file I have no control over.