Hi I have a table called DiaryDate2 and a query called DiaryDateOver8 and want to have a sql query to return the records from the table that are not in the query based on two linked fields username and Diarydate1 The SQL I have written however returns all records SelectCommand="SELECT DiaryDate2.Username, DiaryDate2.DiaryDate1 FROM DiaryDate2 WHERE(DiaryDate2.Username = ?)AND NOT EXISTS (SELECT 1 from DiaryDateover8 where DiaryDateover8.Username=DiaryDate2.Username AND DiaryDateover8.DiaryDate1=DiaryDate2.DiaryDate1)ORDER BY DiaryDate2.DiaryDate1"> Please could someone help Many thanks Colin
I've got this sql statement that keeps returning the wrong data. (it's related to a previous post, but is different)
Code: SELECT C.NAME, OL.PART_ID, SL.SHIPPED_QTY FROM CUSTOMER C INNER JOIN USERS U ON C.ID = U.ID INNER JOIN ORDERS O ON C.ID = O.ID INNER JOIN ORDER_LINE OL ON O.ID = OL.ORDER_ID
SQL 2005 9.00.3402.00 (x64) As Above really when I select * OR select a single column from the view the wrong column data is returned. in SQL Management Studio when I expand the Columns of the view it reflects the old table structure not the new table structure. I can easily fix by compiling the view again but this would mean I would have to recompile all referencing views when I make a change to table structures. I've tried various DBCC Clean Buffers & drop cache with no effect. Is there a command to recompile all views & poss stored procs in a database. Any help or explanation would be appreciated GW
I have a table tbl_rules. This table will define rules for each role. I have not yet defined the fields for the rules. But the table definition is as below:
Create table tbl_Rules ( ID int identity(1,1) not null, Role_ID int not null, primary key (ID), constraint fk_RoleName foreign key (Role_ID) references tbl_Role(ID) )
The table tbl_role has two columns as below:
ID RoleName 1 Manager 2 Analyst 3 Admin
So far so good. I created the tbl_rules.
But what i want to do is when I do select * from tbl_Rules, i want to show as below:
ID Role_ID 1 Manager 2 Admin
Instead it shows:
ID Role_ID 1 1 2 3
Is there a way to do this? The goal is to return the select * from tbl_Rules results to a gridview to enable adds and changes. I could do this by doing queries for each column, but I was hoping to make it easier. Not sure if this is even possible.
A DB2 store procedure returns two data sets, when executed from SSMS, using linked server. Do we have any simple way to save the two data sets in two different tables ?
i have the following stored procedure which does a hit to a server and returns the response
it is hitting the server but the problem is the response is null
any ideas what i'm doing wrong
/****** Object: Stored Procedure dbo.http_geturl Script Date: 2/12/2007 2:00:40 PM ******/ ALTER procedure [dbo].[http_geturl]( @sUrl varchar(8000), @response varchar(8000) out) As Declare @obj int ,@hr int ,@status int ,@msg varchar(255)
exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT if @hr < 0 begin /* Raiserror('sp_OACreate MSXML2.ServerXMLHttp failed', 16, 1) */ return end
exec @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @sUrl, false if @hr <0 begin set @msg = 'sp_OAMethod Open failed' goto eh end
exec @hr = sp_OAMethod @obj, 'send' if @hr <0 begin set @msg = 'sp_OAMethod Send failed' goto eh end
exec @hr = sp_OAGetProperty @obj, 'status', @status OUT if @hr <0 begin set @msg = 'sp_OAMethod read status failed' goto eh end if @status <> 200 begin set @msg = 'sp_OAMethod http status ' + str(@status) goto eh end
exec @hr = sp_OAGetProperty @obj, 'responseText', @response OUT if @hr <0 begin set @msg = 'sp_OAMethod read response failed' goto eh end exec @hr = sp_OADestroy @obj return
ALTER PROCEDURE dbo.usp_Create_Fact_Job (@startDate date, @endDate date) AS /*--Debug--*/ --DECLARE @startDate date --DECLARE @endDate date
--SET @startDate = '01 APR 2014' --SET @endDate = '02 APR 2014' ; /*-- end of Debug*/ WITH CTE_one AS ( blah blah blah)
SELECT a whole bunch of fields from the joined tables and CTEs...When I run the code inside the stored procedure by Declaring and setting the start and enddates manually the code runs in 4 minutes (missing some indexes ).When I call the stored procedure with the ExEC
It never returns a results set but doesn't error out either. I have left it for 40 minutes and still no joy.The sproc is reasonably complicated; 6 CTEs to find the most recent version of records and some 2 joins to parent tables (parent and grandparent), 3 joins to child tables (child, grandchild and great grandchild) and 3 joins to lookup views each of which self references a table to filter for last version of a record.
From MS Dynamics NAV 2013 I get a lot of querries that have a where clause like this:
where [Field1] like @p1 and [Field1] < @p2. Field1 is the only primary key field and clustered index. The query also has a TOP 50 clause. @p1 is always a "Starts-With"-value (something like N'abc%').
The query plan uses a clustered index seek but the number of reads look more like a clustered index scan.
Depending on the table size I see 1M or more reads for these querries.
If I rebuild the query in SSMS, but replace the paramerters with actual values I only see a few reads.
I was able to reproduce the issue with a temp table. See code below.
Is there a way to make SQL Server use another strategy when using the parameterized query?
SQL Server Version is 11.0.3401. if object_id('tempdb..#tbl') is not null drop table #tbl; create table #tbl ( [No] nvarchar(20) ,[Description1] nvarchar(250)
Right now I have to do something like this and it is time consuming every time I have to query a specific table...
SELECT lots_of_columns FROM table WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1') AND other_query_critiera_here
Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).
I would like to be able to combine the functionality of IN and LIKE in a WHERE clause. Although the simple AdventureWorks2012 example below illustrates the concept with 3 search criteria, the real-world example I need to apply the concept to has a couple dozen. This returns 50 rows, but requires multiple OR ... LIKE functions:
SELECT DISTINCT c.Name FROM Sales.Store c WHERE c.Name LIKE '% sports %' OR c.Name LIKE '% exercise %' OR c.Name LIKE '%toy%'
What I would like to do is something like this, which doesn't work:
SELECT DISTINCT c.Name FROM Sales.Store c WHERE c.Name IN(LIKE '% sports %', LIKE '% exercise %', LIKE '%toy%')
I could load up a cursor and loop through it, but the syntax is more cumbersome than the multiple LIKE statements, not to mention most SQL programmers are horrified at the mention of the abominable word 'cursor' for performance reasons.
A while back, a "quirky update" method was proposed for lightning fast running totals based on the three-part MSSQL UPDATE's SET statement and tally tables. However, some claimed this was not 100% absolutely guaranteed behavior.
How does the new OVER clause compare in terms of performance ?
DECLARE @Tbl TABLE ( pk int not null primary key identity, N int ) INSERT INTO @Tbl (N) SELECT TOP 1000 1 FROM syscolumns a CROSS JOIN syscolumns b SELECT pk, SUM(pk) OVER (ORDER BY pk ) FROM @Tbl
I am creating a dynamic query where i am appending a where clause something like -
IF (@CurCaptureDate IS NOT NULL) SET @where_clause = @where_clause + CHAR(10) + 'AND CD.CaptureDate = ' + @CurCaptureDate
PS - CD.CaptureDate is datetime and @CurCaptureDate is also datetime
but when executing , it gives this error - Msg 241, Level 16, State 1, Line 169 Conversion failed when converting date and/or time from character string.
i am not able to use convert here with these quotes.
i tried this - SET @where_clause = @where_clause + CHAR(10) + 'AND CD.CaptureDate = ' + CONVERT(VARCHAR(25),@CurCaptureDate )
but it makes it to -
AND CD.CaptureDate = Jul 19 2014 12:00AM. I would need the date in quotes.
Because of the way in which a specific piece of code is written, I'm bound into using a WHERE clause for a report generation.Each Inspection generates a unique Inspection Number. Any re-inspection created from that inspection is assigned that Inspection Number and appended with ".A", ".B", ".C" and so on.
The problem is this: Each row's Primary Key is the "InspectionId" in "dbo.v_InspectionDetailsReports". I need to return not only the data related to that particular InspectionId, but also the data related to any previous related inspection. For example, if I have a main number of CCS-2012 and three re-inspections, CCS-2012.A, CCS-2012.B and CCS-2012.C, and I report on CCS-2012.B, I need all the data for CCS-2012, CCS-2012.A and CCS-2012.B but NOT CCS-2012.C.
I would prefer to not have to do everything in a WHERE statement, but my hands are a bit tied.
The "SELECT * FROM dbo.v_InspectionDetailsReports WHERE . . ." is already hardcoded (don't ask). SELECT * FROM dbo.v_InspectionDetailsReports WHERE ( RefOnly = 0 OR RefOnly IS NULL
I am using an aggregate with the OVER clause.Running the script is fast less than 1 second but when I say insert into a temp table the execution plan is very different at it take 8 seconds.I have attached the execution plans. Also the Statistics IO, Time messages. I am using SQL Server 2014 with backward compatibility to 2008 R2.
if (select OBJECT_ID('tempdb..#MM')) is not null drop table #MM CREATE TABLE #MM ([MyTableID] [int], [ParticipantID] [int], [ConferenceID] [nvarchar](50), [Points] [money], [DateCreated] [datetime], [StartPoints] [money], [EndPoints] [money], [LowPoints] [money], [HighPoints] [money]) insert into #MM ([MyTableID], [ParticipantID], [ConferenceID], [Points], [DateCreated], [StartPoints], [EndPoints], [LowPoints], [HighPoints]) selectmm.MyTableID, mm.ParticipantID, mm.ConferenceID, mm.Points, mm.DateCreated,
I am running this stored Prcedure and getting multiple lines for the output. Below are the queries the create the temp table (its holding the data), and the query that generates the output:
/* this creates the temp table */ Select count(*) as 'Donations', d_vst_id into Donations_temp from dnr_vst_db_rec where convert(varchar(10),d_vst_date) between convert(varchar(10), @Beg_Vst_Date, 112) and convert(varchar(10), @End_Vst_Date, 112) and d_vst_status = 'DN' and d_vst_dontyp in ('E1', 'E2') group by d_vst_id
/* this query generates the output */
select distinct cast(getdate() as varchar(30)) as 'TODAY' ,CONVERT(varchar(10), @Beg_Vst_Date,101) as 'BEGDTE' ,CONVERT(varchar(10), @End_Vst_Date,101) as 'ENDDTE' ,case Donations when '1' then sum(1) else 0 end as 'ONE1' ,case Donations when '2' then sum(1) else 0 end as 'ONE2' ,case Donations when '3' then sum(1) else 0 end as 'ONE3' ,case Donations when '4' then sum(1) else 0 end as 'ONE4' ,case Donations when '5' then sum(1) else 0 end as 'ONE5' ,case Donations when '6' then sum(1) else 0 end as 'ONE6' ,case Donations when '7' then sum(1) else 0 end as 'ONE7' ,case Donations when '1' then Sum(0) when '2' then Sum(0) when '3' then Sum(0) when '4' then Sum(0) when '5' then Sum(0) when '6' then Sum(0) when '7' then Sum(0) else Sum(1) end as 'ONEA' from Donations_temp group by Donations
I have 10k indexes I need to rebuild and each time the script reaches an error it stops all further activity. How can I append 'GO' to the end of each line so it will continue on error messages?
Once I have the syntax I can do a find and replace function in Notepad++
USE [AdventureWorks2014] + char(13) + char(10) + GO ALTER INDEX [IX_Person] ON [Person].[Person] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF) + char(13) + char(10) + GO ALTER INDEX [IX_Emp] ON [HumanResources].[Employee] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF) + char(13) + char(10) + GO ************** Truncate ***********
create table a (id int, name varchar(10)); create table b(id int, sal int); insert into a values(1,'John'),(1,'ken'),(2,'paul'); insert into b values(1,400),(1,500);
select * from a cross apply( select max(sal) as sal from b where b.id = a.id)b;
Below is the result for the same:
idname sal 1John500 1ken500 2paulNULL
Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.
One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output. I'm running this query on SQL Server 2012.
I am having probelms trying to get a stored proedure to return an output value. The outline of the specific request is that I supply a varchar which is unique within a set of tables (tables named in another table). I want to search each of the tables until I find the one that has the value and when found return the GUID of the row and the table name. I have not been successful in getting the value of the GUID (an int) to be returned. If I run in debug and stop after the SQL call I can see the value in the SQL output parameter but it does not appear in the variable I specified in the SQL parameter setting. I am probably doing something simplly wrong but cannot see it. Please help if you can. Regards, Major (that is my Christian name ;-) The code files are copied below. Web app code sing System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; using EFormsLIB; public partial class _Default : System.Web.UI.Page { String gstrConnectionString; // Connection to the SQL database, set in the config file to allow for changes while testing and running protected void Page_Load(object sender, EventArgs e) { // Read the connection string from the config file EFormsRoutines r = new EFormsRoutines(); gstrConnectionString = r.ReadConfigString("EForms21ConnectionString", ""); } protected void btnCancel_Click(object sender, EventArgs e) { this.Dispose(); } protected void btnGet_Click(object sender, EventArgs e) { // Get GUID for the form using UniqueDocID as the BlueWare ID. int intGUID = -1; SqlConnection SqlCDB = new SqlConnection(gstrConnectionString); SqlCommand SqlCmd = SqlCDB.CreateCommand(); SqlCmd.CommandText = "GetGUIDfromBWID"; SqlCmd.CommandType = CommandType.StoredProcedure; SqlParameter BWIDParameter = new SqlParameter(); BWIDParameter.ParameterName = "@BWID"; BWIDParameter.SqlDbType = SqlDbType.VarChar; BWIDParameter.Size = 30; BWIDParameter.Direction = ParameterDirection.Input; BWIDParameter.Value = TextBox1.Text; SqlCmd.Parameters.Add(BWIDParameter); SqlParameter GUIDParameter = new SqlParameter(); GUIDParameter.ParameterName = "@GUID"; GUIDParameter.SqlDbType = SqlDbType.Int; GUIDParameter.Direction = ParameterDirection.Output; GUIDParameter.Value = intGUID; SqlCmd.Parameters.Add(GUIDParameter); SqlCmd.Connection.Open(); int ret1 = SqlCmd.ExecuteNonQuery(); SqlCmd.Connection.Close(); Label1.Text = intGUID.ToString(); } } web app aspx <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Label ID="Label1" runat="server" Style="z-index: 100; left: 48px; position: absolute; top: 24px" Text="Label"></asp:Label> <asp:Button ID="btnGet" runat="server" OnClick="btnGet_Click" Text="Get" style="z-index: 101; left: 8px; position: absolute; top: 48px" /> <asp:Button ID="btnCancel" runat="server" OnClick="btnCancel_Click" Text="Cancel" style="z-index: 102; left: 64px; position: absolute; top: 48px" /> <asp:TextBox ID="TextBox1" runat="server" Style="z-index: 103; left: 48px; position: absolute; top: 0px" Width="48px"></asp:TextBox> <asp:Label ID="Label2" runat="server" Style="z-index: 104; left: 0px; position: absolute; top: 0px" Text="BWID"></asp:Label> <asp:Label ID="Label3" runat="server" Style="z-index: 106; left: 0px; position: absolute; top: 24px" Text="GUID"></asp:Label> </div> </form> </body> </html> SQL procedure set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[GetGUIDfromBWID] @BWID varchar(30), @GUID int outputASBEGIN SET NOCOUNT ON; declare formscursor cursor for select formname from forms for read only declare @found int, @formname varchar(30) select @found = 0 open formscursor fetch next from formscursor into @formname while (@found = 0) begin if (@@fetch_status <> 0) select @found = 2 else begin if @formname = 'UAT040' begin if (select count(*) from UAT040 where BWID = @BWID) =1 begin select @GUID = (select GUID from UAT040 where BWID = @BWID) select @found = 1 end end else if @formname = 'GEN001' begin if (select count(*) from GEN001 where BWID = @BWID) =1 begin select @GUID = (select GUID from GEN001 where BWID = @BWID) select @found = 1 end end else if @formname = 'GEN002' begin if (select count(*) from GEN002 where BWID = @BWID) =1 begin select @GUID = (select GUID from GEN002 where BWID = @BWID) select @found = 1 end end else if @formname = 'CFT001' begin if (select count(*) from CFT001 where BWID = @BWID) =1 begin select @GUID = (select GUID from CFT001 where BWID = @BWID) select @found = 1 end end fetch next from formscursor into @formname end end deallocate formscursor if @found = 2 select @GUID = -16 select @GUIDend
Hi, I have this output, @RegisterFlag int OUTPUTand I have a transaction going on, so my code (I just put some relevant code here) is: 1 BEGIN TRAN 2 SELECT @getDealername = OrgName FROM Org WHERE OrgName = @DealerName 3 If @getDealername is null 4 5 ELSE 6 BEGIN 7 set @RegisterFlag = 2 8 ROLLBACK TRAN 9 RETURN 10 END 11 12 COMMIT TRAN 13 set @RegisterFlag = 1 The problem I am facing now is I couldn't get @RegisterFlag = 2 return back to my asp.net code when it reached line 7, instead I got this error mesg:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0. How do I fix this? Many big thanks.
We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data.
In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.
The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.
The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe.It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing).e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.
USE [xxxxxxxxxx] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
[code]....
So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).
change @pIdentifier nvarchar(25) to nvarchar(22) Trim any potential blanks from either side of both parts of the identifier comparison Change the SELECT TOP(1) to an EXISTS
The only other thought is the two way parameter direction in the C# for the result OUTPUT. I have been unable to replicate this using a test app and our test databases. Have observed selects failing to find even though the data is there, like this before?
How I am using a CASE statement within a WHERE clause to filter data:
CREATE PROCEDURE dbo.GetSomeStuff @filter1 varchar(100) = '', @filter2 varchar(100) = '' AS BEGIN SELECT
[Code] .
What I want, is to be able to pass in a single value to filter the table, or if I pass in (at the moment a blank) for no filter to be applied to the table.
Is this a good way to accomplish that, or is there a better way? Also, down the line I'm probably going to want to have multiple filter items for a single filter, what would be the best way to implement that?
the code below works (this is only a quick dumbed down version of the actual code, it might not work 100% for all cases). Is it at all possible to exploit the functions that were added to SSQL since v. 2005 to simplify this code ?
In SSRS, a parameter allows the user to create a list of invoices (from CRM) to be ordered in any of the following ways the user prefers:
'Document Date (most recent date first)' 'Document Number (highest number first)' 'Document Date (most recent first) and Number' 'Document Number (lowest number first)'
The invoices have a (supposedly) sequential identity-generated number. However Accounting may want to set a different date than the creation date on some invoices. So there is no way the invoice numbers will be in the same sequence as the invoice dates.
So I just created the "sorting fields" - they appear as junk in the output dataset (just do not drop them in the SSRS tablix - they have to be part of the SELECT statement to be usable in the ORDER BY clause.
The code is:
DECLARE @ls_OrderBy varchar(80) --'Document Number (highest number first)' --'Customer and Document Date (most recent date first)' --'Customer and Document Number (highest number first)' --'Document Date (most recent first) and Number'
I have a two tables each having a uniqueidentifier column person_id
I am trying to a select statement where I want a list of the person_id's in one table that are not in another table.
-- insert into wch_needed those who need checked
insert into #wch_needed (person_id, rendered_by ) select distinct e.person_id, e.rendered_by from #wch_who o, encounter e where o.person_id not in (select distinct person_id from #wch_have ) and o.person_id = e.person_id
the where conditional
where o.person_id not in (select distinct person_id from #wch_have )
I have the following code and the result set is coming out as nvarchar. So, when I create a report in SSRS, it is not formatting as a date.
, CASE WHEN isnull(cv2.Accepted,0)='True' AND cv2.Visit ='V2' AND cv2.StepNo='3' THEN 'Y' ELSE CONVERT(varchar,[dbo].[fn_Get_WorkingDays] (co.PlannedGoLiveDt, -10)) END AS 'System Verified'
I have a sql query that gets the count of exams held in each month.
Below is the code that I have used.
select Examid, count(*) as CumCount from [dbo].[Exams] where ExamCategory in ('Major','Critical') and Month(EXOCCRDATE) = Month(getdate()) and Year(EXOCCRDATE) = Year(getdate()) group by Examid
The code works good when we have data for the current month. When we dont have any exams for the current month, the code outputs empty values. I want the code to be altered so that when there is no value returned in the output, i want a default value shown in the output.
I have attached the sample data that I am using. In the data we dont have dates for the month of October. So when I run the code it will display empty output. So what I need is I need a text like 'No Data' to be shown when no value is returned by the query.
