Tag Info

Hot answers tagged

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


15

The foremost step to do is to run the Upgrade Advisor on SQL 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 in a ...


13

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


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


12

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


10

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


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

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


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


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

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 Open SQL Server Configuration Manager from Start Menu > Programs > Microsoft SQL Server 20xx > Configuration Tools > relevant to the newest ...


6

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


6

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


6

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


6

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


5

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.


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

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

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

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



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