sql-server-2000

Indexing in Sql Server

What is Clustured and non clustured indexing? How to index a table using sql server 2000 Enterprise manager? thanks in advance for your kind reply.. ...

How can I select distinct rows when a text field is part of the returned fields in MS SQL Server 2000?

I have a database-driven FAQ that is organised into sections and I am trying to get the section data for only those sections who have a question/answer associated with them. Here is the schema: |---------------------| |----------------------| | Section | | Quest-Ans | |---------------------| |-----...

What's the best way to add double quotes in sql server 2000

I have the following. SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER FUNCTION doublequotestring(@str nvarchar(1998)) RETURNS nvarchar(4000) AS BEGIN DECLARE @ret nvarchar(4000), @sq char(1) SELECT @sq = '"' SELECT @ret = replace(@str, @sq, @sq + @sq) RETURN(@sq + @ret + @sq) END...

SQL replication - Publication deleted, but log file still growing

To preface, I've seen the command to answer this question before, but now I can't find it again, so I'm just looking for the single SQL statement that will solve my problem. I had two publications on a SQL Server 2000 database at one point, but I've since deleted them. However, my log file is growing, and appears to contain unreplicated...

How do I combine these two SQL statements into one, in a manner compatible with both MSSQL 2000 and 2005?

My end goal is to accomplish something like: CREATE FOREIGN KEY IF NOT EXISTS FOREIGN KEY Since that statement doesn't appear to exist, I'm attempting to sort of kludge it together. I have a statement that will return the FK name if it exists: SELECT f.name AS ForeignKey FROM sys.foreign_keys AS f WHERE OBJECT_NAME(f.parent_object_...

Multiple Counts in SQL Query

Give the following simple table structure: Departments PK - DeptID DeptName -------------------------- 1 Department 1 2 Department 2 3 Department 3 4 Department 4 Groups PK - GroupdID DeptID -------------------------- 1 1 2 1 3 3 4 ...

How can I use sp_xml_preparedocument on result of NTEXT query in SQL 2000?

I know NTEXT is going away and that there are larger best-practices issues here (like storing XML in an NTEXT column), but I have a table containing XML from which I need to pluck a attribute value. This should be easy to do using sp_xml_preparedocument but is made more tricky by the fact that you cannot declare a local variable of type ...

ADVICE on billing Query in SQL Server 2000

I need some advice in tackling a query. I can handle this in a front-end application, however, due to design, I have to inplement this in the back-end. I have the following CREATE TABLE [dbo].[openitems]( [id] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [type] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, ...

Print result set without cursor

I'm using T-SQL and I want to print out a result set. This is just a ~2x6 (dynamic size) set but I'm not entirely sure how I can do this without using a CURSOR. Is there a nice way I can print these to console/email/wherever? ...

security in sql server 2000

How to create user and connect user in sql server 2000? ...

Error when installing sql server 2000 on vmware (Windows XP)

The installer crashes with instruction to check c:\windows\sqlstp.log for more information, here's what the log contains: Connecting to Server ... driver={sql server};server=JJOHN-vm;UID=sa;PWD=;database=master [Microsoft][ODBC SQL Server Driver][Shared Memory]General network error. Check your network documentation. [Microsoft][ODBC ...

Database design help with varying schemas

I work for a billing service that uses some complicated mainframe-based billing software for it's core services. We have all kinds of codes we set up that are used for tracking things: payment codes, provider codes, write-off codes, etc... Each type of code has a completely different set of data items that control what the code does an...

What is the best way to query data from multilpe tables and databases?

I have 5 databases which represent different regions of the country. In each database, there are a few hundred tables, each with 10,000-2,000,000 transaction records. Each table is a representation of a customer in the respective region. Each of these tables has the same schema. I want to query all tables as if they were one table. The...

SQL error with script to create

I used the SQL Server management studio on a table with Create Script to New and did minor changes. Give me an error "Incorrect syntax near '('" for the "(" after "WITH" /* EventType Table Creation */ CREATE TABLE [EventType] ( [pkEventID] [int] IDENTITY(1,1) NOT NULL, [Description] [nvarchar](50) NOT NULL, [BeginDate] [datetime]...

How can I check for average concurrent events in a SQL table based on the date, time and duration of the events?

I have a set of call detail records, and from those records, I'm supposed to determine the average concurrent active calls per system, per hour (at a precision of one minute). If I query 7pm to 8pm, I should see the average concurrent calls for the hour (averaging the concurrent calls for each minute) within that hour (for each system). ...

HOW TO: Umbraco 4.0 with Microsoft SQL Server 2000

We have a server that is running Microsoft SQL Server 2000 and would like to setup Umbraco 4.0. I tried the web install, but it choked on the MS SQL 2005 Express install. So I next tried the manual installation of MS SQL 2005 Express, which choked also with an error indicating that there was incompatibility issue because we already have ...

SQL2000 Read Only View

I've checked BOL and I don't see what I'm looking for. I know that Oracle has a "WITH READ ONLY" option when creating a view. I don't see this option in SQL2000. Do I need to add an INSTEADOF trigger to accomplish this? In short, I don't want users of the view to be able to update the data. Thanks ST ...

SQL Server 2000 temp table vs table variable

What would be more efficient in storing some temp data (50k rows in one and 50k in another) to perform come calculation. I'll be doing this process once, nightly. How do you check the efficiency when comparing something like this? ...

SQL Server 2000 drop column with constraints

I have the same problem as described in this question, but there it's SQL Server 2005 and the "accepted" answer doesn't work in SQL Server 2000. Specifically: I'm trying to run ALTER TABLE foo DROP COLUMN bar, and it's failing because there's a "default constraint." Meaning, I have a default value on that column which SQL Server implem...

Set based calculation for events table, exceptuating an event

Hello, I have a table which contains events for change the state of a maintenance service. This is a sample of data: id date event 234 2009-04-22 10:00:00 Service Request 234 2009-04-22 10:02:00 Service Approbation 234 2009-04-22 10:03:00 Service Asignation 234 2009-04-22 10:40:00 Service Fulfilled ... In a report, i need to show tim...