Table Adapter generates bad SQL under load

[=?Utf-8?B?SmFtZXNJRURPVE5FVA==?=]

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

[=?Utf-8?B?SmFtZXMgSm9obnNvbg==?=]

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

Thanks,
James
--
James Johnson
"JamesIEDOTNET" wrote:

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

[Juan T. Llibre]

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" <Ja**********@discussions.microsoft.comwrote in message
news:67**********************************@microsof t.com...

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

Thanks,
James
--
James Johnson
"JamesIEDOTNET" wrote:

>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