DB Engine :: Repeated Warning - Columns With No Statistics
Nov 18, 2015
I am running on following environment -Â
OS- Windows server 2012Â
SQL - Windows SQL 2012 R2
Sharepoint 2010 SP2 Â Â
SQL has DB restored from earlier server. DB is quiet large in size because used with sharepoint.
Following steps have been followed on this restored DB -Â
Maintenance Plan
Rebuild-Reorganize the indexes
Update Statistics
After above steps, query on Sharepoint table found performant. But after some delay/idol time(overnight) on server. Query takes much more(20X) time to execute. On running execution plans observed that some warnings are seen on columns which are primary keys.Â
Columns with no statistics 'AllDocs.tp_DocID'
When Update statistics is executed again in SQL management studio above issue is again seen resolved, but came again after some delay.
Is there any SQL logs where can I find activities performed during overnight with SQL which make this issue to happen? This issue was not there on Win2k8 environment.
I'm running SQL Server 6.5, with the latest Service pack on NT 4.0 with SP4. A couple of days ago I started getting a warning message in my event log right after a scheduled task ran. The scheduled task calls three stored procedures (data dumps for master, msdb, and a production database) it also runs a consistency check on each before dumping them. I can interactively run each stored procedure and get NO error messages. Prior to getting the warning message (which occurs after the scheduled task runs) I get normal informational entries that the dumps occurred without a problem and the files are written to the proper area. The error is in the Task Engine category and are Warning type messages. Here is the text:
SQL Server Scheduled Task: 27, 'Proddata Dump' -- Status: Failed -- Task Invoked on: 5/20/99 5:00:00 AM -- Message: Database 'ASSMTCTRPROD' (116647 pages) dumped to file <1> on device 'D:DATDUMPASSMTCTRPROD_990520_DATA.DMP'. (Message 4035)
Any help that anyone could give would be greatly appreciated. Thanks
IdnCol ColumnA ColumnB ColumnC ColumnD 1 CC DD EE FF 2 DD GG HH JJ 3 HH JJ KK HH
How I can check for the repeated value in the column. for example in the 3rd record, in columnA and ColumnD the value HH is repeated so is there a way to find if there are values repeated in any of the columnA ColumnB ColumnC ColumnD.
I have problem, i wanted a query which will search the duplicate and then give suggestionmost repeated word Table containing the records like below
ID Movie Name New Name
1 Spider Man Spider Man
2 Spider Man 2 Spider Man
3 Spider Man 3 Spider Man
4 Spider Man UK Spider Man
5 Spider Man USA Spider Man
6 New Spider Man Spider Man
7 Spider Man Black Spider Man
8 Spider Man Part 1 Spider Man
9 Spider Man Part 2 Spider Man
10 Spider Man I Spider Man
11 Spider Man III Spider Man
12 Spider Man Part II Spider Man My manufacturer send me the data in this format and i have to allocate there new name to do some comparison I wanted to make this process automatic. what i mean is i need a query which will give me a repeated records along with suggestion as new name. I am fully confident that you guys will help me out from this problem. Looking forward
I have a flat file data source and SQL Server destination data flow. Only a subset of columns from the source are mapped to the destination. During execution SSIS returns DTS pipline warnings for every unmapped source column. Is some kind of transformation the only way to get rid of these warnings?
Also this data flow subsequently returns an error: [SQL Server Destination [1293]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'GlobalDTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security."
I'm researching this error, but if anyone is familiar with it your advice would be appreciated. Thanks.
[Production IDW [1]] Warning: Truncation may occur due to retrieving data from database column "Industry" with a length of 16 to data flow column "Industry" with a length of 8.
When I look at the industry lookup table the column size is 50. But the data flow metatdata is saying the oclumn is 8. How can I change the data flow column? When I try to edit it and click on metadata it is uneditable. The field it is matching to is 50 and the field it is coming from is 16.
I am really puzzled by an apparent difference between table index key column order and its statistics order. I was under understanding that index statistics mirror index definition. However, in my db 2470 index ordinal definitions match statistics definition but 66 do not. I also can reproduce such discrepancy in 2008 R2, 2012 and 2014.
As per definition,
stats_column_id int
1-based ordinal within set of stats columns
This script duplicates this for me.
BEGIN TRAN GO use tempdb GO CREATE TABLE [dbo].[ItemProperties]( [itmID] [int] NOT NULL, [cpID] [smallint] NOT NULL, [ipuID] [tinyint] NOT NULL,
[Code] ....
The result I get is this:
object_id      stats_name                                     stats_column_list 1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, cpID, ipuID,
and
object_id      index_name                                     index_column_list 1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, ipuID, cpID,
Also a query I used to discover this in my db is:
WITH stat AS ( SELECT s.object_id ,s.name as stats_name ,( SELECT c.name + ', ' as [data()] FROM sys.stats_columns as sc
Hello group.I have an issue, which has bothered me for a while now:I'm wondering why the column statistics, which SQL Server wants me tocreate, if I turn off auto-created statistics, are so important to theoptimizer?Example: from Northwind (with auto create stats off), I do the following:SELECT * FROM Customers WHERE Country = 'Sweden'My query plan show a clustered index scan, which is expected - no indexexists for Country. BUT, the query plan also shows, that the optimizer ismissing a statistic on Country, which tells me, that the optimizer wouldbenefit from knowing this.I cannot see why? (and I've been trying for a while now).If I create the missing statistics, nothing happens in the query plan (andwhy should it?). I could understand it, if the optimizer suggested an indexon Country - this would make sense, but if creating the missing index, queryanalyzer creates the statistics with an empty index, which seems to me to beless than usable.I've been thinking long and hard about this, but haven't been able to reacha conclusion :) It has some relevance to my work, because allowing theoptimizer to create missing statistics limits my options for designingindexes (e.g. covering) for some rather wide tables, so I'm thinking why notturn it off altogether. But I would like to know the consequences - hopesomebody has already delved into this, and knows a good explanation.RgdsJesper
What is the unit of the numbers you get in the Time Statistics-part when running a query in Microsoft SQL Server Management Studio with Client Statistics turned on?
Currently I get mostly 0´s, but if I try and *** up a query on purpose I can get it up to around 30... Is it milliseconds or som made up number based on clockcycles or... ?
I would also like to know if it´s possible to change the precision.
CONTAINSTABLE(ClinicalLiteratureTbl, *, '("body mass index" OR "BMI" OR "Quetelet`s Index" OR "Quetelet Index" OR "Quetelets Index") AND ("myocardial infarction" OR "myocardial infarct" OR "MI" OR "myocardium infarct" OR "myocardium infarction" OR "cardiac infarction" OR "myocardial necrosis" OR "coronary attack" OR "myocardium necrosis" OR "myocardial infarction syndrome" OR "myocardial necrosis syndrome" OR "heart attack" OR "coronary thrombosis" OR "AMI" OR "post-AMI" OR "post AMI" OR "post infarction" OR "post-infarction")') AS KEY_TBL WHERE FT_TBL.ArticleID = KEY_TBL.[KEY] AND FT_TBL.RaterGroupId IN (1,2,3,4) ORDER BY IssueYear DESC
I have an SP which concatenates 2 columns in a where clause - ie WHERE [Column1] + [Column2] = @var. Â This, as far as I'm aware, is not going to access any seek on an index on a table.
My task is to create an index on this table and get the SP to access said index.
Aside from combining the two columns into one column on the table, how I can get an SP to access the newly created index when queried by the SP?
One thought of mine is to firstly index Column1 and allow the SP to access this index by inserting the rows into a temp table. Â Then a search on the temp table to retrieve the records on a search on Column2.
Why this SQL procedure gives contiguous repeated records ( 3 or 4 times ) ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ALTER PROCEDURE GetProductsOnPromotInDep (@DepartmentID INT) AS SELECT Product.ProductID, Title FROM Product INNER JOIN (ProductCategory INNER JOIN (Category INNER JOIN Department ON Department.DepartmentID = Category.DepartmentID) ON ProductCategory.CategoryID = Category.CategoryID) ON Product.ProductID = ProductCategory.ProductID WHERE Category.DepartmentID = @DepartmentID AND ProductCategory.CategoryID = Category.CategoryID AND Product.ProductID = ProductCategory.ProductID AND Product.OnPromotion = 1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If I have a student table with 3 columns ID, FirstName and LastName ID- FirstName -LastName ------------------------- 1 Tom Hanks 2 Jerry Thomas
and I have a subject table with two columns StudentID and Subject. StudentID is a foreign key to the student table. StudentID - Subject ------------------------ 1 History 1 Biology 2 History
If my query is select distinct student.id, student.fname, student.lname, student.subject from student left outer join subject on student.id = subject.studentid
then I get 1 Tom Hanks History 1 Tom Hanks Biology.
Is there a way I could get 1 Tom Hanks History null null null Biology
For the below mentioned query their is repetition of rows with the same data.
SELECT srs.prod_area as PA, srs.art as ArtNo, b.adsc_unicode as ArtName, cast(case when a.unit <> '2' then c.avsx/10 else c.avsx/1000 end as integer(2)) as AwsMHS, cast(srs.estimate/10 as integer(1)) as AwsSRSThisWeek,
I am beginner on using SQL. How can I select the repeated rows (five or more times) on a table which have the same ID's but different updated date. I do not need to group all the rows with the same ID,s but rows which are repeated many times according to the required reports needed.
Below are some information regarding tables and views:
Hi I have created a Percent Log Used Alert with a threshold of 85% and am getting e-mailed with Database Mail, the problem is that I continue to get e-mailed repeatedly with the same e-mail until I disable the Alert. Is there any way to have it just e-mail it once?
I have rows coming from the db including: Contract Number, Contract Name, owner and Actions associated with each contract.
SQL statement brings back: Contract Number Contract Name Sales Owner Action 1234 123453 $50 Neil x 1234 123453 $50 Bob y 534232 5464634211 $30 Harry z
The problem is that each contract can have multiple actions associated with it... There ideal output would be:
Contract Number Contract Name Sales Owner Action 1234 123453 $50 Neil x Bob y 534232 5464634211 $30 Harry z
Total: $80
Basically I need to hide and not include repeated items based on a contract number... one idea I had was creating a group based on contract number and then display info in the header and then only owner and actions in the detail section.. The problem is Totals... how can I can it to avoid count the duplicated values..
I am developing a system for my uni course and I am stuck a little problem...
Basically its all about lecturers, students modules etc - A student has many modules, a module has manu students, a lecturer has many modules and a module has many lecturers.
I am trying to get a list of lecturers that run modules associated with a particular student. I am able to get a list of the appropriate lecturers, but some lecturers are repeated because they teach more than one module that the student is associated with.
How can I stop the repeats?
Heres my sql select code in my cs file:
string sqlDisplayLec = "SELECT * FROM student_module sm, lecturer_module lm, users u WHERE sm.user_id=" + myUserid + "" + " AND lm.module_id = sm.module_id " + " AND u.user_id = lm.user_id "; SqlCommand sqlc2 = new SqlCommand(sqlDisplayLec,sqlConnection); sqlConnection.Open(); lecturersDG.DataSource = sqlc2.ExecuteReader(CommandBehavior.CloseConnection); lecturersDG.DataBind();
And here is a pic of my Data Model: Data Model Screenshot
I am running a query on multiple tables and the data I get back consists of several repeated rows but with one column different. I want to take out those repeated rows and for the column that is different join that data and separate it by a comma. Can this be done?
Ex. Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 717 Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 610 Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 310
So i would like this data to come up as: Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 717,610,310
In the web site that I am building ( in C# language ), a hypothetic customer who would buy something would be redirected to a Secure payments company where he would make the payment and then the company would send back to my web site, information about this transaction.
My program would then save this info in a Microsoft SQL database. The problem is that this company uses to send the same info several times repeatedly and I do not want to store the same info more than once.
So I want a SQL procedure where it takes the invoice number of the customer ( contained in its string of info ) and looks inside a table to see if it was already stored there. If it is there ( or not ), it would return a value, which could be false/true or 0/1 so my program could use this value to save a new info or not and then activate ( or not ) some related tasks.
I am still learning SQL and I tried the below procedure but it is not working. Which alternative procedure could solve the problem ?
~~~~~~~~~~~~~~~~~~~~~~~~~ CREATE PROCEDURE VerifyIfInvoiceExists (@Invoice VARCHAR(50)) AS SELECT COUNT(*) FROM IPN_received WHERE Invoice = @Invoice ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I'm using the following query to look in a log file and show somestatistics. It counts the total number of views and the total numberof unique users who have viewed a particular item (the "id" and"title" fields are associated with the item).SELECT title, COUNT(id) AS NumberViews, COUNT(DISTINCT UID) ASNumberUniqueUsers, Type, idFROM ActivityLogWHERE dtTime >= 'Nov 1 2004 12:00AM' AND DtTime <= 'Nov 1 200512:00AM'GROUP BY Title, Type, hdnIdORDER BY TopViewed descThis works fine on SQL Server 2000 (our development machine), but onSQL Server 7 (our production machine), the title column has the samevalue for every row. The other columns show the correct values.If I remove the "ORDER BY" clause, then it works fine. If I removethe "COUNT(DISTINCT UID)" column, it works fine. If I totally removethe WHERE clause, it works fine.Any ideas? It seems like a bug since it works fine on sql2k. I'vetried adding OPTION (MAXDOP 1) to the end of the query, but thatdidn't help.We're using SQL Server 7.0 sp1, and my boss doesn't want to riskupgrading to sp4 because it might screw up all of our otherapplications. I looked through all of the sp2 through sp4 bug fixes,and I didn't see anything specifically mentioning this.Thanks.
I am using the query below to retrieve these results: You can see that the results are repeated, once for DATIF = 1 and then again for DATIF = 2. In this case does not matter if the results appear close to DATIF 1 or DATIF 2. Take in care that I can not know how may extradates or Extrasums are attached to each Account.
Is there any way to avoid these repeated rows? Thanks in advance, Aldo.
ExtraSums.SUF FROM EXTRADATES AS ExtraDates LEFT OUTER JOIN EXTRADATENAMES AS ExtraDateNames ON ExtraDates.DATFID = ExtraDateNames.DATFID RIGHT OUTER JOIN ACCOUNTS AS Accounts ON ExtraDates.KEF = Accounts.ACCOUNTKEY LEFT OUTER JOIN EXTRASUMS AS ExtraSums LEFT OUTER JOIN EXTRASUMNAMES AS ExtraSumNames ON ExtraSums.SUFID = ExtraSumNames.SUFID ON Accounts.ACCOUNTKEY = ExtraSums.KEF LEFT OUTER JOIN EXTRANOTENAMES RIGHT OUTER JOIN EXTRANOTES ON EXTRANOTENAMES.NOTEID = EXTRANOTES.NOTEID ON Accounts.ACCOUNTKEY = EXTRANOTES.KEF WHERE Accounts.SORTGROUP BETWEEN 0 AND 999999999 AND Accounts.ACCOUNTKEY BETWEEN '123456' AND '123456'
I have hundreds of these errors saying 'Login failed for user 'Reporting' The user is not associated with a trusted SQL Server connection [CLIENT: ip address]
The ip address is that of the server that sql server is installed on.
Looking in my log file, all looks good until I get to Service Broker manager has started, then I get Error: 18452, Severity: 14 State: 1 then these two lines repeat about every minute, for the last 3 days!
I think I must have just missed a tick box somewhere, but where?
I have been into one of the databases, and input and checked data, both via an application I wrote and SQL Server Management Studio.
I am also having trouble connecting using my application to connect to the database, I can only connect if I use a Windows administrator account (this SQL Server 2005 running on a Windows 2003 Server, with the app on PC running Windows 2000)
I have a matrix report with one row group and one dynamic column group. My issue is that I want to see the column group to be appeared only on the first page of the report not on every next page, because I am going to finally export the report to Excel spreedsheet so I don't want the column to be repeated in the middle of the records.
I am fairly new to SQL Server 2005 and before now, I have only had to restore databases, and connect to tables via ODBC connection in a reference (read only) setup. Today, I have a very small project to set up using the server.
I have a userlogon.csv file that the network stores on a file server in a hidden share logon$. It has 4 columns, UserID, Computer, Date, Time.
I was able to create a database called UserLogon and import the file as it was today. I want to create a scheduled update so the server would go to this file perhaps 4 times a day (or more) and grab any new logins that have appended itself to this CSV file.
So, as a newbie with a 1,900 page SQL Server 2005 unleashed manual at my side, could someone outline what the steps are in general I should follow to set this up?
I have the process laid out in my mind, but I don't know how to translate in into a scheduled task of the SQL Server :
1. Create DB and import the table (done) 2. create a stored procedure that connects to the CSV file and evaluates date and time stamps then appends any new records into the SQL db table. (appending records would be achieved by using the INSERT and WHERE statements?) 3. Schedule a job to perform this task on a routine basis.
It appears that the file connection portion of this set up is defined outside the evaluation and append record procedure? (not in the same stored procedure). Perhaps I tie the whole process together using the Job Manager, selecting the file settings, and then the stored procedure to be performed on the file.?
I hope I have been descriptive enough to ask if someone could outline the modules/features/processes involved so I can read up on them and figure them out using the book.
Ihave a table With A Culomn That Is NOT Unique And Repeat Many Tims BUT I want To Have a Select Command That Filter Repeated Value And Show Only One of rows Whith Same Data
In the web site that I am building ( in C# language ), a hypothetic customer who would buy something would then be redirected to a Secure payments company where he would make the payment and then the company would send back to my web site, information about this transaction.
My program would then save this info in a Microsoft SQL database. The problem is that this company uses to send the same info several times repeatedly and I do not want to store the same info more than once.
So I want a SQL procedure where it takes the invoice number of the customer ( contained in its string of info ) and looks inside a table to see if it was already stored there. If it is there ( or not ), it would return a value, which could be false/true or 0/1 so my program could use this value to save a new info or not and then activate ( or not ) some related tasks.
I am still learning SQL and I tried the below procedure but it is not working. Which alternative procedure could solve the problem ?
~~~~~~~~~~~~~~~~~~~~~~~~~ CREATE PROCEDURE VerifyIfInvoiceExists (@Invoice VARCHAR(50)) AS SELECT COUNT(*) FROM IPN_received WHERE Invoice = @Invoice ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INSERT #Visits (OpportunityID, ActivityID, FirstVisit, ScheduledEnd) SELECT 1, 1001, '2014-08-17', '2014-08-17 12:00:00.000' UNION ALL SELECT 1, 1002, '2014-08-17', '2014-08-17 17:04:13.000' UNION ALL SELECT 2, 1003, '2014-08-18', '2014-08-18 20:39:56.000' UNION ALL
Basically I'd like to mark the first Activity for each OpportunityID as a First Visit if its ScheduledEnd falls on the same day as the FirstVisit, and otherwise mark it as a Repeat Visit.
I have this so far, but it doesn't pick up on that the ScheduledEnd needs to be on the same day as the FirstVisit date to count as a first visit:
SELECT*, CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit) WHEN ScheduledEnd THEN 1 ELSE 0 END AS isFirstVisit, CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit) WHEN ScheduledEnd THEN 0 ELSE 1 END AS isRepeatVisit FROM#Visits
