SQL Server 2008 :: Sync Between A View And Other Concrete Table
Jul 23, 2012
Here's the scenario. We tried to create a full text indexing on a view, unfortunately the view was created based on Left outer join. Hence we were pushed to do the following.
We planned to create a table based on the data that is rendered in this view(this view is formed by combining 14 tables.) . Finally we were able to create a full text indexing on this new table. For simplicity let us called this new table as tableA.
Now the problem is how can we update (if any data is changed on one of the concrete tables of the view) or insert (when new data is inserted in one of the underlying tables of the view) the respective data into this new table - tableA which was created based on this view.
In other words, there should be a sync between the view and tableA. Is it possible to achieve this ???
View is formed based on different tables (not based on tableA)?
View 2 Replies
ADVERTISEMENT
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
May 27, 2015
We have two databases with same schema and tables (same table names, basically main DB and a copy of the main DB). following is example of table names from 2 DBs.
CREATE TABLE #SourceDatabase (SourceColumn1 VARCHAR(50))
INSERT INTO #SourceDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6')
SELECT * FROM #SourceDatabase
DROP TABLE #SourceDatabase
CREATE TABLE #ArchiveDatabase (SourceColumn2 VARCHAR(50))
INSERT INTO #ArchiveDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6')
SELECT * FROM #ArchiveDatabase
DROP TABLE #ArchiveDatabase
We need a T_SQL statement that can create one view for each table from both the databases(assuming both databases have same number of tables and same table names). so that we can run the T_SQL on a thrid database and the third DB has all the views (one view for each table from the 2 DBs). and the name of the view should be same as the tables name. and all 3 DBs are on the same server.
the 2 temp tables are just examples, DBs have around 1700 tables each. so we ned something like following for each table.
CREATE VIEW DBO.TABLE1 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE1] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE1]
CREATE VIEW DBO.TABLE2 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE2] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE2]
CREATE VIEW DBO.TABLE3 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE3] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE3]
CREATE VIEW DBO.TABLE4 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE4] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE4]
CREATE VIEW DBO.TABLE5 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE5] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE5]
CREATE VIEW DBO.TABLE6 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE6] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE6]
View 6 Replies
View Related
Jan 22, 2015
We have setup a report server where a db's tables are refreshed from a backup daily.
All SP and views are created in a different DB but look at the daily refresh tables
Can I setup the DB that creates the view to point at the DB with the tables so when I say new view it looks at the tables in that DB ...
View 4 Replies
View Related
Mar 11, 2015
I have a view made up of a few base tables and another view. I have created an INSTEAD OF trigger on this view, but it doesn't seem to fire whenever a new record shows in the view. The purpose of the trigger is to insert a sister record in a table whenever a new record shown in the view. Here's the catch, the table that the trigger is supposed to insert into is not a base table within the view and the view is not an updatable view. My question is... Do INSTEAD OF triggers only affect the base table(s) within the view and does the view itself have to be an updatable view?
View 3 Replies
View Related
Jun 21, 2010
Can I view the log cache size in SQL Server memory any DMV's which states that.
View 9 Replies
View Related
Sep 4, 2015
A recent SharePoint upgrade has rendered several views obsolete. I am redefining them so that our upper level executive reports show valid data.(yes, I know that doing anything to sharepoint could cause MS to deny support, having said that, this is something I've inherited and need to fix, pronto) The old view was created like so:
USE [AHMC]
GO
/****** Object: View [dbo].[vwSurgicalVolumes] Script Date: 09/04/2015 09:28:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwSurgicalVolumes] AS
SELECT
[code]....
As I said, this view is used in a report showing surgical minutes.SharePoint is now on a new server, which is linked differently (distributed?) I've used OPENQUERY to get my 'new' query to work;
SELECT *
FROM OPENQUERY ([PORTALWEBDB], 'SELECT
--AllLists
AL.tp_ID AS ALtpID
,AL.tp_WebID as altpwebid
,AL.tp_Title AS ALTitle
[code]....
My data (ie surgical minutes, etc) seems to be in the XML column, AUD.tp_ColumnSet . So I need to parse it out and convert it to INT to maintain consistency with the previous view. How do I do this within the context of the view definition?Here is a representation of the new and old view data copied to excel :
<datetime1>2014-08-14T04:00:00</datetime1><float1>2.000000000000000e+000</float1><float2>4.190000000000000e+002</float2><float3>1.600000000000000e+001</float3><float4>8.110000000000000e+002</float4><sql_variant1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
[Code] ....
can't format it to make it look decent. InHouseCases =2, InHouseMinutes=419, OutPatientCases =16, OutPatientMinutes=1230. This corresponds to the new data I can see in the XML column; 2.000000000000000e+000 is indeed 2 and 4.190000000000000e_002 is indeed 419.
View 4 Replies
View Related
Jun 2, 2015
I gave a user all required permission to view the SSRS report. User is able to select from the dropdown list but unable to view the data, It is showing a blank screen.
View 9 Replies
View Related
Aug 26, 2015
I have one view which is based on couple of tables. Here is the definition of view. Which are the options i can use to optimize the view for better performance. This is one of the view which causing issue on database.
CREATE VIEW [dbo].[V_Reqs]
WITH SCHEMABINDING
AS
SELECT purchase.Req.RequisitionID, purchase.Req.StatusCode AS Expr2, purchase.Req.CollectionDateTime,
purchase.Req.ReportDateTime, purchase.Req.ReceivedDateTime, purchase.Req.PatientName, purchase.Req.AddressOne,
purchase.Req.AddressTwo, purchase.Req.City, purchase.Req.PostalCode, purchase.Req.PhoneNumber,
[code]....
View 3 Replies
View Related
Sep 15, 2015
I was able to create a view and convert it to excel. Now I want to it to schedule it for everyday and then email the excel file as an attachment to couple of people.
Would SSRS be an option? where I can create a report of the view and schedule it?
Does any know the process I need to follow?
Do I have to uses SSIS ? and then set it up as SQl server job?
View 9 Replies
View Related
Oct 29, 2015
The below would work if all of the values in A.Value were numbers but they are not. So I need to restrict the view to only look at the following measures but still show all the other row.
WHERE [Measure] IN ('RTT-01','RTT-04','RTT-07')
SELECT
M.[Description]
,A.*
,M.Threshold
,M.[Threshold Direction]
[Code] ....
Is there any way that I can create a select statement in the case when to only look for them measures that I know contain numbers?
View 9 Replies
View Related
Mar 25, 2015
I'm creating Indexed view by JOINING multiple tables and trying to create FULL TEXT search index. Unique column is generated by concatenating to ID columns from different table. I can successfully able to create unique index however when trying to create FULLTEXT INDEX getting below error.
"A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key."
The message clearly says the column should be single-column index, non-deterministic.
View 0 Replies
View Related
Mar 6, 2013
The developers in our shop have a need to explicitly grant view definition permissions to themselves on stored procedures they create in their development databases. They have dbo level permissions in these databases and although they can explicitly grant view definition permissions to other developers in the same database, they are unable to do so for themselves. When they attempt this, it appears that they are successful but when they check the stored procedure afterwards the permission is not there for themselves.
While this does not cause an issue in development, the intention is for these view definition permissions to be carried forward to the test and production databases where they only have datareader permissions.
When these stored procedures are scripted out by the dba to move to Test and Production the view definition permissions are not scripted out for the developer in question.
Is there a way that a developer with dbo rights in a database can explicitly grant themselves view definition permissions on a stored procedure they create as dbo?
View 9 Replies
View Related
Jul 24, 2015
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
[Code] ....
View 4 Replies
View Related
Apr 10, 2015
I created am inventory table with few columns say, Servername, version, patching details, etc
I want a tracking of the table.
Let's say people are asked to modify the base table and I want a complete capture of the details modified and the session of the user ( ) who (system_user) is actually modifying the details.
View 1 Replies
View Related
Feb 23, 2015
I am trying to insert bulk data into main table from staging table in sql server 2012. If any error comes, this total activity is rollbacked. I don't want that to happen. I want to know the records where ever the problem persists, and the rest has to be inserted.
View 2 Replies
View Related
Apr 24, 2015
I would like to create a table valued function that fetch through the table below using a cursor and return the records that are unique
EmpidChDateSiteuseridinitsal finsalNote
-------------------------------------------- ----------
236102015-4-21 22:02:10.8072570 0.696176161 change inisal value
236112015-4-21 22:02:11.0502570 0.696176161change inisal value
236122015-4-21 22:02:11.1202570 0.696176161 change inisal value
236132015-4-21 22:02:11.2452570 0.696176161change inisal value
View 9 Replies
View Related
Mar 13, 2015
Here is the sample data for table.
create table #sample (emp varchar(max),data1 varchar(max),data2 varchar(max), Rdate date)
insert into #sample (emp,data1,data2,rdate)
values('john','','',getdate())
insert into #sample (emp,data1,data2,rdate)
values('tony','','',getdate())
[Code] ...
I need to update the data1, data2 from yesterday or before but they should be non blank and non null and update to todays data.
It means, I can't update to 2015-03-12 values to 2015-03-13, as they are blanks or nulls.
So I need to go 2015-03-11 and check the values, if they are not blank , i need to update with todays values.
Before the update :
empdata1data2Rdate
john3/13/2015
tony3/13/2015
john3/12/2015
tony3/12/2015
johncd3/11/2015
tonyab3/11/2015
After the update:
empdata1data2Rdate
johncd3/13/2015
tonyab3/13/2015
john3/12/2015
tony3/12/2015
johncd3/11/2015
tonyab3/11/2015
View 4 Replies
View Related
May 26, 2015
which ALTER TABLE/ALTER COLUMN- Statement has a Recreate Table as result ?
View 2 Replies
View Related
Sep 16, 2015
I have a scenario where I have to Update a table with date when there are new records in another table
For example:
I load ODS table with the data from a file in SSIS. the file has CustomerID and other columns.
Now, when there is new record for any customerID in Ods, then Update the dbo table with the most recent record for every CustomerID(i.e. update the date column in dbo for that customerID). Also Include an Identifier that relates back to the ODS table. How do I do this?
View 8 Replies
View Related
May 18, 2015
I have two table 'Cal_date' and 'RPT_Invoice_Shipped'.Table cal_data has column month_no, start_date and end_date. And table RPT_Invoice_Shipped has columns Day_No, Date, Div_code, Total_Invoiced, Shipped_Value, Line_Shipped, Unit_Shipped, Transaction_Date.
I am using below insert statment to insert data in RPT_Invoice_Shipped table.
insert into [Global_Report_Staging].[dbo].[RPT_Invoice_Shipped]
(Day_No, Date, Div_code, Total_Invoiced, Transaction_Date)
select , CONVERT(DATE,Getdate()) as Date, LTRIM(RTRIM(div_Code)),
sum(tot_Net_Amt) as Total_Invoiced, (dateadd(day, -1, convert(date, getdate())))
from [Global_Report_Staging].[dbo].[STG_Shipped_Invoiced]
WHERE CONVERT(DATE,Created_date )=CONVERT(DATE,Getdate())
group by div_code
while inserting in column Day_No in RPT_Invoice_Shipped table, I have to use formula (Transaction_Date-start_date+1) where Transaction_Date from STG_Shipped_Invoiced and start_date from Cal_date table. I was using datepart (mm, Transaction_Date) so it gives month_no, and this month_no we can join with month_no of Cal_date table and fetch start_date from Cal_date table, so that we can use start_date for formula (Transaction_Date-start_date+1).
But I am getting difficulty to arrange this in above query. how to achieve this?
View 1 Replies
View Related
Jun 9, 2015
I am trying to replicated table A into table B withing the same server and the same database.
I am getting an error message when trying to configure the subscription: You have selected the Publisher as a Subscriber and entered a subscription database that is the same as the publishing database. Select another subscription database.
Is there any workaround for this?
I am using SQL Server 2008 R2.
View 2 Replies
View Related
Nov 11, 2014
sync values from table to calender. I got SQL Server 2012 and Outlook 2013.
I want to know is there any way all appointments booked in table should sync with exchange server automatically.
View 5 Replies
View Related
Feb 11, 2008
Hi
I'm using VS'08 and develop in VB.
I'm using SQL CE 3.5 as a local cache for SQL'05 tables.
The table that's being updated uses an interger autonumber for the PK.
When the program starts up and inserts records into the table, it works, as long as no on else is insering recoreds into the same table.
Once a duplicate PK is created by another WS, the records no longer update the SQL'05 table.
If the blocked program is restarted, it'll insert records with a PK that's past the one found at the initial sync. until blocked again.
PK ---- ProgID ----- MSG
1
0
4
B
hello
2/6/2008 9:33:55 PM
2/6/2008 9:33:55 PM
2
0
4
B
hello
2/11/2008 7:54:38 PM
2/11/2008 7:54:38 PM
3
1
1
B
hello
2/11/2008 8:32:41 PM
2/11/2008 8:32:41 PM
4
0
4
T
just something
2/11/2008 8:34:18 PM
2/11/2008 8:34:18 PM
5
1
1
B
one
2/11/2008 9:13:41 PM
2/11/2008 9:13:41 PM
6
1
1
B
two
2/11/2008 9:14:06 PM
2/11/2008 9:14:06 PM
7
1
1
B
three
2/11/2008 9:14:35 PM
2/11/2008 9:14:35 PM
8
1
1
B
four
2/11/2008 9:15:04 PM
2/11/2008 9:15:04 PM
9
1
1
B
five
2/11/2008 9:15:59 PM
2/11/2008 9:15:59 PM
10
0
4
B
cp 1
2/11/2008 9:17:44 PM
2/11/2008 9:17:44 PM
11
0
4
B
cp 2
2/11/2008 9:18:13 PM
2/11/2008 9:18:13 PM
12
1
1
B
eight
2/11/2008 9:21:31 PM
2/11/2008 9:21:31 PM
13
0
4
B
cp 3
2/11/2008 9:21:52 PM
2/11/2008 9:21:52 PM
NULL
NULL
NULL
NULL
NULL
NULL
NULL
David L.
View 8 Replies
View Related
May 15, 2015
have done inserting into files using BCP ,but is it really possible to bcp data from one table to another?
View 6 Replies
View Related
Jun 23, 2015
I have a situation where a person, who created our DW, likes to go in and change table name. This person had no prior SQL xp and is at a higher level then me in the company. I need to know whenever somebody changes a table name I get notified, with the old table name and the new table name. This can be either set on the server level or the DB level. I would like to use the policy management but cant find the way to do it.
View 9 Replies
View Related
Sep 11, 2015
I am working on SQL SERVER schemas using a plug-in in SQL developer itself now my requirement is to generate DDL for a table which is part of SQL SERVER schema...
View 3 Replies
View Related
Oct 15, 2015
I have few queries.
1. Is there any way to get the Table Row Count if we are not maintaining the count.
2. This is for Update statistics
a. Should we run update statistics in our database for all the tables? The database is highly transactional.
b. How should i calculate the sample size that will suite for all the tables.
There are some tables which gets reindex, this we will ignore. We have a job, which reorganise some tables. Now the decision need to be taken what table should we update statistics to:
1. The table which has been reorganised
2. Table which has its statistics outdated.
View 2 Replies
View Related
Feb 18, 2015
Production and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?
View 3 Replies
View Related
Jul 3, 2015
I have an sql server table which serves as a criteria table for my sql server query.
i wish to update the sql server table from the excel worksheet. The intention is to allow the end user to change the values in a specific column in the sql server table via excel.
The table in question has the following fields
SELECT
[Cluster]
,[Max_Break_btw]
,[RefD_Max_Break]
,[DischD_Max_Break]
,[MaxReviewPeriods]
FROM [databseName].[dbo].[SpellClusterAssum]
I will like to change / update the values in the "[Max_Break_btw]" column.
View 0 Replies
View Related
Aug 9, 2000
Any idea on how I do this?
Any advice welcome.........
View 1 Replies
View Related
Dec 31, 2011
I have a table that contains records of transactions with ID column is primary key
I use partition follow ID column, each partition have 1 million records.
CREATE PARTITION FUNCTION [pfTBLTRANS_ID](int) AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000, 4000000, 5000000, 6000000, 7000000, 8000000, 9000000, 10000000)
CREATE PARTITION SCHEME [psTBLTRANS_ID] AS PARTITION [pfTBLTRANS_ID] TO ([GROUP01], [GROUP02], [GROUP03], [GROUP04], [GROUP05], [GROUP06], [GROUP07], [GROUP08], [GROUP09], [GROUP10], [GROUP11])
But now I have more records with IDs that are greater than 11.000.000. So how can I add more partitions to this table ?
View 9 Replies
View Related
Feb 4, 2015
I have a table like below,
CREATE TABLE Student
(
Id BIGINT not null
,Name NCHAR(20) not Null
,Branch NVARCHAR (64) null
)
The table contains : 100000 rows .
1)Number of rows in a data page
2)Total number of pages required for the table
3)Total Table size in KB or MB
4)Total file size in Kb or MB
View 4 Replies
View Related
