Migrating SqlServer2000-&&>2005 And Classic ADO: Problem With AddNew To Recordset, Related To AdFldUnknownUpdatable Attribute?
Mar 26, 2006
The application is running ADO (MDAC) version 2.81.1117.0.
The application gets an recordset created by SQLServer by an select statement. In this case the recordset is empty.
The application adds a record to the recordset by AddNew(). Works fine. But when assigning the first field (smalldatetime in database) I get ADO error -2147217887.
I have inspected the recordset and I found a difference between the working one from SQLServer2000 and the new one from SQLServer2005: The Attributes item for the fields in the recordset has the flag adFldUnknownUpdatable set in the working SQL2000 version but is not set from SQL2005.
The database on SQL2005 is copied from the SQL2000 system and attatched to the SQL2005 system. I Have checket that the compability flag for the database on the SQL2005 system is set to SQL2000 compability.
hopefully, someone has already solved this problem
I have been assigned an application that maps data from access databases into a view in Sql Server 2005. A vb6 application does a select on the view. The recordset is then modified by the application, and eventually, the original tables will be updated with the new data. Some of the fields in the view are the result of calculations ( i.e. adding multiple varchar fields together to produce one complex varchar result ). Any of the fields that are calculated in the manner previously described have the adFldUnknownUpdatable bit set in the attributes of the recordset, and consequently, you get a -2147217887 error ( multiple-step operation .... ) when you try to change the value of the field in the recordset. Does anyone know how to get around this problem?
We need to migrate data from Sqlserver2000 database to Sqlserver2005. The SQLserver2000 DB was poorly Normalized and the DB was redesigned inSqlserver 2005. Both the databases are operational.Currently we have around 90 tables that need to migrate the data from the OLD DB to NEW DB, all the table mappings between the old schema and new schema have been successfully established.
Each Table is treated on its own merit and applying its own business rules which allows the data to be transformed to the new tables.The client wants us to build a SSIS solution that needs to performs the following
a. Import all Look Tables.
b. Import all master Tables ( One time import of some tables data and other tables may need to import every 4 hrs)
c. Import all Transaction tables( One time import and other tables may need to import every 4 hrs of data)
My Question is how to apporoach this scenario of pulling one time lookup, master and transaction tables and scheduling 4 hrs data importing process, how to build a SSIS solution for this type of scenario.
I add new records to a table with ADO. The tables contain an auto-increment identity column. I want to retrieve the identity value after the insert operation. This works fine for SQL Server 2000. On SQL Server 2005 this only works if I use a table in the select statement. If I use a view in the select statement, ADO returns no value for the identity column, a trace with profiler shows that there is no Select @@IDENTITY statement.
What is the reason for this behavior?
How can I change this behavior in SQL 2005 so that the behavior is the same as in SQL Server 2000?
i am migrating one database from SQL Server 2008 where I have around 20 databases to SQLServer 2012. To migrate the login I was thinking about to use SP_Help <g class="gr_ gr_114 gr-alert gr_spell ContextualSpelling ins-del multiReplace" data-gr-id="114" id="114">revlogin</g> store procedure but now what I believe is this SP scripts out whole server level login and I don't need all the logins in the server except related to the database that I am migrating to.
I want to know if it is possible to connect from classic ASP to a SQL 2005 database, and if so, can someone provide a sample connection string, I'm not sure if it is my connection string or server config. Thanks, Ed.
I am trying to return multiple recordsets to a classic ASP web page from SQL Server 2005 and then use GetRows() in ASP to fill 2 arrays with the data. I'm using a command object to run a stored procedure which performs 2 simple selects in SQL Server. The stored procedure works fine in SQL Server Management Studio so I'm guessing it's an ADO issue.
The ASP code looks like this: strConnect = "DRIVER={SQL Native Client};SERVER=MyServer.IsAtMyIsp.com;DATABASE=MyDb;UID=Me;PWD=MyPwd; MARS Connection=True;"
Set conn = Server.CreateObject("ADODB.Connection") conn.ConnectionString = strConnect conn.Open
Set objCommand = Server.CreateObject("ADODB.Command") Set objRecordset = Server.CreateObject("ADODB.Recordset") Set objRs = Server.CreateObject("ADODB.Recordset")
If Len(strBeginDate) And IsDate(strBeginDate) Then .Parameters.Append .CreateParameter("@i_DateStart",adDate,adParamInput,,strBeginDate) If Len(strEndDate) And IsDate(strEndDate) Then .Parameters.Append .CreateParameter("@i_DateEnd",adDate,adParamInput,,strEndDate) End If End If End With
I'm considering shifting my database server to 64-bit MS SQL Server 2005 for improved scalability and performance. I'm concerned, however, that my classic ASP website (which sits on a separate server) may have problems communicating via ADO/OLEDB because of communication problems between 32-bit IIS on the web server and 64-bit MS SQL Server on the database server.
My current set up (which works fine) is:
Web Server: Windows Server 2003, Standard Edition, SP1 - running IIS with a set of ASP websites Database Server: Windows 2000 SP4, running MS SQL Server 2000
Connection String:
MyConnection="Provider=SQLOLEDB;Network Library=DBMSSOCN;SERVER=192.168.0.1;INITIAL CATALOG=MyDatabase;UID=MyUserID;PWD=MyPassword" Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open MyConnection
My core question is:
If I change my database server to new machine with 64-bit Windows Server 2003 running 64-bit MS SQL Server 2005, will my (32-bit) web server be able to connect from ASP as it does now?
iam having vs2005 installed on my machine and i also installed sqlserver2005 dev edition on my machine...Now iam devloping a Window Appplication in which I need to populate a grid from Sqlserver2000 (dev edition) which installed on remote (i.e another network)....when i try to run my application it gives me a error tht "Unable to connect to sqlserver2005".actaully i specified in my connection string to connect to Sqlserver2000 on the remote machine.
I feel to know how to enable remote connection on Sqlserver 2000.So tht i can access this sqlserver2000 from another machine
I migrated my SQL Server 7 database to the new SQL Server 2005 that I installed on my PC. I have classic ASP programs on my PC that used to access the SQL Server 7 database. However, global.asa and these ASP programs can no longer connect to the new SS 2005 database.
Hello, I have the following problem with MS SQL and ASP: When adding a new code with the code below I need to get the automatically generated ID. I know there are threads discussing this but I have found none which discuss it when using the rs.AddNew method. I don't think "SELECT @@IDENTITY" works for me. Here's the code:
Code:
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorType = 2 rs.LockType = 3
StrSQL = "SELECT * FROM table1"
rs.Open strSQL, objConn rs.Addnew
rs("Field1") = "Value1" rs("Field2") = "Value2"
rs.Update
rs.Movelast RowID = rs("ID")
rs.Close
I though the rs.Movelast will work and it did most of the time, but I found that it does not work ALL of the time. What happens is that sometimes a record may be added before the last record in the table and "RowID" would actually contain that records ID. So I need your help. Thanks
I had to change the key columns of a dimension attribute to fix an error. I did this in BIDS. The change was from a single key column to a composite key column. Now I am getting these error when I process the cube:
Measure group attribute key column x does not match source attribute ..
I looked at the cube XMLA definition under mesaure groups and it still shows a single key column with inherited binding. However, the BIDS does not give me an option correct this in any way. I have had to do this once before and the only option seems to be removing the dimension from the cube and add it back in. But that is very error prone since I lose any specific settings at the cube dimension level not to mention aggregations no longer include the dimension, etc.
Not seeing an alternative, I went through each measure group (I have 7) and changed the key columns manually in the XMLA and saved the cube. This worked, but I don't understand why BIDS automatically doesn't do it.
Is this a flaw in the BIDS or I should be missing something.
Hi,We have been using ADO and the AddNew method for a long time as a meansto add records to the database. It has always worked fine - no problem.But - we recently started using INSERT triggers that simply call a fewstored procs (they're actually SSNS stored procs that send new eventinfo to notification services). Anyway, these triggers do not seem tofire at all! If I execute an insert command manually from QueryAnalyser, no problems. But the trigger does not fire at all from myapplication!Does anyone know why this could be? For info, my connection string usedby the ADO connection object looks like this: Provider=SQLOLEDB.1;DataSource=XXX;Initial Catalog=YYYAnd my AddRecord ADO code looks like this:With rs.Open sSQL, ConnectionString, adOpenKeyset, adLockOptimistic,adCmdTable And adExecuteNoRecords.AddNewAm I mnissing something obvious here? Any help appreciated!
I have a developer who built an application using the SSE 2005 December CTP and I would like to bring the db's into the official SSE 2005 released version. I have attempted the following:
1. Create the db's in the released version, detach and re-attach the CTP db's. I got an error telling me they were incompatible.
2. Installed CTP version on one server, backed up the db's and then attempted to restore the db's on another server running the official released version of SSE 2005. Got an error attempting that too.
3. Installed the Management Studio for SS 2005 Developer edition thinking that I could connect to the two seperate instances of each server and like I could in EM 2000 run the transfer wizard to move the db's and data from one server to the next. Problem with this is that I can't get an object browser connection established. I have TCP/IP all config'd correctly, too.
Any other ideas where I can go with this at this time?
I make a Prerequisites Setup to my application to install SQL Server 2005 Express.
I found that the included file (C:Program FilesMicrosoft Visual Studio 8SDKv2.0BootStrapperPackagesSqlExpressenSQLEXPR32.EXE) version is 9.0.1399.6 there is an xml file with the name package.xml which contain a PublicKey
later I download a new version 9.0.3042.0. from internet
then I replace the the old file with the new one and after rebuilding Prerequisites Setup, I got this error
Prerequisites Setup Warning 1 The value of the 'PublicKey' attribute in 'SQL Server 2005 Express Edition' does not match that of file 'C:Program FilesMicrosoft Visual Studio 8SDKv2.0BootstrapperPackagesSqlExpressensqlexpr32.exe'. C: estingPrerequisites SetupPrerequisites Setup.vdproj Prerequisites Setup
When I install my application, Prerequisites fail to install
How can I got out from this bug and keep the new version ??
I hope someone can help me in this problem. I create the Procedure to generate the dynamic SQL statement, and execute the dynamic SQL at end of the Procedure. When I done in Server Management Studio, all thing is OK, return me the record.
Hey y'all, I'm looking to migrate several databases on SQL 7 and SQL 2000 to a new SQL 2005 box. Has anyone done such a migration? What would you recommend as a good methodology for it? Also, should I expect stored procedures, triggers, indexes, other complementary stuff to break? I'd love to hear about your migration experiences.
We are planning on upgrading a SQL 7 Server to 2005 SP1. I ran the Update Advisor and one of the issues listed was that the Database Maintenance Plan would not work in Server 2005. The Advisor mentions a tool to use but not which one, which is my first question.
I went online and found some documentation regarding copying the database etc., however, when attempting to copy from the SQL 2000 (DB in SQL 7) to 2005 (I have SQL 2000 and 2005 on my machine) when I try and connect to the 2005 box, its listed as one of the instances but will not connect.
For grins and kicks I backed the database up in the 2000 environment and restored it on a test machine which is in 2005. The Advisor was correct, I am unable to create a Database Maintenance Plan, which I find odd. The Database shows up under the databases but will not show up in the list of db's to create a plan for. So, I attempted to create the jobs and alerts manually.
There has to be an easier way to do this, can anyone help?
I have a SQL7 server that runs one database for our company. The rest of the datastore is on a SQL2005 server. How best can I transfer the SQL7 database to SQL2005?????
Does anyone know how I can do this? I have 2 tables in an ODBC datasource (INV HEADER) and (INV DETAILS). The relationship on these 2 tables is (INVNUM). I want to import these tables into SQL 2005 on a nightly basis by date. The problem is the date field is on the (INV HEADER) table and not the (INV DETAILS) table. Basically I want to import all the (INV DETAILS) rows that have the same (INVNUM) as the (INVHEADER) but don't know how to do this. I could use a join on the source tables but how would I direct specific columns to 2 different destinations? Any help on this would be great and appreciated. Thanks
Hi,I move my website to our production server who use SQL 2005 instead of SQL Express.The website was, of course, developped with SQL Express.Where can i find the steps that i need to configure the SQL 2005 server for working with my SQL Express database.Thanks
I'm in need of migrating DB's from a SQL 2000 server to a new SQL 2005 server. Has anyone had any success in this or does anyone know of a process which would be best for this situation?
Here's my setup...
SQL2000 SERVER....OS - Server 2000 MDF's/LDF's reside on D:MSSQLData Backups reside on D:MSSQLBackup
SQL2005 SERVER....OS - Server 2003 MDF's/LDF's reside on E:MSSQLData Backups reside on E:MSSQLBackup
Any information on how to get these DB from 2000 to 2005 will be greatly appreciated.
Hi, since havenot done migration yet, my company is planning to migrate from sql 2000 to sql 2005 server. and i am in group for capacity planning. can anybody suggest me the steps i should be really taking in order to migrate to 2005, if anyone has done the it in real world with database up to 50g. thanks Derek
Guys! Very very junior in this migration part. Hope the seniors can help out....PLEASE!!!!!! Don't get me wrong asking you guys to spoon feed me. I have read a lot of articles and even BOL and also not to forget read millions of books on this. But just wanted to make sure that I am in the right path and want to do this without any problem. That's why I have decided to ask the Gurus here who have done this in past.
I have this machine now:
Microsoft SQL Server 2000 - 8.00.2040 (Intel X86) Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: )
Migrating to this machine:
Microsoft SQL Server 2005 - (X64) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I have these in the SQL Server 2000 box: 1. Databases, tables (of course) 2. Views 3. Securities(Logins, Roles, Password) 4. DTS packages 5. Jobs 6. Replications 7. Linked Servers 8. Stored Procedures
Every article is different. I was confused at a point of time. Please show me the right way guys!
Yesterday I started this topic and I came step by step higher and now I have finalized my steps to migrate.
Seniors and Gurus and who has done this before, please review and correct me where I am wrong and/or add some tips for me to have this one going on successfully! Thanking all of you guys in advance! Here is what I have:
Before Migrating: 1. Install Service Packs, Security Patches and Hot Fixes 2. Install DTS Designer Tool from Microsoft to prevent conflict and failure of migrating current DTS packages to SQL Server 2005 3. Backup EACH and EVERY Database and double check the .bak files whether it exist and backup was successful 4. Just before migrating, reboot the server to clarify that the SQL Server Services and SQL Server Agent is working When you are ready to migrate: 5. Make sure there are no users trying to connect to the OLD AND the NEW SQL Server. Inform them well in advance about the downtime. Shutdown any applications or websites that might be accessing the SQL Server. 6. Stop the SQL Server, SQL Agent and MS Search Services on the NEW SQL Server If downtime is not possible: 7. For ever changing OLTP database, by the time you restore the backup onto the new server, the old server would have received more changes. To avoid this problem, you need to resort to log shipping. Log shipping is nothing but an automated process of backup and restore. The following steps will explain the process of log shipping (Note that SQL Server 2000 Enterprise Edition provides inbuilt logs hipping. We don't need that for this purpose. We can simply create a job that backs up and restores the databases 8. Create a job on the old server, with two steps. The first steps backs up the transaction log of the OLTP database. The second step restores this transaction log backup file onto the new server, using WITH STANDBY or WITH NORECOVERY option of the RESTORE command. Schedule this job to run every 10 minutes or 15 minutes, depending on the volume of transactions your OLTP database receives 9. This job will take care of shipping all new transactions since the full database backup, to the database on the new server. Let this job run until you can afford some downtime on your production database (that is, mid-night, or early morning, or a planned maintenance window) 10. Now to be doubly sure, no new transactions are coming in, set the database in 'single user' or 'dbo use only' mode. Take one last transaction log backup of the production OLTP database, and restore it onto the database on new server. This time use the WITH RECOVERY option of the RESTORE command 11. Run UPDATE_STATISTIC on User Database to reorganize all indexes Considerations: 12. Security - The security model changed along with object ownership, but users that own objects will be turned into schemas and all of the object and role permissions will come over. These you will want to look over afterwards, but they should be ok. 13. Unless you have hundreds of them, it is always better to re-create them after upgrading. You cannot detach an MSDB database on a 2000 server and attach it on a 2005 server and have it work. Fortunate in this, we can script out the jobs from SQL Server 2000 and run it on the new SQL Server 2005 14. Typically you will have to re-create your replication too. Again, the good news is that you can generate the scripts for replication on a 2000 server and execute them on a 2005 server and it will create the appropriate articles and publications 15. For DTS packages, it is always recommended to use the Package Migration wizard to do this so that less or no problems will be encountered. Consider about the jobs previously set. 16. Logins can be created by using sp_hexadecimal and sp_revlogin2005
Help. I have been tasked with upgrading a 2000 instance to 2005.I have actually done this before, but it was a long time ago, and Ididn't do it alone. Now, the sitation is little different, and I needto know the EXACT steps to take.Does anyone have a FAQ or link that outlines migration steps? I foundone on sql server central, but it isn't very detailed.One of the important things I need to know is, how do I create arollback plan if I am upgrading from 2000 to 2005 on the same server(instance)?Also, why can't I seem to find a comprehensive list of TO DO's whenupgrading? Doesn't microsoft provide this? You would think so. I willrun upgrade advisor first, but isn't there also documentationsomewhere?I seem to recall lots of permissions issues that arose with 2005.HELPThanks
Hello guys, I have vry basic question. I'm migrating from 2000 to 2005. MY question is do we have to recode all packages or just migrating of old 2000 DTS is fine. If so, then I can see only small icon of DTS package in 2005, whihc is not showing the details of DTS. Kindly, explain me what do i really do, migrating old one or re-creating new in 2005. Thanks in Advance
