Good day, I have about 4GB of data, separated in about 10 different tables. Each table has a lot of columns, and each column can be a search criteria in a query. I'm not a DBA at all, and I don't know much about indexes, but I want to speed up the search as much as possible. The important point is, there won't be any update, insert or d...
MS SQL Server 2000 I have a column in Table A called Name. I wish to sort the Name field. Many but not all of the records for Name start will KL and are followed by a number (KL 1234, KL 2, KL 323, etc). Table A Name Able Bravo KL 2 KL 323 KL 1234 Zebra If I use Select Name from A Order by Name I get Able Bravo KL 1234 KL 2 KL ...
Is there any way for a DBA to peek in on the execution plan of a long-running query in SQL Server 2000? I know how to get the SQL being run using fn_get_sql(). And yes, theoretically if open a new connection and set the environment flags the same, it should generate the same plan for the SQL. However, I'm in a data warehouse environme...
I'm trying to use SQL Server 2000 DTS Package to download a file from a FTP server. However, the FTP module in DTS only shows the files in my ftp login "Home" directory. I need to change the remote directory once I've connected. How do I do this? I can quite happily browse to the directory in the Files tab of the FTP task. The direc...
Hi, Is the following possible in SQL Server 2000? CREATE FUNCTION getItemType (@code varchar(18)) RETURNS int AS BEGIN Declare @Type tinyint Select @Type = case len(@code) WHEN 12,14,17 THEN 1 WHEN 13,15,18 THEN 2 WHEN 8,10 THEN 3 ELSE 0 END RETURN (@Type) END Thanks ...
Hi, I am having an Event Management System in which i want, If an event is registered for 5 days (21 jan 2009 to 26 Jan 2009) Then if another person wants to register an event between 22 jan 2009 to 24 jan 2009 then it will not allow to register. I want to check this using SQL query, so please tell me how can i do this. ...
I'm trying to retrieve data from an SQL Server 2000 server, and place into Excel. Which sounds simple I know. I'm currently Copying, and Pasting into Excel, from Management Studio The problem is one of the columns is an address, and it’s not retaining the newlines. These new lines have to stay in the same cell in excel, I.E cannot take ...
I have a table (SQL 2000) with over 10,000,000 records. Records get added at a rate of approximately 80,000-100,000 per week. Once a week a few reports get generated from the data. The reports are typically fairly slow to run because there are few indexes (presumably to speed up the INSERTs). One new report could really benefit from ...
I'm trying to import an excel file in to a SQL Server 2000 database using DTS. This is nothing fancy, just a straight import. Where I work, we do this 1000 times a day. This procedure usually works without an issue but something must have changed in the file. I'm getting the below error: I've checked to ensure that the column "Assi...
How can I get a query which uses an OR in the WHERE clause to split itself into two queries with a UNION during compilation? If I manually rewrite it, the query using the UNION is 100x faster than the single query, because it can effectively use different indices in each query of the union. Is there any way I can make the optimizer use...
Is there any chance of installing Reporting Services 2008 against an instance of SQL Server 2000? The docs clearly state that only SQL Server 2005 or 2008 are supported, but I thought I should ask anyway, for ways to bypass this limitation. I should mention that I am not talking about the data source for my reports (which is a remote da...
Hi All, It's been a long while since I looked at SQL Jobs on SQL Server 2000. On SQL 2005, if I want to allow a user to create jobs etc I add the requisite login to msdb and assign one of the roles SQLAgentUserRole, SQLAgentReaderRole or SQLAgentOperatorsRole role. No problem there. Perhaps someone can help me jog my memory about rig...
Using SQL Server, how do I script a constraint on a field in a table, so that the acceptable range of values is between 0 and 100? ...
I'm working with a C++ application that uses SQL Native Client to communicate via ODBC with a SQL Server 2000 database. Before doing any database work, I allocate an environment handle as follows: retcode = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &EnvironmentHandle ); This completes successfully. To enable connection pooling...
A coworker asked me to look at indexing on some tables because his query was running very long. Over an hour. select count(1) from databaseA.dbo.table1 inner join databaseA.dbo.table2 on (table1.key = table2.key) inner join databaseB.dbo.table3 on (table1.key = table3.key) Note the different databases. This was being run from Databa...
How to protect a SQL Server database from viewing others? ...
I've had a look at this question but there are no responses regarding performance. Are there any performance benefits from doing a simple upgrade from SQL2000 to SQL2005? I am thinking a line of business OLTP datbase. I am not using OLAP or FTI. ...
Hi all, I want to copy an Oracle Database to a DB2 one using DTS in SQL Server 2000. When I set up both connections using their respective driver, I can read both databases, and link the copy operations between them. But when I want to run the DTS package, an error shows up saying "Driver not capable." Here's the log, thanks in advan...
We've been having intermittent problems causing users to be forcibly logged out of out application. Our set-up is ASP.Net/C# web application on Windows Server 2003 Standard Edition with SQL Server 2000 on the back end. We've recently performed a major product upgrade on our client's VMWare server (we have a guest instance dedicated to u...
I have a database that I used specifically for logging actions of users. The database has a few small tables that are aimed at specific types of actions. This data is rarely searched, but the rowcounts of the tables are starting to climb into multi-millions. I haven't noticed a large slow down, but I want to know if indexing the table fo...