I have a query with 2 subqueries, and no error message is reported, but, my problem is that the 2 subqueries do not follow the GROUP BY rule and show the total instead of by vendor...
Code:
SELECT Table1.agents AS Vendor
, Count(Table1.carS) AS Car_Sold
, Sum(Table1.carP) AS Car_Price
, Count(Table1.busS) AS MortBus_Sold
, Sum(Table1.busP) AS busPRice
[Code] ....
Hi allI had some info on setting db owners and chaining so I can execute a dtsrun(many thanks to Dan for that)I have taken the comments on board and done some more research on theinternet, but I'm still struggling.I am testing this on my personal edition, but when I runEXEC sp_dboption 'my dbname', 'db chaining', trueit tells me that the SP doesn't accept the option db chaining.I ran select @@version and I have SP3 on where I believe this option wasimplentedcan anyone give me any further pointers.many thanks for any helpAndy
View 2 Replies View RelatedI deleted some records out of an entity, I'd like to keep the Codes as contiguous and incremental, meaning no breaks between the code numbers.I created a business rule and applied it but codes remain the same.
I used the "Default to a generated Value" action, then selected the Code attrib. --Saved.
Then back to the Entity, I applied business rules. But nothing seemed to have happened. As there was no change in codes.
There are no validation errors either.
I have a query that maps all products to some customer levels. In this case levels 0,5,7 and 8
DELETE FROM ProductCustomerLevel
WHERE CustomerLevelID IN (0, 5, 7, 8)
INSERT ProductCustomerLevel
(
ProductID,
CustomerLevelID
[Code] ....
Basically this maps all products in a database to these customer levels so that they get a discount.
I know need to create a new customer level, example number 9. These will only have 1 or 2 products applied to it.
How can I change the SQL above so that it does not map those products already in Customer Level 9 to levels 0,5,7 and 8
I have the following SELECT query, the results of which I would deletefrom the table they're pulled from:SELECT A.* FROM SalesOrderPartPrices AWHERE EXISTS(SELECT 'Exists' FROM SalesOrderPartPrices BWHERE(A.SalesOrderNo = B.SalesOrderNo) AND(A.PartNo = B.PartNo) AND(A.UnitPrice = B.UnitPrice) AND(A.RowID > B.RowID))However, when I try to run the following query, I get an error ("Msg170, Level 15, State 1, Line 1: Incorrect syntax near 'A'."):DELETE FROM SalesOrderPartPrices AWHERE EXISTS(SELECT 'Exists' FROM SalesOrderPartPrices BWHERE(A.SalesOrderNo = B.SalesOrderNo) AND(A.PartNo = B.PartNo) AND(A.UnitPrice = B.UnitPrice) AND(A.RowID > B.RowID))What am I doing wrong, and how do I resolve the issue? Any help isgreatly appreciated....... thanks in advance!-=Tek Boy=-
View 6 Replies View RelatedHi.
I'm investigating whether I can lock down a feature exclusively to only those who belong to a certain domain group.
From what I understand, the master.sys.login_token view holds information on all the groups I'm a part of. However, I'm looking at the output from
SELECT * FROM master.sys.login_token
and see a number of groups that I can't verify I have access to. I have even run the DOS command "net group <domaingroup> /dom" and don't see myself listed in its output.
Is this normal? Is the output a culmination of everyone logged in at the time?
@@version = 9.00.2047.00
Hello,
I'm new to SQL and need help with a query. Not sure if this is the right place.
I have 2 tables, one MASTER and one DETAIL.
The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.
I want to return a populated MASTER table with entries based on the DETAIL.value.
SELECT MASTER.*
FROM MASTER
WHERE DETAIL.value > 3
This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.
Hope this makes sense.
How can I do this?
GrkEngineer
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
How to use a dsn-less connection from DTS?Source: MySQLDestination: SQL Server 7.0I have the ADODB.connection code but unsure where and how to use it.Pls advise.TIA!Bob
View 1 Replies View RelatedWe have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
[code]....
1. Let's say I have a SqlDataSource named Fred. Say I have a string variable, call it Barney. If I call Fred.Insert(Barney) HOW Does the insert command GET the Barney value and pass it to the SQL Insers string? 2. Lets say also in this same insert, I want the user Lets say Batty, who is logged in, to have her username automatically inserted into the table along with her Barney value. What is the method to get a hold of user name that is magically floating around in the session somewhere? All said and done, I'd love for it to work like this: "INSERT INTO [BedRock] ([UserName], [Character] ) VALUES (SomethingThatResolvesToSignedInUserName, SomehtingThatResolvesToStringValueofVariable) Table BedrockUserName CharacterBettyRubble 'Bam-Bam' THANKS inadvance fro ANY HelpDan-Simple and sleepless-Harper
View 3 Replies View RelatedFollow up for previouse message
I had only 40 mb of disk space in the c drive. could that be the reason. if so, how can I turn on the server and get into win NT and make the server up and running.
I hope someone can reply as soon as possible
Ahmed
Hi all,
I have some doubt about :
What is the basic difference between Patches,SP,Hot fixes in SQL Server ?
and
What are the steps we have to Follow before applying Service Pack ,Patches,Hot fixes?
Thanks
Hi All,
I am facing a problem in my personal edition of sql server 2000. my this edition is hung after some time due to error "the schedular 0 appear to be hung in sql server 2000" and next error is "the schedular 1 appear to be hung in sql server 2000" i found the solution of this error i run sp4, after that this error wont appear to me in the error log , but the other problem occur which is "your server is configure for 8 concurrent process but currently 166 process running so the performance would be degrade" and after that i am not able to log into the server. Due to this concurrent process problem i want to go for sql server 2000 Enterprise edition. so what stragery should i follow for upgradation of server. any other alternative of this problem
Regards
Shashi Kant chauhan
I have a table:
CREATE TABLE [dbo].[tx1]( [f1] [nvarchar](50) , [seq] [int] IDENTITY(1000,1) NOT NULL ) ON [PRIMARY]
SELECT *
FROM dbo.tx1
ORDER BY seq DESC
go
f1 seq
zz 1003
uu 1002
kk 1001
yy 1000
create view vx1 as
SELECT top 100 percent *
FROM dbo.tx1
ORDER BY seq DESC
go
select * from vx1
yy 1000
kk 1001
uu 1002
zz 1003
We have an NT 4.0 sp4 server with Sql 6.5, sp4 and its server name needs to be changed. This server will also be upgraded to Sql 7.0 sp1 too.
Is changing the server name easier/more efficient etc in 65 or 70?
Does anyone have any recommendations and or tips to follow in avoiding pitfalls, headaches etc?
Additional info: Replication is not involved here. If we upgrade to 70 first, it will be a "One-computer upgrade" process followed. BOL for 70 doesn't mention anything about a Server Name change. BOL for 65 does have "How to change to the current server name in the 6.x master database..." I was hoping to find specific details on the 'how to...' but was surprised that 70 doesn't cover this.
I have also seen a Jan 15 '99 posting by Sharon Dooley under "Machine Rename" which raises concerns that MS missed the boat on Server Name changes.
Any help would be appreciated.
Thank you,
TW
I need to know the steps on how to configure the SSRS Forms Authentication and its IIS Configuration. What specific Config Files should be modified?
i am using IIS 6.0, Windows Server 2003, SQL Server 2005.
Please help....
Is there a way to access your views in x database from the master database?
I'm getting this error
Invalid object name 'v_StatisticsScalars'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'v_StatisticsScalars'.
I'd like to create a master-detail output from SQL2005 to display in a page using classic ASP. Ideally, I want the output to contain a <div> to show/hide the order details beneathe the order header, as shown below.
OrderNo OrderDate
1001 7/27/2007
+ <div to show/hide>
Item Description
AAA desc_for_itemAAA
BBB desc_for_itemBBB
+ </div>
1002 7/26/2007
+ <div to show/hide>
Item Description
CCC desc_for_itemCCC
+ </div>
I currently just have a stored procedure returning the results and it is very slow as I'm simply querying the details section for each order header. Does anyone have any ideas on how I can create an efficient and fast method for returning these results in the format above?
Hi
I came across something like 3-4-5 rule while going through datamining book....but couldn't get from where that rule has been generalized and how it really works....
can anyone explain this rule ?
Thank you
I need to write a sql query which is a master-detail query. Here's the example structure of tables:
Master table:
ColID as longint, ColA as int, ColB as int, ColPartID as longint, ColPartName as longint
Child table -- Wheel:
ColID as longint, ColA as int, ColB as int
Child table -- Door:
ColID as longint, ColA as int, ColB as int
Child table -- Window:
ColID as longint, ColA as int, ColB as int
..... etc
From the master table, it needs to join with its child in order to get the detailed information. However, there're more than one child table for it to join. In other words, the query has to choose the correct child table to join for each row selectively. The use of correct child depends on one of the columns in its master table (ColPartName).
My question is: Does it worth of me keep finding a solution for this query or should I abandon this? I really need some advice, please.
Many thanks,
Leonard
Hi,
I have a program in VB.NET 2003 where I have query "SELECT COUNT(*) FROM master..sysservers WHERE srvname = '" & strServerName & "'". This program copies data from .MDF file to another .MDF file. This query is written since I have created linked server.
Issue 1:
But now I to copy data from .SDF (SQL Server Mobile Edition) to desktop .MDF file. For this I cannot fire the above query on .SDF file. What is the other alternative?
Issue 2:
In the existing prgram that copies data from .MDF to .MDF, I have created linked server. Can I do the same with mobile edition?
Regards,
MI
Hi,
How do I extract rules and it's value from a database?
I can extract the rules through view(sys.objects) but where can I get it's content?
Regards
Marcelo Gamba
How can I setup the dbs in sql server so that when I change the data in one table the changes will cascade down to the tables in my other dbs. Therefore, one database would hold a primary key table. If I had 15 other dbs, then I could somehow link them so the data changed in the primary key table of the 1st database would cascade down to the other dbs.
Thanks
Hi,
I am having problem in getting result out of two table, one table is Item Mater which stores global items for all offices and other is stock file which stores office wise stock items as follows:
ITEM MASTER
--------------
NCODE ITEMNAME
1 A
2 B
3 C
4 D
5 E
STOCKDETAILS
-----------------------------------
NCODE ITEMCODE OFFICEID
1 1 1
2 2 1
3 3 1
4 1 2
5 2 2
6 4 2
7 5 3
I want office wise stock details which inludes items found in stock file and remaining itmes from item master. example for office 1
--------------------------------------------
FOR OFFICE - 1
--------------------------------------------
ITEMCODE ITEMNAME OFFICEID
--------------------------------------------
1 A 1
2 B 1
3 C 1
4 D NULL
5 E NULL
i want a single view from which i can select data like i shown above, any kind of help is highly appriciated, what i tried is , i created union of both tables and tried to get data out of union view but result is not up to desire.
Thanks in advance
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
I wonder about whether this rule is valid or invalid for nested BEGIN/END statement...
Code:
BEGIN
BEGIN
--Query #1 (blah)...
END
WHILE EXISTS (SELECT TOP 1 * FROM #tmpTblPurchaseRaw)
BEGIN
BEGIN
--Query #2 (blah)...
END
BEGIN
--Query #3 (blah)...
END
END
END
I have no idea if nested BEGIN/END is allowed or not...
I am getting some data from another database and only want to copy sensible data...
How do I write a validation rule in SQL (SQL SERVER 2000) for a fax number, so that it only contains numbers-- i.e digits 0-9
Thanks in advance
Trying to find which Employees have NOT entered a record (timecard).
Table #1 Employee_Hours
Table #2 Employee (List active)
Table #1
SELECT EID, DATEPART(wk,[Date]) AS Week, MAX([Date]) AS WeekEnding
FROM Employee_Hours
WHERE ([Date] Between '1/1/04' AND '12/31/04')
GROUP BY EID, DATEPART(wk,[Date])
HAVING MAX([Date]) = '1/24/04' --Pay period
Table #2
SELECT EID, DisplayName
FROM Employee
WHERE (Active=1)
I tried subqueries and temp tables but can't get the results I need. The first query will return the employees who entered their time for the given pay period.
Thanks
Hi,
I have the following sql statement:
INSERT INTO prg_lvl VALUES (
'Data Recovery',
'Data Recovery',
7,
'ENG',
0,
16,
'Menu=w_data_recovery_stk;',
( select max(menu_id) + 1 from prg_lvl ),
'K');
that works for Sybase and I wanna make it works for SQL Server.
The problem is the use ot the subquery (select max(menu_id) + 1 from prg_lvl).
Could I use subqueries in SQL Server in general or is there is any other method to overcome this problem
Thanks,
fady
Hi,
I would like to know
What is Corelated SubQueries??
Thanks in Advance
I am very new to SQL Server, working with it in class. I am trying to do procedures involving subqueries using the Northwind database, and am having a rough time.
For instance, I have the following code, which lists all orders for the month of November, 1996:
SELECT Customers.CustomerID, Customers.CompanyName, CAST(Orders.OrderDate AS CHAR (11) )AS OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
AND OrderDate BETWEEN '11/1/1996' AND '11/30/1996' ;
I have been asked to do the same thing, but using a subquery. I came up with the following:
SELECT CustomerID, CompanyName FROM Customers a
WHERE EXISTS (SELECT OrderDate FROM Orders b WHERE a.CustomerID = b.CustomerID
AND OrderDate BETWEEN '11/1/1996' AND '11/30/1996' ) ;
However, it does not list the Order Date, as I want it to, and the information is not correct (it is missing a few orders from November '97 that should show up. Can someone tell me what I am doing incorrectly?
Please note that I do not need anyone to give me the code, but simply give me an idea of what I am doing incorrectly, or let me know how it can be done, so that I may do it. =-)
Hi all,I've seen mention that you can use nested subqueries down to as manylevels as you like but whenever I run the following:select * from table1where tab1ID in(select tab1ID from table2 where tab2ID in(select tab2ID from Table3 where Tab3ID=N))I get the error "incorrect syntax near the keyword 'where'"Can anyone confirm that sub-subqueries are possible? If so is thesyntax I'm using correct?I'm running SQL 2000 through a Delphi 5 app.RegardsJon
View 2 Replies View Related