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 ...
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'
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 ...
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:
Manual queries using a linked server from 2012 or an application
However these will not necessarily bring over other ...
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 ...
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
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
I infer that your data looks like this:
║ PersonID ║ Name ║ Gender ║
║ 1 ║ John ║ M ║
║ 2 ║ Vicky ║ F ║
║ 3 ║ Bob ║ M ║
║ PersonID ║ JobName ║ HireDate ║
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 ...
I managed to install following these steps:
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 .....
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)....
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 ...
You must move the test on beenverified from the WHERE clause to LEFT ... ON ...:
, 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 ...
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';
/* cp.*, ct.* */
cp.objtype AS [Type],
cp.refcounts AS ReferenceCount,
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
DTSRun /N"Your package Name" /S<ServerName>...
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 ...
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 ...
If you don't:
it will be unmaintainable at some point because of OS, version, patch, whatever
hardware failure may force an upgrade at the wrong time
What you gain:
older code can be simplified with new constructs (eg ROW_NUMBER)
far better error handling (TRY/CATCH)
engine improvements: most queries will run quicker on a later version
MS don't support ...
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, ...
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 ...
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 ...
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 ...