Mirror A View To A Table

Aug 8, 2007

I would like to replicate a single view to a table that is stored on another db server (connected as linked server object).
Is there a way to imitate the behavior of a trigger (insert, delete, update) for a view?
I could assign the triggers to the table that provides the primary key.
So I could handle insert, delete events.
But what about updates that affect row in other tables that are used in this view?




Code Snippet
CREATE TRIGGER mirror_tableA_insert
ON [TESTDB].[dbo].[tableA]
FOR INSERT
AS
BEGIN
set nocount on
SET XACT_ABORT ON
set REMOTE_PROC_TRANSACTIONS off
INSERT INTO OPENQUERY(TESTLINKED, 'SELECT * FROM tableA')
SELECT *
FROM [TESTDB].[dbo].[myView] orig
INNER JOIN inserted i
ON i.prim = orig.prim
END


Thanks in advance for any hints!

Marcus

View 7 Replies


ADVERTISEMENT

Mirroring :: How To Reduce LDF Size While Mirror Enabled With Mirror And Witness Server

Jun 14, 2015

I am using SQl Server 2012 Database Mirroring with around 40 gb as mdf and 1 gb as ldf. Now my ldf size increased . How to reduce ldf size while mirror enabled with mirror server and witness server. Can shrink the ldf with mirror enables.

View 4 Replies View Related

How To Mirror A Table Exactly When It Changes?

Aug 6, 2007

Hi folks,

I am creating an external database for a legacy application and one of the requirements is that every table has to be declared twice in the database:

TABLE_READ and TABLE_WRITE.

Now the idea is that as soon as a change is made to TABLE_WRITE, these
changes should be reflected in TABLE_READ.

Now there are dozens of these tables, and I dont want to have to go
through and write AFTER UPDATE/INSTERT/DELETE triggers for all of
them. Is there an easier way?

- can you use replication within the same database instance (ie, replicating tables instead of instances)
- some kind of table mirroring within a single database?
- is there a good solution using tsql/stored procedures?

Thanks in advance,

Alex

View 2 Replies View Related

Mirror And Witness Connection In A Disconnected State Immediately After Adding Witness Server To Mirror Session.

Jan 31, 2008


After adding the Witness Server to the Mirror session, the Witness Connection state between the Mirror and Witness Connection is Disconnected and the state between Principal and Witness Connection is Connected.

The procedures defined in Books Online was used to setup Database Mirroring...when the Witness server was added to the Mirror session, only the alter database T-SQL statement was executed on the Principal server.

ALTER DATABASE <db_name> SET WITNESS = 'TCP://<servername>:<port>'

After executing the above statement, a few seconds later the state between Principal and Witness Connection changed to Connected and the state between Mirror and Witness Connection remains Disconnected.

The Mirror session is not using Certificates, every server is on the same domain, using the same domain login account, and all servers have SP2 installed running Enterprise Edition.

Any idea's why the state between Mirror and Witness Connection remains Disconnected?

Thanks,

View 9 Replies View Related

Mirror Database-How Do I Remove It From Being The Mirror

Dec 27, 2006

I was trying to test mirroring and now would like to delete the mirror database but it says I need to remove database mirroring first. I deleted the endpoint and cannot figure out how to remove the mirroring. Can someone please help.

View 1 Replies View Related

Large Table-Table Partition, View Or Other Method?

Aug 27, 2007

Hi everyone,

I use sql 2005. What is the best practice for dealing with large table (more than million rows)? Table Partition, View or other?

Can you please give some suggestions? It will be very helpful if you can post some references or examples.

Thank you!

View 12 Replies View Related

T-SQL (SS2K8) :: Create Union View To Display Current Values From Table A And All Historical Values From Table B

May 6, 2014

I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.

Q. Can this be done with one joined or conditional select statement?

DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid

View 9 Replies View Related

Table/view

Nov 16, 2006

there is a table - TableA in my DB
which iam using in a stored procedure now the table is normalised and the same date is spread in 6 diff tables.
my admin is creating a View which is just like TableA.

my question is do i have to change anything in my stored procedure as TableA is a view and not a table anymore.

View 2 Replies View Related

View From A Table

Aug 14, 2005

HiComplete newbie to sql server. I have a Companies table from which I wouldlike to create a Clients view as follows;SELECT ID, Company, CompanyTypeFROM dbo.CompaniesWHERE (CompanyType = N'Client')Such that when I insert a record into this view it automatically setsCompany Type to Client. Just so I can treat the view as just another tablewithout having to worry about setting the correct company type. Is there away to do this either via views or some other way?ThanksRegards

View 1 Replies View Related

Using Table UDF In A View

May 4, 2007

