Automatic Rollback Inside Using Statement?
If I start a transaction using the following approach ...
using (SqlTransaction trans = destConn.BeginTransaction())
{
...do some transfers using SqlBulkCopy
}
...will an automatic rollback occur in case of unhandled errors inside the scope of the using statement?
View Complete Forum Thread with Replies
Related Forum Messages:
Automatic Rollback
I am getting an automatic rollback in a transaction that fails to insert a row, via a trigger, due to a duplicate key. The transaction is automatically rolled back and unless the error correctly handled a 'partial update' occurs. Why in the example below is the transaction automatically rolled back (statements 1 to 3) and is there any way of knowing what errors generate an auto rollback. For example if the duplicate key error occurs on the insert into table B below, and not the triggered update, then no auto rollback occurs. Begin Tran -- 1 insert into A values ('A1') -- 2 updates OK insert into B values ('B1') -- 3 updates OK -- triggers insert into B_Hist -- 4 Fails with Duplicate Key error insert into C values ('C1') -- 5 updates OK Commit Tran -- 6 3902 error No corresponding Begin Thanks.
View Replies !
Generate Script For All Databases(including All Objects Inside) In My Server Automatic
Hello, This request is for sql7 on nt4. I am searching for a sp that can generate the entire objects on all my databass in the server. Something like the 'Generate sql scripts' for sql7 but i need it to be capable to run as a scheduled job and automaticly enters in all my databases in the server. I need to take sql scripts for all my development databases every night. I have something like 80 databases in development on the server. Thanks
View Replies !
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
Problem With Rollback Statement
Hi, I have written a store procedure which inserts data into two tables. What I want do is to rollback transaction if the second insert fails. Below is a code. Does anyone see my error? Thanks, poc1010 Create proc AddProducts @dcint=null, @pcint=null, @imagepathvarchar(50)=null, @typevarchar(2)=null, @descriptionvarchar(1000)=null, @gendervarchar(8)=null, @productidint=null, @pccodevarchar(2)=null, @weightvarchar(80)=null, @pricemoney=null, @activevarchar(1)=null as declare @errorsave int set @errorsave=0 declare @dg int Begin transaction insert productdescription( designercategory, productcategory, imagepath, type, [description], gender) values(@dc, @pc, @imagepath, @type, @description, @gender) if @@error <> 0 set @errorsave=@@error set @dg = @@identity begin insert Products( productid, designergroup, designercategory, productcategory, pccode, weight, price, active) values(@productid, @dg, @dc, @pc, @pccode, @weight, @price, @active) if @@error <> 0 set @errorsave=@@error end if @errorsave <> 0 begin print 'Insert into Products tables failed' rollback transaction return -5--Insert into Products tables failed end commit transaction print 'Success' return 0 --Success
View Replies !
Can't Rollback Alter Database Statement DDL Trigger
Recently I created a DDL Server-scope trigger using the following: create trigger stop_alter_database on all server for ALTER_DATABASE as rollback; print 'database change stopped by stop_alter_database'; go Then I ran the following script: alter database [test] modify file (name=test', maxsize=2028mb); The result was: Msg 3609, Level 16, State 2, Line 1 The transaction ended in the trigger. The batch has been aborted. database change stopped by stop_alter_database The problem is that when I checked the max size of the data file it had changed. So, the statement was never rolled back. Is there something I'm missing because I can't find any documentation or articles that state the inability to rollback alter database statements. Whats going on?
View Replies !
Error: COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Why?
Hello: I am implimenting the creation of sequence numbers .I use an insert proc on a table that generates the numbers using an identity field: procedure usp_createidentity begin transaction insert into [tblOrderNumber] with default values rollback ' done so no records in this table select @OrderNumber = scope_identity() I call this from another proc that inserts values into my order table: procedure usp_Insert @OrderNumber int as SET XACT_ABORT ON; BEGIN TRY BEGIN TRANSACTION EXEC usp_GetNewOrderNumber @OrderNumber = @OrderNumber output INSERT INTO [dbo].[tblOrder] ([OrderNumber]) values (@orderNumber) ' inserts value from other stored proc COMMIT TRANSACTION END TRY BEGIN CATCH if (XACT_STATE() = -1) ROLLBACK TRANSACTION else if (XACT_STATE() = 1) COMMIT TRANSACTION END CATCH Here is the problem. When I run usp_Insert I get the following: Error 266 Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0. This refers to the usp_GetNewOrderNumber that is called inside the other proc as shown above. The problem does not happen if I put each statement in usp_Insert in its own try/catch. transaction statements. Maybe it has something to do with the rollback call in the usp_getneworder. What do I need to do to get rid of this. problem and still run these within one try/catch trans statement set. Thanks
View Replies !
Using A While Inside A Select Statement
Hi All, Can we use the while loop inside a select statement? Meaning, something like this: Code Block SELECT DATE, WHILE (SELECT TOP 1 DATEPART(HH,DATE) FROM SC_DATEDIMENSION_TABLE) <= 23 (SELECT DATEADD(HH,6,SC_DATEDIMENSION_TABLE.DATE) ) FROM SC_DATEDIMENSION_TABLE What I want to do here is I have a table which has all the dates but with time only representing 00 hrs. I want to display this column and along side, I want to have another column, which displays the date split at 6 hours. So, one left column, there will 4 columns on the right. Hope the question is clear. Thanks a lot. Mannu.
View Replies !
Select Statement Inside UDf
iam trying to rerieve a certain value from one table and i want to use that vaue inside a UDF iam usinf a table valued function as i have to retireve no of values Can i do something like this to retrieve the value SET @Value=Select Value from Table WHERE xyz='some no.' as this value is being calculated by some other fucntion and now this funcation has to use this at runtime.
View Replies !
If Statement Inside Sql Query??
Hello, I have an if statement for one of my columns in my query. I want to write the if statement as a part of my select statement. How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this. I could use the "decode function" but I have something like this: if column1 = '1' or column2 = '2' then select "Yes" etc.. I tried doing select decode (column1 or column2, , , ) but it doesn't work. Any ideas?
View Replies !
If Statement Inside Sql Query??
Hello, I have an if statement for one of my columns in my query. I want to write the if statement as a part of my select statement. How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this. I could use the "decode function" but I have something like this: if column1 = '1' or column2 = '2' then select "Yes" etc.. I tried doing select decode (column1 or column2, , , ) but it doesn't work. Any ideas?
View Replies !
Counter Inside Select Statement?
Hi, can you add a counter inside a select statement to get a unique id line of the rows? In my forum i have a page that displays a users past posts in the forum, these are first sorted according to their topicID and then they are sorted after creation date. What i want is in the select statement is to create a counter to get a new numeric order. This is the normal way: SELECT id, post, comment, created FROM forum_posts WHERE (topicID = @topicID) ... some more where/order by statements This is what i want: DECLARE @tempCounter bigintSET @tempCounter = 0SELECT @tempCounter, id, post, comment, created FROM forum_posts WHERE (topicID = @topicID)... some more where/order by statements and at the end.. (SELECT @tempCounter = @tempCounter + 1) Anyone know if this can be done?
View Replies !
Calling Variable Inside T-SQL Statement
Can someone please take a quick look at this and tell me what I'm doing wrong I'm sure it's something simple. I'm a little new to stored procedures but I've been using SQL and T-SQL for quite some time, I've just always used inline queries with my ASP. This procedure needs to be run monthly by me or another person I grant access to and will update sales information that our sales staff will be paid commission on. I need to supply the start date and and end date for the query and it will pull this information from our business system which is hosted remotely by a third party and pull it into our local SQL server where we can run commission reports against it. (I hope this is enough information you can understand where I'm trying to go with this). I know my problem right now lies in how I'm trying to call the variable inside of my T-SQL. Any help is appreciated. This is an old Unix system and it stores the date as YYYYMMDD as numeric values incase someone wonders why I have dimed my dates as numeric instead of as datetime =) I'm using a relativity client to create an ODBC connection to the UNIX server and then using a linked server to map a connection in SQL this is the reason for the OpenQuery(<CompanyName> SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: XXXXXXXXXXXXX -- Create date: 10/4/2007 -- Description: This proc is designed to pull all CSA -- part sales from XXXXXX business system and upload them -- into the local XXXXXXXX Database for commission reporting -- =============================================CREATE proc usp_CSAPartsSalesUpdate@date1 int, @date2 int As INSERT INTO CSAPartsSales ( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount ) SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmt From OpenQuery(<CompanyName>, 'Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmtFROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo WHERE (((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>=''' + @date1 + ''' And (PMINVHST.HInvDate)<=''' + @date2 + ''') AND ((Trim([CPBASC_All].[SalesRoute]))<>'''' And (Trim([CPBASC_All].[SalesRoute]))<>''000''))') In this example date1 will be equal to 20070901 and date2 will be equal to 20070930 so I can pull all CSA sales for the month of September. This is the error message I get when I try to create the proc: Msg 102, Level 15, State 1, Procedure usp_CSAPartsSalesUpdate, Line 17 Incorrect syntax near '+'. ~~~ Thanks All~~~
View Replies !
If Statement Inside A Stored Procedure!!!!
hi all, i'm wondering if i can use one stored procedure in too cases, this is the senario: i have stored procedure for admin and another one for user, they have the same select everything is the same except that in admin SP i have where @admin = admin and for user i have where @user = user if there a way to use if and else to make this happen this is what i did so far: CREATE PROCEDURE [test] @admin INT, @user INT, @indexType INT as if @indexType = 1 begin SELECT * FROM table WHERE something IN (SELECT * FROM anothertable where admin = @admin) end else begin SELECT * FROM table WHERE user = @user end GO any suggestion will be very helpful thanks
View Replies !
'Case' Statement Inside 'Where' Clause
Hi I've been trying to put a simple case statement into my 'where' clause but having no luck, is there another way to do the following? DECLARE @searchCriteria Int SET @searchCriteria = 2 SELECT column1, column2 FROM TABLE WHERE CASE @searchCriteria WHEN 1 THEN (column3 = 1000100) WHEN 2 THEN (column3 = 1000101) END CASE ...cheers
View Replies !
Declare Inside Select Statement?
I have a need to execute a cursor inside a select statment, but I'm having problems figuring this out. The reason this need to be inside a select statement is that I am inserting the cursor logic into a query expression in PeopleSoft Query. So! Here's the statement that works: ====================== DECLARE @fixeddate datetime DECLARE @CVG_ELECT char(1) DECLARE @Effdt datetime DECLARE EFFDTS CURSOR FOR SELECT Z.EFFDT, COVERAGE_ELECT FROM PS_LIFE_ADD_BEN Z WHERE Z.EMPLID = '1000' AND Z.EFFDT <= GETDATE() AND Z.PLAN_TYPE = '20' ORDER BY Z.EFFDT DESC OPEN EFFDTS FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT WHILE @@FETCH_STATUS = 0 BEGIN if @CVG_ELECT <> 'E' break ELSE SET @fixeddate = @Effdt FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT END CLOSE EFFDTS DEALLOCATE EFFDTS PRINT @fixeddate ====================== If I execute this in SQL Query Analyzer it gives me the data I am looking for. However, if I try to paste this into a select statement, it goes boom (actually, it says "Incorrect syntax near the keyword 'DECLARE'.", but you get the idea). Is it possible to encapsulate this inside a select statement?
View Replies !
Select Case Inside Sql Statement ?
Code: function findingcinemaid(nameofthecinema) findcinemaid = "select cinemasid from cinemas" &_ " where brand = 'tgv' and cinemaplace2 like '"&nameofthecinema&"'" set cinemaidfound = objconndb.execute (findcinemaid) end function select case foreachcinema case 0 cinemaname = "ONE UTAMA" findingcinemaid(cinemaname) case 1 cinemaname = "MINES" findingcinemaid(cinemaname) case 2 cinemaname = "SEREMBAN 2" findingcinemaid(cinemaname) case 3 cinemaname = "KINTA CITY" findingcinemaid(cinemaname) case 4 cinemaname = "BUKIT RAJA" findingcinemaid(cinemaname) case 5 cinemaname = "TEBRAU CITY" findingcinemaid(cinemaname) case 6 cinemaname = "SUNWAY PYRAMID" findingcinemaid(cinemaname) case 7 cinemaname = "SURIA KLCC" findingcinemaid(cinemaname) end select any possible way I can merge this select case statement with the sql statement ? I try if else but too many code , defeating the original purpose of simplfying it
View Replies !
Help: Table Name Displaying Twice In SQL Statement Inside SQL Server Enterprise Manager
For some reason whenever I look at the SQL statement of a particular table, the table name displays twice.For example,SELECT * FROM State StateEven when I execute this statement, it still returns the correct results. It does this for all tables in this particular database. I also check another database and thoses display the table names in the SQL statements correctly. Does anyone know why the table name would display twice in a table inside of a particular database?
View Replies !
Need Info About Dynamic Reporting (Read Problem Statement, Inside)
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 Replies !
EXEC Inside CASE Inside SELECT
I'm trying to execute a stored procedure within the case clause of select statement. The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant. @val1 and @val2 are passed in CREATE TABLE #TEMP( tempid INT IDENTITY (1,1) NOT NULL, myint INT NOT NULL, mybool BIT NOT NULL ) INSERT INTO #TEMP (myint, mybool) SELECT my_int_from_tbl, CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0 FROM dbo.tbl WHERE tbl.val2 = @val2 SELECT COUNT(*) FROM #TEMP WHERE mybool = 1 If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that. Any suggestions?
View Replies !
Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False 'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i ' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
View Replies !
Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it? What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results. Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application. However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password. Looking forward for replies from expert here. Thanks in advance. Note: Hope my explaination here clearly describe my current problems.
View Replies !
Automatic SQL Backup
Hi, We are working on creating an automatic backup tool for our web application. Our goal is to run a script that "zips" the virtual host/application directory. We have the script to zip the application directory, but is there a way to run a SQL Backup and then zip the output easily? This is where we are struggling. Any Suggestions are appreciated Brent
View Replies !
SQL Automatic Growth
We use SQL 2000 and our database is configured to grow automatically by10%. Currently 96% of our database is used. At what point will thedatabase expand - what is the trigger point?
View Replies !
Automatic Fields
Hi,it's possibile to define table fields for automatic Last_Update_Date and forCreation_date using column formula??Thanks !!!
View Replies !
Automatic Process
I need to automatically generate via SQL, export to Excel and e-mailedto other people montly. what should I setup in the sql server?
View Replies !
Automatic Updates
I have two sql 2000 server tables one is active and one is terminated (I inherited this db) and I was thinking of having the active table automatically update the terminated table when an employee is terminated. Access is at the front end and in the form of the active table theres a drop down text box that has the employement status(active, terminated, on leave...etc) so when the status turns into terminated i want the acitve table to send those records to the terminated table, ( the data in both tables are not exactly the same). looking into a trigger or stored procedure. This is the first time I've done this so I'm doing some reasearch on how to handle it. Any suggestions
View Replies !
Automatic Updates
well as I get further into this project of automatic updates I'm fining more and more barriers. The combo list box which indicates whether the employee is terminated or active might be a problem with sql since you cant create a Row source and a Row source type in a sql table. that combo box exsist in the properties of the form. The Row Source Type is a Value List. Shoot :(
View Replies !
Automatic Job Monitoring
I'm a SQL Server 7/2000 DBA and manages about 40 servers in different networks. Every morning I check through the Enterprise Manager if all Jobs (backup, maintenance, etc.) have run successfully. This check costs me 1 hour per day. Because of a reorganization I've got some new college's and lost some college's. My new college's think this is to much work, so it should be automated. They only want the failed jobs to report an error on a website or something like that, and don't want to check 40 servers. I don't agree in this, because I'm affraid I'm going to miss some errors. How do you do your checks every morning? Thanks, Marco
View Replies !
Automatic Number
I don't know very much about Sql Server and need help. I have two primary keys called AirlineCode e ID in the same table. This id is an automatic number. So I don't need to input this value, since it's created automatic. What I need is this ID be created automatic but for each new AirlineCode the ID starts as 1 again. For example : If I put AirlineCode = 220 then ID starts as 1. In another row AirlineCode is also 220, then ID is 2. But in another row AirlineCode = 500, then this automatic number come back to 1. then the rows can be like this : AirlineCode ID 220 1 220 2 500 1 220 3 300 1 500 2 I think I have to create a script to make this, but I don't kwon how... Ask someone to help-me Best Regards Marcelo
View Replies !
Automatic Recovery
Dear Friends, I want to Automatically take Backup at 7:00 p.m cutoff Time.and automatically restore the database..to a temporary database,generate my report and delete the database. Friends,this backup & restore should not require any user interface..it should me automatic. Plz can anyone help me out. Regards Salim g belim
View Replies !
Automatic Updation
Hi all, I am using the odbc connector to get a local table. I want that table to get populated every night automatically. How can i do that? Also i want to check if any existing record is updated/changed in the dsn database. Any help is highly appreciated. Thanks, Jeff.
View Replies !
Automatic Email
Dear Freind, Its all boutt Dts.I have already created a DTSpackage and Activex script in VBscript to retrieve some particular names ...and i want to send those names through E mail.But the email should be automatic(using sql Sheduler ).Real Problem is...How can i write the code to access the DTS object from ASP.Net with VB? and How can shedule...please help me...i am hopefully waiting..........thanks in advance
View Replies !
Automatic Update
I hope that a someone has answer for me. I want create a new database in sql server and import the database tables of Dynamics CRM and of other application (Accountview) import so that I can make a link of these two databases. My question is it possible that this database update or refresh automatic and how? The database of Dynamics CRM manages by sql server. But the database of accountview is not manages by sql server. I make a link with odbc driver for Accountview database. Can someone help me please?
View Replies !
Automatic PDF Output
Normally if I want a report in PDF I have to first view the report in the web browser, then select export as PDF and save the PDF file. Is there a way to avoid the first step (web browser viewing) and have the report immediatly in PDF as soon as I click the "View Report" button? (without using email/file subscription) Thank you, Roberto
View Replies !
Automatic Refreshing
I am wondering if it is possible to have a report generated by RS refresh periodically automatically. This could be realized by inserting a few lines of JavaScript to the report including the reload() function, but I do not know if there is anyway to do such thing. Thanks in advance for any tip!
View Replies !
Automatic Drilldown
Hi! I'm experiencing whit a strange behaviour in reporting services and drilldown: I۪ve a report with a graphic and nothing else. If I publish the report through a Windows Server 2003 R2 I can click on a element in the graph and a new report is shown. But if I publish the report by a windows XP Pro I see the report correctly but if I click on a element in the graph nothing happens. Does it depend on the SSRS configuration or is the behaviour OS related? Thanks in advance. Al̩x
View Replies !
Automatic RTRIM????
Could anyone explain why this happens: -- All outputs works but only the first should select 'works' where '1' = '1' select 'works' where '1' = '1 ' select 'works' where '1 ' = '1' Seems to me like trailing blanks are automatically trimmed, why?
View Replies !
Automatic Failover... Help
Hi there, We've recently set up a Principle, Mirror and Witness configuration with the Mirror and Witness in a separate building to the Principle. All three are part of the same domain (DMZ) and are different servers, the buildings are connected via a fiber optic cable. All servers and SQL Server instances are logged in with the same domain admin account DMZesAdmin. Mirroring is all set-up and the databases are synchronized. Every once in a while some (not all, normally 6 out of 15) databases will switch roles and become active on the mirror. The SQL Server mirroring monitor job then reports: Date 25/01/2007 12:37:01 Log Job History (Database Mirroring Monitor Job) Step ID 1 Server DMZSQL01 Job Name Database Mirroring Monitor Job Step Name Duration 00:00:02 Sql Severity 16 Sql Message ID 32038 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Executed as user: DMZesadmin. An internal error has occurred in the database mirroring monitor. [SQLSTATE 42000] (Error 32038). The step failed. I have no idea, what causes the failover, it could be a slow network or a bad set-up, can anyone give me some ideas of what to do to track down the problem or any experience of what could be causing this, it happens randomly every day or three. No warning and if I go to the mirror and failover back to the principle again then it's all just fine. However I don't want half my databases working on 1 server and half on the other. Any ideas? Thanks Ed UPDATE: I've just been looking at the logs on my Mirror and at the same time it reports in this order Error: 1479, Severity: 16, State: 1. The mirroring connection to "TCP://DMZSQL01.dmz.local:5022" has timed out for database "WARCMedia" after 10 seconds without a response. Check the service and network connections. Database mirroring is inactive for database 'WARCMedia'. This is an informational message only. No user action is required. Recovery is writing a checkpoint in database 'WARCMedia' (41). This is an informational message only. No user action is required. The mirrored database "WARCMedia" is changing roles from "PRINCIPAL" to "MIRROR" due to Failover. Database mirroring is inactive for database 'WARCMedia'. This is an informational message only. No user action is required. ... This looks like a time out, is there any way to set the TimeOut threashold for Database mirroring or set retry intervals??
View Replies !
Automatic Installation
I have a small desktop application that is distributed in CD. The client installs the application without any attendance, therefore I need that everything is completely automatic. Is it possible to install SqlServer Express in that way?
View Replies !
Automatic Update
hi, i have: create table tbl_order ( id int ,price float ,quantity int ,product nvarchar(50) ,date_of_order smalldatetime ,date_of_change smalldatetime ) insert into tbl_order (id, price, quantity, product, date_of_order) values (1, 23.21, 2, 'A+B', getdate()) insert into tbl_order (id, price, quantity, product, date_of_order) values (2, 22.21, 2, 'A+B+C', getdate()) and i want to make automatic update: -if new order is made, i want to update field date_of_change with getdate(). i want to put it in a job with some trigger or something that will recognize instant new insert. thank you in advance,
View Replies !
Automatic ID In A Primary Key?
CREATE TABLE [dbo].[tblProve]( [ID][varchar](50)NOT NULL, [tempCountrys][varchar](100) NULL, CONSTRAINT [PK_tblProve] PRIMARY KEY CLUSTERED ([ID] ASC) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO I have a table "dbo.tblProve" that have a Primery Key ID of type varchar(10) and this primary key have a Default value or Binding like this: ([dbo].[fnc_ProveNewID]()) This function "dbo.fnc_ProveNewID()" finds the max(ID) in the same table and creates the next ID. When I insert a record into the table dbo.tblProve the function creates the new ID automatically (this is the format of the ID '0000000001' or '000000010' etc. ). But when I insert a lot of records from a Select Query it displays this message below: Msg 2627, Level 14, State 1, Line 2 Violation of PRIMARY KEY constraint 'PK_tblProve'. Cannot insert duplicate key in object 'dbo.tblProve'. The statement has been terminated. Is there any way to create a new ID into a table automatically without to be inserted by the user or client?.
View Replies !
Automatic Scheduling
I have initiated database backup plan in 2005,occuring every 2 days.backup plan worked for the first 2 schedules,later backup plans is not working.I have checked the time and date setups,its all fine.how to solve.
View Replies !
Automatic Failover
Hi there, I am testing the db mirroring, making sure it will auto failover. I've stopped the SQL services on my principal and then I looked at the mirror db is says it's restoring. It stayed like that for 10 min before I enabled the mirroring again. Anyone knows why it's not failing over?????? Here's my setup: SQL 2005 Standard, Server 1 Principal, Server 2 Mirror & Witness.
View Replies !
|
TRACKER
