Task/transaction Token By Logging

Jul 23, 2005

Hallo All,

I'm making in my DB some logs.
I have a separate table containing:
ID,
Date,
Source,
Type,
ErrorNo,
Description

Acctualy the table struct is not immportand that's why I do not post
the script.
I have one procedure, which I call in my other function procedures,
triggers etc. with parameters.
This procedure (WriteToLog) is called, if in other procedures, triggers
is any validation, exception or only information which I would like to
log me separately, and it writes the record into this table.

But sometimes, it is very hard, to follow much logs, and I figured out,
if I could have something like transaction ID, task ID, it will be much
easier.

Firs idea was to, by going from procedures to other procedures, pass a
self generated token (ie. date with any additional number). But then, I
need to change everywhere where I call WriteToLog procedure the call
syntax.

Let's say that we have following situation:

Procedure1:

Action1
Select1
CompareOfValues1
exec WriteToLog

Action2
CompareOfValues2
exec WriteToLog

Action3
exec Procedure2
Procedure2:
Action1
exec WriteToLog
Select1
Insert1
Trigger1Started:
Action1
CompareOfValues1
exec WriteToLog
Action2
Trigger1Exit:
Procedure2Exit:
Procedure1Exit:

Now ... I could pass this my generated number, from SP to SP and so on.
But I would like to now, does the MS SQL server has something what
identifies transaction like descripted below.

In this case, I do not need to pass any number, only I need to get this
number anyhow in SP WriteToLog, and insert it into my log table.
Any sugestions?

Thank's in advance.

Mateusz

View 3 Replies


ADVERTISEMENT

There Was An Error Parsing The Query. [ Token Line Number = 1,Token Line Offset = 43,Token In Error = C]

Jul 27, 2007


Hello all
Trying to delete some data from a SSCE (2005) DB produces the exception:
SqlCeException
There was an error parsing the query. [ Token line number = 1,Token line offset = 43,Token in error = C]
Here is the code I am using

string dsc = Application.StartupPath + "\FCDB07.sdf";

conn = new SqlCeConnection("DataSource = " + dsc);

conn.Open();

cmd = conn.CreateCommand();

cmd.CommandText = "DELETE FROM DataContainer WHERE FileName =" + dgContainers[0, SelRowIndex].Value.ToString();

cmd.ExecuteNonQuery(); //There was an error parsing the query. [ Token line number = 1,Token line offset = 43,Token in error = C ]

conn.Close();

Any Idea on What causes this?

TIA
Trophus

View 3 Replies View Related

Error Parsing Query: [ Token Line Number = 1,Token Line Offset = 83,Token In Error = 5 ]

Nov 23, 2007

Hey all-

I'm trying to insert some values into an SQL Compact database on a WM6 device but there is something apparently wrong with my SQL statement...

The program is going to allow users to schedule an SMS message to be sent at a certain date and time. I'm using a database to keep track of the scheduled SMS messages. The database has 3 rows: phone number, message, and the date/time to be sent.

Here is the relevent code:


private void scheduleMenu_Click(object sender, EventArgs e)

{

//connect to DB and do our scheduling magic



string message = messageBox.Text; //should rename messageBox...

string phoneNum = phoneNumBox.Text;



string dataBase = @"Program FilesSMS_Scheduler2SMSDatabase.sdf";

//SqlCeEngine eng = new SqlCeEngine(dataBase);

SqlCeConnection conn = new SqlCeConnection("Data Source=" + dataBase);

conn.Open();



//insert phone number, message text, and date/time into DB
string cmd = "INSERT INTO Scheduler(phoneNum, message, date) VALUES("+ phoneNum + ", "+ message + ", "+ dateTimePicker1.Value +")";

SqlCeCommand cmdPhone = new SqlCeCommand(cmd,conn);





cmdPhone.ExecuteNonQuery(); //error occures here...



messageBox.Text = "";

MessageBox.Show("Message Scheduled!");

}



