Dear Jamie,
Thanks for the reply.
We have another problem to solve.
on the node we are getting product A -2 >=1.978
What does it mean (-2) ?
It is mentioned as two time slices ago. Please help me to undertand this.
From
menik
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)
[Code] .....
can someone tell me what the folowing SQL does?
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
Thanks in advance, Ralph
I have a job whose first step is to run a DTS package via a DTSRUN Operating System Command. I get the following message.
DTSRun: Loading... DTSRun: Executing... Error: -2147220499 (800403ED); Provider Error: 0 (0) Error string: No Steps have been defined for the transformation Package. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 700. Process Exit Code 1. The step failed.
Prior to 2/29/2000, it had run dozens of times successfully, the last time on 2/23/2000.
I would be most appreciative of any help.
Thanks.
I ran the DBCC SHOW_STATISTICS command for all of my indexes; I was told that high density numbers are bad, low numbers good. I have some questions about my results, though; I'm not sure how to interpret them.
Of 48 indexes, 14 have a density of 0. Does this mean that the indexes are not selective enough? Does it mean they're garbage and I should toss them?
6 have a density of NULL. They are all primary keys. I suppose this just means that they're never used because these tables are rarely queried. Would this assumption be correct?
13 have a density of 1. I have no idea what this means.
The others have densities ranging from 0.01210491 to 0.5841165. I was told that the lower this number is, the more selective and thus more useful an index is. I think 0.5841165 is too high a number. Would this be correct?
Thanks in advance.
I'm designing a new database which will be the back-end to a heavily-used web-based application (all these terms are relative - I guess the use won't be that heavy in the grand scheme of things, I'm only talking 100 users or so at the very most). Data from the old application database will be migrated to this one, and the old database is around 7GB in size after 5 years of use.
I have two different ways of linking some tables in mind, one which is slightly more complex than the other but which potentially has benefits over the simpler method. However, I'm concerned that I might be 'over-cooking' the design, and that performance would suffer as a result, so I've tried creating the two different versions of the database (the part of it I'm concerned with, anyway), one for each of the solutions I've got in mind, migrated the data into the relevant tables and carried out some queries on the data to collect some statistics.
The problem is that, whilst I can see that the more complex method is more expensive, as expected, I don't really understand if the difference is significant. Since I don't know what the numbers in the Client Statistics window actually mean (there are no units! I'm guessing times are in milliseconds?), or how much of real-world impact the difference will have, I'm finding it hard to interpret my statistics and come to a decision.
Querying the entirety of my tables to return ~20,000 records listing one column from each of the main tables I'm playing with, the simpler method had a Total Execution Time of 199, and the more complex a Total Execution Time of 272. Is that the statistic I should be most concerned with? Is that a difference I should be concerned about? Is the difference likely to be magnified when the database is much larger and in use, such that a difference of 73 milliseconds in this test scenario could end up being as much as a whole second in production, for example?
Folks,
I'm going to write an advanced whitepaper on interpreting the results of CHECKDB in SQL Server 2005 (mostly applicable to SQL Server 2000 as well), should be available before end of the year. Couple of questions for you:
1) would this be interesting/useful to you?
2) anything in particular you'd like to see covered?
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Hi,
I used SET STATISTICS TIME ON to get execution stats for a query. I found that the CPU Time was sometimes greater than the elapsed time. How is this possible? The query does not use any parallelism since I used the query option MAXDOP 1. Is the elapsed time wait time? Is the total execution time the sum of the CPU time and elapsed time?
SQL Server Execution Times:
CPU time = 797 ms, elapsed time = 162 ms.
Hi,
I used a decision-tree mining-model to describe and predict fraud. The table contains 1039 records with 775 distinct value of A-number (the calling party). I used 9 columns in the model. SQL Server reports that only 3 columns are significant in predicting the fraud
- BPN_is_too_short (called party-number is too short)
- Duration_is_zero
- Invalid_area_code
The key-column in A-number, and the predicted column is Is_Fraud with the range of values are only 0 and 1. There's no record with NULL (missing-value) in the column Is_Fraud.
Mining Legend shows in the first split
[-] 625 cases of fraud
[-] 150 cases of non-fraud
[-] 0 cases of missing
In addition to that, Mining Legend shows
[-] 79.69% of fraud
[-] 19.64% of non-fraud
[-] 0.67% Missing
Now when I compare those values, they don't match.
(A) 625/775 is 80.645%, not 79.69%
(B) 150/775 is 19.355%, not 19.64%
(C) 0 cases of NULL (missing value) should imply 0% of missing, not 0.67% of missing
Furthermore in one node (with the split on duration_is_zero), there are 541 cases of fraud and 0 cases of non-fraud. This implies the node is leaf-node. However, Mining Legend shows
514 cases of fraud, 99.35%
0 cases of non-fraud, 0.33%
[F] 0 cases of missing, 0.33%
My questions
(1) Why the values don't match like in cases A through C ?
(2) Why the values don't match even in cases D through F when we have no subtree at all ?
I've searched explanation by reading the mathematical reasoning, entropy, Gini index; but it does not answer the discrepancies of those values and percentages in the Mining Legend.
Regards,
Bernaridho
We have a gaming application which generates transactional data in MongoDB which eventually sends the data to SQL Server and it is in JSON format. This data needs to be used for reporting tool but visualizing this data in forms of a table is proving to be difficult. One example of a column we receive is:
{responseCode:0 transactionId:null amount:200.00 message:account balance }
We need to build a sort of ETL or batch job but need to interpret this in a form which SQL Server can understand.
We run std 2008.  In my ssrs log I see this for one of our most critical reports...
library!ReportServer_0-64!2244!07/07/2015-08:24:53:: Call to GetPermissionsAction(/somedirectory/somedirectory1).... which I assume is an indication of a report starting to render by first checking permissions.
Around the time my user says he still saw the revolving arrow and he stopped the report because he felt it was running too long, I see...
webserver!ReportServer_0-64!1dbc!07/07/2015-08:54:44:: i INFO: Processed report. Report='/somedirectory/somedirectory1/importantreport', Stream=''
How can it be true that he stopped it and ssrs reports that it processed the report?
About 4 minutes later I see this entry in the log...
webserver!ReportServer_0-64!15e4!07/07/2015-08:58:34:: i INFO: Processed report. Report='/somedirectory/somedirectory1/importantreport', Stream=''
Which processed report message is right? Could there be multiples cuz of subreports? I see a number of errors and exceptions around these same times but do not know how to tie either to a specific report. Is there a way?
Hello I installed MS SQl 2005 the eval version and it has expired. I have bought a copy now and i need to put the product keys in without having to reinstall the program. Is there anyway i can do this with having to reinstall SQl again..
Any help would be great
Randy Martin
I want to upgrade 5 SQL Server 6.5 production boxes to SQL Server 7. I got
SQL Server 7.0 pricing and licensing list from
www.microsoft.com/sql/70/gen/pricing.htm and I think I should buy 5 SQL
Server 7.0 Combined Product/Version/Competitive Upgrade (Since I have been using SQL Server 6.5, I don’t need to buy the full product. Is this right?). I am not sure if
the upgrade offer (the price is much lower than the full product offer $699/$1399) includes the SQL Server 7.0 both software and access license.
Thanks for any help in advance.
Stella Liu
Hi,
I am interested in opinions on the following,
main(product_id, short_text, price, type)
productTypes(type_id, type)
productTables(type_id, tableName)
productA(product_id, field1, field2)
productB(product_id, field1, field2)
To retrieve all products of type 'A', one must know the table name, in this case 'productA'.
Here is one method.
Create a table that contains the table name that corresponds to each product type, thus the stored procedure only needs to recieve the type_id which can be used to obtain the name of the respective table.
how can i get the product of two fields in sql server?
ie. i want to get like this, - select price, qty, product(price,qty) as amount from xxx - like this
I am installing SQL Server x64 on a new server, and I was told to use the same license key we have for the other servers... but no one seems to know where it is. Is there any way I could see the license key for one of the working boxes so I could put it on the new one?
Thanks,
Does anyone know of a SQL Server product that would let me do the following?Connect to the instanceConfigure thresholds like,1.Show databases not backed up in the last X days2. Show databases that are full that have logs not backed up in the lastx days/hours.3. Show jobs that have failed in the last x days.I would then like to click a process button and have it bring back anythingthat is outside those thresholds?, any thing like that in a windowsapplication?
View 1 Replies View RelatedHi All,
I have a field with numbers (double datatype) as values and I want a aggregate function that gives me the product of all the values in that field. Is there a way to do that in SSRS?
I can't find any timeframe for the 1.0 release of SQL Server Everywhere. Is this information available?
View 6 Replies View RelatedHow can the licensing product key with which SQL Server is installed be replaced/updated?
If this is possible, will the same procedure apply both to SQL Server 2000 and SQL Server 2005?
Thanks.
hi,
I am realy sorry that I am putting this question here since I don't know where to post this question.
We have purchased
Windows server CAL 2003 English OLP NL user CAL
with 5 user license
We got the Microsoft open license agreement after a month or so.
Now I want to know how to proceed with this license.
Since I am newbie to this kindly guide me in this regard.
thanks
We are building a system that has mobile clients (laptop with XP and tablet with XP). These clients have data moving back and forth to a centralised n-tier server based system. The communications is proprietory and are very low bandwidth (5 KByte/sec).
We've decided that the best approach to handling both the reference data (ranging from trivial to a complex list of items - about 2 million rows) and the day-to-day operational data is with a database product rather than attempting to handle it ourselves within code.
The question is - what SQL platform to use? SQL Everywhere seems to be a fairly good choice except that it does not seem to support stored procedures. SQL Express is another possibility but there are concerns about the size of the footprint and managing the database engine and the database itself.
The users of the mobile client are considered computer illiterate and would not be able to manage any database administrative tasks. They only come back to base every three to six months. History has shown remote management of the machine has proven difficult (if not impossible) via the low bandwidth connection.
The machines have 512MB of RAM and only 40GB of disk. They have to support XP, Office, our client, and our GIS client. What is the best answer?
Traditionally we would have used MSDE.
Regards
Paul
When I was setting up my new SQL server I entered the CD-KEY off of the wrong license. I don't want to have to uninstall everything to reinstall it with the new CD-KEY.
Is there a way to change the CD-KEY after installation?
I have a search box on my website which is used to search the products database.
I will be using the search text in an SQL stored procedure that uses LIKE statements. The search string could realistically contain any character.
How do I prevent SQL injection when any search string is reasonably feasible? .
I know "select distinct ProductId from Product " selects one product only one time how can i apply the same logic in a query like give bellow SELECT I.QuoteRequestItemId, I.ProductId ,P.StorePartNumber from QuoteRequestItem I left join Product P on I.ProductId = P.ProductId ie product with same productId should be considered only oncethere should not be more than one row with same productId
View 2 Replies View RelatedHere I have attached excel sheet. I want to do row wise sum, product and then sub total.
View 4 Replies View RelatedHi guys,
Does anybody know a convenient way to return the product of a column, similar to something like the 'sum' function?
If I have a table of form id-date-value, I'd like to do something like:
select id, date, product(val)
If not, does anyone know of a way one could structure this as a subquery?
If all else fails, I suppose I'll have to resort to a loop or cursor, but I'd rather not.
Any help would be much appreciated.
I have a SQL statement with two left outer joins which connects 3 tables. Vendors, Tracking & Activity. For whatever reason, even though each is a one-to-many relationship, I am able to join 2 tables (from Vendors to Tracking) without an issue. when I then join Activity, I get a Cartesian product.I suspected that 'DISTINCT'.
SELECT DISTINCT CASE
WHEN `vendor`.`companyname` IS NULL then 'No Company Assigned'
ELSE `vendor`.`companyname`
END AS companyNameSQL, `tracking`.`pkgTracking`, CASE
[code]....
I have a table with product_name and introduction_date(when the product was first introduced)as columns. now i wana calculate average as below
if item is sold in previous business year(suppose 2011-12) then avg should be avg price in businessyear(2010-11), if it is newly introduced(suppose 2013-14)then avg should be of current year(2013-14).
Note:- business year Apr-march
I want to get a list of any Categories where ALL the products in that Category are not published (Published = 0). (I want to get the Categories where no products are listed for it). Here are the tables, not sure where to begin :
SELECT [Id], Published
FROM Product
WHERE Published = 0
SELECT [Id] ,[Name]
FROM Category
SELECT [Id] ,[ProductId] ,[CategoryId]
FROM Product_Category_Mapping
Steve writes "Probably not a tough question if I wasn't a newbie:
I have two copies of MSSQL 2005 standard edition, intended for installation on a dual proc db server. The server is physically placed in a remote hosting service.
Since I need to install remotely, it's non-trivial how to get the bits over there. I could fed-ex the CDs to the hosting service and have them feed the machine.
However they suggested that they simply use their MSDN version, which I accepted.
But it turns out the MSDN version setup has a pre-populated product key, and does not allow me to entire my own product key(s).
So the questions:
1) besides the product key thing, is there any difference between the MSDN version and the retail version?
2) does it matter that I enter my product key(s) during installation, or do I need to simply posess the keys to be legit?
3) On a dual-proc system, must two keys entered to activate operation on each processor?
So in general, I'm confused as to the technical dependence of installation on the product keys. After $12k I kinda want to use them."
Hi,I've run into a bit of a sticky design issue. We have products inthree categories which I will call 'A', 'B' and 'C'. We have "kits"which contain three products, one from each category.Below is some sample SQL to set things up, but I need to ensure thateach kit gets three products -- one from each category. Obviously,this basic SQL doesn't allow that. Any suggestions? Do I need adifferent schema design, or is there something else I should belooking at?Cheers,CurtisCREATE TABLE category (id int identity primary key,name varchar(30));CREATE TABLE products (id int identity primary key,name varchar(30),category_id int references category(id));CREATE TABLE kits (id int identity primary key,name varchar(30));CREATE TABLE kit_products (kit_id int references kits(id),product_id int references products(id));
View 1 Replies View Relatedi have table:
customer(customerid, age,sex....)
orderdata(orderid, customerid,day)
orderdetails(orderid, productid, quantity)
products(productid, productname,...)
now, i want to show some product for customer when i now him age and sex.
e.ct: if he is a man and age =20 i show product : ball, pull, sport close....... if man is a woman , i show lips, babara, t_shirt, skirt....
if man is a chirdren, i will show joy, story for chidren....
how i create my mining model. and how i query for result in DTS