I have an existing publication and I want to modify an index on my main server. I do not want the index to be pushed out to the other server, but I'm wondering if the index will break replication.Any thoughts?
View 1 RepliesHi
View 3 Replies View RelatedI have an existing publication in sql 2012 with 2 articles, and then I add 2 more articles. After that when I generate a snapshot, will the snapshot be generated for 2 new articles only or for all 4 articles?
I remember adding 1 new articles to one existing publication with 150 articles and when I generated snapshot, it was generated only for 1 article. But I don't remember clearly.
Does it behave differently for small and large number of articles?
-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
First of all; My Oracle publication works fine when I don't explicit specify the shema_option parameter for the articles I'm adding to the publication. The reason why I then want to explicit specify the parameter is as following.
I'm developing a replication solution to get data from our production server (Oracle) to our Data Warehouse (SQL Server). The SQL Server (and the Data Warehouse code) uses the SQL_Latin1_General_CP1_CI_AS collation. When I don't explicit specify the schema_option, the nvarchar columns of the replicated tables are created using the SQL_Latin1_General_CP1_CS_AS collation and this results in some comparison errors, when for instance a select statement is trying to compare two nvarchar strings using different collations.
I've tried to specify the schema_option parameter as "@schema_option = 0x80" (Replicates primary key constraints.) to avoid the use of the SQL_Latin1_General_CP1_CS_AS collation when creating the destination tables - I'm not sure it's enough? No matter what, I'm getting an error when I'm doing it (see below).
Message
2006-07-13 12:00:15.529 Applied script 'ITEMTRANSLATION_2.sch'
2006-07-13 12:00:15.544 Bulk copying data into table 'ITEMTRANSLATION'
2006-07-13 12:00:15.544 Agent message code 20037. The process could not bulk copy into table '"ITEMTRANSLATION"'.
2006-07-13 12:00:15.591 Category:NULL
Source: Microsoft SQL Native Client
Number: 208
Message: Invalid object name 'ITEMTRANSLATION'.
2006-07-13 12:00:15.591 Category:NULL
Source:
Number: 20253
The questions are now whether I actually have a schema_option alternative for Oracle Publishing? If so, what is the solution, and eventually how can I avoid the error stated above?
If I'm not able to avoid the article columns getting created with the "wrong" collation, is there then any other obviously solution to the problem?
Thanks!
Best regards,
JB
CREATE PROCEDURE [dbo].[WWspLoadData]
@Planid varchar(5), @OverMar int = Null
need to check here if the @PlainID exists in the tbOldCustomers.PlainID
if it does exists then do nothing and exit the store procedure, and if it does not exists continue
to the rest of the store procedure.
Is there a way that upon exiting VSS I can be warned if I have programs checked-out, so that I don't leave for vactaion with an important program checked-out?
Thanks!
Hello,
I have been writing this piece of software for quite a while. And as it got bigger and bigger (more forms, more datasets) I started experiencing the following problem.
Well, I use an MDF file as a database container that gets copied to the destination directory after each build.
Well here is what happens:
I launch my application, work with it, search the database and so on and so on.
Then I exit the application.
I launch it again and I get en exception saying that file counld not be located on the disk.
I close the app, and run it again. I get the same thing.
Somtimes after a couple of tries I get an error saying that my user does not have rights to access the default database. (I have windows authentication on SQL server)
Now here is the trick: I open SQL Server Management Studio Express detatch (Delete) the database from the default server (the only one in the system ".SQLEXPRESS") and the application runs again.
What am I doing wrong? How can I programatically detatch the database from the server (Maybe some sql command)? Do I need it though?
This is the connection string that I use for connection:
Code Snippet
Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyAppDB.mdf;Integrated Security=True;Connect Timeout=10;User Instance=False
But the confusing part of it is that it happend on my work computer and not on my home computer. I have the same User/Password/Workgroup/VS version/MS SQL versoion/Windows version on both machines.
Thank you.
If I add a nonclustered index to an existing table, does it take the table offline while creating the index?
View 2 Replies View RelatedHi allI recently noticed when trying to optimise a major query of a chess websiteI am the webmaster of, that adding an order by for "gamenumber" which is aclustered index field as in for example "order by timeleft desc, gamenumberdesc" actually speeded up the queries and reduced sql server 2000 timeouts.I have an ASP error log and I am fairly sure that a dramatic reduction insql server timeouts is simply attributed to adding an extra seeminglyredundant order by field - which is the clustered index. Is this phenomenaat all possible or is it my imagination?!Other special attributes of the query includes the use of "Top" to obtain amaximum specified number of rows. Perhaps it is just the uniquecharacteristics of the query, but I would have thought that the less orderby fields would imply faster performance. Has anyone else noticed that aseemingly redundant order by column on for example the clustered indexcolumn, can actually help speed up queries?!Best wishesTryfon GavrielWebmasterwww.chessworld.net
View 4 Replies View RelatedHere is the DDL of a table that I am trying to add an identity column and create a clustered index. I am using SQL 2012 SE
CREATE TABLE [dbo].[IPF](
[IPFId] [uniqueidentifier] NOT NULL,
[IPId] [uniqueidentifier] NOT NULL,
[FId] [uniqueidentifier] NULL,
[FName] [nvarchar](50) NULL,
[FItemId] [uniqueidentifier] NULL,
[code]....
The table currently has 220million rows. I am trying to add a new identity new column and create a clustered index on it. I ran the script and its been more than 26hrs and its still running. I ensured there is no blocking.
I have an SSIS package that is pretty simple.
An Execute SQL task returns one row with two values that are correctly stored into variables.
Based off those two variables, a sequence container is chosen to execute.
That sequence container then does magic. The last step of the container has an execute SQL task that runs and stores the result in a variable - let's call this[User::result] with type Int32.
Based off the value in [User::result] one of two final tasks run. A non zero value executes one task; a zero value executes another. Basically,SOMETHING runs after this.
Simple? I thought so... except Step 4 doesn't work. If I set a post-execute breakpoint on the sequence container, the variables are populated as expected, but nothing else runs. If I add another task to run (without a conditional expression) to run after the sequence container completes, the pre-execute breakpoint shows the data looking exactly as I expect.
Those script tasks are just MessageBox.Show calls and the expression, as you can see, doesn't use variables at all.
We have implemented a very small reporting database which has a main table that started off small and has now grown to around half a million rows. Initially, there were no indexes on the table apart from a clustered index, but as the data has grown, performance has dropped and so we have added a number of indexes. This has resolved the performance issues.
Before creating the indexes SQL Server had auto created a number of statistic objects (_WA_Sys_000... etc). After creating the indexes, new statistic objects where created for the new indexes. In some cases, there are duplicate statistics (auto and index) for the same columns.Should I go through and drop the duplicate auto statistics? Will having duplicates cause issues at all?
Hi, everyone,
I am new in SQL server 2005. I had setup SQL server 2005 P2P replication. Somehow it did not work one of two way replication. I tried to delete the publication. However I could not do it. have the same problem. When I tried to delete the publication, I got the publication " " does not exist.[SQL server error: 20026]. I tried to use sp_droppublication, it gave me error "the database is not enabled for publication". Nevertheless, I can see the publication in MS SQL Management Studio and Publication monitor with OK status. I could not find the distribution database either.
Could you anyone has ideas to delete this publication? I am sorry I am not a programmer. Please give me more detail explanation if you can. Thanks.
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
thank you in advance
Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.
View 7 Replies View RelatedI am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
I have a publication on my SQL Server.
The first time my application syncs with the server it works fine.
If I try to sync again I get "Permission Denied Number 80040E09 NativeErr: 0"
Other publications work fine, it just seems to be this one.
Any ideas?
Hi!
I have a strange error, when I try to create publication
Msg 3933, Level 16, State 1, Procedure sp_MSrepl_addpublication, Line 1222
Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.
What is it?
P. S. The error arise with all types of replication.
Dear All.
We had Teradata 4700 SMP. We have moved data from TD to MS_SQL SERVER 2003. records are 19.65 Millions.
table is >> Order_Dtl
Columns are:-
Client_ID varchar 10
Order_ID varchar 50
Order_Sub_ID decimal
.....
...
..
.
Pk is (ClientID+OrderId+OrderSubID)
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
I am thank u to all who read or reply.
Arshad
Manager Database
Esoulconsultancy.com
(Teradata Master)
10g OCP
My SSIS package is running very slow taking so much time to execute, One task is taking 2hr for inserting 100k records, i have disabled unused index still it is taking time.I am rebuilding/Refreshing indexes and stats once in month if i try to execute on daily basis will it improve my SSIS Package performance?
View 2 Replies View RelatedKeep getting this error when positioning to the last page of a report.
Using Server 2003...SqlRpt Svcs 2000 sp2
Detail error msg:
Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. (rrRenderingError) Get Online Help
Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown.
Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index
Anyone have any suggestions? Any way to find out what collection is blowing?...or where parameter name: index comes from?
Hello ,
I have a problem with a publication which is a transactional replication on SQL Server 7.0 .
I want to add stored procedure to the existing publication along with the tables . But the Enterprise manager does not allow to select or check the box
against the required stored procedure although it displays when i right click on the publication and select proeprties .
Can i have some help in this regard ? Does anybody knows to add a stored procedure by means of a query .
Is there any other way to modify publication in SQL Server 7.0 although it is possible in SQL Server 2000 .?
Many thanks in advance.
Sandra .
Because of problems trying to alter databases used
for replication my software would need to find out
if a database is a publisher or was repliated (using
T-SQL). Is this possible?
Hi,
I'm use MSSQL2000 SP3 with replication.
I want to know that Can i add new article to existing publication by
Enterprice Manager ?
someone told me that we can do that in older version but not now,right? :confused:
Hi guys.
i had configured a merge replication betwen 2 server and i was running fine. but due to network problems my network admin has changed the ip address and name of the publisher, distributor and subscriber.
what i did. i just drop the publication and now i m trying to create a new one. but i cannt' able to create. i dont' know y.. this is the error i m facing..
Msg 208, Level 16, State 1, Procedure sp_addmergepublication, Line 239
Invalid object name 'dbo.sysmergepublications'.
Msg 21040, Level 11, State 1, Procedure sp_MSaddmergepub_snapshot, Line 65
Publication 'DIS_MASTER_Merge' does not exist.
Msg 20026, Level 16, State 1, Procedure sp_grant_publication_access, Line 176
The publication 'DIS_MASTER_Merge' does not exist.
Msg 20026, Level 16, State 1, Procedure sp_grant_publication_access, Line 176
The publication 'DIS_MASTER_Merge' does not exist.
Msg 20026, Level 16, State 1, Procedure sp_grant_publication_access, Line 176
The publication 'DIS_MASTER_Merge' does not exist.
Msg 20026, Level 16, State 1, Procedure sp_grant_publication_access, Line 176
The publication 'DIS_MASTER_Merge' does not exist.
Msg 20026, Level 16, State 1, Procedure sp_grant_publication_access, Line 176
The publication 'DIS_MASTER_Merge' does not exist.
Msg 208, Level 16, State 1, Procedure sp_changemergepublication, Line 205
Invalid object name 'dbo.sysmergepublications'.
I have been wrestling with this one all day and cannot find anything on MSDN, google, etc.... I have configured replication, but cannot create a new publication either programmatically or using the wizard.
I programmatically did the following:
1. I set up my local server as a distributor, and created a distribution database (@distributor set to server sysname)
execute sp_adddistributor @distributor = @distributor
execute sp_adddistributiondb @database = @distributionDB, @security_mode=1;
2. I registered my local server also as a Publisher (@publisher set to server sysname, @distribution = 'distributor')
SELECT @sql = 'sp_adddistpublisher @publisher = ''' + @publisher +
''', @distribution_db = ''' + @distributionDB +
''', @working_directory = ''' + @directory + ''', @security_mode = 1'
SELECT @sp_executesql = quotename(@distributionDB) + '..sp_executesql '
EXEC @sp_executesql @sql
3. I enabled my local database for transactional replication (@replicationDB)
exec sp_replicationdboption
@dbname = @replicationDB,
@optname = 'publish', @value = 'true
4. I added a logReaderAgent on the ReplicationDB
SELECT @sql = 'sp_addlogreader_agent @publisher_security_mode = 1'
SELECT @sp_executesql = quotename(@replicationDB) + '..sp_executesql
EXEC @sp_executesql @sql
All is well up to this point, no problems, all looks great. When I check the status of things using sp_help repl stored procedures to check on publisher, distributor all looks healthy. Also looks good when I use the SSMS replication interface to check on distributor, publisher, etc. . HOWEVER...... I cannot create any publications.
I tried programmatically, and also by using the wizard.
I keep getting the same error :'Cannot insert the value NULL into column 'pubid', table'.
This is the sql I try to run:
EXEC sp_addpublication
@publication = 'test3333',
@status = N'active',
@allow_push = N'true',
@allow_pull = N'true'
This is the error:
Msg 515, Level 16, State 2, Procedure sp_MSrepl_addpublication, Line 1320
Cannot insert the value NULL into column 'pubid', table 'CypressMaster.dbo.IHpublications'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 14018, Level 16, State 1, Procedure sp_MSrepl_addpublication, Line 1348
Could not create the publication.
If I use the wizard instead of trying to create the publication programmatically, I get this error (just about the same content, but a little different wording):
Creating Publication
- Creating Publication 'NewPublication' (Error)
Messages
* SQL Server could not create publication 'NewPublication'. (New Publication Wizard)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot insert the value NULL into column 'pubid', table 'CypressMaster.dbo.IHpublications'; column does not allow nulls. INSERT fails.
Could not create the publication.
Object 'NewPublication' does not exist or is not a valid object for this operation.
Changed database context to 'CypressMaster'.
The statement has been terminated. (Microsoft SQL Server, Error: 515)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=515&LinkId=20476
- Adding articles (Stopped)
- Starting the Snapshot Agent (Stopped)
- Generating a script file (Stopped)
hi
I was following the walkthrough "Creating a Mobile Application with SQL Server Mobile" and when I got to the point where you create a "local publication" I couldn't find the link "Local Publication" in my Object Explorer.
I read all the help in books online however id did not tell me how to bring that link there.
I did install the replication component using the CD installation. I have SQL Server 2005 Standard Edition and Visual Studio 2005
I also found the help "Using the Publication Wizard to Create a Publication" but did not know where to locate or start the wizard.
any help will be appreciated.
When go to set up a new publication I get the below error:
TITLE: New Publication Wizard
------------------------------
SQL Server is unable to connect to server 'LEICS-DC1'.
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
------------------------------
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, ''. (Replication.Utilities)
------------------------------
BUTTONS:
OK
------------------------------
I am specfing the server name but it still brings back the same error, the message at the bottom also stats to try and connect to '' which of course is impossible.
This is a domain controller if that makes any difference, SQL 2005 SP2, Win 2003 STD.
Thanks
Hi,
I've come across this one before in one of the threads, unfortunately for me the author managed to figure it out for his/her self and there for no answer was posted, so can anybody help PLEASE???
Creating Publication
- Creating Publication 'SQLMobile' (Error)
Messages
* SQL Server could not create publication 'SQLMobile'. (New Publication Wizard)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Invalid object name 'dbo.sysmergepublications'.
Publication 'SQLMobile' does not exist.
Changed database context to 'SQLMobile'. (Microsoft SQL Server, Error: 208)
Its been created via UI
on SQL2005
the distributor is correctly installed
i also have not tried creating a publication on any other database.....this is the first.
Any Ideas?
hello friends
i have table1 and 200 coulumn of table1 :) i have 647.600 records. i entered my records to table1 with for step to code lines in one day :)
i select category1 category2 and category3 with select code but i have just one index.. it is productnumber and it is primarykey..So my select code lines is so slow.. it is 7-9 second.. how can i select in 0.1 second ? Should i create index for category1 and category2 and category3 ? But i dont know create index.. My select code lines is below.. Could you learn me and show me index for it ?? or Could you learn me and show me fast Select code lines and index or etc ??? Also my search code line have a dangerous releated to attaching table1 with hackers :)
cheersi send 3 value of treview1 node and childnode and child.childnode to below page.aspx :)
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
If Request("TextBox1") IsNot Nothing ThenTextBox1.Text = Request("TextBox1")
End If
If Request("TextBox2") IsNot Nothing ThenTextBox2.Text = Request("TextBox2")
End If
If Request("TextBox3") IsNot Nothing ThenTextBox3.Text = Request("TextBox3")
End If
End If
Dim searchword As String
If Request("TextBox3") = "" And Request("TextBox2") = "" Then
searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "'"
End If
If Request("TextBox3") = "" Then
searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "'"
End If
If Request("TextBox3") <> "" And Request("TextBox2") <> "" And Request("TextBox1") <> "" Then
searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "' and kategori2= '" & Request("TextBox3") & "'"
End If
SqlDataSource1.SelectCommand = searchword
End Sub
Hi,
I'm running a merge replication on a sql2k machine to 6 sql2k subscribers.
Since a few day's only one of the merge agents fail's with the following error:
The merge process could not retrieve generation information at the 'Subscriber'.
The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'.
All DBCC CHECKDB command's return 0 errors :confused:
I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . .
Any ideas as to what is causing this error?
Hi everyone,
When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ??
In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??
Thanks
Hello ,
I want to drop a table from a publication , so that i can copy some data from another server . After the copy , i want to add the article or the table back again to the publication without making any changes to the subscribers configuration .
Is it really possible on SQL 7.0 ? Right now it does not allow me to copy the data to the published table or even drop the article (table) and it says that the article is published for replication and cannot be dropped or modified .
The table is configured for transactional replication .
I will have to drop the entire publication and create all the subcribers again .
Any help in this regard please .
Sameer