Can changes made to replicated stored procedures (and User-defined Functions) on the Publisher update the Subscriber database?
If I ALTER a SP (or UDF) on Database1 with peer-to-peer Transaction Replication to Database2, will the alter replicate to the Subscriber(s)?
Thomas
ThomBeaux
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
How would I go about tracing UDF performance in profiler? I'd like to
specifically know the impact of the UDF without having to dig into the
execution plan of the statement containing it. Is this possible?
I know there is a lot of information already out there on this topic,but given the following scenario...--------------------------------------------------------------------------------------------------------------------------------------Create a view like so ( pardon the pseudo-code )...CREATE View vwContactAddressesSelect * FROM Contact INNER JOIN Address ON Contact.ContactID =Address.ContactIDAnd then do a sargable select from the view using a stored procedureCREATE STORED PROCEDURE spSelect_ContactAddresses@ContactID intASSelect * FROM vwContactAddresses WHERE ContactID = @ContactID--------------------------------------------------------------------------------------------------------------------------------------In my understanding, "vwContactAddresses" would be substituted with theactual SQL join statement when the view is accessed.So for the stored procedure in question an execution plan for"Select * FROM Contact INNER JOIN Address ON Contact.ContactID =Address.ContactID WHERE ContactID = @ContactID" would be cached.Correct?With regards to execution plan caching, is this not the same ascreating an inline UDF that takes parameters or just creating a storedprocedure that would do the join w/out the view reference?
View 3 Replies View RelatedHi,
What is the difference between a computed column and a UDF?
Is a computed column the same as the "Formula" field under Design Table in Enterprise Manager?
Also, what is the proper syntax for the Formula field? Can I use regular SQL on it or is there more to it?
thanks,
Frank
-- Author: Igor Nikiforov, Montreal, EMail: udfs@sympatico.ca
-- GETWORDCOUNT() User-Defined Function Counts the words in a string.
-- GETWORDCOUNT(@cString[, @cDelimiters])
-- Parameters
-- @cString nvarchar(4000) - Specifies the string whose words will be counted.
-- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString.
-- The default delimiters are space, tab, carriage return, and line feed. Note that GETWORDCOUNT( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter.
-- Return Value smallint
-- Remarks GETWORDCOUNT() by default assumes that words are delimited by spaces or tabs. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.
-- If you use 'AAA aaa, BBB bbb, CCC ccc.' as the target string for dbo.GETWORDCOUNT(), you can get all the following results.
-- declare @cString nvarchar(4000)
-- set @cString = 'AAA aaa, BBB bbb, CCC ccc.'
-- select dbo.GETWORDCOUNT(@cString, default) -- 6 - character groups, delimited by ' '
-- select dbo.GETWORDCOUNT(@cString, ',') -- 3 - character groups, delimited by ','
-- select dbo.GETWORDCOUNT(@cString, '.') -- 1 - character group, delimited by '.'
-- See Also GETWORDNUM() User-Defined Function
-- UDF the name and functionality of which correspond to the same built-in function of Visual FoxPro
CREATE function GETWORDCOUNT (@cSrting nvarchar(4000), @cDelimiters nvarchar(256) )
returns smallint
as
begin
-- if no break string is specified, the function uses spaces, tabs and line feed to delimit words.
set @cDelimiters = isnull(@cDelimiters, space(1)+char(9)+char(10))
declare @p smallint, @end_of_string smallint, @wordcount smallint
select @p = 1, @wordcount = 0
select @end_of_string = 1 + datalength(@cSrting)/(case SQL_VARIANT_PROPERTY(@cSrting,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
while dbo.CHARINDEX_BIN(substring(@cSrting, @p, 1), @cDelimiters, 1) > 0 and @end_of_string > @p -- skip opening break characters, if any
set @p = @p + 1
if @p < @end_of_string
begin
set @wordcount = 1 -- count the one we are in now count transitions from 'not in word' to 'in word'
-- if the current character is a break char, but the next one is not, we have entered a new word
while @p < @end_of_string
begin
if @p +1 < @end_of_string and dbo.CHARINDEX_BIN(substring(@cSrting, @p, 1), @cDelimiters, 1) > 0 and dbo.CHARINDEX_BIN(substring(@cSrting, @p+1, 1), @cDelimiters, 1) = 0
select @wordcount = @wordcount + 1, @p = @p + 1 -- Skip over the first character in the word. We know it cannot be a break character.
set @p = @p + 1
end
end
return @wordcount
end
GO
-- Author: Igor Nikiforov, Montreal, EMail: udfs@sympatico.ca
-- GETWORDNUM() User-Defined Function
-- Returns a specified word from a string.
-- GETWORDNUM(@cString, @nIndex[, @cDelimiters])
-- Parameters @cString nvarchar(4000) - Specifies the string to be evaluated
-- @nIndex smallint - Specifies the index position of the word to be returned. For example, if @nIndex is 3, GETWORDNUM( ) returns the third word (if @cString contains three or more words).
-- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString.
-- The default delimiters are space, tab, carriage return, and line feed. Note that GetWordNum( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter.
-- Return Value nvarchar(4000)
-- Remarks Returns the word at the position specified by @nIndex in the target string, @cString. If @cString contains fewer than @nIndex words, GETWORDNUM( ) returns an empty string.
-- See Also
-- GETWORDCOUNT() User-Defined Function
-- UDF the name and functionality of which correspond to the same built-in function of Visual FoxPro
CREATE function GETWORDNUM (@cSrting nvarchar(4000), @nIndex smallint, @cDelimiters nvarchar(256) )
returns nvarchar(4000)
as
begin
-- if no break string is specified, the function uses spaces, tabs and line feed to delimit words.
set @cDelimiters = isnull(@cDelimiters, space(1)+char(9)+char(10))
declare @i smallint, @j smallint, @p smallint, @q smallint, @qmin smallint, @end_of_string smallint, @LenDelimiters smallint, @outstr nvarchar(4000)
select @i = 1, @p = 1, @q = 0, @outstr = ''
select @end_of_string = 1 + datalength(@cSrting)/(case SQL_VARIANT_PROPERTY(@cSrting,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
select @LenDelimiters = datalength(@cDelimiters)/(case SQL_VARIANT_PROPERTY(@cDelimiters,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
while @i <= @nIndex
begin
while dbo.CHARINDEX_BIN(substring(@cSrting, @p, 1), @cDelimiters, 1) > 0 and @end_of_string > @p -- skip opening break characters, if any
set @p = @p + 1
if @p >= @end_of_string
break
select @j = 1, @qmin = @end_of_string -- find next break character it marks the end of this word
while @j <= @LenDelimiters
begin
set @q = dbo.CHARINDEX_BIN(substring(@cDelimiters, @j, 1), @cSrting, @p)
set @j = @j + 1
if @q > 0 and @qmin > @q
set @qmin = @q
end
if @i = @nIndex -- this is the actual word we are looking for
begin
set @outstr = substring(@cSrting, @p, @qmin-@p)
break
end
set @p = @qmin + 1
if (@p >= @end_of_string)
break
set @i = @i + 1
end
return @outstr
end
GO
-- Is similar to the built-in function Transact-SQL charindex, but regardless of collation settings,
-- executes case-sensitive search
-- Author: Igor Nikiforov, Montreal, EMail: udfs@sympatico.ca
CREATE function CHARINDEX_BIN(@expression1 nvarchar(4000), @expression2 nvarchar(4000), @start_location smallint = 1)
returns nvarchar(4000)
as
begin
return charindex( cast(@expression1 as nvarchar(4000)) COLLATE Latin1_General_BIN, cast(@expression2 as nvarchar(4000)) COLLATE Latin1_General_BIN, @start_location )
end
GO
Hi!I need to refresh an entire database.I can recompile SPs with sp_recompile (or DBCC FLUSHPROCINDB), andrefresh views with sp_refreshView, but I cannot find any way torefresh my user-defined functions (some of them are like views, withparameters).Any help appreciated :) !Ben
View 5 Replies View RelatedHi,
I have written an udf that checks for validations of an email address and returns 0 / 1 as per the validations. I came across an artical on MSDN that describes the steps to make my own system udf. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01l1.asp
with help of that artical, I made my udf as a system udf. and now I can use it as follows.
select * , fn_isvalidemail(email) from #tp
now, I wanted to use this as a declarative check constraints in my tables. so I tried
create table testtable
( email_addr varchar (255) null CONSTRAINT CK_email_chk CHECK ( fn_isvalidemail(email) = 1) )
although this is syntax is logically correct, it does not get compiled
it gives me an error saying,
Server: Msg 195, Level 15, State 10, Line 2
'fn_isvalidemail' is not a recognized function name.
the strange part is that i made the function as another normal udf, It works in declarative constraints.
create table testtable
( email varchar (255) null CONSTRAINT CK_1p12 CHECK ( dbo.isvalidemail(email) = 1) )
Any thoughts on this ?
- Amit
I have always learn that UDFs are not Pre-Compiled while Stored procedures are. But I always had this questions why are UDFs are not Pre-Compiled.
I searched online for the answer but dint get an concrete justification on the same.
Hello,
How can I call UDFs on linked servers in SQL Server 2005?
More details:
I have some UDFs on Instance "VAXPServerA" in Database "DB1", for example "getSomething(number)".
It might look like this (for example):
----
CREATE FUNCTION [dbo].[getSomething] (
@theNumber int
)
RETURNS int AS
BEGIN
DECLARE @result int
SET @result = theNumber * 5
RETURN @result
END
----
I can call this function inside of the DB by typing:
----
SELECT dbo.getSomething(5)
----
I also can call the function from another DB on instance "VAXPServerA" by typing:
----
SELECT DB1.dbo.getSomething(5)
----
I have a second instance called "VAXPServerB", and "VAXPServerA" is a linked server in "VAXPServerB". I can do selects and stuff, it works fine.
But when I want to call the function...
----
SELECT [VAXPServerA].DB1.dbo.getSomething(5)
----
I obtain the next error:
Msg. 207, Level 16, State 1, Line 1
The column name 'VAXPServerA' is not valid.
Any hint?
Thanks in advance
David
I have a bunch of SPs that all rely on a UDF that parses a commadelimitted list of numbers into a table. Everything was working fine,but now my application is growing and I'm starting to approach the 8000character limit of the varChar variable used to store the list.I would like to change the UDF only and avoid having to dig through allof my stored procedures. I was hoping to use the text datatype toallow for much larger lists, but I am unable to perform anymanipulations necessary to parse the list into a table. I have triedPATINDEX, but it alone is not enough without the text maniuplations andI don't think the sp_xml_preparedocument can be used in a UDF.Anyone with any thoughts on managing large arrays in t-sql?thanks,Matt Weiner
View 2 Replies View RelatedHello Folks,I encountered a problem with SQL server 2000 and UDFs.I have a scalar UDF and a table UDF where I would like the scalar UDFto provide the argument for the table UDF like in:SELECT*FROMtransaction_tWHEREtrxn_gu_id in (select get_trxns_for_quarter(get_current_quarter( GetDate() ) ))'get_current_quarter' returns an integer which is a GUID in a tablecontaining business quarter definitions, like start date, end date.'get_current_quarter' is a scalar UDF.'get_trxns_for_quarter' will then get all transctions that fall intothat quarter and return their GUID's in a table.'get_trxns_for_quarter' is a table UDF.This doesn't seem to work at all. Regardless whether I provide thenamespace (schema) calling the scalar UDF or not. Error message isjust different.Both functions operate correctly invoked un-nested.The whole expression does work fine if I turn 'get_trxns_for_quarter'into a scalar UDF as well, e.g. by returning just one trxn_gu_id withe.g. MAX() in a scalar datatype. But of course that's no good to me.It also works fine if I select the result of 'get_current_quarter'into a variable and pass that variable into 'get_trxns_for_quarter'.But that's no good to me either since then I cannot use the wholething embedded into other SELECT clauses.Both UDF's are non-deterministic but I couldnt see how that would havean impact anyway.Never mind the syntax on that example or anyhting, I tried all theobvious and not so obvious stuff and it really seems to come down tothe fact that one UDF is scalar and the other one is not. However, Idid not come across any type of information saying that this cannot bedone.Have you any ideas?Any help would be greatly appreciated.Carsten
View 6 Replies View RelatedI am in the process of building my first "large scale" database system (after 15+ years of developing Windows Apps and Web Apps) - so I am very VERY "Green" when it comes to Database development & SQL et al.
A little context setting: I am building a multi-tier Statistical Analysis & Reporting system where the "end product" will be Reports created in Reporting Services. There are a ton of business rules that I am implementing in a Business Logic Tier (hidden from the "end user" by a Data Access Tier) comprised of SQL in the form of UDFs (scalar) and Views.
The question: I have been reading that UDFs cause a performance hit compared to things like in-line functions. Alot of the Rules (implemented as Scalar UDFs) build on each other so that the output of UDF #1 is used as input to UDF #2.
So far I am implementing the Business Logic as a hierarchy of Views (7 Views to be exact) with each view implementing multiple Rules; each Rule basically a Scalar UDF. Below is an example of what I am doing:
Example
View #1 -> Select A, B, C, funcX1(A) as ValueX1, funcY1(B, C) as ValueY1 FROM someView
Then
View #2 -> Select A, B, C, ValueX1, ValueY1, funcX2 (ValueX1) as ValueX2, funcY2(ValueY2) as ValueY2 FROM View#1
Currently I have a hierarchy of 7 views that each use UDFs to implement the Business Rules, where the value calculated from a UDF in one View is used as input to UDF in a View further down the Hierarchy.
Is there a better way of implementing all of the Rules instead of using multiple Views with a bunch of UDFs?
The "end product" dataset is then exposed as a Stored Procedure to the reports in Reporting Services.
Any help would be GREATLY appreciated.
Thanks!
- marty
There is something very strange going on here. Tested with ADO 2.7 andMSDE/2000. At first, things look quite sensible.You have a simple SQL query, let's sayselect * from mytab where col1 = 1234Now, let's write a simple VB program to do this query back to anMSDE/2000 database on our local machine. Effectively, we'llrs.open sSQLrs.closeand do that 1,000 times. We wont bother fetching the result set, itisn't important in this example.No problem. On my machine this takes around 1.6 seconds and modifyingthe code so that the column value in the where clause changes eachtime (i.e col1 = nnnn), doesn't make a substantial difference to thistime. Well, that all seems reasonable, so moving right along...Now we do it with a stored procedurecreate procedure proctest(@id int)asselect * from mytab where col1 = @idand we now find that executingproctest nnnn1,000 times takes around 1.6 seconds whether or not the argumentchanges. So far so good. No obvious saving, but then we wouldn'texpect any. The query is very simple, after all.Well, get to the point!Now create a table-returning UDFcreate function functest(@id int) returns table asreturn(select * from mytab where col1 = @id)try calling that 1,000 times asselect * from functest(nnnn)and we get around 5.5 seconds on my machine if the argument changes,otherwise 1.6 seconds if it remains the same for each call.Hmm, looks like the query plan is discarded if the argument changes.Well, that's fair enough I guess. UDFs might well be more expensive...gotta be careful about using them. It's odd that discarding the queryplan seems to be SO expensive, but hey, waddya expect?. (perhaps theUDF is completely rebuilt, who knows)last test, then. Create an SP that calls the UDFcreate procedure proctest1(@id int)asselect * from functest(@id)Ok, here's the $64,000 question. How long will this take if @idchanges each time. The raw UDF took 5.5 seconds, remember, so thisshould be slightly slower.But... IT IS NOT.. It takes 1.6 seconds whether or not @id changes.Somehow, the UDF becomes FOUR TIMES more efficient when wrapped in anSP.My theory, which I stress is not entirely scientific, goes somethinglike this:-I deduce that SQL Server decides to reuse the query plan in thiscircumstance but does NOT when the UDF is called directly. This iscounter-intuitive but it may be because SQL Server's query parser istuned for conventional SQL i.e it can saywell, I've gotselect * from mytab WHERE [something or other]and now I've gotselect * from mytab WHERE [something else]so I can probably re-use the query plan from last time. (I don't knowif it is this clever, but it does seem to know when twotextually-different queries have some degree of commonality)Whereas withselect * from UDF(arg1)andselect * from UDF(arg2)it goes... hmm, mebbe not.... I better not risk it.But withsp_something arg1andsp_something arg2it goes... yup, i'll just go call it... and because the SP was alreadycompiled, the internal call to the UDF already has a query plan.Anyway, that's the theory. For more complex UDFs, by the way, theperformance increase can be a lot more substantial. On a big complexUDF with a bunch of joins, I measured a tenfold increase inperformance just by wrapping it in an SP, as above.Obviously, wrapping a UDF in an SP isn't generally a good thing; theidea of UDFs is to allow the column list and where clause to filterthe rowset of the UDF, but if you are repeatedly calling the UDF withthe same where clause and column list, this will make it a *lot*faster.
View 3 Replies View RelatedWe have migrated a database (myDb2014) to a SQL Server 2014 instance. We have another on a 2008R2 instance (myDb2008). Both on the same server.
I have setup a linked server to the 2014 instance from the 2008R2 one.
I have a number of sprocs on myDb2008 that call TVFs and sprocs on myDb2014. This worked fine when they were both on the same instance.
Now however, I have an issue where I would have to update all the references in each calling query.
I looked into creating Synonyms but this only works for Tables/Views.
Is there a workaround to querying myDb2014 TVFs/sprocs without having to update each calling query in myDb2008?
How can I list the stored procedures and user-defined functions that reference a given column? I could search ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES for '%MyColumnName%' but MyColumnName is not always unique.
Thanks.
(From an exchange originally posted on SQLServer.com, which wasn't resolved...)
To return views tailored to the user, I have a simple users table that holds user IDs, view names, parameter names, and parameter values that are fetched based on SUSER_SNAME(). The UDF is called MyParam, and takes as string arguments, the name of the view in use, and a parameter name. (The view the user sees is really a call to a corresponding table returning UDF, which accepts some parameters corresponding to the user.)
But the performance is very dependent on the nature of the function call. Here are two samples and the numbers reported by (my first use of) the performance monitor:
Call to table returning UDF, using local variables:
declare @orgauth varchar(50)
set @orgauth = dbo.MyParam('DeptAwards', 'OrgAuth')
declare @since datetime
set @since = DATEADD(DAY,-1 * dbo.MyParam('DeptAwards', 'DaysAgo'),CURRENT_TIMESTAMP)
select * from deptAwardsfn(@orgauth,@since)
[187 CPU, 16103 Reads, 187 Duration]
Call to same table returning UDF, using scalar UDFs in parameters:
SELECT *
from deptAwardsFn (
dbo.MyParam('DeptAwards', 'OrgAuth')
,DATEADD(DAY,-1 * dbo.MyParam('DeptAwards', 'DaysAgo'),CURRENT_TIMESTAMP)
)
[20625 CPU, 1709010 Reads, 20632 Duration]
(My BOL documentation claims the CPU is in milliseconds and the Duration is in microseconds -- which I question.) Regardless of the unit of measure, it takes a whole bunch longer in the second case.
My only guess is that T-SQL is deciding that the parameter values (returned by dbo.MyParam) are nondeterministic, and continually reevaluates them somehow or other. (What ever happened to call by value?)
Can anyone shed some light on this strange (to me) behavior?
----- (and later, from me)---
(I have since discovered that the reference to CURRENT_TIMESTAMP in the function argument is the cause, but I suspect that is an error -- it should only capture the value of CURRENT_TIMESTAMP once, when making the function call IMHO.)
I am new (very new) to SQL. I 'm trying to set up SQL 7 to replicate a particular table on a daily schedule and then FTP the file out to a remote directory. Sounds easy! I'm sure it is, I just need someone to walk me through the steps.
If anyone can help me out I would really appreciate it.
Hi my friends,
I'm replicating with SQL Server 2000(updated Service Pack 3) get error:
SQL Server Enterprise Manager could not configure 'BREEZE1' as the Distributor for 'BREEZE1'.
Error 14113: Could not execute "'"C:program filesmicrosoft sql server80 ools\binnosql" -E -l60 -t60 -d"distribution" -b -i"c:MSDESQLBinnMSSQL\installinstdist.sql" -o"c:msdesqlinnmssql\installinstdist.out"'". Check 'instdist.out' in the install directory.
'distribution' is not configured as a distribution database.
Deleting database file 'c:msdesqldatamssqldatadistribution.ldf'.
Deleting database file 'c:msdesqldatamssqldatadistribution.mdf'.
I have a feeling this is failing because I have not C:msdesqlinnmssqlinstall directory at all.
Is there something I'm doing wrong...If the SP3 can affect???
This code :
declare @filenumber as char
set @filenumber = '65'
declare @filenu2 as char(6)
set @filenu2 = CONVERT(CHAR(6),(replicate('0',(6-len(@filenumber)))+ @filenumber ))
select @filenu2 as x
This should return 000065
but it returns 000006
i have stepped through it the replicate should replicate the 0 4 times
(6 - len of @filenumber) instaed its doing it 5 ??
Merge replication. We switched a publication over from push to pull and are now initiating everything within an application. We have just encountered a situation where it is now completely impossible to replicate DDL.
When this was a push subscription, we could execute the following and it would fly straight through the engine and hit every subscriber without having to do anything at all:
ALTER TABLE <tablename>
ADD <columnname> <datatype> NULL
Now that it is a pull subscription when I issue an ALTER TABLE and add a nullable column to the end of the table, it does NOT replicate at ALL. We get the following error message:
The schema definition of the destination table 'dbo'.'Player' in the subscription database does not match the schema definition of the source table in the publication database. Reinitialize the subscription without a snapshot after ensuring that the schema definition of the destination table is the same as the source table. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199478)
Get help: http://help/MSSQL_REPL-2147199478
The process was successfully stopped. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199481)
Get help: http://help/MSSQL_REPL-2147199481
It apears that we are now required to either reinitialize every subscriber every time we add a column or we are required to first distribute the DDL change to each subscriber, make sure they all have it, then add it to the publisher before anyone replicates, and then reinit every single one of them without a snapshot. This makes absolutely no sense at all.
The interesting thing is that we can add articles at will and those get applied with absolutely no problems at all to the subscribers without having to do anything other than add the article and generate a new snapshot.
Version 9.00.3042.00
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 RelatedWE have a production database that we need to sync with about 100
salesman's laptops. These laptops will not always be online and
would need to sync on the fly.
We are thinking of having a separate distribution database to handle
the load and creating either a pull subscription or even just a DTS
package. We would like to have the salesmen just signon and go to an
ASP page and hit an Icon and either the pull subscription or the dts
package would start the sync..
Anyone tried this before and have any other ideas??
Thanks for any help??
SB
I have set up a simple trans rep from a server in the office to the web. both servers are NT4sp6aSQL 7.0. Tables only.
The publication and distribution db are on the server at the office, and there is a full time connection through the firewall.
The initialization and first replication works perfectly, but after that, there is a message from the snapshot agent that "no subscriptions needed initialization", the logreader says thare are no replicated transactions and the Distribution agent says there are no replicated transactions. What am I missing?
Thanks in advance
Hi, 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
Can you have a database setup for both replication to server A and log shipping to server B?
Thanks,
Ken Nicholson
Hi,
I'm new to replication.
I configured Server named TESTER as publisher & distributer. Create new publication for database named TEST ( type : Transactional ), then Create a new push subcriptions for database TESTREPLICATION ( on the same Server : TESTER). Then I open a table in database TEST to input some data but I couldn't see any tables or data in database TESTREPLICATION ( I chose Continuously, not scheduled).
Yet, after the lunch, I could see the tables, no data. I tried to choose Start Synchronizing but not helped.
Please tell me if I configured incorrectly ?
Thanks,
Could someone please point me in the direction on where to look for info on how to:
Replicate an MS SQL 6.5 tables on an Oracle 10.2.0 server every 5-10min?
Hi:
In the publishing server, admin users could change and non-admin user password and also add login and drop login.
How could it happen to not only replicate the application database to subscriber server but also replicate the changes made in master database?
So that the remote subscriber server will have the most current login updates?
thanks
D
Hi,
When I'm using MS SQL transactional replication and select all the tables to be replicated, some of the tables are marked with the red cross signs and aren't being replicated. How do I include these tables into the MS SQL replication? I tried to build indexes and re-created publications, but it didn't work.
Please help!
Thanks in advance,
-Alla
Is any tool is available for replicating the Microsoft sql database..
View 1 Replies View RelatedHi all, I have done the replication its working good.Now i have created the new table in publisher. but i have created the same table in sub scription too. how can i replcate this new table..?
Thanks
Krishna
how do you design a dev box?
how do you replicate from prod to dev?
Ideas will be appreacited.
do you get a bak file and restore it?
DTS ?
linkservers?
???
=============================
http://www.sqlserverstudy.com