Say my query looks like: SELECT t1, t2 FROM t1 LEFT JOIN t2 ON (t1.id = t2.id AND t2.userid = @userid) WHERE t1.enabled = 1 AND t1.startDate t2.counter) Now this table might have a few hundred thousand rows in it. Would you suggest I put an index on the JOIN clause only like this? t2.id t2.userid What about the where clause? Or is the join clause more important? I realize testing is important, but in theory what should be done? (This is for SQL Server 2000)

I am attempting to copy off approximately 220 gigs of database MDF and LDF files from a MS SQL server 2000 install in order to transfer ownership to a third party. The error I get when I try to stop the database using the enterprise manager is > Error 1051: A Stop Control Has Been Sent To A Service Which Other Running Services Are Dependent On Going to the service and looking at the dependencies tab does not show any other services running in relation to it. If I stop the actual Windows service, and try to copy it tells me that the databases are still in use. I have changed the service from automatic to manual and rebooted, and it still insists the files are in use. I don't particularly care about copying the log files, but how can I resolve the error to do a clean shutdown and be able to copy off at least the MDF files?

I have an SSIS package that connects MSSQL2008 server to MSSQL2000 and pulls data off the 2000 server and transfers it to the 2008 server. The 2000 server was recently virtualized and since then the SSIS package will not authenticate. I can see both servers with OSQL -L/sqlcmd -L. All services, agents and browsers are running. The SQL user that runs the SSIS package can connect via Test Connection button in the Package Explorer>Connection Manager, but when I attempt to execute the package the error output indicates that the login failed, the password does not match. Error 18456 Sev 14 State 8. I am sure the password is correct as I just created the new SQL user to test. Am I missing something that the SSIS package may authenticate somewhere else? Should the connection string include the password, or does that get pulled form elsewhere in the SSIS package? I am not a DB admin/SQL guy so please excuse my green-ness if I am overlooking something simple here, which I hope I am. Thanks for any input

I have two virtual machines. Server A is running SQL Server 2000. Server B is running SQL Server 2008R2. I am using the same domain user to log in to both boxes. But I cannot log in to SQL Server locally on server A. I get Cannot open user default database. Login failed. when attempting with Query Analyzer or Enterprise Manager. I can connect from server B using SSMS if I specify a database to connect to. Once thus in, I have tried changing the default database for my user to 'master' via the GUI and via exec sp_defaultdb 'DOMAIN\username', 'master' but it does not change things. I have tried setting other databases as the default and verified that those databases are queryable by me. No luck. Unless I open a connection with SSMS and specify a database, the login fails. This login problem is also preventing me from running linked queries against server A. How can I get this fixed?

We run SQL Server 2000 and we get a few of these errors every night. Could not continue scan with NOLOCK due to data movement The query that throws this error is a large complex query that joins over a dozen tables. Our underlying data can be updated frequently. The cultural 'best-practice' is that, in the past, introduction of NOLOCK hints increased performance and improved concurrency. This query doesn't need to be 100% accurate, i.e. we will tolerate dirty reads etc. However, we are struggling to understand why the database is throwing this error, even though we have all these locking hints. Can anyone shed some light on this - be gentle, I'm actually a programmer, not a DBA :) PS: We have applied the fix mentioned below previously: http://support.microsoft.com/kb/815008

I have recently upgraded SQL Server 2000 database to 2008 R2. ------------------- **What I did was:** 1. Shutdown SQL Server 2000 (express) service on old machine, 2. Move datafiles (*mydatabase.mdf* and *mydatabase.ldf*) to new machine, 3. Run SQL Server Management Studio 2008, 4. Connect to local database engine, 5. Attach datafiles to database. 6. Change the compatibility level of database to SQL 2008 (100). ------------------- **Question: *What else should I do to make migration complete?*** I want to: 1. use new features like checksumming and full recovery model, 2. make this database to be exactly as it was created in SQL 2008 R2, 3. make this database to be fully compatibile, correct and be perfect suited for new, SQL 2008 R2 database engine. ------------------- *In other words:* I just want to know how to correctly and completly convert old SQL 2000 database to new 2008 R2 database, be calm that everything is done right and be happy with all new features. ------------------- I'm asking this question, because I've found a lot of sites on the Internet that says so many different things that makes me confused: some say that it is required to rebuild indexes, another says to do other things... and now I know nothing so I want to hear experienced person opinion and clear, step-by-step instructions. I work for very small company, I'm on my own and I don't want to screw things up. --- **Sir, I'm really impressed with your answer, I wasn't expecting so much.** --- **So some comments:** 1. The database is now in production. As I said, it was upgraded using deattach-attach method as I desribed in first post and as described on MSDN: http://msdn.microsoft.com/en-us/library/ms189625.aspx It had to be done quickly, so I was forced to do it that way. Let's forget about how inappropriate could it was and focus on current situation. 2. The users/persmission is not a problem here - there are only few and permissions are simple. 3. The application which use database is compatibile with SQL 2000 till 2012 so this is not a problem either. 4. The database file (MDF) isn't big - only about 1GB. --- **Few more questions:** 1. You recommend to use backup/restore method, but I did as written above, so can I encounter any problems now? Everything worked without any problem. 2. About checksumming and full recovery model: it wasn't available/enabled on SQL 2000, so I want to use them now. You said that the only thing I need to do is to enable those options in database properties? I've read somewhere, that it isn't enough and I should also rebuild indexes or something. I really don't know, I just asking. 3. I'm prepairing to migrate this database to SQL 2012 - so first it was from SQL 2000 to 2008 R2, now it will be from 2008 R2 to 2012 (it was impossible to do this directly because of lack of support of SQL 2000 databases in SQL 2012). So I understand that I should follow your guide: backup it in 2008 R2 and restore in 2012, then do the rest of your tips, right? 4. Please explain me the backup/restore method: Is it like a dump of database to SQL queries and then restoring it by executing a bunch of queries? Will this method by the way "defragment" my database? If not, how to defragment/optimize it manually? 5. As we were using SQL 2000 Express for years (no management interface), we were doing backups simply by stopping engine and RAR the DATA directory. For now, as we are on SQL 2008, isn't this still better than using backup function in Management Studio? 6. *Full recovery mode with frequent Transaction log backups* - Where is the Transaction log stored - is it the LDF file? How sould I backup it properly? --- I know that my questions may sound silly, I'm not professional database admin, but I am the only person here who can do such "hard core" task like upgrading the database engine. I'm also sure that your knowleadge will help a lot to other people like me. Thank you very much for your time and knowleadge, I really appreciate this.

