How Long Until SQL SP Is Executed

Mar 11, 2008



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?

View 1 Replies


ADVERTISEMENT

Same DTS Fails Executed As Job ,but Run Fine When Executed From DTS Designer

Mar 13, 2002

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.

View 5 Replies View Related

Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.

May 26, 2008

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.

View 4 Replies View Related

How Can I Know When A SP Was Executed?

Apr 18, 2007

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.

View 6 Replies View Related

Why Is This Code Executed Twice?

Feb 22, 2007

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()

View 2 Replies View Related

Sprocs- What Actually Got Executed?

Sep 21, 2005

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 Related

DTS Hanging When Executed Via A Job

Feb 12, 2001

I 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.

View 1 Replies View Related

Trace Executed SQL

Jun 29, 2004

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.

View 1 Replies View Related

How To Get The Last Query Executed?

Feb 9, 2004

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 Replies View Related

Begin End - Not Executed

May 14, 2008

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;

View 9 Replies View Related

Log All Commands Executed In QA

Jul 23, 2005

Hi All,Is there way that commands executed in the query analyzer get loggedautomatically?TIAJoriz

View 3 Replies View Related

How SQL Commands Get Executed?

Apr 17, 2007

When we send any SQL command to database engine , how exactly it gets executed?

View 7 Replies View Related

DTS Executed As A Scheduled Job Fails

Mar 6, 2003

I 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!

View 3 Replies View Related

Package Fails When Executed By The Job

Dec 12, 2005

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:

View 5 Replies View Related

Jobs Executed In The Past

May 16, 2008

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

View 2 Replies View Related

How To Tell How Many Times An SQL Executed In V7 Or V2000?

Feb 21, 2006

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 Related

Determine The Last Time SP Was Executed

Jul 20, 2005

Hello -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 Related

Percent Of Executed Procedure

Jul 20, 2005

Hi!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 Related

How Stored Procedures Are Executed

Apr 20, 2007

I 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).

View 6 Replies View Related

Script Component Not Executed

Jun 30, 2006

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

View 1 Replies View Related

What Order Are Datasets Executed?

Aug 4, 2005

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 Related

Return Executed Value Of Field

Feb 11, 2008

I 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?

View 7 Replies View Related

Updates Executed Regardless Of Conditional?

Jun 30, 2006

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

View 3 Replies View Related

When Does ConnectionManagerBase.Validate() Get Executed?

Feb 13, 2008

[Microsoft follow-up]

Hi,
I'm pondering building a custom connection manager and within the Validate() method I want to initiate a connection to a SQL Server instance, check something out, and then close he connection at the end of the method. Here's the code I have so far:




Code Snippet
public override Microsoft.SqlServer.Dts.Runtime.DTSExecResult Validate(Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents infoEvents)
{
DTSExecResult execRes = DTSExecResult.Success;
if (String.IsNullOrEmpty(_serverName))
{
infoEvents.FireError(0, "SqlConnectionManager", "No server name specified", String.Empty, 0);
execRes = DTSExecResult.Failure;
}
else
{

//Establish a connection and check that it is pointing to an MDM DB
SqlConnection sqlConnToValidate = new SqlConnection();
sqlConnToValidate.ConnectionString = this._connectionString;
try
{
sqlConnToValidate.Open();
if (!IsMDMDatabase(sqlConnToValidate))
{
execRes = DTSExecResult.Failure;
}
}
catch (Exception e)
{
infoEvents.FireError(0, "MDMConnectionManager", e.Message, String.Empty, 0);
execRes = DTSExecResult.Failure;
}
finally
{
if (sqlConnToValidate.State != ConnectionState.Closed)
sqlConnToValidate.Close();
}
}
return execRes;
}






I'm worried about the overhead of establishing a connection every time Validate() is called. So the questions are


When does Validate() get called?

Does it get called for every component/task that uses it?

Does anyone suspect that what I'm doing here is necassarily a bad thing or not?

Thanks
Jamie

View 5 Replies View Related

T-SQL: Different SELECT Executed Depending On Condition.

Apr 30, 2004

I'm trying to execute a different SELECT statement depdning on a certain condition (my codes below). However, Query Analyzer complains that 'Table #Endresult already exists in the database', even though only one of those statements would be executed depending on the condition. Any ideas as to a work around? I need the result in an end temporary table.


IF @ShiftPeriod = 'Day'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkDays = 1
ELSE IF @ShiftPeriod = 'Night'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkNights = 1
ELSE IF @ShiftPeriod = 'Evenings'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkEvenings = 1
ELSE
SELECT * INTO #EndResult FROM #NursesAvailable

View 1 Replies View Related

Is There A Way To Find Our When A Specific Procedure Was Last Executed.

