Logging SQL Queries
Dec 16, 2007Hi,
I´m currently playing around with ASP.NET.
Is there a way to log all Queries that are send to the SQL-Server? Something like the query.log of a Mysql.
ADVERTISEMENT
Logging Sql Queries
Jul 20, 2005In SQLServer 2000, I am sure there was a way that you configure thedatabase to log to a file each query that gets run against it.However, I cannot seen to find where to set this up in EnterpriseManager.
View 2 Replies View RelatedLogging To Event Viewer Fails But SQL Server Logging Works OK - Why?
Jun 18, 2007Greetings,
I am developing a package on my local workstation. I have defined two logging service providers. One is for SQL Server and the other is for the Windows Event Log. I am using the Dts.Log method in a script task to write log entries.
Logging is working properly with the SQL Server provider and rows are being inserted into the sysdtslog90 table. However, the only events that are being logged in the Windows Event Log are the package start and end events which I believe SSIS is doing automatically anyway.
Is there something I need to do to enable WIndows Event Log logging other than defining a log provider and making sure it is checked active? Won't SSIS write to two different logs with one Dts.Log call? Any ideas on what might be going wrong with my approach?
Thanks,
BCB
Logging Package Name For Any Event In Sysdtslog90 Logging Table
Oct 17, 2007
Hi,
I decided to use the SQL Server log provider to store logging data of all my Integration Services packages. I also created some reports about this data for operating purposes.
I have a problem occurs the name of the executing package is not always written to the log,but the name of the single task which failed. But that is not very useful information for operating, because I do not see any chance to get the name of the package by the information which is logged in the sysdtslog90 table in the database which I defined for SSIS Logging.
How do I configure the package to always log the package information into the table, too?
Best regards,
Stefoon
Tie Together Custom Logging And SSIS Logging?
Sep 12, 2005I recently read the project real ETL design best practices whitepaper. I too, want to do custom logging as I do today, and also use SSIS logging. The paper recommended using the variable system::PackageExecutionId to tie the 2 logging methods together.
View 4 Replies View RelatedParameterized Queries Running Slower Than Non-parameterized Queries
Jul 20, 2005HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 Replies View RelatedLogging In
Dec 6, 2006How can I check from database username and password? It doesent need any special authentication, just a lookup through the database and if the user exist than continue with the next page.Thanks
View 2 Replies View RelatedLogging Who Did What
Oct 1, 2004I have a web application accessing a SQL Server database (the ususal stuff).
I want to be able to log who did what on which table. I need to display this information on the web application. Is there an easy way of doing this, rather that making duplicates of a lot of data?
The best way I have thought of so far is making a new table with the following fields:
Table_Changed
Table_Primary_Key
Old_Field_Value
New_Field_Value
User
Date_Changed
Every time someone changes something, it is logged in this table, so that, at any time, I can display who changed what.
I have one more question. If I do do it this way, is there a way of getting the primary key value of any table? E.G. could I do something like this_table.primary_key.value ?
Jags
Logging
May 30, 2001Is there a way to produce a log of all SQL statements hitting a database in a given range of time by a specific SPID? Obviously the SQL Server activity logs do not go into that much detail, except when errors are produced or a change is made to a system table. Is there a setting to add more detail, or to log a specific SPID's actions, or maybe a third party software that will give me what I am looking for?
Thanks. JT
Logging ?
Aug 22, 2000does SQL 7.0 have any built in logging capabilities to identify row level actions by operator. For instance, can it tell me that a particular user deleted or inserted a row? How would I tell it who the operator is?
View 2 Replies View RelatedDML Without Logging
Jun 11, 2004Is there a way to stop logging DML? I have a large delete and I dont want to log the operation, is it possible?
View 2 Replies View RelatedSQL Logging
Dec 9, 2004Hi,
is there any chance to log all incoming SQL-statements with date/time and duration ?
Thx for help.
dajm
Logging
Aug 20, 2007Is there anyway to log every incoming command, INSERT/UPDATE/SELECT/DELETE to a seperate database table?
Thanks
Logging Changes
Mar 27, 2008I've been asked to write a trigger that will basically log changes to certain fields in certain tables, then create a front-end application where the user will be able to review the info. The front-end app. is not a problem for me - the trigger is. I have found example of how to do this on Update when it's a complete row you want to log, but not a specific field. In addition, I also need to know if someone is attempting to read certain data and who that user is. If the user is not someone that is allowed to read the data, then I need to send an email alert.
I believe it's possible to do the above (despite my lack of knowledge :) -
Does anyone know where I can get more information on how to accomplish the above - or where to start looking?
Thanks to any who can guide me in the right direction.
Logging In To MSDE
Dec 16, 2003I have a problem acessing MSDE. My server runs at "NT AUTHORITY/NETWORK SERVICE" so it is not allowed to connect through the windows authentication feature. It seems that the password for the "sa" user account was changed during setup, after reading the logs from the setup I can see that it was changed but I cannot see what it was changed to. How would I set up a new account that I could use to access the server though SQL Authentication???
David Legg
Transaction Logging
Oct 20, 2005Lets say I have version 1 of a database - DB1. I am creating the second database, DB2.
What I need is a log of all the SQL statements that where used to
change DB1 into DB2. This means recording both what happened in the GUI
and in the SQL Query Analyser.
Is there a way I can do this? I know SQL Server has a transaction log
somewhere. Is there a way to set this to output all the changes made
from a set date on a database into a SQL log file?
Thanks in advance for any help.
Jagdip
Inserting Without Logging
Jan 31, 2002Other than doing bulk insert is there a way to insert in SQL 7 without logging the transaction in order to speed up the process?
If so what command(s) allow this?
DTS And Transaction Logging
Jan 29, 2001If you use the import feature of DTS to import data into various tables and your database is setup with truncate log on checkpoint set to false, will SQL Server log those transactions?
Thanks,
Veronica
Deadlock Logging
Nov 8, 2001I have (amongst many) an overnight SQL stored procedure that occasionally takes over an hour to run compared to its usual 2 minutes. It does the same each night, as does everything around it. (N.B. The job truncates and reinserts data into a table.)
I'm just wondering if a user request during the day that reads the table perhaps leave the PID open and thus a lock open with it. Then, perhaps my SQL job comes along and waits for the lock to release before it performs the truncate.
Couple of questions:
1. Could this happen?
2. If so, is there a log anywhere to suggest that a deadlock situation arose and PID x was the loser?
Thx in advance.
Andy Richardson
Is Any Way To Reindex 27 Gig Db Without Logging
Sep 25, 2002Hello everybody.
1. I have 28 Gig database with 4 tables above 4 Gig each with very bad
fragmenataion, each table has between 3 and 5 indexes
2. Database set for full recovery and I use custom log Shipping to restore
db on stand by server every 15 min.
I tried to run DBCC INDEXDEFRAG on one index on 4Gig table .
following took place
1. It took 4 hrs to complite DBCC INDEXDEFRAG
2. log shipping fail.
3. log file size of 2 Gig generated after DBCC INDEXDEFRAG complited
I tried to run drop and create clustered index on table it create same
problem - log growing, log shipping fails
(if log shipping fails and stand by database get suspended it will take 6- 8
hrs to restore it from backup and apply all logs)
So my question is
What would be best way to rebuild- reindex - defragment 28 Gig database
when it set to fully recovery and log shipping
Thank you
Alex
DTS Progress Logging
Nov 22, 2002I need to logg progress of a DTS job. How do I do that? I'd like the progress to be stored in a file and emailed to me.
I need to track progress of the transfer of each table in two databases.
Update With No Logging
Jul 6, 1999I would like to update a SQL 6.5 table with modifications to certain columns, but I don't want any of it logged. Is there a way to do this? Any help is appreciated.
View 1 Replies View RelatedError While Logging
Apr 15, 2008Hi all,
I am getting the following error when I log into SQL server using MS SQL Server Management Studio.
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.) (.Net SqlClient Data Provider)".
I tried to allow remote connections using Surface Area Configuration, but it is not allowing me to do so.
I am not sure what I need to do. Anybody, please advice.
Thanks & Regards
Logging Blocks
Apr 23, 2008I would like to log blocks to see what causes it.
One hack of a way to do it is to run code similar to that which populates the blk column in sp_who; on a job every 5 seconds. It would store the results of code similar to the sp_who output whenever any of the rows has a value greater than zero in the blk column.
This way, I can see what was going on at the time there was a problem.
Does anyone have a better strategy for this?
Logging Server
Apr 24, 2007Hi Guys hope someone can help with this one, I am working on SQL 2000 and having problem with logging a new server instance to be run in query analyzer let me explain,
I have the default sql server and then also an instance called sqlone on the same machine for study reasons.
However after loading the new instance on and then registering it in enterprise manager it appears fine on there, but I am having problem only in query analyzer the sqlone instance does not appear in the drop down menu so that I can select too work on that server
Only reason I need this is as I am practicing adding linked servers and without the second instance being visible it won't work correctly.
For other infomation the instance ok in the enterprise manager, server manager and then in the computer management in the administrative tools in the control panel.
Any advice please people
Thanks
Neil G
Error Logging
Jun 18, 2007Hey all
I'm using the Execute SQL Task to import error messages into a table. I'm using the following:
"INSERT INTO SSISLog (EventDescription)
VALUES (" +@[System::ErrorDescription]+")"
I've got an import task with deliberate errors in the file to be imported to test the error logging task. When I execute it, I get the following messages:
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". )" failed with the following error: "The name 'An' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (There was an error with input column "IssueNo" (94) on input "Destination Input" (68). The column status returned was: "The value could not be converted because of a potential loss of data.". )" failed with the following error: "The name 'There' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (The "input "Destination Input" (68)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (68)" specifies failure on error. An error occurred on the specified object of the specified component. )" failed with the following error: "The name 'The' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (The ProcessInput method on component "Destination - tbl_clients" (55) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. )" failed with the following error: "The name 'The' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (Thread "WorkThread0" has exited with error code 0xC0209029. )" failed with the following error: "The name 'Thread' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any ideas?
Thank you in advance
Rupa
Enable Logging
Mar 8, 2008All, I am using SSIS in sqlserver. I wanted to enable logging in SSIS. I just want to log all the activities in the log file. How can i enable logging in SSIS? I would appreicate if any one could answer for this. thanks in Advance.
View 1 Replies View RelatedLogging Uses Of SELECT ??
Jul 20, 2005Scenario:-I'm brand new to SQL Server 2000-I have a vendor supplied application running on SQLServer 2k.-I need to log which of my users has seen which data (from aparticular table). It's a healthcare privacy thing.-The application does not do this and the vendor has no interest inimplementing it, though it seems pretty easy if one has the app code.So I don't control the application or its code but I need to catchwho's seen what. Note that I don't have to catch it all. In otherwords, I don't need to catch every instance, only whether user X hasseen patient Y's data at all. So I'm talking about a fairly small dataset. The end result I'm heading toward is a case in which a patientdemands to know who's seen his health records. I go to a db table andsay "select username from <viewlogtable> where patient_id = XXX".How do I do that? It'd be easy if I could it were possible to write aselect trigger, or if I had some hooks into the app, but as it is I amstumped. This is trickier than logging updates and inserts...Thanks,John
View 3 Replies View RelatedHow To Stop Logging
Jul 20, 2005Hi,I am doing some resource hungry tasks (some extraction and loadingthrough DTS), for which each time the SQL Server Log files gets filledup!Is there any way to stop the logging (like as during restore)?Thanks in advance.-surajit
View 1 Replies View RelatedLogging Errors
Jul 20, 2005SQL Server 2000 BE, Access 2002 FE.I want to write a stored procedure, that will among other things logerrors to a table, I want to be able to report a summary of work doneand errors to the user and let them decide whether to commit orrollback. I so far foresee a couple of problems.1. Inside a transaction, I write to the error log table, will I be ableto read it back again before commit/rollback?2. If I rollback, my error log gets rolled back too.Is it possible to log the errors outside of the current transaction?
View 2 Replies View RelatedFunction Logging
May 8, 2008 In our stored procedure tracking world, For every stored procedure which is called I call a log entry SP which logs the user and the parameters passed by the user in a table to track who executed the SP and at what time. Can I do the similar thing in a FUNCTION? I tried doing a insert into table name and also calling SP within function but it failed. Is there a limitation ? If so, anybody with a workaround on how can function execution tracking be handled?
Thanks !
Logging Not Working
Jan 17, 2008Hi,
I have several packages where I set up logging to SQL Server.
Originally, I had my logging pointing to Server A, database A (the same server that stores the packages).
Later, I changed the logging to point to Server B, database B (but the packages still are on server A).
I granted execute on "sp_dts_addlogentry" to my OLE DB connection user on Server B. When I run a package, it created the logging table in the form: [OLE DB connection user].sysdtslog90.
Everything seems ok (although on Server A, the sysdtslog90 table belongs to "dbo", which doesn't make any sense to me, because I've only connected to the database through my OLE DB connection user in both cases - so why does one table belong to the "OLE DB connection user", and the other "dbo"?
However, when I run a select on user.sysdtslog90 on Server B after running my packages, there's no entries. I know that at minimum, there should be "start" and "end" package entries. So the point is, the logging is working when I point to Server A, but not Server B.
What's going on here?
Thanks
Logging In SSIS
Jan 24, 2007Hi:
Just implemented logging on my package, and the type of logging is to a text file. Any ideas on where should i store my log folder/file, so when I do my build and deploy, I end up moving the folder as well. Do I have to manually do that? How would that work? What if I save the log file outside of my project folder? Where is the log folder/file path specified? Thanks.