Basic Logging/processing Db Question

Oct 5, 2004

I have many worker systems logging data to a central database.
The central database needs to periodically process all that data. During this processing time, new records can't be added part way through and the workers still need to log the data somewhere.

I was thinking of the following approach:

1) Rename the table the worker logs to. Let's say from LogTable to ProcessingTable
2) Immediately create a new and empty table for the worker to log to (LogTable). Possibly wrap this and the preceeding rename step in a transaction which may provide some atomicity.
3) Process the ProcessingTable
4) Drop ProcessingTable.

To the db experts here, does this sound like a decent approach or are there problems with this?

View 6 Replies

Where Is The 'Analysis Services Processing Task' Logging To

Apr 1, 2008

All,

I 'm using 'Analysis Services Processing Task' as part of a SSIS package to refresh the cube. in the property page,

the 'loggingMode' is set 'enabled', but there is no records in the sysdtslog90 table while all other tasks are logged in the table. How to logging into the sysdtslog90 table?

Thanks in advance

Jessie

View 3 Replies View Related

Analysis Services Processing Task: Logging And Error Handling

Mar 5, 2007

I have an Analysis Services Processing Task in my SSIS package. I run the SSIS package using SQL Server job, the running of the package is a job step.

When I process manually the analysis services objects (in practise cubes) using dtexec utility I get a lot of log. In case the processing fails I get error messages that quite well describe the error. But when I run the job the only information I get in the job log is that the job step failed. I know the failure happens in the Analysis Services Processing Task.

Is there any way in SSIS to get a) the log of the Analysis Services processing or b) the error messages of the Analysis Services processing? Or should the processing be done some other way than I've been doing?

View 4 Replies View Related

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

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

DTS PROCESSING

Aug 3, 2001

I have a DTS that imports data from an orcle database into SQL Server.
Doesn't the processing mostly occur on the SQL Server, not on the
oracle database from which the data is being imported?
The oracle database is vendor provieded and they are saying
our SQL Server DTS package is killing their server.
Any insight is appreciated.
Thanks

View 1 Replies View Related

XML Processing

Mar 27, 2008

Hey All,

I've got a process that creates records in my database based on XML input that I've gotten. What I am doing it giving this XML to a stored procedure to handle a specific task, then modify the XML and send it to the next stored procedure.

For instance, the XML could hold header records with detail records, I would first send the XML to a stored procedure that creates the header records, then updates the XML so the XML now knows the identity values of the header records I have just created, and then send the XML to the next stored procedure to create the details for those headers.

All works great and fine, but I have a problem with writing the identity values back to the XML. It seems I can only change one item in the XML at a time and thus need to loop this. For many records this really takes a long time.

Here is some sample code of what I'm doing (please excuse any typos, this is a simplified version of the code) :

declare @lvSeq numeric(15)
declare @lvRowNo int
declare @lvNumRows int

insert into myHeaderTable (
recid, recdesc
) select
ref.value('@recid', 'nvarchar(25)') recid,
ref.value('@recdesc', 'nvarchar(250)') recdesc
from @pXML.nodes('//headers/header') R(ref)

select @lvRowNo=1, @lvNumRows = @pXML.value('count(//headers/header)', 'int')
while (@lvRowNo<=@lvNnumRows) begin
select @lvSeq = recseq
from myHeaderTable
where recid = @pXML.value('//headers/header[position()=sql:variable("@lvRowNo")]/@recid)

set @pXML.modify('replace value of (//headers/header[position()=sql:variable("@lvRowNo")]/@recseq with sql:variable("@lvSeq")')

select @lvRowNo=@lvRowNo+1
end

Obviously I am looking for a better way to update the XML with the sequences. The insert takes a second, the loop takes minutes with large XML sets. I guess MSSQL is searching the whole XML to find the item to update.

It would be nice if I didn't have to loop through the XML. One solution I was thinking off is to store the XML in a temporary table with a single record per header item. Then I could do the modify in one go and recreate the XML by simply selecting the contents of the temporary tabel. I have no idea if this is possible.

So something like this:

select
ref.value('@recid','nvarchar(25)') recid,
ref.value('.','XML') XMLData -- this gives an error
into #TMP_XML
from @pXML.nodes('//headers/header') R(ref)

insert into myHeaderTable (
recid, recdesc
) select
recid,
ref.value('@recdesc', 'nvarchar(250)') recdesc
from #TMP_XML CROSS APPLY XMLData.nodes('/header') R(ref)

update #TMP_XML
set XMLData.modify('replace ....')
from myheadertable
where #TMP_XML.recid = myheadertable.recid

-- recreate XML here, not sure how....

View 1 Replies View Related

A Very Basic Q

Mar 18, 2005

This is probably a very silly question.I started learning ASP.net by following ASP.NET Unleashed. I am stuck where he wants me to open a connection to SQL Server database. I have just downloaded
MSDE. But I dont know where to type this code and how to run it..so as to connect to the database.

<%@ Import Namespace="System.Data.SqlClient" %>

<Script Runat="Server">

Sub Page_Load
Dim conPubs As SqlConnection

conPubs = New SqlConnection( "server=localhost;uid=webuser;pwd=secret;database=pubs" )
conPubs.Open()
End Sub

</Script>

Connection Opened!

Now do i have to change the uid to SA ? (i had to assign one when i downloaded and installed MSDE?

Thanks for the help.

View 1 Replies View Related

Basic DTS...

Nov 16, 2005

Hi all,

am not very experienced in using DTS and really need your help. I have a dts package that i have scheduled to run every day. Here's what i want the package to do:

1. Check whether a value for a certain column in a certain row of a table in my database is 0 or 1. If it is 1, then
2. Run the dts task (which i have created and is working)

In other words, when the package is started, i want to execute a stored procedure or sql task or whatever, and if that returns 1 then i want to continue, if it returns 0 i want to finish the package without running the dts task. I'm sure there's a simple way to do this, but i could use your help...!

Thanks,
Elisabet

View 1 Replies View Related

Need Basic.

Jul 11, 2007

Hi All,

Can this be done and if so can you give a bullet list of the steps need to accomplish this.

I need to load a bunch of files into a stagging table. Need to loop through the files and load them.

Thanks,

Michael

View 3 Replies View Related

BASIC Q

Mar 12, 2008

Hi,
what this statement do?
does it add all the values or combine all the values.
REPLACE combine WITH lc_tran + lc_exp + lc_war + ll_boc

Regards
kk

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