I faced a problem when using Northwind sample Database. On of the table in the database is 'Order Details'. I am unable to query on the table because of the space in the name ('Order'<space> 'Details') of the table. When I remove the space in the name, the reports associated in Cognos are not working properly. Let me know whether there is any purpose to put the name like that and if I want to see the data in query mode how it is possible.
I have came across a table in SQL server 2000 which named 'Order Details' in the sample 'Northwind' database which is available with the product. Am using the eval version . Generally no table name exist with a 'space' between the words. But the table 'Order Details' exist in the Northwind sample database.
Due to the naming convention i can't run sql queries on that. Is anyone aware of this type of issues.
SELECT DISTINCT TblOrder.CustomerUID, TblOrder.OrderHiddenID, TblPayment.PaymentAmount, TblPayment.Result, TblOrder.OrderID FROM TblOrder CROSS JOIN TblPayment WHERE (TblOrder.CustomerUID = @IsCustomerID) AND (TblOrder.OrderHiddenID = @IsHiddenID) AND (TblPayment.Result = 'Pending') AND (TblOrder.OrderID IN (SELECT MAX(TblOrder2.OrderID) FROM TblOrder TblOrder2))
one customer can have more than one orders. So i need to select customer last inserted order details from database.So when i use above sql i returns null.what might be the reason for this
I have a requirement to present computed sales order status value
Parent table : consumer Child table of consumer : sales order Child table of sales order: items
Items table has status column with awaiting, accepted , rejected.
Status of the sales order should be set as following If all items are rejected then sales order status=reject Or if at least one item is accepted then sales order status = accept Or if items status is awaiting then sales order status = pend
Query to find all sales order per consumer with computed order status for each sales order.
I'm trying to get some XML data into SQL Server but i ran into problem when inserting the data (multiple orders with multiple order details) using a single sproc. Is it possible, or do I have to do in some other way? :confused:
I simplified my example to this: ----------------------------- --CREATE PROCEDURE sp_InsertOrders AS
DECLARE @docHandle INT, @xmlDoc VARCHAR(4000), @orderID INT
--DROP TABLE #Orders CREATE TABLE #Orders ( OrderId SMALLINT IDENTITY(1,1), FkCustomerID SMALLINT NOT NULL, OrderDate DATETIME NOT NULL )
--DROP TABLE #OrderDetails CREATE TABLE #OrderDetails ( OrderDetailsId SMALLINT IDENTITY(1,1), FkOrderID SMALLINT NOT NULL, ProductID SMALLINT NOT NULL, UnitPrice SMALLINT NOT NULL )
INSERT INTO #Orders (FkCustomerID, OrderDate) SELECT CustomerID, OrderDate FROM OpenXML(@docHandle, 'Orders/Order', 3) WITH ( CustomerID INTEGER, OrderDate DATETIME )
SET @OrderID = @@IDENTITY;
--INSERT INTO #OrderDetails (@OrderID, ProductID, UnitPrice) SELECT @OrderID AS OrderID, ProductID, UnitPrice FROM OpenXML(@docHandle, 'Orders/Order/OrderDetails', 3) WITH ( ProductID INTEGER, UnitPrice INTEGER ) -----------------------------
All orders are inserted first which makes the use of @@IDENTITY incorrect (it works fine if you insert a single order with multiple order details). Since it was quite some time since I last worked with SQL I am not sure if am doing it the right way... :confused: :confused: Anybody out there who knows how to solve the problem?
I created am inventory table with few columns say, Servername, version, patching details, etc
I want a tracking of the table.
Let's say people are asked to modify the base table and I want a complete capture of the details modified and the session of the user ( ) who (system_user) is actually modifying the details.
I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
From the http://msdn.microsoft.com/en-us/library/bb384469.aspx (Walkthrough: Creating Stored Procedures for the Northwind Customers Table, I copied the following sql code:
--UpdateSPforNWcustomersTable.sql--
USE NORTHWIND
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.[SelectCustomers]
GO
CREATE PROCEDURE dbo.[SelectCustomers]
AS
SET NOCOUNT ON;
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertCustomers' AND user_name(uid) = 'dbo')
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
==================================================================================== I executed the above code in my SQL Server Management Studio Express (SSMSE) and I got the following error messages:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'NORTHWIND'. No entry found with that name.
Make sure that the name is entered correctly.
=============================================================================================================== I know I recreated the NORTHWIND Database from a different Database before and I did not do anything for the entry in sysdatabases. How can I change the entry in sysdatabases for database 'NORTHWIND' now? Please help and advise.
I need to combine the details of two tables and narrow the data included.
in my tutor table i have
tutorLastName, tutorSessions and tutorSkills
in my term table i have
week, date, session_Name, tutorName
i need to combine the tales to show:
(from the term table) week, date,session_Name, tutorName, (from the tutor table) tutorSkills.
i then need to narrow the table down so it shows only tutors with guitar skills and the term dats when the session_name is empty
i can run the two searches sepereately but cannot combine them i am tryin to use Where date = "whateverdate" AND session_name is null AND tutorSkills = guitar
I had noticed that certain expressions won't work in details. Is this true ? when I tried using an IIF expression, I always get a blank or an empty field even if it was a text. could someone clarify this ? I don't know whether it is just me or is it a limitation.
I want to make a report with a table who gives some figures of a family and elements of her subfamily. For example, for the Fruit family I have like subfamily the apple, the pear, the banana. I tried to make a table with a group but to aggregate some figures of the subfamily dataset, calculations are complicated.
So I make two report: - one for a dataset for the subfamily with a parameter (the family). - one for the dataset of the family.
In the second, I want to put the under-report (the first) in the details of the table while passing in parameter the family.
But, I have an error when I want to see the result:" Error: Impossible to post the under-report."
Can anyone tell me is it possible to put static data (a string) inside details section of a table? I've tried to find some more pieces of information in the Report Definition Language Specification, but to no avail.
When I put some strings as a detail's cells values they are displayed in the preview window in VS. But thay are invisible when I open my report using ReportViewer in my app. So does it means that the only accepted value of a cell within the details section of a table is a name of the field from DataSet (something like =Fields!FieldName.Value) ?
I have created a linked server using my local SQL2005. The linked server can be seen as a linked server, but the database can not been expanded to see the tables, stored procedures, views, and other details regarding the linked server. The only method that I have found that will allow me to see all the details is to use XP's Remote Desktop Connection to access the database. Has anyone else experienced this problem, if so, how did you resolve it?
We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.
I building some reports with SSRS, and showing data in a table. The table has one Details row that forms at the end multiple rows with the tree view (+ and -).
Is there a way to open the tree view upwards and not downwards so that the children of level 2, 3 etc come over the parent row?
I've goggled it everywhere, but I don't seem to find the answer. If anyone could help me out, I would appreciated.
I am trying to create a matrix report to work similar to a pivot table, where when a user clicks on the count, the details are displayed. When I add the action to open a sub-report, how do I pass in the parameter values for the group that was selected? meaning if in row group- I have company name, and column group I have job title, when the count gets clicked, i need to pass in the company name for the row that was clicked along with the column group. How can I proceed?
I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?
this query returns the values i want in the table.
select a.quantity * b.price from tblCustomerPurchases as a join tblProduct as b on a.ID=b.ID
SET @RowCnt = 1 SET @date = CONVERT(CHAR(10),GETDATE(),110) SET @ArchPath = '\D$EDATAWorkFoldersSendSendData' SELECT @TotalRows = count(*) FROM table1 --select @ArchPath
WHILE (@RowCnt <= @TotalRows) BEGIN SELECT @AccountNumber = AccountNumber, @output_filename FROM table1 WHERE Identity_Number = @RowCnt --PRINT @AccountNumber --test SELECT @sql = N'bcp "SELECT h.HeaderText, d.RECORD FROM table2 d INNER JOIN table3 h ON d.HeaderID = h.HeaderID WHERE d.ccountNumber = ''' + @AccountNumber+'''" queryout "'+@ArchPath+ @output_filename + '.txt" -T -c' --PRINT @sql EXEC master..xp_cmdshell @sql SELECT @RowCnt = @RowCnt + 1 END
I noticed that some queries against an mssql db require the tables in the FROM part of the statement to be in a particular order. Does anyone know why?
For example SELECT * FROM table1, table2, table3 WHERE <blah>
May throw an error (Unknown table table3 [I can't remember the exact verbiage of the error]), while simply rearranging the table order to:
SELECT * FROM table3, table1, table2 WHERE <blah> will work.
It seems like the error has something to do with how mssql handles the joins of the tables, but I can't seem to find any documentation about it.
merchant contractbr date a 2333 1/1/2005 a A34 3/12/2006 a R78 2/1/2005 .. b b b c c c .....
different merchant has different number of contracts. I want to order the contracts for merchants according to the date. the result table should look like:
merchant contractbr date order a 2333 1/1/2005 1 a R78 2/1/2005 2 a A34 3/12/2006 3 .. b 1 b 2 b 3 c 1 c 2 c 3 c 4 c 5 .....
Greetings,I have an application that need to get all the userdefined child tables first before their parents.I wrote a query, given in this newsgroup only, as belowSELECT o.nameFROM sysobjects oWHERE o.type='U'ORDER BY case WHEN exists ( SELECT *FROM sysforeignkeys fWHERE o.id = f.fkeyid )THEN 1ELSE 0end, o.namegoWhen i try to truncate the first table of the list, it still tells methat tha table is being referenced by foreign key in another table. Mymain job is to truncate all the user defined tables before loading datainto them.Is there something wrong in the query? Or if someone can tell me abetter approach.Any help will be appreciated.TIA
When I say to sort on a datetime field on descending order, the date is sorted. However, the time difference is not reflected in the results. Any way, we can fix it. i.e. If I have two records with the same dates but different times, the sorting order is not considering the time.
I've set up a transactional replication, but I need to specify in which order the tables are to be replicated (tables with foreign keys last). How/where do set table order ?