May 29, 2002

Hi,

Is there any system tables or something , which one can query to get when a specific procedure was last run.

Thanks for you help.

View 1 Replies View Related

How To Get Recordset From Only Last SELECT Stmt Executed???

Feb 14, 2005

How to get recordset from only last SELECT stmt executed???

this is part of my code.. in T-Sql


Create proc some mySp
...
AS
...
set nocount on
if (@SelUserID is null)
select 0
else if (@SelUserID = @userID)

select 1

else
begin

select * -- select a.
from dms_prsn_trx_log
where @incNo = ult_incid_no and
prsn_trx_no = 10 and
trx_log_txt = @logText
if (@@rowcount != 0)
set @isForwardedByUser = 1
select 2 -- select b. I NEED This value.

end
set nocount off
GO

here it executes select a, b.
But, I want mySp return last executed select result. which is here "select 2"

I thought set nocount ON does that, but it doesn't.
How can I do???

View 2 Replies View Related

Trapping Sqlcmd Errors When Executed From A Job

Jul 17, 2007

Hi I am running some scripts in files using sqlcmd via a SQL Server Agent job. If sqlcmd generates an error (for example if it is unable to connect) then the job fails. However, if the T-SQL within the script is invalid (syntax, name resolution etc etc) the job completes reporting success. If sqlcmd is invoked directly via the query window then no error is raised however there is a result set returned reporting the error. Anyone know why and whether is it possible to get the error to be recognised by the job? invalid_sql.sql--The below is not actually valid SQL.do SOME stuff, innit! sqlcmdEXEC master.dbo.xp_cmdshell "sqlcmd -S my_server -i C:invalid_sql.sql" Cheers

View 2 Replies View Related

SnapshotAgent Cant Be Executed, Not Sufficient Rights

Sep 12, 2007

Hi all,

i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.

Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful

2. Creating the publication: executed as User2(db_owner)
2.1 publication.Create(); ... successful
2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with
agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by
agent.GenerateSnapshot();

And at this point,i got an error message, because the snapshot agent cant be executed ...

2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --------------------------------------
2007-09-12 12:05:46.60 -Publisher EDOM04SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --------------------------------------
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSq lException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
2007-09-12 12:05:46.96

Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role

This scenario is workin completely fine when i execute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!

Does anybody has any resolutions for this problem?
I appreciate any support.
dbhoop

View 3 Replies View Related

Log The Query Executed In Stored Procedure

Jun 19, 2008

hi,

i want log the query executed in stored procedure ..
any way of doing it in sql server 2000

regards.

View 1 Replies View Related

SSIS Packages Not Getting Executed From Client.

Oct 24, 2007

I migrated a few stored procedures from SQL 2000 to SQL 2005. When the packages are run from the box where the SSIS and SQL Server (Windows server) is installed it is running fine without any issues. When run from my local machine (client) where I have installed the SQL Server Client, the same package would not execute and gives me some weird error messages. I am a local admin on the box. Is this a issue with SSIS? Do we have to log onto the box (server) and then execute the packages?


Any help is appreciated.


Thanks !

View 3 Replies View Related

Linked Server: WHERE Clause Not Being Executed

Jul 23, 2005

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

View 2 Replies View Related

Bad Executed Plan And Wrong Result By SQL

Jul 23, 2005

I have one query that executes many times in a week.I created one Maintenances plan that Rebuild all index in my Database thathas been executed at 23:40 Saturday until stop finished at Sunday.However at middle of week (Wednesday or Thursday), that query don’t returnresult like that must be. The time exceeded and the result are total wrong.I compare the normal executed plan and the “crazy” one that SQL create tomount result.The normal is nested with index seek (very fast, the wrong is Merger withhash aggregate (very slow). After Index Rebuild, the executed plan bringresult that must be, but when the merge plan are executed with many updateson that tables (SAM_GUIA_EVENTO and SAM_GUIA), at middle of week, theresult are total wrong, with many rows back.I recommended Index Seek force by coalesce function on one columnaggregate, but everyone here were very panic with that behavior of SQLServer.Please , anyone help me to explain that!Krisnamourt!P.S: Attachments :--Force Index Query with coalesceSELECT count(*)FROM SAM_GUIA_EVENTOS E,SAM_GUIA GWHERE G.PEG=736740AND E.GUIA=coalesce(G.HANDLE,G.HANDLE) AND E.CLASSEGERENCIALPAGTO is NULL--Normal QuerySELECT count(*)FROM SAM_GUIA_EVENTOS E,SAM_GUIA GWHERE G.PEG=736740AND E.GUIA=G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL--Message posted via http://www.sqlmonster.com

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved