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 ?
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
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.
Hi all,
I got an error when i do an OLE db Source pointing to an sql 2000 database and executing a sql query inside the OLE Source. The ole source will point to an OLE DB destination which is an sql 2005 database.
But i got the below error:
Error at Data Flow Task [OLE DB Destination [245]]: the column firstname cannot be processed because more than one code page (936 and 1252) are specified for it.
Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB destination" (245)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow TaSK: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
hi,
An application, every one hour it connects to sqlserver and uploads data from MSAccess database.i should get the notification weather that application is connected to the server or not.because this application gets currepts very often.
any help is appreciated.
thanks
preethi
hi i am getting the error for granting
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO sa
error is
Cannot find the user 'sa', because it does not exist or you do not have permission.
SO PLEASE GIVE ME THE CODING
step by step for notification
thanks
Hi,
I'm trying to install Sql server notification service on my pc, which has sql server 2000 and vs.net 2003 installed, but its telling me that I should have the .net framework installed before continuing the installation. The .net framework is already installed, any ideas why is it doing this????
Thanks
Hello
This is first post to this forum
I have read that through Notification Services we can send sms through sql server as well as recieve.
Can anyone provide me with links for the same.
Thanks
cmrhema
Hi,I am a newbee to sql server. I know there is a tool calledNotification Services. I want to set up notification for specificentries in sql server table.Thanks a lot for your guidance.AJ
View 1 Replies View Related
I am getting the above error when I try to create Notificatioin services new Instance.
Additional Information is
Failed to connect to server ABC(Microsoft.SqlServer.ConnectionInfo)
An error occured while establishing connection to the server.When connecting to SQL Server 2005, this failure may be caused by the fact that under default settings SQL Server deoes not allow remote connections.(Provider : Named Pipes Provider, error 40 -could not open connection to SQL Server)(Microsoft SQL Server error: 53)
Please help me I am new to SQL Server 2005.I can't able to proceed.
I am trying to set up the email notification system in SQL server 2000, but im having some problems. Mainly, im having problems with setting up an email account in the first place (there is a dedicated mail box, but the one im working with is a different box altogether).
If anyone could help fathom this out from start to finish i would be really grateful.
Has anybody used it . can you give me an idea about the performance etc.?
View 6 Replies View RelatedI have a situation where a person, who created our DW, likes to go in and change table name. This person had no prior SQL xp and is at a higher level then me in the company. I need to know whenever somebody changes a table name I get notified, with the old table name and the new table name. This can be either set on the server level or the DB level. I would like to use the policy management but cant find the way to do it.
View 9 Replies View RelatedI have been testing SQLDependency and I have a two questions;
1) When using a stored procedure to run the dependency query, using a "clean" procedure (containing nothing but a select statement), works fine. Adding try..catch (which is a part of our coding standards), results in the subscription firing immediately because of an invalid query. Is this by design? If so, how can I implement error handling.
2) How does SQLDependency handle SQL Server restarts?
I see two options:
a) The subscription is fired listing a server restart message in the related service broker queue, but as far as I can tell, SQLDependency has no way of handling these messages given the connection has been broken. (Establishing a new connection and dependency won€™t read the said messages).
b) The SQLDependency listener on the client raises an error for the connection being terminated. Can I relay on this event to recreate a connection and dependency?
Help will be appriciated
Boaz
within sql server management studio... we have set up a few sql scripts to run at 2am every night (sql server agent > under jobs)... although it has been successful each night, we'd like to setup sql server agent to automatically email success and failure notifications to our email boxes.
i see under the properties of sql server agent > jobs > properties of a job > there is section that says Notifications but then it doesnt really have a place to type in which email we want to send them to? some reading material or instructions greatly appreciated
Hi,
My name is Vinh, I am a new bee in SQL Server 2005. I am using template script (see below) from SQL Server to create account but when I am right click in database mail for testing email and I got the message, could not connect to mail server.
Below I am trying to use smtp to connect but I know in my company we are using Exchange Mail Server. will that make a lot different?
Please help me,
Thank you very much,
sp_configure 'database mail xps', 1
GO
reconfigure
GO
-------------------------------------------------------------
-- Database Mail Simple Configuration Template.
--
-- This template creates a Database Mail profile, an SMTP account and
-- associates the account to the profile.
-- The template does not grant access to the new profile for
-- any database principals. Use msdb.dbo.sysmail_add_principalprofile
-- to grant access to the new profile for users who are not
-- members of sysadmin.
-------------------------------------------------------------
DECLARE @profile_name sysname,
@account_name sysname,
@SMTP_servername sysname,
@email_address NVARCHAR(128),
@display_name NVARCHAR(128);
-- Profile name. Replace with the name for your profile
SET @profile_name = 'TestProfile';
-- Account information. Replace with the information for your account.
SET @account_name = 'vdang';
SET @SMTP_servername = 'smtp.cgdnow.com';
SET @email_address = 'vdang@cdgnow.com';
SET @display_name = 'Vinh, Dang';
-- Verify the specified account and profile do not already exist.
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
RAISERROR('The specified Database Mail profile (<profile_name,sysname,SampleProfile>) already exists.', 16, 1);
GOTO done;
END;
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
RAISERROR('The specified Database Mail account (<account_name,sysname,SampleAccount>) already exists.', 16, 1) ;
GOTO done;
END;
-- Start a transaction before adding the account and the profile
BEGIN TRANSACTION ;
DECLARE @rv INT;
-- Add the account
EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
@account_name = @account_name,
@email_address = @email_address,
@display_name = @display_name,
@mailserver_name = @SMTP_servername;
IF @rv<>0
BEGIN
RAISERROR('Failed to create the specified Database Mail account (<account_name,sysname,SampleAccount>).', 16, 1) ;
GOTO done;
END
-- Add the profile
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
@profile_name = @profile_name ;
IF @rv<>0
BEGIN
RAISERROR('Failed to create the specified Database Mail profile (<profile_name,sysname,SampleProfile>).', 16, 1);
ROLLBACK TRANSACTION;
GOTO done;
END;
-- Associate the account with the profile.
EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @profile_name,
@account_name = @account_name,
@sequence_number = 1 ;
IF @rv<>0
BEGIN
RAISERROR('Failed to associate the speficied profile with the specified account (<account_name,sysname,SampleAccount>).', 16, 1) ;
ROLLBACK TRANSACTION;
GOTO done;
END;
COMMIT TRANSACTION;
done:
GO
Two days ago I discovered that the drive on our test SqlServer2005 was full. The current Error_log had 54+ Gb (yes Gb) of records. Not only did I delete the file I restarted SS and the server.
That afternoon, I discovered that the current log had 33,678 records! I believe 33,616 of the records came from a single run of my app and set of program events. Obviously I'm doing something wrong in my Query Notification implementation and testing. And yet, when I try to to deliberately trigger the phenomenon, I can not.
Here is the head of the exported log file; the two msgs (query dialog closed and cannot drop queue) alternate thousands and thousands of times form a single test run. What is going on?! Pls help!
Date,Source,Severity,Message
10/11/2007 11:42:52,spid5s,Unknown,SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
10/11/2007 11:42:52,spid5s,Unknown,SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
10/11/2007 11:42:49,spid14s,Unknown,Service Broker manager has shut down.
10/11/2007 11:42:47,spid51,Unknown,Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
10/11/2007 11:42:47,spid51,Unknown,Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
10/11/2007 11:42:47,spid51,Unknown,Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
10/11/2007 11:42:04,spid51,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid54s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid54s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid52s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid52s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid52s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid52s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid54s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid54s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid52s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid52s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid54s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid54s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
10/11/2007 11:42:03,spid52s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
Is there a way to set up automated email notification beginning 14 days prior to when a SQL Server Login password (that has "Enforce password expiration" enabled) will expire?
Thanks,
-Dave
I'm trying to set enable SQL cache invalidation on a webpage, specifically on a a datasource that fills a GridView.
The website http://beta.asp.net/QUICKSTART/aspnet/doc/caching/SQLInvalidation.aspx makes it sound so very simple, and I have attempted to do all that it says, yet something just isn't right. The site says that the following needs to be done.
Queries must explicitly include column names in the SELECT statement. Using "SELECT *" results in a query that will not be registered with Sql Server 2005 query notifications.
Table names in queries must include the ownername. For example, if you issue a query against the authors table in the pubs database, the query must reference the table as "dbo.authors".
The security identity running the query must have rights to register queries for notification in Sql Server 2005. This right can be granted with the following T-SQL command: GRANT SUBSCRIBE QUERY NOTIFICATIONS TO username.
The security identity running the query must also have rights to send query notifications from Sql Server 2005. This right can be granted with the following T-SQL command: GRANT SEND ON SERVICE::SqlQueryNotificationService TO username.
I've done items 1,2 & 3, but when I attempt to do item 4 I get the following result in TSql:
Msg 15151, Level 16, State 1, Line 1
Cannot find the service 'SqlQueryNotificationService', because it does not exist or you do not have permission.
My login account has sysadmin priviledges, so I'm guessing the latter part of the error doesn't apply to me. But what do I have to do to get SqlQueryNoticationService to exist?
Is there any way to trigger an event (ie. call a procedure/job/etc.) when/if an AG fails over?
Not looking to use agent alerts.
In the notification tab of job properties, I can see three options in the drop down list at the right hand side.
When the job succeeds
When the job fails
When the job completes
In which system table these options are saved ? Is it possible to add a custom option in that dropdown list.
Is there any built-in way of kicking off a job on SQL Server 2005 Agent whenever a package/job completes in Oracle?
Are there any (Triggers? Msft queue? Event Notification?) mechanisms to automate running a job on the SQL side?
Any article or knowledge articles would be appreciated also.
If not are there any built-in stardardized polling techniques? Or are there any timers in SSIS?
That way I can delay executing a child package until a certain record has been inserted into a control table in Oracle.
I don't want to write an inefficient for loop that blocks all other processing on the server and iterates once every second.
Thanks
I have used the notification-based cache invalidation with my web application, but how can I check it really works? I mena if the backend data didn't change , the data will come from cache not from the database?
<asp:SqlDataSource ID="SqlDataSource_WebInfo" runat="server" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>" SelectCommand="SELECT simplified, traditional FROM temp" ProviderName="System.Data.SqlClient" EnableCaching="True" SqlCacheDependency="CommandNotification">
</asp:SqlDataSource>
Thanks a lot!
An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.
On SQL Server 200 instances the job ran in minutes in the old 2000 package.
Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?
Any inputs will be much appreciated.
Thanks,
MShah
Hi,
I am using WIN XP, SQL Server 2000 database and vb, asp application running on MTS. I have a transaction where it will process 500 items and write to the tables in database. When opening the connection I tried to cache the connection and made use of it when ever the code is called. Here is how I did it.
Public Function GetCachedADOConn(ByVal eConnect As DBConnectVar, Optional ByVal blnUseShapeRS As Boolean = False) _
As ADODB.Connection
Dim cnnADO As ADODB.Connection
On Error GoTo EH
If m_CachedConnection Is Nothing Then
Set cnnADO = New ADODB.Connection
' Check if we need to load the cache first...
If Len(mstrConnect(eConnect - 1)) = 0 Then Call CacheOneConnectionSet(eConnect)
' Now just return the cached data
With cnnADO
If blnUseShapeRS Then
.ConnectionString = "Provider=MSDataShape;Data " & mstrConnect(eConnect - 1)
Else
.ConnectionString = mstrConnect(eConnect - 1)
End If
.CommandTimeout = mintCommandTimeout(eConnect - 1)
.ConnectionTimeout = mintConnectTimeout(eConnect - 1)
.CursorLocation = adUseClient
.Open
End With
Set m_CachedConnection = cnnADO
End If
Set GetCachedADOConn = m_CachedConnection
'Set cnnADO = Nothing
Exit Function
Here the main part is making use of m_CachedConnection as a public variable declared as adodb.connection.
When I run the application it throws me an error
Unable to establish connection(Micorsoft OLEDB Provider for SQL Server , New transaction cannot enlist in the specified transaction coordinator.)
Can some one help me How I can address the above issue.
Thank you,
Nagesh.
Hi,
I can connect to a remote SQL server with ADO.NET but not with OLEDB.
I get
------------------------------
Test connection failed because of an error in initializing provider. Login timeout expired
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.
Named Pipes Provider: Could not open a connection to SQL Server [233].
------------------------------
Through the SQL Server Connection Manager, if I disable Named Pipes and enable TCP/IP, I get for OLEDB connection:
------------------------------
Test connection failed because of an error in initializing provider. Unable to complete login process due to delay in opening server connection
------------------------------
with Connection and General Timeout are both 0.
Appreciate the help
Gulden
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
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
Hi,
I try to access the table "UserGroup" in my local sql server 2005 database. However I get this error message"Invalid object name 'UserGroup'. "I'm using MS Visual Studio 2005 and MS Sql Server 2005. I did install the latest Sql Native. In fact I just migrate from ASP.NET to ASP.NET 2.0 . It is working if my connection string is point to my "online host" without any error.'Online Connection String - Working<add key="OLEDB" value="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=abc;Initial Catalog=mydatabase_db;Data Source=xxx.xx.xx.xx,xxxx; Password=xxxx;Connect Timeout=3600"/>
'Local Connection String - Not working (SQL Authentication)<add key="OLEDB" value="Provider=SQLNCLI;Trusted_Connection=No;UID=calvin;Initial Catalog=mydatabase_db;Data Source=CALVINNB; Password=xxxxxx"/>OR(Windows Authentication)<add key="OLEDB" value="Provider=SQLNCLI;Server=CALVINNB;Database=mydatabase_db;Trusted_Connection=yes"/>Is the Oledb provider having problem in MS SQL Server 2005?
Any help would be more appreciate.Calvin
Dear all,
Still i am having problem with connecting sql server with ASP (Intranet).
Following is the code which i am using for the connection now,
Set conn=Server.CreateObject("ADODB.Connection")
conn.open "DSN=aspfirst;Uid=;PWD=;"
The error mesage , generated by this code is attached with this mail. Please check the attachment and hel me in the same
Graceson
I have a package that is basically a container for using the Execute Package task to call many "child" packages. Each child package basically uses a data flow to move data from one database to another database on the same SQL Server. In the case of the child packages, I've used a combination of OLEDB and ADO.Net connections on the destinations. I ensured that all tasks and packages are set to TransactionOption.Supported and that the "parent" package has Required set at the package level. Anyway, the problem I'm finding is that one of the OLEDB connections is being blocked. When I look in SQL Server Activity Monitor I see that there are numerous ADO.Net connections that are on multiple processes (all belonging to these packages) even though when I check the Details, I can see that it has completed the final statement of the package the process belongs to. Thinking this may have been a multithreading issue, I changed MaximumConcurrentExecutables to 1 in all of the packages. Basically, even though all of the packages appear to have their own processes, when I look at the locks for the objects I'm working with, they all show up on the lowest process ID. My assumption here is that since the processID is associated with the first package, it is the "owner" of the transaction and even though the packages have their own processIDs, they show up on the first process because they're in the transaction. If I change the connection from OLEDB to ADO.Net, it resolves the issue and the blocking does not occur. So my question is, if I have an OLEDB connection manager and ADO.Net connection manager, can they share in the same transaction or are they in their own transactions?
View 1 Replies View Related
How do I get the CLSID for SQL Server 2005 OLEDB Provider? The following code yields the CLSID for 2000 Provider
m_hr = CLSIDFromProgID(L"SQLOLEDB",&clsid); // SQL Server
Thanks
JEK
Howdy Folks!
I just finished an OLEDB interface written in VS2005 C++ that works with Sql Server Compact Edition 3.1. My team now wants me to make my interface compatible with Sql Server 2000, 2005, and 2005 Express. My question is...what header/lib files do I need installed and in my stdafx.h to pull this off? Is there a particular order I need to include these files in?
Right now I'm just including the following for SSCE:
#include <ssceerr30.h>
#include <ssceoledb30.h>
Once I get the right includes figured out, the next step is figuring out what CLSID's I should use in the CoCreateInstance call. I *think* I'm supposed to use CLSID_SQLOLEDB for 2000, CLSID_SQLNCLI for 2005, and I haven't a clue for 2005 Express. I could use some clarification on this as well.
Thanks a ton in advance...I've always been impressed by the MSDN boards...