37

The foremost step to do is to run the Upgrade Advisor on SQL Server 2000 database and address all the issues reported by it. As a best practice, use the Upgrade Advisor tool on your SQL Server 2000 legacy database and import a trace file to the Upgrade Advisor tool for analysis. The trace file lets the Upgrade Advisor detect issues that might not show up ...


25

Clearly there isn't a nested CASE expression here. Not in the query text, no. But the parser always expands CASE expressions to the nested form: SELECT CASE SUBSTRING(p.Name, 1, 1) WHEN 'a' THEN '1' WHEN 'b' THEN '2' WHEN 'c' THEN '3' WHEN 'd' THEN '4' WHEN 'e' THEN '5' WHEN 'f' THEN '6' WHEN 'g'...


23

If you want to go back to using the dbo schema like you were in SQL Server 2000, you can move the table back into the dbo schema: ALTER SCHEMA dbo TRANSFER erpadmin.tablename; An alternative if you like having the non-dbo schema is to set your user's default schema to erpadmin then if you do not specify a schema, it will use that as default. (Members of ...


21

No, there is no workaround to upgrading a database directly from 2000 to 2012. Since you don't have a ton of data, you can do all kinds of things to move the data (but not the database as a whole), including: Import/Export wizard SSIS BCP Manual queries using a linked server from 2012 or an application However these will not necessarily bring over other ...


21

I know of no hacks to get SQL Server 2000 to install on an existing Windows 10 installation. There are probably forces against you from both within the setup program (which looks for certain things on the host system) and within the operating system (which prevents many flavors of older software from getting installed, mostly for security reasons but also ...


20

If you are looking for a function to block the execution batch for a specified amount of time, you can use WAITFOR in SQL Server 2000. This function is similar to SLEEP function in Oracle. Usage examples: WAITFOR DELAY '00:00:30'; -- wait for 30 seconds WAITFOR TIME '13:30'; -- wait until server time is 13:30


18

Making SA the owner of a database actually simplifies and/or solves a number of things, but can have some security implications. In particular, remember that if SA is the owner of a database, then dbo = 'SA'. This means that, among other things, any procedures in the [dbo] schema (which is the default) that have "EXECUTE As Owner" in them, are actually ...


17

This is a classic case of why you should specify the schema name when accessing database objects. When it is unspecified and you're trying to access an object in a non-default schema then you're going to run into the issue that you're seeing right now. The real fix is to change your application (or whatever querying agent you have right now causing the ...


16

Personally, I would avoid the detach/attach mechanisms. Especially in SQL Server 2000, I just don't trust that you will always bring the server back up and be able to attach those files. I've heard plenty of stories where this didn't happen cleanly - just because you have a Plan B doesn't automatically make Plan A sensible. With backup / restore, you don't ...


15

Unfortunately if you want to be 100% accurate, the plan cache isn't going to cut it, because there are all types of scenarios in SQL Server where a plan may not get cached at all. For example, OPTION(RECOMPILE), zero cost plans, optimize for ad hoc workloads and single use plan stubs, etc. Since you want a method that works across 2000-2012, your only real ...


12

Yes, I have done it. My website http://www.rocketclubs.com is on SQL server 2000 and I updated it last night in full windows 8, SSMS 2012 setup.


10

I infer that your data looks like this: Person Table ╔══════════╦═══════╦════════╗ ║ PersonID ║ Name ║ Gender ║ ╠══════════╬═══════╬════════╣ ║ 1 ║ John ║ M ║ ║ 2 ║ Vicky ║ F ║ ║ 3 ║ Bob ║ M ║ ╚══════════╩═══════╩════════╝ Job Table ╔══════════╦═════════════╦════════════╗ ║ PersonID ║ JobName ║ HireDate ║ ╠═══...


10

You should be using the catalog views instead of the old system tables (e.g. sys.objects instead of sys.sysobjects, sys.columns instead of sys.syscolumns). The system tables still exist, but they are there for backward compatibility only. Some of them have been moved to the hidden system database, mssqlsystemresource, and/or are only visible when connecting ...


10

I managed to install following these steps: Extract SP4 Copy ..SP4\x86\other\sqlredis.exe to ..\originalinstallpath\x86\other (this avoid mdac insall freezing) Create this folder structure (any place): Microsoft SQL Server\80\Tools\Binn Microsoft SQL Server\MSSQL\Binn Find out sqlunirl.dll on SP4 path and copy to Binn folder above Copy dll files on .....


9

Yes, you absolutely CAN connect to a SQL Server 2000 instance using SSMS 2012 as a client. I do it every day as I still have 5 SQL Server 2000 instances in my environment that I manage. However, keep in mind that SSMS will present you with some options based on functions that are available in the version of SSMS you are using, and you may not be able to ...


9

What you do will depend on your SQL Server version, as well as whether you can afford to take the SQL Server service down in order to establish new credentials. The first two methods here do not require restarting the instance: For SQL Server 2005, 2008, and 2008 R2 instances You can connect using the NT AUTHORITY\SYSTEM account (or other backdoor methods). ...


9

You must move the test on beenverified from the WHERE clause to LEFT ... ON ...: SELECT sa.empname , coalesce(Da.itemssold, 0) FROM #ShowAll sa LEFT JOIN #Data da ON sa.empid = da.empid AND da.beenverified = 'Yes'; With a LEFT JOIN, when there is no match on empid between the two tables, this query (minus the WHERE clause) returns NULL for ...


8

Even though this answer has been accepted, please see Jonathan Kehayias' answer below for a much better way to do this. For SQL Server 2012, you could inspect the plan cache for the name of the view. DECLARE @FindSql nvarchar(max) = 'name_of_view'; SELECT /* cp.*, ct.* */ cp.objtype AS [Type], cp.refcounts AS ReferenceCount, cp....


8

You ave to use /!X /!C after the DTS command. /!X ==> Do not run the package /!C ==> copy the results to clipboard To avoid this, you can create a SQL Agent Job and choose CmdExec as a step type with below commandline: DTSRun /N"your package name" /S<servername> /E --- for trusted connection OR DTSRun /N"Your package Name" /S<ServerName>...


8

Ugh, 2000. Assuming all objects are owned by dbo, you can generate a script, e.g.: SELECT N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name) + N' TO [user];' FROM sysobjects WHERE type IN (N'U', N'V'); SELECT N'GRANT EXECUTE ON dbo.' + QUOTENAME(name) + N' TO [user];' FROM sysobjects WHERE type = N'P'; Now you can copy and paste the ...


