SQL Server 2012 :: How To Get All Queries That Executed In Server For Day
Feb 27, 2014
In one of our requirement, I want all the query details for the SQL query batch that got executed for the day. I know, we can get sql query from dm_ exec_ query_stats. But I want all sql query along with their session details ie. ExecutedDateTime, SessionId, UserID etc. I have tried using sys.dm_ exec_ sessions. But it contains only last executed query details for all the sessions. how to obtain all the session details for all the query executed for the day in the server.
I am using Server 2012 and very new to SQL. I have a request from a physician for a list of his patients that meet a criteria. This is stored in a temp table names #cohort.
Using this cohort he wants each row to be one patient with a list of labs, vitals, etc. Three items are the most recent lab value and date. I could query each lab individually and place it into a temp table and then join all temp tables at the end, but I am trying to move past that and have all labs in one temp table. All temp tables are joined with PatientSID.
I tried to do something for just 2 labs, but it is not working. There could be nulls values when joined with the #cohort table.
Individually the SELECT statements pull in the most recent lab value and date, but I cannot get them into a temp table with one row of PatientSID and then the lab value and date if they exist.
IF OBJECT_ID ('TEMPDB..#lab') IS NOT NULL DROP TABLE #lab SELECT cohort.PatientSID ,SubQuery1.LabChemResultNumericValueAS 'A1c%' ,SubQuery1.LabChemCompleteDateTimeAS 'A1c% Date' ,SubQuery2.LabChemResultNumericValueAS 'LDL'
Our developers have gotten this idea lately that instead of having many small stored procedures that do one thing and have small parameter lists that SQL can optimize query plans for, its better to put like 8-10 different queries in the same stored procedure.
They tend to look like this:
create procedure UberProc (@QueryId varchar(50)) as
if @QueryId = 'First Horrible Idea' begin select stuff from something end if @queryid = 'Second really bad idea' begin select otherstuff from somethingelse end
I see the following problems with this practice:
1) SQL can't cache the query plan appropriately 2) They are harder to debug 3) They use these same sorts of things for not just gets, but also updates, with lots of optional NULLable parameters that are not properly handled to avoid parameter sniffing.
Hi,I am newbie to SQL Server. I am using SQL Server 2000.I know SQL server compiles the SQL stmt or SP and stores the plan forlater use(I know its not always done. But should be done to reduceexecution time) in memory somewere. I want to capute all the executionplan which are kept in memory. In oracle v$sql_plan can be used toaccess the plan in oracle. I want to know how to do that in SQL Server.I can use profiler, but it contributes some CPU utilization.Thanks,Thiru.WantedToBeDBAWantedToBeDBA {at} gmail {dot} com
Hi everyone,Does exist an easy way to count the actually number of queries executed within a page?I've searched here and in google but found anything...Thanks in advance!
I need to search for such SPs in my database in which the queries for update a table contains where clause which uses non primary key while updating rows in table.
If employee table have empId as primary key and an Update query is using empName in where clause to update employee record then such SP should be listed. so there would be hundreds of tables with their primary key and thousands of SPs in a database. How can I find them where the "where" clause is using some other column than its primary key.
If there is any other hint or query to identify such queries that lock tables, I only found the above few queries that are not using primary key in where clause.
I had my database in 6.5 which i upgraded to 7.0 using SQL Server upgrade wizard.Then I created full text catalog. When I say incremental population It gives me warning that You can create full text indexes but can not execute queries against it as the database is still in SQL server 6.5 mode.What is the reason behind this?
Our Sql server is not responding, So we restarted the server and modified one of the sp code. After that we are getting frequently every 2 min giving the below error
The queue 855365233 in database 9 has activation enabled and contains unlocked messages but no RECEIVE has been executed for 453199 seconds
Hi all,One of our companies is having problems with a query on a linked server.They have two servers, serverA and serverB. On serverA they have set up alinked server to serverB.Query: select * from oas_company where code = 'TEST'If I look on serverB via Profiler the query is executed without the wherestatement.Query: select * from oas_company where tstamp = 0Again via profiler I can see that the where statement is now included. Thetstamp field is a numerical field, the code field is a text field (don's askme why)So it looks like if there is a selection on a text field (varchar) the wherestatement is not included.For the above selection this is not a problem as this table only contains10 - 20 records. However the real problem is on the oas_balance table. Itshould only return 3 records, but because it skips the where statement, itreturns 10.000.000 records.Any idea? I know the default collations are different on both servers, couldthat be the cause of the problem? And if so, what could I do to overcomethis problem?Any suggestions would be greatly appreciated.BoB
This is a request for help fixing a SQL Server 2000/ADO problem on WindowsXP. I would appreciate any useful assistance.PROBLEM: SQL Server access on my machine fails as follows:1. All of my VB6 apps reference the following ADO typelib:Microsoft ActiveX Data Objects 2.7 LibraryLocated at:c:Program FilesCommon FilesSystemADOmsado27.tlbWhen I open one of these projects in VB6, the reference to msado27.tlb isshown as MISSING. If I remove the reference then open the references dialogagain, msado27.tlb is listed normally. When I check it and try to close thereferences dialog, I take an "Error in loading DLL".I can remove the reference to msado27.tlb and add a reference to msado15.dll(Microsoft ActiveX Data Objects 2.8 Library).When I run the app, I take a trappable error on the first ADO line my appexecutes:Dim lo_Connection As ADODB.ConnectionSet lo_Connection = New ADODB.ConnectionThe error is:Automation errorThe specified procedure could not be found.2. In Enterprise Manager, when I do "Open Table/Return All Rows" on anytable, I take the following error:"The query cannot be executed because some files are either missing or notregistered. Run setup again to make sure the required files areregistered."CURRENT CONFIGURATIONI develop software in VB6 that accesses SQL Server via a dsn-lessconnections and disconnected recordsets. I am running SQL Server 2000Standard locally, version installed from the MSDN install CD and updated byservice pack download.SQL Server version: version 8.00.760 (SP3)VB version: VB6 SP5Component Checker 2.0 shows no problems, and reports:The following product releases were matched: MDAC 2.8 RTM.My OS is Windows XP Home. As far as I can tell, it is completely updatedwith all hotfixes, including the following:KB810218KB820291KB821253KB822603KB823182KB824105KB824141KB826939KB828035KB829558Q322011Q327979Q814995Q815485ATTEMPTS TO FIXA. I have uninstalled and reinstalled SQL Server and client tools *numerous*times with no change.B. Queries in Query Analyzer execute just fine.C. The last four lines out of sqlstp.log (located in c:Windows) are asfollows:12:40:04 StatsGenerate returned: 212:40:04 StatsGenerate (0x0,0x1,0xf000000,0x100,1033,303,0x0,0x1,0,0,012:40:04 StatsGenerate -1,George Copeland)12:40:04 Installation SucceededD. The only services running during setup are those specified by MicrosoftKB article 192710. There are no relevant errors in the event log.E. I tried reinstalling MDAC 2.7 SP1 Refresh and MDAC 2.8. No change.F. Microsoft KB Article 315868(http://support.microsoft.com/defaul...Ben-us%3B315868) saysthe following:If the reinstallation of the SQL Server 2000 Client Tools does not correctthe problem, then re-register the following DLLs by using Regsvr32.exe, andthen restart the computer:C:Program filesCommon FilesSystemADOMsado15.dllC:Program filesCommon FilesSystemOle DbOledb32.dllC:Program filesCommon FilesSystemOle DbSqloledb.dllC:WINNTSystem32Atl.dllAttempting to register the first three dlls fails with"LoadLibrary("C:Program filesCommon FilesSystemADOMsado15.dll")failed - The specified procedure could not be found.". In any case, thisdoes nothing to fix the problem. And since these link libraries are notregisterable, this KB article is just a little, shall I say, unhelpful.Please note that the MS newsgroups are FULL of requests for help on thisEXACT problem. All attempts to help have failed--NOBODY has figured out thefix. Most people trying to help are treating it as an installation issue,because that is what the error message says. However, I suspect insteadthat a Windows XP hotfix has CLOBBERED ADO.BTW, this is a BIG issue for me. My development machine is hosed, and untilI get this fixed, I AM COMPLETELY DOWN.
I could identify the last or all stored procedure, which was performed on a database, +/- something similar to what the profile of sql server identifies as below ?
I saw a presentation last week where the speaker created some sorta sql server "watch window" (in Sql Server Management Studio I think) where he could watch all the commands being executed on his sql server database in real-time. For example he could navigate to web pages (that hit the database) and as he pressed buttons you could see the sql commands execute in this "watch window." If other users hit the database at the same time you could see those sql queries execute as well. I didn't think at the time to ask how he did it - does anyone know how to set this up? I have a problem with my sql server right now and it would be useful to see which sql queries (etc) are being executed when. Thanks in advance,J. Shane Kunklejkunkle@vt.edu
For example, executing a DTSX stored in a server, execution is fine and successfully but the log file attached to that DTSX is not created at all after the execution. Otherwise if I'm going to execute from the server everything is fine: log file is created and gathering the usual information.
Let me know if exists any option for to enable this from the own DTSX.
If I run local packages log files are created without problems.
I have a specific requirement. I need to insert the DML statements executed from Management Studio into a SQL table. We have SQL Server 2008 R2 and 2012 instances.
I have a script contains multiple statements to update multiple tables. How can I make sure that either all statements get executed successfully or no changes apply to the tables (in case one or more errors occur)? I've been searching on Internet and it seems like I need to use Rollback and begin transaction.
I created DTS a while ago and placed in job to run once a day (it worked fine for 3 months) 2 days ago I changed sa password and now job fails with error (Login failed for user 'sa'.), but it run fine from DTS !!!
1. My DTS created with domain Account DomainSVCSQL2000( sa rights and local admin) 2. SVCSQL service use DomainSVCSQL2000 to run 3. SVCSQL agent use DomainSVCSQL2000 to run 4. DTS use 'osql -E
Where should look for reference to sa ?
Executed as user: MONTREALsvcsql2000. DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'sa'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.
isn't there an automatic log of some sort to check and see what exactly was changed by a given SQL command? A stored proc was ran and I need to figure out what exactly it changed in the underlying table.
I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results. Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
I have a remote server with SQL server 2014 instance on it. There is nothing else running on the SQL Server box(dedicated SQL box). There is only one instance of SQL 2014 on the server. No other versions of SQL server are on the server.
Issue:
1. When I execute a query connecting to the SQL server instance through my local SSMS, the query executes in 30 secs.
2. When i connect to remote server through windows RDP session and execute the same query in the SSMS(on server), then query executes in 1 minute.
I'd like to know if there's a way to pass parent package parameters to a package executed by SQL Server Job Agent? It appears that sp_start_job doesn't have any variable that could accomodate this.
I am wondering if it is possible to make 1 query that gives several, different resultsets.
I am working on a database for speedskating times, in particular I am working on seasonlists
The query I use for this i (in simplified form)
SELECT LastName, FirstName, Country, Time FROM TbExample WHERE Distance = '500' and Gender = 'Man' My question is about the "WHERE Distance = '500' and Gender = 'Man' " part.
I need the combinations 500-Man, 1000-Man, 1500-Man, 500-Women, 1000-Women, 1500-Women.
In the current setup I need to make 6 queries.
Is it possible to make some kind of (as they call it in Excel) Array (or 2: 1 for distances, 1 for gender) that runs through all possible combinations?
We have two queries that run nightly and we'd like to combine them and only have one result set instead of two. What's the best way to combine these? The only difference is the Table the information is being pulled from.
Query 1:
set nocount on select case when datalength(MICRACCTNUMBER) = 4 then convert(char(20),('001 000000000000'+MICRACCTNUMBER)) when datalength(MICRACCTNUMBER) = 5 then convert(char(20),('001 00000000000'+MICRACCTNUMBER)) when datalength(MICRACCTNUMBER) = 6 then convert(char(20),('001 0000000000'+MICRACCTNUMBER))
[code].....
Again, the only difference is the Table the info is coming from...
what the ideal CPU count and Max Degree of Parallelism are for a 3rd party database server.The server has 12 CPUs, 32GB RAM and all database sizes add up to < 30GB so they can all fit in memory (I tried to force this by doing a select * from every table). On certain payroll days, the CPU gets maxed out to 100% for a few seconds.
MAXDOP was originally set to the default 0. We later changed it to 8 based on several 'best-practices' articles. However the vendor suggests to change it to 1 (no parallelism), while others suggest changing it to 4, so that one run-away query doesn't hog most of the CPUs.
I'd like to find out how many CPUs are actually being used by queries. There is a Degree of Parallelism event in URL.... The BinaryData column says :
0x00000000, indicates a serial plan running in serial. 0x01000000, indicates a parallel plan running in serial. >= 0x02000000 indicates a parallel plan running in parallel.- What does "parallel plan running in serial" mean ?
I see a lot of 0x01000000, and a few 0x08000000's in my trace.How can i determine whether one query is hogging CPUs and if reducing it to 4 will work?
Is there an easy way to convert Access Queries to SQL Views without doing it manually?I have used the Databse tool to migrate tables, but cannot see to find something similiar for queries.
I have to create a SQL job which will run around 50 queries and email results when the query gets some results.These are like quality checks which run to check errors in the system so if any query(out of 50 queries) returns some results an email with the details will be sent .So if 5 queries return results 5 emails with the details will be sent .
I think of something like A table which has one column as the query .What will be the best way of handling such a scenario , may be need an SSIS package with steps ?
