Tag Info

Hot answers tagged

17

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


16

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


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

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


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

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


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


9

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.


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.


8

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


7

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


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

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


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

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

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

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

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

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

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


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

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

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'


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

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



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