I'm creating my first Report Model and I've managed to get through it, but if I select the "Update model statistics before generating" in the "Report Model Wizard", I get this error:
"Specified method is not supported"
(It would be a little less frustrating if it actually HAD specified the method <s>)
Anyone know what this is or how to fix it?
Thanks in advance,
Mike
I'm trying to create a few report models in Visual Studio to demonstrate the Report Builder tool and I'm encountering an issue when I try to generate a Report Model.
I get the error message "Specified Method Is Not Supported" when I click Finish on the Report Model wizard. The error detail is:
===================================
Specified method is not supported. (Microsoft Visual Studio)
------------------------------
Program Location:
at Microsoft.ReportingServices.ModelDesigner.Wizards.ChooseSemanticModelName.CreateSqlDsvStatisticsProvider(IDbConnection connection)
at Microsoft.ReportingServices.ModelDesigner.Wizards.ChooseSemanticModelName.BuildModel()
Steps to reproduce:
1. Create a new Report Model project in Visual Studio 2005 against SQL Server 2005
2. Create a new data source. The data source was tested and works correctly
3. Create a new data source view. The data source view was also tested (clicked on Explore Data) and I was able to look at the data in the view
4. Right-click on Report Models and select "Add New Report Model"
5. Click next at the welcome screen
6. Select the data source view created in #3
7. Leave the selected options for report model generation rules for creating the data source view
(Create Entities for all Tables, Create Count Aggregates, Create Attributes, Create Attributes for auto-increment columns, Create date variations, Create numeric aggregates, Create date aggregates, Create roles were all selected)
8. Click next
9. Select Update Model Statistics Before Generating (default option)
10. Click next
11. Click Run
Once I click run it processes for approx 3 seconds and then returns the error above.
If anyone could help me I'd appreciate it. Microsoft hasn't published any details on the namespace and I'm not sure why I'd be getting the error I am.
Thanks in advane,
Maurice
Sorry about the huge post, but I think this is the amount ofinformation necessary for someone to help me with a good answer.I'm writing a statistical analysis program in ASP.net and MSSQL7 thatanalyzes data that I've collected from my business's webpage and thehits it's collecting from the various pay-per-click (PPC) engines.I've arrived at problems writing a SQL call to generate certainstatistics.Whenever someone enters our site from one of the PPC search engines, Iwrite out a row to the Hits table. In that table are the followingcolumns:HitID - the Unique ID assigned to each hit that comes into the siteKeyword - the keyword the user searched on when he or she came to thesiteSearchEngine - the PPC engine the user came fromSource - this is pretty much always 'PPC'...if we were to do otherthings, like a newsletter, then this would be different.TimeArrived - the date and time the user arrived at the website. Ihave no idea why I didn't call it "datearrived," since I use "date"and not "time" pretty much everywhere else...(I don't think the rest are important, but they might be, so I'llinclude them for completeness's sake)Referring URL - the URL the user came fromReferring Website - the string between the 'http://' and the first '/'in the URL. I know it's redundant information, but when I designedthis part, I didn't know how to parse it out afterwards, so I justfigured I'd duplicate it.Page Visited - the page the user first arrived atWhen a person comes to the site, I also write out a session cookiecontaining the user's hitID. If the person fills out an enrollmentform (a process which we refer to as "responding"), I attach thatsession ID to the form. The response form (and thus the responsestable) is long; these are the important fields:id - a unique ID for each responsedate - the date and time of the responsestatus - a varchar field containing a status code. I would have madeit a number, but I wanted it to be viewable from looking at the rawdatabase.hitid - the HitID of the user, taken from the session cookie. If thereis no session cookie (for whatever reason), the HidID is written outas 0. While it wouldn't occur often, I can't guarantee that there willnever be more than one response record attached to a singular hitid.Later, some of the responses turn into "confirmations", which meansthat they've actually ordered from us, not just filled out the form.This usually happens about three or four days after the initialresponse. When this happens, the status of the response is changed toa phrase containing the word "confirm" in it (there are a few of them,but they all contain that word).So now that we've collected all this marketing intel., I need toanalyze it.I've written a parser that takes reports from various pay-per-clickcompanies and puts them into a table called PPC. Information in thiscolumn is written out as one record per search engine per keyword perday. The schema is as follows:id - a unique ID for the record in the tabledate - the date to which the information in the record appliessearchengine - the PPC engine to which the information applieskeyword - the keyword to which the information appliesclicks - the number of clicks on the applicable keyword on theapplicable search engine on the applicable day.impressions - same as clicks, but for impressionscpc - the cost per click on the applicable keyword ...avgpos - (I don't always have a value for this field) The averageposition that the keyword was shown in for the applicable keyword ...With this data in, the last step is actually analyzing the threetables for useful statistics on the various keywords, search engines,and time frames. That's the step I've been trying to complete.So what I need is a SQL call that I can run that generates a tablewith the following information:SearchEngineKeywordCost / Click - When calculating the CPC, I can't just take an averageof all the records. I need to calculate the total amount spent per day(clicks * cpc), add that up for every day, and then divide that by thenumber of total clicks. Just doing an average doesn't take intoaccount the fact that some days we'll get more clicks than others.Total Spent - # Clicks * CPC#Responses - counting the number of records in the responses table#Confirms - counting the number of records in the responses table with"confirm" in their statusTotal Spent / #ResponsesTotal Spent / #ConfirmsOh yeah, and I want to be able to order by any four of the fields inany order, narrow my selection to only those keywords that either areor contain a user-specified string, further narrow my selection toonly those records that fit other user-specified criteria for any ofthe columns in the table I'm generating, and select only the top xrecords (where x is a user-specified number). I already haveuser-controls that output the SQL for all of these things, but I needto have places in which I may put that SQL in my call.After many trials and tribulations, I've come up with the followingSQL call. Right now, its output for nearly every row is incorrect, Ithink in a large part due to the fact that the method that I'm usingto generate the number of clicks is yielding incorrect values.If you'd like to help me and you think that modifying the followingcall is easier than writing a whole new one, be my guest; if you'dprefer to write a new one, I'm game for that, too. I'm just concernedwith its working right now, and any help you can give me is greatlyappreciated.Anyway, here's the call:/*sp_dboption @dbname='NDP', @optname='Select Into', @optvalue=true;*//*Running the above might be necessary to get the "Select Into"s towork*/Drop table ResponsesPPCDrop table ConfirmPPCDrop table TempPPCSELECT Responses.[ID] as [ID], Responses.Status, PPC.SearchEngine,PPC.KeywordInto ResponsesPPCFROM Responses, PPCWHERE Responses.HitID IN(SELECT Hits.HitIDFROM HitsWHERE Hits.SearchEngine = PPC.SearchEngineAND Hits.Keyword = PPC.Keyword)SELECT ID, Status, SearchEngine, KeywordInto ConfirmPPCFROM ResponsesPPCWHERE Status LIKE "%confirm%"Order by SearchEngine, KeywordSELECT PPC.SearchEngine, PPC.Keyword,SUM(PPC.Clicks), /*I noticed that thiscolumn gives me incorrect values(I don't need it in my final report, but it's useful for debugging).For some keywords, it gives me huge numbers(e.g. 265 clicks on one word that got ~10 clicks /day over five days),and for others, it doesn't give me enough. I think this is a majorpartof what's throwing off the rest of the statistics*/Case SUM(PPC.Clicks) WHEN 0 THEN 0 ELSESUM(PPC.clicks * PPC.cpc) / SUM(PPC.Clicks) END as CPC,SUM(PPC.clicks * PPC.cpc) AS TotalCost,count(ResponsesPPC.ID) As NumResponses,Count(ConfirmPPC.ID) As Confirms,(Case Count(ResponsesPPC.ID) WHEN 0 THEN 0 ELSESUM(PPC.clicks * PPC.cpc) / count(ResponsesPPC.ID) END) ASCostPerResponse,(Case Count(ConfirmPPC.ID) WHEN 0 THEN 0 ELSESUM(PPC.clicks * PPC.cpc) / count(ConfirmPPC.ID) END) AsCostPerConfirmFROM (PPC LEFT JOIN ResponsesPPC ON PPC.SearchEngine =ResponsesPPC.SearchEngineAND PPC.Keyword = ResponsesPPC.Keyword)LEFT JOIN ConfirmPPC ON PPC.SearchEngine = ConfirmPPC.SearchEngineAND PPC.Keyword = ConfirmPPC.KeywordGROUP BY PPC.SearchEngine, PPC.KeywordOrder by PPC.keyword desc/*Drop table ResponsesPPCDrop table ConfirmPPCDrop table TempPPC*//*I don't drop them right now so I can look at them,but normally, one would drop those tables.*/Thanks a lot for your help,-Starwiz
View 4 Replies View RelatedIs it possible to write a SP (Automate) to generate STATISTICS on any database and then use the output to create the stats on that database.
I ran the tuning adviser and it suggested indexes with lot of STATISTICS on the dev environment. This dev environment is replicated in several other environment with data size in these environment varying. I would like to know if I can create a SP which generates STATISTICS information pertaining to specific database environment for the query in question for tuning.
I have been generating report models for users to use with Report Builder and there is no data when they select the model. I noticed that the tables I chose did not have a primary key and when I chose a different table, with a primary key, and generated a model from it, then there was data for the user to use in Report Builder.
Is there a documented work around or will I need to set a primary key on each table?
Hi,
I just have a Dataset with my tables and thats it
I have a grid view with several datas on it
no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error
Unable to find nongeneric method...
I've try to create an Update query into my table adapters but still not working with this one
Also, try to remove the original_{0} and got the same error...
Please help if anyone has a solution
Thanks
When I attempt to generate a datasource model I get the following error messages:
------------------------------------------------
More than one item in the Entity 'Customer' has the
name 'Customer Merge Custs'. Item names must be unique
among immediate siblings. (DuplicateItemName)
More than one Field in the Entity 'Customer' has the
name 'Customer Merge Custs'. Field names must be
unique within an Entity. (DuplicateFieldName)
More than one item in the Entity 'Pricing Service Layout
Detail' has the name 'Pricing Service
Extensions'. Item names must be unique among immediate
siblings. (DuplicateItemName)
More than one Field in the Entity 'Pricing Service Layout
Detail' has the name 'Pricing Service
Extensions'. Field names must be unique within an Entity.
(DuplicateFieldName)
---------------------------------------------------
Examining any of the above tables in SQL Server Management Studio does not reveal any duplicate column names. In fact, 'Customer_Merge_Custs' does not appear to be a column in 'Customer' nor does 'Pricing_Service_Extensions' appear in 'Pricing_Service_Layout_Detail'.
As an experiment, deleting the table 'Pricing_Service_Extensions' and regenerating did make the two associated messages go away.
Steve P.
Wasted a lot of time figuring out the possible cause of this. I was working in connected mode (with VSS), and when it tried to create a Report Model (after doing all its validations/checks), it had to check-out the project file, which is normal. But before doing so, it tries to check-out the .dsv file as well (data source view file from which report model is being generated). While attempting this, the visual studio crashes.
I could never guess that this could be an issue. All the time I was trying to figure out if there was anything wrong with the data in my tables.
So, for me, a simple solution worked - check-out the .dsv file before you start creating data model. I hope this may save time for others...
hi,
I try to generate ad hoc reporting model for analysis services cube in reporting services.
in Management Studio I connected to reporting services, defined a data source to the analysis services database.
But If i try to generate model i get the error:
"While connection with a data source a mistake has appeared, or the query is invalid for the data source. (rsCannotPrepareQuery)(Report Services SOAP Proxy Source)"
Any ideas as to what may be the problem?
Thanks!
karaman
My problem is this: How do I dynamically generate a Report Model Definition with c#?
Is there some sort of method I could call from the ReportingService2005 web service? Or some sort of APIs I could use?
If I didn't have a dynamic database structure, I would just create a Report Model Definition with BIS and then deploy the same model to each customer. However, our product creates additional tables in the database, depending on what data users wish to collect.
There are currently 2 solutions for this problem. First, I can manually create a Report Model Definition through the Buisness Intelligence Studio (BIS). However, I wish to be able to dynamically generate the report model without having to go through BIS. Second, I could use C# and manually write the XML of the SMDL. However this seems problematic.
I'm really hoping for some MS API that I'm missing out on here. Thanks for the help.
- Sean
The Folowing code is not working anymore. (500 error)
Set objRS = strSQL1.Execute
strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID)
objRS.Open strSQL1, objConn , 2 , 3 , adCmdText
If not (objRS.BOF and objRS.EOF) Then
objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1
objRS.update
End If
objRS.Close
The .execute Method works fine
strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID)
objConn.Execute strSQL1
W2003 + IIS6.0
Pls advice?
I am using SQL 6.5. We have tables of upto 2.5GB in size. Running update statistics againts these tables takes too long, and locks out users.
Anyone know how I can make this quicker?
Davy
Hi,
I am contemplating creating a job to execute every 5 mins which will update index statisics if they are more than say 8% out. I would like to know what thoughts people have on this? i.e. pros and cons.
I like forward to what you have to say.
I have auto stats on. Our stats are often more than 10% out. At what level do you reckon the query plan might be effected by out of data stats?
Thanks
Jamie.
Is the Update Statistics found in a Maintenance Plan performed online or offline? Will it kick users out when this is run on SQL Server 2000?
Thanks
Hi,
It seems to me there are many ways to update statistics for a table. i.e. "sp_updatestats", "sp_recompile", "dbcc updateusage"
Can somebody tell me the difference between those commands and what's the best way for updating your statistics? Does reindexing update the statistics?
thx,
Wilfred van Dijk
HI,
To update statistics for entire DB i have taken the script from under given link.But need to know the
1 : what is sample percent on update statistics
2 : will it be applicable for 2005 ?
script taken from :
http://weblogs.sqlteam.com/tarad/archive/2006/08/14/11194.aspx
kindly reply soon.
How do you schedule a weekly job to do "update statistics" thru all the databases?
View 6 Replies View RelatedHi All,
I update statistics for three tables every day 2:00 AM and in the job we call
one stored procedure and, in that stored procedure only three statements are
writtern for update statistics
Like:
Exec('update statistics TBL1 with fullscan')
Exec('update statistics TBL2 with fullscan')
Exec('update statistics TBL3 with fullscan')
And this job was working fine since many months but last two days its getting fail and it gives the error messages like :
could not continue scan with NOLOCK due to data movement
So could you help me what is the solution for this
Thanks,
Aric
Hi,
I would like to know when we upgrade SQL Server 2000 database to SQL Server 2005 is it required to update the statistics even if we rebuild all the indexes or create new indexes?
Thanks,
Ritesh
Hi,
I am planning to change our current UPDATE STATISTICS strategy, which is auto stats ON. Our database is terrabytes sizes and some tables with millions of rows with over 200 indexes in one table. Some of these indexes are not really used. Most of the tables are very small.
Droping and creating new indexes are quite often used in our environment. So static script may not help.
How can I identify most frequently used indexes in a table?
With the Microsft recommended auto stats ON, what are the best other practices I can include to improve the effeciency?
Any help would be apprecited. It would be realy great if any of you can share some scripts to generates dynamic scripts.
best regards
Priw
Hello,
Can "update statistics tablename" be run while the server is in multi-user mode and users accessing the table to be updated?????
Thanks in advance...
On a SQL 7 sp 2 server, I have a database with about about 77,000 records, with automatic update statistics on inserting 1000 records took 43 minutes. With automatic update off, it took 23 minutes to insert the same 1000 records. On the same machine, I inserted 1000 records into 2 other databases with the same database structure and automatic update statistics on. On the second database, there are about 174000 records and it took 35 minutes to insert 1000 records. On the third database, there are about 93000 records and it took 19 minutes to insert 1000 records.
Why is the time so high on the first database?
What can be done to fix the problem?
Thank you.
I have a query that retrieves a single record from searching on two tables.
The statement goes like this...
select sum(amount) from Table1 A union Table2 B on a.id = b.id
where date < ### and date > #### and account = ###
As people are running a particular report, this statement is executed time and
time again to pull up the numbers necesarry for the report. When the report gets slow, I can speed it up by updating the statistics. My concern is that I'm having to update the statistcs every hour; otherwise, the query becomes slow. I have noticed that users are inserting data while users are running the report on one of the tables listed above. I'm sure that's making it become more fragmented and ultimately slowing down the query.
Do you have any suggestion on how I can make the union of these two tables faster? Or is there anything I could do to speed the query besides creating clusted indexes?
Any help would be appreciated....thank you
I am maintaining a large table with millions of rows that has two non clustered indexes and data changing frequently, I need to keep the indexes fresh. Update Statistics runs much quicker than Reindex. What is the appropriate situation for each and why?
Thanks in advance.
Hi
We are upgrading from sql 7 to 2000.During the upgrade process do we have to do a reindexing of all tables or will update statistics take care of that.
Or do we have to do both?
What is the difference between reindexing and update statistics.
Thanks
Madhukar Gole
I have recently defragged my SQL server using INDEXDEFRAG. Can somebody please tell me how to update the statistics on all the tables? Thanks in advance.
Below is the script that I executed to defrag all the tables in my database if anyone needs this.
/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 20.0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
I am looking to run UPDATE STATISTICS for the first time, don't ask why it wasn't done prior please :(, on a set of large tables in our 346gb database whcih has been being populated with transactional data for the past 4 years. The tables contain 1.2, 35, 64, and 92 million rows. I have used the STAT_DATE function to determine that none of these tables have ever had update statistics run for them.
My question is how should I go about this process and what options should I be selecting when issuing the command? I assume that I must first run with the FULLSCAN paramater in order to initially generate statistics for the table then would assume that following this initial population I could run without any paramaters nightly against the tables in the database to keep statistics up to date. Any guideance you all could provide to a newb would be greatly appreciated.
Thanks! Scott Clark
Hi Gurus,
I need you update statistics on table .
Is there any command or stored procedure for this.
Thanks,
ServerTeam
Hi all,
I'm using SQL 7, there is a setting on DB properties called "Auto update statistics", what kind of statistics does this refers to and how can this stats be accessed?
Thanks for your help!
Gustavo
Thanks,
GS
Hi. I have automatic statistic update turned on for all my databases. Isthis an overhead I can do without? Could I update them overnight when thedatabase is hardly in use?Thanks--Chris Weston
View 1 Replies View RelatedI have Auto Create Statistics and Auto Update Statistics ON in one of my databases.
How do I know what time the Auto Update Statistics Kicks on ?
I dont see it in the log.
Is there a way for me to find out when was the last time Auto Update Statistics kicked on?
TIA
I am using the Maintencance Plan wizard, but it only allows me to either select the "reorganize data and indexes" option or the "update statistics" option (in the Optimizations tab). I can't select both of them. What is the reason for this?
joe
Hi all,
Is it neccessay to run the "Update Statistics" after run "DBCC DBReindex"? Does "Update Statistics" automatically update the stats?
Thanks in advance
Kim,