Hot answers tagged sql-server-2000
16
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 ...
13
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 ...
11
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 ...
11
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 ...
11
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 ...
8
We talked about a similar issue before here and here and what worked in that case was setting the SQL Server to be dependent on the disk drivers. You'll want to test this to make sure this works in your setup but it should do the trick.
7
You can use DBCC SQLPERF("waitstats"). This will return the wait times of what tasks your SQL server was waiting on. Detailed explanations of each counter can be found online. You can use this information to find out your bottlenecks.
Also, turn on the client statistics in query analyzer to see the wait times on the client side.
I am assuming you hardware ...
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 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
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'
...
6
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 ...
6
If you can plan some downtime, I would unattach the database from the server, move the physical files and then reattach the database.
Right click on the database --> all tasks --> detach database.
Move files
Right click on 'Databases' --> all tasks --> attach database.
The other option you have is to add another data file on the b: drive (right click on ...
6
SQL Server 2000 has ALTER TABLE foo ENABLE TRIGGER ALL too
And to generate the required ALTER TABLE script use this
SELECT
DISTINCT 'ALTER TABLE ' + OBJECT_NAME(parent_obj) + ' /*DISABLE*/ ENABLE TRIGGER ALL'
FROM
sysobjects
WHERE
xtype = 'TR'
I wouldn't bother testing status of the triggers, personally. A disabled trigger should not exist ...
5
You can import the old DTS packages into SQL Server 2008 using the DTS Migration Wizard. You can find it on the SQL 2008 Management Studio at the following folder: Server -> Management -> Legacy -> Data Transformation Services - Migration Wizard. Point to the current source of the packages and this should help you do your job.
You should be able to find ...
5
SELECT
j.[name]
, jh.run_date
, jh.run_time
, jh.sql_severity
, jh.message
FROM
msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j
ON j.job_id = jh.job_id
WHERE
jh.run_status = 0 -- Failure
AND jh.run_date > DATEADD(DAY, -1, GETDATE())
ORDER BY
jh.run_date DESC
5
Have you tried with backup and restore? Make a backup in SQL Server 2005 and use it to restore the db to the R2 instance. At the end of the restore it should be upgraded automatically.
Or is it any different for SBS 2003 and can't make a backup?
To be able to transfer also the logins you could get help from the following KB articles:
How to transfer ...
5
Whichever method you use, you'll need to be aware of any restrictions on SQL Server 2008 R2 Express that might be a show-stopper eg 10GB maximum size of any one database, single CPU, memory restrictions etc
Ideally, you'll also have tested how the application works with the new version of SQL Server. If it's a vendor product, do they support the new SQL ...
5
Instead of using dynamic SQL, you could always just change your proc calls to:
EXEC Database.dbo.usp_Myprocedure 'Parameter' WITH RECOMPILE
The WITH RECOMPILE forces (you guessed it!) a recompile of the execution plan whenever it is run.
You can also include WITH RECOMPILE in the definition of the stored proc:
CREATE PROCEDURE usp.MyProcedure ...
5
Since you are using the Backup/Restore method, you don't need to copy the database (mdf) and log file (ldf) to the new server, just the backup files. You do not need to recreate the database either. The database, database files and log file will all be created during the restore process. The upgrade process is done by SQL Server when restoring your database ...
5
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 ...
5
You don't have to perform any task (destructive or non-destructive) at all.
You can use the built-in function IS_SRVROLEMEMBER to find if a certain LOGIN is a memeber of the sysadmin server role:
SELECT IS_SRVROLEMEMBER('sysadmin','<LoginName>');
Note that for roles other than sysadmin the result will be positive (=1) for implicit membership ...
5
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 ║
...
5
One way (SQL Fiddle)
SELECT SName,
SId,
LEFT(SName, MAX(LEN(PName))) AS PName,
CAST(SUBSTRING(MAX(STR(LEN(PName), 10) + LTRIM(PId)), 11, 10) AS INT) AS PId
FROM Sources S
JOIN Prefixes P
ON S.SName LIKE P.PName + '%'
GROUP BY SId,
SName
ORDER BY SId
I am ...
5
As stated on MSDN side-by-side installation of SQL 2012 with earlier version is supported. However take note of the instance name, if your SQL 2000 instance is a default instance then your SQL 2012 install will have to be a named instance.
I would take note also that some issues may occur with SQL 2000 tools as your SQL Server 2012 installation may attempt ...
5
As stated in the comments, dates are not stored as "dates". They are actually stored as numbers. So there is no need to worry about that side of things. You can change the default output though by changing your language setting.
EXEC sp_configure 'default language', '23';
GO
RECONFIGURE
GO
This is the setting for British English. You can see all of ...
5
High CPU in SQL Server is very often caused by poor indexing
Unfortunately, SQL Server 2000 lacks the tools of later versions to track these down easily
Saying that, if you run SQL Profiler you will be able to find high CPU queries and start looking at query plans to work out what indexes are missing
4
You can the steps mentioned in the link below to reset the SA password:
http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx
4
This forum or ServerFault.
In the 13 years i've been working with SQL Server since 6.5 I've frequented several (SQL Server Central, SQL Server Performance etc) but it's hard to discern quality answers on these.
The reputation system on the Stack Exchange sites is a powerful tool to ensure higher quality answers as well weeding out the poor ones.
I can't ...
4
Only recently waved goodbye to the last SQL2K server I looked after, so have a few scripts in the toolbox still. You could also root around in the SSC scripts archive.
Here's an old index maintenance script by Kimberly Tripp:
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp - all rights reserved.
-- ...
4
Some things you can look at...
Reduce the batch size from 10000 to something smaller, like 2000 or 1000 (you didn't say how large your row size is).
Try turning on IO Stats to see just how much IO the FK lookups are taking.
What is the waiting caused by when the insert it happening (master.dbo.sysprocesses)?
Lets start here and see where we go.
Only top voted, non community-wiki answers of a minimum length are eligible
