Weird And Funny Question
Sep 22, 2006I specified configuration like shown below
"select * from table where id<>***" always doesn't work
however i changed that like
"id != ***" working!
Who can tell me why?
ADVERTISEMENT
Tech's Are So Funny
Aug 16, 2004Ok a network tech put an executive assistants database on sql with the upsize wizard in access. Only problem is that she cant input anything into the database, my guess is that its due to no Primary key in the new sql table. I have had this problem before am I correct in my assumptions???
View 6 Replies View RelatedFunny Search Error
Oct 28, 2004I have two fields I am searching on .... address and zipCode
When I have both pieces of data, everything is fine, i get back the expected results, but i am unable able to search on the individual pieces. Here is the SQL I am using. It's placed within a store procedure in Sqlserver that I amreferencing from my VB.net code.... any help??
IF ((NOT @address IS NULL) AND( NOT @zipCode IS NULL))
BEGIN
SELECT OrderNumber FROM Orders WHERE ShpAddr_Address1 = @address AND ShpAddr_ZipCode = @zipCode
END
ELSE IF NOT @address IS NULL
BEGIN
SELECT OrderNumber FROM Orders WHERE ShpAddr_Address1 = @address
END
ELSE IF NOT @zipCode IS NULL
BEGIN
SELECT OrderNumber FROM Orders WHERE ShpAddr_ZipCode = @zipCode
END
any help would be great. thanks
Funny Network Behaviour
Jul 20, 2005Hi,In order to establish a security enhanced SQL server setup, I triedto switch off network access by disabling all networking protocols,so that the server can only be reached through a pipe. Neverthelessthe server was visible in the network and could be accessed fromall clients. Does anybody know what is going on here?Georg
View 1 Replies View RelatedFunny Problem (bug?) Using MAX() On SQL Mobile
Feb 8, 2006This sure looks like a bug, but I'm still new...
First I create a simple database:
SqlCeEngine eng = new SqlCeEngine(@"Data Source=My Documents est.sdf");
eng.CreateDatabase();
eng.Dispose();
Then I put something in it:
SqlCeConnection conn = new SqlCeConnection(@"Data Source=My Documents est.sdf");
SqlCeCommand cmd = new SqlCeCommand("CREATE TABLE myTable (ID int, name nchar(10))");
cmd.Connection=conn;
conn.Open();
cmd.ExecuteNonQuery();
cmd.CommandText="INSERT INTO myTable (ID, name) VALUES (1,'bill')";
cmd.ExecuteNonQuery();
Then I try to get the maximum value from the first column.
cmd.CommandText = "SELECT max(ID) FROM myTable";
int index = (int)cmd.ExecuteScalar();
At this point I get an InvalidCastException raised. Using the datareader to extract the value produces the same exception, however I can go through the Query Analyzer on the device and execute the query and it comes out fine...???
This has really got me confused. Any thoughts?
Funny Error During Runs
Jan 26, 2007Can any one please help explain why this error has happened, it is not a normal error of SSIS,
"SSIS Debug host has encountered a problem and needs to close. We are sorry for the inconvenience"
It has 2 buttons Debug and Close, this error happened in the middle of a script task.
Query Return Funny Characters
Apr 18, 2006I run a query in QA which return funny characters (suppose to be chinese characters). I try saved as CSV and open in excel, still it remain as those funny characters... What can I do to get those output into the chinese character. It doesn't need to be in QA... but I need it in Excel.
Thanks
Funny Characters And Repeating Digits
Jan 17, 2003How do l filter out funny characters in a field like (',','@','#','"'.'/')etc And Repeating digits Like 5555555,2222,111111,00,
000000000.
Tried using the patindex but my method is too long.How do l do that ?
Purchase Order Query: Very Funny And Challenge ^_^
Mar 1, 2006i query a purchase order table, there is one column called PO_No, format: LP-0245111-0004
i make following statement to query: the middle code act as my id, using it search my records, the last 4 digit used to find the last purchase order number
SqlSelectCommand2.CommandText = "SELECT PO_No FROM [PURCHASE ORDER] WHERE PO_No Like '%" & GetYearCode() & "%' ORDER BY Right(PO_No, 4) DESC"
i checked my database, last record is LP-0545381-0300
in my debuging process, surprisingly found that selected record is LP-0545381-301 !
any one hav any suggestion? ^_^
Funny SQL Server Error Messages - Anyone Has An Explanation?
Apr 3, 2006I am getting a new server online at a customer, and our system shows very funny erorrs under full load. No explanations could be found. Anyone has some?
Here we go:
Server failed to resume the transaction, desc: 360000054a. The transaction active in this session has been committed or aborted by another session.,
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
and
New request is not allowed to start because it should come with valid transaction descriptor.
System.Data.SqlClient.SqlException
I am a little lost on those. Simple fact is that I do not find any description of those. Anyone an idea what causes these?
PASTE SQL RESULTS INTO EXCEL - Funny Results
Jan 30, 2008Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.
View 6 Replies View RelatedHere Is A Weird One!!
Sep 24, 2001I have a dts package that is mysteriously changing back to a previously saved version. Package is saved, and re-opened with saved changes, then moments later opened again to have been reverted back to an older version. Has anyone ran into this before?
The only thing that I can think of is that if a version of the package is left open on some other machine (as the older version0, it may be auto-saving, and reverting back.
Really Weird One Here...
Mar 31, 2008I run a website, so all of this is done remotely, I do not have access to the servers directly.
On my prior host, everything on the DB worked just fine. Had it optimized and running pretty good for the site by adding indexes, etc.
Then I moved to my new faster server (new host) and at first everything seemed to be just fine. However, some users started complaining about speed in spots.
Sure enough when I checked a given page -- WHAM... the page took about 14 seconds to load. Not exactly fast. :)
So I checked to make sure the indexes copied over and sure enough they did. But it was still slow, even in Query Analyzer/MSE.
So I decided to rerun my view script... on a lark... just to see if that affected something. And sure enough it did. The page started running at 1 second or less. OK, I thought, the view just got corrupted or something.
All was fine until the next night when it happened again. I had added 12 new rows to the table (I do this nightly) and it seemed that adding the new rows slowed down the system. Until the view was rebuilt. With that done again, it's worked fine.
But each night now I have to rerun that view.
What gives??? Is there a DB setting or something causing views/indexes to not be maintained?
The prior server was 2000 or 2003. The new one is 2005.
Again, I cannot get access to the box itself except through Management Studio. The host's tech support group is less than useless, treating everyone as incompetent before they help you.
Any hints or solutions would be a big help!
Weird E+006
Mar 12, 2008I'm trying to CAST an integer to a varchar and I keep getting e+006 in my result set. The reason I need to do this is so that I can combine 2 columns to return a string.
Here is my table:
incomeBracket | lowIncome(int) | highIncome(int)
-----------------------------------------------
1 1000001 5000000
2 5000001 10000000
Here is my query:
SELECT CAST(incomeLow AS varchar) + ' - ' + CAST(incomeHigh AS varchar) AS incomeRange
FROM IncomeBracket_Currency
How can I get my result to look like this:
1000001 - 5000000
5000001 - 10000000
Instead of what it is returning now:
1e+006 - 5e+006
5e+006 - 1e+007
The query works fine for values that have a character length of 6 and less.
Thanks in advance.
A Weird Error !
Jul 25, 2006I was developing a data-driven website but I got this error
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
(I have Sql server 2005 also downloaded before)
Please help me cause any website that has data can not be executed even the quick start tutorial that ships with the ASP.NET 2.0
What should I do?
Weird Concantenation
Nov 14, 2006Hello and thanks for taking a moment to read this message.I have two colums from which I am taking values. One of which(a bit field), i am running a CASE statement against it to get SQL Server to return a string. The other is just a simple varchar column. My SELECT statement for the columns look like this:
SELECT Case tblDisplayProfile.Approved When 0 then 'Not Approved' when 1 then 'Approved' else 'Not looked at' END AS Approved, tblDisplayProfile.DisplayProfileDesc
These statements do return me something. Now what I want to do is combine (concantenate) the two fields. This is where I have problems. Any suggestions would be greatly appreciated.
Jason
Weird Ado.net 2.0 Error
Nov 19, 2006Why does ado.net produce this error when I kow for a fact that there are no connections to any sql server 2005 database involved - this could and probbaly has had people off looking at the wrong connection strings in their web.configs.
Note:- The connection string we have is to a sql 200 db I just wanted to make that clear.
CheersGegor
Error Message:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)Stack Trace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo)
Weird Problem
Jun 5, 2008Hi, I moved a SQL 2000 DB to a SQL 2005 server. When I run my .NET 1.1 or 3.5 website to call a stored procedure it returns the wrong results.The SQL stored proc runs fine when I excute it within SQL Manager but gives a different result when called via .NET The funny thing is I have 3 SQL servers 2000 , 2005 , and another 2005. The same data on each of the databases and the same website. Two of the databases work but the second 2005 database my production server decrements the dates some how. The funny thing is the stroed proc is not returning dates as such but a varchar of the name of the day that's getting decremented. My stored proc is SELECT
Case datepart(dw, DT_DATE)
When 2 Then 'Mon'
When 3 Then 'Tue'
When 4 Then 'Wed'
When 5 Then 'Thu'
When 6 Then 'Fri' End AS [WeekDay],
Sum(VL_HOURS) as Total
FROM ...
WHERE ....
GROUP BY datepart(dw, DT_DATE) and My c# .net code is "SqlHelper.ExecuteReader( connectionstring, storedproc, paramaeters);"using MS.ApplicationBlocksWhat happens that from sql, and 2 of my websites is when week day = 4 it returns "Wed" However on my Production server it returns "Tue" ???
Weird Error
Mar 18, 2004i have a form which takes in a lot of input...one of them is a date field..am passing all the values to the stored proc and inserting into a table from there...pretty simple...however when the user does not enter any value in the field...its errors out as...
String was not recognized as a valid DateTime.
heres the code in asp.net
myCommand.Parameters.Add(New SqlParameter("@cusbday",SqlDbType.datetime))
myCommand.Parameters("@cusbday").Value = trim(bday.text)
the field is optional....in my stored proc i am setting a default value too
....@cusbday Datetime =NULL,...
i also xplicitly set it to null through code
if @cusbday is null set @cusbday=null
how else do i resolve this...
thanks
Weird Question
Apr 25, 2001Hello!
MSSQL Server 7.0 SP1
When I open DTS designer for some unknown reason I'm getting error message "Could not create Component Categories manager".
Then under Task I don't have any choices.
Is there a quick way to fix it?
Thanks
Lena
WEIRD ERROR
Feb 20, 2001I was running a DTS package which transfer a complete database, about 2.3GB in size, to another database on the same server. It was necessary for environment change from dev to QA. I baby sat the package till it was rebuilding indexes (90%) complete. This morning I have the following error message on my screen,
[Microsoft][ODBC SQL Server Driver][SQL Server]
[Microsoft][ODBC SQL Server Driver][SQL Server]Location: r:SPHINXNTDBMSqueryqeexecqsxchng.inl:749
Expression: (m_cbPageMac + ulPxvarSize) <=m_pxpktdesc->m_cbPageMax
SPID: 40
ProcessID: 347
I have no idea at this time what caused it. And as far as I can tell there in r drive mapped wither on the serve or my computer ( I was running the DTS package from my machine).Any help is appreciated
Really Weird SQL Failure
Jun 9, 2003Last night our production server went crazy. The primary (production) database MDF file had size 15Gb and then for some reason the size became 0 (zero) K, So the SQL right away reported the error:
I/O error 38(Reached end of file.) detected during read of page buffer..
Error: 823, Severity: 24, State: 10
And the database became "suspected".
Now we restoring it, but have anybody seen such a thing before? I'm breaking my head to figure out what could cause this problem!!!!!
Dim
Weird Prob!!!HELP
Aug 1, 2002Whenever I am creating a new database, I am not getting any system stored procedures created :o( the system tables & views are created though :o(
what maybe the problem?
thanks!
Weird Behaviour On Net Use
Jul 5, 2004When I run the command:
exec master..xp_cmdshell 'NET USE'
from the analyzer the box responds there are no entries in the list.
After that, I run the command:
exec master..xp_cmdshell 'NET USE Z: /DELETE'
after which the box responds with a "network connection could not be found."
and that's all okay.
The weird thing is:
exec master..xp_cmdshell 'NET USE Z: \MACHINESHARENAME'
results in a "The local device name is already in use.".
The machine in this particular case is the box itself. I have no problem accessing other disks on other systems. I can see the share using the view command. There's no maximum on the share itself and I can connect to the share using another sql box with the same user.
I don't know why it won't budge, worked before like a charm. After six months or so it just stopped. Anyone seen/solved this behaviour?
thanx,
Weird DateTime Value
Dec 20, 2004I have a datetime field with a value of '-28049-03-16 10:01:16.267' in SQl Server 2000. I haven't a clue how or why this value was entered as I did not design or write the database. However, I do have the task of writing a DTS package to export the data to Access. The problem is that the DTS fails when it tries to export the record with the above value into an Access table with a Date/Time field. I thought I could get round this by using a UDF to say if the date equals this value then make it null:
RETURNS DateTime
As
BEGIN
DECLARE @CheckDate varChar(300)
SET @CheckDate = CONVERT(varChar(300),@Date)
IF (@CheckDate = '-28049-03-16 10:01:16.267')
BEGIN
SET @Date = Null
END
RETURN (@Date)
END
However when I use the UDF it gives me the following error:
Server: Msg 542, Level 16, State 1, Procedure UDFn_CheckDate, Line 23
An invalid datetime value was encountered. Value exceeds the year 9999.
I have tried many variations of Convert, Cast etc. but still can't get it to work.
Anybody got any ideas???
Weird SP Problem
Feb 17, 2006Hello,
I'm now converting my queries from Access db to SQL Server db and I'm new with SP only.
I have a big query that is building from 1 or 2 sub-queries with "TOP x" (x is a variable in ASP).
SP can't be used with an outside variable for "SELECT TOP x.." so I tried to send the sub-query as a variable to the SP
but it refer it as a string and not a code so I can't run this sub-query in the SP.
Example:
CREATE PROCEDURE usp_test
@subSQL varchar(200)
AS
SELECT id FROM tbl WHERE id IN (@subSQL) ORDER BY id DESC
GO
----------------
exec usp_test 'SELECT TOP 15 id FROM tbl2'
What's wrong here? Am I working in a wrong method? :(
Thanks for the helpers,
BuildHome
Weird Error
Dec 30, 2003Heres some SQL I am trying to execute:
INSERT INTO Contact_Info (First,Last,Age) VALUES ('Alex','Strait',19)
Now it seems basic, but there in one more field that you're not supposed to specifiy a value in the SQL, its an int IDENTITY datatype field called num_id. Now this SQL works if it doesnt have the num_id field. But heres the error is gives me, I have never seen it before
Microsoft OLE DB Provider for ODBC Drivers error '80040e57'
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.
/neosql.asp, line 847
Does anyone know what is wrong?
Weird Behaivior
Jun 13, 2008Hi
I have this stored procedure that works a little odd, some times it do what I want it to do which is....
- Insert orderinformation in tbl_Torderinfo table and then transfer all rows that are associated with a ordernumber from tbl_Ctemp to tbl_Torders
but sometimes it only insert data into the tbl_Orderinfo table and doesn't transfer the order details from tbl_Ctemp to tbl_TOrders even if there is rows to transfer. Any ideas of what might cause this odd behaivior?
p_AddOrder
@AdressID Int,
@UserID Int,
@UserRealName nVarChar(255),
@Comment nVarChar(4000),
@OurReference nvarchar(255),
@YourReference nvarchar(255),
@DeliveryAdress nvarchar(4000),
@Buyer nvarchar(4000),
@DeliverBy nvarchar(255),
@DestCountry nvarchar(255),
@FreightMark nvarchar(255),
AS
DECLARE @iOrderID Int
BEGIN
SET NOCOUNT ON;
INSERT INTO tbl_TOrderInfo
(
AdressID,
UserID,
UserRealName,
DateOrdered,
Comment,
OurReference,
YourReference,
DeliveryAdress,
Buyer,
DeliverBy,
DestCountry,
FreightMark,
OrderID
)
VALUES
(
@AdressID,
@UserID,
@UserRealName,
GETDATE(),
@Comment,
@OurReference,
@YourReference,
@DeliveryAdress,
@Buyer,
@DeliverBy,
@DestCountry,
@FreightMark,
@OrderID
)
SELECT @@IDENTITY
SET @iOrderID = @@IDENTITY
--Insert New ID and Info into Orders table
INSERT INTO tbl_TOrders (TOrderID,ProductName,OwnBID)
SELECT @iOrderID, Product, OwnBID FROM tbl_CTemp WHERE UID = @OrderID
END
Weird SP Problem
Feb 17, 2006Hello,
I'm now converting my queries from Access db to SQL Server db and I'm new with SP only.
I have a big query that is building from 1 or 2 sub-queries with "TOP x" (x is a variable in ASP).
SP can't be used with an outside variable for "SELECT TOP x.." so I tried to send the sub-query as a variable to the SP
but it refer it as a string and not a code so I can't run this sub-query in the SP.
Example:
CREATE PROCEDURE usp_test
@subSQL varchar(200)
AS
SELECT id FROM tbl WHERE id IN (@subSQL) ORDER BY id DESC
GO
----------------
exec usp_test 'SELECT TOP 15 id FROM tbl2'
What's wrong here? Am I working in a wrong method?
Thanks for the helpers,
BuildHome
Weird DTS Problem
Aug 12, 2006have a dts package that is run by a scheduled job which was runningsuccessfully for several months and suddenly stopped working.The dts package does the following: Truncates the table, does a datapump from a specific excel file to a table on sql server, deletes theexcel file. The job does the following:Step 1: Look to see if a file exists and if so, run the dts package. Onsuccess of this step the job goes to step 2.Step2: Looks to see if any files still exist after the first stepStep3: Failure notification. To be triggered on failure of step 1 or2.The owner of the job is the Account that is used to run theSQLServiceAgent and in the SysAdmin server role.The owner of the job is also in the SysAdmin server role.Both the accounts are Windows Accounts.The job runs at schedule time, finds the file, and runs the dtspackage. The first part of the dts package runs successfully - Thetable is cleared. However it is not doing the data pump part. Thereare no failure notifications. The job then goes to step two and sendsa message that the file still exists. The dts task is set to output anerror file which is not being updated, which tells that the task is notexecuted. If the dts package is run manually, then the package runssuccessfully.Any help would be appreciated.ThanksKR
View 2 Replies View RelatedWeird Toolbar
Aug 31, 2006I have just installed my server with server2003 enterprise edition, everything is working fine. However, after I make it online for awhile, I found a weird thing on the tool bar just beside the clock. There are 2 hidden icons, when I move the pointer to these 2 icons, it shows the IP address of my server. Just wanna know if there is anything wrong with that.
Thanks!
tom
Weird BIT Behavior...
Aug 29, 2007I'm seeing some strange behavior from a stored procedure of mine. It essentially grabs a bunch of rows using a fairly simple JOIN....here's the from statement:
Code Snippet
FROM Payment PY (NOLOCK)
JOIN (SELECT DISTINCT
PY.AccountPaymentId,
ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId ASC) AS RowNum
FROM Payment PY (NOLOCK)) AS SQ
ON (SQ.AccountPaymentId = PY.AccountPaymentId)
INNER JOIN Payee PE ON PE.PayeeId = PY.PayeeId INNER JOIN
Party PT ON PE.PartyId = PT.PartyId INNER JOIN
Distribution DS ON PY.DistributionId = DS.DistributionId LEFT OUTER JOIN
Account AC ON DS.AccountId = AC.AccountId INNER JOIN
clm CM ON PE.clm_no = cm.clm_no LEFT OUTER JOIN
PartyAddress PA ON PY.PartyAddressId = PA.PartyAddressId AND
PT.PartyId = PA.PartyId
WHERE RowNum BETWEEN (((@Page * @PageSize) - @PageSize) + 1) AND ((@Page * @PageSize) - @PageSize) + @PageSize
and ((@PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @PayeeName + '%'))
AND ((@AccountId IS NULL) OR (AC.AccountId = @AccountId))
AND ((@DistributionId IS NULL) OR (DS.DistributionId = @DistributionId))
AND ((@PaymentDate IS NULL) OR (DATEADD(day, DATEDIFF(day, 0, PY.PaymentDate), 0) = DATEADD(day, DATEDIFF(day, 0, @PaymentDate), 0))) -- Ignores the time
AND ((@PaymentNumber IS NULL) OR (PY.AccountPaymentId = @PaymentNumber))
AND ((@IsReconciled IS NULL) OR (PY.ReconciledInd = @IsReconciled))
AND ((@AmountIssued IS NULL) OR (PY.PaymentAmount = @AmountIssued))
AND ((@AmountPaid IS NULL) OR (PY.AccountPaidAmount = @AmountPaid))
AND ((@IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @IssueStatus))
AND ((@AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @AccountStatus))
AND ((@IsReissued IS NULL) OR (PY.ReissuedInd = @IsReissued))
ORDER BY AccountPaymentID ASC
When I pass a 1 for the @IsReconciled parameter, I get the right number of rows back - 9779. But when I pass a 0 (zero), i get no rows back, although there are 222 rows which satisfy the condition.
Is there somethig I'm overlooking (I don't think I am...)? I don't know whay 1 works and 0 wouldn't...
FYI - the @IsReconciled parameter is set to NULL at the outset of the procedure -
@IsReconciled Bit = Null
HELP - Weird ADO Problem
Jan 30, 2008Hi, I hope someone can help me with a weird problem I'm having. Towards the end of last week a number of users started reporting problems with one of our legacy applications. The application is written in VB6 connecting via ADO to a SQL 7 database. I managed to trace the error to a piece of inline SQL (shock!horror!) in a DLL that hasn't been changed since 2002, it occurs on the adodb recordset open and generates the error "could not complete cursor operation because the table schema changed after the cursor was declared" although nothing has changed on the database. It does not always happen, it depends on the contents of the strOrganisationIDs and the order of the numbers, although the same combination of numbers will generate the error every time! Initially there were certain users who were not having the error, but they have all gradually 'caught up'! There were some windows updates rolled out about the same time the errors started occuring, but un-installing them makes no difference. Here is a snippet of the code:-
Dim rsRecordSet As ADODB.Recordset
Dim strOrganisationIDs As String
strOrganisationIDs = "(111974,21986,0)"
strSQL = "SELECT o.organisation_id, o.name, o.town, o.county," & _
" o.country, o.key_customer, i.industry_sector," & _
"(SELECT Name FROM SODA_User WHERE User_Id = o.User_ID) as Name1," & _
"(SELECT Name FROM SODA_User WHERE User_ID = o.Assignee) AS Name2," & _
"o.registered_on_web " & _
" FROM Organisation o, Industry_Sector i WHERE o.Sector_ID = i.Sector_ID" & _
" AND Organisation_Id IN " & strOrganisationIDs & _
"ORDER BY o.Search_Name"
rsRecordset.open strSQL
I have managed to find a way around the error - I removed the spaces in front of the o.country, don't know why they were there, and they shouldn't cause the error - in fact they are there on the occasions it works, it seems to be the combination of spaces and certain numbers in the strOrganisationIDs . It sounds stupid and i've been pulling my hair out looking for a reason, as have several colleagues. I don't want to have to roll out the fix as it will be quite messy and affects a lot of users, although i may have to - but what I really want to know is why it has happened. I hope someone can shed some light on this, if any other info is required, let me know.