I'm creating/changing a ton of indexes on a large db. Doing this works if the index already exists. CREATE UNIQUE CLUSTERED INDEX [table1_1] ON [dbo].[table1] ([col1], [col2], [col3]) WITH DROP_EXISTING ON [PRIMARY] But if it does not exist the errors. So I have changed my script to: IF EXISTS (SELECT name FROM sysindexes WHERE name...
Dear All How to sum data week wise in MS-SQL 2000 I have a Table "Reports" with the following columns. TotalSubmissions, ZoneID, RptMonth, RptDay, RptYear 2,1,6,1,2010 1,1,6,2,2010 1,1,6,3,2010 1,2,6,1,2010 1,2,6,2,2010 2,2,6,3,2010 1,2,6,4,2010 1,4,6,1,2010 1,4,6,3,2010 1,4,6,4,2010 I want to make a report week wise for a specifi...
I have a table with an audit log: BugId Timestamp Status 1 2010-06-24 10:00:00 open 2 2010-06-24 11:00:00 open 1 2010-06-25 12:00:00 closed 2 2010-06-26 13:00:00 closed I want a running total of open and closed bugs like: Timestamp # Status 2...
I have a data set that looks like the following. EMPLID PHONE_TYPE PHONE ------ ---------- -------- 100 HOME 111-1111 100 WORK 222-2222 101 HOME 333-3333 102 WORK 444-4444 103 OTHER 555-5555 I want to select exactly one row for each employee using the PHONE_TYPE field to establ...
how to get all the tables and structure in a database in a table format using an sql query or stored procedure? Structure is like below: Sl No FieldName DataType Size Description 1 UserName varchar 50 ...
I'm using DBDeploy.NET for change control management in my T-SQL codebase. DBDeploy works by the developer maintaining a numbered set of change scripts. When a deploy is triggered (via NAnt), DBDeploy looks at a changelog table and determines which patches need to be executed to bring the instance up to date. The issue I have is with ...
Hello all What I want to do is, inform the users about my program that "there is an update going on in database, so the results may not be correct." Thus I have to check if there is some process (like writing or deleting) on my table which my program uses. Edit: The way that I update my table is I use MS Access, I copy from MS Excell ...
Hoe to combine rows in sql server 2000 ...
I have a table where the data are like Data a b c I need to write a SQL query to bring the following output Data abc How to do the same by using in SQL Server 2000 Thanks ...
I attached database from SQL Server 2000 to SQL Server 2005 and it worked well but I had column called (Add Date) which had the Date time of input data and when I insert new data after attached data base to SQL Server 2005 the new data insert with same data 12.00 also it converted all old date to 12.00. Please anyone help me how I can ...
Not being a SQL expert, and also only being semi-competent in CTE, how can I code this statement use the resultset from the following subquery within the main query, as our SQL Server is 2000. declare @subcategoryConcatenate varchar(3999) set @subcategoryConcatenate = '' select @subcategoryConcatenate = @subcategoryConcatenate + pumpCa...
I have a stored proc on SQL Server 2000 that takes 3 parameters. When I call the stored proc from DotNet using SqlCommand.ExecuteReader () it takes about 28 seconds. When I run the same query inside SSMS directly it returns immediately. When I take the query out of the stored proc and run it directly using DotNet it also returns immed...
My sql statement SELECT * FROM mytable WHERE (CONTAINS(keyword,' "green" ')) How do I list the top matched result like this Green Army Green Hunter Green instead of the below Army Green Green Hunter Green I know that we can't use the order by asc or desc with my case. I plan to add a space before 'green' and use the dict...
hey guys I got this. I want to check table PREMIUM_SERVICE_USER if any records exists for strClientID update timeValid for +30 if no records for strClientID insert to premium_service_user table.. what am I doing wrong? It increases timeValid for +30 days but inserts another row too.. thank you.. Select @pre_var = count(*) From PREMIUM_...
I have the following SQL query: select AuditStatusId from dbo.ABC_AuditStatus where coalesce(AuditFrequency, 0) <> 0 I'm struggling a bit to understand it. It looks pretty simple, and I know what the coalesce operator does (more or less), but dont' seem to get the MEANING. Without knowing anymore information except the query above,...
I have a requirement to identify Active databases on a SQL Server 2000 cluster. There are 295 databases but the field has been narrowed to 60 databases that we are unsure if they are still in use. I intend to use SQL Trace [sp_trace_create] and the Audit Login event to identify which databases are still in use. My question is should I...
What I want to do is something like this DECLARE @operator nvarchar; SET @operator = 'AND' SELECT * FROM MyTable WHERE first_column = "1" @operator second_columnt = "2" is there a way to implement a logic like that one ? ...
Hello all, I have a web based program which chooses some records from a database using a checkboxlist. (my check box list sends parameters to a stored procedure and I use the result of stored procedure). Here how my check box list looks like Overflow [] (sends param1) Open Records [] (sends param2) Records with 4...
Hello guys, i have some experience with MySQL but none with SQL Server, i have a computer who host a database (SQL Server 2000) and i have local access to it. The think is that one column of the table NomAntiguedad need to be change from a given date period; I need to multiply by 0.01 all the values of the column between the values of me...
I've got a query I'm working on and I want to increment one of the fields and restart the counter when a key value is different. I know this code doesn't work. Programmatically this is what I want... declare @counter int, @id set @counter = 0 set @id = 0 select distinct id, counter = when id = @id then @counter +...