Transact SQL :: Trigger Execution With OLEDB Connection From In-house Application

Oct 28, 2015

issues with triggers in Sql Server 2014.

A few weeks ago I've done a SQL Server migration from SQL Server 2000 to SQL Server 2014.It was a bit tricky but anything worked fine.

I have some legacy VB6 (Visual Basic 6) applications written in house which worked with Databases on the old SQL server 2000.Surprisingly, these applications worked well after the upgrade to SQL Server 2014 without having to change a piece of code.

Now, some users tell me that they receive some unusual message when saving data  from these legacy applications.After investing for a few hours, I discovered that triggers are not executed when those users try to save data from grids or forms in their applications.Trying to reproduce the INSERT statement in SQL Server Management Studio, the triggers run well.From the application, they don't.

These applications connect to Database Server thru OLEDB connection with the following ADO connection string :

Provider=SQLOLEDB.1;Password={password};User ID={user};Initial Catalog={db};Data Source={datasource}.the {user} is a true SQL account who have read/write/delete access in the databases.

On the web there is a lot of questions involving the same issue, but only from SSIS.I found some articles about an OLEDB connection parameter named FastLoadOptions with a value of FIRE_TRIGGERS, but nowhere how to put it in the ADO OLEDB connection string.

how to reactivate the "normal" use of triggers from an ADO OLEDB connection ?Either with some obscur parameter in the connection string or options somewhere in the SQL Server 2014.

View 4 Replies


ADVERTISEMENT

Failed OLEDB Connection: Cannot Aquire Connection From Connection Manager

Feb 6, 2008

I have a package that uses a for loop to iterate through an unknown amount of excel files and pull their data into a table. However, there will be cases when the file is corrupted or has some sort of problem so that either the transformation will fail or the excel data source will fail with an oledb connection error.
Could anyone suggest a clean way to trap these errors? Specifically, the "Cannot Aquire Connection from Connection Manager", which is the excel connection.

Thanks,

John T

View 3 Replies View Related

Notification From SQL Server To OLEDB Application

Mar 22, 2007

Hi

I am adapting an OLEDB-based application [ported on VC2005] to access a SQL Server 2005 Express database.

The application opens recordsets on tables and stored procedures. From this I can read records and update the database.



But how can I monitor the changes on this database made by other applications ?

Do triggers resolve the pb ?

View 3 Replies View Related

Where Can I Find Northwind OleDb Native Sample Application

Jun 22, 2007

Where can I find Northwind OleDb Native Sample Application?



KB 909751 states to install VS 2005 with: "Smart Device Programmability install option"



When installing VS 2005 I do not get the opportunity to specify this option..



What am I missing?



Thanks in Advance..



JEK

View 3 Replies View Related

Using Oledb Connection From Connection Manager In Script Task

Aug 9, 2006

Is there anyone who tried to use a connection from connection manager to create a new connection in a script task? Including the password?

Now i passed the connection to the script task and called it in the vb script but then the password is not passed into the connect string.

Im searching for an example that works with passing the password in the connectstring?



Any help will be greatfull.


View 1 Replies View Related

Stored Procedures Hangs Application On First Execution

Apr 19, 2007

Hello

I have a .Net application which invokes an stored procedure (SQL Server 2005 Express installed on the same machine). When the stored procedure is called the first time, the application hangs because the sp never ends execution and the application's process has to be killed. But when the application is executed again, the sp runs as expected. What could be happening?

The stored procedure references remote tables by means of synonyms. If the Management Studio is used instead, the sp never ends execution when invoked the first time, but the query can be cancelled.

Now, if the sp is invoked first in the Management Studio first and then by the application, this does not hang (the sp executes as expected).

Thanks a lot.

View 3 Replies View Related

SSIS Package Execution Failed Within ASP.NET Web Application

Aug 11, 2006

Hello All,

I have a SSIS package which run well when stand-alone. However
it was failed when executed from .NET web application. It just simply return Failure. Code snip below:

Dim packagekgResults As DTSExecResult
.........
packagekgResults = package.Execute()

