Hi, Is there any way to make the following LEFT OUTER JOIN statement work in a similar fashion as the SUBQUERY statement that follows it? Customers 1 and 3 haven't ordered anything for the OrderDate '2006-01-01' and I want to exclude them from the display in the LEFT OUTER JOIN query (like...more >>

Hi, I am comparatively new to SQL and was just thinking if anyone can tell me the most efficient way of doing this. I have a table in which Data is arranged in the vertical fomat, as shown below - HospitalID MonthCode New Total 1 2005-12-1 0 2 2 2005-12-1 1 ...more >>

It was always the custom in Enterprise Manager, when creating a view, to create a dummy view, e.g.: SELECT 1 save it, then go back and edit the view. This way, you could edit the view definition without Enterprise Manager destroying your formatting, and you can add comments. Now u...more >>

Hi, I was asked to create a SP that will return 7 random numbers from a range of numbers. SP should take 3 parameters: MinValue, MaxValue and InitialSeed. If and only if all 3 parameters are the same, the same 7 random numbers should return. I have done something so far, just don't know if...more >>

Hard as I tried I just could not get any error when execute this sentence For the following table identity(20,2) content ---------------------------- 42 44rrr 3 test 3 test 44 48rrr 44 test when execute DBCC CHECKIDENT('Table1'), the following message is outputted. Checking id...more >>

Hi, Below is the traditional use of CTE for retrieving nodes of a tree. My question is that when I use a condition like 'where lvl<=2' the execution plan shows that filtering the result is the final phase of execution. Does it mean that if I have a deep level of hierarchies in my table, the ...more >>

Given table "Entities" with PK "Entity_ID", and a table "PhoneNumbers" related by FK PhoneNumbers.Entity_ID, how do I retrieve a list of Entities that have more than one phone number? I know I'm going to need a COUNT() subquery, but am unsure how to nest it given the parent Entities table. ...more >>

Hi, For a hosting company, I need to protect databases from unrelated logins. I mean logins must not be able to see other databases while using SSMS. But be able to view their own database in the list. Any help would be greatly appreciated. Leila ...more >>

I have two tables, tblRequests and tblProcesses which are linked via RequestID. In a Stored Procedure I want to insert two records, one into the main table tblRequests, get the RequestID and use the RequestID to insert a row in the second table tblProcess all within a single transaction. How d...more >>

I hesitate to start these discussions, as I've seen that they very quickly turn hostile and often off the original topic. But in the interest of getting my question answered, I'll roll the dice. I'm looking for comments on naming conventions used when naming primary key columns and foreign ke...more >>

We have several views that are used by Crystal Reports and other reporting apps. These views join together several tables. We have noticed that if I add a column or two to one of the tables that the view is based on, the view will display data in the wrong column. For example, it may show Zipc...more >>

Hello to everyone, I am trying to check scripts when copying column names from one Database to another Database. If the Trigger is the answer. I don't know how to write the script properly calling out the Database name. Please help. Thanks so much,...more >>

Hello to everyone, Please help me on how to Create Defaults using Column Names. I also don't how to use computed column names. If the latter is the answer. Please help. Thanks...more >>

Hi, What should I be taking into concidarration while deciding if I enforce unique (on a column other than a PK or clustered index) by using a UNIQUE INDEX or using a UNIQUE CONSTRAINT? Using sql srvr 2000 sp3a. The column is of type char(10). Thanks, Yaniv ...more >>

I have a problem. I'm building a list of IDs (all numeric) and I know that I can create a SQL statement that goes something like the following: SELECT name FROM people WHERE id IN (1, 2, 4, 5, 7) where the number of values in between the parenthesis is variable. I can create this with th...more >>

