Please advise.
Whilst running a query I recieved the error below.
Cannot create a worktable row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint.
What is ROBUST PLAN hint?.
Help Appreciated.
My experience with query hints are that they are just that, a hint. What I don't understand is when does SQL decide to ignore your hint?
I am trying to use hints and they don't seem to be working.
Something like:
select * from TABLE_NAME with (tablock,xlock)
fails.
Documentation clearly states that this is supported (TABLOCK, NOLOCK, XLOCK, etc.), so I must be missing something simple.
Please let me know how do I use Hints in SQL Server 2005 Compact.
Thanks.
PS.
I am using C#. It doesn't work in my code when I use
SqlCeDataReader rs = cmd.ExecuteReader();
And it doesn't work from inside the VS 2005 "query tool" or whatever it is called, when you do "Connect to Database" and so on.
Do you know of a way, in the OLE DB Command (Executing a sql command for each record), to specify a lock hint?
Example: in my component, I want to update a table but I want to specify a ROWLOCK or UPDLOCK hint.
When I try using this sql statement in the 'SqlCommand' property:
UPDATE DIM_Accounts WITH (UPDLOCK)
SET COL1 = ''
SSIS gives a syntax error.
Do you know any workarounds?
Thanks!
Greetings,
I have two tables:
CustomerOrder
----
ID
CustomerID
StatusID
CustomerOrderDetail
----
ID
Order_ID
StockID
Quantity
CustomerOrderDetail table has clustered unique index for ID and non-clustered for Order_ID
SQL Server 2005 is using table scan for CustomerOrderDetail table When I user the following query:
select
cod.*
from CustomerOrder co
inner join CustomerOrderDetail cod ON cod.Order_ID = co.ID
where
co.StatusID = 8 -- Pending
Both of the tables are pretty big, detail table has more than million records, so scanning the table is a bad idea.
When I specify hint to use index then sql seeks, but how do I make SQL server to use index automatically? I don't want to use hints in my queries.
Thanks!
Is there anything equivalent available in SQL Server for Oracle HINTS ?
eg : Oracle query
select /* + INdex(sno index1) */ sno from test_table
Thanks,
Sam
I am kind of confused about the way SQL Server 2000 handles the hintsthat users supply with their SQL statements.[color=blue]>From BOL, it seems that one can specify them with "WITH (...)" clauses[/color]in SQL statements known as table hints. Sometimes, multiple uses ofthis form in a statement is OK. Then there is the OPTION clause forspecifying statement hints. However, the documentation on OPTIONsection discourages their use.Being relatively new to SQL Server and still learning about it, what isthe general practice? Use hints or not? And if so, how (through WITHor OPTION clauses)?Cheers!
View 5 Replies View RelatedI am running SQL7 SP2 and and noticing table the query processor table scans when I ussue a between 'date1' and 'date2' instead of using the datetime index. If I put in the index hint (index = ix_datetimeXXXX) the query runs fine. My question is does this index hint restict the use of other indexes in the query and secondly how can I specify multiple index hints? Thanks in advance.
View 1 Replies View RelatedHi
My VB.Net (with SQL Server backend) application currently allows more than one user to look at a particular record at the same time. This is not a problem unless both those users also try to update that record as well. One user's changes then overwrite the other's.
I've been reading up on locking hints but my database knowledge is a little scant and I'm also rather dense and need things spelling out for me!! So I have a few questions that I hope someone can help with:
If I add an updlock to my update SQL statement, this would allow both users to view the record but would only allow one user's changes through. Is that correct?
For the other user, would SQL Server return an error message that I can use to tell the user that their update has not worked?
Would I have to get my VB.NET application to re-get the record information so that the user who's update failed can see the changes made by the other user and reapply their own changes?
Does the updlock become unlocked once the record is updated or do I need to specifically unlock it somehow?
Thanks!
I am having problems with doing what seams to be a very easy query. For some reason the SQL Server is trying to do nested loops instead of hash join. I tried to force the use of the hash join using the join hint.
Query 1
select *
from DIM_DATE DD
inner hash join (
select A.student_key,
CONVERT(int, CONVERT(varchar, COALESCE (A.date_withdrawn, getdate()), 112)) AS date_withdrawn_current
FROM FACT_STUDENT AS A
) SSE on DD.date_key= date_withdrawn_current
This query gives an error:
Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN
Second query is not really what I want but it illustrate that it works fine when getdate() is not used.
Query 2
select *
from DIM_DATE DD
inner hash join (
select A.student_key,
CONVERT(int, CONVERT(varchar, COALESCE (A.date_withdrawn, A.date_enrolled), 112)) AS date_withdrawn_current
FROM FACT_STUDENT AS A
) SSE on DD.date_key= date_withdrawn_current
Is there some problem with using function getdate() ? It works fine in SQL Server 2000
This problem occurs on the SQL Server 2005 SP2 ( 9.00.3050.00 (X64) )
and (9.00.2050)
I noticed that the online books say the following:
Note The SQL Server query optimizer automatically makes the correct determination. It is recommended that table-level locking hints be used to change the default locking behavior only when necessary.
Also, at another place in online books, it says:
The table hints are ignored if the table is not accessed by the query plan.
From the above, it seems that using locking hints is not going to guarantee that SQL Server will follow them. Is this true?
Hi,
Why SQL server dose work as follows when I dose not provide any join hints?
It looks like HASH join is the best plan, but SQL dose not. What kind of
JOIN method is used by SQL optimizer?
Thanks in advance,
Wonhyuk William Chung
wonhyukc@usa.net
MCSE/ MCT
-----------
use northwind
go
select orderid, CompanyName --productname,
from orders o inner join customers c on o.customerID = c.CustomerID
/*
Table `Orders`. Scan count 91, logical reads 184, physical reads 0,
read-ahead reads 0.
Table `Customers`. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
.0553
*/
select orderid, CompanyName --productname,
from orders o inner hash join customers c on o.customerID = c.CustomerID
/*
hash
Table `Customers`. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table `Orders`. Scan count 1, logical reads 4, physical reads 0, read-ahead
reads 0.
.115
*/
select orderid, CompanyName --productname,
from orders o inner merge join customers c on o.customerID = c.CustomerID
/*
merge
Table `Customers`. Scan count 1, logical reads 4, physical reads 1,
read-ahead reads 3.
Table `Orders`. Scan count 1, logical reads 4, physical reads 0, read-ahead
reads 0.
.115
*/
select orderid, CompanyName --productname,
from orders o inner loop join customers c on o.customerID = c.CustomerID
/*
loop
Table `Customers`. Scan count 830, logical reads 1681, physical reads 0,
read-ahead reads 0.
Table `Orders`. Scan count 1, logical reads 5, physical reads 0, read-ahead
reads 0.
.116
*/
I need to use two hints (INDEX=indexname) and (NOLOCK).
I've tried
(INDEX=indexname),(NOLOCK)
(INDEX=indexname,NOLOCK)
(INDEX=indexname)(NOLOCK)
and nothing works.
Thanks
While investigating performance problems within an application recently I carried out some tests using SET SHOWPLAN ON.
I had a query like this within a stored procedure:
SELECT MAX(X) FROM Y WHERE Z LIKE @MYVAR
Where @MYVAR was passed in. I discovered that SQL Server did a Table Scan even when Z had an index on it. A problem with 200,000 rows!
If I said
SELECT MAX(X) FROM Y WHERE Z LIKE 'HELLO%'
(i.e., used a constant instead of a variable) SQL Server did use the index correctly and did not do a table scan.
I got around this by rewriting my statement:
SELECT MAX(X) FROM Y (INDEX=MYINDEX) WHERE Z LIKE @MYVAR
in other words by manually specifying the index I had created on the Z column.
Hope this helps someone.
hi all,
I'm trying to run queries on relatively small tables (a few hundred thousand rows) with subqueries of counts per primary key columns as such:
(ColA in tableA is the primary key)
select * from tableA p
where exists (select 1 from ( select ColA, count(1) cnt
from TableA
group by ColA
having count(1)>1 ) t
where t.ColA= p.ColA)
order by some_col
my problem is that sqlserver 2005 sp5 does not materialize the internal subquery properly, or execute it beforehand and it gets confused as heck and pegs the CPUs at 100% forever.
What hints can I use to solve this issue?
I've tried to use ..... "with ...." to prepare/materialize the table upfront, no luck, one version of statement pegged one cpu at 100%, while the other statement pegged ALL cpu's at 100% -- don't remember which.
My only solution right now was to create these subqueries as PHYSICAL tables -- and this would solve the problem but that would entail creating a lot of un-necessary objects.
thanks much for any feedback!!
Cos
Hello.
There is a trace flag that tells SQL Server to ignore index hinting in incoming queries. I'm having a Monday morning problem and I can't remember the trace number nor find it in my notes. Can anyone else come up with it?
Thanks in advance,
-darin
have a dts package that does txt -> sql server.
i have 200 txt files with the same exact format.
just want to know if i can write a SP passing a parameter that loads this txt files. because i dont wanna create 200 packages or 200 sources to load 200 txt files.
say:
exec SP_loadTXT txt1
or should i use bulk insert?
any approaches are fine. any suggestions are fine too.
I've got a SELECT WITH (UPDLOCK, ROWLOCK) WHERE followed by an UPDATE WHERE statement. The results of the SELECT statement are deserialized in C# and updates are made to the deserialized object. Then the object is serialized back into the table with the UPDATE statement. I've got this code running within a transaction scope with the ReadCommited isolation level.
My service receives requests to update data and the requests can come in on different threads. What I'm seeing, is that once in a while, the log messages from my application indicate that two different threads are able to issue the above SELECT statement and both are receiving results. This is a problem since the thread that issues the last UPDATE will overwrite the changes made by the first. Each thread has its own connection and transaction scope.
I've researched all over the place and have tried a few different things, but all things point to the fact that query hints are just hints and that SQL may or may not pay attention to them. If that's the case, how am I suppose to perform a SELECT with the intention of updating so that no one else can do the same? I haven't tried table level locking, but I'd really like to avoid that if possible.
-Mike
Hi,
Using Merge replication, I have a table that is filtered using the HOST_NAME() function. The filter also makes use of a function (as the HOST_NAME() is overriden to return some complex data).
Everything replicates and filters just fine. but when I add a join filter on a different table (the join filter is a simple foreign key join) I get the following error when the snapshot agent is run:
Message: Conflicting locking hints are specified for table "fn_GetIDList". This may be caused by a conflicting hint specified for a view.
Command Text: sp_MSsetup_partition_groups
Parameters: @publication = test1
fn_GetIDList is the function used in the original filter.
Thanks for any help
Graham
Hi,
I would like to implement a kind of standard packages which can be used in all other processes and will be started using the variables.
But I do not know where to store these kind of packages in "best practise", because we
- would like to use them in Dev and in "Real" also without having to change something in the other processes
- we are storing the packages in the folders of the package store
and as far as I understood I would have to share the package store to all developers though that they would be able to do this?
Then I would better choose another folder with defined access rights I think...
Or would it be better to spend some time in developing a custom component?
But this component would work with recordsets rather than the standard data flow elemtents and therefor I would expect a leak of performance...
Or is it possible to do "trasnformation" from a packae to a custom component?
Thanks in advice!
cheers,
Markus
This article instructed me on how to process rows from a table used as a data queue for multiple processes.
http://www.mssqltips.com/tip.asp?tip=1257
I tested this against the AdventureWorks DB (SQL 2005) and multiple SQL connections inside of Sql Mgmt. Studio).
Connection1:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) --skips over locked rows
--COMMIT TRANSACTION
Connection2:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) --skips over locked rows
COMMIT TRANSACTION
This works like I want where connection 2 skips over the locked row from connection 1 and gets the next available record from the table / queue. However, when I add ORDER BY tsql to each sql statement, connection 2 is now blocked waiting for Connection 1 to commit. (This is not what I want)
Connection1:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) order by DueDate
--COMMIT TRANSACTION
Connection2:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) order by DueDate --is blocked until connection 1 commits transaction
COMMIT TRANSACTION
How do I prevent blocking when using these locking hints with ORDER BY?
thanks
I'm running a query, actually its an insert that works when using the TSQL below.
However when I try to use the debugger to step through and using the exact same values as those below I get the following error:
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
Its Killing me because everything else works, but this. Can somebody help.
DECLARE @NoteID INT,-- NULL OUTPUT,
@Note_Description NVARCHAR(3000),-- = NULL,
@Date DateTime,-- = NULL OUTPUT,
@ByWho NVARCHAR(30),-- = NULL,
@FK_Action_Performed NVARCHAR(40),-- = NULL,
@FK_UserID INT,-- = NULL,
@FK_JobID INT,-- = NULL,
@Job_Date DateTime,-- = NULL,
@Start DateTime,-- = NULL,
@Finish DateTime,-- = NULL,
@BeenRead NVARCHAR(10),-- = NULL
@FK_UserIDList NVARCHAR(4000)-- = NULL
--SET @NoteID = 409 --NULL OUTPUT,
SET @Note_Description = 'Tetsing'
--SET @Date DateTime = NULL OUTPUT,
SET @ByWho = 'GeorgeAgaian'
SET @FK_Action_Performed = 'Worked hard'
SET @FK_UserID = 5
SET @FK_JobID = 29
SET @Job_Date = 28/01/03
SET @Start = '1:00:20 PM'
SET @Finish = '1:00:20 PM'
SET @BeenRead = 'UnRead'
SET @FK_UserIDList = '1,2,3'
--AS
--SET NOCOUNT ON
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRANSACTION
SET @Date = GETDATE()
-- Insert Values into the customer table
INSERT Note (Note_Description,
Date,
ByWho,
FK_Action_Performed,
FK_UserID,
FK_JobID,
Job_Date,
Start,
Finish)
SELECT --@NoteID,
@Note_Description,
@Date,
@ByWho,
@FK_Action_Performed,
@FK_UserID,
@FK_JobID,
@Job_Date,
@Start,
@Finish
-- 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
but in query analizer I get the result of
12/31/2006
Why the different dates
Hi,
I get this error dialog when I try to open all the rows of any table from Enterprise manager..
Any help would be really appreciated..
Thanks,
-Srini.
SQL Server 2005 9.0.3161 on Win 2k3 R2
I receive the following error:
"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)
VALUES (@journal_id, @acct_id, @acct_recv_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)
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
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)
[code]....
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)
[code]...
-- 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....?
Hello everybody,
I'm developing a report using the following structure :
declare @sql as nvarchar(4000)
declare @where as nvarchar(2000)
set @sql = 'select ....'
If <conditional1>
begin
set @where = 'some where'
end
If <conditional2>
begin
set @where = 'some where'
end
set @sql = @sql + @where
exec(@sql)
I run it in query analyser and works fine, but when I try to run in Reporting Services, Visual studio stops responding and the cpu reaches 100 %.
I realize that when I cut off the if clauses, then it works at Reporting services.
Does anybody know what is happening?
Why the query works in query analyser and doesn't work in Reporting Service ?
Thanks,
Maurício
SQL Server 2012 Performance Dashboard Main advices me this:
Since the application is from a vendor and I have no control over its code, how can improve this sitation?