Environment: Windows 2003 Shared Server, IIS 6.0

Any idea?

Thanks in advance!

Tina

View 7 Replies View Related

Trigger Execution

Dec 2, 2005

If I have two triggers on a table, does one need to finish before the next is executed or will they execute at the same time?

View 1 Replies View Related

Using One Single-application Connection Vs. One Connection For Every Operation To SQL-Server Via ADO.NET

Aug 11, 2005

Dear members,I'am thinking about the "best" way to connect to my sql-server viaADO.Net (if there is a best way).My application uses the sql-server very intensively. At the momentevery database-operation opens a connection, executes and then closesthe connection.Another way would be to use only a single connection to the database,which is opened when the application starts.What do you think is the better way to get a high performing sqlserver: using one single application connection vs. using oneconnection for every operation?Cheers, Sebastian

View 2 Replies View Related

Suspend Trigger Execution

Aug 23, 2004

Is there any way to suspend a trigger execution after a commit trans?

View 6 Replies View Related

Trigger Execution Permissions

Mar 29, 2004

Probably a simple question.

Do triggers execute using the permissions of the user that caused the trigger to execute?


Thanks!

View 10 Replies View Related

ADO.NET Or OLEDB Connection/recordset?

Aug 9, 2007

My package needs to be a High Performance (target: 150,000 rows in 30 minutes) ETL solution. We are using all MS technologies - SSIS, SQL 2005, BIDS, etc. I need to loop the recordset executed by a Stored Proc in a Execute SQL Task in a Source Script Component.

If I use an ADO.NET Connection Manager, here is the code in the Source Script Component Public Overrides Sub CreateNewOutputRows()

Code 1

Dim sqlAdapter As New SqlDataAdapter

Dim dataRow As Data.DataRow

Dim ds As DataSet = CType(Me.Variables.rsSomeResultset, DataSet)


sqlAdapter.Fill(ds)



Iget: Error: System.InvalidCastException: Unable to cast object of type 'System.Object' to type 'System.Data.DataSet'.



Code 2

Dim oledbAdapter As New OleDb.OleDbDataAdapter
Dim dataTable As DataTable

oledbAdapter.Fill(dataTable, Me.Variables.rsSomeResultset)

Error: System.ArgumentException: Object is not an ADODB.RecordSet or an ADODB.Record. Parameter name: adodb



It works all right when I use an OLEDB Connection Manager with the second code sample.



Question: In order to extract the maximum performance, wouldn't it be preferred to use ADO.NET with SqlClient Provider in an all SQL Server 2005 environment? Or will an OLEDB Connection provide comparable or equal performance?

If so, what code can I use? Since the recordset returned by the Stored Proc (in the Execute SQL Task) can only be captured in a System.Object variable and you can only use the overload of the Fill() method of the OleDbDataAdapter to accept an ADO Recordset or Record object.

View 3 Replies View Related

SQLConnection Vs OLEDB Connection

Oct 16, 2006

Are there compelling reasons to use one versus the other? I am completely redeveloping all of our site from .ASP, VBScript and Access, to ASP.NET, VB.NET and SQL Server 2005 Express. I have a lot of code in ADO and OLEDB already, and would like to make the change to the System.Data.SQLClient classes if I should.

Thanks, Tom

View 1 Replies View Related

CE Database Oledb Connection

Jan 5, 2008

I have tried to connect ce database by using c++ and oledb, and failed in every turn, i dont know what i did wrong...

I created a new database by using create a new database sample in ssce bol, but i couldnt connect that database, it says invalid class string or authentication failed...

here is a simple example...

HRESULT hr;
CDataSource db;
hr = db.Open( "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=Newdatabase.sdf;Password='db_pwd'" );
if (FAILED(hr))
{
return;
}

and also, afx oledb headers causing lots of redefinition warnings, if i include both ssce headers and afxole headers...

and also oledb example in ssce bol download is missing, there is no such like example...

can you provide me simple example to connect any ce database?



Installed programs
visual c++ 9.0
sql server ce books online
sql server ce 3.5 runtime
sql server ce 3.5 server tools
windows xp sp2


