Hello,
In my database (SQL Server 2005), some data were inserted from a external application.
In order to validate the data , I want to get the SQL statment executed by the application.
Is this possible?
Thanks
Robert
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.
Hello,
I have a stored procedure that outputs login token and user token information. The stored procedure has
WITH EXECUTE AS CALLER specified.
When I execute the stored procedure from Management Studio I get the following output from the stored procedure
<login_token pid="267" sid="AQUAAAAAAAUVAAAAdbl1VI3r6l4jX2Nr0AYAAA==" name="MYDOMAINjoe" type="WINDOWS LOGIN" />
<login_token pid="2" sid="Ag==" name="public" type="SERVER ROLE" />
<login_token pid="3" sid="Aw==" name="sysadmin" type="SERVER ROLE" />
<login_token pid="257" sid="AQIAAAAAAAUgAAAAIAIAAA==" name="BUILTINAdministrators" type="WINDOWS GROUP" />
... (more groups)
<user_token pid="7" sid="AQUAAAAAAAUVAAAAdbl1VI3r6l4jX2Nr0AYAAA==" name="MYDOMAINjoe" type="WINDOWS LOGIN" />
<user_token pid="0" name="public" type="ROLE" />
<user_token pid="5" sid="AQUAAAAAAAUVAAAAdbl1VI3r6l4jX2NrCAUAAA==" name="MYDOMAINPeople" type="WINDOWS GROUP" />
<user_token pid="16" name="approleDirector" type="ROLE" />
<user_token pid="16384" name="db_owner" type="ROLE" />
When I execute the stored procedure through my application (IIS application connecting to SQLServer 2005 through SQL Native Client - not .NET)
I get the following
<login_token pid="267" sid="AQUAAAAAAAUVAAAAdbl1VI3r6l4jX2Nr0AYAAA==" name="MYDOMAINjoe" type="WINDOWS LOGIN" />
<login_token pid="2" sid="Ag==" name="public" type="SERVER ROLE" />
<login_token pid="3" sid="Aw==" name="sysadmin" type="SERVER ROLE" />
<login_token pid="257" sid="AQIAAAAAAAUgAAAAIAIAAA==" name="BUILTINAdministrators" type="WINDOWS GROUP" />
... (more groups)
<user_token pid="1" sid="AQUAAAAAAAUVAAAAdbl1VI3r6l4jX2Nr4QQAAA==" name="dbo" type="WINDOWS LOGIN" />
The login token is the same but the user token is dbo instead of the actual user.
What am I doing wrong?
Thanks.
When my SQL Agent job completes successfully, I'd like to send a notification to QlikView. The idea is to stop QlikView having to constantly check if the job has been run, and just get notified when it needs to update its data.
I don't know how to start. Can the job notifications push a success message to QlikView? I don't think I want to send an email, but something that indicates to QlikView to update the tables.
I have the need to restore a database to a user's laptop on a dailybasis. This has been no problem using Enterprise Manager. I found astored procedure to run the restore and tried to call it from a visualbasic program. While I can connect to the database, I get an errortelling me "exclusive access could not be obtained because thedatabase is in use".Does this mean the only way to run the restore is via EnterpriseManager?Thanks,Tom
View 1 Replies View RelatedI have a custom external application that executes some packages. I would like to display the execution progress as it would appear in the Progress tab of the SSIS designer. How would I do that? Any help is appreciated.
View 1 Replies View RelatedCan you put is a call withing a Data Flow that will call an External Application and pass a parameter to that application IE say a command line app and then take the output and assign it to back to the flow as a "column" or whatever for that row... IE I want to take a value push it to an external app and then the output from that app I want to insert it into another field for that row in the new table I am moving the data to.
View 4 Replies View RelatedHi, all here,
I am having a question about the language in which external managed code written can interact with SSIS? Is it only applications wrriten in .NET-compliant language can interact with SQL Server Intergration Services packages?
Thanks a lot in advance for any guidance and advices for that.
With best regards,
Yours sincerely,
Can a stored procedure be executed from within a select statement?
Given a store procedure named: sp_proc
I wish to do something like this:
For each row in the table
execute sp_proc 'parameter1', parameter2'...
end for
...but within a select statement. I know you can do this with stored functions, just not sure what the syntax is for a stored procedure.
I'm evaluating SQL 2005 Everywhere Edition for use by our desktop application. I'm a traditional SQL Server developer and I rely heavily on stored-procedures to encapsulate basic data manipulations across multiple tables and inside multi-statement transactions.
I was excited to see an in-process version of SQL released and my thought was "this is great... now I can ditch the tediousness of individual OLEDB/.NET commands, and write batches of T-SQL and just focus on the data manipulations". But, alas, it seems I cannot. Why is SQL Everywhere Edition limited to executing a single SQL statement at a time?
For example, my application would like to update mutlipe rows in one table, delete multiple rows from another, and insert multiple rows into a third. I can do that with 3 T-SQL statements in a single small batch in a very readable way with full blown SQL Server. (and I can put that batch in a stored procedure and re-use it efficiently later.) If I contemplate how to do that with OLEDB and the single statement limitation of SQL Everywhere, it's a lot more code and a lot less appealing/maintainable. I want as much of my app to be using declarative code and as little as possible tied up in tedious OLEDB calls. Is this not possible with SQL Everywhere Edition?
I have written the following code:
SET NOCOUNT ON
DECLARE @RowCount int; SET @RowCount = 0;
Begin Try
Begin Transaction
--------------------------------------------------------
-----Table Name: AlertsStaticRecord_Archive
-----Column Name: AlertID
--------------------------------------------------------
ALTER TABLE [AlertsStaticRecord_Archive] ALTER COLUMN [AlertID] int NOT NULL;
[Code] .....
But, when I execute these batch, I am getting error:
Msg 8111, Level 16, State 1, Line 11
Cannot define PRIMARY KEY constraint on nullable column in table 'AlertsStaticRecord_Archive'.
Msg 1750, Level 16, State 0, Line 11
Could not create constraint. See previous errors.
Because, the first ALTER statement is not getting executed.
Hi everyone,
I wonder how one can see or save the real sql statement that was executed by some stored procedure (including the one that used supplied parameters)?
Just need that for debugging purposes...
thanks!
I am new to SSIS programming, so bear with me if my question seems naive to you gurus. I have a situation that needs to set the data source for a data flow from external .NET application ('external' means that the application will run on different process than the SSIS). I am trying to set the data source on which the data flow works from my C# application in a DataSet format. Ideal solution is not to save the DataSet to any file on harddisk (I know that will work, but has the overhead of writing, reading and managing the temp file). What I want to achive is that the business logic of picking data for SSIS Data Flow to process is controlled inside my C# application, the Data Flow just does what it does best - Transformation. Have any of you successfully done this before?. Thanks!
View 1 Replies View RelatedI 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 gurus,I just started to look at a very slow-running SQL statementgenerated by an application (Siebel). I spooled the SQL from theapplication, replaced the bind variables by their values, and tunedfrom the Query Analyser. But after awhile, I realized that thestatement using bind variables and the same statement using the valuesinstead of the bind variables often have completely differentexecution plans! Is that normal? Can someone tell me how the SQLServer treats bind variables. Don't worry about being too technical,I'm an Oracle DBA/developer.ThanxDaniel
View 1 Replies View RelatedIn order to feed a fact table of a dwh from a staging table I'm using the MERGE statement in order to control insert and update operations. It is possible that the staging table has duplicate rows respect to the fields controlled in the merge condition:
When I run the first time the MERGE statement unwanted rows could be inserted in the fact table.
Does the MERGE statement allow to manage this case or do I need to filter data from the staging table before to write them into the fact table?
I get the below error on the event log of my application server which uses SQL database.
Details: RuleId:a811dcbc-4c5b-d9de-592b-f01e17fc0e9a. HealthServiceId:a5f70248-b545-4d35-7c84-e7aa87610ee4. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Alert_BaseManagedEntity". The conflict occurred in database "OperationsManager",
table "dbo.BaseManagedEntity", column 'BaseManagedEntityId'.
The statement has been terminated.RuleId:a811dcbc-4c5b-d9de-592b-f01e17fc0e9a. HealthServiceId:a5f70248-b545-4d35-7c84-e7aa87610ee4. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Alert_BaseManagedEntity". The conflict occurred in database "OperationsManager", table "dbo.BaseManagedEntity", column 'BaseManagedEntityId'.The statement has been terminated..
Details: RuleId:a811dcbc-4c5b-d9de-592b-f01e17fc0e9a. HealthServiceId:a5f70248-b545-4d35-7c84-e7aa87610ee4. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Alert_BaseManagedEntity". The conflict occurred in database "OperationsManager", table "dbo. BaseManaged Entity", column 'BaseManagedEntityId'.The statement has been terminated..
have SQL Server 2005 std edition SP1 installed on Windows 2003 Std edition .Configured Transactional (single Publisher and no clustered environment.)
Replication past two months working fine, Now
1.Distrib.exe application err is coming.
Due to which my job is failing (Distributor to Subscriber).
Iam attaching thw file.
Thanks
Sandeep
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 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 Relatedhello, 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 Related