I'm guessing it doesn't like how I am trying to get the data from the different text boxes and the DateTimePicker to go inside the SQL command. Does anyone have any ideas on how to fix my SQL command or how to get data from a textbox and DateTimePicker to be inserted into a database a different way?

View 3 Replies View Related

Passing SAML Token From Security Token Service To Reporting Service

Mar 28, 2007

Hi,

I am using SQL Server 2005 Reporting Services. I want to make it secure. I am also using WCF services and made them secure using Claim based System.Identity Model.

I want to apply same claim based model to Reporting Services.

How can I do that?



Amit

View 2 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

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

Logging Output In A Transaction

Oct 17, 2005

We have a publish job that we are trying to automate, the problem is getting the output back to the app. or a file. Originally we had print statements, this worked great when we manually ran the proc in QA and could capture the output, now that we are automating it from an application I am not sure how to capture these Print statements - ideally I would like to find this out.

The App. is doing a Try-Catch block so using something like isql.exe will not do the trick otherwise that is the route we would go.

I tried logging everyting to a table but those inserts get rolled back with XACT_ABORT. What about the xp proc that logs it to the event log? Thought of that but that would make a real mess of the event log with all of our status messages.

Now we are considering using xp_cmdshell that calls a batch file to output our status text, is this my best option? I would prefer to capture all of the print statements so if anyone knows how to do this that would be preferable!

Thanks!

View 1 Replies View Related

Transaction Logging Backup Set

Jul 20, 2005

HiI take one nightly full database backup at 02:00 and backup thetransaction log to one backup set every 15mins.The commands to do this are as follows and are set up to run asdatabase jobs:-- Database BackupBACKUP DATABASE [ThikosDatabase] TO [DatabaseBackUp] WITH NOINIT ,NOUNLOAD , NAME = N'ThikosDatabase backup', NOSKIP , STATS = 10,NOFORMAT-- Transaction Log Backup Every 15mins.BACKUP LOG [ThikosDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME= N'TransactionLogBackUp', NOSKIP , STATS = 10, DESCRIPTION =N'BackUp the transaction Log every 15 minutes every day.', NOFORMAT,RETAINDAYS = 0At the moment the transaction log backups every 15mins are simplybeing added one by one to the one backup set. I want the backed uptransaction logs in the backup set after 24hrs to be overwritten sodisk space does not add up. I thought RETAINDAYS would do this as anexpirydate is set to expire on the time it is created so i would haveexpexted it to be overwritten when that time comes around the nextday. (But is doesent)I think it is ok to overwrite the transaction log in the backupsetafter 24hrs as you only need the transaction logs since the last fullbackup (which happens nightly at 02:00) for recovery.Does anyone have any ideas?Many thanks.Thiko!

View 3 Replies View Related

Minimize Transaction Logging

May 14, 2007

We use SQL PE 2000 in our lab to store sampled data (lots of data collected at high sampling rate) in real-time. i know this may be an uncommon use of SQL server. For this particular application, performance is much more important than data recoverability. In other words, once-in-a-while data loss is acceptable. Actually we have never used the transaction log for recovery since we started using the SQL PE in this way a few years ago. Certain transaction logging such as bulk deletion is particularly wasteful of the resource. As my understanding from reading the online book and some threads on this forum, the best thing we can do is using bulk-logged recovery model. I am not sure if that helps us at all because it seems that bulk deletion (i.e. DELETE FROM MyTable WHERE ..., may affect hundreds of thousands of records) is not on the list of minimally logged transactions.



I am wondering if anyone could share some good advice.



Thanks in advance!

View 4 Replies View Related

Disable Transaction Logging

May 8, 2007

Hi,



We have a DW project where I would like to disable the transaction logging. Any good advices anyone?

View 3 Replies View Related

Disable Transaction Logging For Table

May 9, 2000

Is it possible to disable the logging operations for insert/update/delete in the transaction file for certain tables?

I have an application where I have to write temporary tables for calculations and printing and all the insert/update/delete absolutely don't have to be logged. They get deleted after use anyway.

Thomas Schoch

View 1 Replies View Related

Long Blocked Transaction Logging

May 5, 2007

Is there anyone know any feature in SQL server which can detect/log the transaction which has been blocked unusually long? My idea is:



If a transaction has run for over 30 minutes, then log the following information in SQL server log:

1. State of the transaction : Blocked, Running, Sleeping

2. Time happeing

3. Duration

4. Last SQL it run

5. Blocked by which thread and info of the thread : the last SQL it run, the state of the thread.



Possibly it would be run in SQL server agent or SQL profiler has such feature, I am not sure about it. Can anyone suggest? Thanks.

View 4 Replies View Related

How To Switch Off Transaction Logging For Inserts/updates?

Nov 22, 1999

Is there a way to switch off transaction logging for insert and update statements and not only for select into bulk copy?

View 1 Replies View Related

Copy Table In A Database Without Transaction Logging.

Mar 18, 2008

Hi y'all, I am doing some searching in the archived threads, but I have a need to copy a table in a database to a new table in the same database, but the new table will be just a table with test data. There are several million rows in the table and I want to do the copy without logging the new inserts in the transaction log.

Is there an easy way to do this? I found this in my search efforts so far, but am just wondering if there is an easier/better way to accomplish what I want to do.

BTW, I normally wouldn't care, but the boss is complaining that it is taking too long to do the copy for a different team, so asked if I knew a way to copy data to a new table without logging. I don't, so here I am ;)

