I am trying to work out the difference between customers that have been billed in the last 3 months and the whole customer base. I have been able to calculate the customers that have been billed using the following SQL DECLARE @DateFrom AS DATETIME SET @DateFrom = CONVERT(DATETIME, CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-' + ...
On a SQL Server 2000 system, I have a templog.ldf file that seems to grow without bound! But when I check, there are never any open transaction in the tempdb (using DBCC OPENTRAN), nor do I ever use explicit transactions within tempdb. I do, however, use temp tables within my stored procedures. And who knows what ASP.NET data access obj...
I need to explain this by example: Is there a best practice or preference for specifying a DateTime and BIT in a database table? In my database I have a Widget table. I need to know if a widget is "Closed" and it's "Closed Date" Business rules say that if a widget is closed, it must have a closed date. If a widget is not closed, i...
Using SQL Server 2000 My Query. SELECT (Format(IIf(CLng(OutTime) > 180000, CDate('18:00:00'), CDate(Format(OutTime, '00:00:00'))) - IIf(CLng(InTime) < 90000, CDate('09:00:00'), CDate(Format(InTime, '00:00:00'))), 'hh:nn:ss')) As WorkTime, (Format(IIf(CLng(InTime) < 90000, CDate('09:00:00') - CDate(Format(InTime, '00:00:00')), ...
Using SQL Server 2000 Table PersonID Date 001 11-02-2009 002 11-02-2009 003 11-02-2009 001 12-02-2009 004 12-02-2009 005 12-02-2009 003 13-02-2009 005 13-02-2009 So on…, I want to display all the Personid by Date wise. Expected Output PersonID Date 001 11-02-2009 002 11-02-2009 003 11-02-2009 004 11-02-2009 005 11-02-2009 001 ...
I have a SQL 2000 database with around 10 million rows and I need to make a query to get the product information based on the full / partial text search. Based on this I need to join back to other tables to check on my business process. I have this implemented using SQL proc, but I can only validate around 6 rows a sec (without threads....
I'd like to be able to insert some values into a table using a single INSERT statement, but I'm having trouble determining the best way. The data I'm inserting is bootstrap (or hard-coded) data, ie. not available in another table. Here's what I've tried so far. create table #t (id int) insert into #t values (1) --- (a) insert into #t ...
Situation: c#, sql 2000 I have a table, lets call it 'mytable' with 30 million rows. The primary key is made up of fields A and B: A char(16) B smallint(2) When i do a search like this, it runs really slowly (eg it does a full tablescan) string a="a"; int b=1; string sql = "select * from table(nolock) where a=@a and b=@b"; using (Sq...
I have an application that (currently) needs to uses DBs served by both SQL Server 2000 and SQL Server 2005. I'm trying to move a DB programmatically from one server to the other. All of the tables I use each have a primary key that is autoincremented. Since these autoincremented keys are used as foreign keys in other tables, my plan ...
I am using SubSonic3 with SQL Server 2000. I have problem with the method "FirstOrDefault" - it always throws an exception = "Line 1: Incorrect syntax near '('." from the SubSonic.Linq dll EDIT (Added code from comment): InventoryDAL = DAL project name (dll) Inventort= Subsonic3 Gnerated classes Name space WHWarehouses = gnerated ob...
Hi, I'm in the process of upgrading from SQL 2000 - SQL 2008 Would it be possible (or feasable) to set up log shipping from Server A - that has the sql 2000 database on, to Server B - that has the SQL 2008 instance on Or is there a better way of upgrading? I need to obviously test my application using the new SQL 2008 instance.... ...
I'm sure this is something simple, but I can't seem to figure it out. Why doesn't this code work? DECLARE @FirstSaturday DATETIME DECLARE @ENDDATE DATETIME SELECT @FirstSaturday = min(RED1.DATE) FROM REDFRIDAYDATES..TBLREDFRIDAYALLDATES RED1 WHERE Period = 9 AND year = 2009 SELECT CASE WHEN getdate() < @FirstSaturday THEN set @ENDD...
I have a SQL Server 2000 database with approximately 220 tables. These tables have a number foreign key relationships between them. Through performance analysis, we've discovered a number of these foreign key relationships are missing indexes. Instead of being reactive to performance problems, I'd like to be pro-active and find all fo...
I am replacing an Access application with a web app, but the client is using SQL Server 2000, and I am using SQL Server 2008. So, I have the database redesigned, with foreign keys, but now I need to get the data on the client's system. Part of the problem is that they have images that are over 32k, so osql failed as the command buffer ...
How can I restrict my users to stop getting Log in / accessed into my Database by Enterprise Manager or by any other way in SQL Server 2000? ...
I've just wrapped a complex SQL Statement in a Table-valued function on SQLServer 2000. When looking at the Query Plan for a SELECT * FROM dbo.NewFunc it just gives me a Table Scan of the table I have created. I'm guessing that this is because table is created in tempdb and I am just selecting from it. So the query is simply : SEL...
Hey guys, I'm tasked with doing a SQL Server 2000 to 2005 migration. I will be doing a side-by-side migration. I've read from a few different sources that I can either: perform a backup of the database on SQL Server 2000, ship that backup to the new hardware and restore the database in SQL Server 2005; or detach the database on SQL Ser...
Hello, I have two tables. Employee Information and Skills Employee Information has the following details - ID (Unique Identifier), FirstName, LastName. Skills has the following details - Employee Id, Skillset A sample data will be as follows: Employee Information Id FirstName LastName 1 John ...
A colleague of mine discovered a behaviour in SQL Server which I was unaware of. CREATE VIEW dbo.vRandNumber AS SELECT RAND() as RandNumber GO CREATE FUNCTION dbo.RandNumber() RETURNS float AS RETURN (SELECT RandNumber FROM vRandNumber) GO DECLARE @mytable TABLE (id INT) INSERT INTO @mytable SELECT 1 INSERT INTO @mytable SELECT 2 INSE...
Here is my query: SELECT publicationId AS PublicationID FROM dbo.PublicationOwner WHERE ownerId = 31331 UNION SELECT AreaBuy.AreaBuyID AS PublicationID FROM AreaBuy JOIN PublicationAreaBuy ON AreaBuy.AreaBuyID = PublicationAreaBuy.AreaBuyID WHERE PublicationAreaBuy.PublicationID IN (SELECT publicationId ...