Query Help-----prob An Easy Answer Out There Somewhere
Sep 21, 2007
I am curretnly a newbie at SQL Server..but i am really good with Access.....i am looking into converting to SQL Express but i have this one issue. Below is a snippet of SQL from my VB6 app. SQL Server 2005 says i cant use 'Cdbl'
T, W, L_value.....are strings in the varchar in my SQL DB. OS_T, L, W dont exist on my actual DB i make the RS disconnected and play with the data. This snippet works fine in Access, but as expected it doesnt in SQLS...Wha are my options, if any? Thanks in advance for any help or advice.
I am curretnly a newbie at SQL Server..but i am really good with Access.....i am looking into converting to SQL Express but i have this one issue. Below is a snippet of SQL from my VB6 app. SQL Server 2005 says i cant use 'Cdbl' T, W, L_value.....are strings in the varchar in my SQL DB. OS_T, L, W dont exist on my actual DB i make the RS disconnected and play with the data. This snippet works fine in Access, but as expected it doesnt in SQLS...Wha are my options, if any? Thanks in advance for any help or advice.
Easy SQL question....first to answer correctly gets...the satisfaction of knowing he helped a fellow developer in need.
Runnning SQL 7.0 on an NT 4.0 platform in a client server network environment. Authentication is done on the NT level. Each user has his/her own name and password. Most users use the same shared software app.
QUESTION: Can the same user login to multiple machines concurrently and run applications without fear of locking or hanging or causing other mischief?
the first line works, but the 2nd line doesn't. I need just the CompanyKey as the table name. Help appreciated. sqlcreate = "CREATE TABLE Company_" + CompanyKey + " (UserID VARCHAR(50), First_Name VARCHAR(50), Last_Name VARCHAR(50), Title VARCHAR(50), Division VARCHAR(50))" sqlcreate = "CREATE TABLE " + CompanyKey + " (UserID VARCHAR(50), First_Name VARCHAR(50), Last_Name VARCHAR(50), Title VARCHAR(50), Division VARCHAR(50))"
How does one get the primary key of the row that is joined in via agroup by aggregate clause when the aggregate is not performed on theprimary key?For example,Person table(PersonID int,FirstName varchar(50)LastName varchar(50))Visit table(VisitID int,PersonID int,VisitDate datetime)These are simplified versions of my tables. I'm trying to create aview that gets the first time each person Visited:selectp.PersonID,min(v.VisitDate)fromVisit vjoinPerson p on p.PersonID = v.PersonIDgroup byp.PersonIDThe problem is that I would like to return the VisitID in theresultset, but when I do it expands the query since I have to also putit in the group by clause.What are the different ways to achieve this?Subqueries?Only return the date and then join off of date on the outside?Neither of these seem too entising...Thanks in advance for any help.-Dave
I am using SQL Server 7.0 SP2. I've got a table x that has fields say:
id1 id2 a b c d e f
(each field name is separated by a space. id1 + id2 is the primary key combination for this table)
If there are say 100 records in this table I can write a SELECT query like:
SELECT (a + b + c + d) AS Item FROM x WHERE id1 = 'my_value1' AND id2 = 'my_value2'
and I could get up to 16 records (say) using this query. The no. of records I get is variable.
My problem is to produce a single record that contains 16 Item fields. Can anyone advise me how to do this without using a cursor? I'll be grateful for any help,
Hi Chaps!! I am in serious problem that My production sql 2000 server with winSp 4 and sqlSP 2 with slammer hotfix is not executing four part distributed query well when I combine the query with begin tran statement it enter into hang mode... else without begin tran it is fine.
request to all of u to get rid of this situation as some modules of our applicaiton is not functioning...
can sp3a installation help or going back to winSp2/3 will be helpfull. (recently we have applied winsp4 but i don't think this is concerned with it)
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says. Command(s) completed successfully. What I really need it to do is to display the results of the query. Does anyone know how to do this? declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId
hi, I have this simple sql query - it should be pretty obvious what I'm trying to achieve but this syntax isn't accepted on SQL 2005, any suggestions? SELECT Name FROM Users WHERE UsersID IN (EXEC dbo.ReturnDataByModule 'Groups',1200)
Hi I'm new to SQL and I'm stuggling with a simple query in MS-server 2005 and wondered if anyone can help me.
I'm trying to devide the ansewrs of two seperate queries, but both the queries use the same coloumn in the where clause to get the answer. Please can some one help!
I have a table like this:Name, SSNJoe Smith, 1111Tom Why, 2222Larry Sam, 3333Paul Tom, 4444Steve bob, 1111I want a query to pull offJoe Smith, 1111Steve bob, 1111because someone accidently put in two different names with the sameSSN. There should only be one 1111 in the SSN field in the wholedatabase. I want to pull the duplicate SSN with the name. How can onequery do this. I can write a VB program to do it but I think a queryshould work.I know how to do this:SELECT SSN, COUNT(*) AS cntFROM testGROUP BY fSSNHAVING (COUNT(*) > 1)to find the duplicate SSN but I need the name listed with the SSN also.Any help?? Thanks!!Sheila
First, let me apologize for how easy this probably is:DESCR TYPE SELL StartDate EndDate65048 04 Price A 4/21/2004 4/26/200465048 06 Price C 4/20/2004 4/27/200465048 08 Price B 4/22/2004 4/28/200465049 04 Price A 4/19/2004 4/24/200465049 06 Price B 4/22/2004 4/25/200465049 09 Price C 4/20/2004 4/29/200465050 07 Price A 4/21/2004 4/25/200465050 06 Price B 4/18/2004 4/28/200465050 05 Price C 4/17/2004 4/29/2004Descr, Type, Sell are CHARStartDate and EndDate are SmallDatetimeI need a simple query that would display the records with:Highest TYPE for each DESCR with:"Date I Enter" >= Startdate"Date I Enter" <= EnddateResults for ("Date I Enter" = 4/23/2004) should be:65048 08 Price B 4/22/2004 4/28/200465049 09 Price C 4/20/2004 4/29/200465050 07 Price A 4/21/2004 4/25/2004I would give you what I have done but it is such a mess I am better offstarting over.Thanks!!-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----http://www.newsfeeds.com - The #1 Newsgroup Service in the World!-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
I hope you mind a questions from an inexperienced SQL 2000 user.
I don't have much experience with building queries for SQL2000. So I am lookinf for a software tool that will help me create simple queries. I'm also looking for an online (either on the web or a downloadable program) query reference to help remine me how to make these queries.
I have two tables (tblRequest and tblDev) whose items have many-to-manyrelationships with each other. I have set things up like this(simplified):Table 1 fields: tblRequest.PrimaryKey tblRequest.Descriptionexample: 10 45 Elm11 63 Green12 123 Main Street13 23 PineTable 2 fields: tblDev.PrimaryKey tblDev.Descriptionexample: 20 Local. No issues21 City owned and main22 Out of county23 Not seen by officerThen Table 3 keeps track of the relationships between tables 1 and 2 bytracking the primary key of the linked items in tables 1 and 2.Table 3 fields: tblLink.RequestPrimaryKey tblLink.DevPrimaryKey10 2010 2111 2212 22Items from tables 1 and 2 may or may not have a relationship listed intable 3.So given an item in tblRequest:tblRequest.PrimaryKey tblRequest.Description12 123 Main StreetI need two queries...The first query would return ALL items from tblDev that are notassociated with this item in tblRequest - in this case 20, 21, 23.The second query would return ALL items from tblDev that ARE associatedwith this item in tblRequest - in this case 22.That's it. Thanks in advance
hi all,I have a table of customers.I have a table of products they have ordered.How can I find all customers who have ordered productA and productB atany time.It sounds so easy, but I can't quite get my head around it!thanksTim
SELECT LB.WBS2, LB.WBS3, LedgerAR.WBS2 AS Expr1, LedgerAR.WBS3 AS Expr2, LB.AmtBud AS amtbud, SUM(LedgerAR.Amount * - 1) AS amt FROM LB LEFT OUTER JOIN LedgerAR ON LedgerAR.WBS1 = LB.WBS1 AND LedgerAR.WBS2 = LB.WBS2 AND LedgerAR.WBS3 = LB.WBS3 WHERE (LB.WBS1 = '001-298') GROUP BY LB.WBS2, LB.WBS3, LedgerAR.WBS2, LedgerAR.WBS3, LB.AmtBud
I want to sum the amtbud column like I did for the amt column. and group everything based on WBS2. However, I keep getting an outrageous amount for the amtbud. This is what is seems to be summing up:
SQL Server 2000. Here's what I have so far. The section of the query I need help with is highlighted in blue.
CREATE PROCEDURE dbo.GetByVersion ( @targetVersion varchar(30), @product varchar(50) ) AS SET NOCOUNT ON; SELECT * FROM MyTable WHERE (product = @product) AND CASE WHEN @targetVersion='' THEN (targetVersion='') ELSE (targetVersion LIKE @targetVersion + '%') END GO
I get a syntax error in the Stored Procedure editor on an equal sign in this line: WHEN @targetVersion='' THEN (targetVersion='')
What I want is this (in psuedocode): if @targerVersion is blank search for records where the targetVersion column = blank else search for records where the targetVersion column starts with @targetVersion
Can anyone offer any suggestions as to how I might modify my query to do what I want? Any help is very much welcome - Thanks in advance! :-)
we've created a ata Flow task to execute several aggregations. Our Task access database using OLE DB source and selects data out of our staging tables (we've analyzed the query using MS SQL Management Studio which didn't showed any issues). But when we try to run our dataflow task using SSIS (debug mode and DTEXEC from command line) we experince that tasks seem to stop during processing.
Unfortunately we didn't found a way to see long logfile entries which explain the issue to us. We do use several aggregation tasks divided in 4 sequences. Unfortunately we just see one logical processor out of 4 logical processors working. It is a Windows 2003 SP2 machine with SQL 2005 SP2 on top of it.
Is there any solution to use all processors to one package for parallel execution?
So basically we experience two issues: - SSIS seems to stop somewhere in thre middle - SSIS just uses one processor instaed of all four
Unbelieveible! I can not seem to find any source for questions database related that don't include using 2.0 controls just as the come out of the box! HELP!!!! Simple issue (or so it seems) but I can't get an anwer: Example: TextBox.Text has information that I want placed in a table in a sql database. So I build a SQLDataSource control, and the triggers to fire the insert method. I modify the InsertCommand of the SQLDataSource so that it will write my record to the table. Something like... InsertCommand="INSERT INTO MyTable(Description, Status) VALUES ('TextBox.Text', 'Default New Record Status)" Now... the problem is with 'TextBox.Text' . When formated as is. the text string TextBox.Text is inserted into a record in the target table. My problem, the simple one remember, is refering to the text string stored inside of TextBox.Text and have that value stored in the new row under the Description column. I can't believe that no one can help with this simple problem. Once resolved then my page is done... this is ALL that holds it up and I can't find a reference to how to go about it. Thanks,Ken....
Hi, I'm working on both VWDEE and VBEE and, in each, when I try to add a database diagram I get the following error message 'Invalid Class String' (after the 'Do you want to create required objects' prompt). Can someone postulate a reason? If I don't get an answer i'll be pretty much dead in the water Thanks in advance of your help.
"Falling back on cursors because they remind you of datasets will result in poor performance "
Hi, The above quote was from an article which advice to use store procedure and avoid T-sql in the body of a webpage. Anyway, To me, I always use cursor when I want to process one row at a time. Is there an alternative to cursor in MS sql server to have similar results.?
Please suggest correct answer (A,B,C,D) for the following Question
You are developing an application for a worldwide furniture wholesaler. You need to create an inventory table on each of the databases located in New York, Chicago, Paris, London, San Francisco, and Tokyo. In order to accommodate a distributed environment, you must ensure that each row entered into the inventory table is unique across all location. How can you create the inventory table?
A.Use the identity function. At first location use IDENTITY(1,4), at second location use IDENTITY(2,4), and so on. B.Use the identity function. At first location use IDENTITY(1,1), at second location use IDENTITY(100000,1), and so on. C.CREATE TABLE inventory ( Id Uniqueidentifier NOT NULL DEFAULT NEWID(), ItemName Varchar(100) NOT NULL, ItemDescription Varchar(255) NULL, Quantity Int NOT NULL, EntryDate Datetime NOT NULL). D.Use TIMESTAMP data type.
Give the suitable answer for the below question and explain the answer.
1.You have designed the database for a Web site (or online ticketing agency) that is used to purchase concert tickets. During a ticket purchase, a buyer view a list of available tickets, decides whether to buy the tickets, and then attempts to purchase the tickets. This list of available tickets is retrieved in a cursor. For popular concerts, thousands of buyers might attempt to purchase tickets at the same time. Because of the potentially high number of buyers at any one time, you must allow the highest possible level of concurrent access to the data. How should you design the cursor?
(A). Create a cursor within an explicit transaction, and set the transaction isolation level to REPEATABLE READ. (B). Create a cursor that uses optimistic concurrency and positioned updates. In the cursor, place the positioned UPDATE statements within an explicit transaction. (C). Create a cursor that uses optimistic concurrency. In the cursor, use UPDATE statements that specify the key value of the row to be updated in the WHERE clause, and place the UPDATE statements within an implicit transaction. (D). Create a cursor that uses positioned updates. Include the SCROLL_LOCKS argument in the cursor definition to enforce pessimistic concurrency. In the cursor, place the positioned UPDATE statements within an implicit transaction.
What are his steps in the execution of a query? Where/how does he storage the information while executing a query? What type of Index he uses? and so on ...
If anyone knows a website that contains this type of information about the SQL Server SGBD and could share it i would be most appreciated!
1)Which statement shows the maximum salary paid in each job category of each department?_______ A. select dept_id, job_cat,max(salary) from employees where salary > max(salary); B. select dept_id, job_cat,max(salary) from employees group by dept_id,job_cat; C. select dept_id, job_cat,max(salary) from employees; D. select dept_id, job_cat,max(salary) from employees group by dept_id; E. select dept_id, job_cat,max(salary) from employees group by dept_id,job_cat,salary;
2)description of the students table: sid_id number start_date date end_date date which two function are valid on the start_date column?_________¡£ A. sum(start_date) B. avg(start_date) C. count(start_date) D. avg(start_date,end_date) E. min(start_date) F. maximum(start_date)
I'm trying to write a "GetQuote" procedure with a single @Quantity parameter. The purpose of the query is to sum the total cost (price * quantity) of a given (quantity) given the available "Sale" items. The sale table has the following structure:
[SellOrderID] [uniqueidentifier] DEFAULT (newid()), [OrderDate] [datetime] DEFAULT (getdate()), [UserID] [nvarchar](50) , [SellPrice] [float] , [SellQuantity] [int] , [QuantRemaining] [nchar](10) NULL, **QuantRemaining is the sell quantity remaining after existing partial trades on the Sell Order - intentionally denormalised.
A given quantity may be satisfied by multiple 'Sell Orders' and these should be processed from the lowest price first.
e.g. give the following: SellOrderID QuantRemaining Price A 100 $2.00 B 300 $3.00 C 200 $1.75 D 350 $2.50
GetQuote 400 should return: Sum((200*$1.75)+(100*$2.00)+(100*$2.50))
I am relatively new to the DB game and am having problems creating a SQL statement to do this. If anyone has a solution for this it would be greatly appreciated.
