Let's say I have a table of data as per the below..
I'm trying to extract only the green highlighted items..
The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.
As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).
I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.
I do have the following Extended Event session on my Dev box;
CREATE EVENT SESSION [sp_showplan] ON SERVER ADD EVENT sqlserver.query_post_execution_showplan(SET collect_database_name=(1) ACTION(sqlserver.plan_handle) WHERE ([package0].[equal_uint64]([object_type],(8272)) AND [sqlserver].[equal_i_sql_unicode_string]([object_name],N'MyStoreProcedure'))) ADD TARGET package0.event_file(SET filename=N'E:DBA_AuditSP_Exec.xel',metadatafile=N'E:DBA_AuditSP_Exec.xem') WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) GO
The idea is being able to capture the execution plan when the program invokes the store procedure, regardless of the database.
This works on my Dev box. When I manually trigger "MyStoreProcedure" from database A , the event is saved. The same thing happens when I do that from database B. Ok ... so far, so good.
So I went to the live production environment and setup my Extended Events session. But it's saving nothing. I was able to check that the store procedure was executed on several databases but my extended events session never grabbed the plan.
What could be the reason for this? Memory starvation maybe? Is there something I am doing wrong?
suppose i get date from MSsql how do i pass that to dos command is there a way so i can maipulate date and pass on to dos command to run at certain time
Chasis Status Date pl1 sold 10/20/2004 pl1 return 10/21/2004 pl2 sold 10/24/2004 pl2 return 10/25/2004 pl3 sold 11/01/2004 pl4 sold 11/03/2004 pl4 return 11/04/2004 pl4 sold 11/06/2004
sp i want to list out cars that status solid has been sold
so in this case only pl3 and pl4 can be display. So anyone can advise me on this. thanks
I am trying to display a report from a bug tracker that shows the number of issues logged per month, per year and how many issues have been closed in that month. I have no difficulty displaying the number of issues that have been logged within a particular months, but when I need to display the number of issues closed - I get stuck. I am calling from one table 'bugs', which contains date_reported, status_id. I can do a count on the number of bugs between certain dates but I cannot list the number of closed issues in that exact time. I get the feeling I need a select statement within a select statement? If anyone can help. Thanks
I used to use this code to extract coupon usage from two tables orders and coupons
select distinct o.OrderDate, p.SKU, c.CustomerID, c.CustomerLevelID, c.Email, c.FirstName, c.LastName from customer c join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber
[code]....
Following a database upgrade the Coupon Code is no longer used. Instead there are two new SQL tables called Promotions and PromotionsUsage.. The link between the two tables are
ID in Promotions
and
PromotionID in PromotionUsage
Both are the same value. So in order to see new orders for coupon usage how would I change this line in the original SQL to retireve this?
I am trying to test sp in Query Analyser as below but get the error
Server: Msg 8114, Level 16, State 4, Procedure spRMU_GetChaseFiles, Line 0 Error converting data type varchar to datetime.
How do I put the date as a parameter ? I think it may be to do with the fact that MaxDateOut is an expression in my view that the sp is based on. It is taking this as char when it should be datetime. So, how do I make this expression type date ?
I know how to look at active users from Enterprise Manager, but how can I query out the information - what table is that info stored in ??? I don't want a list of all the logins, I just want the list of active users....
I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
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.
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)
Below is what I have so far Also I am getting other FRID besides 0 as well.
Select P.PartID, P.mansPart, P.Cost As PartCost, V.PartID, V.PartNo, V.price As VendPrice, M.title, C.CoName, P.FRID From Parts As P Right Join Vend2Part As V On P.PartID = V.PartID And P.FRID = V.FRID Left Join companies As C
I currently have a query that uses three tables [Table1, Table2, Table3]. Each table only has two columns: Group and something else. I want a query that gives me the group and those three something else.
Table1: Group and Info1 Table2: Group and Info2 Table3: Group and Info3 Query: Group, Info1, Info2, Info3
The groups never repeat in a single table.
However, the groups are not identical in every table. I want the groups to never repeat twice: All the groups should appear if they are there at least once in one of the table, and provide Info1, Info2, Info3 (blank if it's not there in its table, filled if it is).
That should be a fairly simple query, I just can't seem to make it work so groups don't repeat.
Little visual example.
Table1: Group - Info1 1 - a 2 - b 4 - c 5 - d
Table2: Group - Info2 1- aa 3- bb 4- cc
Table3: Group - Info3
5- aaa
The query would give me this:
1 - a - aa - "" 2 - b - "" - "" 3 - "" - bb - "" 4 - c - cc - "" 5 - d - "" - aaa
I work for a federal government client who restricts us to using virtual machines for dev work. Now the VM team has gotten MUCH more aggressive with "right-sizing" (which always means down-sizing) our virtual machines. I have a Dev server with 8 GB RAM and I need that much when developing, but I don't develop all the time. So I want it to look like it needs all that RAM before they bust me down to 1 GB, which they will do.How can I get SQL Server to use up all the memory I've allocated to it? Is there a relatively easy query to do this?
I'm designing a new database which will be the back-end to a heavily-used web-based application (all these terms are relative - I guess the use won't be that heavy in the grand scheme of things, I'm only talking 100 users or so at the very most). Data from the old application database will be migrated to this one, and the old database is around 7GB in size after 5 years of use.
I have two different ways of linking some tables in mind, one which is slightly more complex than the other but which potentially has benefits over the simpler method. However, I'm concerned that I might be 'over-cooking' the design, and that performance would suffer as a result, so I've tried creating the two different versions of the database (the part of it I'm concerned with, anyway), one for each of the solutions I've got in mind, migrated the data into the relevant tables and carried out some queries on the data to collect some statistics.
The problem is that, whilst I can see that the more complex method is more expensive, as expected, I don't really understand if the difference is significant. Since I don't know what the numbers in the Client Statistics window actually mean (there are no units! I'm guessing times are in milliseconds?), or how much of real-world impact the difference will have, I'm finding it hard to interpret my statistics and come to a decision.
Querying the entirety of my tables to return ~20,000 records listing one column from each of the main tables I'm playing with, the simpler method had a Total Execution Time of 199, and the more complex a Total Execution Time of 272. Is that the statistic I should be most concerned with? Is that a difference I should be concerned about? Is the difference likely to be magnified when the database is much larger and in use, such that a difference of 73 milliseconds in this test scenario could end up being as much as a whole second in production, for example?
Due to a previous (mis)configuration, i need to grant readwrite permission on a share from a MSSQL DB User.The SQL user will launch t-sql queries on demand and they cannot be scheduled.
I've created a credential object in SSMS, configured it with the correct AD user and mapped it to the MSSQL DB user.Now, if i execute a simple t-sql backup:
BACKUP DATABASE [DB] TO DISK = N'IP.ADD.RE.SSshareDB.bak' WITH NOFORMAT, NOINIT, NAME = N'DB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
I get an access denied error, monitoring the sqlserv.exe process via procmon, i see that the Sql Server process is not impersonating the AD user configured in the credential, it still try to access using the local machine account .
I am working on task where I need to load data into DWH from OLTP. Load needs to b incremental load. For this I am planning to use CDC, to track changes and only load data which has been new inserts/updates/deletes. But to load data to destination, there are couple of joins, which I need to do. Is there a way in CDC, where I can use query as source?
All the recommendations I see from Microsoft docs is to limit the use of Query Notifications (QNs) to notifying connected clients when changes to mostly-static reference or configuration data occur, and to keep the number of overall query forms in play and connected clients to a minimum. Any way regarding a more integral use of QNs and Service Broker from a web app to notify n-web servers (farm) of an update to data that could be updated concurrently and quite frequently, or with a system where the technique is used extensively with lots of different query forms?
What is a remote connection and query ? And what is a local connection and query? If i'm on management studio on my pc connected to a SQL Server 2012 that is on a server, am I remote ? I didn't had a timeout in SSMS for the following query. Considering the remote query timeout setting is 600
USE Database_name go
BEGIN WAITFOR DELAY '00:11'; select * from [dbo].[Table_Name] END; GO
I need to implement one logic similar to rule engine. Below is the example.how to execute all queries in second table order?? what is the best way to implements this
declare @tblRules AS Table (RuleNo INT, RuleDesc NVARCHAR(500), RuleQuery NVARCHAR(MAX), QueryExecutionInterval NVARCHAR(50)) declare @tblRuleResults AS Table (RuleResultID INT, RuleNo INT, ExecuteTime DateTime, NextExecutionTime DateTime, Result NVARCHAR(10)) INSERT INTO @tblRules VALUES ('1','Fail - 2 times within 1 Hour','XXX','Every 15 Minutes') INSERT INTO @tblRules VALUES ('2','Fail- 2 times within 2 Hour','YYY','Every 30 Minutes')
I am trying to write a query to a file with a unique name and pipe "|" delimited. Problem is in 2012 the sp_cmd does not exist. I am currently using the code