If anyone can help... I have a function that takes one parameter andbring back quite a lot of records. but with a conventional function thefield size has a limit of 8000 and i can not use field type 'text'. thelimit has been reached and the view does not bring back results. anoption i found was to use a table function. but with my limitedexperience with table udf's, I am failing to use it within a view. I ampassing a value from the view as a parameter to use in the function andit doesnt work.Please help.*** Sent via Developersdex http://www.developersdex.com ***

View 1 Replies View Related

Turning A View Into A Table

Apr 15, 2007

I am in a scenario where my tables are refreshed every morning by a batch update.  I have built a few views off of one table.  To increase speed I would like to take all the rows from one of the view s and insert them into their own table.  I know this can be done with some T-SQL but I'm a noob to it and don't know how to specifically do it.Any detailed help would be greatly appreciated. -Nate 

View 1 Replies View Related

Using A View As A Lookup Table

Jan 29, 2004

Hi

I'm designing a database for my final year project at University, and have run into a bit of a dilemma with one of the tables. Basically, it's an equipment loan database where both students and lecturers need to be able to book and borrow equipment. To avoid having two separate bookings tables (i.e. StudentBookings and LecturerBookings) I've got students and lecturers combined into one table called 'Borrowers'. The trouble comes because the clients want the lecturer storing in the booking information table, which can get quite messy because lecturer information is stored in the same table as borrower information meaning that I basically need to relate both the Booking.BorrowerID and Booking.LecturerID to Borrowers.BorrowerID.

In theory, I think this could be solved by creating a View called Lecturers that pulls the required information for Lecturers out of the Borrowers table and then link Booking.LecturerID to the ID of the view. In practice, I've got a couple of queries:

1) Is it possible to perform a JOIN on a view in the way that I would need to here?

2) To keep things nice and clear, is it possible to 'rename' the BorrowerID to LecturerID whilst creating the View?

Sorry if some of this isn't too clear. Please ask any questions to clarify what I mean if you need to.

Cheers
Jon

View 2 Replies View Related

How To Connect To A SQL View NOT Table

Jul 14, 2005

Normally i can use Web Matrix to connect to a SQL table. e.g. Dim queryString As String = "SELECT [MyTable].* FROM [MyTable]"Is is possible to connect to a SQL View.  The reason is because i have generated a view using UNIONS and JOINS and some columns are generated by concatenating columns together (ie.. has alias columns).I also need to use VB.NET to refer to these alias columns. 
 

View 2 Replies View Related

Table And View Will Not Drop

Jun 13, 2001

We have a table created by an application, and a view that joins the table with other tables.

For some reason we are now unable to drop the table or the view. In Enterprise Manager the drop table dialog comes up, we click 'Drop All' and then the hour glass comes up and never goes away. No errors are returned, the process just never returns control to the client, the same when trying to remove the view. Using Query Analyzer is no different.

However stopping and starting the server resolves the problem for a while, but eventually the same problem starts happening. The table is created, populated and dropped using stored procedures called from a web page via asp script. This process may occur numerous times and hasn't been a problem until the last day or so when the developer added a couple of smallint columns to the table.

Anyone know what could be the problem here?

View 2 Replies View Related

Design View Of Table

Jul 25, 2001

How do you get the design view of a table in sql server query analyser?
I know in oracle its desc table_name.
Thanks

View 6 Replies View Related

What&#39;s Different From SELECT FROM A TABLE To A VIEW?

Oct 12, 2000

Hi all,
What's different from SELECT FROM A TABLE to SELECT FROM A VIEW? Which is faster? Can anyone explain my result following?
Example for:
-- TABLE:
set statistics io on
select * from TABLE_NAME
-->Table 'TABLE_NAME'. Scan count 2, logical reads 106, physical reads 0, read-ahead reads 0

-- VIEW:
Create view VIEW_NAME as SELECT * FROM TABLE_NAME
set statistics io on
select * from VIEW_NAME
-->Table 'TABLE_NAME'. Scan count 1, logical reads 53, physical reads 0, read-ahead reads 0.

View 2 Replies View Related

Hide Table View

Oct 14, 1999

Hi,

How can I hide all the system tables so that only user tables show in table view.

Thank you very much!

John

View 1 Replies View Related

Best Way To View Table Properties

Apr 28, 2003

Can anyone help me with displaying several table properites at once. I know I can use sp_help 'tablename' to get one at time. What is the best method to get several databases at a time?

View 5 Replies View Related

View Table Problem

Mar 27, 2007

Hello

I am trying to insert records to view table.

Msg 4405, Level 16, State 1, Line 6
View or function 'table1' is not updatable because the modification affects multiple base tables.

