6
votes
Accepted
SQL Server 2000 - Stripping time from datetime column results using DATEADD and DATEDIFF is returning result with time still?
A datetime column always includes a time part. If you set a datetime column to '2017-05-10', it'll show up as 2017-05-10 00:00:00.
When you want to compare "dates only" with datetime columns, you set ...
- 11.7k
6
votes
I have a database backup (.bak) from SQL Server v7, how do I convert it for load into v10?
You can restore a SQL 7 backup on a SQL 2005 instance, then backup/restore or detach/attach to SQL 2012 or 2014. "version 10" is SQL 2008, so you wouldn't want to stop there, probably. You can get ...
- 47.7k
5
votes
Get maximum, minimum values across columns
It's not going to be pretty (and I have no 2000 installation to test) but this should work.
The pk is the PRIMARY KEY of the table (or a column with UNIQUE NOT NULL constraints):
SELECT d.column1, d....
- 98.7k
5
votes
Accepted
how to get total count of partial counts
Try adding a column name for COUNT(attendance) - such as CountAttendance.
SELECT COUNT(*) FROM
(
SELECT student_id,
COUNT(attendance) as CountAttendance
FROM student_attendance
WHERE ...
5
votes
Can I restore a current mdf that is current with an ldf that is two years old?
Ignore the two-year old LDF file, it isn't relevant at this point.
Your only option is to re-attach the MDF, without the LDF, and get SQL Server to rebuild it.
I advise you to make a copy of your MDF ...
- 14.6k
4
votes
how to install SQL 2000 in windows server 2016 OS
I would highly recommend that you take the time and effort required to connect your app to a newer version of SQL, preferably one that is officially supported. i.e 2012+. (SQL 2000, 2005 and 2008 and ...
- 3,243
4
votes
Accepted
Index issue on upgrade from SQL Server 2000 to 2012
The unstated assumption in the question is that the subquery is executed first, then the outer DELETE is processed. This is not how things work. People write queries that express a logical requirement,...
- 90.3k
4
votes
Combining multiple query result sets that share common columns into one specified temp table
Your problem is your temp table #TruckSummary has more column than your select statement. You will need to mention the column names (one's in your select statement) in that case.
Following code will ...
- 13.6k
4
votes
Accepted
Prevent T-SQL code that is not compatible to break the script
Instead of doing the sysdatabases.comptlevel check inside of your dynamic SQL, pull the sysdatabases.cmptlevel check out to the top-level batch and use it to determine which databases to build/run ...
- 2,594
4
votes
Accepted
Query Output Window not honoring fixed length
It is honoring the fixed character length. However, it does not appear to be using a monospaced font so it looks like it isn't. The character length is fixed for the column. The character width is ...
- 2,211
3
votes
Combining multiple query result sets that share common columns into one specified temp table
Currently I am trying to combine three views into one stored procedure for a report. At first I started off using joins for the common data fields but found one of the tables "XFER" had data that was ...
- 10k
3
votes
Display columns as one row
I suggest that you pipe the results of your query to a temporary table and work from there. You query is already complex enough and to achieve the results you are after you need to perform multiple ...
- 11.3k
3
votes
MS SQL Server 2000 ODBC driver for Linux
@Dan, this is by design. SQL Server 2005 is not longer supported. Newly released client drivers support SQL Server 2005+.
That said, can you try this?
wget https://gallery.technet.microsoft.com/...
- 131
3
votes
Are there any outstanding critical security issues for Sql Server 2000?
Are there any outstanding critical security issues for Sql Server 2000?
Yes, there are no patches available for newly discovered issues like meltdown/spectre
Microsoft only releases security patches ...
- 6,318
3
votes
Accepted
conversion of a non correlated sub query to a join statement
Your initial query shows that configurations must have no more than one row (or it would fail). You could rewrite this as a join using
SELECT student_id,
COUNT(attendance)
FROM ...
- 86.5k
3
votes
How can I email query results formatted in html from MSSQL 2000 server?
It looks like you're missing an apostrophe from the end of the @body_text parameter. This fixes that problem:
@body_text=N'SELECT TOP 100 * FROM payhistory WHERE entered BETWEEN
DATEADD(wk,DATEDIFF(...
- 70.5k
3
votes
Accepted
Migrating SQL Server 2000 to 2008/14 - Stored procedures compatibility?
You should check breaking changes.
you can use upgrade advisor or
dbatools - Test-DbaMigrationConstraint
This function only validate SQL Server 2008 versions or higher.
I have written an ...
- 62.4k
2
votes
Migrating master databases between clusters by detaching/attaching them
Detach/attach is quite easily the most dangerous way to "migrate" a user database, never mind a system database.
Why do you think you need to migrate master specifically? It is probably much cleaner ...
- 182k
2
votes
Migrating master databases between clusters by detaching/attaching them
SQLServer can't work without a master database at all as it stores essential configuration information in it (such as the table of databases in the system), so it wouldn't allow you to detach it. ...
- 5,028
2
votes
Modern SQL Profiler against SQL 2000
You have to install management tools from sql server 2005/2008 or 2008R2 to manage sql server 2000.
Profiler from sql server 2014 wont work. There were problems from 2014 using profiler to 2005 - ...
- 62.4k
2
votes
Problems with insert trigger and primary key
At first I thought this was due to the INSERT INTO pre_case that you are doing, not the INSERT INTO trigger. But that turned out not to be the case after more info was supplied.
Judging by the google ...
- 5,986
2
votes
Accepted
How do you delete an aliased user
You can use sp_dropalias do drop the legacy SQL Server 2000 user alias in SQL Server 2008 R2:
EXEC sp_dropalias N'<name-of-aliased-login-here>';
This stored procedure was removed in SQL ...
- 28.5k
2
votes
Accepted
SQL Server 2000 Hitting 8000 Byte Limit Exporting BLOB
You should be using the Bulk Copy Program (BCP), a command-line utility that ships
with SQL Server 2000, for importing and exporting large amounts of data in
SQL Server 2000 databases.
For more ...
- 136
2
votes
Unable to Restore a Database to a previous point in time from a Full Backup Set
You didn't say what backups you have. If you only have a full backup, then there's no point-i-time restore. It can only be restored to the "snapshot in time" it reflects (towards the end of the backup ...
- 18k
2
votes
Accepted
Best way to copy MS SQL (2000) database files to an external drive
Per the specific requirements of getting copies of the .mdf and .ldf files, you can simply stop the SQL Server service, copy the files, then start the SQL Server service.
The way to do it without ...
- 7,984
2
votes
Count results of table by date
Are there any transactions on that particular day?
Check the data "by eye" and see if it's the query that's "missing" them or if they really aren't there at all!
Your query looks ...
- 9,486
1
vote
Prevent T-SQL code that is not compatible to break the script
You don't have to run the query in the context of each database to read its catalog. You can use a three-part name like mydb.sys.columns. This should also allow you to include the 80 compat ...
- 47.7k
1
vote
Accepted
How to retrieve subtotal for order lines (rollup does not exist in sql 2000)
Figured it out:
select TS.Order_no, part_no, cost_per_line,
case when Order_no = (select top 1 Order_no from #tempsales
where cust_po = TS.cust_po
...
- 171
1
vote
Return next row that meets condition
This answer returns your desired result and it should run on SQL-Server 2000, but it is not valid until you are not able to set a Task order.
SELECT t1.Name, t1.Age,
(SELECT TOP 1 t2.Task
...
- 23.9k
Only top scored, non community-wiki answers of a minimum length are eligible