Here is what I found so far:Following 3 things need to be done
1) create table as not logged initially
2) set autocommit=off
3) and activate the not logged initially option
Now the inserts happen without the use of transaction logs

View 12 Replies View Related

Script Task Logging

Aug 1, 2007



I seem to be doing something wrong I have a script task as follows:


Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim emptyBytes(0) As Byte

Dts.Log("The change in records between this load and the last load is " & CDbl(Dts.Variables("percent").Value) * 100 & "%.", 0, emptyBytes)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class


I have logging turned on for the package going to a text file with just errors checked. For this step I specifically checked the box for logging on this step (instead of inheriting logging) checked the logging provider, and on details checked ScriptTaskLogEntry.

The upshot is, the step runs fine but I have no entry in the log. HELP

Thanks,

LarryC

View 4 Replies View Related

Script Task: Dts.Logging.Log

Jul 6, 2006

I am trying to understand the use-case of Dts.Logging.Log.

Following BOL, I see that I can use Dts.Log to send a custom log message to my log provider. The following example code produces an event called "User:ScriptTaskLogEntry", with the message "My custom message here".


Dim dataBytes(0) As Byte

Dts.Log("My custom message here", 0, dataBytes)

I would like to be able to produce my own custom event (lets call it "MyCustomEvent"), as well as a custom log message. I thought it would be possible via Dts.Logging.Log, which requires that I specify an event name, in addition to a log entry. However, when I place the following code in the script task, I do not get any logging whatsoever.


Dim dataBytes(0) As Byte

Dts.Logging.Log("MyCustomEvent", System.Environment.MachineName, System.Environment.UserName, "Custom Log", "", "", "My custom message here", Now, Now, 0, dataBytes)

Any thoughts?

Thanks!

View 4 Replies View Related

SSIS (Integration Services) Transfer SQL Server Objects Task: This Task Can Not Participate In A Transaction

Feb 1, 2007

In short, does the €œTransfer SQL Server Objects Task€? support distributed transactions?

In trying to use a €œTransfer SQL Server Objects Task€? in a container using a transaction on the container. The task is set to support the transaction. It is setup to copy table data from several tables from a non-domain server (sql server 2000) to a domain-based server (sql server 2005). I get an error stating, €œThis task can not participate in a transaction€?.