table1 is view table

My Synatx is

insert into table1
select table1.col1, table2.col2
from table2, table3
where table2_id = table3_id

what is wrong with these.
Thanks

View 5 Replies View Related

SQL 2k Replication - View To Table

Sep 20, 2005

We would like to copy sensitive prod data into a devleopment environment and use a view to alter some of the sensitive rows. (incl. ssn's)

We have views that will "scrub" the data and substitute ssn's with an alternate key for R.I.

Some would like us to use replication to copy from our scrubbed views to our dev tables. Anyone have suggestions on how to accomplish this?

Only ops has access to distributors on-site, including my laptop :( so I can't do much experimenting and have to back-seat drive.

View 1 Replies View Related

Trying To Sync A View Instead Table (ppc)

Jan 5, 2004

I am working on a program for the Pocket PC platform, which will piggy-back on a helpdesk program which stores it data in an SQL server. I have no problems with getting information from the server, or any connectivity issues.

I have found that it is easy to synchronize an access .mdb file, or an ODBC Source to a .cdb file on the PPC. I have a data provider for the SQL database in question, and the "Import Database" function works fine; it pulls up a listing of all the tables and fields in the helpdesk database.

My problem is as follows. The table used to store the memo information is at least 20-30 megs. I only need a few key entries from that table. I also need to make the sync process as simple as possible (that's why i was looking into activesync), but there does not appear to be ANY way to import an SQL View, as opposed to a table.

SQL CE appears to be one possible solution, but we are running SQL 7, and the CE server tools require SQL 2000.

So, is there a way to trick activesync into sync'ing a VIEW instead of a table?

Do you have ANY OTHER ideas, which would be simple come time to sync?

View 4 Replies View Related

Index A View Or A Table

Apr 8, 2004

I have a table that has thousands of rows inserted daily (rows are seldom updated or deleted)

The table is also involved in frequent non-simple select statements. It currently has about a million rows.

Out of the 15 odd columns in the table, I can see about 6 that would benefit being indexed to speed up the select statements.

Before I do this, I was wondering if people think that perhaps I should create an indexed view that all select statements use, rather than adding indexes directly to the table.

Can anyone advise me the performance benefits/disadvantages of indexed views over indexed tables?

Thanks

View 14 Replies View Related

View On The Fly Or Temp Table

May 6, 2008

problem:

Have a table with Data and Dependencies(Foreign Keys) and Stored Procedures, views etc.
Need the Data in that table to put in different order.
For exampl, put older years in the begining or end so when sorted by year, you will get right data.
Question is: If i use view it will be based on the table with Wrong entry order and if i use Temporary Table
each time stored procedure is run, it will be created and overhead.

Example of the table is below with Wrong order.
I should have entered the old years first.
Note this is example and not the actual table!


ID Yr Title/Model Serial#

---------------------------------
1 2005 Toyota Camery IXp12365555
2 2006 Honda Accord XJi9770009
3 2007 Honda Accord XJi9000009
4 2004 Honda Accord XJi9880009
5 2005 Honda Accord XJi9009009
6 2007 Honda Accord XJi9078009

If this example is not right, my bottom line is this:
I have entered the Data in a table and i'm getting wrong resutls when i order by Desc or Asc and now either have to delete all rows and re-enter it or have someother clever way!

View 6 Replies View Related

Modifying A Table From A View

Jun 18, 2008

I have two tables, a data table (MainTable) that contains a user initials and other empty rows and then i have a user table.

I also have an inner join view with the initials from both tables as the joining field.

I want the users to be able to modify the data table (MainTable) but also display the users name field.

here is the sql of the view:

SELECT dbo.MainTable.DateCreated, dbo.InitialsListing.Initials, dbo.MainTable.CustomerNumber, dbo.InitialsListing.AdvocateName,
dbo.MainTable.Supervisor, dbo.MainTable.Complete, dbo.MainTable.FirstNotify, dbo.MainTable.SecondNotify, dbo.MainTable.ThirdNotify,
dbo.MainTable.AdvocateInitials
FROM dbo.InitialsListing INNER JOIN
dbo.MainTable ON dbo.InitialsListing.Initials = dbo.MainTable.AdvocateInitials

View 3 Replies View Related

Using Temporary Table In A View

Jul 23, 2013

I am trying to create a view using to three queries below and I get the error message Views or functions are not allowed on temporary tables. Is there a way to do that or is there a way to combine the three queries below so I don't have to use a temp table so I create a view?

--Query 1

SELECT * INTO #MOVEMENTS
FROM [GW_DW].[dbo].[DimStatusHistory] d
WHERE TransferFromToProgram<>''
AND d.Status=12
;

--Query 2
SELECT DISTINCT

[Code] ....

View 6 Replies View Related

Create View From Table

Oct 6, 2014

I want to create a view from table j25_teamlist2014 with the 7 columns shown below. But I only want the rows which don't have a Status of 'Not Available'.This sql works fine apart from the Team Name and Team Captain columns display the team which is an interger ie Row 1 shows 1 in the Team Name and Team Captain columns instead of 'My Team' and 'Jo Bloggs'.Also when viewing the original table and the new view the structures are slightly different.

CREATE VIEW j25_availableteamlist2014 AS
SELECT Team, Day, Time, Lane, Team Name, Team Captain, Status
FROM j25_teamlist2014
WHERE Status NOT LIKE 'N%'

View 5 Replies View Related

SQL View/Table Question

Jul 23, 2005

MVP's and the likeI am looking for suggestions , confirmationLet me start by saying bar none, performance is paramount with thequeries to be retured off this view/table query.To that end I am completley donormailzing the data into a view (or atable) with all possible legal combinations ( I have seen this refferedto as Croation Method ?) Its ends up at 3 total rows per account onaverage.THis query is to be executed only from the web, so once again theperformance is paramount.Ok here is what I have so far I have a set of related tables 5 or sothat are for the most part 1 to 1 but in the case of one particulartable its an average raqtion of 20 to 1.Broken all out with 150k customer rows it ends up at 500k total rows inthe viewAbout 7 cols are dynamic aggregates (Sum(tran_AMT)) , min(tran_amt) ,etcI had it in a view and with 1/5 million rows (In the view) it performedadequatley, I will most likeley get to somewhere around 10 milliontotal rows (In the view)I looked into indexing it but thats not going to happen because I amdoing several subquery's , outer joins etc.The View with a Select * returns in 40 SecondsNow i ran into an issue with recursing this query into several seperatesubqueries and hit the 256 table limit, after only about 15 recursions.Now the data only gets updated once , perhaps twice a day, in a batchtransaction.So I Put all the data from the view into a table, and indexed that, Ican return all rows in 12 seconds and any refining conditions I throwat it, like Date > 12/1/2003 (then it executes in under 6 second_ onlyslices the query time down big time (A very good thing)Since its only updates 2x a day I drop and recreate the table in my DTS, and I have triggers on the other tables for Online updates (usuallyno more that 100 a day) but to ensure the table is ALWAYS current itgets recreated on import of additional rows.Is there a name to this maddness ? My co-workers aree leary of it butthey come from a dbase background, denomailzing it into a table andthen indexing the table I couldnt be happeier with ther performance.Are there any lurking gremlins in this design ?I cannot see any pitfalls with doing this , or are there some ?,basically I am denomailizing for read-only query performance.They are concerned about the denorilization for performance, eventhough its only on a read only table (was and could be a view I justcant index this particualr view)So I guess Im looking for a , "Sounds OK" or "Sounds Great" from theSQL Gods ....Chris

View 2 Replies View Related

Integrity Between Table And View

Jul 23, 2005

It is possible to drop the table without dropping the view referencingit. How do I force integrity?Madhivanan

View 9 Replies View Related

Table And View Permissions

Aug 2, 2007

I have a list of users that I want to restrict access to tables in a database. The goal is to allow the users to use select statements on the views instead of the tables. How can this be accomplished?

View 14 Replies View Related

How To Create Table From A View

Nov 7, 2006

Dear all,

Can I create a table from a view like Oracle? The syntax is something like as the below.

create table table1 as select * from view1;

Thanks

View 1 Replies View Related

Restoring Table Or View Or Sp

Sep 22, 2006

Actually I have very big size of databse. Also its back up is obvious that also biger size.

In case of only data loss of one table or view or sp or change then restore whole database then get back previous backup database & restore table i.e. it consuming too much time.

I want to restore only one table or view in faster way. how i can do it. plz give any suggestions

View 4 Replies View Related

Permission On View Not On Table

Dec 14, 2007

I am trying to create a set of "Reporting" views and grant Select permission on those views instead of the tables. I created the view in the same database but under a different schema from the tables. When the user tries to select from the views they get an error saying they do not have select privilege on the tables. Do I need to grant them select on the underlying tables?

View 4 Replies View Related

Creating Table From A View!!!

Jan 15, 2008



Hi folks!!

I have been working on a view and now I want to create a table which has not only similar columns and datatypes but also same data as I have fetched in the view.
How can I do that? Do I have to use a cursor or there is a simpler way to do it?
Please advice me here..

Can I use
Create table <tablename> as ( Select * from <viewname>) ?????

Thanks in advance for reading this.
Tanya

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved