Replication Snapshot Failing - QUOTED IDENTIFIER
Sep 9, 2007
Hi,
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'
View 1 Replies
ADVERTISEMENT
May 14, 2003
During Index Rebuild, I get the following error:
"DBCC failed because the following set options have incorrect settings: 'Quoted_Identifier'"
Any ideas what might be causing this?
Thanks,
Ken Nicholson
View 8 Replies
View Related
Dec 14, 2006
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)
View 8 Replies
View Related
Aug 28, 2007
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.
Thanks in advance.
View 1 Replies
View Related
Jan 16, 2003
Hi,
If you run the following code on 7.0 server, it will send a test file in csv format.
--*********************************
create table ##temp1
(tmp varchar(15))
insert into ##temp1
select 'Erin, Brockowich'
declare @cmd varchar(255)
select @cmd = 'select * from ##temp1'
EXEC master..xp_sendmail
@recipients = '<email>', --change to your email
@query = @cmd,
@subject = 'test-test',
@attach_results = 'true',
@attachments = 'test.csv',
@width = 800
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.
Can you suggest any work around?
Thanks,
Shaili
:confused:
View 1 Replies
View Related
May 27, 2015
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.
View 4 Replies
View Related
May 26, 2015
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies
View Related
May 7, 2002
The database will grow to 40GB in a short while and I intend to schedule replication every hour. Can this not be done using snapshot replication??
Subject:
From:
Date: Snapshot Replication - Help!! (reply)
MAk (mak_999@yahoo.com)
5/7/2002 12:02:08 PM
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.
Thanks
Robert
View 1 Replies
View Related
Aug 27, 2001
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 ....
Do you have any remedy for that? Thanks
View 1 Replies
View Related
Dec 28, 2000
2 questions:
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?
Thanks so much for any and all help!
View 1 Replies
View Related
Jan 13, 2003
Hi Guys,
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.
Please help.
Thanks,
Anu
View 3 Replies
View Related
Apr 13, 2006
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?
View 2 Replies
View Related
Jul 23, 2005
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.
View 1 Replies
View Related
Sep 28, 2006
Hi,
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.
Thanks,
Prashant
View 5 Replies
View Related
Apr 3, 2007
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)
------------------------------
BUTTONS:
OK
View 1 Replies
View Related
Aug 7, 2006
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?
Thanks,
Varun
View 1 Replies
View Related
May 9, 2001
Hi Everybody:
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?
Thanks in advance.
Joan
View 4 Replies
View Related
Nov 14, 2000
I get the following error:
Another snapshot is running for the publication.
I just upgraded to Service pack2 and can not get the replication working. I have not clue what is wrong.
Thanks in Advance,
Phillip M. Tricoli
View 1 Replies
View Related
Feb 18, 2007
Hello,
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.
1) -Publisher [SRVLOADER] -PublisherDB [PRESENTATION] -Distributor [SRVLOADER] -Publication [SRVREPORTER] -DistributorSecurityMode 1
2)-Subscriber [SRVREPORTER] -SubscriberDB [PRESENTATION] -Publisher [SRVLOADER] -Distributor [SRVLOADER] -DistributorSecurityMode 1 -Publication [SRVREPORTER] -PublisherDB [PRESENTATION]
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.
View 5 Replies
View Related
Feb 4, 2004
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?
Thank you
View 1 Replies
View Related
Jun 12, 2015
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.
View 1 Replies
View Related
Jan 4, 2006
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?
any help very much appreciated!!
View 1 Replies
View Related
Aug 23, 2007
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?
View 1 Replies
View Related
Aug 23, 2007
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?
Thanks
View 1 Replies
View Related
Jul 28, 2006
When configuring snapshot replication of the OnePoint database used by MOM i am having the following error being reported in SQL Replication Monitor:
Command attempted:
CREATE TABLE "dbo"."Computer"(
"idComputer" "uniqueidentifier" ROWGUIDCOL NOT NULL,
"Name" "nvarchar"(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
"Description" "nvarchar"(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
"CustomData1" "nvarchar"(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
"CustomData2" "nvarchar"(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
"Domain" "nvarchar"(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
"DNSName" "nvarchar"(512) COLLATE SQL_Latin1_General_CP1
(Transaction sequence number: 0x000004670002E32300BF00000002, Command ID: 375)
Error messages:
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.
Any suggestions?
View 4 Replies
View Related
Apr 9, 2007
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.
Thanks,
Kraig
View 3 Replies
View Related
Jun 3, 2015
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%.
View 9 Replies
View Related
Sep 6, 2006
When I try to drop the publication in this order:
exec sp_dropsubscription @publication = @publicName, @subscriber = @servName, @article = N'all'
exec sp_droppublication @publication = @publicName
I am getting the following error after dropping the publication
Msg 16943, Level 16, State 4, Procedure sp_MSrepl_changesubstatus, Line 1271
Could not complete cursor operation because the table schema changed after the cursor was declared.
Msg 16943, Level 16, State 4, Procedure sp_MSrepl_changesubstatus, Line 1271
Could not complete cursor operation because the table schema changed after the cursor was declared.
Could anyone give me a suggestion about how to avoid this error?
Thanks!
View 5 Replies
View Related
Mar 31, 2006
hi all,
I grouped my tables into two publications
To each publication i added my "datamart1" server as a subcriber
using the same database as a destination
I dont have problem when i create snapshot since each of the publication
generates its own snapshot.
The only problem i encounter is that. when i begin to sychronize any one of the two
subscription The two subscription gets synchronized.
I need to isolate each publication's syncronization to know exactly how much time would each publication take to get synchronized in the datamart
thanks,
joey
View 1 Replies
View Related
Aug 22, 2006
Hi,
I have got a snapshot replication and have got distributor, publisher and subscriber on different servers all having sql server 2005.
I am looking for a piece of code that i can execute in order to initialize snapshot with new data and push data to subscriber on demand.
I assume there are TSQL calling replication sproc but i m not sure which proc should be used. any help would be highly appriciated.
Thanks,
Furrukh Baig
View 6 Replies
View Related
May 21, 2002
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.
Regards.
View 1 Replies
View Related
Feb 16, 2001
Hi:
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
Thanks, Jay
View 1 Replies
View Related
Feb 2, 2004
Hi
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...
TIA
View 2 Replies
View Related
