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?
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?
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?
I 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.
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
How 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
I 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 ?
Is 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?
does 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?
I'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.
Hi,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.
I 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???
Lets 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?
Other 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?
If 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?
I 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?
Hello 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
I 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.
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.
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?
Hi 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.
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.
All, 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.
Scenario:-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
Hi,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
In 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.
SQL 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?
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.
