I know that the query governor works off of estimated query plans, but the estimation is usually so far off from reality that it might as be generating a random number to determine whether a query should run.
Right now I have query governor set at 300. I've been able to run queries that take more than 10 minutes without any complaint from query governor.
What's really annoying though, is that it has also blocked queries that take less than a second to run. It blocked a query for having an estimated executing time of 338 seconds. When I set the limit up to 400, the query ran virtually instantly.
Is this just how query governor is suppose to work? Is there any way to make it work better?
Hello there, i'm pretty new to web development and i've come across this problem, so here's the case.I got a videoclub database and i wanna show the movies categorized by the category that the movie belongs to. I got a table with all the categories (the table includes an ID and the name of the category),and the movies table has movie data plus a category id field which is related with the categories table. Anyway enough with the background info here's the query i'm trying to use:SELECT MovieID, Name, Director, Category, Casting, Photo, Supplier, ReleaseDateFROM MoviesWHERE (Category = @ Category) The error i'm getting on the query builder is "Error in WHERE clause near '@'. Unable to parse query text.". If i aint wrong i'm supposed to use the "@" to indicate that its supposed to get data from a control. Anyway thats the prob, any help would be really apreciated.P.S. The control i'm using is a dropdownlist that shows the categories names and has as a value the category id, so there isn't a variable miss match error as much as i know.
I have enabled the query governor on our SQL2000 SP2 server with athreshold of 3600. Now, some of the maintenance jobs fail due to thelimit being to low (e.g. one of the user databases integrity checkfails nightly).I have tried to put the command 'SET QUERY_GOVERNOR_COST_LIMIT 0' justbefore the line in the step which reads 'EXECUTEmaster.dbo.xp_sqlmaint N'-Plan etc'but it has no effect.Does anyone know how to get around this situation without usingsp_configure to change the query governor settings at a systemwidelevel?GC.
I want to turn on the query governor cost limit option for 20 minutes so that queries do not run longer than 20 minutes but I could not find any info as if this option would also not allow database backup job or other maintenance jobs to run more than 20 minutes. We have backups (Via RedGate) run over 3.5 hours and others like rebuild indexes and integrity checks even more than 3.5 hours....
My table uses the identity of +1, and starts at 1 for a primary key.
These are ticket numbers, and each time a new ticket is created a number is taken, currently at around 45.
When a ticket is opened and cancelled I decide to just set a status column for that ticket number to cancelled. Therefor a ticket remains in the database that offers no use (we don't want cancelled tickets cloggin.)
using an SSIS package to delete is pretty simple, just Delete from Table Where Status = 0 (or cancelled).
Then I will have some numebrs from the past I would like to recover.
For example, my ticketnums table could read that I am using tickets 1, 4, 7, 8, 9, 11.. etc
Tickets 2,3,5,6,10 are cancelled, can I recover these ticket numebrs?
I read a lot of posts in this forum, but none of them provide a solution.
Probably it is related to SP2 as many report, I have SP2 as well...
A funny situation: I have a report parameter @date, datatype datetime, so I can use the datepicker control.
But, even when not using this @date parameter in my dataset, I still get the error:
The value provided for the report parameter 'date' is not valid for its type. (rsReportParameterTypeMismatch)
Of course it has to do with regional settings and languages etc... But at my international client's site, workstations are in any regional setting and servers are in en-US, nothing I can do about that.
Come'on Microsoft, don't tell us this datePicker is only for the US market?
First of all, please let me prefix this by saying AAARRRRRGGGGGHH!!!
Troubleshooting an SSIS package is not even close to easy. The error messages are incredibly cryptic and are anything but useful. It is absurd that for most of the errors that I get, I find myself putting the error codes into Google to find out what they mean and how to address them. Since there is indeed an known exception, why not include a "descriptive" description of the error?
Okay, enough with the venting. Sorry about that...
What I really came here to ask is this, what in the world does this error message REALLY mean? I have found nothing useful on the Internet so far. The resulting forums and blogs with the error code do not apply to what I am doing.
Error: The attempt to send a row to SQL Server failed with error code 0x80004005.
Hey guys, I'm new to reporting services and I just need to throw together a report that references multiple datasources. I can query up all my info individually, but I need to write a query that says,
Select ProductName,ProductId from datasource 2
where ProductId = @ProductIdFromDataSource1
I can get this to work, but if my query from datasource 1 returns 10 productID's, this will only return the productname from the first ProductID, 10 times. I believe that's because I'm using the First() aggregate function. Is there another function I can use that doesn't do that, or some way I can reference each field individually?
Just to clarify a little more, here's what I want:
Hate to sound so dumb, but, it's been years since I've done any SQL programming and I was bad at the time!
What is the code for taking my time stamp 2007-02-09 10:15:02.000 and just displaying the date as 02/09/2007?
Do I use variables? Are they set up before my select statement? Can anyone suggest a really good, easy, very explanable SQL (that will work in SQL Server) progamming book?
Hi, I changed the sa password today on my subscriber databases, and now my transactional replication fails, as it cannot connect to the subscribers. I updated the publisher's linked server logins, but it still fails. Help please! Thanks, Jason
Hi, I am getting many instances of the following error, and I cannot find any documention on it, or how to remedy. Any help/suggestions would be GREATLY appreciated. Thanks in advance - jason
SQL Server Assertion: File: <xcbmgr.cpp>, line=1299 Failed Assertion = 'pss->IsXcbLocked ()'. Dump thread - spid = 58, PSS = 0x2e637158, EC = 0x2e637308 Stack Dump being sent to C:MSSQL7logSQL00115.dmp ************************************************** ***************************** * * BEGIN STACK DUMP: * 10/04/00 16:39:54 spid 58 * * Input Buffer 42 bytes - * s e l e c t u i d = N E W I D ( ) * ************************************************** *****************************
Can someone help me rewrite this query? Basically I need to check if the name stored in "CustomerName" already exist in the table "Renter". If it does not then I ncan insert the new customer name into the data table "Renter". If the value in "CustomerName" already exists in the database, then I need to bypass the "INSERT" and somehow return a value indicating that the insert was not performed. How can I do this. Here is the query I currently have for performing the insert. CREATE PROCEDURE [dbo].[VacancyGet] ( @CustomerName nvarchar(100), @ClientCode nvarchar(10) ) AS INSERT INTO Renter ( CustomerName, ClientCode, ) VALUES ( @CustomerName, @ClientCode, )
Hi - does the workload governor kick in when more than 8 concurrent operations take place on MSDE as a whole, or does it kick on each database.
Ie. if I have 8 databases within MSDE - and there is 1 operation happening on each one at any given time, will one additional operation on any of the databases invoke the workload governor across ALL databases?
I am in the process of porting over an application from Access To SQL implementing SQL Server 2005 Express. My intention is to implement this database on a full time server and upgrade to a full blown version of SQL later. Am I correct in assuming that there is not limit on the number of concurrent connections to SQL Server Express since it was stated that the "Workload Governor" has been removed? Or does something else control the number of users that can be simultaneously connected to the server.
My reason for asking is I have 7 machines that need to access the server. I also have 2 databases that need to be accessed from each machine. If there is no limit, I will keep my databases seperate. However, if there is a limit, I will most likely merge the tables into 1 db.
I was looking for a little insight on Resource Governor. I have a request from my manager to limit the resources a certain third party's DB can use on our system. We allow them to keep a DB on our SQL 2012 server so they can run query's to compare data to their server without linking the two machines.
I was looking into resource manager to perhaps accomplish this task but I'm not real familiar with it. If Resource governor can be used to only allow a DB named UserDB1 to use only 1GB or Memory at any one time?
Or perhaps there is another way aside from Resource governor?
I'm working on a VB.NET project where we are intending to use MSDE as ourback-end database. The actual number of users is expected to be low and Idon't have any concerns as to whether MSDE will be up to the job (most ofthe time). I'm aware that if we end up with lots of users connecting to oursite at once (which may happen as certain times in the year when people needinformation for deadlines) then the performance governor in MSDE will kickin and slow everything down.What I don't know is how do I find out whether the performance governor haskicked in?I would like to know how to monitor this so we can make an educated decisionas to when we need to migrate to a full version of SQL Server (if at all).Can anyone point me in the right direction for finding this out?thanks,Brian Cryer.
I want to create a classifier function where I can restrict all the logins except two logins for the resource pool.
Case Scenario is : I am creating a resource pool and on that pool I want to restrict the users for that particular pool but I want to allow some of the user IDs full access to that databses so how could I create a script for that scenario.
How to restrict resources usage based on individual Databases in resource governor?
We have many databases in one instance; I would like to restrict resource usage to each database respectively.
I created 2 pools as pool_login, pool_DBNAME, and 2 workload groups as GroupLogin,GroupDBNAME, and also the classifier function.After setup above, I use following statement to check what sessions are in each group .
Even if there are spids which are accessing database DBNAME, I can’t see that they fall into the group GroupDBNAME and pool pool_DBNAME.
SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id, s.login_time, CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20)) FROM sys.dm_exec_sessions s INNER JOIN sys.dm_resource_governor_workload_groups g ON g.group_id = s.group_id ORDER BY g.name GO
Following is the code to create pool, group,classifier function:
USE master GO; -- Create a resource pool pool_login. CREATE RESOURCE POOL pool_login WITH
[Code] ....
-- Create a workload group to use this pool.
CREATE WORKLOAD GROUP GroupLogin USING pool_login; GO CREATE WORKLOAD GROUP GroupDBNAME USING pool_DBNAME;
[code]....
-- Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1); GO
-- Get the new Customer Identifier, return as OUTPUT param SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned -- users. IF @FK_UserIDList IS NOT NULL EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record -- Retrieve Address reference into @AddressId -- EXEC spInsertForUserNote -- @FK_UserID, --@NoteID, -- @BeenRead -- @Fax, -- @PKId, -- @AddressId OUTPUT
COMMIT TRANSACTION
-------------------------------------------------- GO
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1 FROM testsupplierSCNCR I am getting the result of 01/01/2007
"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I have traced this to an insert statement that executes as part of a stored procedure.
INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)
There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.
Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows. SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches FROM dbo.MAKES INNER JOIN dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC "> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" /> <asp:Parameter Name="ATT_ID2" /> <asp:Parameter Name="VAL2" /> <asp:Parameter Name="ATT_ID3" /> <asp:Parameter Name="VAL3" /> <asp:Parameter Name="ATT_ID4" /> <asp:Parameter Name="VAL4" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>" SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource> <br /> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID"> </asp:DropDownList> <asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br /> <br /> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False" ReadOnly="True" SortExpression="DERIVATIVE_ID" /> <asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" /> </Columns> </asp:GridView> </asp:Content> AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback). Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage. Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.
Thanks in advance
Daniel Buchanan.
If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.
We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in ** =================================== Sequence contains no elements ------------------------------ Program Location: at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke() at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120)) insert into #temptable SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10 --column name declare @cname varchar(30)
-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!! -- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"
set nocount on go if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1 if object_id('tempdb.dbo
[code]....
This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like
SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name FROM... WHERE ... and calcd name in (@variable)
