Hi
I have a question about change of compability.
I want to change compability level from 2000 to 2005 on a very large database with a lot of indexes. I have heard that if I change compability level the indexes will no longer be usable.
Is it enough to just rebuild all indexed after I have changed compability level or do I have to do something else?
I am in the process of migrating from Sql Server 2000 to 2005. Part of my plan is to move some database's to 2005, but use the 2000 compatibility mode for the short term. My issue is this, our DR boxes are still on SQL Server 2000, would I still be able to use our log shipping processes? Or would I be better off in starting with migrating the DR boxes to 2005 first?
Thanks in advance.
Hi,
I have a database that is in the SQL Server 2000 compatability mode on my SQL 2005 server. I am trying to restore a backup of this database on my SQL Server 2000 database on another server and keep getting strange messages. First trying Red-Gate and then plain SQL Server with no luck.
It seems as I remember that the 7.0 and 2000 compatibility issue between backups did not arise when the compatibility mode was set to 7.0.
Is this "planned" behavior or an "undocumented feature"??? Anyone else have success doing this?
Thanks,
John Campbell
Hi All
I€™m having a problem setting up maintenance plans on one of our SQL 2005 servers but first here€™s a bit of back ground.
The server is a Virtual machine running win2k3R2 SP2 server std and SQL 2k5, A supplier arrived to install and app on the server when discovering there app didn€™t work with 2k5 they seem to have set the 2 dbs on the server into 2k compatibility mode and set the entire SQL install to think its a 2000 install. (I€™m not sure how this is done all I can see is the wrong version number in management studio) The problem this is giving me it that the folder in management studio for Database Maintenance Plans is no longer under the management folder instead it under a sub folder called Legacy (which is how management studio seems to deal with all registered 2000 instances) and as such a can no longer right click and create new database maintenance plans is there any way around this or will I have to setup my backup job manually?
Ohh and have the supplier shot at dawn
Thanks All
Jon
Can you run SQL Server 2005 in SQL Server 2000 compatibility mode and still use it with Reporting Services 2005?
View 4 Replies View RelatedHi:
The first stmts in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_06_35bq.asp are
"Autocommit mode is the default transaction management mode of Microsoft® SQL Server„¢. Every Transact-SQL statement is committed or rolled back when it completes."
"if a statement completes successfully, it is committed; if it encounters any error, it is rolled back. A SQL Server connection operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions."
My question is, how do we change autocommit mode. I don't want the dml to be commited until i explicitly do commit. This should be default setting.
I want this change either to be made at database level, maybe by doing some setting or through some T-SQL stmt.
I DO NOT want to use anything that has to be done in query windows like set ... off | on in each and every query window i use.
The change has to be done only once and should persist through-out.
Any solution will be of help to me.
Thanking you,
Regards,
kumar
How can I change the authentication mode for sqlexpress without going through the UI? I looked at the docs for sp_configure, dmo and smo but I may have missed it. I'd like to be able to run a script that changes the authentication mode of an installed sqlexpress instance from windows only to moxed mode.
Thanks
Hi all,
here is the context of our problem:
- Publisher :
version SQL Server 2005 SP2
table in SQL Server 2000 (80) compatibility level
publication in SQL Server 2000 compatibility level
- Distributor : SQL Server 2005 SP2
- Subscriber : SQL Server 2000 SP4 + hotfixes (version 8.00.2187)
There is only one article in our publication (a simple table with a GUID and a nvarchar(50) columns), and we have left the default resolver.
1 - The first synchronization of the subscription is successfully completed, and it is the same for the different updates/inserts/deletes or conflicts.
2 - Then we change the Resolver of our article by our own COM-Based Custom Resolver (developped in C# 2.0). This resolver only change the default behaviour: the subscriber always win (this is for a test, in the future we will have a complex business logic). All the synchronizations works fine and do what we want in the conflicts.
3 - We rollback the resolver to the default one... and here is the problem!
The synchonizations stop to work correctly. For all of them we've got the same error:
Code SnippetReplprov.dll , 2007/09/25 14:26:07.591, 4000, 16890, S1, ERROR: ErrNo = 0x80045017, ErrSrc = <null>, ErrType = 8, ErrStr = The schema script '
declare @cmd nvarchar(1000)
set @cmd='exec sys.sp_MSchangearticleresolver @article_resolver=@ar, @resolver_clsid=@rc, @artid=@ai, @resolver_info=@ri'
exec dbo.sp_executesql @cmd, N'@ar> nvarchar(255),@rc nvarchar(40),@ai uniqueidentifie..." mailto:N?@ar">N'@armailto:N'@ar">N'@ar</A< A>> nvarchar(255),@rc nvarchar(40),@ai uniqueidentifie...
It is no more possible to synchronize this subscription... Any remark will be helpfull cause we did not find anything on the net concerning this error.
Thanks a lot!
Hi,
What is the impact of changing the recovery mode of a database from Full to simple? The client I am at has set their database to full recovery mode, set their log files to grow automatically. But I don't think they have ever done a backup of their transaction log (it has grown to over 19Gig, where the data portion of the database is only around 400M).
What is the impact of truncating the transaction log now? After truncating it, i would like to shrink the file to a managable number and change the recovery mode to simple (they don't need transaction log backups)
Any info on this would be appreciated.
Thanks in advance
Jim
I have a 2005 database sitting on a 2012 server, we're looking to change its compatibility mode from 90 to 110 so we can avail of what 2012 offers.I did a migration project a couple years ago for SQL Server 2000 databases changing to 2008 R2 and we ran into loads of problems where we left most databases in compatibility mode 80 as these were application databases that the owners couldn't stand over in terms of deprecated code / features no longer in use in 2008 R2.
From what I can see with changing from 90 to 110, there doesn't seem to be as many issues but I just want to double check if there's a way to confirm this. I know upgrade advisor is a handy tool but will it pick up database specific issues as opposed to database server compatibility issues? URLs...I know UA won't cover all the bases but it would look good when attached to the report I'm submitting recommending the change.
Hi all,
This is my first post and I am a newbie user of SQL Server as well. I am using SQL Server 2005 Express edition. I have also installed SQL Server 2005 Mangement Studio express.
Initially I was under Windows Authetication but today I changed it to mix mode. After doing everything and enabling the authentication for the "sa" user I am getting the following error.
In the login dialog window for the user name I am providing: sa
TITLE: Connect to Server
------------------------------
Cannot connect to WC-D9A44337398FSQLEXPRESS.
------------------------------
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
When I try the second time it gives this error:
TITLE: Connect to Server
------------------------------
Cannot connect to WC-D9A44337398FSQLEXPRESS.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
However I am still able to login through the Mangement Studio through the Windows Authentication.
Any help is greatly appreciated
Durning install I selected Window's Authentication only, but now it seems we may need to use a Mixed Mode with an SA account etc... is there anyway to switch SQL 2005 to use Mixed Mode after the fact?
View 1 Replies View RelatedHi,
In sql 2000 was possible to copy entire database objects from one db to another. This includes tables, pk, indexes, views, sp, functions, logins and so on.
This is very usefull because at the same time it creates a full log with entire database scripting, it can be used to track changes in databases.
In sql 2005, I can't find a solution like this.
Someone have an idea to this?
I have tried everything I found on Google, but nothing seems to be working. Already Ran alter command set multi_user, but getting an error.
"Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1..ALTER DATABASE statement failed."
Hello,
In a Data Flow Task, I have an insert that occurs into a SQL Server 2000 table from a fixed width flat file. The SQL Server table that the data goes into is accessed through an OLE DB connection manager that uses the Native OLE DBMicrosoft OLE DB Provider for SQL Server.
In the OLE DB Destination, I changed the access mode from Table or View - fast load to Table or View because I needed to implement OLE DB Destination Error Output. The Error output goes to a SQL Server 2000 table that uses the same connection manager.
The OLE DB Destination Editor Error Output 'Error' option is configured to 'Redirect' the row. 'Set this value to selected cells' is set to 'Fail component'.
Was changing the access mode the simple reason why the insert from the flat file takes so much longer, or could there be other problems?
Thank you for your help!
cdun2
SQL 200 introduce changes to licensing. There are two licensing modes, Per Seat and Per Processor.
Is there a way to change the licensing mode from Per Seat to Per Processor for a current SQL Installation
Do you know of any resources in terms of Transact SQL in order to migrate from 6.5 to 2000?
View 1 Replies View RelatedI have gotten my production version of SQL locked into single user mode. We recently upgraded to a clustered configeration and when I went to pause the system to get control I was told Pause was not available in a clustered environment. I restarted the service with -m [single user] but couldn't run Enterprise Manager as it appeared to take the only user slot and not give me one. Then I hit on startingup with Query Analyzer and that worked but now how do I get it back into multi-user mode. I placed the -m in the settings using Enterprise manager; could I stop the service and restart at the cmd prompt using -f [minimal config] and get Enterprise manager running? Any assistance appreciated.
View 4 Replies View RelatedHi all:
I know i can use the sentence SET IMPLICIT_TRANSACTIONS ON in a Stored Procedure to force SQL Server to set the connection into implicit transaction mode.
Have i a sentence or configuration to force all SQL Server connections to implicit transaction mode?
Thanks in advance.
hi all,
one of my sql database went in suspect mode can any one advice why it has happened is it the problem with sql server . This has happend for the second time in 4 months .
thanks and regards
jk
SQL Users/DBAs,I'm trying to move data files around for a database that is in standbymode. I can detach/attach the database fine usingSP_DETACH_DB/SP_ATTACH_DB , but when I re-attach the database the logsequence is broken and I can't restore any more logs. Does anyoneknow if there is a way to move around data files and keep the databasein standby mode?Thanks,JB
View 4 Replies View RelatedHi all,
I have a customer with an RS installation in a DMZ, using ADAM for its authentication.
The problem I have is as follows :
When setting up the execution log reporting (supplied as part of the standard install) all information being captured for report execution e.g Which user ran a specific report and when, is coming up with 'UNKNOWN' for the user name.
Does anyone have any idea where to look to fix the recording of user information? I am not sure whether it is a RS or A.D.A.M issue.
Thanks
Clint Pugh
Hello everyone,
We have SQL server 2000 on windows 2000. From our desktop MS Access was connected to database through ODBC. Since yesterday (after our consultant had updated another asp application in the same server) we are getting €œSQL Server Error: 18452. €¦.. Login failed for user €˜(null)€™. Reason: not associated with a trusted SQL Server connection. €œ
The consultant saying he did not touch any settings on server (?). And the problem is even server€™s security property shows it has mixed mode authentication. But I cannot connect to server even through my SQL server enterprise manager in windows authentication mode. I can connect if I use SQL server authentication. Through ODBC I can connect also and test fine, if I use SQL authentication. But the Access application wouldn€™t connect using SQL authentication in ODBC.
So, can any one help me to find out what else could be done to make sure that the server is going to accept windows authentication. I€™m thinking any other settings or registry change €¦ etc. Please help.
Thanks
My company has recently sold a part of its business, and along with that business goes a live SQL 2000 server. However, the license for said SQL Server was not part of the sale. So, to make a long story short I've been tasked with finding a way to A) change the license key of the existing installation to the new owner's license key while B) keeping the existing installation intact and running.
Now I've done enough digging to know exactly where the key info is kept in the registry (in HKEY_LOCAL_MACHINESoftwareMicrosoftMicrosoft SQL ServerRegistration), but I'm more than a bit leery of changing the key at the registry level without confirmation that it will work. I do have a test server I can use as a test if necessary, but I'd prefer to get more feedback before I begin trials.
Thank you in advance
Hi.
I don't know how to change the collation in SQL Server 2000, my collation is Modern_Spanish_CI_AS, if somedoby knows the procedure to change the collation I would be thankful.
Thank you again and best regards.
Christian
Dear All,I have one field in my table which shows the day of the week. It isshowingSunday = 1Monday = 2Tuesday = 3But i want Monday is the first day. I know i can use DATEFIRST tochange it but it works in Query Analyzerbut when i come and see mytable it is showing the old settings. Is it possible i can change itpermanently. Any help in this regard will be higly appreciatedRegardsS
View 2 Replies View RelatedWhy is it that when you change a field name is SQL Server it sometimes completely messes things up. I renamed a field in one of my tables from Emp# to EmpNumber. I had a view based on this table and naturally I knew I would have to change a view I had based on the table. I opened the view and changed the field from Emp# to EmpNumber but when I tried to open the view I got an error “Invalid column Name EMP#”. I have not been able to fix this. I have dropped and recreated the view, refreshed all the objects using enterprise manager, refreshed all the objects using Query Analyzer, shut down and restarted my computer, taken my database offline and put it back on line. The field name EMP# is not in any tables in my database and not referenced any views or procs . I am just starting on this database so I could verify this very easily because I only have a few views and procs.
Has anyone had this problem or more importantly does anyone know why this is happening or how to fix it?
Greetings,
I have been tasked with changing our local domian name from .com to .local. I want to make sure I understand the risks to SQL server 2000 when i make this change. We use SQL for Great Plains version 8, here is my simple plan:
1. Dis-join all workstations from .com domain
2. Make a full backup of all databases iin SQL server 2000 -- all databases use the SA account and not NT authentication
3. Dis-join SQL server
4. Change domain name
5. Re-join SQL server box and workstations
6. Launch Great Plains and go home happy!
I am having trouble identifying the risk to my plan and am wondering if:
1. SQL will launch under the new domain
2. Will the backup I made restore under the new domian
3. Will I experience authentication problems even though we use the SA account?
I am not a DB admin and am feeling a little unsure about this task any help on the risks or links to "how to" guides would be appreciated.
Robert
Hi fellow techs! I am getting to know SQL 2000 intimately and it's been a fun ride with the top down!
My question is, are there any gotchas that I should be aware of in SQL 2000 if I want to change 3 of my databases running on one 2000 SQL server from simple to full recovery model? I want to begin backing up the log file for point in time purposes, but don't want anything to break by disregarding the gotchas, if there are any.
Thanks!
Hi
We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.
If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?
Please help us.
Regards,
Sankar N
Hi,We currently have a fairly old product, which was originally onlycompatible with SQL 2000.When we upgraded our server to SQL 2005, the client product gave us a"This product is not compatible with this version of SQL server"error.We put the server into SQL 2000 compatability mode, and things worked.Then I tested a Windows Vista workstation client. The error returned.I realise that if we are simply dealing with a client incompatabilitywith Vista, then I'm in the wrong forum, but what I'm hoping is thatsomeone knows anything server side, perhaps where SQL 2000compatability mode becomes ignored for some reason, when the client isrunning Vista.The vendor pretty much no longer exists, and nothing I've been able todo with Vista's compatability modes has been helpful so far.
View 1 Replies View RelatedHello...
When I migrated data from one SQL Server to another I got collection problems because collation of the target server was different from the source one.
The best solution I thought about was to change collation of the database in target server to be equal to the server collation so that when a temporary table is created, and the collation used would be the server collation, no error would occur. All sounds logic, but, after I ran ALTER DATABASE command and changed the collation of the database, I verified that all varchar fields of all database tables retained the old collation, not the new database collation I set.
Is there any way to change the collation of all fields at once when I change the database collation?
Thanks for your help
Jaime
We have reports in SharePoint integrated mode which are really slow when compared to native mode. I have been asked to research and give info on what exactly causes the delays.
Any articles which give me information as to what happens when a report is run from SharePoint server and where does it log.