Query File Table And Get Document For Specific Product
Feb 11, 2015
Lest Say with File-stream we have a table document where the file-stream column is located, then we have another table called product-document where the productID and the Document-node are both foreign key of the Product and the Document tables respectively. if i want to query the file-stream document for a specific product, i can just join all three tables, but with file-table there is something i probably didn't get as how to query the file table and get a document for a specific product.
I am currently working on a website that deals with sales of products. For one of my pages for the website I need it to be able to change the current sales information for a specific product.
The top part of the following code selects the specific product however I cannot get the update query to work.
Code: $describeQuery = "SELECT p.ID, p.NAME, dt.[Year], dt.[Month], dt.SalesVolume FROM Products p join (select ProductCode, sum(SalesVolume) as SalesVolume, [Year], [Month] from MonthlySales group by ProductCode, [Year], [Month])dt on dt.ProductCode = p.ID WHERE [NAME] = '$desiredProduct' AND [Year] = '$desiredYear' AND [Month] = '$desiredMonth'";
$editQuery = "UPDATE MonthlySales SET SalesVolume = '$NewSales' WHERE ID = '$desiredProduct' AND Year = '$desiredYear' AND Month = '$desiredMonth'";
I have a transaction table (which contains details of all products on all orders) and is have a discrepancies table which includes details on products that couldnt been found (hence error in stock count)...
Now I want to write a query returning details of all products from a specfic orders.. pretty simple
SELECT product, description, qty from TRANSACTIONS where order_no = 'xxx'
but i also want to add an extra boolean column (true if product is in discrepancies table and false if product is not in discrepancies table...will become checkbox at front end)... im not sure how to write this bit....
I know it would a transaction table LEFT JOIN to discrepancies table, but I do not know how to write syntax to add extra column...
I have a table with all my orders lines, I need to identify (DAX formula) for a specific customer which is the first order of a specific article (first date filtered for customer and article) and use this information to tag the order line for that article for the specific customer as "Newly Ordered article" if the order date = first order date or as "Reordered article" if the date on the specific order line is subsequent to the first order date.
I imagine I need to combine a Lookup date filtered for customer and article and use it with a IF formula.
Similar to this I would also like to define that if the first order for a product for the specific custumer is older then a certain date, then this would be defined as and historical customer for the specific article, if the first order on the article is more recent the a specific date, then this will be defined as "new customer for that article".
I have a sales order table that has customer, sales, product and sales date. I am looking to return the first sales date by customer for a specific product.
SalesTable:
Customer Product Sales Date Sales First Bike Date
Jon Bike 12/4/2011 $10.00
[Code] ....
I would like to return the date of the first bike purchase date by each customer and repeat that date for each row and customer. Can I use function to complete? I have a datedim table as well related to the sales date.
How can we use the SQL INSERT statement in the stored procedure to create a table in a user specified filegroup? Table1 will be created by using the following SQL script and will be placed in a default filegroup. The question is can this table been created in a specified filegroup other than default filegroup.
INSERT INTO Table1(col1,col2) SELECT col1,col2 FROM Table2
Example, userA table assign to userA filegroup. userB table assign to userB filegroup. Usually by using CREATE statement you can put the table on a specific filegroup as long as a predefined filegroup has been hard coded in the script. I appreciate any inputs.
I have two tables in my database: order_id with fields order (text) and comp_ID (int) and another table called customers with comp_ID (int) and company name (text) and other company information fields. The link between the two tables is the comp_ID. With every order that's made the company that made the order is stored with it in the order_id table. If I type in the order id (text), I want to be able to use the order id to search the order_id table and find out what the comp_ID of the company that made that order is. Then use that comp_ID to pull up the record of company information from the customers table with the same comp_ID. Is there some way to do this in one query? Or how do I accomplish this?
MSSQL Server 2000 SP3 in both houston and memphisI have a database in houston, lets call it RED. Specific tables fromdatabase RED are copied to database BLUE. Database BLUE is then backedup, ftp'd to memphis and restored. In memphis there is a single tablein this database that will not open in enterprise manager when youchoose to open all rows you get a generic ODBC timeout error no numbersjust simply "TIMEOUT".If I log into the servers in houston and open the table from databaseRED or BLUE there is no issue. I can however return up to 66,199 rowswithout an error. If i choose a number higher than this i get thetimeout error. I discovered I could run a querySelect Distinct * from f0911and it would display all my data. I am able to export the data fromthe table using this query to a csv file and reimport the data into atable and it works just fine.I built another sql 2000 server SP4 and there is no issue with thetable in the database when it is restored there. I have also triedrestoring as a different database name on the production (sql 2000 sp3)server to no avail.I can't figure out why this single table is not functioning properly inthe one instance of sql 2000.If anyone has any ideas please share them, I'm running out myself. I'mobviously very new at sql database administration and would appreciateany advice.Also, i don't believe the issue has to do with timeout countdowns.Everywhere i could change them (Enterprise manager and SQL Serveritself) they are set to unlimited if possible. I also don't see howthe problem could be related to the service pack of the sql server,seeing as although it works on the sp4 server in my possession it stillworks just fine on the sp3 server in houston.Thanks,Michael Smith
I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.
I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.
For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?
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
I am using the code below to get all the children of a particular product and it is working fine. How to get the particular product's id in the select statement. for example, i need to show 891 in a separate column for all the records returned by the query below.
DECLARE @Hierarchy TABLE (Product_Id INT, Parent_Product_Id INT) INSERT INTO @Hierarchy VALUES (123, 234) INSERT INTO @Hierarchy VALUES (234, 456) INSERT INTO @Hierarchy VALUES (456, 678) INSERT INTO @Hierarchy VALUES (678, 891) INSERT INTO @Hierarchy VALUES (891, NULL)
I'm using the import wizard to create a new table from a flat file source. The table gets created but no data gets copied. What's wrong? Here's the report:
I have read previous threads on this, still not working.
New installation of SQL Server 2005 on a Windows Server 2003 box. I have installed SP1 on both the server & the client, do I have to re-create the package?
I have created a dts (.dtsx file). which gets the data from my log file and insert into my sql 2005 table. In dts i am using 'Flat File Source' and 'OLE DB Destination' (becoz i need to acess my db remotely). When i execute this through 'SQL Server Business Intelligence Development Studio' it's working fine but when i execute this through my c# code, it throws me an error 'The product level is insufficient for component Flat File Source (1)'.
I've just downloaded SQL Server Express (with advanced services) from both the main SQL Server Express website and from MSDN. When I right click on the exe I downloaded and go into properties the Product Version says SQLEVALVER. To me this implies it is an evaluation.
Can I confirm this is just a mistake and if we start to redistribute the product it won't timeout in the future? I am sure it is not an evaluation but just want to make absolutely sure.
Hello everyone! I've got a problem with a real slow query, I would be very happy if somebody has any idea to improve the speed of it... The idea is to get the top 2 products, a customer hasn't bought wich are in his interest...
query (simplificated) ------------------------------------------------- SELECT TOP 2 prodID, Title, Price FROM bestSold7Days WHERE prodID NOT IN (SELECT prodID FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID WHERE (orders.custID=394)) AND (prodType = COALESCE((SELECT TOP 1 products.prodID FROM orders INNER JOIN orderProducts ON order.orderID = orderProducts.orderID INNER JOIN products ON orderProducts.prodID = products.prodID WHERE (orders.custID=394) GROUP BY products.prodType ORDER BY SUM(orderProducts.PCS) DESC), 2)) ------------------------------------------------- end query
(COALESCE is for replacing if the customer hasnt ordered anything, or hasnt ordered anything of this type)...
Name Qty Location ----------------------------------- Item A 2 Warehouse 1 Item A 5 Warehouse 2 Item B 3 Warehouse 1 Item C 1 Warehouse 1 Item B 6 Warehouse 2
How is the sql command/query/store procedure to generate result like this?
Name Qty ----------------- Item A 7 Item B 9 Item C 1
I have a sql query with a 'for xml explicit' clause. I would like to export the results of the query as an .xml file. I specify the query as 'SQL Command' in an OLEDB source adapter, but I don't know see an adapter that I can use for the destination. Any ideas? Thanks.
I have a table having XML column. I want to read a XML document and insert content of the XML file into this xml column. CREATE TABLE [XMLTest]( [DataAsXML] [xml] NULL,
[CreatedDate] [datetime] NULL )
I have tried the following query declare @filepath varchar(100)
declare @filename varchar(100)
set @filename = 'Referred'
set @filepath = 'D:ReportOutput'+ @FileName +'.xml'
print @filepath
insert into XMLTest
SELECT xCol,getdate()
FROM (SELECT * FROM OPENROWSET
(BULK @filepath,SINGLE_BLOB) AS xCol) AS R(xCol)
Problem is that When I give complete file path for BULK instead of variable name i.e. BULK 'C:Test.xml', query runs fine. But when I try to use @filepath, I get error "Incorrect syntax near '@filepath'." What am I doing incorrect? Also,If some one can suggest a better approach to load content of XML document to table?
SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document)
SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB)
AS Document"
EXEC (@QUERY)
This puts some values including a pdf or .doc file into a table, tblDocTable.
Is it possible to change this so that I can get the values from a table rather than as parameters. The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport.
tblImportExport has the path for the document (DocPath) so I would subsitute that field, ie. DocPath, for the @fPath variable.
Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport.
I am working the Books Online documentation for the full-text search feature of SQL Server 2005 Express Advanced and having a problem following the instructions.
I made sure to choose the "Full Text Search" option during installation of VB 2005 Express Advanced.
I downloaded, installed, and attached the AdventureWorks database successfully.
I checked to ensure that the database was enabled for full-text search, but could not follow the instructions for indexing a table within the database. Here are the instructions from Books Online: To enable a table for full-text indexing
Expand the server group, expand Databases, expand User Databases, and expand the database that contains the table you want to enable for full-text indexing.
Right-click the table that you want to enable for full-text indexing.
Select Full-Text index, and then click Enable Full-Text indexing.
Another document notes:
To create a full-text index on a table, the table must have a single, unique not null column. For example, consider a full-text index for the Document table in Adventure Works in which the DocumentID column is the primary key column.
When I right-click the Document table (Production.Document) in the AdventureWorks database, there is no option to "Select Full-Text Index" or "Enable Full Text indexing".
Am I missing something here?
How do I get the the table indexed for full text search?
I try to activate the map document control on my report. My Report is composed of a table in which I group by 2 criterias. When I set the document map label of the first entry of the group list then I get an error on the preview :
"An internal error occurred on the report server. See the error log for more details. "
Can someone tell where can I find the error log file ?
I have someone who is sending me .htm documents, with a table in them, and I was wondering if there is a way to import the data from those tables into a SQL table, probably using an SSIS Package.
Ok, so im pretty much finished writing my forum web page. However to display things like how many replies each thread has and who replied last, i need to perform a query in the code file. Im guessing its simple enough but i cant get the syntax for actually performing any query. I already know the sql syntax like select * from all that stuff but how do i get do something like: Dim x as integer = sqlQuery("Select count(*) FROM ...") Currently i have it all working by creating a table and making it invisible and just pulling data from the table but thats sloppy and pretty ineffecient if i databind a table for every single topic name.
I need to copy the following columns from my Employee table in my Performance DB to my Employee table in my VacationRequest DB: CompanyID, FacilityID, EmployeeID, FirstName, LastName, [Password] = 'nippert', Role = 'Employee' I tried the advice on this website but to no avail:http://www.w3schools.com/sql/sql_select_into.asp
