My replication is failing to apply the initial snapshot because of the issue with QUOTED identifier. The snapshot files for the stored procedure are being generated using the 'SET QUOTED IDENTIFIER ON' where as my store procedure code is using the double quotes for string comparision e.g if @val = "test".
It is not possible for me to change the sps code as there are 1000+ sps exists. Is there any way to generate the snapshot files with 'SET QUOTED IDENTIFIER OFF'
I have a "merge" publication created, with only Procedures, Views and Functions. (Note I have other merge publications that replicate the tables from the same database). I keep getting the same error (see -below) on various procedures. I cannot find anything wrong with the procedures themselves. I also checked the offending procedure by removing it from the publication and compiling it in the database...it works fine. None of my other publications (all tables) encounter this error. * I have 3 others.
My version of SQL is 9.00.1399.06
Here is the message I am getting. Any help would be greatly appreciated. Note I changed the proc name due to client restrictions.
Message: StartIndex cannot be less than zero. Parameter name: startIndex Command Text: select 'number' = convert(int, 0), 'definition' = definition from sys.sql_modules where object_id = object_id(@qualified_object_name) union all select 'number' = convert(int, procedure_number), 'definition' = definition from sys.numbered_procedures where object_id = object_id(@qualified_object_name) Parameters: @qualified_object_name = [dbo].[pra_merge]
Stack: at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior) at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, CommandBehavior commandBehavior) at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.TextModeOnObjectScripter.Script() at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateNonTableArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, SqlSmoObject smoObject, Boolean quotedIdentifierOn, Boolean ansiNullsOn, Boolean textMode) at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateStoredProcedureArticleScripts(ArticleScriptingBundle articleScriptingBundle) at Microsoft.SqlServer.Replication.Snapshot.MergeSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle) at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle) at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting() at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting() at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoScripting() at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot() at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun() at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 52006) Get help: http://help/52006
Source: mscorlib Target Site: System.Text.StringBuilder Remove(Int32, Int32) Message: StartIndex cannot be less than zero. Parameter name: startIndex Stack: at System.Text.StringBuilder.Remove(Int32 startIndex, Int32 length) at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.TextModeOnObjectScripter.ProcessGetObjectScriptResult(SqlDataReader dataReader) at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior) (Source: mscorlib, Error number: 0)
Replication is failing while applying the snapshot as the stored procs are out of sync ( the objects referenced in the stored procedures are no more exists in the database) . Is there any easy way to identify the out of sync procedures so that I can exclude these stored procedures from the articles list. I am having around 1000 procs and is not possible to test them by executing.
drop table ##temp1 --**********************************
When you receive an email and double click on te attachment, it will launch excel automatically and put 'Erin' and 'Brockowich' in seperate columns. which is good and that is the way I want it.
But if I run this code on sql 2000 server, it will generate a file with 'csv' extension unrecognizable by excel. If you open this 2000 attachment in notepad, you can see that the data looks like "Erin, Brockowich" (vs Erin, Brockowich without quotes on 7.0 server ), no wonder it is unrecognizable by excel.
I have set quoted_identifier off while compiling all the user sps on 2000 server. But while sending emails from within the procedure, all the attachments still generated with quotes.
How can I get rid of the quotes? We have at least 45 routines running generating coma seperated files as a result of the query and sending emails to the clients for years. Now all of a sudden all the routines got messed up with 2000 upgrade.
I have existing table which is having Set Quoted Identifier Off and Set Ansi Null
Now I want to change those setting so Is there any alter statement for the same?
Also Let's say At my database level If those settings are off and If I convert it to ON then It is not taking effect on existing tables SP which are already build.
Create jobs to copy database and restore database in destination servers
------------ Robert at 5/7/2002 11:00:30 AM
Yes and I would rather not use dts to accomplish this task.
------------ Ray Miao at 5/7/2002 10:02:15 AM
Do you have direct network connection to remote server? Did you try dts?
------------ Robert at 5/7/2002 9:08:06 AM
I've been trying to replicate a database to an off site server using snapshot replication. It is scheduled to run every hour but I've noticed when data is changed at the source it never gets replicated to the destination. Does anyone know why?? I can't use transactional replication beause not all the tables have primary keys and they can't be added due to code. Some tables have id colunms and have been created with the Not for Replication option on the subscriber. Any help will be appreciated.
I can set up snapshot replication for those tables without foreign key constraints. But if there are foreign keys in the table, there will be error message indicating that this object can not be dropped because it is referenced by ....
1) In snapshot replication, can the subsciber send info back to the publisher (even in a manual process)
2) In snapshot replication, do we need a distributor set up between the publisher and subscriber if there will only be a single subscriber, or can we write directly to it?
We have a production server in East Coast (SQL Server 2000 SP2 - Database size is around 30 Gig). We have a reporting server is the West Coast. We need to replicate (transactional replication every one hour) from East coast to West coast. Is there any way that I can take a backup and restore upto the last transaction backup and then start replication agent on the production (by saying schema and data already exist). Basically we don't wan't to snapshot using FTP or bcp through WAN because it is going to be very slow.
If this is possible, will there be any validation problem.
Suppose i want to replicate data from server A to server B I am using snaphot replication.I did the snapshot replication for the first time and server B got a snapshot of server A.
Next time i run snapshot i want the incremental data to be replicated and not all..Is this possible in snapshot replication? If not which type of replication should i use?
Hi All,I have set up a snapshot replication, and schedule it to run everynight. The snapshot run successfully, and data get replicated to thesubscribed server. However, data do not get transfer as the second dayand there after. I check the job history, the job (distribution) runsuccessfully. I start the snapshot agent again, then data gettransferred. I can schedule the snapshot agent to run every night, butthis is just not the way it's supposed to be. Is there anyone out therecan give me some help. Thanks.
I am using SQL SERVER 2005 snapshot wizard to create snapshot. But as soon as i create a snapshot it takes away all the indexes and constraint for the tabels on the subscription end although i have this indexes and constraint on Publisher side.
Can someone help me in finding if their is some setting to create a snapshot without losing indexes and constraints or if their is some other way to do this.
Hi is that possible to configure replication in the following situation.
server A is built by snap of server B. Because i am able to create publisher on server B but i am unable to create same on server A.
could any one explain the situation, why it is happening this way.
any help is appriciated.
if you are curious what kind of error i got.
TITLE: Publisher Properties
------------------------------
An error occurred connecting to Publisher 'SERVER A'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.PubshrPropertiesErrorSR&EvtID=CantConnectToPublisher&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
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, 'SERVER B'. (Replication.Utilities)
I am looking forward at performing a SNAPSHOT REPLICATION between a 2000-Publisher and a 2005-Subscriber. But on following the wizard the latter is unable to recognize the publisher.
Could anyone help to let me know the possible reason or if there is some setting which i may have missed out?
We encounter a difficult problem in our production environment.
I have a merge replicaiton which contains 10 tables from Server s1 and database d1 to Server s2 and database d2. Among those 10 tables 5 of them are true merge tables ( carry data both ways, from publisher to subscriber and from subscriber to publisher). Other 5 tables I would like them to carry data only one way from publisher to subscriber ( never merge data from subscriber). Anyone has any suggestions how I impliment this?
We are using Snapshot replication to move data for our Data Warehouse from a server which performs the loading to the production reporting server. We are currently using Sql Server 2005 in both environments. The loading server is setup as the Distributor and Publisher and the production reporting server is setup as the subscriber. However, the replication is not automated instead we only want it to run if the load process is successful. To do this we did not set a schedule for replication but added the jobs that the Replication Wizard created to our own Sql Agent job which manages the load. I have listed the below commands that the sql agent is running for replication in the order in which they are ran.
In our own job we run each of these jobs using the EXEC SP_START_JOB() procedure. Each job listed above runs under their own PROXY account.
Finally, we are experiencing an intermittent problem where the Agent will report it is unable to Copy data into "X" table, with "X" being any one of the tables in the reporting database. This has only ever occurred during the automation process and not from manually running each agent job. This problem also might not occur for a couple of days and then might occur 2 or 3 days in a row, which has led us to question the dependability of this approach.
In closing I would like to ask if there is a problem with the way we have set it up? Something we could do to improve the process so it becomes more reliable? Any advice would be greatly appreciated.
Is there anyway to prevent deadlocks during the snapshot replication?
I understand that you can minimize by maybe creating a couple different snapshots (mixing tables to minimize locking while snapshot is being created), but is there any other way?
I configured the transactional replication using push method. I use the option generate snapshot. Until the Snapshot complete, I didn't any tables in the subscriber db.After that also I can see in some tables it has indexes and some tables it doesn't but it has data in that table .
How does the bulk copy works from snapshot?i.e. is it first create the table with columns then insert data later it will create the indexes...How the sequence works in the subscriber.
Hello, Harry Half wit here!! I know that snapshot replication is the simplest form of syncing with SQL server and I can't even figure that out today!!. I keep getting myself confused as to who should be configured to be a publisher, distibutor or subscriber etc etc. My scenario is simple: 1 server creating a daily snapshot of a table and then 1 remote laptop (msde) pulls the snapshot into it's own database. Heres what i did so far; I configured the server to be a publisher and distributor (is that right?) and didn't set up any subscribers because i want to do that from my remote. From the remote I did nothing but go into EM tools"create new pull subscription" but I cannot see the publication on the server.
SHould I set my remote to be a distributor to do this?
Snapshot replication. View synchronization status. Last status message: "The job failed. The Job was invoked by User distributor_admin. The last step to run was step 3 (Detect nonlogged agent shutdown.)."
What should I do to make the synchronization work?
I have two clusters and one publishes snapshots and the other cluster subscribes. I am planning an SP2 installation but I see in the SP2 readme that there are only special considerations for merge and transaction replication.
Will I need to patch in a certain order and will it matter if I patch the publisher who is also the distributor and then a week later patch the subscriber?
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_ComputerHeartbeat", or the name is ambiguous. (Source: MSSQLServer, Error number: 4121) Get help: http://help/4121
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_ComputerHeartbeat", or the name is ambiguous. (Source: MSSQLServer, Error number: 4121) Get help: http://help/4121
Unfortunately I can't find much help with this error.
What I'm trying to do is to create a snapshot replication where it is pushed from the publication server. I have it kinda working but my problem is that the subscription server is not grabbing the tables its asking for. It only grabs some replication tables.
I have noticed two things.
First: When I go to Job syncrinization progress it states that its still in progress even though the replicated database is Very small.
Second: In replication Monitor under the subscription watch list tab it states that the subscription has never been initialized.
We have a database which was replicated with merge replication. we added a few table and a field to another. When I try to create the snapshot I get:
An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.
I have detached and re-attached the db, removed all triggers from the tables, and still I cannot recreate the snapshot the last step before the error as listed in the monitor is generating all procedures for all table articles 49%.
I've set up Transactional replication Pushed from SQL 7 to SQL 2000. All works fine accept I have noticed that the snapshot agent is running once a week and storing a lot of data I don't really need in c:msql7 epldataunc eplicationnamedatastamp. This is gradually building up and as C is a smallish partition it'll only survive for another week.
My questions are: 1) Can the Snapshot schedule be disabled? I'm under the impression that it's only required when a new subscriber comes along which is never! If I do need a new subscriber I could just manually produce another snapshot.
2) Can I delete the previous snapshot folders under ReplData? On a development server I renamed one of the folders and the ground didn't open up below my feet.
3) What effect would changing the working folder used by the distribution agen have, the distributer is on the publisher (which as I mentioned is pushing) I've tried changing the working folder, on a development server, I manually ran a snapshot which whose BCP files all ended up in my new location and the Transactional Replication didn't seem to care.
I've looked in a few places for a difinitive answer and I'm not having much luck so any help (preferably before saturday) would be greatly appreciated.
If I create a merge replication snapshot for database A today and I subscribe to the servers on Monday, will the changes made to database A from the time I created the snapshot be applied to the other servers once i subscribe to them
I am in the process of setting up replication between two SQL boxes - the first being the production SQL Server and the second being a secondary server which is to be used as part of a web portal solution. (In fact there are two pairs of SQL boxes - SQL 7 and SQL 2k, one of each on the LAN and one of each at the hosting site.)
We need to have near realtime replication, so transactional replication is the desired mechanism. The trouble is that not all tables have primary keys defined. Oh yes, and the main database is 20G in size and has >900 user tables! :-0 (The other databases are much better behaved!)
When I set up transactional replication, I am allowed only to include tables which have PKs defined. AFAIK, the initial transactional replication snapshot will also only include these same tables.
If I set up snapshot replication (separately), I can include all the tables in the database. However, I cannot then replicate in real time.
Can I combine the two replication schemes to deliver updates to the same target database: - transactional replication delivering realtime updates to the tables with PKs during the day and - snapshot replication updating all the tables once per 24hrs at night?
Or is there a better way of doing this?
I am not sure whether I can modify the existing schemas, as some of the databases are 'maintained' by an external provider. Even if I could, if I had to add a column to have a PK, I would potentially be adding to my diskspace requirement rather significantly...
