After Moving To SQL2005, Disconnected Recordsets Are Ready-only
Feb 16, 2006
We're using ADO disconnected recordsets. On SQL 2000, we could update these on the client (without propagating the changes to the server) even if the underlying view or table was non-updatable.
When running our apps against SQL 2005 (using the same client-side environment), we can no longer change any attributes of those disconnected recordsets, that connect to a non-updatable database object (the rest of the app runs fine, we can update all updatable database objects through disconnected recordsets) . Does SQL 2005 respond to such calls differently from SQL 2000, so that ADO recordsets are built in a new way (which makes them read-only in our setting)?
We are coming out of the dark ages with our app using SQL 7 and, following the excellent advice of the folks here on SQLTeam, installing SQL 2005 Express on our new webserver.
Not being terribly fluent in all things SQL, I was wondering if anybody could provide input on the best practices for getting SQL 2005 Express going on the new server.
So far I've:
- Installed SQL 2005 Express - Downloaded and "installed" the SSEUtil for CMD line instructions - Downloaded and installed the graphical management interface (very nice, makes me feel more comfortable - like SQL 7 console!) - Copied backup files (made using SQL backup maintenance) to the new server
Should I simply create an empty db of the same name on the 2005 server and then restore the 7 data? Or ????
I searched briefly for previous posts of this nature and didn't find too much info so I hope I'm not duplicating effort here...
Thanks in advance for any advice!
Mmmmmkay. Yeah, did you get the memo about the TPS reports?
I am planning to move the sql server 2000 database to the sql server 2005 database. I have to restore that by using the back up copy. Do I need to follow any special instructions or just like moving from one server to another. Could some body help with this. Thanks!
Hi I have a (possibly) common position where half of our IT department is SQL 2005 and the rest is SQL2000. For myself, having to work in a SQL2000 environment and needing data from a SQL2005 Cluster I came up with this solution. Also, to alert me when the process starts and completes since it is part of a scheduled process, I have it do a RAISERROR with logging to record entry and exit times. In addition, this runs out a series of PRINT statements that lets the operator know what table is currently being worked on.
Of course, any suggestions for speeding this up would be helpful!
What I have found that works for getting data (albeit slow) from SQL 2005 down to SQL 2000 is to script a fairly simple copy process -
-- it is actually pretty easy to follow if you just read the code......
-- first, we find all the views which are present, so they can be ignored when copying the raw data
CREATE TABLE #VIEWS (TABLE_NAME NVARCHAR (255) )
INSERT #VIEWS SELECT TABLE_NAME FROM OPENDATASOURCE( 'SQLOLEDB', Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' ) .SQL_SIS_SYSTEM.INFORMATION_SCHEMA.VIEWS
-- now, we get the actual tables with data CREATE TABLE #TEMP (TBL_SCHEMA VARCHAR (12), TBL_NAME VARCHAR (255), RECCNT INT )
INSERT #TEMP SELECT TABLE_SCHEMA , TABLE_NAME FROM OPENDATASOURCE( 'SQLOLEDB', 'Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' ) .SQL_SIS_SYSTEM.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM #VIEWS)
-- then, we start copying tables over - now we tag the ones which are populated with at least 1 row. -- the first cursor loop gets all table names, the second will find row counts of source tables. -- this segment uses (ugh) cursor to loop through and gather all of the data. This cursor is at the table name level - not -- processing anything, and is used only to find tables which have a rowcount > 0 -- believe it or not, the cursors run pretty darn quickly since they arnet doing any calculations - just finding -- tables with row counts > 0
SET @QUOT = CHAR(39) SET @LBRAKT = '[' SET @RBRAKT = ']' SET @IUT = 'IsUserTable' SET @ODBC_CMD ='(' + @QUOT + 'SQLOLEDB' + @QUOT + ',' + @QUOT + 'Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' + @QUOT + ').SQL_SIS_SYSTEM.dbo.' PRINT 'BEGIN TABLE SCHEMA LOAD CURSOR. ' + CAST(GETDATE() AS VARCHAR (50) ) DECLARE GETEM CURSOR FOR SELECT TBL_SCHEMA, TBL_NAME FROM #TEMP OPEN GETEM FETCH NEXT FROM GETEM INTO @TBL_SCHEMA, @TBL_NAME
WHILE @@FETCH_STATUS = 0 BEGIN
SET @SQL = 'UPDATE #TEMP SET RECCNT = ' + '(SELECT COUNT(*) FROM OPENDATASOURCE(' + @QUOT + 'SQLOLEDB'+ @QUOT + ',' + @QUOT +''Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' + @QUOT + ').SQL_SIS_SYSTEM.' + @TBL_SCHEMA + '.' + @TBL_NAME +')' + ' WHERE TBL_NAME = ' + @QUOT + @TBL_NAME + @QUOT EXEC (@SQL) FETCH NEXT FROM GETEM INTO @TBL_SCHEMA, @TBL_NAME END CLOSE GETEM DEALLOCATE GETEM
We have a number of databases running on our SQL2000 Servers. The databases use Stored Procedures which we call from our VB.NET apps, developed in-house. We now wish to upgrade our servers to SQL2005.
In the past, when upgrading from VS2002 to 2003 and then 2005, we have developed and executed Test Plans to ensure that the code upgrades properly. Microsoft even supplies FXCop and other tools to help. I now wish to develop and execute a similar Test Plan to ensure that our databases will work as required.
Does Microsoft supply any similar tools or guidelines for testing that databases written for SQL2000 will work as required on SQL2005? I see the Upgrade Wizard to examine the Server but is there a similar tool for testing databases? Are there any guidelines on the tests to include in such a Test Plan?
Hello All, i am trying to create a normal ASP.NET application using VS2005. Yesterday i was adding tables.. entering data from within "View Table Data" tab, but today i am unable to do any data insertion or updating except by entering the insert or update statement by hand. When updating data of existing record or trying to add new data, it reports that "Cell is readonly!". I did not modify any settings or configuration and actually did nothing to set it to readonly! I am quite confused to be honest and writing sql statements by hand is kinda time consuming for me if i want to update a single field. What can i do to re-enable data modifications from VS2005 without reporting readonly? Thanks,Rakan
This is a C++ / ADO / SQL question. Maybe not the right forum but I am guessing there are some programmers out there ...
I am trying to use ADO disconnected recordset to insert data into a sql table. I am using AddNew(vField, vValue) with UpdateBatch(). The code below does not throw any exceptions ... but does not add data to the table.
Any comments are appreciated, Thanks, Chris
void CTestApp::TestDatabaseUpdateBatch1a(void) { int nDataCount = 0; long nIndex = 0; long nIndex2 = 0;
try { // When we open the application we will open the ADO connection pConnection.CreateInstance(__uuidof(Connection));
// Replace Data Source value with your server name. bstr_t bstrConnect("Provider='sqloledb';Data Source='SQLDEV';" "Initial Catalog='AlphaNumericData';" "User Id=cmacgowan;Password=cmacgowan");
// Open the ado connection pConnection->Open(bstrConnect,"","",adConnectUnspecified);
// Create an instance of the database pRecordset.CreateInstance(__uuidof(Recordset));
// Select the correct sql string. Note that we are creating an // empty string by doing a select on the primary key. We are only // doing inserts and we do not want to bring data back from the // server
csSQL = "SELECT * FROM dbo.AAMacgowanTest WHERE RecordId IS NULL"; // csSQL = "SELECT * FROM dbo.DICastRaw1Hr";
// Test to see if the recordset is connected if(pRecordset->GetState() != adStateClosed) { // The recordset is connected, we will see if we are // at the end
if((pRecordset->BOF) && (pRecordset->GetadoEOF())) { // The recordset is empty bIsEmpty = false; }
// COleDateTime is a wrapper for VARIANT's DATE type. COleVariant is // a wrapper for VARIANTs themselves. If you need to create a // variant, you can say: COleDateTime oledtCurrentDate2 = COleDateTime::GetCurrentTime();
// Convert the OleDateTime to the varient COleVariant vCurrentDateTime2(oledtCurrentDate2);
//Set the DATE variant data type. memset(&st, 0, sizeof(SYSTEMTIME)); st.wYear = 2000; st.wMonth = 1; st.wDay = 1; st.wHour = 12;
// vect is a vector of COleSafeArrays containing the records for(i = 0; i < 10; i++) {
// Setup the data nValueIndex = 0; vValueList[nValueIndex].vt = VT_BSTR; vValueList[nValueIndex].bstrVal = ::SysAllocString(L"BLUE"); nValueIndex++;
I must release a new Db on 4/1 into an isolated small network (pier workgroup) and my choices are MSDE or Express. I'm new to both (MSDE yesterday, Express today). I am familiar with SQL Server and prefer using the tools with Express, but is it stable enough to use in this type of production environment?
I will be hosting my website in the next few months and doing more testing and I was having trouble finding a place to host the site that allows enough space for my sqlserver2005 database. I am estimating that the database will be around 40-50GB. Most hosting companies only give about 500MB for the database. Does anyone know of a company that provides more space? Also, is SQL Express production ready, or is it just for developing applications? If it is production ready, is there any "major" performace impact in using it over the full version. The reason I am asking is because hosting companies only give 500MB for a database, but alot more space for the web page. If I can put the SQLExpress file in the App_Data folder, then I will have more space. Does this even sound like it will work? Opinions are appreciated!
We're constructing a three-tier application. We want the middle tier to extract a recordset from the database, disconnect from the database, then pass the recordset to the front tier. After changes have been made by the front tier, it will pass the recordset back to the middle tier, which will reconnect, and update the database. The problem: Using stored procedures, the recordset is no longer available once the connection has been closed. Is there any way, using stored procedures, to be able to keep the recordset available after the connection has been closed, either by preserving it, copying it, or otherwise?
I've an issue while calling Oracle Stored procedure from MS SQL Server 2012 using LINKED SERVER, It gets connected and do the execution, But sometimes, it was disconnected and says this message 'A severe error occurred on the current command. The result if any should be discarded'.
I'm having a problem with two mirrored databases, using SQL Server 2005 SP2, that autmatically failed over last night for unknown reasons. I was trying to fail them back over to the primary SQL server because it says the mirror is disconnected and out of sync. Other databases failed over too, but I was able to fail those back over without a problem. It's just these two. I removed the mirror from the secondary server thinking that this would allow me to restore the database back on the primary server, but that didn't help.
These two databases show a status of (Principal, Disconnected / In Recovery) and I still cannot do anything with the two databases on the primary server. When I try to pull up the properties for them, I get the following error:
"Database <Name> is enabled for Database Mirroring, but neither the partner nor the witness server instances are available: the database cannot be opened.(Microsoft SQL Server, Error: 955)"
I cannot delete, detach, Alter or do anything with the databases. If I could just delete them that would be fine so I can just do a restore, but I can't seem to do anything.
Does anyone know what I can do to resolve this problem.
I have a mobile application written in vb.net using MsSQL and I want to use disconnected datasets to sync up to a remote MySQL database. is this possible? Its a two way sync, i.e I download and upload info.
Hello, Is SQL Server 2005 mirroring production ready yet. We have two servers and plan to set up mirroring between them. We have the Standard Editon installed on them. Is Standard Edition sufficient or does it need Enterprise Edition?
Its been almost 10 years since I have had to do work with good-old RecordSet objects...
I am filling a RecordSet with data returned from a SQL server via a stored procedure. I then set the ActiveConnection property to Nothing in order to disconnect it so I can make changes to it.
But when I try to set the value on a given row I get back a "Multiple-step operation generated errors. Check each status value" error message. My understanding is that this is indicative of trying to use the wrong datatype. I have verified that the type is correct (I am dealing with integers) so I am at a loss for what the problem could be.
Oddly enough, when I look at the Attribute property of the field I get back a value of 112. When you break it down I think that value indicates the row value is read-only? (Could that be my problem? Just a really bad/unhelpful error message?) But if I try to change it I get back a message saying cannot be done since RecordSet is already open.
I have a .Net database application that we've successfully deployed in a connected environment. Now we have a client that has the need to store data on a central SQL Server and publish that data out to tablet PC's that will be able to disconnect from the central SQL Server. At some point those tablet PC's will come back in and connect to the central server via VPN and will need to push their changes back to the server. Some fundamental questions:
1) Am I correct in assuming that replication is the best way to accomplish this?
If so,
2) Which replication type sounds appropriate to the above scenario?
3) Am I correct in assuming that the tablet PC's will need some version of SQL Server to support editing of the data in the disconnected state?
My perhaps incorrect first take on this was that we could use a licensed SQL Server on the central server and SQL Server Express as a replication subscriber on the tablet PC's.
While a database upgrade schema changes were being made the Mirror became disconnected will this recover itself when it reconnects or will it be the case that we will have to copy the db files to the Mirror and set it up from scratch.
I just set up a server with Windows 2003 Standard x64. I have a copy of SQL Server 2005 Standard Edition that I got from the Microsoft Launch event. On the back of the case, it gives version requirements for both the x86 and x64 versions. When I went to install it, it didn't ask me which one I wanted to install. When I look on the CD, I see different installers for x86 and x64 for various pre-requisites, like MSXML. As I'm installing SQL Server I see that it is installing most things to "C:Program Files (x86)" which is where 32-bit programs go.. I decided to let it finish. Most of the binaries and everything, include sqlservr.exe, are in the Program Files (x86) folder, but it did install a few things in Program Files, so it must be aware of the difference.
I checked task manager and, I see that the sqlservr.exe and SQLAGENT90.exe are NOT 32-bit processes. So, great, they're running natively in 64-bit mode! But when I look at the server options in SQL Server Management Studio, the patform is listed as NT INTEL X86. The memory page also shows an option to use AWE which according to the documentation is only available and relevant to 32-bit installations.
SO I'm pretty confused. Almost everything points to this being an x86 installation, but the running processes are running natively in x64. Does anyone know what's going on here?
I just tried to run the x64 version of SP1 for SQL 2005, and it told me that it cannot update x86 products, so I guess that clinches it. I'm still pretty confused and annoyed though. If this isn't x64, how can I get an x64 version of the same product, and will my license key work?
We are using SQL Server CE 3. So far, the product gets an A for ease of installation and its ability to deal with complex queries and but fails in a number of other areas. This is causing mjor project overruns. 1. Speed is a major concern. Small simple queries take an unreasonable tme to execute. This has required fundamental changes to current design patterms and a major re-write. 2. Bugs: Set identity_insert tablename on: tablename appears to be case sensitive (10 hours to track this beasty down) 3 Vista seems to have its own issues - refusing to cast fieldnames for simple column names eg 'Select UID as DepositID ...' returns a "DepositID" under Windows XP but returns "UID" under Windows Vista. Does this mean there is a different engine operating in the Vista environment. [I have since tracked this to a quirk in the SDAC interface ]
All this in a product we are intending to ship in the near future. Ar others having these / similar problems? Is there a register of known issues and workarounds? Is the product under active repair / development ? Why is behaviour different under Vista?
I created a little test application with SQL server. After everything was tested successfully I builded a setup file with Installshield. I installed the setup file on a different mashine. Everything runs without problems, but when I make entries to the database and I reopen the application no entry is saved. I am new to SQL server.
is it possible to concatenate 2 recordsets ? i have 2 select, and i need then to be displayed one after other.using union all the results are gathered, ordered like only one select.thanks for all
I've written a query which needs to return different records depending on its argument.
I've used an IF statment to test which of the three SELECT statments should be returned. However, when I try to access the data from VBA in Access I find that the recordset is empty, whereas the query returns thr correct data if I run it in the SQL environment. WHy is this? More importantly, how do I make it return records?
Code:
ALTER PROC [dbo].[spFilteredContractListByUser]
@UserID varchar(7) --The user's 'T' number
AS
PRINT 'User ID is' + @UserID
--Get this user's filter type from tbl
DECLARE @FilterType int
SELECT @FilterType = FilterID FROM tblFindContractUserFilter WHERE UserID = @UserID
-- Standard filter -- Returning all queries matching the username IF @FilterType = 1 BEGIN SELECT DISTINCT [Acc No], Business, Type, [Log Date], [Logged By], [Ack. Date], [Ack. By], [Late?] FROM qryQueriesWorkList WHERE (suser_sname = @UserID) END ELSE IF @FilterType = 2 -- Return only queries acknowledged by @UserID BEGIN SELECT DISTINCT q.[Acc No], q.Business, q.Type, q.[Log Date], q.[Logged By], q.[Ack. Date], q.[Ack. By], q.[Late?] FROM qryQueriesWorkList q WHERE (q.[Ack. By] = dbo.getEmployeeName(@UserID)) END ELSE IF @FilterType = 3 -- Return all the queries which have not been acknowledged BEGIN SELECT DISTINCT q.[Acc No], q.Business, q.Type, q.[Log Date], q.[Logged By], q.[Ack. Date], q.[Ack. By], q.[Late?] FROM qryQueriesWorkList q WHERE (q.[Ack. By] IS NULL) END
I need to decrease the amount of time it takes to return a set of data of the following format:
State[Provided vai user intput]->All Counties in State->All Cities in County->All Zones in City[usually < 50 or 75 per city]
current behavior: 1. currently user selects state all counties are returned from db 2. foreach county cities are returned 3. foreach city streets are returned 4. objects hydrated with data & display drawn
As you can see this gets expensive. I know there has to be a better way to do this. Any suggestions? We use sql server/c#.net.
I'm having SSIS block right now and thought I'd see if anyone had a simple solution to this.
What I have is SSN data coming in from several different tables, which needs to be inserted into a SurrogateSSN table which just uses an Identity field for the surrogate.
I normally build a seperate package for each detination table, so in each package that has SSN data in the source, I need to insert into the SurrogateSSN table (then I can use the surrogate values later in my package).
What I'm looking for is a simple solution if possible (preferrable using existing non-script tasks, or very short script), where I can pass in the SSN. Is there way to create a package that will accept a recordset (of SSN's) for this insert, that I can call from several different packages, and pass in the recordset of SSN's?
