Hello,
I am trying to add a View to an existing publication and it the subscribers (all devices with SQL CE) don't get the View after replication. I have deleted and recreated the publication, and only the tables will appear on the device, not the view.
Also, I want the data from the view to be dynamic, filtered by using the Host_Name() function/value. Will this work for a View?
thanks
- will
Using SQL 7.0 I'd like to replicate just schema from DB on server A to DB on server B, then be able to replicate data only form DB on server B to DB on server A. I need help!!
Thanks for ANY information you can give me...
~Jepadria
Hi,
I'm setting up Transaction Replication b/w SQL Server 2K and SQL Server 2K5.
I have published Tables, Views and SPs as articles.
One of the views has more than 300 columns. So when i try to replicate it, I'm encountering the
following error Message.
"Error 20068: The article cannot be created on table because it has more than 255 columns."
When a view can be created with more than 255 cokumns, why the problem arises when we replicate
it?
Can any one help me on this?
Thanks,
SBR.
prerequisites:
MSSQL 2005 64bit as publisher (transactional replication)
MSSQL 2000 32 bit as subscriber (pull, read-only)
All objects are set to replicate except foreign keys and defaults.
There is a linked server DW on both the publisher and the subscriber that both can access (sa and user logins mapped)
The snapshot generation and loading is fine.
but then I issue this statement at the publisher:
ALTER VIEW [dbo].[v_Test] as
select * from DW.DW_DB.dbo.v_Test
but it fails to replicate at the subscriber with the message:
Category:COMMANDSource: Failed CommandNumber: Message: ALTER VIEW [dbo].[v_Test] asselect * from DW.DW_DB.dbo.v_TestCategoryQLSERVERSource: SubscriberNumber: 7399Message: OLE DB provider 'SQLOLEDB' reported an error.
I tried the command on the Subcriber via Query Analyzer and it works.
What gives?
All suggestions are welcome!
All,
I have a huge problem to solve.
1) Need to create an Indexed View that joins multiple tables.I know how to create a regular View, but How do I create an Indexed View ?
2)Need be able to replicate the Indexed View [above] across to another server B into a table.
I have to get this done and seriously don't know where to start.
Do HELP me out .Thank You.
I have some issue when replicating a view (transactional replication).I have following objects on publisher:
Table: [Metadataschema].[Entity]
View: [dbo].[EntityLogicalView]
the view definition is:
create view [dbo].[EntityLogicalView] as (SELECT * FROM [Entity] WHERE OverwriteTime = 0)
this is what I get when I run "Script View as Create". The same query is used by the replication.Unfortunately this query fails on the subscriber "Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber". I cannot even runt his query manually on the subscriber, it only works when I change it like this:create view [dbo].[EntityLogicalView] as (SELECT * FROM [Metadataschema].[Entity] WHERE OverwriteTime = 0) Is there any way to get this working? Can I change the creation scripts used by the replication or force publisher to include schema names in all objects?
Hi everyone, I've recently been thrown into a DBA role (I've never done any DBA work except writing a few SQL queries), so please go easy on me if these are stupid questions. My first task is to find the best way to replicate data between two SQL Server production databases. The data is to come from Production DB #1 to Production DB #2 (for access by a different system). The data has to be super-close -- not necessarily real-time, but within a few minutes. So when data is updated in #1, #2 shouldn't be be lagged by more than 45 minutes (5-10 is ideal). There are hundreds of thousands of records.What would be the best way to do this? Are there options in SQL Server 2005 to do "differential" updates from DB1 to DB2? Or is that how "transactional replication" works? If we were to implement a "full recovery model", will this impact any sort of replication? Thanks.
View 2 Replies View RelatedHi, there
I have a situation here....
I have Production DB and Development DB in the same SQL7.0 box....
I want to update the data and SP from Pro. to Dev at least once a day...
So this is my plan.
1. Back up(Pro.) and restore(Dev.) so I can have the same DB in the same box.
2. Using a Replication (Pulication and Subscription) ro update the data and SP.
3. Because DTS can not update the SP, I use the replication instead of DTS.
I need to update the SP, too..(Front end is ACCESS and backend is SQL7.0 DB)
Is there any other methods or way to make ti happen... Any suggestion can help...
Thanks in advance
Jay
Hi Folks,
We have transactional replication setup to replicate data from production across to a reporting server.
We want to ARCHIVE production, but don't want the ARCHIVE duplicated on the reporting server.
Does anyone know of a way that the reporting server can be stopped from replicating these changes, and continue to hold the FULL history of the database?
Cheers,
David
Hi Folks,
We have transactional replication setup to replicate data from production across to a reporting server.
We want to ARCHIVE production, but don't want the ARCHIVE duplicated on the reporting server.
Does anyone know of a way that the reporting server can be stopped from replicating these changes, and continue to hold the FULL history of the database?
Cheers,
David
I'm working on a web app that needs to be able to take a row in the database and duplicate it, creating a new row in the same table with the same data except for the ID field and reference field.So basically: table1.row1 references table2.row1. I need to duplicate the data in table1.row1 (creating table1.row2) with the same reference to table2.row1.Is there any easy way to do this in SQL? I'm just looking for some ideas or a framework to accomplish this.
View 1 Replies View RelatedWe have a distributed Database in DB2 and SQL Server 2000.
As the user updates/Inserts data to DB2 Database , the data need to be dynamically replicated to SQL Server.
Please Let me know the best possible method of doing it.
Thanks,
Ravi.
Hello group,i am relatively new to SQL-Server database, but i have lots ofexperience with DB2 and Oracle Database. One of my tasks is setting upa replication between a Mysql-Database running on Linux and one of ourSQL-Servers.How do i achieve this ?If i understand the documentation correctly you have to program thereplication mechanism for yourself or you have to use some third partytool.Could anyone please outline, how to set up the replication mechanism(pointing me to some web-site should be enough) and also tell me ifthere is any third party tool.Thanks in advance and greetings from ViennaUli
View 1 Replies View RelatedI have a database A include five tables, and have more than 1,500,000 rows. There is a replica database of A. First of all, there is no data in the two dbs. When I finish inserting data into A, the replica db seems still work, the log file size still changes. How can I know the replication finished or not? How long it will take to finish replicate 1,500,000 rows of data?
View 1 Replies View RelatedWe have merge replication, Publisher work on SQLserver2005
standard edition,
Subscriber work on
SQL Express. My problem is following.
Considering that the Subscriber is on SQL Express we can use
only Merge replication.
Is it possible to develop merge replication to replicate
data only from Subscriber to Publisher.
Hi Guys,
View 7 Replies View RelatedHello,
I have a small three server development environment where I am getting my feet wet with replication. I have set up peer-to-peer transactional replication and it works fine for data added to the publication's table after the publication was created. However, rows in the table that existed prior to the publication's creation have never replicated. If any of the "old" rows are edited they cause an error on the subscribing servers when the replicator attempts to apply updates to rows that do not exist.
How can I get the old rows that predate the publication to replicate?
Thanks,
BCB
Hi Guys
I am trying to replicate data from DB2/AS400 to SQL Server2005 (ENT edition) currently we use 3rd party tool to replicate data from DB2 to SQL Server2000 (ENT edition) and like to get rid of this 3rd party tool. I am searching for the last 3 weeks but didn€™t get a good starting point. I have linked DB2 to SQL Server2005 and can run queries against DB2/AS400 box. Now I want to set up transactional replication from DB2 to SQL Server 2005. I have read about peer to peer topologies but I don€™t know if that€™s the route I have to take?
So can someone please help me? I really appreciate your help.
Thanks
Tariq
pulling data from a table in Server A and creating the same table on Server B.
From what I've read you can't export/import or use a wizard as the db is of the type dbo.md_*****.
I am migrating DTS packages to SSIS (recreating all the logic).
I have a Data Driven Query task in DTS with
Source query - select x,y from table1 (from database db1)
Binding - table2 which contains columns which match table1 x,y (fron database db2)
Transformation - maping from source table1 x,y to Binding table2 x,y
Queries - type update update table2 set x=? where y=?
I know that there is no similar task in SSIS,can someone tell me how to replicate this in SSIS
Thanks in Adv
there are several remote locations where sql is running, my company has asked me to find a way to collect all the data from the remote locations to a central location automatically,for example day to day data should be synced at night time from 2am to 7 am and it should be compressed automatically before data transfers to the central location. NOTE there is no domain only standalone workstations
View 3 Replies View RelatedIs it possible to replicate data from 3 publishers to a single/central subscriber transactionally? In other words I have Server A, Server B, Server C with databases A,B,C respectively. I need to replicate 2 articles from A,2 from B and 2 from C to a central Server D that hosts database D. D will have only 6 articles. The replication is Transactional Replication.
If it is possible what will be the drawbacks of such implementation? (if one server goes down will the whole replication break?) If not possible then what is the best way of implementing this?
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string basedÂ
column b: is the source filename of the data load (varchar 256)
....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
Hi ! I have a textbox and a Search button. When the user inputs a value and press the button, i want a datagrid to be filled.
The following code runs:
Dim connect As New Data.SqlClient.SqlConnection( _
"Server=SrvnameSQLEXPRESS;Integrated Security=True; UID= ;password= ; database=dtbsname")
connect.Open()
Dim cmd As New SqlCommand
Dim valor As New SqlParameter("@valor", SqlDbType.VarChar, 50)
cmd.CommandText = "Ver_Contactos_Reducido"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = connect
cmd.Parameters.Add(valor)
cmd.Parameters("@valor").Value = texto
Dim adapter As New SqlDataAdapter(cmd)
Dim ds As New System.Data.DataSet()
adapter.Fill(ds)
GridViewContactos.DataSource = ds
GridViewContactos.DataBind()
connect.Close()
I drag a datagrid on the page and only changed its Id.
Into the SQL database the stored procedure is the following:
ALTER PROCEDURE [dbo].[Ver_Contactos_Reducido]
(@Valor VARCHAR(100))
AS
BEGIN
SET NOCOUNT ON;
SELECT NombreRazonSocial, Nombre, Apellido,TelefonoLaboral,
Interno, TelefonoCelular, Email1, Organizacion
FROM CONTACTOS
WHERE NombreRazonSocial = @Valor OR Nombre = @Valor OR Apellido = @Valor OR TelefonoLaboral = @Valor OR Interno = @Valor OR
TelefonoCelular =@Valor OR Email1 = @Valor OR Organizacion= @Valor
END
When i run the page i can't see the datagrid, and after i enter a text and press the button, nothing happens. What am i doing wrong??
Thks!!
I have a view that give me the data of all the batched. Now I am using a query on view to get a single batched data. when I am using direct query it was taking 0 sec but when I am using Through view "select * from myView where batched=2" then its taking 30 mnt.
View 3 Replies View RelatedI have table:
ID Name Value
1 A V1
2 B V2
3 A V3
4 E V4
5 A V5
6 G V6
7 B V7
8 E V8
Now i want to display :
Name : A
1 V1
3 V3
5 V5
Name : B
2 V2
7 V7
Name : E
4 V4
8 V8
Name : G
6 V6
Any one help me ?
Thank you very much.
When using ADO.Net to retrieve data from SQL Server into a SQLDataReader I use this code in a Data Access class:
public SqlDataReader GetView(string ViewName)
{
SqlConnection myConnection = new SqlConnection(ConnectionString);
SqlCommand myCommand = myConnection.CreateCommand();
myCommand.CommandText = ViewName;
myCommand.CommandTimeout = 120;
// Mark the Command as a SPROC
[COLOR="Red"]myCommand.CommandType = CommandType.StoredProcedure[/COLOR];
// Execute the stored procedure and Return the datareader result
myConnection.Open();
return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
This works fine when I am getting the data from a Stored Procedure.
But, on this occasion, I want to get the data from a View. How can I do this? There does not seem to be an option of CommandType.View
Thanks for any help.
Is there a way of changing the value of a column in a view? For example, I have a table with defect code and a description (other columns as well). In a view, I would like to see only the defect code and the description, but if the code is > 90, I would like to define what the description is.
TIA
Jennifer
I created a view of a table like the following:
create view SomeView
as
select * from SomeTable
where SomeField > 0
I later modified the table to include more fields INTERSPERSED among the previous ones. I noticed that if I didn't recreate the view, the data was shifted if I used a select statement to query the view. I had dates associated with field names where there should be varchars, FirstNames became addresses, etc. Without naming each field in the select statement, does anyone know of a better way to create a view so that you don't have to remember which views do a "select *"?
Hi ,
I have question regard data partition view .
Please see below sample from BOL + sample of execution plane .
I would like to ask what is the way to avoid the optimizer scan tables out of the scope (I would expect that the only table for this query will be SUPPLY1)
Thanks,
Eyal
--This example uses tables named SUPPLY1, SUPPLY2, SUPPLY3, and SUPPLY4, which correspond to the supplier tables from four offices, located in different countries/regions.
USE tempdb
GO
--create the tables and insert the values
CREATE TABLE SUPPLY1 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 1 and 150),
supplier CHAR(50)
)
CREATE TABLE SUPPLY2 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 151 and 300),
supplier CHAR(50)
)
CREATE TABLE SUPPLY3 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 301 and 450),
supplier CHAR(50)
)
CREATE TABLE SUPPLY4 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 451 and 600),
supplier CHAR(50)
)
GO
--create the view that combines all supplier tables
CREATE VIEW all_supplier_view
AS
SELECT *
FROM SUPPLY1
UNION ALL
SELECT *
FROM SUPPLY2
UNION ALL
SELECT *
FROM SUPPLY3
UNION ALL
SELECT *
FROM SUPPLY4
GO
INSERT all_supplier_view VALUES ('1', 'CaliforniaCorp')
INSERT all_supplier_view VALUES ('5', 'BraziliaLtd')
INSERT all_supplier_view VALUES ('231', 'FarEast')
INSERT all_supplier_view VALUES ('280', 'NZ')
INSERT all_supplier_view VALUES ('321', 'EuroGroup')
INSERT all_supplier_view VALUES ('442', 'UKArchip')
INSERT all_supplier_view VALUES ('475', 'India')
INSERT all_supplier_view VALUES ('521', 'Afrique')
GO
/* */
SELECT * FROM all_supplier_view WHERE supplyID BETWEEN 1 and 150
Hi,
I have an accounting table with 5 year of data for an account, the table headers look as following
Acctno___Year___db_1,db_2,db,db_3,db4,….db_12,Cr_1,cr_2,cr_3…,cr-12
I want to with select statement to convert the data to query the header must be the following:-
Years1___Year2___year3___year4____year5
Db_1
Db_2
Db_3
.
.
.
Thanks for any help
Guys,
How do I create a view that will pull data from dbases on 2 different servers ?
I have 2 server names....
svr10mbr01
svr11mbr02
2 Databases...
PWBstaff on svr10mbr01
PWBdata on svr10mbr02
I need to create a view in PWBdata that will do something like....
Select * from PWBstaff.dbo.staff
Is this possible, if so what is the syntax.
Ta
Bob
"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com