I am wondering if it means exactly what it says €“ this task in SSIS can€™t participate at all. Or does it mean that it won€™t in this scenario for some reason. I attempted a simple copy of data from mssql 2005 to mssql 2005 (same server) and the task still failed). MSDTC appears to be running properly on my machine and such (I can do a simple distributed transaction across linked server to the 2000 server in Query Analyzer (QA)). Also, MSDTC appears to be working on both servers with distributed transaction query tests in QA.

Here€™s the error info€¦

SSIS package "Development BusinessContacts and Products Migration.dtsx" starting.
Information: 0x4001100A at Copy BusinessContacts Data: Starting distributed transaction for this container.
Error: 0xC002F319 at Copy BusinessContacts database table data 1, Transfer SQL Server Objects Task: This task can not participate in a transaction.
Task failed: Copy BusinessContacts database table data 1
Information: 0x4001100C at Copy BusinessContacts database table data 1: Aborting the current distributed transaction.
Information: 0x4001100C at Copy BusinessContacts Data: Aborting the current distributed transaction.
SSIS package "Development BusinessContacts and Products Migration.dtsx" finished: Failure.
The program '[4700] Development BusinessContacts and Products Migration.dtsx: DTS' has exited with code 0 (0x0).

View 9 Replies View Related

Logging Query Messages From ExecuteSQL Task

Dec 15, 2006

The ExecuteSQL task makes it easy to capture the resultset of a query or stored proc in a variable. But what about the messages that would normally appear in the Messages tab when running queries in Management Studio or Query Analyzer? There must be a way to display and log rowcounts and other messages that a query normally produces.
 
Various package/task logging configrations haven't helped. I've read discussions about logging rowcounts in data flow tasks, but what about capturing messages in ExecuteSQL and other control flow tasks that run T-SQL?
 
I only have enough time to dabble in this technology so I may be missing something obvious or approaching this incorrectly. Any suggestion, reference, constructive criticism or reponse would be appreciated.
 

View 15 Replies View Related

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

How Do I Start A Transaction :-Dataflow Task + Excute SQL Task

Mar 7, 2007

1 :Control Flow Excute SQL task: Truncate Table

2: Dataflow Task: Datareader--Script componant--OLE DB Destination (SQL Server 2005--a single table --always around 600,000 rows)

How do I set up a transaction where if there is a failure the Truncate Table command will roll back---and the OLE Destination (A single SQL Server table) will be left the same as before the load started.

Another question with that volume of data --600,000 rows will a truncate table be pratical in a transaction

Any ides welcome

thanks in advance

David

View 3 Replies View Related

No Debugging And Logging When A Single Script Task Is Executed

Jul 13, 2006

Hi,

I cannot execute a script task in the VBA code window.

I cannot debug or log if I run a single script task from the right click Execute Task .

Every time I have to run the entire package in order to be able to debug.

What am I missing?

appreciate a help.

Gulden

View 4 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

Integration Services :: Logging Record Counts To Execute Task For Table Update

Jun 20, 2015

Have an SSIS package running great in 2008R2. It generates several flat files based on inline database queries. The first step of the package inserts a record into a log stats table and the last step of the package updates this record with the package name, run time and execution status. Now I need to add the records counts for each flat file to the log table. 

Is there a way I can update one field for run counts with each of the counts for each file. So the [run counts] table column would look something like:

file1: 43522
file2: 645367
file3: 7883

Is it possible to store the record counts and flat file names in variables then concat them at the end when updating this record?

Or, is a better way to just insert/update a new record for each flat file step and log the counts for that file for its own record?

In either case, how I can capture the file count and pass that to the update statement.

View 4 Replies View Related

SQL Server 2012 :: Server Autonomous Transaction Logging

May 14, 2014

I know Oracle provide pragma directives to execute autonomous transactions which i used before on Oracle for logging. Now i want to repeat the same in SQL Server but unfortunately i found such pragmas are not existing in SQL Server.After several google searching, i have found that i can use loopback linked server to generate autonomous transaction calls.

If i have Server A & Server B where server B is a loop back server of Server A and all my objects are existing on Server A. I just wanted to user Server B for logging only. should i have logging tables on Server B? Logging procedures on Server A? and call logging procedures (via Execute ) from application procedures residing on Server A?

