FAQ Database Discussion Community


How to remove white spaces from columns in SQL Server 2000?

sql-server,sql-server-2000,whitespace
I have an old SQL Server 2000 database from which I read data. My problem is every query involving a String returns a value the size of that column filled with blank spaces. e.g: let's say we have a column called NAME CHAR(20). All queries would return: "John " instead...

SQL Server : join number between different date

sql-server,sql-server-2000
I have 2 tables like this: num datetime color ... ........ ..... 40 2007/07/07 red 40 2012/12/01 white name datetime num .... ........ ... Tom 2008/01/22 40 Tom 2014/02/27 40 How I can do inner join on those tables like this? name date num color .... .......... ... ...... Tom...

Make date with month week and weekday in SQL Server 2000

sql,sql-server-2000
In SQL Server 2000, I have: Year: 2015, Month: 01, Week: 4, Week-Day: 2 I need: 2015-01-19 I have : Year: 2015, Month: 01, Week: 3, Week-Day: 3 I need: 2015-01-13 And so on. Thanks in advance....

Export USP Results To CSV

sql-server-2000,export-to-csv
I have a stored procedure which basically runs a insert into statement for about 5 tables and places all the data into 1 table (easier to work with this way). I was wanting to instead of inserting into the table, just exporting each result-set into a different csv file (taking...

Join Clause With a XOR Statement

sql,join,sql-server-2000,xor
I am doing a join and I can't seem to make this XOR to properly work. SELECT t1.COMPANY, t1.MILES, CASE WHEN t2.MILES IS NULL THEN t3.MILES ELSE t2.MILES END AS MILES2, CASE WHEN t2.MILES = t1.MILES AND t2.MILES != 9999 THEN t2.FLATRATE ELSE t3.RATEBASIS END AS RATE FROM TABLE1 AS...

SQL Server 2000 query conversion [closed]

sql,sql-server,sql-server-2005,sql-server-2000
I have a query written in SQL Server 2000 and I want to re-write that to use in SQL Server 2005 and newer versions. CREATE TABLE #TEMP(AffinityGroupName VarChar(100), MostPopularVehicle VarChar(100), LeadCount Int, CNTL_ID int, CNTT_ID int, DayTime datetime, MemberFirstName varchar(50), MemberLastName varchar(50), MemberAddress1 varchar(150), MemberAddress2 varchar(150), MemberCity varchar(50), MemberState varchar(2),...

How to filter data on one table based on reference data on another table?

sql,sql-server,sql-server-2000
I have two tables account_agent and bulk_index. The data on each table is as follows account_agent name code account remark scheme avi 10 151362 first as babi 11 123478 new as avi 10 151721 new as avi 10 151765 new as sam sas/01 925458 tin sas bulk_index agentcode account customer_name...

How to transfer the server logins from SQL Server 2000 to SQL Server 2012

sql-server-2000
I have migrate the database from SQL Server 2000 to SQL Server 2012. But how to transfer the server logins from SQL Server 2000 to SQL Server 2012? The server logins are below the Server Security.

List Of Views Not Containing Certain Field

sql-server,sql-server-2000
I know the syntax to get a list of all views in a database, but how could I then flip that to show me a list of all the views that do not contain a field? For example, I want to see all views that do not contain the field...

select multiple records based on order by

sql,sql-server,sql-server-2000,greatest-n-per-group
i have a table with a bunch of customer IDs. in a customer table is also these IDs but each id can be on multiple records for the same customer. i want to select the most recently used record which i can get by doing order by <my_field> desc say...

SQL Server : query error

sql,sql-server,database,sql-server-2000
I try creating this table: CREATE TABLE [dbo].[WEBENGINE_ACTIVE_SESSIONS] ( [session_user_id] [int] NOT NULL, [session_id] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL, [session_time] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL, [session_ip] [varchar](25) COLLATE Chinese_PRC_CI_AS NOT NULL, CONSTRAINT [PK_WEBENGINE_ACTIVE_SESSIONS] PRIMARY KEY CLUSTERED ([session_user_id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO and I get...

Joining view and tables in sql server

sql-server,sql-server-2000
I have the following tables. Customer Customer_id, Customer_name Customer_details Customer_id,Phone_no,Address Order_details Order_id,Customer_id,Order_type I have created a view as following Create view Orders_Analysis Select c.Customer_id,cd.phone_no,od.order_id From customer c inner join order_details od on c. Customer_id=od. Customer_id Inner join Where c. Customer_id=cd. Customer_id cd. Customer_id=c.Customer_id Now using the above view and pre...

Count Rows On a Condition in SQL SERVER 2000

sql,sql-server-2000
I want to fetch data in a following format from SQL SERVER 2000 Department | TotalPresent | Employee | Officer XYZ 12 6 6 I am running this query SELECT a.department, Count(emp_id) AS totalpresent, CASE WHEN a.type = 'Employee' THEN Count(a.type) ELSE 0 END AS employee, CASE WHEN a.type =...

Fix Query Display Row With The Last ID With SQL Server 2000

sql-server-2000
I have row table like this : ID EQ TI LE VAL NO ================================== 1504 TX 410413 Y 2065000 1 1504 TX 410413 Y 2065000 2 1504 TX 410413 Y 2065000 3 1504 TX 410413 Y 730000 4 1504 TX 410413 C 2606900 5 1504 TX 410413 Y 4120900 6...

Foreach loop in the database SQL

sql,sql-server,sql-server-2000
I have two tables in the database (Account and Transaction). I want to get the latest transaction of an account in the Transaction table. If I do it in, let's say PHP. It would be just get first all the accounts in Account table, stored in an array. Then select...

NHibernate variable limits with SQL 2000

c#,nhibernate,sql-server-2000
I am trying to use the Take method in hibernate with SQL 2000. When doing so, it gives me the error:Dialect does not support variable limits. In searching around I found a way to override the check for variable limit support and set my code up as follows: return Fluently.Configure()...

SELECT WHERE DATETIME is within 4 hours of another DATETIME SQL Server 2000

sql,sql-server,datetime,sql-server-2000
I have two tables with the datetime datatype, CreatedDate and CompletedDate and I want to find all the rows where the CompletedDate is within four hours of the CreatedDate. My source database is SQL Server 2000 and it is linked to my SQL Server 2008. Created Date Completed Date 2015-03-05...

Using case and like statment without duplicate results

sql,sql-server,sql-server-2000
I am trying to use case and like statement. When I run this below query it is duplicating results on some rows where it is y on particular columns. I am using SQL Server 2000. SELECT DISTINCT [rjvn_pound_reference] ,t_reference ,t_street_name ,t_zone_name ,( CASE WHEN rjvn_note LIKE '%CORRESPONDENCE%' THEN 'Y' ELSE...

store nvarchar larger than 4000 in sql 2000

sql-server,sql-server-2000,nvarchar
I'm trying to execute sql using @SumSQL var type nvarchar 4000 , when i enter a value of parameter in my stored procedure larger than a specific value like '11' the program return sql error ... the problem comes that when the sql store the char in the var @SumSQL...

Erroneous query works when used as subquery - how? Why?

sql,sql-server,select,subquery,sql-server-2000
Let's consider two tables: MY_DB.dbo.MY_TABLE1 that does contain column MY_PROBLEMATIC_COLUMN MY_DB.dbo.MY_TABLE2 that does not contain column MY_PROBLEMATIC_COLUMN As expected, query1: SELECT MY_PROBLEMATIC_COLUMN FROM MY_DB.dbo.MY_TABLE2 Results with: Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'MY_PROBLEMATIC_COLUMN'. However, this query2, which uses query1 as a subquery, works: SELECT...

Join two tables - One common column with different values

sql,sql-server,join,sql-server-2000
I have been searching around for how to do this for days - unfortunately I don't have much experience with SQL Queries, so it's been a bit of trial and error. Basically, I have created two tables - both with one DateTime column and a different column with values in....

Rolling time frame without temp tables

date,sql-server-2012,sql-server-2000
I need to pull the sum of sales over a rolling 12 month time frame but my DBA did not give me the rights to make temp tables so I can not use those like I have seen in several posts when looking online. Also most of the most I...