I am planning to upgrade a SQL Server 2000 instance to a SQL Server 2005 instance. I am not going to change any database code, so I won't be taking advantage of any SQL2005 specific syntax. Is there any reason to set my compatibility higher (to level 9)? Or is it ok to leave at compatibility level 8? ...
My database query has been running very fast until it changed to very slow recently. No changed have occurred in the database apart from normal data growth. I have noticed that the database statistics have "never" been updated. Is there an easy way that I can update these statistics across my entire database so I can see if that is t...
Similar but NOT IDENTICAL to SQL Server 2000 - Query a Table’s Foreign Key relationships I need a T-SQL statement that will work SQL 2000 that given a table name, will return the foreign key relationships for that table e.g. Table MyFristTable has a foreign key to MySecondTable, where MyFirstTable.ColA must be in MySecondTable.ColB. I'...
How to Create a Table from the recordset value sqlCardEvent1 = "select * from tmp_CARDEVENT" If rsCardEvent1.State = 1 Then rsCardEvent1.Close rsCardEvent1.Open sqlCardEvent1, Cn, adOpenStatic, adLockOptimistic cmdCardEvent1.CommandText = sqlCardEvent1 Set rsCardEvent1 = cmdCardEvent1.Execute sql33 = "create table t...
Using Access Database Table Cardno name cardeventdate Intime Outtime 0001 Michael 20080811 102746 185249 0001 Michael 20080812 080828 080828 0002 Michael 20080811 082615 082615 0002 Michael 20080812 073624 190605 From the Above Table I want to Display another Two Column like C...
I have two tables (1) MonthlyTarget {SalesManCode, TargetMonthYear, TargetValue}; this table has 1966177 rows. (2) MonthlySales {SalesManCode, SaleDate, AchievedValue}; this table has 400310 rows. I have to make a query that produces a result like the following table: {SalesManCode, JanTar, JanAch, FebTar, FebAch,....., DecTar, De...
I have two tables (1) MonthlyTarget {SalesManCode, TargetValue}; (2) MonthlySales {SalesManCode, SaleDate, AchievedValue}; I have to make a query that produces a result like the following table: {SalesManCode, JanTar, JanAch, FebTar, FebAch,....., DecTar, DecAch} What should be the query? ...
What is the best way to achieve this exclusion matrix via query. There are fixed number of products in a table and idea is if a product is sold to a customer (represented by row), the other products (columns) may or may not be sold based on the rule matrix below. The aim is to get products code which are allowed to sold for any given s...
My database has a table with thousands of records. The primary key is an integer. There's a lot of foreign key constraints associated with this column. I want to change this column to become an identity key. What's the best way to do it? I also need to send this update to our clients installations. Bonus points for an answer that work...
I'm trying to migrate from sql server 2000 to mysql by using DTS. That's the best tool I know yet. But one a sql server tables has a column with datetime type, the DTS suggest the following sql code: `StartDate` long varbinary NULL, `EndDate` long varbinary NULL, It should be: `StartDate` DateTime NULL, `EndDate` DateTime NUL...
How to get Previous Column Value? IIf id1 = id2 then display previous column id1 value id1 id2 1001 1001 1002 1002 1003 1003 so on... select id1, id2, Iff id2 = id1 then disply previous id1 value as idadjusted Output id1 id2 id3(Expected) 1001 1001 **1000** 1002 1002 **1001** 1003 1003 **1002** so on... ...
Is it possible to create a composite key in sql 2000 of the form: event_id id2 adf 1 adf 2 adf 3 cfg 1 cfg 2 edf 1 where the id2 restarts the count at each change of event_id. I need the numbering to be exactly like that either by creating a table or other SEL...
I am trying to restore a database by first restoring a full backup and then restoring a differential backup by using the Microsoft.SqlServer.Management.Smo.Restore class. The full backup is restored with the following code: Restore myFullRestore = new Restore(); myFullRestore.Database = "DatabaseName"; myFullRestore.Action = RestoreActi...
I want to add a variable number of records in a table (days) And I've seen a neat solution for this: SET @nRecords=DATEDIFF(d,'2009-01-01',getdate()) SET ROWCOUNT @nRecords INSERT int(identity,0,1) INTO #temp FROM sysobjects a,sysobjects b SET ROWCOUNT 0 But sadly that doesn't work in a UDF (because the #temp and the SET ROWCOUNT). A...
I'm receiving this error while attempting to execute a package: "The connection is currently being used by a task. The connection cannot be closed or reused." Any ideas? ...
In the past, whenever my application needed to insert many records into a SQL 2000 db, I would call a stored procedure, once for each record. When inserting many records, I found that performance suffered, particularly when with a fat client, calling a web service to perform the database call. Then I learned that if I passed XML data re...
I am using a timetabling system with SQL Server 2000 backend I need to list events with tutors and rooms next to them this can be more than 1 so could do with turning the multiple rows of rooms and tutors into + separated lists. I have used the code below in the past: DECLARE @Tutors as varchar(8000) SELECT @Tutors = isnull(@Tutors + '...
Sometimes when diagnosing issues with our SQL Server 2000 database it might be helpful to know that a stored procedure is using a bad plan or is having trouble coming up with a good plan at the time I'm running into problems. I'm wondering if there is a query or command I can run to tell me how many execution plans are cached currently f...
Hi, If there is a SQL Server 2000 installed on a Server, and i have a VWD Express 2008 on a PC on that network, what could be the possible reasons for the SQL Server not being visible in the Server Explorer of the VWD ? Both PC's are on the same workgroup. I also tried keying in the name of the server, and the Login and password, but ...
Using MS Access database I want to display today record min (time) and previous day record max (time) Table PERSON ID CARDEVENTDATE CARDEVENTTIME 5008 20090805 080000 5008 20090805 140000 5008 20090809 180000 5008 20090809 220000 5008 20090813 090000 5008 20090813 ...