Hello everyone,
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.
Thank you,
mike
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!
eg.
select Sum(column1)
where column1 = 'x'
devided by
select Sum(column1)
where column1 = 'y'
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
View 3 Replies View RelatedFirst, 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! =-----
View 3 Replies View RelatedI 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
View 1 Replies View Relatedhi 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
View 7 Replies View RelatedHi,
Could someone confirm that the following query:
update table set x=1, y=1, z=1 where a = 1 or b = 1 or c = 1 or d = 1
is more efficient than this query:
update table set x=1, y=1, z=1 where e <> 1 and f <> 1 and g <> 1
Thanks!
Hi,
I have this query:
Code:
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
it produces the following output:
Code:
WBS2WBS3Expr1Expr2amtbudamt
014101014101300095
1217010121701080007290
12170804000
121709012170903200065960
121711012171101800034450
121712012171204400038010
12171402000
1217170121717013500935
12171804500
1217220500
12172601000
12175001217500800622.5
12221604000
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:
Code:
01410101410130000
014101014101300047.5
12170901217090320000
12170901217090320000
12170901217090320000
12170901217090320000
12170901217090320000
12170901217090320000
12170901217090320000
12170901217090320000
12170901217090320000
etc....
Any help will be appreciated I am just stumped.
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.
strSQL = strSQL & "ORDER.OS_T_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_T_Sort, "
strSQL = strSQL & "ORDER.OS_W_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_W_Sort, "
strSQL = strSQL & "ORDER.OS_L_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_L_Sort, "
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.
strSQL = strSQL & "ORDER.OS_T_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_T_Sort, "
strSQL = strSQL & "ORDER.OS_W_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_W_Sort, "
strSQL = strSQL & "ORDER.OS_L_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_L_Sort, "
How do I rewrite this query so that I do not use a temp table?
SELECT SITEID, MIN(R1PROGRAM) AS MINR1, MAX(R1PROGRAM) AS MAXR1
INTO #TEMP1
FROM SITECONTROLDATA
WHERE CALC_DATE > GETDATE() - 12
GROUP BY SITEID
SELECT * FROM #TEMP1 WHERE MINR1 = MAXR1
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! :-)
I am looking for a simple example or guidance on how to move the results of a query from a mysql database to sql server 2005 using SSIS.
It seems rather difficult to do this or at least I am stuggling to figure it out. It was easy to do in DTS, how do you do it in SSIS?
Hi,
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
your advice is appreciated
Good day,
I would like to know if there are any good MDX query builder tools out there. The one that is shipped with AS does not suffice in that it does not support more than 2 axes. Also if there are any are there any that make life a little easier in that they are drag and drop and all but create the statement for you?
I need to create a matrix in SSRS using cubes and in doing so need to write MDX queries, not my strong point. Are there any tools that can help me "auto create" the statement with drag and drop functionality?
Thanks in advance
Hi people!I'm looking for an SQL query builder application that helps me constructSELECT queries with full respect of underlaying relational model. It wouldbe nice if the app is database independant because my work covers MSSQL,mySQL and Oracle DB.OpenSource and/or comercial suggestions are welcome!*** thanx ***
View 1 Replies View RelatedHI, in a lookup component, when I want to use the query builder, it will show tables from a different connection manager than the one I picked for being the lookup source. E.g. connection manager A points to a schema that contains table1 and table2 and connection manager B points to a schema that contains table 5 and table 6.
When I configure the lookup component to use connection manager B, I still see connection manager A tablles; isntead of seeing table 5 and 6 I see table 1 and 2.
Am I missing something?
Thank you for your help,
Cctoe
hi,i can do"select * from products where name = @name" kind of statements in query builder but"select * from products where name LIKE @name" dosen't work!" any ideas? i'm using sql server express. thanks
View 3 Replies View RelatedHi All,
For one of our projects, we are supposed to write a query builder to display reports in tabular format. For the filter criteria ( i.e where clause ) user can select from a list of predefined columns and assign conditions to it. For example, user can select 'CustomerName' as the column and specify condition where name begines with 'A'. Similarly he can also select ouput columns
Problem
The columns that user selects can come from different tables. We are planning to store mapping between tables and columns and also their primary and foreign keys in an xml file. But I would want to understand how to build the join clause.
For example, say there are 4 tables customer, customer address, order, and items. Say user wants the number of items purchased by each customer who stays in 'Delhi'.
1. In output column he selects customer.name and count(items.id)
2. In filter criteria he selects address.city = 'delhi'
How would the query builder know that to join customer and items, order also has to be joined. Any pointers would be helpful.
I am trying to emulate the "Find unmatched Records" Access Query using DTS. I have a small table with a list of codes that I need to use to exclude records in my text export.
I have created a left outer join and set the criteria for the joined field in the 'right' table to be null. I am repeatedly getting no records as a result.
Wouldn't the joined field in the right table for unmatched records by default be null? I'm stumped as to why this seamingly simple process won't work for me. I have attached the select statement for your review.
Any help would be greatly appreciated.
Can we view the query that is fired on the Report builder to debug as i am finding some error in data...
View 3 Replies View RelatedOn pages 123, 124 of Data Mining with SQL Server 2005, I can't finish the example. When I add 'Monthly' to the end of the expression "[Home Owners].[Theater Freq]" in the Criteria/Argument column, I usually get an error message saying the syntax is incorrect when I switch to the Results view. If I place a dot after the original expression, like this: [Home Owners].[Theater Freq].Monthly, I get an error message that "The dot expression is not allowed in the context at line 4, column 23. Use sub-SELECT instead." What am I doing wrong?
Thanks!
Either edit the query from Dataset edit dialog or in the query builder (switch the design mode to show the query string), I can't save the query and always get the following error:
TITLE: Microsoft Visual Studio
------------------------------
The query cannot be retrieved from the query builder.
Check the query for syntax errors.
Reporting Services will continue to use the most recent valid query.
------------------------------
ADDITIONAL INFORMATION:
Query preparation failed. (Microsoft.AnalysisServices.Controls)
------------------------------
I am sure there isn't syntax error in the query string because I only add a space after a comma.
NOTE: I am using Brian Larsons book Delivering Business Intelligence with SQL Server 2005. I am stuck on page 588 - step 20. I am using Windows Vista Ultimate
Once all the Dimensions & attributes I have selected are dragged into the query area in MDX query builder no data is displayed. There is a message: "No rows found. Click to execute the query." - clicking does nothing.
How can I resolve this problem ???
We have SQL Server 6.5 with an Access 8.0 front-end. We are about to re-write our front-end in VB6 using RDO. Obviously Access has a nice fast and easy to use query builder. My question therefore is - is there any such query builder that can be used within VB6 to simplify query writing ? Or do I have to write all query's and and stored procedures using straight SQL and/or stored procedures. I do not want to use Visdata as it doesn't appear up to the task.
Thankyou whoever helps
Paul
Hi, I am trying to generate a report and I am using the query builder feature in SQL Server Reporting Services.
I just entered a simple query (actually the query builder generated it for me):
SELECT ID, pname
FROM jira393.project
and it seems to run fine but when I click "OK" to go to the next step, I keep getting an invalid object name error: 'jira393.project'
I don't know what is wrong right now since when I run it, it seems to display the correct information but trying to go to the next step displays that invalid object error.
Does anyone have any idea what is wrong?
Hi,
I have created a model from the underlying database. Now I want the capability to write SQL Queries when creating reports instead of drag and drop the reports. Is there anyway we can achieve it in MS Report Builder. I would like to know the options available in MS world on this.
Thanks,
S Suresh
Is their a way when creating a Report Builder model to set a query timeout? Similar to what can be done when creating a Reporting DataSet in VS.NET during report development.
My ultimate goal is to be able to limit ad-hoc report users running Report Builder from waisting resources on my SQL instance. This has nothing to do with a report being published to a report server and setting a reports execution time limit.
In my reports I want Date attribute to be added only in the data set and not in the query builder, As if I am adding it in the query builder report is taking very long time to generate and I am getting around 7000 pages of data which is absurd.So, if I can add it only in the data set then I can use filters for my reports and get only the required data. So, is there any way where in which I can add the Date attribute only in the dataset and not in the query builder????
View 1 Replies View RelatedHello,
I have a question about using the query builder, or just straight sql, to create expressions. My first example caused the query builder to generate a query configuration error:
Expression: EIM_REQUISITION_DETAILS.EST_UNIT_COST * EIM_REQUISITION_DETAILS.REQRD_QTY AS COST
Syntax error: Expecting "," or "FROM" after column alias or "*"
Initially I thought I was dead in the water but then I decided to just run it anyway and it worked! I got my cost. OK so fine the query tool doesn't recognize some sql expressions. Because I am trying to make a large flat file I thought I'd concatenate 2 fields to generate a recognizable requisition, which everyone knows is the year followed by a hyphen then the req number:
My old Access query had this: Req: Right([REQ_YR],2) & "-" & [REQ_NO]
and I tried to change it to this: substring (EIM_REQUISITIONS.REQ_YR from 2 for 2) || '-' || EIM_REQUISITIONS.REQ_NO AS REQ
So apparently my older sql book doesn't have the syntax right. Since I plan to do more of this could someone point me to a reliable book or site that could help me with sql syntax?
Thanks,
Dana
Hi
i have a database with several tables and i want to create a Insert query. and i do this by adding the tables to a dataset and create the relations between them and now i click on the table i want to create a query for and select Add Query. and then the TableAdapter Query configuration Wizard pops-up. but the problem is that in the query builder i can add ONLY ONE table, why? i need to add more than one table in order to make my query.
This is how this problem accured: At first i had to two databases, the first database i created myself and there i had all my tables and the second database was the one that contained all the membership info(this database is the one that visual studio creates automaticly for you when u are using the membership thing ASPNETDB.MDF). so i decided to only have one database and i re-created all my tables in the aspnetdb.mfd database, that is where this problem accured, that i can add only one table in the query builder.
appreciate any help :) tnx