Is there a way to find out when a database was last accessed on SQL Server 2000? By this I mean, had a table read, a stored procedure called etc... I have a list of databases and I am trying to find out which databases are still needed or not. I'm not sure sp_who2.LastBatch will give me the result I need.

I am running SQL Server 2008 Upgrade Advisor tool on a SQL Server 2000 DB, and the final report shows an error: > Database Server,"PreUpgrade",
"After upgrade, new reserved keywords cannot be used as identifiers",
"Upgrade Advisor detected an idetifier with name **Merge**",
"Source Type: Database","Database: **Domus**","Object Name: **crosstab**","Object Type: P ",
"Key Word: **pivot**" The message is a bit ambiguos for me.... Using the following script , I have searched the keyword 'pivot' and 'Merge' in all tables, however there are no results. Should I ignore this error?

I need to export the views from *multiple* databases of an older server. (Essentially, to generate a list of views using a particular piece of SQL Server) The fastest way I've found thus far is to manually use the Generate scripts function to export the views to a file. I feel like there is probably a better way I don't know about. Thanks in advance,

I imported a bunch of tables from SQL Server 2000 to my 2008 database. All the imported tables are prefixed with my username eg: erpadmin.tablename. In the table properties it lists 'erpadmin' as the db schema. When I write a query I now have to include 'erpadmin.' in front of all the table names which is confusing. Current result: select * from erpadmin.tablename Desired result: select * from tablename

I'm currently upgrading SQL Server from 2000 to 2008, the database was successfully transferred, the issue remains with the security logins, I have found the stored procedures that extracts the logins with Sid/Password and so on. The issue I'm facing is the password length is 94-128 character in SQL Server 2000.  When I tried to create the security login in SQL Server 2008, only 54 characters are used not the whole password hash, is there anything I'm missing?  Here is the creation query: DECLARE @pwd sysname SET @pwd = CONVERT (varbinary(256), 'Password hash') EXEC master..sp_addlogin 'Name', @pwd, @sid = Sid, @encryptopt = 'skip_encryption'

I have a SQL Server 2000 machine that has a pretty big database on it - almost a terabyte in size. There's a particular table that has been a source of some production issues and any attempt to try to fix it seems to cause the same problem....self-blocking (I know it's not a big deal on SQL Server 2000 SP4 but then a blocking spid of -2). Even something like SELECT count(1) FROM causes this spid(-2) to appear. I went into the table properties and there's about 1.5 million rows which isn't much. I have no problems running the same count statement on much larger sized tables. I did notice the fragmentation is quite high on this table (around 45%) but any attempt to either rebuild or reorganize wreaks havoc on the system. I tried running the reorganize during the weekend for like 8 hours and the job was still running and people were complaining of time-out issues. Then the other day, I tried using the import/export wizard to copy the table to my local instance and it ran fine for the first 250K rows and I was also monitoring sysprocesses and saw the spid for the ETL process. It was fine for those first 250K rows...then the wizard row count wouldn't increment anymore. Then I check the spid on 2K box and it was blocked by spid(-2) again. What the hell is wrong with the table? Is there an integrity issue? The only way to run SELECT statements against it without this happening is to use NOLOCK.

I have SQL Server 2000 (aka version 8.0) that failed to recover a database after a power failure. The only backup I found are the current mdf file and an ldf file that is two years old. Can I restore the database using the current mdf file and an old ldf file?

