Bizarre Query
Jul 5, 2006
Good afternoon,
I have a bizarre question. When running the following query:
select SomeColumnName from TableA where PK_TableA in
(select PK_TableA from TableB)
I get results. This should not be feasible, because the query within
the in clause:
select PK_TableA from TableB
is not possible - there is no PK_TableA column within the TableB table.
Running the sub-query alone gives an error, but when using it as
sub-query in the first statement, I get every row within TableA.
If it helps any, the exact query I'm running is:
select demonstratorid from DirectSalesAgent where DirectSalesAgentId in
(select directsalesagentid from WebsiteSubscriptionPayment)
Shouldn't a query return an error if the sub-query has an invalid
column name?
View 1 Replies
ADVERTISEMENT
Jan 11, 2007
I previously posted about a problem where it seemed that changing thecase of the word "BY" in a SELECT query was causing it to run much muchfaster.Now I've hit the same thing again, where basically almost any change Imake to how the query is executed (so that it still performs the samefunction) causes the performance to jump from a dismal 7 or 8 secondsto instantaneous. It's a very simple query of the form:SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0which was running fine until a moment ago, when it suddently startedrunning hopelessly slowly. If change anything in the query tolowercase (or the Min to uppercase), it runs fine again. Last timesomeone suggested something about a bad plan being cached, and after abit of research I found the commands DBCC DROPCLEANBUFFERS and DBCCFREEPROCCACHE. Sure enough, after running these, the query startedrunning fine again. The question isa) why is this happening? Is it a bug in my code, or in SQL server?b) is it worth detecting it and fixing it automatically? I.e, should Iput some code in that notices that a query is running far too slowly,then runs "DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE"? Or will thatcause other problems?Thanks
View 29 Replies
View Related
Jul 23, 2005
I'm doing a select which includes the following:casewhen (rtrim(ltrim(T464.COMMENT_4)) = '' or T464.COMMENT_4 is null)then ''elseconvert(datetime,left(replace(replace(T464.COMMENT _4,' QTR: ',''),' -',''),10))end as QuarterStartDateThe COMMENT_4 field is a char(51), and contains values like: ' QTR:03/01/2005 - 05/31/2005', '', a number of spaces, or NULL. If I removethe convert to datetime within the case, data returns as expected,retaining NULLs and blanks. If I leave the convert in, it convertsevery row to datetime and seems to disregard the case expression.I've attempted converting COMMENT_4 to a varchar first, and that didn'thelp either. Also this does not seem to be affected by removal of theLTRIM and RTRIM. Googling for this turns up no similar results. HaveI simply nested too far within the case, or what?Any and all help and advice would be greatly appreciated.Thanks!
View 2 Replies
View Related
Feb 19, 2008
Running into a very bizarre problem here. I'm querying a couple of tables based on a date range. Nothing fancy; simply stuff. The query runs fine when I hard-code the dates, but when I use variables, the query just hangs.
This works fine:
SELECT AVG(DATEDIFF(ss, B.SomeDate, C.SomeDate))
FROM A WITH(NOLOCK)
INNER JOIN B WITH(NOLOCK) ON A.BID = B.BID
INNER JOIN C WITH(NOLOCK) ON A.CID = C.CID
WHERE A.SomeDate>='2008-02-09 00:00:00.000' AND A.SomeDate<'2008-02-09 01:00:00.000'
AND DATEDIFF(ss, B.SomeDate, C.SomeDate)<100000
This does NOT: NO clue why :confused:
DECLARE @FromDate DATETIME
, @ToDate DATETIME
SELECT @FromDate = '2008-02-09 00:00:00.000'
SELECT @ToDate = '2008-02-09 01:00:00.000'
SELECT AVG(DATEDIFF(ss, B.SomeDate, C.SomeDate))
FROM A WITH(NOLOCK)
INNER JOIN B WITH(NOLOCK) ON A.BID = B.BID
INNER JOIN C WITH(NOLOCK) ON A.CID = C.CID
WHERE A.SomeDate>=@FromDate AND A.SomeDate<@ToDate
AND DATEDIFF(ss, B.SomeDate, C.SomeDate)<100000
Any ideas?
-a
View 5 Replies
View Related
Feb 5, 2008
sql2k5
sp2
We moved a very tiny db (< 100 mb) from 2000 to 2005 (via attach/ detach) recently and now there are a couple sprocs which take forever to run from the app. Yes, the stats have since been updated as I reorg the indexes weekly. Now here is where it gets really strange, I can run this sproc in 0 seconds via Query Analyzer (QA). In other words I:
Have a developer run it from the app while monitoring in Profiler. There is a setting in the app for a 30 timeout, so after 30 seconds the app blows up and I see the exact code that the developer tried to run.
I can grab that code from Profiler, plop it into QA, and run it. Results are returned instantly. We have done this test a number of times, so I know it's not just a one time thing.
How can this be? The only thing that got changed in the app was the connection string to the new 2005 server.
View 9 Replies
View Related
Jan 22, 2008
Background: We have an Asp.Net page where you enter a serial number (for a part my company manufactures), and it performs some updates to the data. Before it does, it does some simple selects for validation.Problem: Testers reported that the page will work fine for a while (5 minutes?), but then suddenly start throwing command timeout exceptions. I discovered that the timeouts always occur at the same validation query. Other queries are executed before this one, and they never timeout. The query is nothing fancy. It joins five tables, has a simple where clause, and selects a count of the records. It runs in Management Studio in under one second. The system is in a development environment under very little load. There's never a valid reason for the timeout. The where clause is parameterized, and the query itself is stored in our data access layer as a string. We execute it using SqlHelper's ExecuteScalar method. There's truly nothing remarkable about the query or how we execute it.After some playing with the page for a bit, and getting lots of successes, I'll get the timeout myself, and it will continue to occur for each additional attempt. If I let the system "rest" for a period of time (a couple of hours), it will be working again.The fun bit: When the system is in "timeout mode", if I go into our data access layer and add a single space character anywhere in the command text (where it has no semantic meaning) the timeouts go away. Take the extra space out, timeout again. Put it back in, no timeout. I did this (jaw agape) for a dozen times and the result was always the same!In previous attempts at debugging this problem, it would be in "timeout mode" and I'd make a semantic change to the query (hoping to fix it) and the problem would go away. I'd tell the testers it was fixed just to have them come back a day later and say it's not. That's what made me finally try the whitespace idea. Changing the string itself made the problem go away temporarily. What I do understand about this:SQL Server will cache the execution plan of a text query, and re-use it on successive queries. It probably caches it based on some hash of the query (just a guess), and changing one character would cause the hashing function to generate a different hash, hence it's a "different" query to SQL Server. So it won't use the same cached execution plan, and will make a new one. If the original execution plan was somehow borked, then that would explain why the timeouts return once I put the query back to it's exact original form. ('Cause it will use that same broken execution plan). This would also explain why letting the system rest would alleviate the problem. I imagine the execution plan eventually expires from the cache.One more thing: If I stop using SqlHelper, and contruct the SqlCommand myself, I can specify a command timeout. If I set it really high, it will eventually complete the query. It will just never do it within the default command timeout period. What exactly is going on is a mystery to me. I thought of a fix: Make the query non-constant and append a random comment at the end to make it appear different each time to SQL Server! LOL! If it came to that, I would immediately submit my own code to TheDailyWTF.com. :D
View 10 Replies
View Related
Jul 23, 2005
I am getting a failure on the db backup job of one of my maintenanceplans. It is coming back with the generic error message of,"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The stepfailed."I then checked the Database Maintenance Plan History page, but thisshows all the steps having run successfully. If I check the drives forthe actual backup files, they exist and look healthy too!There is plenty of space on the drives, so it is not that.I've checked the NT logs and all they say is,"SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DBMaintenance Plan for All User Databases''(0xC06E15E2A9E1414087BE19541D167861) - Status: Failed - Invoked on:2005-06-29 21:00:04 - Message: The job failed. The Job was invoked bySchedule 35 (Schedule 1). The last step to run was step 1 (Step 1). "Which doesn't give me any clues.Since the backups have actually run to success, I am going to take offthe option on the maintenance plan to "Verfiy the integrity of thebackup upon completion". Maybe it is this that is causing problems,rather than the backup?Anyone had anything similar?
View 4 Replies
View Related
Mar 1, 2006
I have a maintenance plan that consists of several parts. It basically backs up all the databases, deletes old backups and then shrinks the databases.
The odd thing is that it appears to back up all the databases, can't tell if it does step 2 or 3 and then it fails with the errors below.
How do i correct this short of throwing it out and starting from scratch?
This is a package originally from one server that i'm trying to deploy to a 2nd server. Already using configuration files to chagne the target connections etc.
thx.
PackageStart,WSWT4361,NT AUTHORITYSYSTEM,ProdBackupPlan,{645B67A9-0377-4462-BE81-755D4B1CE9DD},{AB598508-1858-41BC-8844-CA793A7861D4},2/28/2006 5:09:16 PM,2/28/2006 5:09:16 PM,0,0x,Beginning of package execution.
OnError,WSWT4361,NT AUTHORITYSYSTEM,{56B53144-6DA7-4276-B37B-A09B1254DD3C},{56B53144-6DA7-4276-B37B-A09B1254DD3C},{AB598508-1858-41BC-8844-CA793A7861D4},2/28/2006 5:09:17 PM,2/28/2006 5:09:17 PM,-1073548784,0x,Executing the query "DECLARE @Guid UNIQUEIDENTIFIER
EXECUTE msdb..sp_maintplan_open_logentry '{645B67A9-0377-4462-BE81-755D4B1CE9DD}', '{92E2538E-BED5-4935-897A-AB6ACAEC373A}',NULL, @Guid OUTPUT
Select CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
OnError,WSWT4361,NT AUTHORITYSYSTEM,OnPreExecute,{B88BD5B5-138F-4024-A2A5-D60403296701},{AB598508-1858-41BC-8844-CA793A7861D4},2/28/2006 5:09:17 PM,2/28/2006 5:09:17 PM,-1073548784,0x,Executing the query "DECLARE @Guid UNIQUEIDENTIFIER
EXECUTE msdb..sp_maintplan_open_logentry '{645B67A9-0377-4462-BE81-755D4B1CE9DD}', '{92E2538E-BED5-4935-897A-AB6ACAEC373A}',NULL, @Guid OUTPUT
Select CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
OnError,WSWT4361,NT AUTHORITYSYSTEM,ProdBackupPlan,{645B67A9-0377-4462-BE81-755D4B1CE9DD},{AB598508-1858-41BC-8844-CA793A7861D4},2/28/2006 5:09:17 PM,2/28/2006 5:09:17 PM,-1073548784,0x,Executing the query "DECLARE @Guid UNIQUEIDENTIFIER
EXECUTE msdb..sp_maintplan_open_logentry '{645B67A9-0377-4462-BE81-755D4B1CE9DD}', '{92E2538E-BED5-4935-897A-AB6ACAEC373A}',NULL, @Guid OUTPUT
Select CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
View 2 Replies
View Related
Feb 5, 2008
Good afternoon,
3 of my fellow employees and I am having some strange issues with SQL Server...
When we are connected to the local network, we are unable to log into our SQL Server Instances locally through SSMS or programs that use SQL Server unless we have our firewalls "default"(no exceptions). Of course, this is useless if we need to make client connections from other computers. So, if we add an exception, say "port 1433", the client CAN connect, but we can no longer connect locally...to ANY instances.
We have tried adding exceptions to the firewall for all SQL ports and programs. We've also made sure we've got the latest hotfixes and service packs. If I log in as the local computer admin and do not connect to the domain and turn off my firewall, everything works fine.
1. Tried enabling Remote Connections
2. Allowed multiple ports through firewall
3. Allowed multiple programs through firewall
4. Re-installed SQL
It's almost as if something on the local network or domain is causing an issue with our firewall.....
Anyone have any ideas?
Thanks,
View 2 Replies
View Related
Apr 18, 2008
I have a controller package that coordinates the execution of a few child packages. The controller is responsible for logging errors/events to a logging database: each event hander has a corresponding Execute SQL Task that executes an stored proc in the logging database.
The ServerName of the logging database connection is set by a package configuration environment variable (ENV_DB_SERVER).
The controller runs seemlessly on development. However, when we move it to UAT, we get this strange, and truncated, error:
Log Job History (ORD_Daily_CMIDW1_Load)
Step ID 1
Server NAMCFMSSDDB602
Job Name ORD_Daily_CMIDW1_Load
Step Name Controller CMIDW1 - Daily Load
Duration 00:00:12
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: RELXXXX. ... 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:25:46 PM Error: 2008-04-14 13:25:57.97 Code: 0x00000000 Source: Log Package error of Job Description: String or binary data would be truncated. End Error Error: 2008-04-14 13:25:58.03 Code: 0xC002F210 Source: Log Package error of Job Execute SQL Task Description: Executing the query "EXEC usp_Log_Event @App_ID = 'ORD', @Comp_ID = 'DailyLoad-CMIDW1', @Comp_Start_Dt = '4/14/2008 1:25:57 PM', @Task_Start_Dt = '2008-04-14 13:25:57.958', @Task = 'Log Package start', @Status = 'Error', @Msg = '0: String or binary data would be truncated.', @Node = 'xxxxxx', @Executor = 'RELXXXXX" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connecti... The package execution fa... The step failed.
If you look at the stored procedure call, it has the message "0: String or binary data would be truncated." This is the message that should be logged to the logging db. However, the call itself appears to fail with "String or binary data would be truncated." Yet when we run the exact sp call, taken from the above error, against the db, it executes fine.
So we thought maybe when we moved to UAT the environment variable wasn't set properly and therefore the sp execution would fail.
Well, I tested that out on dev by changing the environment variable to trash and got this error (which is what one would expect):
Message
Executed as user: RELXXXXX. ...o acquire connection "conn ALERTS". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Error: 2008-04-15 15:06:52.92 Code: 0xC00291EC Source: Log Package start Execute SQL Task Description: Failed to acquire connection "conn ALERTS". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Error: 2008-04-15 15:07:09.24 Code: 0xC0202009 Source: Controller CITILINK - Daily Load Connection manager "conn ALERTS" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "An error has occurred while establishing a connection to the . The step failed.
So it would seem that the UAT deployment is connecting to the logging database just fine, but is generating some kind of truncation error that seems impossible to reproduce on dev. We have tried everything we can to reverse-engineer the error on dev...
Anyone? There are bits of brick stuck to my forehead... =) Thanks a lot.
View 5 Replies
View Related
Oct 31, 2007
Hi
I have an excel file with 2 rows that I am importing into a staging table. The format on the excel sheet is a custom one which is DD-MMM-YY so in the actual cell the data appears as 01-NOV-07 and 30-OCT-07. The format of the data in the input window above the spreadsheet is 01/11/2007 and 30/10/2007.
The format in the Excel source of the data flow task is Unicode String [DT_WSTR] length 255. I then have a data conversion step that changes this to a string [DT_STR] length 255 as the staging table is non unicode. I then have a derived column function that simply does a ISNULL replace on NULL values to blank. Finally there is an OLE DB destination which is the staging table.
This is where the problem occurs. If I breakpoint the package here and look at the staging table the dates now read as 11/1/2007 and 10/30/2007. The data type in the staging table is varchar 255. I am not doing any other transformations or T-SQL stuff on the data - it is being flipped to MM/DD/YYYY during the import.
By the way I am english hence I need the english variation on the date. I know i could put some T-SQL in to flip it around to english again but wondered why it was happening like this
thanks in advance
View 1 Replies
View Related
Dec 19, 2003
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
View 1 Replies
View Related
May 28, 2008
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
View 4 Replies
View Related
Jan 22, 2001
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.
View 1 Replies
View Related
May 24, 2007
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).
View 5 Replies
View Related
Mar 28, 2007
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)
View 4 Replies
View Related
Aug 5, 2014
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.
View 2 Replies
View Related
Jun 15, 2007
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.
View 1 Replies
View Related
Jul 19, 2015
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]....
View 3 Replies
View Related
Jun 26, 2015
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]...
View 4 Replies
View Related
Sep 22, 2015
-- 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....?
View 2 Replies
View Related
Apr 30, 2007
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
View 2 Replies
View Related
Oct 6, 2015
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?
View 3 Replies
View Related
Jul 30, 2015
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.
I want to do it using SELECT. Is it possible?
View 13 Replies
View Related
Nov 15, 2007
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)
View 1 Replies
View Related
Oct 30, 2015
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
View 0 Replies
View Related
Mar 25, 2008
Hi all,
In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql:
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SalesByCategory]
@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'
BEGIN
SELECT @OrdYear = '1998'
END
SELECT ProductName,
TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Diagnostics
Public Class ConnectionPoolingForm
Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
'Force app to be available for SqlClient perf counting
Using cn As New SqlConnection()
End Using
InitializeMinSize()
InitializePerfCounters()
End Sub
Sub InitializeMinSize()
Me.MinimumSize = Me.Size
End Sub
Dim _SelectedConnection As DbConnection = Nothing
Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged
_SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)
EnableOrDisableButtons(_SelectedConnection)
End Sub
Sub DisableAllButtons()
btnAdd.Enabled = False
btnOpen.Enabled = False
btnQuery.Enabled = False
btnClose.Enabled = False
btnRemove.Enabled = False
btnClearPool.Enabled = False
btnClearAllPools.Enabled = False
End Sub
Sub EnableOrDisableButtons(ByVal cn As DbConnection)
btnAdd.Enabled = True
If cn Is Nothing Then
btnOpen.Enabled = False
btnQuery.Enabled = False
btnClose.Enabled = False
btnRemove.Enabled = False
btnClearPool.Enabled = False
Else
Dim connectionState As ConnectionState = cn.State
btnOpen.Enabled = (connectionState = connectionState.Closed)
btnQuery.Enabled = (connectionState = connectionState.Open)
btnClose.Enabled = btnQuery.Enabled
btnRemove.Enabled = True
If Not (TryCast(cn, SqlConnection) Is Nothing) Then
btnClearPool.Enabled = True
End If
End If
btnClearAllPools.Enabled = True
End Sub
Sub StartWaitUI()
Me.Cursor = Cursors.WaitCursor
DisableAllButtons()
End Sub
Sub EndWaitUI()
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
End Sub
Sub SetStatus(ByVal NewStatus As String)
RefreshPerfCounters()
Me.statusStrip.Items(0).Text = NewStatus
End Sub
Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click
Dim strConn As String = txtConnectionString.Text
Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()
Try
bldr.ConnectionString = strConn
Catch ex As Exception
MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)
Return
End Try
Dim dlg As New ConnectionStringBuilderDialog()
If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then
txtConnectionString.Text = dlg.ConnectionString
SetStatus("Ready")
Else
SetStatus("Operation cancelled")
End If
End Sub
Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
Dim blnError As Boolean = False
Dim strErrorMessage As String = ""
Dim strErrorCaption As String = "Connection attempt failed"
StartWaitUI()
Try
Dim cn As DbConnection = _ProviderFactory.CreateConnection()
cn.ConnectionString = txtConnectionString.Text
cn.Open()
lstConnections.SelectedIndex = lstConnections.Items.Add(cn)
Catch ex As Exception
blnError = True
strErrorMessage = ex.Message
End Try
EndWaitUI()
If blnError Then
SetStatus(strErrorCaption)
MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
SetStatus("Connection opened succesfully")
End If
End Sub
Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click
StartWaitUI()
Try
_SelectedConnection.Open()
EnableOrDisableButtons(_SelectedConnection)
SetStatus("Connection opened succesfully")
EndWaitUI()
Catch ex As Exception
EndWaitUI()
Dim strErrorCaption As String = "Connection attempt failed"
SetStatus(strErrorCaption)
MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click
Dim queryDialog As New QueryDialog()
If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Me.Cursor = Cursors.WaitCursor
DisableAllButtons()
Try
Dim cmd As DbCommand = _SelectedConnection.CreateCommand()
cmd.CommandText = queryDialog.txtQuery.Text
Using rdr As DbDataReader = cmd.ExecuteReader()
If rdr.HasRows Then
Dim resultsForm As New QueryResultsForm()
resultsForm.ShowResults(cmd.CommandText, rdr)
SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))
Else
SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))
End If
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
End Using
Catch ex As Exception
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
Dim strErrorCaption As String = "Query attempt failed"
SetStatus(strErrorCaption)
MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Else
SetStatus("Operation cancelled")
End If
End Sub
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the code successfully and I got a box which asked for "Enter the query string".
I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.
Thanks in advance,
Scott Chang
View 4 Replies
View Related
Nov 7, 2007
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID
FROM tableU u
LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
View 1 Replies
View Related
Feb 12, 2008
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId
View 3 Replies
View Related
Jan 7, 2002
I am trying to run queries against any of the user tables in my MS SQL 7.0 database. I get a message the Query Designer encountered a query error.
We have tried rebooting the SQL Server and I am still getting these messages. Also, the SQL error logs look fine - all database
maintenance are running successfully including the DBCCs which show no errors. Any help would be greatly appreciated as we are to go
into production in a few days.
View 2 Replies
View Related
Nov 23, 2004
How to optimize the following Stored procedure running on MSSQL server 2000 sp4 :
CREATE PROCEDURE proc1
@Franchise ObjectId
, @dtmStart DATETIME
, @dtmEnd DATETIME
AS
BEGIN
SET NOCOUNT ON
SELECT p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec
, count(1) "Count"
, sum(Amount) "Total"
FROM tb_Event t
JOIN tb_Prod p
ON ( t.ProdId = p.ProdId )
JOIN tb_ACDef a
ON ( t.ACDefId = a.ACDefId )
JOIN tb_Curr c
ON ( t.CurrId = c.CurrId )
JOIN tb_Event e
ON ( t.EventId = e.EventId )
JOIN tb_Setl s
ON ( s.BUId = t.BUId
and s.SetlD = t.SetlD )
WHERE Fran = @Franchise
AND t.CDate >= @dtmStart
AND t.CDate <= @dtmEnd
AND s.Status = 1
GROUP BY p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec
RETURN 1
END
GO
View 8 Replies
View Related
Jul 23, 2005
I am able to run a query which runs FAst in QA but slow in theapplication.It takes about 16 m in QA but 1000 ms on theApplication.What I wanted to know is why would the query take a longtime in the application when it runs fast on SQL server?How should we try debugging it?Ajay
View 2 Replies
View Related
Dec 22, 2006
Hi there. I have occasional sincronization error.-XSUBSYSTEM Merge
-XSERVER MYSERVER
-XCMDLINE 0
-XCancelEventHandle 000006E0
2006-12-22 14:55:00.833 Connecting to Subscriber 'Subscriber01'
2006-12-22 14:55:00.895 Connecting to Distributor 'Publisher01'
2006-12-22 14:55:02.974 Initializing
2006-12-22 14:55:03.083 Connecting to Publisher 'Publisher01'
2006-12-22 14:55:06.005 Retrieving publication information
2006-12-22 14:55:06.130 Retrieving subscription information.
2006-12-22 15:00:07.222 The merge process failed to execute a query because the query timed out. If this failure continues, increase the query timeout for the process. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
2006-12-22 15:00:07.456 Error converting data type nvarchar to numeric.
2006-12-22 15:00:07.800 Category:SQLSERVER
Source: Subscriber01
Number: 8114
Message: Error converting data type nvarchar to numeric.
After manual syncronization it goes under control.
Could anybody explain the relationship between converting error and timed out query?
If this is poor connection's problem how can I increase the query timeout for the process?
View 5 Replies
View Related
