SQL Server 2012 :: TRANSACTIONS After Moving To A New Server
Jun 15, 2015
I have a piece of code that has happily run daily for at least 4 years. I have moved it to a machine that is meant to have an identical set-up, and it fails. I need to work out why it fails, but I am stuck. The code works as follows:
CREATE storedProc1 (Parameter1, etc)
DECLARE @TransactionName nvarchar(127), @TransactionActive bit
-- Plus Other Variables...
BEGIN
BEGIN TRY
SET @TransactionName= 'TransactionName1'
SET @TransactionActive = 'True'
BEGIN TRANSACTION @TransactionName WITH MARK
[Code] .....
When I EXECute StoredProc1, it fails with the Error Message "Cannot roll back . No transaction or savepoint of that name was found." I think that message comes from a ROLLBACK TRANSACTION statement in the CATCH of StoredProc2. I have tried commenting out that ROLLBACK statement, which changes the error message to: "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2."
View 3 Replies
ADVERTISEMENT
Oct 2, 2014
We have a job that is related to BI. We need to move that job to another server.
Is it better to use transfer job task in ssis or script out the job and run that script on other server?
Is there any options available to do this task?
Before move and after move what are the steps we need to take? What are the probable errors and risks we may get.
View 9 Replies
View Related
Jun 18, 2014
I have a project where I am creating a test environment. My objective is to give the client staff a set of instructions to follow to make sure the test mirrors production. I have migrated the SRS databases over to the new test server. All of the reports are there as is the security but where I run into an issue is the data sources for SRS. They are all pointed at the production servers. Is there an easy way to make the change possibly using a SQL update statement or exporting all the reports to XML and doing a find and replace. The shared data sources I went through manually and changed but there were 40 data sources. Now I realized some of the reports have embedded data connections. There are hundreds of reports to go through.
View 5 Replies
View Related
Jun 11, 2014
So I have two tables with similar columns etc, unfortunately the Insert to the History table fails. Noto sure how to format.
Source Table.
CREATE TABLE [dbo].[SKUCURRENT](
[UID] [int] IDENTITY(1,1) NOT NULL,
[SKU] [nvarchar](100) NOT NULL,
[WHSELOC] [nvarchar](50) NOT NULL,
[WEIGHT] [nvarchar](50) NOT NULL,
[Code] ....
View 1 Replies
View Related
Aug 10, 2005
I'm just moving from Access/DAO, and I'm trying to keep some of my apps with the same code but running against an MSDE backend, using SQL Passthrough, everything is fine except DBEngine.BeginTrans/CommitTrans. With DAO, against an mdb, say you want to add an invoice/then the invoiceitems, only committing the tranactions if all the invoiceitems are added ok. The ref-integrity forces you to have the InvoiceID for the new InvoiceItems. With DAO transactions you get an autoincrement number back before you commit the transaction so you can use that for adding the invoiceitems, (adding the invoice and it's items being considered to be one
"atomic" transaction.) SQL Server's transactions seem not to give you back an autoincrement number until you commit so you seem to have to split the transaction into multiple transactions which seems to defeat the point of transactions in the first place.
View 2 Replies
View Related
Mar 7, 2006
Hi All, first post.
I have done a bit of searching around and cant find a clear answer to this question.
Current Setup
Desktop application (c#) that connects to a SQL Server 2005 express database on the same local network as the application (currently 3 users)
It is only a very small company and has just taken on their first remote worker, but expects to take on another 6-8 over the next few months. They have asked for the database to be moved online.
The application was written in such a way that everything has been done using no stored procs, or views, it is all native SQL.
This will be my first DB hosted online and before I go ahead and do anything I just wanted to make sure what I have to do is correct, sorry if this is a very basic question, although I have been programming for a long time, I have never had the chance to do any online databases before.
Will this work.
1.Find a SQL Server 2005 Hosting company.
2.Move the database to the server.
3.Setup the users permissions.
3.Alter the connection string in the application to point to the new location.
So the only thing that would change would be a new connection string in the application preferences?
Or am I living in a dream world, because nothing is ever that simple.
One thing I am worried about is the security/visiblity of the database and data as it travels from the server to the client and back.
Thanks for any advise you can give.
Mark
View 2 Replies
View Related
Feb 9, 2007
hi, does anyone know how I can make a connection to a 2000 db thru ssis?
View 8 Replies
View Related
Mar 24, 2015
In an instance with 2008R2, the value of Transactions/sec in Master DB is very bigger than an instance with 2012.
Is it normal? I ask it because both instances execute the same application with the normals differences between them (the Transactions/sec's number for the application's bds is similar).
View 0 Replies
View Related
Mar 31, 2008
My searches have come up blank, so I'm hoping someone else can point me in the right direction.
I'm done some development in SQL Server Express, working on tables and procedures. I've now got to move those tables/procedures into SQL Server and I don't know of an easy way to do this. Are there any tools I can take advantage of that would allow me to get all these objects moved? The only way I can see to do it is to move objects over one-by-one. This would be really time-consuming, and I'd like to avoid it if possible.
I'm positive I'm not the first one to try this, but what should I be searching for? Are there tools/procedures I should be looking for?
Appreciate any help I can get.
Thanks.
View 2 Replies
View Related
Feb 28, 2000
We have both a production SQL 7 server, QA, and Development. From time to time, I want to move just the data from the production server to the other 2 servers without modifing the objects that may have been changed such as stored procedures and rights. Is there a way using the SQL tools provided that we can just move the data. Becuase also what happens is that the rights to the objects change which means my developers no longer have access to the tables for selects in QA since the changes where overwritten by production where they do not have the rights.
Thanks
Ricky Kelley
View 3 Replies
View Related
Mar 18, 2008
I have two databases on two different servers (an old hosting solution and a new). I need to get all the data from one server to the other but I have no clue as how to do it with MS SQL Server Management Studio Express.
I can easily make the create table scripts that will allow me to create the tables on the new server but what about all the data. I wondered if there is some way to export and import data from one server to another?
With the old SQL Enterprise Manager I was able to import or export as I saw fit but I can't seem to find anything like this with the express manager.
Anyone has any advise as I would like to avoid coding somekind of export program myself,
Many kind regards
Weinreich
View 3 Replies
View Related
May 22, 2007
We have a new server (SRV63) and I followed the instructions at http://msdn2.microsoft.com/en-us/library/ms156421.aspx to move the report server database from our old server (SRV38). Everything appeared OK, however, when I went to initialize the instance in the Reporting Services Configuration tool, it shows an initialized instance (checkbox is checked) for SRV38 and and uninitialized instance for SRV63 (checkbox is not checked). Please note that I am not trying to create a scale-out deployment. I tried to initialize SRV63 but received the error below.
--------------------
ReportServicesConfigUI.WMIProvider.WMIProviderException: The report server installation is not initialized. (rsReportServerNotActivated)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.InitializeReportServer(String installationId)
------------------
This is the only thing stopping me from shutting down SRV38 and bringing SRV63 into production. Can someone assist me with this issue?
View 4 Replies
View Related
May 23, 2006
Hi, i was after some advice on moving a SQL Server 2000 database from one server to another.
Usually i would do this by backing up the database on the original server the copying it accross the LAN to the new server and restoring it there. This database is 10Gb in size and copying it accross the LAN will take some time and i would like to minimise downtime if possible. The database is at a customer site where i am not responsible for the network or Hardware.
Thanks in advance for your help
Nick
View 4 Replies
View Related
Apr 25, 2014
how many transactions per second transactional replication can handle?
Server has 128GB RAM, 12 core 2 Process (24 core total) each transaction is approximately 32k.
Publisher and Subscriber are on different servers on same LAN, separate disks for logs, data, indexes and tempDB.
I've found data on how many transactions per second SQL Server can handle but nothing on replication.
View 1 Replies
View Related
May 9, 2007
I am using ADO.Net for data access and was wondering if anyone knows a good resource for information of sql transactions? Also, do you know if the ForEach statement can be made in sql transactions?
View 3 Replies
View Related
May 28, 2005
Hi,I am currently having dificulties in finding a way to use transactions while using classes for table representation.Let me explain, i have two tables a Customers and a Movements one, so two tables = two classes. Each class supports Insert, Edit and Delete, so if this tables worked seperatly there would be no problem but in this case whenever i create a customer i must create a movement for that customer so the connection don't pass between classes and i cannot use transactions ;(Is there any brilliant way to use transactions any other way? Even between connections or getting a solution for my implementation?Best Regards,Luis Simões
View 1 Replies
View Related
Apr 22, 2014
I have a update trigger. In this trigger I need to insert few records in 3 tables. If error comes in any of these inserts then previous inserts to get committed. This trigger was written in Sybase and it was possible to create transaction and commit the transactions.
View 4 Replies
View Related
Mar 8, 2015
So I've been monitoring long-running transactions on a SQL Server that hosts a couple of vendor-supplied databases that look after our factory.Today I noticed a pair that have confused my Excel spreadsheet (that I've been using to analyze these transactions).So here's the weird thing that I spotted. Given this query:
SELECT p.spid, p.login_time, at.transaction_begin_time, datediff(second, p.login_time, at.transaction_begin_time) as [difference]
FROM sys.sysprocesses AS p INNER JOIN
sys.dm_tran_session_transactions AS st ON st.session_id = p.spid INNER JOIN
sys.dm_tran_active_transactions AS at ON st.transaction_id = at.transaction_id
[code]....
I had a look in the event log on the server, which had just been rebooted at around that time. It seems that the clock got changed on boot-up, with the size of it quite surprising. This meant that these processes were able to start their transactions *before* they logged on. Hopefully this doesn't cause any other weird problems.So I've requested an investigation about time synchronization on our virtualization hosts... and in the mean time, have set the SQL Server services to 'delayed start'.
View 0 Replies
View Related
Jan 24, 2007
I built a web application using my VS2005 installation, which includes SQL Server Express.
All works nicely. However, when I tried to move this application to a system with SQL Server Developer, I cannot get it to work at all - data access receives several errors, with the most common being this one:
Cannot open database "xyz.mdf" requested by the login. The login failed. Login failed for user 'MACHINEASPNET'.
Here is my connection string (with specific names changed, obviously):
Data source=MACHINE;Initial Catalog=xyz.mdf;Integrated Security=SSPI;User=Me;Password=MyPassword
I have given users MACHINEASPNET and MACHINEMe all possible permissions (that I know) within SQL Server.
I have spent days on this, with no solution - help would be most appreciated.
View 3 Replies
View Related
Mar 13, 2000
I missed the days of 6.5 when I can take a database from one server create a device for LOAD on another sever and use the LOAD database command to put that database on another server that has MS SQL SERVER on it..
So what is the trick with 7.0 if DTS is out of the question??
View 4 Replies
View Related
Aug 8, 2007
I am using reporting services 2000 with SQL server 2000.
The report server is on my machine and report server database is on another sql server.
I need to move report server database from server1 to server2.I have tried the method which i found on net i.e
1-Stop the reporting services
2-detach the report server database from server1 and attach it to server2
3-reconfigure reporting services using RSconfig wherein i specified the new server name , database name and authentication.
4-Start the reporting services
5-Restart IIS
After following all these steps when i browse the report server i get the following error :
"The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable) Get Online Help "
Kindly help me on this.
View 2 Replies
View Related
May 6, 2008
I am actually the MOM administrator and only know enough SQL 2005 to keep mom working....sometimes I noticed on my dev mom box that SQL server agent has a lot of jobs configured to run against the mom onepoint database. The production box does not have these jobs. What would be the simplest way to export these jobs so that i can import them on the production box. Any help would be appreciated.
View 1 Replies
View Related
May 5, 2006
My existing SQL server is an old box that can no longer cope with the load. I need to replace it with a brand new box.
I am looking for a comprehensive set of steps that I could follow to ensure that the transition to the new server goes well.
I'm looking to transfer all my user databases but I'm not so sure whether I have to backup and restore my system databases to the new server as well.
I want to name the new server the same as the old server since I will be taking the old server out of production but I'm not sure whether I should rename the server AFTER I move over all the database and how that would affect SQL. Or should I first rename the old server, remove it out of the domain, then name my new server with the same name as my old server, and then proceed with the SQL installation and the moving of the databases.
Also, I do have a maintenance plan, DTS packages and some jobs set up on my old server that I would also like to move to the new server.
Is there a web page that explains all the steps in granular detail ?
View 1 Replies
View Related
Mar 22, 2006
Hi,I am new to SQL Server Administraion, and straight away thereoccured a case in which some users modified the database and I have totrack 'em out something this :-A table was modified on 17 - 03 -2006 and now I want to see whatqueries occured on that day how can I do that! I have seen thetransaction log but it ain't helpful. Do help me out!
View 1 Replies
View Related
Jan 24, 2007
Hello guys!
I am working with SQL Server 2005 a few months, and i need your help about transactions topic.
In first, i hope you are patient with my english, i will try to explain it the best i can ;-)) and now... my question and thanks you in advance.
I am trying to set the transaction option of a foreach loop container task in run time using the Expressions option. There, I have configured the transaction option of this task with a variable value (var type int) and this variable is informed at the beginning of my dts using a script task (values: 0 for Not Supported, 1 for Supported, 2 for Required).
The problem is the transaction option gets the variable value but... the variable value by default. After, during the dts execution, the variable gets the new value but not the transaction option.
In summary, my question is ... is it possible to set the transaction option during the run time?.
Thanks for your help!
Chris.
View 7 Replies
View Related
Oct 13, 2015
I have a requirements to collect Transactions per second on 50 databases from a sql server instances.provide me a script to collect TPS for 50 databases?
View 8 Replies
View Related
Mar 19, 2007
I have a million plus line program that uses DAO and JET/Access databases. We are modifying the code so that it will work with BOTH JET and SQL Server databases with an eye to moving to just SQL Server eventually.
Much of the code works just fine without change when using DAO and SQL server as 90+ percent of the code is accessing data READ-ONLY. (Jet's ODBC capability is used)
There are however some transactions that will not work.
When I examine the SQL Trace, I notice that after the transaction has begun, the SPID has changed. This happens often when cursors are used, BUT happens even if the only activity after a DBEngine.BeginTrans is issued DO NOT USE CURSORS as in the following code:
DBEngine.BeginTrans
strSQL = "INSERT INTO [TestTable] (MyKey,MyData, MyData2) VALUES (1,'apple','fruit')"
DB1.Execute strSQL
strSQL = "UPDATE [TestTable] SET [MyData] = 'apple2' WHERE [MyKey] = 1"
DB1.Execute strSQL
DBEngine.Committrans
The second DB1.Execute times out: [Microsoft][ODBC SQL Server Driver]Timeout expired
The trace shows a new SPID is opened when the UPDATE query is executed. A new SPID is spawned and the JET engine is sending an SELECT SQL to get the value for the MyKey:
SELECT "dbo"."TestTable"."MyKey" FROM "dbo"."TestTable" WHERE ("MyKey" = 1 )
The second SQL statement is waiting for the first one that is being done in a transaction to be completed. The program is expecting that both of these will be being done on the SAME SPID or at least the same transaction scope.
So, since I have no way of knowing how and when a new SPID will be spawned using DAO, we are not going to use it for when we do transactions.
I have tried to use ADO (Provider=SQLOLEDB.1), but ADO has the same problems as DAO as far as a new SPID being spawned in some circumstances, but it works in more situations than DAO.
The Connection Object is not guaranteed to be attached to a specific SPID - it can change at will. So Cn.Execute done twice in succession may be done on separate SPIDs.
My question is this: Is there a way to make sure that no new SPID is spawned?, Is there a way to know when a new SPID will be spawned so we can know how to avoid this situation? Is there a better way than using ADO? Keep in mind that this program has to be able to use BOTH Access/Jet databases and SQL server.
Note:
I have spent over 160 hours reading about Jet, the VB Guide to SQL Server, searching MSDN, Google etc. No article really does justice to this subject of transactions using DAO, ADO and SQL Server and the issue of the SPIDs (equivalent to a session and the session is the scope of the transaction). If you could be sure to stay on the same SPID, then you could just issue your own SQL Begin Transaction and control it all if you only plan on doing SQL statements without the need for cursors. A Microsoft article on this subject would be really helpful to all the programmers that are just now getting around to migrating to SQL Server.
Also, note, that if we did not do transactions, then we would not have to modify a single line of our code to make it work with SQL Server.
There is virtually no documentation for much of what I have written about in this Thread. I am very surprised as it seems this would have come up for thousands of programmers. I must be missing something, so thank you in advance for your help.
View 4 Replies
View Related
Mar 5, 2015
I vaguely remember reading somewhere that all distributed transactions are executed at Serializable Isolation Level "under the covers."
1. Is this true?
2. What does "under the covers" mean in this case; i.e. will I not see the isolation level represented accurately in requests?
View 9 Replies
View Related
May 5, 2005
Hi,
i wanna know how to use rollback in sql server without actually using transaction...
is there any way i can do that?
thanx in advance
View 3 Replies
View Related
May 11, 2005
hi,
i have developed an web-enabled database application using ASp.net, C# and sql server 2000.
now i want to implement transaction controls over the same
can anyone plz help me in implementing the same?
thanks in advance
View 4 Replies
View Related
Mar 16, 2004
Hi
I have a couple of sql servers registered in EM which are outside of the domain. Whenever I run queries on them with the query analyzer, they seem not to commit the transaction. When I'm about to close down the window, I'm always told that there are uncomitted transactions.
Is this some kind of a bug, and is there something I can do about it?
Thanks
Martin N Jensen
View 2 Replies
View Related
May 22, 2004
Hi all,
When i begin a distributed transaction..., do i need to always
set xact_abort on?
But when i tried to put custom error handling to roll back a transaction with a linked server , it seems its impossible (as set xact_abort on, already automatically rolls back the transaction)
Does anyone have any ideas on this?
Thanks in advance
Benny
View 2 Replies
View Related
Feb 15, 2007
Hi,
I've tried to enclose a few database operations in a TransactionScope block but it looks like SQL Server CE RM does ignores ambiental transaction.
Here is the code:
static void TestTxn() {
// Command to insert an integer in a table with a single integer column
string cmdPassText = "INSERT TESTTABLE (INTFIELD) VALUES(1)";
// Command to force field type mismatch exception
string cmdFailText = "INSERT TESTTABLE (INTFIELD) VALUES('Foo')";
using (TransactionScope scope = new TransactionScope()) {
using (SqlCeConnection conn = new SqlCeConnection("DataSource = 'Test.sdf'")) {
try {
conn.Open();
SqlCeCommand cmdPass = new SqlCeCommand(cmdPassText, conn);
returnValue = cmdPass.ExecuteNonQuery();
SqlCeCommand cmdFail = new SqlCeCommand(cmdFailText, conn);
returnValue = cmdFail.ExecuteNonQuery();
}
catch (Exception ex){
Console.WriteLine("Command failed");
Console.WriteLine("Exception Message: {0}", ex.Message);
}
}
scope.Complete();
}
}
After first command suceeds and seccond command failes table still has one affected row after transaction.
Am I doing something wrong or System.Transactions.Transaction is not supported with SQL Server CE RM?
Thanks,
Aleksandar
View 3 Replies
View Related