SQL Query Are Being Executed By SQL Server Engine?
Aug 13, 2007How SQL Query/ Stored procedure are being executed by SQL Server Engine ?
View 1 Replies
ADVERTISEMENT
Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.
May 26, 2008Just 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.
SQL Server/ADO/XP Problem Query Cannot Be Executed
Jul 20, 2005This 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.
View 5 Replies View RelatedScheduling A Query To Be Executed At Given Time In SQL Server 2005
Apr 4, 2007Hi friend!
I want to schedule a query or procedure running at given time regularly eg: at 12AM daily. Please tell me how to do that in sql server 2005.
We use SQL server 2005 developer edition.
Thanks in advance.
How To Get The Last Query Executed?
Feb 9, 2004Hello, i have a trigger and i want to know the query that raised it, or want to retrieve the last executed query by the server. I think it's a hard question but i know that someone can help me... Thanks
View 3 Replies View RelatedSame DTS Fails Executed As Job ,but Run Fine When Executed From DTS Designer
Mar 13, 2002I 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.
DB Engine :: Min Memory Per Query Server
May 6, 2015Is there has any SQL query to check running out of "min memory per query SQL Server"(default value is 1024KB)?
View 2 Replies View RelatedLog The Query Executed In Stored Procedure
Jun 19, 2008hi,
i want log the query executed in stored procedure ..
any way of doing it in sql server 2000
regards.
Performance Difference Between Query Executed Through ASP.NET And SSMS
Sep 18, 2007I have also posted this in microsoft.public.sqlserver.programming.
I have a query which, depending on where I run it from, will either take 10 milliseconds or 10 seconds.
The query works perfectly when run in SQL Server Management Studio... in my database of around 70,000 items it returns the results in around 10ms. It uses all my indexes and indexed views correctly.
However when I run the identical query from my ASP.NET application, it takes around 10 seconds... 1000 times longer.
Looking at it in Sql Server Profiler I can't see any difference in the query, except from ASP.NET it needs 62531 reads and from SSMS it needs only 318 reads. If I copy the slow running ASP.NET query from the profiler into SSMS, then it runs quick again. The results returned are the same.
I have provided more details of the query below, but I guess my real question is: What is the best way to debug this? I'm not an expert with SQL Server, so any pointers on where I should start looking to find the difference in how the query is being executed would be a great help.
The query is of the form:
WITH RowPost AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY DateCreated DESC) AS Row,
ItemId,
Title,
....
FROM
Items_View WITH(NOEXPAND)
WHERE ItemX >= @minX AND ItemX <= @maxX AND ItemY >= @minY AND ItemY <= @maxY
)
SELECT
*,
(SELECT Count(*) FROM RowPost) AS [Count]
FROM RowPost
WHERE Row >= @minRow AND Row < @maxRow
Where Items_View is an indexed view, and WITH(NOEXPAND) is being used to force it to use the indexed view (this is optimal). The line beginning "SELECT Count(*)" is to get the total number of results (without having to run the inner query a second time).
This is running against SQL Server Developer Edition.
Analysis :: Excel Waiting For Query To Be Executed
Jun 2, 2015My Excel Pivot Table connects to and SSAS 2008 cube. What exactly happens in the back-end during 'Waiting for the query to be executed...'
View 3 Replies View RelatedIs It Possible Extract File Name From Script Executed In Query Analyser ?
Apr 6, 2006Hi ,
is it possible extract file name from script executed in query
analyser ?
Example steps
1. open QA
2. open c:mySuperScript.sql
code in c:mySuperScript.sql
use master
select @filename as [file executed]
select count(*) as xfrom syslogins
3. run
-- result
file executed
------------
c:mySuperScript.sql
x
--
112
Why we need it.
Each database update created using sql compare tool from red-gate
Script saved in Vss
When script executed in production using QA we save output as proof of deployment
currently we add to each script
select
'script= put you file name'+ char(13)+
'server = ' + cast(@@servername as char(20))+ char(13)+
'dbname= ' + cast(DB_NAME()as char(20)) + char(13) +
'execution date= '+ cast(getdate()as char(24))+ char(13)+
'executed by = '+ system_user
We want wrap everything in procedure that will
extract executed file name and other values
this procedure should be first line of code in each script
Thanks
Alex
Script Your Data In A T-SQL Format (which Can Be Executed In Query Analyzer)
Jul 20, 2005Hi all,i'm working with SQL Server for about 4 years, and i have eversearching for a tool which can script my DATA in a T-SQL format tocopy them very easy from one server to another.For example scripts like that:IF NOT EXISTS (...)INSERT INTO (...)ELSEUPDATE (...)I have never found a tool like that, so i write a it by myself.This software is a free version in the moment, you can download it onmy website:http://www.sqlscripter.com..NET Framework on your client PC is required.Have fun with it ...Thomas
View 2 Replies View RelatedDB Engine :: Linked Server - Getting Error When Performing Cross Instance Query With Joins
Apr 26, 2015I've successfully created a Linked Server that connects a local DB Engine with another DB Engine through an ip over an extranet. I am able to run simple Select statement queries on the Local DB Engine and get results from the linked server. However when attempting to perform more complex queries that join tables from the linked server with tables from the local DB server, I get the following error message after several minutes of execution:
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Protocol error in TDS stream".
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
How I can resolve it. I've read on Distributed Transactions but I understand that it only applies to manipulation statements?
Both are SQL servers. Linked Server is SQL2008R2 if not mistaken. Local DB Engine is SQL2014.
DB Engine :: Multiple Execution Of Query Pattern Generates Same Query Plan
Oct 6, 2015SQL 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?
Linked Server: WHERE Clause Not Being Executed
Jul 23, 2005Hi 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
View 2 Replies View RelatedRows Skipped Out In Stored Procedure While Return All Rows If Query Executed Seprate
Nov 8, 2007Hi All,
I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
But nothing is improving
A Procedure Runs Slow As A Job But Runs Fast Executed In A Query Window
Apr 23, 2008Performance issue.
I have a very complex Stored Procedure called by a Job that is Scheduled to run every night.
It's execution takes sometimes 1 or 2 hours and sometimes 7 hours or more.
So, if it is running for more than 4 hours I stop the Job and I run the procedure from a Query Window and it never takes more than 2 hours.
Can anyone help me identify the problem ? I want to run from the Job and not to worry about it.
Some more information:
- It is SQL 2000 Enterprise with SP4 in a Cluster (It happens the same way in any node).
- The SQL Server and SQL Agent services run using a Domain Account that have full Administrative access.
- When I connect to a Query Window I also use a Windows Account.
- There is no locks or process bloking or being blocked while the job is running.
- Using the Task Manager the processor activity is ok, no more than 30 % in any processor.
Transact SQL :: Stored Procedure Executed At The Server?
Nov 5, 2015I 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 ?
View 2 Replies View RelatedViewing Commands Being Executed On Sql Server In Real Time
Sep 19, 2006I 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
View 2 Replies View RelatedServer Options Are Not Being Executed From Local Programatically Code?¿
Aug 16, 2006Dear all,
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.
Thanks in advance,
SQL Server 2012 :: How To Get All Queries That Executed In Server For Day
Feb 27, 2014In 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.
View 7 Replies View RelatedSQL Server 2008 :: Track All DML Statements Executed From SSMS Into A Table
Apr 1, 2015I 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.
View 8 Replies View RelatedSQL Server 2014 :: How To Roll Back All Changes If Not All Statements Executed Successfully
Oct 15, 2015I 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.
View 5 Replies View RelatedSQL Server 2008 :: Find Out What Data Was Changed By A Stored Procedure After It Was Executed?
Jul 22, 2015isn'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.
View 3 Replies View RelatedSQL Server 2014 :: Find Out Last Executed Date For Any Stored Procedure In Database
Oct 6, 2015Is it possible to find out the last executed date for any stored proc in the database using system tables or writing any other query.
View 2 Replies View RelatedQuery Engine Error
Jan 4, 2007Can anyone tell me why I am getting this error when I try to view my report:
Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][SQL Sever] Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=,>,>= or when the subquery is used as an
SQL CE 2.0: Calling Engine.Dispose() After Every Query?
May 19, 2006After making several hundred queries against a SQL CE 2.0 database (through NetCF/ADO.NET), I begin getting a SqlCeException: "Not enough storage is available to complete this operation."
Microsoft speaks to this situation in the following hotfix:
http://support.microsoft.com/?kbid=827837
When I contacted them to receive the hotfixed ssce20.dll, they described the problem as SqlCeDataReader and SqlCeDataAdapter not releasing their memory resources after they went out of scope. In addition to using the hotfixed binary, they also advised me to call SqlCeEngine.Dispose() after every query to force SQL CE to release resources, as shown in the "finally" block of the code below.
I have a couple of questions about this:
(1)
Will this cause a lot of performance overhead for me, especially if my application makes frequent queries using the following code?
(2)
Can a cache an instance of SqlCeEngine and call Dispose() on that cached instance repeatedly, so I can avoid having to instantiate a new SqlCeEngine each time?
SqlCeConnection conn = null;
try
{
conn = new SqlCeConnection(connectionString);
dbCmd.Connection = conn;
dbCmd.Connection.Open();
dbCmd.ExecuteReader(CommandBehavior.CloseConnection);
<use the reader>
}
finally
{
dbCmd.Connection.Close();
// Add the following code to release resources?
SqlCeEngine engine = new SqlCeEngine(connectionString);
engine.Dispose();
}
DB Engine :: Query Tuning - IN Operator
Aug 26, 2015I have a the following query which takes long time
DECLARE @ACCOUNTS TABLE(ACCOUNT_ID INT)
INSERT INTO @ACCOUNTS
SELECT ACCOUNT_ID FROM ACCOUNT
WHERE A_DESCR in ('AA', 'BB', 'CC', 'DD', 'EE', 'FF', 'GG')
I used the following key words: sql server query tuning in operator
DB Engine :: One Query Using Up Whole TempDB (50GB)?
Aug 18, 2015I have a query that is using up the whole TempDB (50GB) and still failing because the disk runs out of space.
The script has SELECT FROM SELECT FROM SELECT etc. and in each select it also seems to have joins.
I can also see a few UNION and UNION ALLs.
Is this normal for one query to use up that much space?
If I wanted to make this better where would I start?
Last Query Statement Fired Into The MSSQL Engine
Jan 8, 2007Hi,
How can you get the Query Statement of the last executed SQL command.
I am not quite sure but I do remember coming across such a command ( maybe an undocumented one).
Thanks for your help and pointers.
My Best wishes for the new year to all the folks in the forum. Wishing you greater days ahead.
Warm Regards,
Ranjit S Hans.
---------------------------------------------------------------------
Everywhere is a walking distance if you have the time - Steven Wright
DB Engine :: How To Use Particular Query In Buffer Cache Permanently
Aug 3, 2015I've a spatial (GIS) Data which is used frequently insertion, updation.
5 lakh records insertion in daily basis. when I trying to generate reports last 3 days or one weak, it takes 20-30 minute.
very disappointing while playing with clients. how to boostup and perform fast.
I think as so once we set query plan in buffer permanently then i would be faster than ever.
How To Force The Query Engine To Use An Index With An OR Clause?
Nov 16, 2007I have the following table:
Create Table Item(
I_Code NVarChar(40) Primary Key NOT NULL,
I_MatID NVarChar(40),
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20),
I_CompanyFound nvarchar(20),
I_Info1 nvarchar(55),
I_Acquired nvarchar(35),
I_Info2 nvarchar(55),
I_Info3 nvarchar(55),
I_Date DateTime DEFAULT GetDate()
);
Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);
Create Index ind_Item_CompanyFound on Item(I_CompanyFound);
create Index ind_Item_i1 on Item(I_Company,I_CompanyFound);
create Index ind_Item_i2 on Item(I_CompanyFound,I_Company);
Now this query DOES NOT use index:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' or I_CompanyFound='102' )
While this one use:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' )
UNION
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_CompanyFound='102' )
Both return the same rows. Is this a bug? I found the following:
http://support.microsoft.com/kb/223423
Some feedback?
Thanks
DB Engine :: Query With WHERE Condition That Lasts Forever
May 27, 2015I've a complex view on a SQL 2014 Enterprise Edition. If I query the view with:
SELECT * FROM myComplexView
it takes 14 seconds to completes
if I want a subset of the result and I run the query with a WHERE clause:
SELECT * FROM myComplexView WHERE [Season]='A16'
The query never completes (I've waited 10 minutes and then cancelled the task).