thanks in advance

View 1 Replies View Related

Cannot Make OLEDB Connection To .dbf

Jan 7, 2008

Hi there,

I know this has been a topic of discussion before and I have read and tried the suggestions to no avail. I have a few .dbf files to import into my sql database and I know that one needs to make a oledb connection to these files.

I am using the Jet provider and have tried setting the extended properties to all of the DBASE versions and still cannot get it to connect. These files' names are shorter than 8 chars so that issue is no problem in this case.
I can actually get these files to show in a Microsoft Access 2008 database when I import it from there so I know that I must be missing something in my SSIS project.


After doing some reading on this topic, it would seem that this shouldn't be such a schlepp but, here I am

Maybe I need to install some driver or somethin?

Regards
Mike

View 6 Replies View Related

Recieving Notification Of Trigger Execution In VB

Jun 15, 2001

Hi,
is it possible from SQL to inform notification of trigger execution in VB using DMO ( or some other method ? )

Any help appreciated.

Thanks

View 1 Replies View Related

Connection String OLEDB - SqlServer

Jul 23, 2005

I need to conect my software from a remote client to a server, mystandard connection:Provider=SQLOLEDB.1;Persist Security Info=False;UserID=sa;Password=;Initial Catalog=MyDb;DataSource=192.168.0.100;Network=DBMSSOCN;andProvider=SQLOLEDB.1;Persist Security Info=False;UserID=sa;Password=;Initial Catalog=MyDb;DataSource=192.168.0.100;Network=DBNMPNTW;make a error connection,i have tryed with ODBC connection and its OK only in Named Piped.Can any one help me write the right connection string?thanksrm

View 2 Replies View Related

Script Component And OLEDB Connection

Jan 15, 2008



Hi
I am using OLEDB Connection in script component. I get a login failure when I run the package. I use SQL Server authentication. Do you have to use windows authentication in script component? Thanks

View 6 Replies View Related

OLEDB Connection To 32-bit Or 64-bit SQL Server 2000

Apr 16, 2008

An application vendor is telling me that their product will not run with SQL Server 2000 64-bit. Here is the layout:
* Dedicated 64-bit machine running Windows Server 2003 Enterprise, 64-bit.
* Separate application server running Windows Server 2003 - SBS.
Application server uses DCOM and OLEDB to obtain data from SQL Server.

My question: Is there any way for the application server to know if the data supplier is running 32-bit or 64-bit?
If so, then perhaps the application vendor is right.
If not, then they are just trying to put me off and avoid fixing a problem. In this case, I need an authoritative statement as ammunition to take back to their support department.
.... Warren

View 11 Replies View Related

OLEDB Connection Error With (win 64 Bit Server)

Jun 26, 2006

Hi

Im trying to connecting from SQL 2005 (win 64bit server) to a Oracle database via OLEDB but get the following message. The OraOLEDB.Oracle.1 provider is not registered on the local machine.

If we try ODBC we get the following message "error in initializing provider. Attemt to load Oracle client libraries threw BadImageFormatException.This problem will occur when running in 64bit mode whit the 32 bit Oracle Client components installed."

It should be the 64 bit client that is installed.

In the test environment (32 bit win) both works.

Pleas help

Rickard

View 3 Replies View Related

Etl Use OleDb And Loose Connection Password

Sep 12, 2007

Hello world.

I have a big problem.
Im writing an application in Sql 2005 Etl.
This application use 2 kind of ole db connection. One towards a Sql Server, and the other towards As400
to use ole db towords As400 is necessary to save user and password to connect.
There is a flag to save password.........and obviously a checked it. !!!!!

The problem is:
If I stay inside Visual studio and i use this application.......all is correct.
If I use my application in sql like a job.........loose password information and crash immediatly

someone can help me ?????????

thanks all in advance.
Alberto.

View 5 Replies View Related

Db2 Connection Problems SSIS (OLEDB)

Mar 21, 2006

Hello,

I'm trying to connect to a DB2 database via SSIS and I'm getting some problems:

I'm creating a new OLE DB Connection Manager and I'm getting two distinct errors:

1) When I try to use "IBM OLEDB Provider for DB2 Servers" I can create the connection manager and the connection is tested successfully.

But when I try to use the connection in OLE DB Source when I will list the tables I get this error:

Could not retrieve the table information for the connection manager 'MyDataSource'.
truncated. SQLSTATE=01004
CLI0002W Data truncated. SQLSTATE=01004

ADDITIONAL INFORMATION:

truncated. SQLSTATE=01004
CLI0002W Data truncated. SQLSTATE=01004 (IBM OLE DB Provider for DB2 Servers)

2) When I try to use "Microsoft OLE DB Provider for DB2" when I try to test the connection I get this error:

Test connection failed because of an error in initializing provider. The parameter is incorrect.

Anyone had these problems ?

Thanks,

Guber

View 5 Replies View Related

Trigger Procedure Execution On Database Creation.

Feb 2, 2006

Hello,

Is there a way to trigger the execution of a procedure when a database is created ? We're using this 3rd party system in which you only specify the database server. Whenever the users create a new "Storage Area", the system actually creates a new database.

I need to handle backups for that system so I want to create a procedure that will generate a backup device and backup jobs automatically whenever a database is created.

I will also need 2 procedures to handle database renaming and deletion. I searched around but can't find a way to trigger the execution of my procedures.

View 4 Replies View Related

Procedure Execution Sequence Inside Trigger

Feb 17, 2014

How the procedure will be called inside the trigger,whether first procedure followed by second or parallel it will execute?

CREATE TRIGGER [dbo].[InsertDatFXActualStaging]
ON [dbo].[DatFXActualStaging]--change this table to DatFXActualStaging
for INSERT
AS
BEGIN
SET NOCOUNT ON;

[Code] ....

View 1 Replies View Related

Trigger DTS Package Thr' Asp.net Application

Jun 17, 2005

 I am newbie to DTS stuff. Is there anyway I can trigger DTS package thr' asp.net applicationPlease help

View 1 Replies View Related

SQL Trigger To Notify VB.NET Application ?

Aug 10, 2007

Hi all,

I have a VB.NET 2005 application that needs to monitor constantly each change of a field on a SQL server 2005.
Is there a way of setting a trigger up in the SQL Server that notifies the VB.NET application to avoid having the VB.NET apllication querying the SQL table all day for changes?

The table in SQL stores a temperature value. I need the VB.NET application to receive this value everytime there's a change. I could do a timer on the VB.NET application to check for changes in the table every 1 second but I don't think this is efficient. The temperature value may change once every two or three seconds.

The VB.NET application will process the temperature value.

Is there a way of notifying the VB.NET application (which runs from a different server) from within SQL Server? If so, how? If not, what would you go about implementing this?

Thanks again for your comments,

ST

View 1 Replies View Related

Application Variable In A CLR Trigger

Apr 30, 2008

Is it possible to get a variable( for example user id of the currently logged in user) in a CLR SQL Server trigger.

View 1 Replies View Related

Setting The OLEDB Connection Properties At Runtime

Jan 30, 2007

I used to do this in DTS but wondered how to do it in SSIS. I have a For Each ADO recordset loop that reads these four columns into variables:

DataSource Name, Name(for a WHERE clause), user and password.

For each "Building" there is a new server to connect to, with identical tables. I have 10 tables in the loop to pump from one database to a SQL 2005 database (i.e. 10 dataflows). I want to set the source connection information with the variables. My questions:

1. How do I set the connection information for the source connections? Do I just set the properties for the connection manager and then it sets for all the sources? What property do I set--do I need a script task?

2. How do I use the values from the "Name" column in a WHERE clause in each data flow?

Thanks!

Kayda

View 4 Replies View Related

Creating OLEDB Connection From SSIS To Inforrmix

Aug 9, 2007

