Seek expert’s advice on database mirroring
in high safety mode when safety is full if for some reason communication between principal and mirror is broken for say 2 days then principal will keep working but in disconnected mode. When communication link will establish again then principal can see the mirror server
1. Will establish the mirroring automatically?
2. Then will it synchronise the mirror databases automatically?
3. If it will synchronise databases automatically then principal will process transaction
or wait till principal-mirror will be in synchronised state which again depends on
number of transaction need to redo on mirror?
Trying to create mirroring in SQL Server 2005. I followed the steps through Security Wizard. For miror server instance connect I used 'SA' login and pwd..I m not using witness..as i clicked on finish I got below error. On pricipal server i got error message but on mirror server I got success message.
error message:
TITLE: Microsoft SQL Server ------------------------------ SQL Server cannot create the mirroring endpoint, 'Mirroring'. ------------------------------ ADDITIONAL INFORMATION: Create failed for Endpoint 'Mirroring'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Endpoint&LinkId=20476 ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments. To enable database mirroring for evaluation purposes, use trace flag 1400 during startup. For more information about trace flags and startup options, see SQL Server Books Online. (Microsoft SQL Server, Error: 1498) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1406&EvtSrc=MSSQLServer&EvtID=1498&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------
Select @@version for Principal server is Microsoft SQL Server 2005 - 9.00.1406.00 (Intel X86) Mar 3 2007 18:40:02 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Select @@version for Mirror server is Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
I searched some google pages and got information that I need to enable DB Mirroring. I followed To configure startup options : and add -T 1400 there and restart service.
also run T-SQL
CREATE ENDPOINT [Mirroring] AS TCP (LISTENER_PORT = 5022) FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = ENABLED);
but getting error Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'ENABLED'.
also this ALTER ENDPOINT [Mirroring] STATE = STARTED still get error: Msg 1088, Level 15, State 100, Line 1 Cannot find the object "Mirroring" because it does not exist or you do not have permissions.
Nothing i m seeing on Principal server but getting onw row on mirror server
SELECT * FROM sys.database_mirroring_endpoints;
But still it didnt work for me
One more thing what happened if i did
ALTER ENDPOINT [Mirroring] STATE = STOPPED
on mirror server?? Mirror server remain as it is as it was before doing all above steps???
HI i have two server one is active server called server1 and the other is mirror server called server2 today i am facing a problem. the problem is my all database which is configured for mirroring showing the green up arrow plus this message (Mirror, Synchronized / Restoring...) and i cannot open the databases and showing error message that the database is acting mirror database these two were working fine from last year but today i dont know why its happen and also in log i can see the message with the database configured for mirroring is below
Bypassing recovery for database '' because it is marked as a mirror database, which cannot be recovered
I have a website called Project. It has 2 database files. one is ASPNETDB.mdf and another one is project.mdf.
The last time I edited my website was this afternoon. nothing wrong. Then tonight, when I try to work on the project again, I found out that I couldn't access the project.mdf database file. The error message occurs when I try to expand the project.mdf:
Cannot open user default database. Login failed. Login failed for user 'DefaultAndy'
I believe Default is the name of my computer and Andy is the name for my windows account. I use Windows authentication for that database file.
This is the funny thing. If I rename my project folder under the My DocumentsVisual Studio 2005WebsiteProject to something else, I will be able to open the project.mdf file.
Does anybody have any idea what is going on??
I really appreciate and thank you soooo much for your time !
I have installed SQL Server 7.0 SP2 on a couple of servers and have been trying to get the DTS package to run. Unfortunately when I try to create a new DTS package, I get the foll DTS Designer error:
"Could not create Component Categories Manager"
I've tried using the DTS Import/Export wizard only to keep getting a Dr Watson error....What am I doing wrong??
Actually we have already backed up the Very large database(1.1 TB). We are trying to migrate the backup file to other server . But the copy is running really slow because of slow network banwidth. What are best options to transfer terabyte file from one server to other. Thanks in advance
Guys, I really need help with this one. I am populating the data warehouse and have to deal with some bad sourse databases. In one table they have 28 columns, used as flags. Each column represents a particular facility. Each record can have 0 to many facilities selected, so this table could have NULL or 'Y' in one or many of those columns. That's a terrible DB design. They should have build a 1-to-many table relationship, but instead, they put everything in one table. And the other funny thing is that those 28 columns are literally named Column1, Column2 ... Column28, and then the actual facility names are HARDCODED in their application codes !!! So, the table might look like this: Col1 Col2 Col3 Col4 Col5 .... Col28 -------------------------------------------------- NULL Y NULL NULL Y NULL Y NULL NULL NULL NULL NULL NULL Y NULL NULL NULL Y ........... NULL Y NULL NULL Y Y
My task is to get for each record the first 4 not NULL facility NAMES. I have a script that does that, but this script is very primitive and is huge ! I kind of do 4 runs through this file. First run I get the record ID for each record, and populate the first of the 4 facility names. I am just using CASE statement and check each field if it's NOT NULL then enter the actual hardcoded name. I put all this into a #temp table. The second run I join the main table with this #temp table, again getting the record ID and the first populated Facility Column 1, and then create a Second Facility field, by using the CASE statement again, going through each field, checking for NOT NULL AND NOT being the same as the First Facility name... And so on... Like I said, this is working, but I was just wondering is there is a simpler way of doing this.
Any help or suggestion will be greatly appreciated.
Using SQL Server 2008, we would like propose mirroring between two servers of a critical database. Since we initiate, may require to clarify on its purpose and also required changes from application end.Any changes required from OS Level? (I believe both servers IP or Host name should be added in host entries. Mirroring ports should be allowed/open including Principal and mirror server IP Addresses): Windows Team.Any changes required from Application? (Instance name, authentication: user name and its password should be added in web config files): Application Team.Any changes required from Network Team?Also for mirroring both the principal and mirror servers should be with same version, does it only mean SQL Server 2008 versions are enough or does it also mean to say build numbers 10.00.4000 should also be same.URL....
We run a site through asp.net (programmed in visual studio.net) and the site's beginning to get a lot of problems (as the site grows)
Here is the latest error message. I was wondering if someone could help me troubleshoot this.
Server Error in '/' Application. --------------------------------------------------------------------------------
Transaction (Process ID 145) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Transaction (Process ID 145) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException: Transaction (Process ID 145) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.] System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +44 System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +5 System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +36 Saltwaterfishing.Components.DB.RankResults.SearchAllSites(String query, Int32 currentPage, Int32 pageSize) in ***************ComponentsDBRankResults.cs:43 ***************.Controls.RankResultsGrid.BindDataGrid() in ***************\ControlsRankResultsGrid.ascx.cs:82 ***************.Controls.RankResultsGrid.ShowResults() in ***************ControlsRankResultsGrid.ascx.cs:68 ***************.Main.ShowPage() in ***************\saltwaterfishingmain.aspx.cs:75 ***************.Main.Page_Load(Object sender, EventArgs e) in ***************\main.aspx.cs:43 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +35 System.Web.UI.Page.ProcessRequestMain() +731
I am facing a query problem.i have 2 tables called emp-table and report-table.report table for listing report of employees. following are criterias..
1 I want list all reports from report-table 2 But no report should have empstatus 5 3 reports need not necessary to have emp-table reference.ie emp-id reference in emp_table. briefly saying i want all reports from report table,in which no one have empstatus 5
i need urgent help sorting out my SQl please.... i have 6 tables Student class lectures student_results attendance
my case study says " the system should be able to record attendance of student to any class. the system shoul also be able to automatically send out a warning to any student that misses any two lectures for the first time."
can anyone help me solve this problem on SQL (oracle) please...
Hi, I am trying to Implement Multi parameter... If i give NULL it works fine but if i give '7,4' I get this error message
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near '17'.
This is my sproc
Code Block ALTER Procedure [dbo].[usp_GetOrdersByOrderDate] @ClientId nvarchar(max)= NULL, @StartDate datetime, @EndDate datetime AS Declare @SQLTEXT as nvarchar(max) if @ClientId is null Begin Select o.[OrderId], o.[OrderDate], o.[CreatedByUserId], c.LoginId, o.[Quantity], o.[RequiredDeliveryDate], cp.PlanId, cp.ClientPlanId, cp.ClientId FROM [Order] o Inner Join ClientPlan cp on o.PlanId = cp.PlanId and o.CreatedByUserId = cp.UserId Inner Join ClientUser c on o.CreatedByUserId = c.UserId WHERE --cp.ClientId = @ClientId --AND o.OrderDate BETWEEN @StartDate AND @EndDate ORDER BY o.OrderId DESC END ELSE BEGIN SELECT @SQLTEXT = 'Select o.[OrderId], o.[OrderDate], o.[CreatedByUserId], c.LoginId, o.[Quantity], o.[RequiredDeliveryDate], cp.PlanId, cp.ClientPlanId, cp.ClientId FROM [Order] o Inner Join ClientPlan cp on o.PlanId = cp.PlanId and o.CreatedByUserId = cp.UserId Inner Join ClientUser c on o.CreatedByUserId = c.UserId WHERE cp.ClientId in (' + @ClientId + ') AND o.OrderDate BETWEEN ' + Convert(varchar, @StartDate) + ' AND ' + convert(varchar, @EndDate) + ' ORDER BY o.OrderId DESC' execute (@SQLTEXT) END
Hi, I am using this stored procedure to populate a report... if the particular report value is NULL i am getting this error
Cannot read the next data row for the data sest PlanEligiReqattri. Invalid lenght parameter passed to the right function...
This is my stored procedure
Code Block ALTER Procedure [dbo].[rpt_PlanEligiReqAttriApplied] @PlanId int AS BEGIN Declare @List varchar(255) Set @List =' ' Select @List = @List + ', ' + PERA.Name
From PlanEligibilityReqAttriApplied PERAA Inner Join PlanEligibilityReqAttributes PERA on PERAA.PlanEligibilityReqId = PERA.PlanEligibilityReqId Where PlanId = @PlanId select Right(@List, Len(@List) - 2) --set @List = Right(@List, Len(@List) - 1) End
I want to have a page break between the first set and the second set. The last subreport of the first set, Subreport6A, has a pagebreak at end = true, so it should break. The problem is that when I export to Excel I am getting one sheet for the 12 subreports.
Hi i am trying to transfer the contents of a dbf file to sql server 2005 and this is my error message 'C:Documents and SettingsKarenMy DocumentsVisual Studio 2005WebSitesASC1pdfclientAS.DBF' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. This is my code If (FlAS.PostedFile.FileName.ToLower.EndsWith(".dbf")) Then FlAS.PostedFile.SaveAs(location) Try 'Connection string to a dbase file Dim dbfConnectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source ={0};Extended Properties= dBase IV", location)
'create connection to the DBF file Using connection As Data.OleDb.OleDbConnection = New Data.OleDb.OleDbConnection(dbfConnectionString) Dim command As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("Select * from AS.DBF", connection) connection.Open()
'Create a dbDatareader to the dbf file Using dr As Data.Common.DbDataReader = command.ExecuteReader() Dim sqlConnectionString As String = System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION) Dim myConnection As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION)) Dim query As String = "Truncate table ASDBF" myConnection.Open() Dim cmd As New SqlCommand(query, myConnection) cmd.CommandType = CommandType.Text cmd.ExecuteScalar() myConnection.Close()
'bulk copy of sql server Using BulkCopy As SqlBulkCopy = New SqlBulkCopy(sqlConnectionString) BulkCopy.DestinationTableName = "ASDBF" BulkCopy.WriteToServer(dr) End Using End Using connection.Close() End Using
Hi, I am trying to insert the Source name and clientId to a table called clientSource...
The User will send in some Dbf File.... So in a particular file called PlanDbf.. I have the following fields PlanNumber, Name, SRC1Name, SRC2Name, SRC3Name.... SRC20Name 170234 Constructions Employee Deferral Employer Discretionary Employer Matching....
And in another table called SourceDBF i have the following fields with data PlanNumber PARTID SOURCE_NUM etc... 170234 123456789 1 170234 123456789 3 170234 451231334 1 So how do i match the Source_NUM with SRCnames when i insert it into the table.. INSERT INTO Statement..ClientSource(@ClientId, SourceName)SELECT s.SOURCE_NUM FROM SourceDBF .. but i am stuck.. any help will be appreciated. Regards Karen
Msg 2503, Level 16, State 1 Table Corrupt: Page Linkage is not consistent; check the following pages: (Current page#=298065; page pointing to this page =286585; previous page# indicated in this page=283321).
Can somebody urgently help? Any help will be appreciated.
Hi everybody! I create my database by restore from server’s backup. Every object there is owned by dbo77 user. I want to execute stored procedure in following way: exec sp1 and not – exec dbo77.sp1, when I connect with the same user. I create new Login – dbo77 in Securities folder of my server (LOCAL). And when I connect by this user to that database, I still can’t use syntax: exec sp1. An error says that my login dbo77 does not have an associated user name. When I try to mark that database in dbo77 ->property->Database_Access it writes an error that dbo77 already exists. How can I make this db work with syntax exec sp1, if I login with same login name as the owner of sp1?
I have a stored procedure which activates a task when ever that sp is called. and this procedure gets out of the loop.
the task get started running.
Is there any way in Sql server that the same stored procedures should wait until the Task finishes and return a code either 1 or 0 for success or faliure of the task.
Hi all!I am in need of writing a few stored procedures.The first one is to create a stored procedure to recover a databasefrom backup and the second one is to create a stored procedure toexecute a transaction log backup (even though I know this can be donethrough a maintainence plan). Any help would be greatly appreciated.I know the commands to recover and backup -- but I dont know how toformulate them into a stored procedureThanks in advance!
urgent help needed regarding transactional replication2005
I did set up transactional replication on sql 2005 and also in article properties have selected to replicate foreign keys,default constraints and indexes. I checked the schema properties at publisher and subscriber and found that on one table a foreign key is not replicated which has an on delete cascade. I am not sure why is not replicating that constraint.The other table has a foreign key which is also not replicated.
I have a date "4/25/2007" but the requirement is to have data as "04/25/2007" .if month is single digit it should have leading zeroes, same with date, can some tell me how to convert this.
I have a execute SQL task in which i have a delete statement with global params.
delete from <tname> where (strval=?) and (year=?)
both are varchar type variables.
my strval column gets a value something like "DecData1.0". But when i ran my DTS package it is ignoring this SQL statement. Might because of the special char '.' in "DecData1.0".
How to handle such situation.
I also tried by putting a single quote. delete from <tname> where (strval='?') and (year=?) But it is giving an error.
I had only 40 mb of disk space in the c drive. could that be the reason. if so, how can I turn on the server and get into win NT and make the server up and running. I hope someone can reply as soon as possible
Hi every one I am new meber of this community with an urgent new problem with SQL server DTS process:) pls HELP! PROCESS DETAILS:
My data base(db) is on main frame(flat file with hierarchical db format) I used vb script to get that db and split it in to three files so processing is easy after this I use dts PROCESS WITH EXECUTE SQL TASK in combination of some bulk insert scripts to pick these files from server directory and import it in to sql server tables. I know I could have use bulk insert task in dts but my formate file was giving me a problem while I try to develop it through that process so I ended up using tsql bulk insert statment with formate file in the EXECUTE SQL TASK .
PROBLEM : My problem is I have to manually activate VB script where as I want this to be part of my DTS packet so it can be automated.
I know there is Active x scripting task in DTS but have not used it can some one please tell me if this script can be put in to Active X scripting task with or with out any modifications and also what are the modifications I need to do so it can run before other DTS task (which picks the file and insert it in three different tables after format file application.
Set fso = WScript.CreateObject("Scripting.FileSystemObject")
'Set SourceFile = fso.OpenTextFile("C:SourceWGEE.NONX.GMM220D.UNLOAD.G00178888V00", ForReading) Set SourceFile = fso.OpenTextFile("C:SourceDataNotepad.txt", ForReading)
set l1 = fso.OpenTextFile("C:SourceLevel1.txt", ForWriting,True) set l2 = fso.OpenTextFile("C:SourceLevel2.txt", ForWriting,True) set l3 = fso.OpenTextFile("C:SourceLevel3.txt", ForWriting,True)
Do While SourceFile.AtEndOfStream <> True retstring = SourceFile.ReadLine
if Instr(1,retstring,"PACM",0) > 0 then Level1No = Level1No + 1 Level1Str = Rpad(Level1No," ",10) l1.WriteLine(Level1Str & retstring) End if
if Instr(1,retstring,"PACPRS",0) > 0 then Level2No = Level2No + 1 Level2Str = Rpad(Level2No," ",10) l2.WriteLine(Level1Str & Level2Str & retstring) End if
if Instr(1,retstring,"PACACC",0) > 0 then Level3No = Level3No + 1 Level3Str = Rpad(Level3No," ",10) l3.WriteLine(Level1Str & Level2Str & Level3Str & retstring) End if
Loop
Function Rpad (MyValue, MyPadChar, MyPaddedLength) Rpad = MyValue & string(MyPaddedLength - Len(MyValue), MyPadChar) End Function
I am writting a query to retrive some information about a person from the database, now intresting thing is that the query does'nt find the person who dont have a Last name in the database. i pass on the values which user have entered, the values are FirstName, LastName and EmployID, what ever information is given, its passed to the query and the person should be retrived if the person is logged in but problem is that the query works only if the person have a LastName in the database... this is my query.. please tell me what is wrong and what should i change.... its Urgent guys
SELECT distinct top 22 cfg_person.*,cfg_place.name,login_info.callablenumber,login_info_server.server FROM cfg_person,cfg_login_info,cfg_agent_login,cfg_switch,login_info,cfg_place, login_info_server
where cfg_person.state='1' and (cfg_switch.type = 5) and cfg_person.is_agent=2
and cfg_person.first_name like 'Monique%' and cfg_person.last_name like '%' and cfg_person.employee_id like '%'
and cfg_person.dbid=cfg_login_info.person_dbid and cfg_login_info.agent_login_dbid = cfg_agent_login.dbid and cfg_agent_login.switch_dbid = cfg_switch.dbid and login_info.person_dbid = cfg_person.dbid and login_info.logged_voice=1 and cfg_place.dbid = login_info.place_dbid and login_info.server_id = login_info_server.id
I get the following error when I try to access the database from my web application Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed. The application works when I run it on my local machine, but when I move the application to a remote server, I get the above error. Below is the connection string section in my web.config file. <connectionStrings> <remove name="LocalSqlServer"/> <add name="LocalSqlServer" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|CiaaASPNETDB.MDF;user instance=true;Integrated Security=True;Initial Catalog=CiaaASPNETDB;Integrated Security=True;user instance=true;Connection Timeout = 0" providerName="System.Data.SqlClient"/> <!--Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|CiaaASPNETDB.MDF;user instance=true;Integrated Security=True;Initial Catalog=CiaaASPNETDB --> </connectionStrings> Can anyone help me with this.
I don't know what I should try next, all of my tries have been without results in this case. I just wanted to create a temporary table, then insert one row into it and then use this row to compare if this row exists in another table or not. This is my script: ------------------------ CREATE PROCEDURE imp_Tippimport (@TGName char(33)) AS
BEGIN
CREATE TABLE #tTippgeber ( TGName2 char(33) )
INSERT INTO #tTippgeber (TGName2) VALUES (@TGName) *** see below ***
IF NOT exists (select TGName FROM Tippgeber WHERE TGName = @TGName)
INSERT INTO Tippgeber SELECT TGName2 FROM #tTippgeber /* SELECT * INTO Tippgeber FROM #tTippgeber */
ELSE
UPDATE Tippgeber SET TGName = t.TGName2 FROM #tTippgeber AS t, Tippgeber WHERE TGName = t.TGName2
END GO
I also created a cursor and used a fetch in to a veriable in the line with the stars to see the value of TGName2 but it was NULL.
if i try to delete data via MFC from Microsofts SQL Server 2000 i get following message: Data can be read only.. Weird i can write and read already. do i have to setup anything in Micrsofts SQL Server 2000 ??
