Table Adapter generates bad SQL under load

G

Guest

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_HomeMessagesTableAdapter ta = new
myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessagesTableAdapter();
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
 
G

Guest

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

Thanks,
James
--
James Johnson


JamesIEDOTNET said:
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_HomeMessagesTableAdapter ta = new
myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessagesTableAdapter();
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
Click to expand...
 
J

Juan T. Llibre

May I suggest the SQL Server 2005 MSDN Forums ?

http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=19&SiteID=1





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

Thanks,
James
--
James Johnson


JamesIEDOTNET said:
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_HomeMessagesTableAdapter ta = new
myUCR_HomePageMsgsTableAdapters.MyUCR_HomeMessagesTableAdapter();
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

bad sql error message 1
C# or Sql Server 6
peculiar SQL Data Adapter Configuration Wizard Refresh problem 0
slow sql data adapter fil(.) when grouping 1
Datagrid, SQL Adapter question 1
MySQL - "create table" creates malfunctioning tables 0
How to retrieve data using sql 0
MySql's equivalence to MsSql's 'TOP' command/function/clause 1

Members online

Total: 27 (members: 1, guests: 26)
Robots: 315

Forum statistics

Threads
473,755
Messages
2,569,535
Members
45,007
Latest member
obedient dusk

Latest Threads

Top