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 ...


24

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 ...


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 ...


16

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 ...


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 ...


14

This issue is called parameter sniffing. Later versions of SQL Server give you more options in dealing with it such as OPTION (RECOMPILE) or OPTIMIZE FOR hints. You might try declaring variables in the stored procedure, assigning the parameter values to the variables and using the variables in place of the parameters as it sounds as though most of the time ...


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.


11

You can turn on trace flag 7300 which might give you a more detailed error message How many rows does a representative query return? How fast/reliable is the network connection between the two servers? It's possible that a large dataset is taking too long to transfer (on top of the actual query time). You could raise the timeout value. You can try to ...


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

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

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

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

This is a reasonably well-known issue with SQL Server 2000 - essentially, what happens is if a row gets deleted by process A while process B is doing a scan (either at READ UNCOMMITTED or WITH (NOLOCK)), then process B goes "huh what happened to this data" when it tries to read it. More precisely, the row has to be deleted after process B reads the index, ...


7

Quite honestly, I think your easiest approach will be: backup your user databases uninstall SQL Server reinstall SQL Server with the right collation restore your databases fix the collation on the user databases Also you know that SQL Server 2000 is well out of mainstream maintenance, right? And that quite soon there will have been FOUR major releases ...


7

I would suggest using the SQL Server Counters in the Windows PerfMon Utility. This is for SQL 2005 but the instructions provide the gist of things for you.


7

For SQL Server 2000, I quite honestly wouldn't bother trying to get operators to work. SQL Mail is a royal PITA and requires Outlook or a similar mail client to be installed on the server. I would rather just setup each job to have a step called "mail on failure", which uses a token to identify the job, and then calls a stored procedure. You would only reach ...


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 ...


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