Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ScoobyDoo
Starting Member
4 Posts |
 Posted - 2003-03-06 : 17:03:02
|
| Why can I get this to work in a SQL Query but not on my ASP page? The error I get is this: Object or provider is not capable of performing requested operation. If I take off the SET ROWCOUNT 1 from my ASP page everything works fine. It has something to do with SET ROWCOUNT. ________________________________ SQLstmt = "SET ROWCOUNT 1 SELECT scIPaddress = sc.IPaddress, scCompName = sc.CompName, scExtension = sc.Extension, ServiceCallID = sc.ServiceCallID, scCategoryID = sc.CategoryID, " SQLstmt = SQLstmt & "scSubCategoryID = sc.SubCategoryID, scTechnicianID = sc.TechnicianID, scEmployeeID = sc.EmployeeID, scLocationID = sc.LocationID, " SQLstmt = SQLstmt & "scPriorityID = sc.PriorityID, scDateTimeInitiated = sc.DateTimeInitiated, scDateTimeCompleted = sc.DateTimeCompleted, scDescription = sc.Description, scResolution = sc.Resolution, scStatus = sc.Status, " SQLstmt = SQLstmt & "empEmployeeID = emp.EmployeeID, empLastName = emp.LastName, empNickName = emp.NickName, loIsDeleted = lo.IsDeleted, loLocNumber = lo.LocNumber, " SQLstmt = SQLstmt & "loLocationID = lo.LocationID, loLocationName = lo.LocationName, priPriorityID = pri.PriorityID, priPriorityName = pri.PriorityName, catCategory_id = cat.Category_id, " SQLstmt = SQLstmt & "catCategory = cat.Category, TechnicianNickName = tech.NickName, TechnicianLastName = tech.LastName, sSubcSubCategory_id = sSubc.SubCategory_id, " SQLstmt = SQLstmt & "sSubcSubCategory = sSubc.SubCategory FROM ScatServiceCall as sc " SQLstmt = SQLstmt & "INNER JOIN Employees as emp on sc.EmployeeID = emp.EmployeeID " SQLstmt = SQLstmt & "INNER JOIN Locations as lo on sc.LocationID = lo.LocationID " SQLstmt = SQLstmt & "INNER JOIN ScatPriorities as pri on sc.PriorityID = pri.PriorityID " SQLstmt = SQLstmt & "INNER JOIN ScatCats as cat on sc.CategoryID = cat.Category_id " SQLstmt = SQLstmt & "INNER JOIN Employees as tech on sc.TechnicianID = tech.EmployeeID " SQLstmt = SQLstmt & "INNER JOIN ScatSubCats as sSubc on sc.SubCategoryID = sSubc.SubCategory_id " SQLstmt = SQLstmt & "WHERE (((((((((((emp.NickName LIKE '%[" & Replace(ucase(mid(strSearch, 1, 1)) & lcase(mid(strSearch, 1, 1)) & "]" & mid(strSearch, 2), "'", "''") & "%')" SQLstmt = SQLstmt & " OR (emp.LastName LIKE '%[" & Replace(ucase(mid(strSearch, 1, 1)) & lcase(mid(strSearch, 1, 1)) & "]" & mid(strSearch, 2), "'", "''") & "%')" SQLstmt = SQLstmt & " OR (sc.IPaddress LIKE '%[" & Replace(ucase(mid(strSearch, 1, 1)) & lcase(mid(strSearch, 1, 1)) & "]" & mid(strSearch, 2), "'", "''") & "%')" SQLstmt = SQLstmt & " OR (sc.CompName LIKE '%[" & Replace(ucase(mid(strSearch, 1, 1)) & lcase(mid(strSearch, 1, 1)) & "]" & mid(strSearch, 2), "'", "''") & "%')" SQLstmt = SQLstmt & " OR (sc.Resolution LIKE '%[" & Replace(ucase(mid(strSearch, 1, 1)) & lcase(mid(strSearch, 1, 1)) & "]" & mid(strSearch, 2), "'", "''") & "%')" SQLstmt = SQLstmt & " OR (sc.Description LIKE '%[" & Replace(ucase(mid(strSearch, 1, 1)) & lcase(mid(strSearch, 1, 1)) & "]" & mid(strSearch, 2), "'", "''") & "%')" SQLstmt = SQLstmt & " OR (lo.LocationName LIKE '%[" & Replace(ucase(mid(strSearch, 1, 1)) & lcase(mid(strSearch, 1, 1)) & "]" & mid(strSearch, 2), "'", "''") & "%')" SQLstmt = SQLstmt & " OR (tech.NickName LIKE '%[" & Replace(ucase(mid(strSearch, 1, 1)) & lcase(mid(strSearch, 1, 1)) & "]" & mid(strSearch, 2), "'", "''") & "%')" SQLstmt = SQLstmt & " OR (tech.LastName LIKE '%[" & Replace(ucase(mid(strSearch, 1, 1)) & lcase(mid(strSearch, 1, 1)) & "]" & mid(strSearch, 2), "'", "''") & "%')" '---SQLstmt = SQLstmt & "OR cat.Category LIKE '%[" & Replace(ucase(mid(strSearch, 1, 1)) & lcase(mid(strSearch, 1, 1)) & "]" & mid(strSearch, 2), "'", "''") & "%'" '---SQLstmt = SQLstmt & "OR sSubc.SubCategory LIKE '%[" & Replace(ucase(mid(strSearch, 1, 1)) & lcase(mid(strSearch, 1, 1)) & "]" & mid(strSearch, 2), "'", "''") & "%'" If Request("SCATtechnicians") <> "0" Then SQLstmt = SQLstmt & ") AND (tech.NickName LIKE '%" & Request("SCATtechnicians") & "%'))" Else SQLstmt = SQLstmt & "))" End If If Request("Employees") <> "0" Then SQLstmt = SQLstmt & ") AND (emp.NickName LIKE '%" & Request("Employees") & "%'))" Else SQLstmt = SQLstmt & "))" End If If Request("Location") <> "0" Then SQLstmt = SQLstmt & ") AND (lo.LocationName LIKE '%" & Request("Location") & "%'))" Else SQLstmt = SQLstmt & "))" End If If Request("Status") <> "" Then If Request("Status") = 0 Then SQLstmt = SQLstmt & ") AND (sc.Status=0))" End If If Request("Status") = 1 Then SQLstmt = SQLstmt & ") AND (sc.Status=1))" End If ' If Request("Status") = 3 Then ' SQLstmt = SQLstmt & ") AND (sc.Status=0)) OR (sc.Status=1) OR (sc.Status=2))" ' End If Else SQLstmt = SQLstmt & "))" End If If Request("date1") <> "" and Request("date2") <> "" Then SQLstmt = SQLstmt & ") AND ((sc.DateTimeInitiated >= '" & (dtFirstDate) & "') AND (sc.DateTimeInitiated < '" & (dtSecondDate) & "')))" Else SQLstmt = SQLstmt & "))" End If SQLstmt = SQLstmt & " ORDER BY tech.NickName;" Response.Write "DEBUG: SQLstmt is " & SQLstmt & "<P>" & vbNewLine rsTemp.Open SQLstmt, dbCoastal,3 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-06 : 17:26:39
|
| It sounds like the provider (OLEDB, ODBC, etc...), which is not SQL Server, does not allow SET ROWCOUNT. Why don't you put your sql statement into a stored procedure and then just call the stored procedure from your ASP. If you do it this way, then your query will run on the server where SET ROWCOUNT is allowed.Tara |
 |
|
|
ScoobyDoo
Starting Member
4 Posts |
Posted - 2003-03-06 : 17:28:07
|
| Never thought about that. Thanks. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-03-06 : 18:24:18
|
| Holy Dynamic SQL Batman!Look at the provider in your connection string, or tell us what it is.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda>Edited by - michaelp on 03/06/2003 18:24:51 |
|
|
|
ScoobyDoo
Starting Member
4 Posts |
Posted - 2003-03-07 : 10:00:11
|
| Set dbCoastal = Server.CreateObject("ADODB.Connection")dbCoastal.ConnectionTimeout = Application("Intranet_ConnectionTimeout")dbCoastal.CommandTimeout = Application("Intranet_CommandTimeout")dbCoastal.Open Application("Intranet_ConnectionString"), Application("Intranet_RuntimeUserName"), Application("Intranet_RuntimePassword") |
|
|
|
|
|
|
|
|
SQL Server 6.5 and SET ROWCOUNT