Hi, When I run this code, it returns "2" which is expected: declare @x table(col1 int) insert @x select 1 insert @x select 2 select * from @x where col1 not in (1) But when NULL is added to the list of IN, then "2" disappears from te resultset: declare @x table(col1 int) insert @x se...more >>

Currently I'm rewriting SQL code written for Sybase SQL Anywhere into MS SQL 2000. I'm learning a lot of stuff... the latest is I can not use List() as an aggregate funcion... Does anyone know any workaround to get such function working? Thanks..! ...more >>

I'm trying to import a csv file using a BCP Data task in a DTS package in SQL 2000. I can't seem to get the format file for BCP right. The file I want to import has 8 columns of data. The table I'm importing into has 9 columns. The first column is an ID column and I want it to generate during ...more >>

The query returns: EventId EventTitle Active Registration Addendum 2 Test -1 7 7 3 Future -1 0 0 4 Construction 0 0 0 The Numb...more >>

We store all of our dates as UTC however we would like to retrieve these according to the local time zone setting for both current and historical dates and times. There doesn't seem to be a function that performs the reverse of GETUTCDATE(), i.e. it gets the local date & time based on the cu...more >>

I have an issues with a sp that is calling a view. Basically in Profiler it is showing the entire View: select * from contact c join contact_stuff cs on c.id = cs.id where c.status = 'A' -- count 1 million records sp: select * from view where v.id = 15 When running this and viewi...more >>

