I am experiencing a problem with deployment of a VB .Net application which carries out merge replication, and would greatly appreciate any assistance. I am currently struggling to find stuff about my problem on the net, which probably means either (a) I'm missing something really simple or (b) I shouldn't be trying to do this in the first place. :)
The story so far:
1. I added the SQLMergXLib.dll COM to my Visual Studio project, allowing me access to SQL Server merge replication functionality from my code, and wrote a procedure for synchronising a given pull subscription. This worked beautifully and with minimal fuss.
2. I tried to deploy the project to a different machine. I received an error to the effect of, "Couldn't create object, it's a COM object and it's not registered correctly." I have encountered this problem a few times before when trying to use Interop, so I called myself a few names, went back to the deployment project, and specified that the object should be registered. Still no joy. I tried this a couple of different ways round but then...
3. ...found an article on MSDN suggesting that I should consider using the managed Microsoft.SqlServer.Replication, Microsoft.SqlServer.Management, Microsoft.SqlServer.RMO interfaces instead. I reasoned that this would eliminate the registry issue altogether as everything would become native to the .Net framework, and this seemed infinitely preferable anyway, so I promptly substituted the SQLMergX DLL for these, rehashing my code to match. Again, this built and ran OK on my development machine.
4. I tried to deploy the project to a different machine. I received the following error: "Could not load file or assembly 'Microsoft.SqlServer.Replication, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. This application has failed to start because the application configuration is incorrect."
Now it's worth pointing out that I think I know what the problem might be here: No SQL Server of any kind is installed on the target machine. However, SQL Server is not required to deploy standard database solutions in .Net, and I am hoping this is also true with the Microsoft.SqlServer... namespaces, as it is a requirement that the subscription database can be located anywhere (not necessarily the target machine), and therefore that SQL Server need not be present on the target machine. However, if this is simply impossible, please let me know so that I can look at other alternatives.
Other than that, I don't mind what method I use to get the synchronisation working as long as it works (I've been at this a good few days now...), so if anyone has done this before and can offer any assistance (any registering/files I need to include etc, I am hoping it is something that simple), it would be appreciated.
Hello 1.) Is there any solution for integrating the deployment of the sql server express into the setup project of my vb 2005 app ? 'Cause it is too much to install the framework... then the sql server and then the app....... Any help would be appreciated!
hi,i'm new to deploying asp.net pages with built in asp.net login controls over the web server. i've uploaded my asp.net pages with web.config and placed my ASPNETDB in App_Data folder on the webserver as it was placed in local machine. but it dint work and displayed the following error
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
i also enabled the TCP/IP connection on my local machine to accept remote connections.. Can any1 please provide me a step to step guide on how to deploy the webiste? Thanking u in advance. Regards
I am a little bit confused about making an installer/deployer.
I have an application using SQL Server 2005 CE (I have just updated to the most recent version Jan 2007). It will run on a new desktop PC (windows XP) if I install the SQL CE runtime and then copy the .EXE and the .SDF database file. This is great but my client wants the whole thing to install as one operation. I thought click once could do this but it seems that is only for Pocket PC targets? Can anyone point me to a method to make an appropriate installer please?
Pete
I am using the following: SQLServerCE31-EN.msi - installer for SQL CE server runtime SSCE31VSTools-ENU.exe - installed over VS 2005 SP1.
Can someone show me, or direct me, to a source, that shows me how, and what to change, when deploying a website from a development server running Sql Ex to a production server running Sql server 2005. I can’t get the sites to run under Sql server 2005.
They work in Sql Ex. what must I change? The connection string, to what format? and what else? I attached the dB to Sql 2005 and browsed the content in the Sql manager. But can’t get the aspx pages to work on the server.
I am encountering a timeout expired error when deploying a .NET assembly in SQL Server 2005 using Visual Studio.NET. I already enabled SQL Server for CLR. Whenever I run the CREATE ASSEMBLY command in SQL Management Studio, my query just ends up executing without stopping.
I'm having issues setting up merge replication and the errors I'm getting are inconsistent but lead me to believe there is a bigger underlying issue than what they actually indicate.
Issue: unable to initialise subscriber with snapshot for merge replication. Fails for various reasons regardless of environment and setup. Previously this has deployed fine but now it is not.
Publisher is SQL 2005 (9.0.2047), Subscribers are SQLExpress or SQL Dev
The various errors I get are as follows:
These come mixed together generally... Unable to INSERT ... cannot insert NULL into column rowguid. [This error comes up on different tables for different snapshots, including ones with no records] bcp (Bulk Copy) error 20253 batch send failed Unspecified error Failed to send batch after max errors end of file reached, terminator missing or field data incomplete
Actions I have taken so far, all to no effect:
Adjusted Agent Profile to increase timeout values and decrease packet sizes Took new snapshots (tables where errors occured changed but still ame errors) Created a local subscription database on the server to remove any network related issues (using merge sync over VPN) Deleted and recreated Publication Created second publication with different articles on same database to find same errors in different locations again
I've tried reinitializing subscriptions (before subscribers were dropped and I tried recreating) but the DROP command fails due to FK constraints so if I reinitialize any subscriptions they will fail to be applied. Have tried editing the properties to just delete data and not drop tables but that still failed.
I've tried setting up subscriber without snapshot - created publication and snapshot, backed up db, copied to subscriber and restored there. See this article: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=689428&SiteID=1 This bloke suffered same results and the response from MS was to change the help files, not functionality. Doesn't help us dealing with subscribers via satellite AND unable to deploy snapshots...
I've checked the service pack releases to see if any issues like this are addressed and found nothing relevent.
My gut feeling is that the snapshot is not being generated correctly for some reason - system stored procedure errors perhaps.
So far my experience with replication has been good, with the odd issue or frustrating limitation in older versions, but this is really killing me...
Barry
(Sorry that author is "Anonymous" - must have messed that up)
I am developing a application where the database needs to be deployed from the central database server which is sql server 2005, and we have only few fields and few new tables which need to be deployed into pocket pc using sql mobile from sql server 2005. We would like to design the database in sql server 2005 for mobile, which will be only structure and will be deployed into PDA using web service.
Hello, I work for a company that manages computers and their software using Active Directory. One of our packages requires SQL Express 2005, and so far I've been unable to find a good resource online. All I need to do is find a .msi file or a batch script that will install it. Thanks!
We have a large number of clients attempting to replicate two publications on 2005 Express databases (2 publications subscribed to the one subscriber database) with our 2005 Server (9.00.3042.00 SP2 Standard Edition) and experiencing two significant problems:
1) Users experience the following message:
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload).
This problem should not apparently occur with SQL Server 2005 (or 2005 Express) instances with SP2 applied. All clients experiencing this problem have SP2 installed as does our Server and the retention period is 30 days. The subscribers have been replicating well under that.
2) Replications never succeed after appearing to replicate/loop around for hours
This issue is the most critical as we have clients who have been installed and re-installed with new instances of SQL Server 2005 Express, new empty databases (on subscriber before snapshot extraction), and using fresh snapshots (less than an few hours old) which cannot successfully replicate.
Interestingly there is at least 1 instance where several computers are subscribed and successfully replicating the same database as another where replication refuses to succeed.
To test we have taken a republished database from another 2005 Server which is working fine and restored it to the same server as the one holding the database with which we are experiencing problems and subscribed to it. This test worked fine and replication of both publications went through fast and repeatedly without showing any signs of problem.
This indicates that the problem is perhaps data related as it appears localised to that database.
Below are two screenshots which may assist.
Screenshot 1 Shows that on the server side the replication attempts look like they are succeeding despite the fact that the subscriber end does not indicate success. Also the history indicates the the subscription has spent all it's time initialising and not merging any changes.
Screenshot 2 Shows a rogue process which has appears on many of the problem child subscribers. It shows a process running with no end time even though the job indicates failure in the message and even though other replication attempts appear to have succeeded after it. This process stays in the history showing that it is running even when I can find no corresponding process for it.
Can anyone suggest a further course of action/further testing/further information required which may assist?
This is extremely urgent and any assistance would be greatly appreciated!
Is there any way to measure bandwith usage during merge replication between sql server 2005 and sql server mobile 2005 running on a cradled wm5 mobile device.
Attaching the windows performance monitor to the network connection established over usb would work although I was wondering if there was something specific for this case integrated into Sql server 2005 / sql server mobile 2005 / Sql server management studio / third party tools that i could use ?
I have a problem when i start sincronyzing with the emulator of MSVS2005 to SQL2005 in Windows Vista. I have the same program in the emulator, but sincronyzing with windows XP Pro and no problem...
"Failure to connect to SQLServer with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect"
I just want to display all the data of a Single table into a Data Grid, I know that we can drag and drop the table on to a form and datagrid is generated, but here I want to retrive those values through my code, how should i do that
I am getting following errors while running the program Error 1) Error No. 28037, MS SQL Server 2005 Evrywhere Edition Error: A request to send data to the computer running IIS has failed. For more information see HRESULT Error 2) Error No. 0, SQL Server 2005 Evrywhere Edition ADO.Net Data Provider Error: The specified table does not exist [ JobLists ].
Can anybody please tell me, where I went wrong ??? In this code anywhere else????
Note: While adding a Data Source of SQL Server 2005 Mobile Edition, I have added that .sdf file into my project, thats why I have written the Data Source as : .DbFile.sdf
@"Data Source = .DbDotNetCF.sdf";
The code is as follows:
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.SqlServerCe;
namespace DeviceApplication1 { public partial class Form1 : Form { string filename = @".DbDotNetCF.sdf";
private DataSet dsJobLists;
public Form1() { InitializeComponent(); }
private void DeleteDB() { if (System.IO.File.Exists(filename)) { System.IO.File.Delete(filename); } }
private void Sync() { SqlCeReplication repl = new SqlCeReplication();
if (DbDotNetCFDataSetUtil.DesignerUtil.IsRunTime()) { // TODO: Delete this line of code to remove the default AutoFill for 'dbDotNetCFDataSet.JobLists'. this.jobListsTableAdapter.Fill(this.dbDotNetCFDataSet.JobLists); } } } }
I have created a merge replication correctlly( I suppose, there were no errros) Please help
I have been struggling to deploy a custom rendering extension onto Reporting services 2005. I have followed all the steps given in the MSDN article http://msdn.microsoft.com/msdnmag/issues/05/02/customrenderers/defaul...
Especially -
1. Copied the dll generated to the reporting service bin folder.
2. Made the entry in C:Program FilesMicrosoft SQL ServerMSSQL. 3Reporting ServicesReportServer sreportserver.config file as <Extension Name="CUSTOM_RENDERER" Type="MSDNMagazine.CustomRSRenderer.Renderer,MSDNMagazine.CustomRSRenderer"/
3. Made an entry in C:Program FilesMicrosoft SQL ServerMSSQL. 3Reporting ServicesReportServer ssrvpolicy.config file as
<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="Custom Render Extension" Description="This code group grants data extensions full trust."> <IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting Services ReportServerinMSDNMagazine.CustomRSRenderer.dll"/> </CodeGroup>
I am able to see the extension being listed in the Report manager list of available rendering options, But I get the following error from the report server when I try exporting to the custom rendering extension
An attempt has been made to use a rendering extension that is not registered for this report server. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Exception: An attempt has been made to use a rendering extension that is not registered for this report server.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[Exception: An attempt has been made to use a rendering extension that is not registered for this report server.] Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension) +489 Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension) +958
Looking at the logs of the report server at Max trace level, gives me the following dump
w3wp!processing!b!03/01/2007-22:35:22:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An attempt has been made to use a rendering extension that is not registered for this report server., ; Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An attempt has been made to use a rendering extension that is not registered for this report server. w3wp!chunks!b!03/01/2007-22:35:22:: v VERBOSE: ### ID=3f94a6f1-8ff7-448c-8cf3-494ab8fe4837, Length=0, CompressedLength=0, TimeCompressing=0, TimeUncompressing=0, Ratio=0, Buffering=0, Permanent=False w3wp!library!b!03/01/2007-22:35:22:: v VERBOSE: Transaction rollback. w3wp!webserver!b!03/01/2007-22:35:22:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An attempt has been made to use a rendering extension that is not registered for this report server. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An attempt has been made to use a rendering extension that is not registered for this report server. at Microsoft.ReportingServices.ReportProcessing.ReportRendererFactory.GetRenderer(String format, IReportServerInformation serverInfo) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.CreateRenderer(String format) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk createChunkCallback, RenderingContext rc, GetResource getResourceCallback) at Microsoft.ReportingServices.Library.RenderSnapshotAction.Render() at Microsoft.ReportingServices.Library.RSService.RenderFromSessionNoCache(CatalogItemContext reportContext, ClientRequest session, RenderingResult& result) at Microsoft.ReportingServices.Library.RSService.RenderFromSession(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters) at Microsoft.ReportingServices.Library.RSService.RenderNext(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effecectiveParameters, String[]& secondaryStreamNames) at Microsoft.ReportingServices.Library.RenderNextCancelableStep.Execute() at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper() --- End of inner exception stack trace --- at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper() at Microsoft.ReportingServices.Library.RenderNextCancelableStep.RenderNext(RSService rs, CatalogItemContext reportContext, ClientRequest session, JobType type, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames) at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(HttpResponseStreamFactory streamFactory) at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.DoStreamedOperation(StreamedOperation operation) at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderItem(ItemType itemType) at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent() at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage() w3wp!dbcleanup!9!3/1/2007-22:35:41:: v VERBOSE: Starting database cleanup. w3wp!library!9!3/1/2007-22:35:41:: v VERBOSE: Call to CleanBatch() w3wp!dbcleanup!9!3/1/2007-22:35:41:: v VERBOSE: Cleaning expired sessions from DB w3wp!library!9!3/1/2007-22:35:41:: v VERBOSE: Transaction begin. w3wp!library!9!3/1/2007-22:35:41:: v VERBOSE: Transaction commit. w3wp!dbcleanup!9!3/1/2007-22:35:41:: v VERBOSE: Cleaning expired cache from DB w3wp!library!9!3/1/2007-22:35:41:: v VERBOSE: Transaction begin. w3wp!library!9!3/1/2007-22:35:41:: v VERBOSE: Transaction commit. w3wp!dbcleanup!9!3/1/2007-22:35:41:: v VERBOSE: Cleaning orphaned snapshots from DB w3wp!library!9!3/1/2007-22:35:41:: v VERBOSE: Transaction begin. w3wp!library!9!3/1/2007-22:35:41:: v VERBOSE: Transaction commit. w3wp!library!9!3/1/2007-22:35:41:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams w3wp!dbcleanup!9!3/1/2007-22:35:41:: v VERBOSE: Database cleanup executed.
Hi, I am new to asp.net.I am trying to deploy the website on IIS created in visual web developer 2005 express.I am having problem with the aspnetdb.mdf for login.I have copied my entire contents to the physical directory( shared floder),from where the iis virtual directory can access the contents.I can see my login form but when i enter my username and password and click login it gives my error"error occured in establishing the connection". I am using the sql server 2005.I don't know what should i do with aspnetdb.mdf? thanks and urgent help needed
my problem is , i want to deploy my rdl and rds file which i make in the sql 2005 reporting services. Now i want to deploy this rdl through asp.net coding using language VB i hope someone is help me Thanks Rahul Sinha
DB is developed on local computer with MSSQL 2005 Express. My host is on MSSQL 2005 workgroup. Are they compatible, because I am getting errors? Is my approach wrong?
I have tried several approaches.
A) I created a backup of database on my local, then placed a copy on the server. Then I tried to restore through Server Management Studio. I get this error.
TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3169)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=3169&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
B: I also have tried copying the database. I put it in the same path as the other databases that can be read with server management studio on the server. Then, tried to get to it through server managements studio and it did not appear. So I tried to attach it. Then I received this error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'MROACH1'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Could not open new database 'LodgingDB'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
C: I have also tried opening the Database, and back up file through Server Management Studio. without success.
D: I also tried Windows and Software update at microsoft update, but no updates were recommended for Version on Server.
I'm surprised this is so hard. My original data base was created in same family of software. 2005 MS SQL Express. I could use some direct help from someone experienced with this. Am I doing it wrong or are the DB versions incompatible.
I have two servers with sql server 2005, one is in the main office and the other in a store...
I would like that the items table from the main office send new data to the server at store, and the sales data from the store to be send to the main office, can anyone help me to do that?!
I have a setup where I need to replicate the database which is actually subscribing from another database. The current setup is all in SQL Server 2000. I need to now setup a Distrbutor on a SQL server 2005 and publish the database using this distributor to another server on SQL server 2000.
Has anybody done this before. If yes what will I need to check. Can you please let me know :-
1) SQL Server 2000 which SP should be installed to support this enviroment.
2) SQL Server 2005 which SP should be installed to support this environment.
We are implementing 2005 transaction replication on source database to target staging subscring database but we want to keep all transaction changes from source within staging subscribing tables. If source column gets updated we want to keep old record and new updated record in staging subscriber. Transaction replication synchronizes but does not keep history on subscriber. Do we update stored proc's anyone have examples of code or ideas??
I have a problem configuring Bidirectional replication in SQL Server 2005 SP2. I configured Publication and Subscription on two different SQL 2005 instances on different machines (Station1SQL2005 and Station2SQL2005 respectively). Databases are DBTest1 in Station1 and DBTest2 in Station2. I have two tables one in DBTest1 and the other in DBTest2.
Script for the above configuration:
This below configuration does not work if i configure Publication and Subscription on the same machines
For Station1: IF EXISTS(SELECT * FROM sys.databases WHERE name = 'dbtest1') DROP DATABASE dbtest1;
CREATE DATABASE dbtest1 go
--Create table named two_way_dbtest1 that have an IDENTITY column with the NOT FOR REPLICATION option set USE dbtest1 go
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'two_way_dbtest1') DROP TABLE two_way_dbtest1; GO
CREATE TABLE two_way_dbtest1 ( pkcol INTEGER PRIMARY KEY NOT NULL, intcol INTEGER IDENTITY(1,1) NOT FOR REPLICATION, charcol CHAR(100), timestampcol TIMESTAMP )
/*Allocate a predetermined range of values to the primary key column so that the values on the different servers are not in the same range. For example, you can enforce 1-1000 as the key range for the two_way_dbtest1 table in the dbtest1 database, and then enforce 1001 -2000 as the key range for two_way_dbtest2 table in the dbtest2 database. To do so, use the following code: */ -- Constraint to enforce a range of values between 1 and 1000 in database dbtest1 USE dbtest1 go
ALTER TABLE two_way_dbtest1 WITH NOCHECK ADD CONSTRAINT checkprimcol CHECK NOT FOR REPLICATION ( pkcol BETWEEN 1 AND 1000 ) go
--Enable your server as the distributor, and then create a distribution database --Ensure SQL Server Agent service is running before executing the below statement. USE master go sp_adddistributor @distributor = 'Station1SQL2005' go
--create a distribution database for the distributor USE master go sp_adddistributiondb @database='distribution' go
--Enable the computers running SQL Server that are participating in the replication as publishers USE master go
--Enable the identified databases for replication USE master go
exec sp_replicationdboption N'dbtest1', N'publish', true go
--Create the custom stored procedures in the dbtest1 database USE dbtest1 go
-- INSERT Stored Procedure
CREATE PROCEDURE sp_ins_two_way_dbtest1 @pkcol int, @intcol int, @charcol char(100), @timestampcol timestamp, @rowidcol uniqueidentifier AS INSERT INTO two_way_dbtest1 ( pkcol, intcol, charcol ) VALUES ( @pkcol, @intcol, @charcol ) go
--UPDATE Stored Procedure
CREATE PROCEDURE sp_upd_two_way_dbtest1 @pkcol int, @intcol int, @charcol char(100), @timestampcol timestamp, @rowidcol uniqueidentifier, @old_pkcol int as DECLARE @x int DECLARE @y int DECLARE @z char(100)
SELECT @x=pkcol, @y=intcol, @z=charcol FROM two_way_dbtest1 WHERE pkcol = @pkcol
DELETE two_way_dbtest1 WHERE pkcol=@pkcol
INSERT INTO two_way_dbtest1 ( pkcol, intcol, charcol ) VALUES ( CASE ISNULL(@pkcol,0) WHEN 0 THEN @x ELSE @pkcol END, CASE ISNULL(@intcol,0) WHEN 0 THEN @y ELSE @intcol END, CASE ISNULL(@charcol,'N') WHEN 'N' THEN @z ELSE @charcol END ) go
-- DELETE Stored Procedure
CREATE PROCEDURE sp_del_two_way_dbtest1 @old_pkcol int AS DELETE two_way_dbtest1 WHERE pkcol = @old_pkcol go
--Create a transactional publication, and then add articles to the publication in both the dbtest1 and the dbtest2 databases --In the database dbtest1. USE dbtest1 go
-- Adding the transactional publication. EXEC sp_addpublication @publication = N'two_way_pub_dbtest1', @restricted = N'false', @sync_method = N'native', @repl_freq = N'continuous', @description = N'Transactional publication for database dbtest1.', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran = N'true', @autogen_sync_procs = N'true', --To avoid expiry if there are 5 continuous holidays for a company. If 0, well-known subscriptions --to the publication will never expire and be removed by the Expired Subscription Cleanup Agent. @retention = 120 go
/*In this scenario, the dbtest1 database is the central subscriber. Create transactional subscriptions in the dbtest2 database that subscribe to the publication at dbtest1 and in the dbtest1 database that subscribe to the publication at dbtest2 */ --Create all the subscriptions with the LOOPBACK_DETECTION option enabled --Adding the transactional subscription in dbtest1. USE dbtest1 go EXEC sp_addsubscription @publication = N'two_way_pub_dbtest1', @article = N'all', @subscriber = 'Station2SQL2005', @destination_db = N'dbtest2', @sync_type = N'none', @status = N'active', @update_mode = N'sync tran', @loopback_detection = 'true' go
For Station2: --Create database named test1 IF EXISTS(SELECT * FROM sys.databases WHERE name = 'dbtest2') DROP DATABASE dbtest2 go
CREATE DATABASE dbtest2 go
--Create table named two_way_dbtest1 that have an IDENTITY column with the NOT FOR REPLICATION option set USE dbtest2 go
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'two_way_dbtest2') DROP TABLE two_way_dbtest2; GO
CREATE TABLE two_way_dbtest2 ( pkcol INTEGER PRIMARY KEY NOT NULL, intcol INTEGER IDENTITY(1,1) NOT FOR REPLICATION, charcol CHAR(100), timestampcol TIMESTAMP )
/*Allocate a predetermined range of values to the primary key column so that the values on the different servers are not in the same range. For example, you can enforce 1-1000 as the key range for the two_way_dbtest1 table in the dbtest1 database, and then enforce 1001 -2000 as the key range for two_way_dbtest2 table in the dbtest2 database. To do so, use the following code: */ -- Constraint to enforce a range of values between 1 and 1000 in database dbtest1 USE dbtest2 go
ALTER TABLE two_way_dbtest2 WITH NOCHECK ADD CONSTRAINT checkprimcol CHECK NOT FOR REPLICATION ( pkcol BETWEEN 1 AND 1000 ) go
--Enable your server as the distributor, and then create a distribution database --Ensure SQL Server Agent service is running before executing the below statement. USE master go EXEC sp_adddistributor @distributor = 'Station2SQL2005' go
--create a distribution database for the distributor USE master go sp_adddistributiondb @database='distribution' go
--Enable the computers running SQL Server that are participating in the replication as publishers USE master go
--Enable the identified databases for replication USE master go
exec sp_replicationdboption N'dbtest2', N'publish', true go
--Create the custom stored procedures in the dbtest1 database USE dbtest2 go
-- INSERT Stored Procedure
CREATE PROCEDURE sp_ins_two_way_dbtest2 @pkcol int, @intcol int, @charcol char(100), @timestampcol timestamp, @rowidcol uniqueidentifier AS INSERT INTO two_way_dbtest2 ( pkcol, intcol, charcol ) VALUES ( @pkcol, @intcol, @charcol ) go
--UPDATE Stored Procedure
CREATE PROCEDURE sp_upd_two_way_dbtest2 @pkcol int, @intcol int, @charcol char(100), @timestampcol timestamp, @rowidcol uniqueidentifier, @old_pkcol int as DECLARE @x int DECLARE @y int DECLARE @z char(100)
SELECT @x=pkcol, @y=intcol, @z=charcol FROM two_way_dbtest2 WHERE pkcol = @pkcol
DELETE two_way_dbtest2 WHERE pkcol=@pkcol
INSERT INTO two_way_dbtest2 ( pkcol, intcol, charcol ) VALUES ( CASE ISNULL(@pkcol,0) WHEN 0 THEN @x ELSE @pkcol END, CASE ISNULL(@intcol,0) WHEN 0 THEN @y ELSE @intcol END, CASE ISNULL(@charcol,'N') WHEN 'N' THEN @z ELSE @charcol END ) go
-- DELETE Stored Procedure
CREATE PROCEDURE sp_del_two_way_dbtest2 @old_pkcol int AS DELETE two_way_dbtest2 WHERE pkcol = @old_pkcol go
--Create a transactional publication, and then add articles to the publication in both the dbtest1 and the dbtest2 databases --In the database dbtest1. USE dbtest2 go
-- Adding the transactional publication. EXEC sp_addpublication @publication = N'two_way_pub_dbtest2', @restricted = N'false', @sync_method = N'native', @repl_freq = N'continuous', @description = N'Transactional publication for database dbtest2.', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran = N'true', @autogen_sync_procs = N'true', --To avoid expiry if there are 5 continuous holidays for a company. If 0, well-known subscriptions --to the publication will never expire and be removed by the Expired Subscription Cleanup Agent. @retention = 120 go
/*In this scenario, the dbtest1 database is the central subscriber. Create transactional subscriptions in the dbtest2 database that subscribe to the publication at dbtest1 and in the dbtest1 database that subscribe to the publication at dbtest2 */ --Create all the subscriptions with the LOOPBACK_DETECTION option enabled --Adding the transactional subscription in dbtest1. USE dbtest2 go EXEC sp_addsubscription @publication = N'two_way_pub_dbtest2', @article = N'all', @subscriber = 'Station1SQL2005', @destination_db = N'dbtest1', @sync_type = N'none', @status = N'active', @update_mode = N'sync tran', @loopback_detection = 'true' go
---************************************************************************************************* It would be grateful if somebody gives me a solution.
I read that views can't be published with SQL Server 2005 replication. Is this planned for the future? If not what alternatives are there for this?
Simple collecting the data needed in a new table ain't a solution for us (memory consuming). And joining the data on the PPC ain't a good solution either (memory and time consuming). We only want to pull the data.
Greetings... Presently, I am doing one way replication in SQL Server 2005. Server-A is local server at local place and Server-B is remote server at different place. There is not a problem in one way replication. Server-A is Distributor and Server-B is Subscriber in one way replication. I want to setup the following configuration using bidirectional replication (two way replication) on SQL Server 2005 And I am not able to do it. What should I do for this? Should I use Merge Replication for bidirectional Replication. Server-B is live server for users which cannot be stop for a moment. Server-A is local server which is live too. Now please let me know how to do Bidirectional Replication. So whatever data in Server-B (Which is live) should replicate to Server-A or Vice versa ... If we add some column into Server-B's table of Database what could be the effect on Server-A...
Hello All, I created all the role and logins as described in oracleadmin.sql file and were able to query Oracle tables. But when I try to crate publisher on Oracle server from sqL server I get the following error: ------------------------------------------------------------------------------------------------------------------------------- TITLE: Distributor Properties ------------------------------
An error occurred applying the changes to the Distributor.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server+Management+Studio&ProdVer=9.00.3186.00&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476
SQL Server could not enable 'oracle_dev' as a Publisher. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Failed to execute the HREPL.INITPUBLISHER request to Oracle Publisher 'ORACLE_DEV'. Verify that the Oracle package code exists on the Publisher, and that the replication administrative user account has sufficient permissions. Changed database context to 'master'. OLE DB provider "MSDAORA" for linked server "ORACLE_DEV" returned message "One or more errors occurred during processing of command.". OLE DB provider "MSDAORA" for linked server "ORACLE_DEV" returned message "ORA-06550: line 1, column 8: PLS-00201: identifier 'HREPL.INITPUBLISHER' must be declared ORA-06550: line 1, column 8: PL/SQL: Statement ignored ". Error: 7215, Sev: 17, State: 1, Msg: Could not execute statement on remote server 'ORACLE_DEV'. (Microsoft SQL Server, Error: 21651)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3186&EvtSrc=MSSQLServer&EvtID=21651&LinkId=20476
-------------------------------------------------------------------------------------------------------------------------------------------------------- I searched the web and could not find any related info, where is this package can I run it manually? Any help appreciated.
We are trying to set up the Sql Server CE Server Tools on our Web Server and most of the MSDN support items assume that Sql 2005 is installed on the same PC. However, our Sql 2005 database is stored on a dedicated server off site. When we run the Sql CE ST installation we are tols we cannot sync with Sql 2005 until we install the Replication Components. It seems we cannot download them and they can only be located on the Sql 2005 install CD. Is there another way around this as the Sql SP3a and SP4 dont solve the problem and without having to arrange a courier for the CD we cannot seem to move forward.
im trying to set up a replication from a local server to a remote server.
when starting up and selecting the Distribution / publisher, i get the following errors ---> Details as followed...
TITLE: New Publication Wizard ------------------------------
SQL Server is unable to connect to server 'SERVERX'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2050.00&EvtSrc=Microsoft.SqlServer.Management.UI.PubWizardErrorSR&EvtID=CantConnect&LinkId=20476
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'SERVERY'. (Replication.Utilities)
------------------------------ BUTTONS:
OK ------------------------------ and...
===================================
SQL Server is unable to connect to server 'SERVERX'. (New Publication Wizard)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2050.00&EvtSrc=Microsoft.SqlServer.Management.UI.PubWizardErrorSR&EvtID=CantConnect&LinkId=20476
===================================
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'SERVERY'. (Replication.Utilities)
------------------------------ Program Location:
at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.CheckServerAlias(ServerConnection conn) at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.Open() at Microsoft.SqlServer.Management.UI.CreatePublicationWizard.PrepareToShow()
I have a problem that about the Replication of the SQL SERVER 2005. If there are a hundred data in the delivery of replication ,after they transmited fifty data ,somebody want to read the data in the transmited fifty data that has delivery successfully,but the replication delivery hasn't finished,and could he can read the data successfully? In the delivery process of the replication,could the table in the subscription will be lock?And can we oprate the table in the subscription in the delivery process of the replication ?
I've been trying to get my merge replication to work with a sql ce 2.0 on sql server 2005, but it keeps generating shapshot scripts my pocket pc can't execute.
Example of my table.sch file in my snapshot folder: drop Table [dbo].[Application] go
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Application]( [AppID] [nvarchar](20) NOT NULL, [AddOnInfo] [nvarchar](50) NULL, [MaxClients] [int] NULL, [AppName] [nvarchar](255) NULL, [NbrDaysHistory] [int] NOT NULL CONSTRAINT [DF_Application_NbrDaysHistory] DEFAULT (10), [NbrDaysFuture] [int] NOT NULL CONSTRAINT [DF_Application_NbrDaysFuture] DEFAULT (10), [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF__Applicati__rowgu__4C364F0E] DEFAULT (newid()) )
GO SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
ALTER TABLE [dbo].[Application] ADD CONSTRAINT [Application_PK] PRIMARY KEY CLUSTERED ( [AppID] ) GO
I've selected that I was setting up a merge replication that needs to be compatible with sql ce. But it doesn't seem to do this...
I've tried to set the compatibility level to 80SP3 manually but it remains 80RTM... I've tried everything.....
Does anyone have any ideas what could be causing this?
If I remove these lines from the sch file everything works jsut fine:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
ALTER TABLE [dbo].[Application] ADD CONSTRAINT [Application_PK] PRIMARY KEY CLUSTERED ( [AppID] ) GO
Even if I make a publication for sql mobile 2005 it generates the same script.... It's almost as if SQL 2005 is ignoring my settings...