View 2 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

SQL Task Transaction Required

Apr 29, 2008

Hello everybody,

my problem is:

When I try to set the transaction required on a SQL Task, the package will return the following error:

"[Execute SQL Task] Error: Failed to acquire connection "MYDATABASE.ADONET_CONNECTION". Connection may not be configured correctly or you may not have the right permissions on this connection."

But if the connection is made to a SQL Server in the local machine, I can have the transaction required option, if I change the SQL Server Connection to another machine, the task only runs if I get the transaction to the supported option...

Does anyone have any ideia about this problem?

I've googled the problem, but all the possible solutions I've found came to nothing... so I'ved ran out of ideias!

Thanks in advance!

Cheers

View 1 Replies View Related

Send Mail Task Inside Transaction?

Oct 9, 2007

Hi,

Can we include a Send Mail Task inside a transaction?
We intend to callback a sent email in case there is an error in the subsequent task, if it is possible.

Thanks and Regards,
B@ns.

View 4 Replies View Related

Transaction Inside A Data Flow Task

Oct 30, 2006

We are designing an ETL solution for a BI project using SSIS.

We need to load a dimension table from a source DB into the DW; inside the DW there are two tables for each source dimension table: a current dimension table and a storical dimension table.

The source table includes technical columns that indicate if each record was processed correctly by ETL procedures.

Each row in the source table can be a new record, or an exisisting one. If it's a new record, a corresponding new record should be inserted into the current dimension table of the DW; if it's an exisisting one, a new record should be inserted in the storical dimension table and the existing record in the current dimension table should be updated.

Furthermore for each record we need to update the source table with an error code. If the record was processed with succes the code is zero, otherwise it contains an error code.

I try to use a single data flow task, using the 'ole db command trasformation' task for managing update and 'ole db destination' task for managing insert.

The problem is that some insert and update should be executed inside a single transaction, for each record; for example if the source contains a new record I should insert the record in the current dimension table and update the source record with error code zero if every think goes fine.

There is some way to group task in the data flow pane in a single transaction ?

There is a better way to do that ?



Cosimo

View 12 Replies View Related

Implementing Transaction In SSIS Package Using SQL Task

Dec 16, 2007



Hi all,

I am having a simple ssis package. In that i have an sql task that will insert a record in to the database. i find that if i use a simple OLEDB connection it works fine. but the problem comes here when i change the connection string with an valiable. Once i assign the same connection string to a valiable and assign the variable as expression, there after i encounter the following error.



[Execute SQL Task] Error: Failed to acquire connection "DB connection". Connection may not be configured correctly or you may not have the right permissions on this connection.

I am struggling with this for a long time.

Any of you plz suggest me a solution for this problem

Thankz

View 2 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

Unknown Token Received

Jul 20, 2005

I have a client using SQL 2k, SP2 (due application requirements, SP3 is notan option - the application vendor will not specify why). We are receiving:[Microsoft][ODBC SQL Server Driver]Unknown token received from SQL ServerConnection BrokenThere doesn't appear to be any rhyme or reason as to why this is happening.And...it shows it's ugly head at random places during execution. Has anyoneexperienced this also, or have any ideas on what to look for? I have seennumerous suggestions stating to upgrade from MDAC 2.6 to 2.7. At thispoint, I'm not sure if thats an option based on the vendors application(which by the way they no longer support!).Any ideas will be greatly appreciated.Greg

View 1 Replies View Related

How To Do Token In T-SQL Stored Proc?

Nov 8, 2007



Hi,

Now I have a string: "ab,cd,ef,", and I would like to use SQL to token this string to a string array like "ab" "cd" "ef". Then I want to do a loop to insert these values into a table like:

for( int i = 0; i<aStringArray.length i++)
{

insert into aTable(aField) values(aStringArray);
}

How to do that using T-SQL Stored Proc?

Thanks.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved