sql-server-2000

Tell me SQL Server Full-Text searcher is crazy, not me.

i have some customers with a particular address that the user is searching for: 123 generic way There are 5 rows in the database that match: ResidentialAddress1 ============================= 123 GENERIC WAY 123 GENERIC WAY 123 GENERIC WAY 123 GENERIC WAY 123 GENERIC WAY i run a FT query to look for these rows. i'll show you ea...

select a value where it doesnt exist in another table

I have two tables Table A: ID 1 2 3 4 Table B: ID 1 2 3 I have two requests: I want to select all rows in table A that table B doesn't have, which in this case is row 4. I want to delete all rows that table B doesn't have. I am using SQL Server 2000. ...

Why does SQL Server 2000 treat SELECT test.* and SELECT t.est.* the same?

I butter-fingered a query in SQL Server 2000 and added a period in the middle of the table name: SELECT t.est.* FROM test Instead of: SELECT test.* FROM test And the query still executed perfectly. Even SELECT t.e.st.* FROM test executes without issue. I've tried the same query in SQL Server 2008 where the query fails (error: the ...

sqlalchemy date type in 0.6 migration using mssql

I'm connection to mssql server through pyodbc, via FreeTDS odbc driver, on linux ubuntu 10.04. Sqlalchemy 0.5 uses DATETIME for sqlalchemy.Date() fields. Now Sqlalchemy 0.6 uses DATE, but sql server 2000 doesn't have a DATE type. How can I make DATETIME be the default for sqlalchemy.Date() on sqlalchemy 0.6 mssql+pyodbc dialect? I'd l...

SQL (server) date conversion

I want to convert the run duration (from msdb..sysjobhistory) to an elapsed seconds figure. The duration is stored as an integer where for example 13 means 13 secs 213 means 2 mins and 13 secs = 133 secs 310213 means 31 hours, 2 mins and 13 secs = 111,733 secs Is there any neat way of doing this? It's SQL Server 2000 but I wo...

SQL server 2000 how to find transaction history for a table/row

How do you find the modification history for a certain table / row in SQL server 2000? Thanks. ...

Updating .XML through SQL server 2000

Hello Everyone, I want to apply a update trigger on a table in SQL server 2000. Trigger should update .XML file present on another server. I have full access to both servers. I am unable to figure how to do it, please help me out. ...

SQL Count unique objects defined by parameters

I have a table with: id | parameter 1 | A 1 | B 2 | A 3 | A 3 | B That represent objects defined with the values as: 1 -> A,B 2 -> A 3 -> A,B I want to count the number of objects with different parameters using a SQL query, so in this case it would be 2 unique objects as 1 and 3 have the same parameters. There is no const...

Sql server 2000 -Space find

This is query: CREATE TABLE #TempTable(datasize varchar(200)) INSERT #TempTable EXEC sp_spaceused 'Table1' When executing this query error message shown as below Column name or number of supplied values does not match table definition How can I solve this problem? ...

Migrating from Physical SQL (SQL2000) To VMWare machine (SQL2008) - Transferring Large DB

We're in the middle of migrating from a windows & SQL 2000 box to a Virtualised Win & SQL 2k8 box The VMWare box is on a different site, with better hardware, connectivity etc... The old(current) physical machine is still in constant use - I've taken a backup of the DB on this machine, which is 21GB Transfering this to our virtual mach...

What causes SQL Server to return the message 'The statement has been terminated'?

I have a very simple INSERT statement being executed from a PHP script running on a Linux Apache web server. I can run the query fine from within SQL Management Studio and it normally runs fine from PHP as well. However, every once in awhile I get an error message from my PHP script that the query failed and the mssql_get_last_message(...

Sorting nested set by name while keep depth integrity

I'm using the nested set model that'll later be used to build a sitemap for my web site. This is my table structure. create table departments ( id int identity(0, 1) primary key , lft int , rgt int , name nvarchar(60) ); insert into departments (lft, rgt, name) values (1, 10, 'departments'); insert into departments (lft...

Problems with sp_addlinkedserver in SQL Server 2000

Hi! I'm having a bit of a problem with moving specific data from one server running SQL Server 2000 (A) and another running SQL Server 2008 (B). I'm writing a script according to customer specifications which is to be executed on A, populating tables in B with data. However, I can't seem to get the server link to work. -- Bunch of decl...

How do I go about link web content in a database with a nested set model?

My nested set table is as follows. create table depts ( id int identity(0, 1) primary key , lft int , rgt int , name nvarchar(60) , abbrv nvarchar(20) ); Test departments. insert into depts (lft, rgt, name, abbrv) values (1, 14, 'root', 'r'); insert into depts (lft, rgt, name, abbrv) values (2, 3, 'department 1', 'd...

ROW_NUMBER Alternative for SQL Server 2000

RIGHT now I'm using ROW_NUMBER() in my procedure in SQL Server 2008 as follows: WITH cars as(SELECT carid,mileage,retailprice,imageurl,model,year, Zips.Distance AS Miles, Manufacturers.mfgName as Make, dealers.companyname as companyname, CASE @sortby WHEN 'D' THEN ROW_NUMBER() OVER (ORDER BY Manufacturers.mf...

SQL Table to another database

I have 2 databases on SQL (identical). I want a table from one database to be copied to the other database. They are both the same name and the original table can be overeritten. Cheers ...

Will .net 4.0 runtime work with SQL Server 2000?

According to this article, Visual Studio 2010 no longer supports SQL Server 2000. However, it's a bit less clear on whether the runtime (and things like ASP.net 4.0, Linq2SQL and EF) will work with it. It seems that only the design-time features require SQL Server 2005 or greater according to that article. I have a project that requir...

SQL Trigger insert

Hi Guys, What is the best way to set up a trigger to get all new rows in a table and insert them into a work queue table. Many Thanks Sp ...

Monitor and change SQL queries with SQL Server 2000

I have a database upgrade tool that is misbehaving. I would like to catch one of the queries it sends to the database and change it before it is executed. The tool connects via ODBC. The tool and the SQL Server are on the same Windows 2003 Server box. Any ideas? EDIT: (More info) When the tool runs it dies on step 12 out of 100. It...

How do I join on "most recent" records?

I've got two tables in a SQL Server 2000 database joined by a parent child relationship. In the child database, the unique key is made up of the parent id and the datestamp. I'm needing to do a join on these tables such that only the most recent entry for each child is joined. Can anyone give me any hints how I can go about this? ...