How To Read Data From Remote Server Inside A Transaction?
Nov 23, 2005
Hello, everyone:
I have a local transaction,
BEGIN TRAN
INSERT Z_Test SELECT STATE_CODE FROM View_STATE_CODE
COMMIT
View_STATE_CODE points to remote SQL server named PROD. There is error when I run this query:
Server: Msg 8501, Level 16, State 1, Line 12
MSDTC on server 'PROD' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 12
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].
It looks like remote server is not available inside the local transaction. How to handle that?
Thanks
ZYT
View 5 Replies
ADVERTISEMENT
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
Dec 1, 2004
Hi,
I wanted to read the transaction log...is there a way??? I want to know the command executed from the transaction logs.
DBCC TRACEON (3604)
select * from ::fn_dblog(1,null)
DBCC LOG (test,-1)
Anything else apart from these??
Regards,
Asha
View 1 Replies
View Related
Jul 28, 2000
our users believe that we lost some valid data, but no one knows who did it,
I thought I can find it from the transaction dumps I take every hour
so ,Can I read Transaction Dumps (*.TRN) file in SQL server 7.0 or Can I get this information through other means.
Thanks for your help
DP
View 3 Replies
View Related
Mar 24, 2008
Hi,
I have load a CSV file into one of the table in sql server 2005 using bulk insert command. But the csv file in remote system.
Please help me.....
View 1 Replies
View Related
Jul 20, 2005
To support remote transaction on SQL Server i have configured useroptions to 16384. However if i make changes in other properties of SQLServer, user option setting reverts to its original value. I wouldlike to know 1. Is it possible to protect this setting so that changein other properties do not effect this setting? 2. What are theproperties of SQL Server that are linked with user option settings(i.e. Changes in those properties cause reverting in user options)?
View 1 Replies
View Related
Apr 16, 2008
Hi,
I just want to know how to search text/record inside the Html files. I had one column in my database named €˜Path€™ I saved the html file names in this column and the physical files in a folder. Full Text search is enabled on this column. Whenever I try to search it returns nothing.
Just need one small example to know how it works, what functions used to search inside the files and how.
Thanks
Navi
View 8 Replies
View Related
Mar 4, 2004
I am getting a problem in executing this distributed query
CREATE PROCEDURE [sp_ec2fetch] AS
set XACT_ABORT on
begin distributed tran
select * from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=[ip];User ID=[uid];Password=[pass]'
).dml.dbo.act where code like '4010101000'
commit tran
GO
When I run it with a local LAN IP it works fine, but when I use a public ip my analyzer hangs and doesnt give me any output. I am using a dedicated dialup 64kbps connection. I have tried this query without begin distributed trans and commit trans and it worked fine, may be there are extra overheads with transactions, if so let me know
As an alternate I have tried registering server with sp_addlinkedserver and then executing a query with direct server reference, but had the same problem with it.
View 1 Replies
View Related
Jun 21, 2007
Hi All!
I have a specific requirement.
I have to generate a report on the fly.
The display fields, parameters and sort conditions would be user specified at run time in a ASP.NET web form. There will be a superset of the display, filter and sort fields out of which the user cans select one or more.
From the web form, i am taking these three parts as three strings and sending them as parameters to a Stored Procedure. The Stored Procedure will read each string, and identify what are the individual fields and generates the result accordingly.
So here my requirement is that Reporting Services must read the Stored Procedure, create a dataset and even create the User Interface all at run-time as we do not know what fields are displayed at design time. The Headers for each field come from the Stored Procedure. I have to show the report based on what are the fields in the Stored procedure at that instance of time.
I hope i have explained very clearly.
I would be grateful for your contributions.
Thanks
View 1 Replies
View Related
Mar 9, 2007
How do I stop a remote query that INSERTS into a local table from being automatically "upgraded" to a distributed transaction?
I am using Windows 2000 server and SQL Server 2000 SP3a on both machines.
I am executing the following statement in Query analyzer.
INSERT MyLocalServer (col1)
EXECUTE MyRemoteServer.Master.dbo.sp_executesql
@RemoteQuery,
@ParameterDefinition,
@Paramter = 'somevalue'
@RemoteQuery consists of a SELECT four-table join, all tables are on the same linked server.
The Linked server has been set up on MyLocalServer using the "Microsoft OLE DB for SQL Server" provider. In the "Provider Options" for the linked server properties I checked "Non transacted updates" and "dynamic parameters". In the "Server Options" tab I have checked "RPC", "RPC Out", "Data Access".
The EXECUTE part of the query runs great (and returns the data very fast) by itself. But with the INSERT part, the query fails and returns the error:
"Server: Msg 7391, Level 16, State 1, Line 17
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]."
The two servers are seperated by firewalls, so I believe the reason the query is failing is that I haven't followed the procedures for setting up the ports etc described in one of the microsoft support articles: e.g.: 250367.
Configuring the ports involves too much company politics, and besides, for what this query does, it does not need the benefits of a distributed transaction.
How can I execute my query without SQL Server automatically trying to upgrade it to a distributed transaction?
More Info: I can execute the query as a straight INSERT/SELECT linked-server query and it does the INSERT on the local SQL Server just like I want it to, so I assume it is not trying to use distributed transactions; but it takes around 7 seconds to run even though the entire SELECT is executed on the linked server, whereas executing with sp_executesql takes only 1 second.
I thought selected "non-transacted updates" in the provider would solve this problem, but it did not.
Anyone know the answer?
View 2 Replies
View Related
Sep 7, 2006
Hi to all,Probably I'm just doing something stupid, but I would like you to tellme that (if it is so), and point the solution.There ist the thing:I' having a sp, where I call other sp inside.The only problem is, the name of this inside sp is builded variously,and executed over sp_executesql:create pprocedure major_sp@prm_outer_1 varchar(1),@prm_outer_2 varchar(2)assome codingset @nvar_stmtStr = N'exec @int_exRetCode = test_sp_' + @prm_outer_1 +@prm_outer_2set @nvar_stmtStr = @nvar_stmtStr + ' @prm_1, @prm_2, @prm_3, @prm_4output'set @nvar_prmStr = N'@prm_1nvarchar(128), ' +N'@prm_2nvarchar(128), ' +N'@prm_3nvarchar(4000), ' +N'@int_exRetCodeint output, ' +N'@prm_4varchar(64) output'exec sp_executesql @nvar_stmtStr,@nvar_prmStr,@prm_1,@prm_2,@prm_3,@int_exRetCode = @int_exRetCode output,@prm_4 = @prm_4 outputNow the issue is, I've transactions inside test_sp_11 lets say wherethe 11 is @prm_outer_1 + @prm_outer_2.These procedures are existing inside database, but are called dynamiclydepending of the parameters.The problem is, when I call the specified sp directly, the rollbacktransaction is working without any problem.Inside this procedures test_sp_xx, is a call of another sp (lets sayinside_sp).There is a transaction included.When it is called over major_sp, then the rollback is not performedbecause of error:Server: Msg 6401, Level 16, State 1, Procedure inside_sp, Line 54Cannot roll back transactio_bubu. No transaction or savepoint of thatname was found.The funniest way is, if there is no error inside, the commit is workingwithout any problem!The question is majory (because I'm almost sure, that this is anissue): is it possible, to have atransaction inside dynamicly called sp over sp_executesql?If ok to do that?Thank's in advanceMatik
View 1 Replies
View Related
Oct 1, 2007
I have a trigger which requires dropping a member from a role and includes user transactions. However you cannot call sp_droprolemember from within a user transaction. Looking at the code for the procedure gives me a line
quote:
exec %%Owner(Name = @membername).SetRoleMember(RoleID = @roluid, IsMember = 0)
I can find no documentation on SetRoleMember or %%owner and attempts to run this line as an exec statement fails with "syntax error near '%'"
Nor can I find any way of dropping a member from a role using T-SQL or DDL constructs.
How do I drop a member from a role using T-SQL code and avoiding sp_droprolemember? And where do I find information about the %%Owner construct?
View 5 Replies
View Related
May 6, 2008
Hi,
When i execute the following set of statements only 8 is getting inserted into table instead 6 and 8.
Create Table BPTest(id int)
Declare @Id Int
Set @Id = 0
While (@Id < 10)
Begin
begin tran
Insert into BPTest values (@id)
if(@Id > 5)
begin
if(@Id % 2 = 0)
begin
print 'true' print @Id
commit tran
end
else
begin
print 'false' print @Id
rollback tran
end
end
Set @Id = @Id + 1
End
Select * from BPTest
drop table BPTest
Please let me know the reason for this.
Thanks in advance
Regards,
K. Manivannan
View 1 Replies
View Related
Aug 22, 2005
I'm using transactions with my SqlConnection ( sqlConnection.BeginTransaction() ).
I do an Insert into my User table, and then subsequently use the
generated identity ( via @@identity ) to make an insert into another
table. The foreign key relationship is dependant on the generated
identity. For some reason with transactions, I can't retrieve the
insert identity (it always returns as 1). However, I need the
inserts to be under a transaction so that one cannot succeed while the
other fails. Does anyone know a way around this problem?
So heres a simplefied explanation of what I'm trying to do:
Begin Transaction
Insert into User Table
Retrieve Inserted Identity (userID) via @@Identity
Insert into UserContact table, using userID as the foreign key.
Commit Transaction.
Because of the transaction, userID is 1, therefore an insert cannot be
made into the UserContact table because of the foreign key constraint.
I need this to be a transaction in case one or the other fails. Any ideas??
View 5 Replies
View Related
Feb 26, 2006
Is it normal practice to check for @@ERROR after a SELECT statement that retrieves data from a table OR we should only check for @@ERROR after a DELETE/INSERT/UPDATE type of statement? The SQL statement is inside a transaction.
View 1 Replies
View Related
May 9, 2007
Please find my second post in this thread.
Can anyone help me in sorting out the problem and let me know what might be the reason.
Thanks & Regards
Pradeep M V
View 19 Replies
View Related
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
Apr 11, 2006
I have some VB.NET code that starts a transaction and after that executes one by one a lot of queries. Somehow, when I take out the transaction part, my queries are getting executed in around 10 min. With the transaction in place it takes me more than 30 min on one query and then I get timeout.
I have checked sp_lock myprocessid and I've noticed there are a lot of exclusive locks on different objects. Using sp_who I could not see any deadlocks.
I even tried to set the isolation level to Read UNCOMMITED and still have the same problem.
As I said, once I execute my queries without being in a transaction everything works great.
Can you help me to find out the problem?
Thanks,
Laura
View 11 Replies
View Related
Sep 12, 2007
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value
OR
to do System.DateTime.Now in the application and then insert it in the table?
I figure even small differences would be magnified if there is moderate traffic, so every little bit helps.
Thanks.
View 9 Replies
View Related
Mar 24, 2008
Hi All,
I need to read a csv file, which is in remote server using SQl Bulk Insert Command.
Can I read a file Which is in remote server using BULK INSERT.
Thank you.......
View 1 Replies
View Related
Jan 15, 2007
hi:
I try to create full text for new created tables.
Since all new created tables will have same columns with different table name.
After I run the stored procedure to create table, after i got the new table name, I would like to create full-text on that table in the DDL triger.
But I got error like this:
CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.
Any one has idea how to deal with it?
Thanks
View 3 Replies
View Related
Nov 29, 2005
I use the context connection for the "normal" work in the CLR procedure.
View 7 Replies
View Related
Jan 15, 2007
I cannot manage to fetch the new timestamp value inside a TSQL Transaction. I have tried to Select "@LastChanged" before committing the transaction and after committing the transaction. A TimestampCheck variable is used to get the timestamp value of the Custom Business Object. It is checked against the row updating to see if they match. If they do, the Update begins as a Transaction. I send @LastChanged (timestamp) and an InputOutput param, But I also have the same problem sending in a dedicated timestamp param ("@NewLastChanged"): 1 select @TimestampCheck = LastChanged from ADD_Address where AddressId=@AddressId
2
3 if @TimestampCheck is null
4 begin
5 RAISERROR ('AddressId does not exist in ADD_Address: E002', 16, 1) -- AddressId does not exist.
6 return -1
7 end
8 else if @TimestampCheck <> @LastChanged
9 begin
10 RAISERROR ('Timestamps do not match up, the record has been changed: E003', 16, 1)
11 return -1
12 end
13
14
15 Begin Tran Address
16
17 Update ADD_Address
18 set StreetNumber= @StreetNumber, AddressLine1=@AddressLine1, StreetTypeId=@StreetTypeId, AddressLine2=@AddressLine2, AddressLine3=@AddressLine3, CityId=@CityId, StateProvidenceId=@StateProvidenceId, ZipCode=@ZipCode, CreateId=@CreateId, CreateDate=@CreateDate
19 where AddressId= @AddressId
20
21 select @error_code = @@ERROR, @AddressId= scope_identity()
22
23 if @error_code = 0
24 begin
25 commit tran Address
26
27 select @LastChanged = LastChanged
28 from ADD_Address
29 where AddressId = @AddressId
30
31 if @LastChanged is null
32 begin
33 RAISERROR ('LastChanged has returned null in ADD_Address: E004', 16, 1)
34 return -1
35 end
36 if @LastChanged = @TimestampCheck
37 begin
38 RAISERROR ('LastChanged original value has not changed in ADD_Address: E005', 16, 1)
39 return -1
40 end
41 return 0I do not have this problem if I do not use a TSQL Transaction. Is there a way to capture the new timestamp inside a Transaction, or have I missed something?Thank you,jspurlin
View 1 Replies
View Related
Apr 16, 2008
Hi all,
I have a column in SQL server which is of type ntext. Selecting the specific column to view it in report builder, an error message appears with the following description:
- Cannot run this report. The grouping expression 'nameofcolumn' returns the datatype binary. The Grouping Expression cannot return binary data.
Report Builder recognises this as if it was an image...
Thanks in advance!
View 2 Replies
View Related
Dec 17, 2007
Hi,
I am looking for a way to read a transaction log in an Excel format, does anyone know how I can do this? I have already looked at the dbcc log(mydb), and its not documented, doesnt give enough info. I am more looking for an open source/free tool or a way to do it in SQL Server.
Thanks much.
View 4 Replies
View Related
Sep 4, 2006
Hi there,
I need to recover certain transactions that I made on the server.
Those transactions were in scripts and I lost the files.
Is there a way to read the transaction log so that I can see what transactions were executed?
TIA
View 3 Replies
View Related
Feb 2, 2004
I want to use a stored procedure inside Sql Server from my aspx page so that the data entered in the form goes to the database after submit.
My stored procedure (inside the SQL Server) inserts several fields in a table and it returns two variables.
What code I need to write in order to pass the data from the form to the stored procedure inside the Sql Server? And to store the two returned values?
Thanks
View 7 Replies
View Related
Sep 18, 2015
I am getting an error about "Cannot perform a shrinkfile operation inside a user transaction", but I don't have a shrinkfile command in my procedure. Â Does SQL hang on to that command if it was received earlier in a different procedure?
View 5 Replies
View Related
May 11, 2004
Hi,
I need to access and read the transaction log and run an update according to the log....is it possible through sqldmo....is there any other methods through which i can do the same
View 1 Replies
View Related
May 26, 2006
Hello,
I have following situation:
1st thread: start transaction, update some values in table (with (rowlock))
2nd thread: starts select for this record.
I have tried some possibilities with SET TRANSACTION ISOLATION LEVEL but noone is suitable for me. I would like during such running transaction to have possibility to read but the OLD values (last commited).
Am I able to reach this behaviour with some setting of locks, transaction level?
View 2 Replies
View Related
Oct 30, 2006
How can I read the content of transaction log?
View 2 Replies
View Related
Apr 12, 2007
I still have transactions in use mainly to set isolation levels. There are obviously no update/delete statements in them. I'm pretty sure this will still add rows to the transaction log even though there won't be any writes to the db data files. Is it still a critical best practice to use RAID 1/0 instead of RAID 5 for the logs? Looking to re-use some existing storage rather than buy new ones. Thoughts?
View 1 Replies
View Related
Jun 25, 2015
In C# .NET I have the possible to create some validations of my data, with regulary expressions. Do SQL have the same feature? I will like to do an data validation of all my insert statement inside the sql-server. Is that possible?
View 5 Replies
View Related