Hi all, This works but will not return all the columns on the sheet? Willn't return the last 10 columns. I even went into DTS and doesn't see the last 10 columns? SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\MyServer\MyShare\test.XLS', 'SELECT * FROM [Result...more >>

I have a couple of q's after reading the link regarding getting the above certification. (Note: I do not have my 2000 MCDBA certification.) http://www.microsoft.com/learning/mcp/mcitp/dbadmin/default.mspx 1. Do you have to take all the courses for each of the two required exams? I notice...more >>

I need to join on two tables by a symbol field. The problem is that one table contains the symbol but with a prefix and a suffix Table 1 Symbol 0.1PGOmPLECINWE 0.1PGOmPLEF Table 2 Symbol_ID #DF0.1PGOmPLECINWEJ08 #DF0.1PGOmPLEFC07 I can't use substring because the symbol_id is not a c...more >>

Here is my issue. This is not the complete table structure, but only the fields that are necessary for this discussion. CREATE TABLE Loads (LoadNumber int NOT NULL, Bounced bit NOT NULL) INSERT INTO Loads VALUES (1,1) INSERT INTO Loads VALUES (1,0) INSER...more >>

I want to run a SQL statement in Query Analyzer as user, "developer". Here's how I did it: Select Start->All Programs->SQL Tools->Microsoft SQL Server->Query Analyzer. Then I right-mouse click "Run As" It at first gives me a dialog to prompt for user name and password. I entered "develope...more >>

I want to run a SQL statement in Query Analyzer as user, "developer". Here's how I did it: Select Start->All Programs->SQL Tools->Microsoft SQL Server->Query Analyzer. Then I right-mouse click "Run As" It at first gives me a dialog to prompt for user name and password. I entered "develope...more >>

I want to run a SQL statement in Query Analyzer as user, "developer". Here's how I did it: Select Start->All Programs->SQL Tools->Microsoft SQL Server->Query Analyzer. Then I right-mouse click "Run As" It at first gives me a dialog to prompt for user name and password. I entered "develope...more >>

I want to run an SQL statement by a user, "developers". I was told that I could do this by selecting Start->All Programs->SQL Tools->Microsoft SQL Server->Query Analyzer, then clicking on the right mouse button and selecting "Run as..." . At first, it gives me a screen ("Run as") to prompt m...more >>

I cannot find the link that has the 2005 MCDBA course requirements. I have found the SS 2000 MCDBA. If someone could post the link, I'd appreciate it......more >>

I have a table in sql 2k. the table has only 3 fields as follow: RequestTypeID char(10) not null Activities varchar(255) not null BD int(4) not null When I tried to copy and paste some data within the activities field, I got the following message: .."key column information is insufficient...more >>

I am trying to get the patient’s time in treatment in days by comparing multiple records in a table. The DDL for the table is CREATE TABLE #temp (patient_id int not null, clinic_id char(9) null, service_date datetime null) A patient will ...more >>

What is the best way of passing a dataset from .net to to a SQL Stored procedure? I a would like to use the xml datatype or xml as a string. How would I process the xml once it arrives at a stored proc? -- Arne Garvander Certified Geek Professional Data Dude...more >>

I am trying to update a column with two criteria and it will not accept the second criteria statement. update dbo.Rates set RetainedCost = 8 (select ModelNo, Month from dbo.Rates where ModelNo Like '%10' and Month = '04') This is one table that is being updated. The retained cost is updat...more >>

Hello All, thought I'd ask here first. Has anyone experience this error message "Subquery returned more than 1 value" when the query has multiple joins on several tables but no subqueries written in the SQL syntax? SELECT D.*, l.[Column] , l.[Column2] As AliasColumn3, r.[Column3] FROM Tab...more >>

Hi all I have a funny little problem ... I am trying to use the function ISNUMERIC like in the following examples: ***************************************** DECLARE @TestString varchar(50) SET @TestString = 'Hi Newsgroup' SELECT CASE WHEN ISNUMERIC(@TestString) = 1 THEN CONVER...more >>

Hello, I am currently writing a query using a DB which someone else designed. One of the tables uses a reserved keyword, namely dbo.User. Everytime I included this table in my SQL, I get the error "Invalid object name 'dbo.User'." I have tried including brackets like [dbo.User] . The SQL pa...more >>

I'm trying to join 2 tables in a conditional way: select llt.* from llt join v on case v.tipo when 2 then 's' when 12 then 'n' else '' end = llt.flag The aim is to join v table rows to llt table rows following these assumptions: if v.tipo value is 2 then join v table rows to llt table ...more >>

Hi, I receive an E_FAIL error when my program try to update a column. The bug arise only when the data > 256 MByte (and < 2GByte). column type : image Server : MSDE 2000, version - 8.0.2039 (sp 4???) technology : OLEDB, from C++ -> CDynamicAccessor As I see the provider read all data fro...more >>

Hi Guys, This is going to sound like a newbie question, but I feel I need to ask it, I have 2 variations of the same SP with the only difference being the where clause Version 1 CREATE PROCEDURE [dbo].[USP_FMSRX140] (@X int, @Y int) AS --Delare the StartIndex Variable declare @StartIn...more >>

Hi, I have a problem, I´m working with SQL Server 2005 database but I need that database in a SQL Server 2000 database format. Thanks for your help. ...more >>

Hello I am trying to list all the particular properties of my databases, in a mixed server environment. Specifically I am interested in the recovery mode, so in 2005 and use select [name], recovery_model_desc from sys.databases order by 2 asc however this s not an option on th...more >>

Dear All, I am working on both SQL2005 and MYSQL4.0. There are some great = functions / features in MYSQL that is missing in MSSQL. In MYSQL, GROUP BY can let you to use non-aggregated columns in SELECT = that do not appear in GROUP BY clause: http://dev.mysql.com/doc/refman/5.0/en/group-by...more >>

Hi, One of our customers has strange problem with their SQL Server. We have written an SP for them that retrieves 12 rows from 12 light queries and inserts into a table variable. Finally selects all rows from table variable and returns to the caller. In normal situation this SP finishes within...more >>

Hello everyone,Example table:+--------+------------+-------+ | type | variety | price | +--------+------------+-------+ | apple | gala | 2.79 | | apple | fuji | 0.24 | | apple | limbertwig | 2.87 | | orange | valencia | 3.59 | | orange | navel | 9.36 | | pear ...more >>