Hi,
I am trying to Create an OLEDB Connection from SSIS to Inforrmix without any success. Would someone be able to help.We are running Informix version 9.4. We were able to create an OLEDB connection with SQL server 2000 and DTS. But, when trying to create an OLEDB connection using SQL server 2005 and SSIS, I am getting an error message. The connection is created successfully but cannot access databases and tables on informix. The error message when trying to load the Informix tables:
'Could not retrieve table information for the connection manager ....
'Ifxoledbc.e' failed with no error message available '



Your feedback is appreciated

Thank you,
Patrick.

View 1 Replies View Related

CacheType In Lookup For Oracle OLEDB Connection

May 15, 2007

Hi,



1. If I have millions of rows to be compared, then which cache type is prefereed for lookup, Partial or no caching?



2. If I have lookup connected to Oracle Oledb, cache type as partial and SqlCommandParam as following

select * from (SELECT ORDER_ID, OPER_KEY, STEP_KEY, SUM(OCCUR_COUNT) AS OCCUR_COUNT FROM SFWID_OPER_DESC_EXPLD GROUP BY ORDER_ID, OPER_KEY, STEP_KEY) refTable where refTable.ORDER_ID = ? and refTable.OPER_KEY = ? and refTable.STEP_KEY = ?

then it doesn't allow me to add the parameters from Advance tab of Lookup transformation edition, and raises following error

"Provider cannot derive parameter information and SetParametInfo has not been called"



what am I missing?

View 5 Replies View Related

SQL Server Stopping While Configuring OLEDB Connection

Jan 23, 2007

When I configure and test and OLEDB connection SQL Server 2005 keeps stopping with an error message as shown below. The problem seems to happen when I open the connection for ediing in Data Flow Window. There is a consistent pattern of errors (in there Event Log) relating to the Evnet Log prior to this error occurring.

Any idea what the problem is?

I have to say that my experience with SSIS has not been very encouraging so far.



Event Type: Error
Event Source: MSSQL$SQL2005
Event Category: (2)
Event ID: 17311
Date: 23/01/2007
Time: 2:06:41 PM
Description:
SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 9f 43 00 00 10 00 00 00 ŸC......
0008: 14 00 00 00 53 00 59 00 ....S.Y.
0010: 44 00 2d 00 4c 00 2d 00 D.-.L.-.
0018: 30 00 31 00 33 00 32 00 0.1.3.2.
0020: 36 00 5c 00 53 00 51 00 6..S.Q.
0028: 4c 00 32 00 30 00 30 00 L.2.0.0.
0030: 35 00 00 00 11 00 00 00 5.......
0038: 45 00 78 00 61 00 6d 00 E.x.a.m.
0040: 70 00 6c 00 65 00 48 00 p.l.e.H.
0048: 69 00 73 00 74 00 6f 00 i.s.t.o.
0050: 72 00 69 00 61 00 6e 00 r.i.a.n.
0058: 00 00 ..

View 1 Replies View Related

A Lot Of Locks In SQL SERVER 2005 Using OLEDB Connection

May 28, 2008



hello,

I have an application wrote in Power Builder 11 and use OLEDB connection for SQL SERVER 2005 DB.
it works excellent till multiple users access the application. 2 seconds passes and locks appears and blocks all the DB!
I realy don't know what is the reason. In past I used SQL Native driver and it works well with the same application!!! (other reasons caused me to change the connection. not this).
Does someone can help me????????

View 3 Replies View Related

OleDB Connection Class In Custom Task?

Jun 9, 2006

I have OLE DB Connections set up in my connection manager (Native OLE DBMicrosoft OLE DB Provider for SQL Server). I would like to reference and query these connections from a custom task, written in C#. I currently reference it as follows:

using System.Data.OleDb;

...................................

OleDbConnection connection = (OleDbConnection) connections["MyConnection"].AcquireConnection(null);

What may be obvious to some (though wasn't to me, as I am new at this), when I run the task, I get an error saying that I cannot make this cast. After perusing the boards, I understand that this is because I am not making a cast to the right connection type. Well, that is where I am lost. What connection type (and corresponding library) do I need to reference? I want to continue to use the "Native OLE DB..." connection.

Thanks!

View 3 Replies View Related







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