I can't access to a database created by SQL Server 2000 from Visual Studio 2013, I asked this before in StackOverflow, but I don't get an useful answer: https://stackoverflow.com/questions/21065565/when-i-create-a-connection-to-the-datasource-of-an-old-application-throw-me-an (SELECT @@VERSION = Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) ) After a search in google I found some workarounds with a similar problem: http://n8v.enteuxis.org/2009/12/getting-microsoft-visual-studio-2005-to-talk-to-microsoft-sql-server-2008/ http://support.microsoft.com/kb/954961/en-us But two solutions send me to download Microsoft Visual Studio 2005 Service Pack 1 Update for Microsoft SQL Server 2008 Support. I can't install that, because I don't have Visual Studio 2005 and I need "VS2013 - Olds (2000)". I have installed SQL Server 2008 (which I don't use today) and **SQL Server 2012**. Don't exist a 'Microsoft Visual Studio 2013 Update for Microsoft SQL Server Olds'. What can I do?

I have a 1 GB .LDF for a test database which I want to shrink right down. I am happy to delete its contents if needed. How can I do this for SQL Server 2000? I have tried the following to no huge avail: BACKUP LOG Database_TEST with truncate_only DBCC SHRINKFILE (Database_TEST_Log, 1000)

I might be forgetting something, but essentially, I'm struggling with IDENTITY_INSERT. I'm trying to move data between tables, both of which have an identity column (similar schema). SET IDENTITY_INSERT DestinationTable ON; returns that IDENTITY_INSERT is already ON for that table. (Error Msg 8107) Fine, it's already ON, I can work with that, right? Then: INSERT INTO DestinationTable (Id, ColA) SELECT Id, ColA FROM SourceTable; returns that I can't insert an explicit value .. when IDENTITY_INSERT is OFF (Error Msg 544). But it just said it was ON. So, what's going on? EDIT: Actual script (save for table names and structure): SET IDENTITY_INSERT User2.DestinationTable ON; INSERT INTO User2.DestinationTable (Id, ColA) SELECT Id, ColA FROM User1.SourceTable; SET IDENTITY_INSERT User2.DestinationTable OFF;

I have two servers running SQL Server 2000 and SQL Server 2005.
There is a database A,B in SQL Server 2000. Similarly I have the same in A,B in SQL Server 2005. A,B are name of the database. I have a linked server pointing from SQL Server 2005 database A to SQL Server 2000 database B. Here the scenario is I can able to access database SQL Server 2000 database B from SQL Server 2005. Two issues I face I am randomly getting the error: > OLE DB provider "SQLNCLI" for linked server "SQL2000" returned > message "Communication link failure". Msg 10054, Level 16, State 1, > Line 0 TCP Provider: An existing connection was forcibly closed by the > remote host. Msg 18456, Level 14, State 1, Line 0 Login failed for > user 'NT AUTHORITY\ANONYMOUS LOGON'. How to resolve this error? Also, when the user want to access the database located in the same server what must be done? I mean accessing the database SQL Server 2005 database A and SQL Server 2005 database A i.e ignoring the linked server.

I know that an INSERT on a SQL table can be slow for any number of reasons: * Existence of INSERT TRIGGERs on the table * Lots of enforced constraints that have to be checked (usually foreign keys) * Page splits in the clustered index when a row is inserted in the middle of the table * Updating all the related non-clustered indexes * Blocking from other activity on the table * Poor IO write response time * ... anything I missed? How can I tell which is responsible in my specific case? How can I measure the impact of page splits vs non-clustered index updates vs everything else? I have a stored proc that inserts about 10,000 rows at a time (from a temp table), which takes about 90 seconds per 10k rows. That's unacceptably slow, as it causes other spids to time out. I've looked at the execution plan, and I see the INSERT CLUSTERED INDEX task and all the INDEX SEEKS from the FK lookups, but it still doesn't tell me for sure why it takes so long. No triggers, but the table does have a handful of FKeys (that appear to be properly indexed). This is a SQL 2000 database.

We have a legacy application that is only used for a read only reference. It is using SQL Server 2000. We want to migrate its database to SQL version 2012, however we can't upgrade the software and the vendor doesn't exist anymore. The problem is that the views/stored procedures on the database are using old NON-ANSI SQL code that isn't supported anymore. When we tried to simply restore it on SQL 2008R2 (as there isn't a direct restore from 2000 to 2012), and increase the compatibility level to 100, we got validation errors on the views/stored procedures, as they're using old join syntax. >The query uses non-ANSI outer join operators ("\*=" or "=\*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes. As there are ~200 objects that need to be updated with a newer and proper join syntax code, I was wondering whether a tool/script/code-generator exists somewhere that will help me convert the code. I did notice that if I right click the view object and select Design, the query of the view IS converted to the proper syntax. But that isn't automated enough for me. Any thoughts?

I have two SQL Server instances that I manage. One is a SQL Server 2000 instance, and the other is 2005. Some where, on these servers, I recall setting up a couple table triggers that were executed under certain conditions. I need to look-up these triggers as a point of reference for a new project, however, for the life of me I can not find them. Is there some crafty SQL statement that I can execute that can iterate over all my databases and, subsequently, iterate over each table and output any triggers associated with the tables?