I have a column for which a NULL value is allowed. However, when the user provides some value for the column, I want that value to be unique. Aside from using a trigger to verify the uniqueness at Insert/Update, is there another way to guaranty uniqueness yet allow NULL values? (I use SQL 2000 ...more >>

Hi all, I have a table of format: Key Value ===== ====== key1 1 key2 2 key3 1999-01-1 .... Where Key column holds the unique key and the Value column holds each of the Key's corresponding value. The Value column is of type Varchar so that it can hold multip...more >>

I am looking at the graphical estimated execution plan in Query Analyzer for a query. One of the steps in the query is in red and indicates "Warning: Statistics missing for this table..." for a column. However I have used both sp_helpstats and DBCC SHOW_STATISTICS to verify that statistics do ...more >>

I have the following SP CREATE PROCEDURE procCreateARReportTable AS if exists (select 'x' from Information_schema.tables where table_name = 'ARReport') BEGIN Print 'Table Existed, drop table' Drop table ARReport END print 'Create ARReport Table' select acctno, dateo...more >>

hi all, We are working with sql 2000. We have a process that runs on serveral compatible sql servers. This process pulls data from one server (on sp3) onto these receiving servers. Some of these receiving servers are on sp3 and some of them are on sp4. None of the sp3 one have this fail...more >>

Hi everyone, I am calling SQLDOM.ScriptTransfer method from an ActiveX Script Task inside a DST package: objSQLDMO_DB.ScriptTransfer objSQLDMO_Transfer, 2, sPath Works fine, "sPath" file is created with all the scripts I need. However, if I don't want to save the scripts into a fil...more >>

There are a few easy ways I could test for the existance of a specific row in a table, but what is the most efficient? What if it's an indexed column? SELECT TRUE AS DoesExist FROM myTable WHERE MyID=5; Would seem to be efficient, but won't that check all the Rows, even if it already fo...more >>

Hello, I need to insert some records from tbl1 to tbl2. tbl1.ID is a pk and tbl2.ID is a PK. The problem is that tbl2 already contains some of the records from tbl1 whcih is causing a PK violation. what tsql statement can perform the insert without violating PK? Insert Into tbl2(ID, f...more >>

Please help. I have this query which works fine to get the maximum create date between 6 tables: create table #temp1 (create_date datetime,call_stat char(30)) insert into #temp1 (create_date,call_stat) (SELECT create_dt,'Disposition' as call_stat FROM tblotherdisposition where scp_seqno =...more >>

CREATE TABLE test (stk_num varchar(3), avg_num real, import_dt smalldatetime) INSERT INTO test values('aaa',27.44,'1/23/2006') INSERT INTO test values('aaa',25.00,'1/30/2006') INSERT INTO test values('aaa',1.76,'2/6/2006') INSERT INTO test values('bbb',2.45,'1/23/2006') INSERT INTO test value...more >>

Our transaction log grows at twice the rate of our data. How do we maintenance this file? Currently we have a job that creates a backup of the database (data & log) nightly. Also, how can I view the database log?...more >>

I am having trouble inserting records into a table which has a unique index on a computed field (varchar 16) and 2 character fields. I get an error saying that the ARITHABORT settings are incorrect no matter how I set them. If I create a unique index on 3 char fields all works fine. Is ther...more >>

i'm using sql 2000 with reporting services standard edition. is there a way to find out or query the version for the reporting services? thank you. ...more >>

Hello, I am trying to modify an existing column in an existing SQL 2005 table to become a primary key with autoincrementing values. This is what i have so far... alter table myTable alter column myID INT NOT NULL PRIMARY KEY IDENTITY(1,1) What am I missing on this? Thanks in Ad...more >>

Hi, is it any EM for SQL 2005 Express Edition? how to view the data? Thanks. ...more >>

I can not assign more than one variable using this cursor: DECLARE Itemid_Cursor CURSOR FOR select distinct itemid,itemname from items OPEN Itemid_Cursor FETCH NEXT FROM Itemid_Cursor INTO @itemid,@itemname ...more >>

Hey Team, I have this function to locate objects by GUIDs across multiple tables... I was using UNION which worked perfectly, but in order to increase performance, I want the proc to stop after it's located a row. It works fine, except I have an issue with multiple record sets, where if th...more >>

The code below is based on the sample provided in the MSDN documentation for the ADO Recordset Save() method. The purpose of including this code is to illustrate a behaviour we have noticed and to ask for clarification / advice. The code simply opens a connection to the Pubs database, opens ...more >>

I have a financial database which tracks charges and payments form clients. I need to create a query which returns only those months when a client hasn't paid in full and the amount still owing. So, if the table looks like this: client date(mm/dd/yyyy) charge payme...more >>

OK, everyone. I have hit a wall while trying to accomplish the following goal. I have a table (tblDocInfo) that stores information about documents. Among the information being stored, I have three fields that store information about attorneys (ReviewingAtty, DraftingAtty, ContactAtty) who a...more >>

Hi, I ran trace which caught huge number of exception events related to one stored procedure which uses temporary tables. Exception events are: error 208, severity 16, state 0. This means "Invalid object name '%.*ls'.". I tried to catch which exactly statement causes this exception, but ther...more >>

How to write T-SQL to implement the DTS transform task for large tables? How to disable log? or do batch update/insert? Is it possible?...more >>

My charindex seems to always return 0 no matter what. I tried it on different SQL servers and I always get 0 no matter what. Here was a test script I tried and still got zero. DECLARE @myvar as varchar(25) DECLARE @myvar2 as varchar(25) SET @myvar = 'hello' SET @myvar2 = 'll' PRINT CHAR...more >>

Hi There, is it possible to Concatenate strings using group by Select Concatenate (Name) from table1 group by dept -- Thanks Ganesh...more >>

Hi, I'm looking for a function to convert a character to hexadecimal. E.g., 7 -> 37 Can anyone help pls ? TIA !...more >>

I am trying to debug a stored procedure on an SQL Server Database present on a server. When I use the Debug... option from Sql Query Analyser, Query nalyser prompts me for parameters for the stored procedure but when I supply the parameters and hit OK, the stored procedure simply runs through wi...more >>

I have a query which is like this Select field a, field b,field c, field d from table1 where field d not in (select distinct field d from table 2) Table 1 has 35000 records Table 2 has 12391876 records Field d is of type varchar. If there is any duplicate to be found in table 2 th...more >>

Afternoon all.. I'm trying to join two tables together. The tables have information like: Table_Data Name - Calls - Sales Bob ---- 17 ----- 10 John --- 23 ----- 5 Dave --- 25 ----- 7 Carol -- 16 ----- 13 Table_Target_Data Name - Calls - Sales Bob ---- 30 ----- 20 Carol -- 40 -...more >>

I'm writing a simple application that stores/pulls data into/from SQL 2005 Express Edition database. In one process data is inserted to the database, on another process same data gets pulled out. Both operations complete successfully and fast however the puller sees the inserted data way aft...more >>

Hi There, We use lot of servers, My program lists all the servers but just names only, I'd like to display with description how can i do that dynamicaly For each server i want to add a propery live, test uat, test sit something like that Basically want to add property to the server not ...more >>

Hi all, I'm trying to compare two dates, from two tables. Both are of datetime datatype. I dont want the time portion to be involved in the date, as I only need to compare days. Having the datetime in the comparison yeilds unexpected results Thanks Robert ...more >>

Hi all, I am trying to return a true / false value via case statement. The boolean value returned is determined whether a column contains a null value. Can someone help with the following query as it is causing an error... SELECT q.ColumnID, q.ColumnText, CASE a.ColumnID ...more >>

...more >>

Hello there I've imported table without any unique key The table have some duplicatate data on it. and i need to delete the duplicate data So far i had to alter the table and add unique field, and use it to delete the duplicate records Is there a way to do this without altering the t...more >>

Dear All, I want to know, how to retrieve the foreign key for a table using T-SQL. Regards, Sathiamoorthy ...more >>

Hi, Used Mssql 5 exp. On win 2003 server my problem, client user time out 15 minute by sql server, Client user inside my program, connection closed, logout program and relogin program work again, no network problem, no closed network connection How can i do, ...more >>

Hi, I want to implement an organization chart. I use adjacency list model (like implementation of Employees table in Northwind). My problem is that sometimes an employee in not directly beneath another employee. For example an employee is parent of three "Divisions" and these divisions contai...more >>