8

Priority 1: Fix the table. You should not be storing date and time data, in varchar columns, and quite possibly should not be separating date and time into their own separate columns. You should also avoid reserved words/keywords as columns, but I suspect you may have just dumbed down your actual table structure. Priority 2: Stop using BETWEEN. This can ...


7

I used to move databases almost constantly, due to SAN reconfiguration and migrations. Assuming that you are moving a whole server at a time, I would go with something like your path #2. (If you are moving one database at a time, and eventually doing every database on a server, that would be more problematic since you would have to be changing paths to the ...


7

This is set by the client. It is passed in via the "Workstation ID" property in the connection string. So I presume that you will need to make some configuration updates on your client machines.


7

Bad news: SQL Server 2000 isn't officially supported. You're putting yourself in a situation where the link between these two servers could cause problems, and if you call Microsoft for support, you'll be out of luck. Don't be That Guy who breaks the production server because he wanted to connect these two servers. Tell your management that SQL Server 2000 ...


7

As addition to @AdamWenger answer. To create scripts for transfering to anothe schema you can use following script select 'ALTER SCHEMA dbo TRANSFER '+s.name+'.'+t.name from sys.schemas s join sys.tables t on t.schema_id=s.schema_id where s.name='erpadmin'


7

According to this MSDN article for using FOR XML in SQL Server 2000, "ELEMENTS Specifies that the columns are returned as subelements. Otherwise, they are mapped to XML attributes. This option is supported in AUTO mode only." So, your usage of ELEMENTS with the RAW option isn't supported in SQL Server 2000, but it does work with AUTO. Edit: If you were to ...


6

I'm not sure why you're installing SQL Server 2000 anywhere in the year 2012, but no, there shouldn't be any technical issue other than the ones you've identified and the fact that it is nowhere near supported. In any case, there is no 64-bit version of SQL Server 2000 - there is only 32-bit for x86 and 64-bit for IA. I assume your Windows Server 2008 box ...


6

Yes, I have systems with 2000 and 2012 installed. I have only tried with installing 2000 first though; I haven't been brave enough to try installing 2000 after 2012 was in place.


6

My hunch is that the query is getting re-written somewhere along the way to have a slightly different CASE structure, e.g. CASE WHEN column = 'a' THEN '1' ELSE CASE WHEN column = 'b' THEN '2' ELSE ... I believe this is a bug in whatever linked server provider you're using (in fact perhaps all of them - I've seen it reported against several). I also believe ...


Only top voted, non community-wiki answers of a minimum length are eligible