Hello, 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 RepliesJust 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 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.
hi,
i want log the query executed in stored procedure ..
any way of doing it in sql server 2000
regards.
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.
View 5 Replies View RelatedHow SQL Query/ Stored procedure are being executed by SQL Server Engine ?
View 1 Replies View RelatedI 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.
My 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 RelatedHi ,
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
Hi 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 RelatedHi 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.
Hi 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
Performance 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.
Hi everyone, I've got a question. How can I Know when was the last time that a Stored Procedure was executed?
I'm thanking for your help.
Oscar.
Hi,
this code inserts twice the same record. I thing it is due to the "Selet Scope_Identity" in the sqlcommand.If i remove the Select part, the inserts occurs only once. If i remove the line "comd.ExecuteNonQuery()", then the inserts also occurs once.
Is there something wrong in my code?
ThanksT.
Dim connection As SqlConnection Dim comd As SqlCommand Dim connectionstr, sql As String Dim iden As Integer connectionstr = ConfigurationManager.ConnectionStrings("econn").ConnectionString.ToString() connection = New SqlConnection(connectionstr) comd = New SqlCommand() comd.Connection = connection sql = "INSERT INTO table(field,...) VALUES (@fld,...); SELECT SCOPE_IDENTITY()" comd.Parameters.Add("@var1", SqlDbType.NVarChar, 10).Value = txtvnm.Text ... connection.Open() iden = Convert.ToInt32(comd.ExecuteScalar()) comd.ExecuteNonQuery() connection.Close()
I know you can use sql profiler to see what sqlcode actually executed when you run a sproc, but is there any way toget this information in asp.net? After executing a sproc, I'd like to send the sqlcode that was sent, to my Audit class. Is there any wayto retrieve this in asp.net itself?cheers!
View 1 Replies View RelatedI created a simple DTS which executes a VB standard exe that simply writes a string to an ascii file opened as append. SQL Server, the exe, and the ascii file are all on the same NT box (mine). If I execute the DTS myself the process works with no problems. When I attempt to execute the DTS via a job the job hangs with no apparent indications as to what may be the cause of the hangup. The SQL Server Agent is up and running and set to run under the system account. I have applied SQL Server SP3. The same problem was occuring prior to applying SP3.
Does anybody have any idea? All sugestions are appreciated.
Thanks in advance.
Hi folks
Is there any (easy!) way in which I can see the SQL that has been executed. I'm using stored procs that create & execute other stored procs(via sp_executesql). At the early stages there are often trivial errors in the created procs that cause rather general exception messages that do not give much of a clue as to where the error is. (It's tedious to find these in the debugger) ... and was wondering if there's any trace output type option I can turn on to see what sql has been presented for execution.
Thanks.
hello, I have a big problem with a script.. some instructions seems to be not executed. I don't understand. If I execute line perline it's ok - but the entire block no.
Explain me and find the solution:
IF NOT EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ACS_ACL'
AND COLUMN_NAME = 'ZoneUId' )
BEGIN
ALTER TABLE dbo.ACS_ACL ADD ZoneUId T_UID;
UPDATE dbo.ACS_ACL SET ZoneUId = '1' WHERE ZoneUId IS NULL;
print 'end of script'
END
result:
Msg 207, Level 16, State 1, Line 9
Invalid column name 'ZoneUId'.
the error is on line:
UPDATE dbo.ACS_ACL SET ZoneUId = '000000001' WHERE ZoneUId IS NULL;
how is is possible because the preceding line is
ALTER TABLE dbo.ACS_ACL ADD ZoneUId T_UID;
Hi All,Is there way that commands executed in the query analyzer get loggedautomatically?TIAJoriz
View 3 Replies View Related
I have a program that allows various users to login to the sql server. On the login window there is a dropdown that lists all the databases on the server and the user can select which database they wish to login to. To get the list of databases on the server I use a login created for the program we will call 'worker'. The program logs in as worker and runs "SELECT * FROM sys.databases". Before I load the login window, I check to make sure the login worker exists or has been corrupted (in case a user deletes it or changes the password, etc) and if it has been corrupted or deleted, I recreate the login using the "CREATE LOGIN worker WITH PASSWORD = '123' " function.
The problem occurs after I recreate the worker login. The login is created successfully but when the login window appears and logs in as the worker login to get a list of the databases, I get an error saying the login failed for worker. If open the login window again, everything works fine (the worker login isn't created again as it already exists). Further, if I run the same code but put a break point in the code and step through everything, it works the first time.
Is there an amount of time that is necessary to wait for the CREATE LOGIN function to be executed?
When we send any SQL command to database engine , how exactly it gets executed?
View 7 Replies View RelatedI have a SQL 2000 DTS package which executes without errors when run from the DTS design screen or by right clicking the package and selecting execute.
When I schedule the package and get it to run at a certain time or highlight the job and select start, the package comes back with an error saying there was a problem with a transformation.
I have tried scheduling the DTS by right clicking on it and selecting schedule and creating a new job and using DTSRun with an operating system command. I get the same results! Help!
Hai ....,
I have a DTS Package that is used to dispatch mails to all the customers. When I execute the package manually by selecting it and then clicking Execute Package from the context menu, it executes to completion successfully. But when I schedule to execute it using Job, then I get the following error message:
... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnError: DTSStep_DTSExecuteSQLTask_3, Error = -2147220421 (8004043B) Error string: The task reported failure on execution. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task reported failure on execution. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error: -2147467262 (80004002); Provider Error: 0 (0) Error string: No such interface supported Error source: Microsoft OLE DB Provider for SQL Serv... Process Exit Code 1. The step failed.
FYI: This Package is under machine7. When I copy the package to my system (machine3) and execute it using Job, it succeeds :rolleyes:
Please help me out of this problem :confused:
How to find the jobs executed in the past for a perticular period.
e.g. jobs ran between 2-3pm in the past.
I am using sysjobhistory but not really able to use column run_time
------------------------
I think, therefore I am - Rene Descartes
Using the dm_exec_query_stats in 2005, I know I can get the number ofexecutions for a particular sql_handle, but is it possible to get thenumber of execs for a SQL in version 7 or 2000? Also is it possible toget reads/writes/etc in these early versions?Thanks.
View 1 Replies View RelatedHello -I am trying to determine the last time a SP was executed. Does anyoneknow how to do this? I'm trying to cleanup some databases.Thanks!*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies View RelatedHi!How can I get percent of executed procedure in MSSQL Server, lik inEnterprise Manager and/or dbMgr2k ?I use Visual C# and MSDE.Thank's for help, gregory
View 1 Replies View RelatedI was wondering the way in which stored procedures are executed.
For example if I had a stored procedure (A) that executed a sub stored procedure (B).
And two different users where to execute the Stored procedure at the same time what would happen.
Would the second user have to wait while the first user had finished (A and B), dose each user get a copy of the stored procedure or can one user be running stored procedure (B) whilst the other is running stored procedure (A).
Hi
I have a ssis project that contains a parent package and 2 child packages. The parent package loads data from multiple flat files into a database and then kicks off the 2 child packages using separate execute package tasks.
The child package has a data flow.Within the data flow data is extracted from a database. The data is transformed using a script component and then loaded into a second database.
The problem I have is that the second child package is not working. It appears as if the data is being extracted fine.However the script component does not seem to be being executed so the columns that are being transformed are not being changed and so the write to the database fails. When I send the error rows to a database table with all the fields varchar(200) the write completes but the transformed columns are blank.
Also if I put a message box or ComponentMetaData.FireInformation in the script component I get no output.
However when i run this project on my development machine it runs fine but when I run it on the staging server it gives the problems explained above.
Any ideas please????
Thanks
G
I'm running a report that uses multiple datasets. One of which is basically a script written in SQL. I would like this dataset, let's call it code, to execute before the other ones. How can I ensure that this happens?
View 7 Replies View RelatedI have a field in a table that has sql statements as follows;
'Low_Quote_Mortgage_' + REPLACE(CONVERT(VARCHAR, GETDATE(), 10),'-','') + '.txt'
I am trying to create a proc/view to return the data in an executed form ie;
Low_Quote_Mortgage_021108.txt
I tried using a function ;
create FUNCTION [dbo].[DynamicFileConversion] (
@dynamic_filename VARCHAR(100)
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Static_filename VARCHAR(100)
SET @Static_filename = (@dynamic_filename)
RETURN @Static_filename
END
SELECT suptype_id, oen.dbo.DynamicFileConversion(REPLACE(dynamic_filename, '%day%', 'GETDATE()')) AS Link
FROM abc.dbo.ondemand
Didn't work...the only way i can think to do this is by first inserting the data into a temp table then using a cursor to go through each record and linking it back to the table ...just seems really inefficient anyone know a better way?
Greetings all. I hope someone has seen this before. It's driving me up the wall. Here's the scenario. Import a table via bulk insert. Some columns imported need to have the first 6 characters stripped. Write a generic routine to do this. Here's the script:
declare @pattern varchar(10)
set @pattern = 'yn' --only 2 fields are to be used with this table
declare @cnt int
set @cnt = 0
while @cnt < len(rtrim(@pattern)) begin
set @cnt = @cnt + 1
if @cnt = 1 and substring(@pattern,@cnt,1) = 'y' begin
print 'updating f1'
update TempImport set f1 = substring(f1,6,len(f1)-6)
end
if @cnt = 2 and substring(@pattern,@cnt,1) = 'y' begin
print 'updating f2'
update TempImport set f2 = substring(f2,6,len(f2)-6)
end
if @cnt = 3 and substring(@pattern,@cnt,1) = 'y' begin
print 'updating f3'
update TempImport set f3 = substring(f3,6,len(f3)-6)
end
end
The result???
updating f1
Server: Msg 207, Level 16, State 1, Line 10
Invalid column name 'f3'.
Server: Msg 207, Level 16, State 1, Line 10
Invalid column name 'f3'.
Server: Msg 207, Level 16, State 1, Line 10
Invalid column name 'f3'.
Okay, ignoring the fact that the loop seems to run 3 passes, the table only contains 2 fields, f1 and f2. Since the @pattern is only 2 characters long this should not be a problem. The trace shows only 'updating f1' yet all updates are being executed. The 'if' statement seems to be ignored by the updates. What am I missing?
Thanks in advance. Floyd Shadwick