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

View 5 Replies

Logging To Event Viewer Fails But SQL Server Logging Works OK - Why?

Jun 18, 2007

Greetings,

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

View 3 Replies View Related

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

View 5 Replies View Related

Tie Together Custom Logging And SSIS Logging?

Sep 12, 2005

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.

View 4 Replies View Related

Help Convert MS Access Function To MS SQL User Defined Function

Aug 1, 2005

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

View 3 Replies View Related

In-Line Table-Valued Function: How To Get The Result Out From The Function?

Dec 9, 2007

Hi all,

I executed the following sql script successfuuly:

shcInLineTableFN.sql:

USE pubs

GO

CREATE FUNCTION dbo.AuthorsForState(@cState char(2))

RETURNS TABLE

AS

RETURN (SELECT * FROM Authors WHERE state = @cState)

GO

And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.

I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:

shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO

I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.

Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.

Thanks in advance,
Scott Chang

View 8 Replies View Related

Logging In

Dec 6, 2006

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

View 2 Replies View Related

Logging Who Did What

Oct 1, 2004

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 ?

Jags

View 3 Replies View Related

Logging

May 30, 2001

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?

Thanks. JT

View 1 Replies View Related

Logging ?

Aug 22, 2000

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?

View 2 Replies View Related

DML Without Logging

Jun 11, 2004

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

SQL Logging

Dec 9, 2004

Hi,

is there any chance to log all incoming SQL-statements with date/time and duration ?

Thx for help.

dajm

View 2 Replies View Related

Logging

Aug 20, 2007

Is there anyway to log every incoming command, INSERT/UPDATE/SELECT/DELETE to a seperate database table?

Thanks

View 4 Replies View Related

Logging Changes

Mar 27, 2008

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.

View 1 Replies View Related

Logging SQL Queries

Dec 16, 2007

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.

View 3 Replies View Related

Logging In To MSDE

Dec 16, 2003

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

David Legg

View 1 Replies View Related

Transaction Logging

Oct 20, 2005

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?

Thanks in advance for any help.

Jagdip

View 3 Replies View Related

Inserting Without Logging

Jan 31, 2002

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?

View 1 Replies View Related

DTS And Transaction Logging

Jan 29, 2001

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?

Thanks,

Veronica

View 1 Replies View Related

Deadlock Logging

Nov 8, 2001

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?

Thx in advance.

Andy Richardson

View 2 Replies View Related

Is Any Way To Reindex 27 Gig Db Without Logging

Sep 25, 2002

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

Thank you
Alex

View 1 Replies View Related

DTS Progress Logging

Nov 22, 2002

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

View 2 Replies View Related

Update With No Logging

Jul 6, 1999

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.

View 1 Replies View Related

Error While Logging

Apr 15, 2008

Hi 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

View 20 Replies View Related

Logging Blocks

Apr 23, 2008

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

View 17 Replies View Related

Logging Server

Apr 24, 2007

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.

Any advice please people

Thanks
Neil G

View 2 Replies View Related

Error Logging

Jun 18, 2007

Hey 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

View 4 Replies View Related

Enable Logging

Mar 8, 2008

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.

View 1 Replies View Related

Logging Uses Of SELECT ??

Jul 20, 2005

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

View 3 Replies View Related

How To Stop Logging

Jul 20, 2005

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

View 1 Replies View Related

Logging Sql Queries

Jul 20, 2005

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.

View 2 Replies View Related

Logging Errors

Jul 20, 2005

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?

View 2 Replies View Related

Logging Not Working

Jan 17, 2008

Hi,

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

View 7 Replies View Related