I have a design that includes articles that will be searched. Obviously its too slow to put them into fields, and impossible because some have photos or are otherwise html documents. So I want to put pointers to their location.
Two questions. For each deployment, both desktop and web, where is the best place to put the articles. In any folder, or only in an iis virtual folder?
dennist685
Is there a place where i can find events that takes place in the sql server? Like adding data to a database or something like that....
Regards
Karen
hi all,
I was trying to set the articles to
"delete data in the existing table that matches the row filter"
in the publications "article>articles>table articles>snapshot" of
the publication
but everytime i reopen that property tab
the publication restore to the
default setting of "drop exisiting table and recreate it"
i"m using sql server 2000. snapshot replication--
thanks,
------------------------------------
joey
Hello,
maybe anyone knows good books or articles about ETL, ETL processes, ETL optimization ?
If you know where i can download - post a link
Thanks
Is there a way in T-SQL to programmatically list all the articles? What I'm wanting to do is grab these articles and run this SP:
EXEC sp_publication_validation 'people', @rowcount_only=2, @full_or_fast=0
For each aritcle/publication, but I need a way to programmatically calculate the 'people' article in this case and pass that information along to the sp_publication_validation SP.
Is there a way, perhaps in system tables or system SP to accomplish this?
Thanks,
Phil
Hello Everyonen and thanks for your help in advance. I am developing a conetnet management system to allow for the storage of articles within a SQL Server 2000 database. I am using FreeTextBox as the editor for users to enter articles. I ahve two questions. First, many of the articles are quite lengthy and including HTML formatting go well beyond 8000 characters. How should I go about storing these articles? Should I use a TEXT datatype, or perhaps split the data into more than one row. This leads to my second quuestion. Many sites that display article type data break the artilce into multiple pages with page numbers or next links to page back and forth. I am not sure hot to go about implmeneting this. Any help on this topic would be greatly appreciated. Thanks.
View 3 Replies View RelatedHi every1,
Does anyone know where i would be able to find good articles on this subject:
-Best Practices (Logical - planning & Physical) in Setting up Database Projects for MSSQL Server 2000 with Visual Source Safe 6.0
*It would also be helpful if articles take replicated environments into consideration.
thankyou
Hi
where i can get good articles on indexes ?
I'm new to replication and set up a Snapshot Replication on the tables in my sample database. Â Everything seemed to work (database created on Subscriber instance, jobs set up, etc) but the articles (tables) are not being created in the subscriber's database. Â
I can see the files created in the folder but I don't see the tables or the data. Â Also, I confirmed that the articles are checked so they should be included.
Hello Friends
Can any body tell about the database jobs. what are the database jobs and how do they work.
If any body have any link relevant to it please send me as early as possible.
Thanks
SARB
I have a page which loads an article based on an ID, what i want to do now is when the article loads there will be a repeater control which holds headlines of related articles in it. My problem is how can i achieve this in terms of passing through the articleCategoryID to the stored procedure "stream_RelatedArticles".
This is my stored procedure that loads the initial article;
ALTER PROCEDURE [dbo].[stream_Articles]
as
SELECT TOP 5 articleID, articleTitle,articleDescription, articleAuthor,articleDatePublished,articleBody from Articles ORDER BY articleDatePublished
And this is the page_load;if (!IsPostBack)
{string strID = Request.QueryString["id"];
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);SqlCommand command = new SqlCommand("stream_ArticlesByID", conn);
command.Parameters.Add("@articleID", SqlDbType.Int).Value = Request.QueryString["id"];command.CommandType = CommandType.StoredProcedure;
conn.Open();SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
ArticleList1.DataSource = reader;
ArticleList1.DataBind();
reader.Close();
}
And here is my related article stored procedure;ALTER PROCEDURE [dbo].[stream_RelatedArticles]@articleCategoryID INT
as
SELECT articleID,articleCategoryID, articleTitle,articleDescription, articleAuthor,articleDatePublished,articleBody from Articles
WHERE articleCategoryID = @articleCategoryID
Greetings,I have one table, named Article, and one table name Category.The problem is, one Article could be in just one or in several categories.What is the best way to connect data between Article and Category according to fast search performance?I have several ideas:1. To have third cross table Article_Category with fields Article_ID and Category_ID, and search Article_Category table 2. To have several INTEGER columns in Article table (like Category_ID1, Category_ID2,..) and search those columns3. Add one VARCHAR field in Article table where I could write Category ID's delimited by some character (e.g. by comma), and do text search in only that column.What is recommended for solving problems like this?
View 7 Replies View RelatedHey All - Another replication question:
Using VBScript in an ActiveX module of a DTS package...
Anyone have an idea how to add ALL articles to a Subscription.
The only way I can figure it is to load an array from sysobjects, then run sp_addarticle on each one.
Thanks for your help
RobbieD
Guys,
Do you have articles on this? this is for my reference.
thanks!
I have setup Transactional replication in SQL 2005 between two servers and have about 200 tables being replicated. The problem is that every time, I add or drop a table to replication, and start the Snapshot agent, it re-initializes every article and re-loads every article. This process takes 1 hour to complete and CPU usage goes to 100% during that time.
This behaviour seems very different from SQL 2000 where I would start Snapshot agent and only the relevant tables were added/dropped.
Has that functionality changed from 2000 to 2005? Am I not doing something right?
Thanks,
Amir
I wonder if anyone can advise, I currently have a replication set to 5 offices. Which has been working fine for some 18 months+, then the replication dropped out. When I looked in the publisher it had lost all its articles and would not allow me to add back in. I deleted the publication and recreated it and then after creating a new snapshot starting rolling back out to the various locations, and one of the locations failed and again all the articles where lost. I deleted and rebuilt the database at that office and went through the same routine again, only for it to happen again, the issue being that now errors where produced!!!.
So at present I have the publication and all the subscriptions running apart from this one office.
Has anyone had this before, or any ideas. I have run a full virus scan just in case and it was fine.
Hello,
I have some articles already published and would like to change some of the articles default @conflict_retention of 14 days to 45 days. How can I accomplish this via t-sql? Thanks.
John
Dear Friends,
Hi I am having a problem while showing Products from SQL
Server database. Exactly I have a table named Products which has some Products
posted by admin I want to show some latest Products on my default page
based on DateTime which are displayed like show last week articles,show last two weeks articles etc on button click I written my SQL query like: searchquery = "select * from Prducts where Date between '" + System.DateTime.Now.AddDays(-7).ToShortDateString() + "' and '" + System.DateTime.Now.AddDays(-1).AddHours(23).AddMinutes(59).AddSeconds(29).ToShortDateString() + "'"; If any body knows plz help me
Hello,
i want to Select all articles form a category without an article that is selected.
Thanx a lot !
We are runnning on SQL Server 2012 SP1 + CU9.
I have found some articles with no publication in our transactional replication.
For example, running this:
select p.publication,
a.publication_id,
a.article
from dbo.MSArticles as a
left outer join dbo.MSpublications as p
on a.publication_id = p.publication_id
shows this:
NULL1org_Community
NULL3org_Community
Purchasing to EDW5org_Community
NULL1org_Division
NULL3org_Division
Purchasing to EDW5org_Division
How can I get rid of the articles that are not part of a publication?
I can't use sp_droparticle because it requires a publication which these articles do not have.
I have been adding a lot of Articles for Publication.
They make it to the distributer but they do not make it to the subscriber.
If I add articles before the previous batch of articles get applied to the subscriber could that cause a problem and for replication to become out of sync?
It is a pull transactional replication.
I have had to generate a snapshot for the problem to go away but it keeps happening.
Fortunately it is not in production yet.
[URL]
Hi All -
Any recommendations for good advanced t-sql books/articles? I find myself involved with writing increasingly more complex queries and after spending a few hours on some, and then searching on this site for potential answers/help, I am wondering if there might be some good books on creating more advanced/complex t-sql for real world scenarios.
Thanks
- will
I would like to create a database & save to that data base the ratings of articles that are given by the users.
(I want to create a recommendation system.)
I am using joomla! How do I do this ?
Hello,
My company is moving to a SQL Server-based packaged application early next year. We€™re planning our SQL Server architecture but have some questions that I can€™t readily find answers for. I€™m hoping someone here can point me in the right direction.
We have three servers, I€™ll call them A, B, and C. We want to duplicate all changes to certain databases on server A to server B, then duplicate changes to selected databases and tables on server B to server C.
Ideally we€™d run SQL Server 2005 Enterprise Edition on all three servers, but the packaged application vendor does not support SQL Server 2005 yet, only SQL Server 2000. Our license agreement with them does not allow us to use replication on server A. We€™re free to do whatever we want on our other SQL Servers, but server A must sit alone, untouched, like a monolith on a far-away moon. (I€™m lobbying to have the server named Tycho, or TMA2.) Stranger still, they€™re OK with log shipping from server A to other servers. We€™ve tried to explain that replication and log shipping are both core function built into SQL Server, and that if one is acceptable, then both should be. Their fear is that replication could cause performance and stability problems, and to eliminate this possibility they€™re ruling out replication on server A.
Given these constraints we€™re resigned to using SQL Server 2000 Enterprise Edition on servers A and B, and SQL Server 2005 Enterprise Edition on server C. We plan on periodically shipping logs from server A to server B and applying them at server B.
We€™d like to know if it is possible to also use transactional replication on server B to duplicate changes from server B to server C. I€™ve used log shipping and replication in the past, but never at the same time. My understanding is that a database goes into recovery mode while a transaction log is being applied and that any user changes to the database after the log has been applied will cause later log applications to fail. The scripts I€™ve seen that are used to apply the transaction logs put the database into single user mode after the log has been applied to prevent this.
This raises a few questions:
If we try to RESTORE a log to a database being used as a source for transactional replication articles, will the RESTORE fail? Or will the RESTORE start and break the transactional replication? I€™ll test this on my own, but it€™d be nice to know if anyone has already experienced this.
Is it possible for us to have a database in read-only mode serve as the source for transactional replication articles? (I can€™t imagine why not, ever though it seems counter-intuitive - why would you want to replicate transactions from a database that has no transactions?)
If the answer to number two is yes, can we suspend transactional replication on a database, RESTORE a log to the database, put the database into read-only mode after the RESTORE, and restart the replication on the database?
Thanks in advance for sharing your wisdom, everyone!
--
Thomas C. Mueller
Has anyone had problems with this limitation before or could explain to me what the purpose of this limit is for? It obviously does allow you to publish more than 246 (we have been publishing more than 700 for years). Is this new with SQL 2005? Is my publication going to stop one day and tell me there are too many articles?
Thanks in advance.
Martin
Hi everyone,
do you know any articles on foreach loop container that loops over a dataflow task...pls tell me....
thanks in advance,
I 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 &&%$#@*(#@$%.......------------
We have many users with a mobile application running SQL Mobile and using merge replication to get data back to the SQL 2008 R2 database. This has worked very well for many years.
We now have a requirement to have this data reported on using Reporting Services. This is where it gets messy.
Due to a limitation of Report Builder(see this blog) we cannot provide access to users for creating their own reports. The report database is remote from the host and there is no VPN.
We hit upon the idea of creating an almost identical publication but the articles as read-only. It was only after this was done that we started having trouble with our existing mobile users.
It seems that a published article is EITHER Bi-directional OR Read-only even if they are in separate publications.
I then thought of using Transactional Publication but this too is blocked on creation with "automatic identity range support is useful only for publications that allow updating subscribers"(Merge and Transactional publication are mutually exclusive)
So in the final analysis is there a way for me to have merge replication AND some other form of SQL replication/data transfer that can have the same data transmitted readonly to a separate full SQL server database?
I am setting up filtered replication(tran repl) for a table that is 2+ TB in size. The filtered rows are less than 1% of total size.When I run the snapshot, sometimes it complete in 27 mins(I have already optimized the snapshot agent profile) and sometimes it runs for ever. Mostly it runs forever, so the chances of it finishing soon are really very less.I have infact created filtered index just to cater to the query in the filter i.e. on the filter predicates. The reason its flipping flopping is due to the incorrect plan that it chooses. I can force the right index and the filtered query runs just fine but. I cannot use the query hints when I am adding the filter in replication as its not allowed.
I then went ahead and created the plan guides to force the best plan. But the plan guide was not used because even if there is a single space in the actual query that replication runs in the background the plan guide will not be used(testing on plan guides has proved this). The query needs to be ditto same including whitespaces and carriage returns. I then ran profiler and found that there is a view that replication creates and executes to extract the filtered rows and BCP it. Got the text of the view and created the plan guide on it but it was still not used because sp_helptext output seems to be different than what is actually being fired by replication internally.
Further, I collected the verbose log with level 2 and tried to find out if the main query shows up there with no success.
(a) I can't use the plan guide because I do not know the exact syntax(I mean I have the query but I do not know the white spaces, carriage returns, etc) and
(b) I cannot use query hint while filtering the article.
1. I cannot afford to have downtime.
2. I cannot use backup and restore option either.
My company's SQL environment consists of hundreds of servers. We are just beginning to upgrade from SQL2000 to SQL2005. I'd like to find some readings that might help us with basic design decisions regarding SSIS, especially whether to have SSIS running on many of our servers or to have just a few (maybe even just one?) server(s) dedicated to SSIS that will host all packages. Can anybody suggest a good source for this kind of information? Thank you.
We have restored a database on new server without keeping replication settings. Now while creating the publication no tables are shown in new publication wizard window. In fact we have hundred of tables in database and included in replication on source server from where backup was taken.
What may be the reason that tables are not appearing?
We have a SQLServer 2005 Enterprise merge replication publication with SQL Mobile 3.0 subscribers (Windows Mobile 5.0 and 6.0). We do not use pre-computed partitions due to trigger performance issues with an SSIS/ETL application that supplies data to the merge database. We do use the "Optimize" (=true) option, though we have tried this both ways with no significant differences. We use filters and joins for each worker ID (as HOST_ID) from the subscriptions.
The sync times become increasingly worse after we run the snapshot and bring the publication online. I have tried rerunning the snapshots, this helps little, as it often behaves like the subscription was set to reinitialize and forces a big sync (reload of all data) to the subscriber. We have tried much of the obvious (e.g., flattening filters and joins, adding indexes, etc.).
When users are synchronizing, we watch replication monitor and notice that a lot of time is spent processing "enumerating inserts and updates for article [any article]", especially processing the many generations and batches. This is true for any follow-up syncs after the 1st big sync (initializing the subscription).
I read several posts regarding the batches and generations of changes, and decided to try increasing the €œDownloadGenerationsPerBatch€?. I tried adding this parameter to the snapshot agent job, and the job fails each time with a vague message, even with the default value of 100. How do you change this parameter for SQLServer 2005 Enterprise?
Any suggestions?
Thanks in advance,
Matt
Hi,
Can anyone give me any input on this. Recently TempDB one of my production server came down because tempDB got so big that it chewed up all the space in it's drive. My TempDB was in drive C:, where the Operating system and the rest of the SQL systems databases are(msdb,model,master). The actual production data are located in another logical RAID 5(Drive E:) Drive. I want to prevent the problem from happening again. Is it wise or does it degrade performance if i move TEMPDB from drive C: to drive E:? Is this going to cause a major bottom neck in drive E:, where the data are located?
Thank You for any help!
Eric