Hi, we have a very strange behavior of SQL Server that we try to understand. We have the following Select statement: SELECT DISTINCT TOP 100000 Customer.id FROM Customer LEFT JOIN CustomerAddress CA ON Customer.idDefaultAddress=CA.id WHERE 1=1 and Customer.id IN ( select idCustom...more >>

Is it possible to do something like the example below? Is the syntax correct CASE OrderMetho WHEN 0 THEN 'Fax', 'Email WHEN 1 THEN 'Telephone EN Because 0 is actually equal to both 'Fax' and 'Email'. Thanks in advance, moz...more >>

I am looking for a way to export a few million records from a MS SQL Server DB and subsequently import them into a similar table in another DB. This should all be done in code. Is there a specific strategy for this in order to happen as fast as possible? I can use either ADO or SQLDMO. Thanks...more >>

Hi, I need to extract randomly 5 records from the table "Questions". Now I use SELECT TOP 5 FROM Questions ORDERBY NEWID() And it works. The problem is that I need an additional thing: if SQL extracts record with ID=4, then it should not extract record with ID=9, because they are similar....more >>

NEED TO CREATE A CONSTRAINT THAT SPECIFIES THAT VALUES TO BE ENTERED IN A COLUMN ARE A, B, C, D, F OR NULL. tHE NAME OF THE COLUMN IS GRADE....more >>

i wanna something like this: INSERT INTO Employes (id) VALUES ( (SELECT TOP id from Employes) + 1 ) any help wi welcome TIA ...more >>

Hi I have a case statement where I am comparing two dates rpt_dt= case when ex.e_dt < a1.d_dt then "NA" else e_dt end, give me an error Syntax error during implicit conversion of VARCHAR value 'NA' to a DATETIME field. rpt_dt= case when ex.e_dt < a1.d_dt then "" else ...more >>

Hi I have a stored procedure where I want to handle all errors and return something. Although when, say, an insert violates the integrity, the standard error message is displayed. Is there any way of suppressing it? This is the piece of code; there is a 'nocount on' in the beginning INSERT...more >>

Hey Everyone, I have a very tricky select statement that I need help with. I have a table that contains orders and order details. I want to retrieve the last detail record from every unique order that was placed. The last record is known by the exact date and time that it was ordered. ...more >>

SQL 6.5 Server Installation on Win NT 4.0 Server with Service Pak 6.0. SQL 6.5 installs & sticks at the end on "Indexing Files". The MSSQL Sub Directory is created & Master.Dat is there, but no SQL 6.5. Is this a problem with istallation on a newer computer with old 6.5 software ? Is there a pr...more >>

Hello everyone: We have a sweepstake database. One of the rules is that they can only enter three times. But some people have entered a dozen times or so. I want to do a query that will return a maximum of three rows per name, address and zip collumns. Is there a way to do this? Thank...more >>

I am a newbie in SP. Does anyone know what is the command for SQL statement Like? Instead of user =, I would like to use the Like in SQL statement. Such as: ..... where Location like '%Location%' I just do not know how. Thanks. Here is my SP: CREATE PROCEDURE [dbo].[getCustomerList] (...more >>

When a stored procedure that contains multiple DML statements (e.g, 3 UPDATE statements) is executed, are they DML always executed in the order in which they appear in the stored procedure? ...more >>

Hi I am trying to get data from a SQL2000 table plus a variable field into a recordset which is held in a stored procedure. My code in VB runs the SP and hold the data in a disconnected recordset. I then want to, depending on logic, update the variable field with data and then output. Th...more >>

Is there a way to find out how many sp and udf are using a certain udf in a database. If the function I am changing is only being used in one place there will not be a problem. However, if the function is being used in other places there might be a problem. ...more >>

I guess I really don't know how to use the "And" operator in a Join clause. I have 3 tables - tblTotal, tbl1, and tbl2. tblTotal has an ID field and a Region field of interest. Two Regions - 'N' and 'S' in tblTotal. tbl1 cointains records from tblTotal for region 'N', and tbl2 contains...more >>

Consider the following query: select soundex('MS'), soundex('IBM'), difference ('MS', 'IBM'), difference ('IBM', 'MS') Results from Microsoft SQL Server 2000: ----- ----- ----------- ----------- M200 I150 2 1 Results from IBM DB2 version 8.1 ----- ----- ----------- ----...more >>

Hi Im trying to use the stored procedure xp_sendmail, but I have run into a problem. If I try: dbo.xp_sendmail @recipients = 'mymail@mydomain.com', @message = 'test' I get the following error: "xp_sendmail: Procedure expects parameter @user, which was not supplied." but if I add the @user ...more >>

When you create database diagrams in Enterprise Manager, the details for constructing those diagrams is saved into the dtproperties table. This table includes an image field which contains most of the relevant infomation, in a binary format. SQL Enterprise manager offers no way to script out ...more >>

I need to write a stored procedure which will return 1 record from table Job which is 20 records away (bigger for instance) from the current record defined by input @JobID parameter. For instance I pass @JobID = 1000 and would like to get a record with JobID = 1020 if there are all records betw...more >>

Hello, With reference to performance, which is better - using union in a query or creating a temp table Thanks Prasanna....more >>

Hello, I'm using SQL Server 2000. I would like each line of my SELECT to contain a column giving the number of the current line. It seems the only way to do that is something like: INSERT identity(int,1,1) as lineNumber,* INTO newTable FROM oldTable But oldTable as an identity column...more >>

Hi, I have three datable tables. The table schema is as below. They all have the same foreign key field tblID, and each has individual primary key field as tbl1ID, tbl2ID, tbl3ID. The table is sorted by its primary key field. What I want is to get a result like: tblID tbl1ID tbl1Fld tbl2...more >>

Hello, Can anyone recommend some good Anti-Virus Software for Windows 2000 and 2003 Server version? Seems like Symantec, Norton AV is asking IT professionals to buy minimum 5 licenses just to get a server version. Totally B.S. I want to be able to also exclude files, such that for SQL S...more >>

Is there a way to shrink a database in production without blocking user connections? I have a 75 GB file and want to shrink it down 30 GB. The file had become empty after archiving data. I run the following syntax: USE CustomerService GO DBCC SHRINKFILE (customerservice_dat...more >>

Is there a limit on the size of a character expression, that I can change? In the following (GUI is text): SELECT '(''' + REPLACE(CONVERT(char(5000), GUI), '''', '''''') + ''')' FROM MyTable WHERE GUI IS NOT NULL I get: ('my-stuff But I would expect (and want) to get: ('my-stuf...more >>

Hello, I'm trying to insert into a table consecutive pairs from another table. Right now, I'm using a cursor but I wonder if there's a faster way. Example: My table1 contains a column of integers: 1, 20, 3, 45, 12 I want to obtain this in a two columns table Table2 : (1;3) (3;12) (12...more >>

I am having a bit of difficulty understanding the performance issue I'm facing, as follows: Here's the WHERE clause of my SQL statement: WHERE (DN.OFFICE_NUM = 3) AND (DN.ProcedureID < 9000 OR DN.ProcedureID > 9999) AND (DN.ProcedureID <> 5985) AND (DN.PostingDate BETWEEN...more >>

It's my understanding that [suser_name()] has been changed to [suser_sname()] in SQL Server 2000... my question is: are there any other gochas? thx d --- Posted using Wimdows.net NntpNews Component - Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post ...more >>

When running this query i find that in the select staement if age and sex is not selected the query does an index seek otherwise it does a Tbale scan....The other colums have a non clustered index on them while age and sex do not. What causes teh table scan to take place....is it necessary t...more >>

Lets say I have a table called BudgetDetail with 3 fields in it. DeptID (which is the Key and is an Int), and two float fields called BudgetAmt01 and BudgetAmt02. Further, I run the following query: SELECT DeptID, SUM(BudgetAmt01) AS Jan, SUM(BudgetAmt02) AS Feb FROM BudgetDetai...more >>

My access database I have "medium time" But the time being displayed is 11:00:00 PM How do I make it say 11:00PM (Not sure if it shold have a space before PM) Below is the statement I am using..... SELECT DateOfEvent, EventName, Location, StartTime FROM Events where DateOfEvent >=...more >>

Hello, I am trying to retrieve data from a table where a date column is blank. I have the SQL statement as WHERE DateField = "" and I receive a data type mismatch error. Does anyone know how to fix this? ...more >>

Hi all How can SqlServer communicate with an external application ? Te task: Each time a given value is inserted/updated in a specified field in one of my tables, I want the trigger on this table to send some kind of message to a running application (which I have to write). How can the t...more >>

I have a delimited text file with a column that has date in 'yyyymmdd' format but fails to import into a datetime field. I get a conversion error. I can do an INSERT no problem but the DTS process fails. Any ideas? Thanks....more >>

Certain of our SQL 2000 installations produce error 7399 when attempting to read/write to Excel from within SQL Server (e.g. using the OpenRowset command, using Linked Servers etc). The error produced is: Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0'...more >>

I can't work out how to get what I want in one statement. With this query: select coalesce(contactid, 0) from tblPeople where email = 'this_value_fails@email.com' I get no value back: not zero (which is what I want), not null, nothing. FWIW, entering a good email address gets me the con...more >>

Hello Experts, In earlier Versions of SQL Server (perhaps even before applying Service Packs of SQL Server 2000), when I attempted to create an Index with same name to two different Tables, I get an error msg, that thereabout said "IX_ExampleName already exists." Is this handle ever exis...more >>

Hi I'm trying to optimize my SQL Server 2000 Database. I'm looking at ways to improve 3 SPROCS in particular Initially I intend to discover the baseline, by running each SPROC 10 times and working out the average I intend to use the commands DBCC DropCleanBuffers and DBCC FlushProcInDB(@DB) t...more >>

Using the following piece of code - I would like to assign the result of a query (see Select top 1 b.bookingvalue) to @Valu I do I achieve this Thank --My Cod DECLARE @value varchar(50 DECLARE @Count int SELECT @Count = COUNT(*) Activit WHERE Customerid = IF @Count > 0 BEGI select to...more >>

Help urgent please, I've a piece of code that needs to alter or create a table dinamic, at the end I need to "run" the @stfield Thanks in advanc R Create procedure P a declar @cont int @stfield varchar(255 begi create table t (c1 int null select @cont = while @cont < begi ...more >>

Hello I have a table that one of its column is in dateTime format. My date format in the pc is dd/mm/yyyy (in the Regional Options) In the Query Analyzer I run "SET DATEFORMAT DMY" but when I try to enter data as "21/3/2004 12:00:00" I get error. when I try to enter data as "3/21/2004 1...more >>

Hi guys how to get the number of vowels in a column? Out put should Like this, without using UDF's Column1 Count ----------- --------- abc 1 Curious 4 ...more >>

Hi I m trying to create a simple INSTEAD OF trigger for a view containing a couple of linked tables. One table contains a foreign key (FK) to the other which must hold a related record. However, when I try to insert a new record in Enterprise Manager I get the "Cannot insert the value NULL in...more >>

hi, How i can retun my current Database name in TSQL??? Thank Pippo...more >>

Dear Friends I am facing problem with the backup of SQL i think the backup is corrupt and i need to restore the same as there is one critical issue for the payment which is missing. Please suggest how i can do the same is there any tool which can recover the currupted backup file and a...more >>