Something Wrong With Table Structure Change On Mirrored Database
Mar 6, 2008
I have set up mirroring in high availability mode (principal, mirror, and witness).
After setting up mirroring, I needed to adjust the size of a column in one of my tables. I did so with the following command
ALTER TABLE OrderOptimizer.dbo.rCodes ALTER COLUMN CodeDescription VARCHAR(8000).
The column was initially VARCHAR(128)
When I look at the table structure it shows that the column is now VARCHAR(8000), however I can only enter 128 characters into the field.
I forced a failover to look at the table on the mirrored version. It too showed that the column was VARCHAR(8000).
If I open the table I can enter more than 128 characters into the field, however when I refresh, it is truncated to 128 characters,
Any help would be appreciated
View 4 Replies
ADVERTISEMENT
Aug 8, 2007
Simple question, I hope. I need to add a column to a table of a database that is mirrored. How do I have to do that? Do I need to stop mirroring? Is it sufficient to simply pause mirroring? If I make the change on the principal db, what do I need to do the make the same change on the mirror?
Thanks,
View 6 Replies
View Related
Oct 23, 2015
I have a database I need to copy from a Prod server to a Dev server. There is not enough space on the Dev server. In looking at the size of the files on the Prod server, the Initial Size property for the transaction log on the Prod server is set to 100,000 MB though the log is using nowhere near that.
This is a mirrored database so the recovery model is "full". I know that to change the initial log size, I have to put the database in 'simple" recovery model. Is this possible? Can I just:
1. Pause the mirror
2. Switch recovery model to simple
3. Change the initial size property to something smaller.
4. Shrink the transaction log
5. Change the recovery model back to full and resume the mirror?
I honestly don't know if the transaction log is needed on the Dev server. Meaning I may just be able to restore the transaction log to a different location on the server and delete it so that new one is created.
View 2 Replies
View Related
Aug 30, 2007
Hi....I execute select statement on three tables and i get the following table:
ID
Value
Name
1
10
color
2
20
color
3
30
color
4
40
color
View 5 Replies
View Related
Jan 27, 2006
Can we change table structure in Mirroring. Like adding or deleting columns and tables and how can we do it. Thank you very much.
View 3 Replies
View Related
Oct 5, 2015
I have a simple query which is taking about 2.5 minutes to execute. What can be done to speed it up ? I cannot change the table structure. even without the group by, and without the float and division, it takes about 2.25 minutes.
Select 'SV00302' as FileSource,
[Service_Call_ID],
sum(0) as ActualHours,
sum(cast([Estimate_Hours] as float)/100) as EstHours,
max(MODIFDT+Modified_Time) as ModifiedDateTime
[code]...
View 8 Replies
View Related
Jul 20, 2005
Hello,I have created the following trigger:CREATE TRIGGER tr_UDFTest ON UserDefinedFields FOR INSERT, UPDATEASDECLARE @foobar varchar(100)SELECT @foobar= foobar FROM insertedIF ( @foobar = 'foobar') INSERT INTO LogTable (LogText) values ('Found foobar')ELSE INSERT INTO LogTable (LogText) values ('Did not find foobar')GOBasically, the trigger monitors the values begin put in the foobar field,and acts accordingly based on what it finds. In practice, my needs are abit more complex (the trigger will be programmatically generated, based ona set of rules) but the principle is much the same.ErrorTable is defined as :create table LogTable (LogText varchar(128))UserDefinedFields is a table whose definition may change depending on theuser's needs, but for now assume it contains a varchar column calledfoobar.My problem is that if the user's needs change, and they remove the fieldfoobar, the trigger causes all subsequent inserts/updates to fail with anerror indicating the column foobar doesn't exist. (Which makes sense ofcourse!)CREATE TRIGGER tr_UDFTest ON UserDefinedFields FOR INSERT, UPDATEASDECLARE @foobar varchar(100)if not exists (select * from syscolumns sc inner join sysobjects so on sc.id = so.idwhere sc.name = 'foobar'and so.name = 'UserDefinedFields') BEGININSERT INTO LogTable (LogText) values ('Error : Foobar column does not exist!')RETURNENDSELECT @foobar= foobar FROM insertedIF ( @foobar = 'foobar') INSERT INTO LogTable (LogText) values ('Found foobar')ELSE INSERT INTO LogTable (LogText) values ('Did not find foobar')GOI'd be happy with the above 'flavor' of solution (bailing, or logging anerror and bailing, when we hit unexpected problems) as long as theinserts/updates don't fail otherwise. Perhaps I can nest a transaction, orsupress a RAISEERROR or something?The cleanest solution would probably be to change a bunch of clientsoftware such that it won't remove the foobar field if this field isneeded for a trigger (foreign key constraints based on the set of rulesI'm using are a nice and intuitive solution). Unfortunately, that doesn'twork well with my timeframe (done by thursday) as changing the clientsoftware is impossible by then. Any ideas or suggestions? Platform isWin2k, SQL 2000 Enterprise (I think enterprise, certainly 2000).thanks,Dave
View 6 Replies
View Related
Feb 26, 2008
Say you have an existing populated SQL 2005 database, with 700+ tables, and you want to just change the order of the columns inside every table. Short of manually building conversion scripts, anyone know an automated way to do this? I was thinking thru ways to do them all in one shot, and have tools like Erwin and DbGhost that could be used also. Basically moving some standard audit columns from the end of the tables to just after the PK columns.
Thanks, Bruce
View 8 Replies
View Related
Nov 4, 2015
Change structure of a User Defined Table Types
field
([idConteudo] [int]
NULL) to
([idConteudo] [BigInt]
NULL)
Or really should drop and create again?
CREATE TYPE [dbo].[tbProjeto] AS TABLE(
[dsConteudo] [varchar](64) NOT NULL,
[idConteudo] [int] NULL,
[dtConteudo] [datetime] NULL DEFAULT (getdate())
)
GO
View 4 Replies
View Related
Dec 16, 2007
Hello friends,
I am new to the SQL Server 2005 development.
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database
which is configured and set on the hosting web server. Right now for managing my sql server 2005 database,
i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database,
it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors.
Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still
the problem persist.
Thanks in advance. Any help will be appreciated.
View 5 Replies
View Related
Jan 29, 2008
Hi i have a student table and also an societies table, and a student can belong to as many societies as possible, for example student John Taylor belongs to the Football society, rugby societey and dance society, where as student Jim Jenkins belongs to the Football society, rugby society, arts society and graphics society.
My tables so far look like this;
STUDENTS; student_id, student_name
SOCIETIES; society_id, society_name
My question is how can i make the scenario above reflect in my table structure, would i need to create another table, if so what fields or columns should i place in this new table, thank you.
View 7 Replies
View Related
Dec 14, 2006
Hi,
I am creating reports for an application, that when installed can have various different table owners/qualifiers depending on how client created the DB. How can I create standard reports across all the DB without hardcoding the tablenames qualifier/owner in the dataset query? Again the table structure remain the same just the qualifiers may be different. Any help would be great.
--DCB
View 1 Replies
View Related
Jun 1, 2015
I have an issue in my production , SQL server 2008 R2 , in Error log it is said “ An error occurred in dialog transmission Error : 9655 , State : 3 and Error : 9736 Severity : 16 State 0
I checked in sysmessages , it is said :” The transmission queue table structure in database is inconsistent. Possible database corruption “
So do you think I need to run DBCC checkdb ? Should I run it after working hours or it is fine within working hours ?
View 9 Replies
View Related
Apr 19, 2006
I am using ASP.NET 1.1 and MS SQL 2005 the folowing ODBC stringconnection
Driver={SQL Server};Server=(local);MyBase;Uid=;Pwd=;Trusted_Co nnection=;
when trying to CREATE a TABLE (with vb.net code) I get an error because the TABLE are written in Master !! and not in MyBase
I am using windows Authentication
what can be wrong ?
thank you
View 6 Replies
View Related
Apr 19, 2006
I am using ASP.NET and a normal ODBC stringconnectionDriver={SQL Server};Server=(local);MyBase;Uid=;Pwd=;Trusted_Co nnection=;when trying to CREATE a TABLE (with vb.net code) I get an error because the TABLE are written in Master !! and not in MyBasei am using windows authenticationwhat can be wrong ?thank you
View 13 Replies
View Related
Nov 14, 2014
I have a database (MSSQL). To demonstrate the problem let me show a fictive Tablestructure. I don't want to discuss about how to save the data differntly, because the structure is fix and I can't change it.To get this result I would do a sql query with a lot of joins like that:
SELECT firstname, lastname, email.value, phone.value
FROM Customer
INNER JOIN
(
SELECT Customer_Properties.id, Customer_Properties.value
FROM Customer_Properties
[code]...
I don't think that this is really performant and the SQL-Queries get very complicated. Give it a other methode for that? I can't change the data structure.
View 3 Replies
View Related
Sep 4, 2007
We have a Prinicipal, a Mirror and a Witness server. We have automatic failover configured between the Prinicipal and Mirrored server. When we stop MMSQL service on Prinicipal, not all the databases failover to the Mirrored instance.
Any suggestions would be welcomed as we have a tight deadline to get this in Production.
View 5 Replies
View Related
Feb 8, 2007
hiI want to increase a varchar(5000)to varchar(8000) on a table thathas approximately million rows.....What is the impact on the serveror any good recomendations of a action to accomplish this in the best andfastest way.thanks davep
View 4 Replies
View Related
Oct 11, 2006
I'm having a problem with SQL2005 Database mirroring.
I have an ASP application that loops for a certain amount of interations and in each iteration I create a SQL Connection object and use the failover partner in the connection string. The object then writes a simple record and then the connection is closed and the process starts again.
About half way through my loop I force a failover to the server mentioned as the failover partner in the connection string. At this point my application encounters a SQL Exception error and the application fails.
I read in this article, http://msdn2.microsoft.com/en-us/library/ms366199.aspx, that this is expected and that you shoud request a new connection using the same connection string but this isn't working for me and unfortunately no examples are given anywhere.
I would appreciate any help. Thanks
Luis Bonilla
Here is a sample of my code:
Dim k As Integer = 1
Dim constring As String = "Server=SVR01.XXX.XXX.comInstanceName;Failover Partner=SVR02.XXX.XXX.comInstanceName;" _
& "Database=test;" _
& "Integrated Security=SSPI;"
Do Until k = 60
Using con As New SqlConnection(constring)
Dim cmd As New SqlCommand()
Try
con.Open() '<--------This is where the exception occurs
Catch ex As SqlException
Dim en As String = ex.ErrorCode
Dim em As String = ex.Message
Dim emm As String = ex.Number
Dim enn As Integer = ex.State
End Try
lblprimary.Text = "Current Primary Database = " & con.DataSource
cmd.Connection = con
cmd.CommandText = "INSERT INTO SystemsTest (FirstName, LastName)VALUES ('SQLWriter" & CStr(k) & "','" & con.DataSource & "')"
Try
cmd.ExecuteNonQuery()
Catch
lblresult.Text = "Results = Error with record number " & CStr(k)
End Try
con.Close()
cont:
End Using
Sleep(1000) 'For testing
k = k + 1 'For testing
Loop
View 3 Replies
View Related
Feb 28, 2008
I have a mirred database and have snapshot created from the mirroed database. I can do data selecting from the snapshot. But when the pricipal server is down , I can re-active the mirroed database.( I did succesfully upto this). Then I need to restore the database from snapshot and which failing with following error message.
Code Snippet
Msg 5123, Level 16, State 1, Line 3
CREATE FILE encountered operating system error 3(error not found) while attempting to open or create the physical file 'E:sql_datadevitst_mirrorlog st_mirror_log.ldf'.
Msg 5024, Level 16, State 2, Line 3
No entry found for the primary log file in sysfiles1. Could not rebuild the log.
Msg 5028, Level 16, State 2, Line 3
The system could not activate enough of the database to rebuild the log.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
View 3 Replies
View Related
Jul 9, 2007
I am trying to connect to a failover partner using ADO (non .Net) and Visual C++. ADO is throwing the following exception:
Caught: Unspecified error - Invalid connection string attribute(Microsoft OLE DB Provider for SQL Server)
Connection String:
Provider=SQLOLEDB.1;Data Source=EdwardsvilleSQLSERVER;Failover Partner=Mirror2SQLSERVER;Initial Catalog=SomeCatalog;User Id=SomeId;Password=SomePassword
Any ideas?
View 4 Replies
View Related
Sep 7, 2007
Heya all,
Sorry if this has been posted elsewhere, etc., please point me in the right direction if it has 'cos I couldn't find it!
Right, we have a mirrored database with full safety and a witness for automatic fail over, all works fine, very impressed with it. Now I need to backup the database involved and this is where I could do with some help and answers and/or tips.
As the mirror database is off-line/recovering it seems you can't back that one up, but I'd like to have something that tries to back it up for if/when it fails over and becomes the primary. The solution I've used for now is to write a small .Net application that uses the client side fail over connection string (Data Source=Server1;Failover Partner=Server2) so that it connects to whatever system is currently the primary, and then issues the relevant 'BACKUP xxx' statements to backup the database.
This applications is launched from a windows scheduled task job on the hour (or near to it), every hour. At 06:00 it does a full backup, at 12:00, 18:00 and 00:00 it does a differential backup, and all other times it does a transaction log backup.
This all seems to work fine so far, and generates all the relevant backup files to a share on another server.
So, my main question is; does this look like a good plan? Am I missing some really simple wizard or button that would backup the relevant database from whatever server is up?
Secondly, are the backups from each server interchangeable as they're in a mirrored configuration? That is, for example, the backup application (on the hour) connects to Server1 does a full back up, followed later by a log backup. Server1 then dies, so the next time the backup application runs it connects to Server2 and because of the current time does a log backup from Server2. If we had to restore from backups, could we use the full backup and log from Server1, followed by the log from Server2? Otherwise I'll guess I'll need to modify the logic for the backup application to detect it's failed over, and maybe do a full backup on Server2, or something like that.
Anyway, thanks for any help/advice/tips,
Gareth/OhGod
View 5 Replies
View Related
Feb 29, 2008
Hi all,
I am struggling with configuring SSB network routes to a mirror database. What I want to accomplish is to configure a SSB application to work in a database mirroring setup but I am not looking for a load balancing solution.
According to SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms166090.aspx) the typical routing configuration for a service hosted by a mirrored database is set by specifying the 'mirror_address' field but leaving the 'broker_instance' field empty (i.e. NULL) in the sys.routes table (see Example 3 in link above). I haven't seen how this is possible because if you specify the MIRROR_ADDRESS parameter in the CREATE/ALTER ROUTE command then you must also specify the BROKER_INSTANCE parameter.
I haven't found a way to set the mirror_address field to a valid value and the broker_instance field as NULL, is this possible? If not I must set the broker_instance as the guid of my broker instance but my understanding is that it should primarily be used for load balancing configuration. My preference would be to not set the BROKER_INSTANCE parameter, is this possible?
Best regards,
Oli
View 1 Replies
View Related
Apr 14, 2015
I need to increase the file size for a mirrored database. I am new to using mirroring for replication. Will increasing the file size break the mirror?
View 2 Replies
View Related
Jul 12, 2007
Hello,
I am a C++/C# developer my SQL skills are very limited.
I have a database set up for mirroring and I would like to get an e-mail notification whenever an Automatic Failover occurs. Can anyone show me how to do this using SQLServer 2005? Please provide T-SQL script sample if possible!
Thanks,
-Agustin
View 7 Replies
View Related
Oct 19, 2015
Every once in a while a scheduled restore of a production database backup to a development server will fail with the following error.
RESTORE cannot operate on database 'XXX' because it is configured for database mirroring or has joined an availability group
While it is true the production database is involved in database mirroring, the development server does not have database mirroring enabled. This error tells me something within the backup is telling the development server the database is configured for database mirroring.
However the perplexing part for me is that we only receive this error maybe 5% of the time, if that, and only on a couple of our databases. We have numerous other restores of mirrored production databases to development servers that have never produced this error. So my question is what is causing this error to occur, and why is it not happening all of the time? We get around this error by deleting the DEV database and re-running the restore job.
View 0 Replies
View Related
Jul 20, 2005
Dear Group,I have a table in the a database that I need to change the owner to dbo.How can I do this.Thanks in advance.Jeff Magouirk
View 1 Replies
View Related
Oct 25, 2015
we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.
View 4 Replies
View Related
Aug 14, 2015
Below is my table structure. And I am inserting data from other temp table.
CREATE TABLE #revf (
[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,
[Code] ....
Now in a next step I am deleting the records from #revf table. Please see the delete code below
DELETE
FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2
[Code] ...
I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:
SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],
[Code] ....
If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.
View 5 Replies
View Related
Feb 24, 2006
Hello everyone,I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.I want to know if a column or table has changed any values, like datatype, name, properties, etc.Any suggestions out there ?!
View 3 Replies
View Related
Aug 5, 2015
I need some clarification about adding file in to mirrored dataabse in primary server without downtime and breaking the mirror server.
In our environment we are using monutdisks in both the servers. in primary for ex we have F drive for data files under mount disk 3 in mirror server also we have same drive but in mount drive2.
As per my knowledge if it is same drives we can add the ndf files in the primary that will reflect on mirror. but in current situation i am confusing about mount points with different names.
View 3 Replies
View Related
Jan 6, 2008
Hi,
I have a complicated sql server mobile database (.sdf) and need to create a SQL SERVER database with the same tables. How can I do it without scripting the whole thing? I thought of using the views.information_schema databases, but it is still a lot of coding.
thanks
View 3 Replies
View Related
Jan 20, 2007
Hi allI have two tables in SqlServer with Exactly Same Structure,I want to Copy all Records fromone of them to another one.I came across to "Insert....select..." statement But i have two problem
1) I don't know any thing about Columns name!!! i just know they have same structure and as far as i know , "Insert...select..." need the Column list to operate correctly, am i right?
2) these two table have One Prinary Key column with IDENTITY feature.
Any Help Greatly appriciated.Regards.
View 6 Replies
View Related
