I have a query that references a costly view as well as two derived tables that also need to reference the same costly view. The question is - is there a way to get this information back in one query while avoiding having to use the view multiple times? Perhaps SQL does not support this, and I...more >>

I have a table of keywords (hundreds/thousands of records): KeywordID KeywordName --------- ----------- 1 Apple 2 Orange 3 Pear I then have a table of products (also hundreds/thousands of records): ProductID ProductName ProductDescription -------...more >>

Newbie using SQL 2000 - First Job Schedule is failing - Need help. When I run this UPDATE query from the Table query pane I get this error message and when I click OK the query runs just fine. And the error doesn't come up again while I am in that session, only the first time I try to run the upd...more >>

Sorry, posted this question on the SQLServer.Server group as well. Think this one is probably more suitable though. New to google groups. My apologies I have a table with 15 columns. However, in my data file I only have 9 columns. I have created a format file to map the data fields to the ...more >>

database noob question. My User table refers to itself on one field. I have 3 types of user, and their UserType is stored in one column. The 2nd tier type of user "belongs" to a 1st tier user. 0st tier users are admins. So I have the following fields Users Table ======= Int64 RecID; // ...more >>

I've scratched my brain raw and searched the net. Maybe someone here will be kind enough to help. I have a table: Project Account Year Amount ABC 1234 1922 10.00 ABC 1234 1944 20.00 ABC 2234 1966 50.00 ABC 2234 1...more >>

Hi all, This is what happened last week - I tried to intentionally insert into a table with null values like this INSERT INTO Table1(ID, username) VALUES(xxx, '' ) while "username is the PK", ID is just a number with identity, and both ID and username do not allow nulls. Immediately I r...more >>

Hi, this should be an easy one for most you people but it is eluding me. I am trying to do an update using a subselect and it errors out saying it doesn't like my ORDER BY. UPDATE(webapps.dbo.presidents_blog) SET archive = 'yes', archive_date = '3/30/2007 3:03:12 PM' WHERE topic_id NOT IN (S...more >>

Hi, I have 3 views V1, V2, V3. I created a view V123, which combines three previous views. In the report I use the view V123. The report filters ( date and time and few more fields are applied only on the fields coming from V1 ). In this case, will the entire table used for the views V2...more >>

We have a stored procedure that actually generates dynamic SQL and executes it. (It's a search function basically, so we don't know ahead of time which fields the user will want to search from...) Anyway, the most common search critera are @StartDate and @EndDate. When the stored procedur...more >>

How can I identify policies that do not have at least one associated account that is active? Desired results: C-Only associated account is inactive D-No associated accounts F-Only associated account is inactive CREATE TABLE #Accounts ( AccountID int, Active char (1) ) CREATE TABLE #...more >>

I'm new to working with numeric data that must be very accurate. We're converting data from MS Access though the original source data is still in Dbase IV and I can use that. A typical number looks like: 0.7854350 In Access I linked to the SQL Server 2005 database and wrote INSERT INTO b...more >>

Hi All, I have a DTS package that runs calling a few stored procedures and an activeX module. The package does some data cleanup and load. It takes an hour to complete. I have emails sent at the beginning and end of the package execution along with error notifications if any of the step fails...more >>

This works as a where clause in a query, but I'm trying to change it into a UDF to find valid social security numbers through SQL. I'm getting a syntax error and I'm not sure why? create function dbo.ufn_valid_ssn(@ssn varchar(11)) returns varchar(11) as begin (@ssn like '[0-9][0-9][0-9...more >>

Hello all and I thank you in advance for your responses; Here is my scenario: I have table with a non incrementing identity field. I have built a website so multiple people can add information into this table simultaneously. The way my program looks up the next record is by scalar: select m...more >>

Hi, folks -- I am working on an install bootstrapper that (among other things) will install Server Management Objects (SQLServer2005_XMO.msi from SQL Server 2005 Feature Pack) iff it's not already installed. So, how to detect programmatically whether or not it's already installed? ...more >>

Hi I have a query which looks like this select * from tablename where col1='test' Sometimes in my queries I want to do this select * from tablename where col2='test' Now I want to wrap this in a stored procedure and I am curious as to what is the best way to do this, like this: cr...more >>

dear all if there is value null in first name or last name column then how can eliminate the coma that it should not come in the out put e.g if first name is Ali and last name is null then 'Ali' should b the output not ',Ali' how it can b possible SELECT LastName + ', ' + FirstName AS Mo...more >>

Dear All I have following query select Address = case when c.EngDesc = d.EngDesc and urban_rural = 1 then Address_details +','+ Locality+','+c.EngDesc when c.EngDesc <> d.EngDesc and urban_rural = 1 then Address_details +','+Locality+','+ d.Engdesc +','+ c.EngDesc wh...more >>

Hi, I am looking at a float field in a SqlServer2000 table. In the database I got the following value: 25011.799999999999. I like to export this to flatfile using DTS as 25011.80 or 25011.8 (rounding on two decimals). When rounding this using round(x, 2, 1), I get: 25011.790000000001 ! _Wh...more >>

Hi all, I am hoping you can help me. I have a sql statement which returns three rows, which i am trying to use a cursor to loop through (yuck, i know! But this is the existing paradigm of the code I am in, and I don't want to change it drastically). The select statement I am working with cur...more >>

Bonjour! I would like to know if it is possible to have an application running on SQL 2005 that would wait for certain emails coming on it and than it would convert it into a row in a table. I want to create an application that people could send an email to the server SQL and it would co...more >>

I have a stored procedure that executes a dynamic sql and returns rows. The structure of the results can vary (i.e. the columns returned are not fixed). I have a second stored procedure that woud need data returned by the first stored procedure but I can't figure out how I can receive the d...more >>

hello I'm just a student & I'm looking for a nice helpful E_BOOK ...more >>

Hi, When I was browsing some site, I came across a query as follows: select a.*, b.* from a inner join b on a.field1 = b.field1 where (a.field2 (+)=b.field2). Could any one please interpret this for me or let me know the materials that I get to learn these techniques. I am eager to learn th...more >>

I have very simple example. If I write select statement: select c.* from table1 c INNER JOIN table2 i ON c.warehouseID=i.warehouseID AND c.productID=i.productID I get 3 rows: warehouseID productID quantity ----------------------------------------- 1 'A' ...more >>

Will there EVER be a difference in results returned between using a = variable in the join vs. using it in the where clause? Select * from mytable a left join anothertable b on a.col =3D b.col and = a.somedate =3D @somedate vs. Select * from mytable a left join anothertable b on a.col =...more >>

Hi, I have a table with a field Address containing 1 string, but uses commas to distinguish between road, city, county etc. I need to build a view that has Address1, Address2, Address3, Address4, Address5. Is there a simple way to add to my select statement "SELECT FIRSTNAME, LASTNAME, ADDR...more >>