Table Adapter generates bad SQL under load

I have an application (ASP.NET 2.0/SQL Server 2005) which makes heavy use of
table adapters for pulling records from SQL. Under heavy load, we get a lot
of SQL Server Timeout errors. We have run a trace on SQL Server and it shows
that several of the SQL statements being passed into SQL Server, from the
Table Adapters, have bad SQL.

For example, here is the SQL in one of the table adapters

SELECT HomeMsgID, messageName, messageHTML, messageText, populationID
FROM MyUCR_HomeMessages
WHERE (populationID IN
(SELECT populationID
FROM MyUCR_Population_CPID AS
MyUCR_Population_CPID_1
WHERE (CPID = @CPID))) AND (isVisible = 1)
AND (showDate <= @showDate) AND (removeDate >= @removeDate)

I call it with the following:

DateTime showDate = DateTime.Today;
DateTime removeDate = DateTime.Today;

myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessages TableAdapter ta = new
myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessages TableAdapter();
myUCR_HomePageMsgs.MyUCR_HomeMessagesDataTable dt = new
myUCR_HomePageMsgs.MyUCR_HomeMessagesDataTable();

ta.FillByCPID(dt, showDate, removeDate, CPID);

What the SQL trace shows, when it fails, is this (notice the extra single
quotes around the showDate, removeDate parameters):
E000
exec sp_executesql N'SELECT HomeMsgID, messageName, messageHTML,
messageText, populationID
FROM MyUCR_HomeMessages
WHERE (populationID IN
(SELECT populationID
FROM MyUCR_Population_CPID AS
MyUCR_Population_CPID_1
WHERE (CPID = @CPID))) AND (isVisible = 1)
AND (showDate <= @showDate) AND (removeDate >= @removeDate)',N'@showDate
datetime,@removeDate datetime,@CPID int',@showDate=''2007-02-05
00:00:00:000'',@removeDate=''2007-02-05 00:00:00:000'',@CPID=3071225
1 [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2007'.

I recreated the SQL to use a stored procedure, and got a similar error:

E000 exec dbo.spFillHomeMsgByCPID @showDate=''2007-02-05
00:00:00:000'',@removeDate=''2007-02-05 00:00:00:000'',@CPID=3008195
5 [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2007'.

However, if I create dynamic SQL and use the following, there are no errors.

string mySql = string.Empty;
mySql = "SELECT HomeMsgID, messageName, messageHTML, messageText,
populationID FROM MyUCR_HomeMessages WHERE (populationID IN (SELECT
populationID FROM MyUCR_Population_CPID AS MyUCR_Population_CPID_1 WHERE
(CPID = " + CPID + "))) AND (isVisible = 1) AND (showDate <= '" + showDate +
"') AND (removeDate >= '" + removeDate + "')"; SqlDataAdapter adapter = new
SqlDataAdapter(mySql,
ConfigurationManager.ConnectionStrings["MyUCR2007ConnectionString"].ToString());
DataSet RecordCount = new DataSet();
adapter.Fill(RecordCount);
DataTable testDT = RecordCount.Tables[0];

I am using VSTS with the Service Pack installed. SQL 2005 is running on W2K3
Enterprise, fully patched

Perhaps this isn't the correct forum? Does anyone have an idea on where to
post this message?

Thanks,
James
--
James Johnson

"JamesIEDOTNET" wrote:

Quote:

I have an application (ASP.NET 2.0/SQL Server 2005) which makes heavy use of
table adapters for pulling records from SQL. Under heavy load, we get a lot
of SQL Server Timeout errors. We have run a trace on SQL Server and it shows
that several of the SQL statements being passed into SQL Server, from the
Table Adapters, have bad SQL.
>
For example, here is the SQL in one of the table adapters
>
SELECT HomeMsgID, messageName, messageHTML, messageText, populationID
FROM MyUCR_HomeMessages
WHERE (populationID IN
(SELECT populationID
FROM MyUCR_Population_CPID AS
MyUCR_Population_CPID_1
WHERE (CPID = @CPID))) AND (isVisible = 1)
AND (showDate <= @showDate) AND (removeDate >= @removeDate)
>
I call it with the following:
>
DateTime showDate = DateTime.Today;
DateTime removeDate = DateTime.Today;
>
myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessages TableAdapter ta = new
myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessages TableAdapter();
myUCR_HomePageMsgs.MyUCR_HomeMessagesDataTable dt = new
myUCR_HomePageMsgs.MyUCR_HomeMessagesDataTable();
>
ta.FillByCPID(dt, showDate, removeDate, CPID);
>
What the SQL trace shows, when it fails, is this (notice the extra single
quotes around the showDate, removeDate parameters):
E000
exec sp_executesql N'SELECT HomeMsgID, messageName, messageHTML,
messageText, populationID
FROM MyUCR_HomeMessages
WHERE (populationID IN
(SELECT populationID
FROM MyUCR_Population_CPID AS
MyUCR_Population_CPID_1
WHERE (CPID = @CPID))) AND (isVisible = 1)
AND (showDate <= @showDate) AND (removeDate >= @removeDate)',N'@showDate
datetime,@removeDate datetime,@CPID int',@showDate=''2007-02-05
00:00:00:000'',@removeDate=''2007-02-05 00:00:00:000'',@CPID=3071225
1 [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2007'.
>
I recreated the SQL to use a stored procedure, and got a similar error:
>
E000 exec dbo.spFillHomeMsgByCPID @showDate=''2007-02-05
00:00:00:000'',@removeDate=''2007-02-05 00:00:00:000'',@CPID=3008195
5 [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2007'.
>
However, if I create dynamic SQL and use the following, there are no errors.
>
string mySql = string.Empty;
mySql = "SELECT HomeMsgID, messageName, messageHTML, messageText,
populationID FROM MyUCR_HomeMessages WHERE (populationID IN (SELECT
populationID FROM MyUCR_Population_CPID AS MyUCR_Population_CPID_1 WHERE
(CPID = " + CPID + "))) AND (isVisible = 1) AND (showDate <= '" + showDate +
"') AND (removeDate >= '" + removeDate + "')"; SqlDataAdapter adapter = new
SqlDataAdapter(mySql,
ConfigurationManager.ConnectionStrings["MyUCR2007ConnectionString"].ToString());
DataSet RecordCount = new DataSet();
adapter.Fill(RecordCount);
DataTable testDT = RecordCount.Tables[0];
>
I am using VSTS with the Service Pack installed. SQL 2005 is running on W2K3
Enterprise, fully patched

May I suggest the SQL Server 2005 MSDN Forums ?

http://forums.microsoft.com/MSDN/def...ID=19&SiteID=1

Juan T. Llibre, asp.net MVP
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en español : http://asp.net.do/foros/
===================================
"James Johnson" <JamesJohnson@discussions.microsoft.comwrote in message
news:67FB2545-1C29-459D-B239-0D81272E999E@microsoft.com...

Quote:

Perhaps this isn't the correct forum? Does anyone have an idea on where to
post this message?
>
Thanks,
James
--
James Johnson
>
>
"JamesIEDOTNET" wrote:
>

Quote:

>I have an application (ASP.NET 2.0/SQL Server 2005) which makes heavy use of
>table adapters for pulling records from SQL. Under heavy load, we get a lot
>of SQL Server Timeout errors. We have run a trace on SQL Server and it shows
>that several of the SQL statements being passed into SQL Server, from the
>Table Adapters, have bad SQL.
>>
>For example, here is the SQL in one of the table adapters
>>
>SELECT HomeMsgID, messageName, messageHTML, messageText, populationID
>FROM MyUCR_HomeMessages
>WHERE (populationID IN
> (SELECT populationID
> FROM MyUCR_Population_CPID AS
>MyUCR_Population_CPID_1
> WHERE (CPID = @CPID))) AND (isVisible = 1)
>AND (showDate <= @showDate) AND (removeDate >= @removeDate)
>>
>I call it with the following:
>>
>DateTime showDate = DateTime.Today;
>DateTime removeDate = DateTime.Today;
>>
>myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessage sTableAdapter ta = new
>myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessage sTableAdapter();
>myUCR_HomePageMsgs.MyUCR_HomeMessagesDataTable dt = new
>myUCR_HomePageMsgs.MyUCR_HomeMessagesDataTable( );
>>
>ta.FillByCPID(dt, showDate, removeDate, CPID);
>>
>What the SQL trace shows, when it fails, is this (notice the extra single
>quotes around the showDate, removeDate parameters):
>E000
>exec sp_executesql N'SELECT HomeMsgID, messageName, messageHTML,
>messageText, populationID
>FROM MyUCR_HomeMessages
>WHERE (populationID IN
> (SELECT populationID
> FROM MyUCR_Population_CPID AS
>MyUCR_Population_CPID_1
> WHERE (CPID = @CPID))) AND (isVisible = 1)
>AND (showDate <= @showDate) AND (removeDate >= @removeDate)',N'@showDate
>datetime,@removeDate datetime,@CPID int',@showDate=''2007-02-05
>00:00:00:000'',@removeDate=''2007-02-05 00:00:00:000'',@CPID=3071225
>1 [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2007'.
>>
>I recreated the SQL to use a stored procedure, and got a similar error:
>>
>E000 exec dbo.spFillHomeMsgByCPID @showDate=''2007-02-05
>00:00:00:000'',@removeDate=''2007-02-05 00:00:00:000'',@CPID=3008195
>5 [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2007'.
>>
>However, if I create dynamic SQL and use the following, there are no errors.
>>
>string mySql = string.Empty;
>mySql = "SELECT HomeMsgID, messageName, messageHTML, messageText,
>populationID FROM MyUCR_HomeMessages WHERE (populationID IN (SELECT
>populationID FROM MyUCR_Population_CPID AS MyUCR_Population_CPID_1 WHERE
>(CPID = " + CPID + "))) AND (isVisible = 1) AND (showDate <= '" + showDate +
>"') AND (removeDate >= '" + removeDate + "')"; SqlDataAdapter adapter = new
>SqlDataAdapter(mySql,
>ConfigurationManager.ConnectionStrings["MyUCR2007ConnectionString"].ToString());
>DataSet RecordCount = new DataSet();
>adapter.Fill(RecordCount);
>DataTable testDT = RecordCount.Tables[0];
>>
>I am using VSTS with the Service Pack installed. SQL 2005 is running on W2K3
>Enterprise, fully patched

Table Adapter generates bad SQL under load

re: Table Adapter generates bad SQL under load

re: Table Adapter generates bad SQL under load

Similar ASP.NET bytes

/bytes/development

/bytes/it

/bytes/about