Hi,
We're running a replicationprocess for months now, and 2 days ago it broke down, for no appearant reason I can detect. The setup is this:
A SQL2K server has a publication defined on a database, consisting of several dozen tables. It is a transactional publication, running continuously. There is 1 subscriber, a SQL2005. Both servers run with Win2003 and all have the latest servicepacks.
Up until 2 days ago there was hardly a problem. Then I received errormessages on being unable to load into a specific table. That table had been changed on that day, namely, 1 column was changed from CHAR(13) to CHAR(12). I do not know if this has any relation with the problem we experience.
Currently the databases are structural the same. I could not get the replicationprocess to work and deleted all, publication on server1 and subscription on server2. I tried to setup a new transactional publication, which is not a problem, however, I cannot create a succesful subscription. I receive the message:
The process could not bulkcopy into table 'Tablename', where tablename is the changed table...
Again, both tables in publisher and subscruiber are the same in all respects. When I remove the offending table from the publication, the same message now with another tablename... Removing this table will result in again this message with another tablename...
I am really flabbergasted. Any idea where to look at?
I've never had any issues logging in, and now today I'm getting this error: Exception Details: System.Data.SqlClient.SqlException: Cannot open database "HRIService" requested by the login. The login failed. Any have some knowledge to drop on me concerning this? Here is the entire error:Server Error in '/HRIService' Application.
Cannot open database "HRIService" requested by the login. The login failed.Login failed for user 'IT-P02ASPNET'.
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: Cannot open database "HRIService" requested by the login. The login failed.Login failed for user 'IT-P02ASPNET'.
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.
hi, i am having sql7 with sp2. Recently our server got bounced back. No body stopped the server, there are no work load at that time. Server suddenly stopped and started. It is weired. Can anyone have any clues , how it happened and why it happened. Thanks!
I work for a small analytical laboratory. I'm mostly the net admin guy, but I do the db stuff (poorly) and other things.
Last night right before closing my users started experiencing bigtime slowdown. This morning it was magically better for a short duration. As the day went on the problems got worse.
We use MS Access as a 'front end' to talk to the SQL server. My users would go into fields and toggle on (or off) something they wanted. Sometimes the system acted as in the toggle never happend (cant generate a report without the right options toggled).
I'm not very DB savvy, so I checked all the other things I was having a problem with that day to see if it was direct correlation.
A few people have suggested I defrag the hdd as well as the SQL db. I have maintenance plans set up to back up the db and transaction logs daily (and weekly). However the maintenance plan that had "reorganize data and index pages" checked has been disabled for 2 months, since it seems it never finishes. It would run, and then one of my other plans would run (several hours later on the weekend).. and I'd come in on monday and find 3 maintenance plans all bound up.
Usually I had to stop the SQL server and re-start it as the 'stop' option for maintenance plans wasn't being responsive.
At any rate. I'm looking for suggestions. The DB is (after a shrink) 7GB. I don't think is excceptionally large, so I'm not sure why I'm experiencing these problems.
I found some scripts for DBCC SHOWCONTIG and DBCC INDEXDEFRAG. But I'm not sure how to execute them in a scheduled fashion? Is this just done in the maintenance jobs?
My users need to use this system (reliably) asap tomorrow. I'm going in 2 hours before the office opens in hopes of saving the day and anything I could arm myself with to make this process easier would be completely awesome.
So with that in mind. I'll stop rambling.
Ah yes, some information regarding the system:
Windows SBS Server 2k3 SQL Server 2000 w/ SP3 (or maybe 4?) (came with SBS 2k3)
Hardware: AMD X2 4800+ 2GB ECC RAM 160GB x 2 Sata Raid config. 1x Gigabit Network Card
I have an asp page that inserts several pieces of input into an SQL database. Page was working fine, and Im not sure what could have changed to cause the following error when data is entered into one of the fields:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'room'.
/Global.asp, line 20
where global.asp line 20 is oConn.execute cSql
In this case the text I input was: this room is big
I also tried input of fdsa, and recieved the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'fdsa'.
/Global.asp, line 20
More Details:
The field I am inserting this value into is a (nvarcar(1000),null).
None of the other fields on the page have a problem, and this is the last value insert into the database.
Any suggestions or ideas on where I should look to begin solving this issue.
I am working on a web application at work where we are using a SQL Express 2005 database.
We have 2 environments, my development environment which involves my local machine and VS 2005 Pro, and the database on a seperate server.
The client environment is on another server on another network also running SQL Express.
I was working on Friday, everything was fine and dandy. I was sick yesterday. When I come in today with no code changes (checked SVN) I am now suddenly getting timeout problems.
Error Type: Database
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
I thought maybe this was just a problem with my machine so I've checked another development box here, same problem.
Also, the client emailed and they are getting the same problem. Thier environment is using a compiled version of the code from last week. No changes.
Did something happen yesterday or today that requires a patch or upgrade? Why would these failures suddenly appear? What can I do to troubleshoot this?
Hi. I recently upgrading my dev machine to Vista and VS 2008 simultaneously (clean install) and upgraded my VS2005 project, which was previously using SQL CE 3.1. Now when I compile and run it, I get a database exception telling me to upgrade my database to version 3.5!
First of all, I tried running the Repair() method to upgrade the database file, but then I get an exception telling me the password is incorrect (it most certainly is not, I didn't change a thing), so it won't even let me upgrade it.
Secondly, I even tried removing the reference to System.Data.SqlServerCe and manually adding the verison 3.1 reference back, but it still for some reason loads version 3.5 instead of version 3.1.
So! Please either tell me how I can properly upgrade my users' database file to version 3.5 and have it work with the latest libraries, or tell me how I can at least use the old libraries like I was under VS2005. The .dll files are all present in the project and I haven't changed them at all, they're being properly copied to the output directory and everything. I even checked the "Specific Version" attribute on the reference in the project, but no help.
Do I have to do something special to tell my application to load the local copy of the assembly as opposed to, say, one in the GAC?
I have been developing a database in SQL Server Management Studio Express for the past few weeks. I have been writing stored procedures and functions, creating and deleting tables, etc., etc. and everything has been working just fine. Today, however, if I try to run any of the functions I have written I get "Invalid object name 'functionName'." This occurs even if I create a new function, execute the CREATE statement. Then if I refresh the Functions folder I can see my newly created function, but I cannot run it. This is terribly frustrating.
Does anyone have any ideas as to what might have changed? I am logging in as the same user, on the same machine that I have always logged in on. I created the database that is giving the error.
When I right-click SQLEXPRESS in the object browser, and go to Permissions in the Server Properties dialog, no permissions are selected as "granted" for my login name under any of the Logins/Roles. (ex. BUILTINAdministrators: my login name is listed in the "Explicit permissions for BUILTINAdministrators" list, but no permissions are granted. If I go down the list selecting [checking] the various permissions, then close the dialog. When I open the dialog again, rows have been added to the list that have the Permission name, then "sa" listed as the Grantor, with that row checked. But, there are still no checks next to my login name.)
The only thing that has changed on my machine, that I am aware of, is that a Windows Authentication update ran on my machine earlier today. I am using Windows Authentication in my SQL Server instance. I assume it has something to do with that.
Please help. This is urgent. We have a presentation on this project in 3 days. ugh.
I have a database with tables that are used for various front-ends, Access 97 and ASP. All of a sudden, my "Stores" table will not let me add, edit, delete, insert records. I checked security permissions everywhere and cannot figure out why this is happening. Is there something linked to this that might be running a process that might be causing this?
I have several databases on a 2012 instance that are mirrored to a second server, and log ship to a third server for reporting purposes.Recently, for two of the databases, the log shipping has been failing at least once per day, and sometimes more often.
i have deleted and recreated both the mirroring and log shipped databases on several occasions, but the problem is still happening.The log shipping restore jobs don't get marked as failed in the job history, but if you expand the history you can see errors such as this:
Restoring a new backup of the database cures the problem for anywhere between 15minutes and 12 hours, but it always seems to re-occur.I have run DBCC on the source databases with no errors reported, and five other databases have the log shipping working without errors.
I've got a job that sends out the results of a stored procedure in anemail via xp_sendmail. The job code is as follows:DECLARE @rlist varchar(1000)Declare @Q varchar(100)Select @Q = 'EXEC impact_exec..TLD_Reconciliation'Declare @Sub nvarchar(60)Select @Sub = 'TLD Reconciliation - Condensed Report'SELECT @rlist = 'jmiller@wbhq.com'exec master..xp_sendmail @recipients=@rlist,@query=@Q, @subject=@SubThe error message I get when running this in Query Analyzer is:ODBC error 7410 (42000) Remote access not allowed for Windows NT useractivated by SETUSER.In Enterprise Manager the owner of the stored procedure in question isdbo. Our SQL guru here suggested I change the owner of the storedprocedure from dbo to myself.I did that and got a warning message that changing the owner willbreak connections. (I'm paraphrasing here because I don't rememberthe exact wording of the warning.) Anyhoo, after changing the owner,and then trying to run the code in Query Analyzer I got the errormessage that the stored procedure could not be found.I'm not sure what to do here. I've never seen the error messagebefore. This same query worked just fine a couple of days ago.Any ideas?Thanks,Jennifer
I've been building and running script tasks for years without issue. Then all of sudden last week Visual Studio starts showing the "Task is configured to pre-compile the script, but binary code is not found" error anytime I open a script -- even I make no changes. If I copy the dtsx package to another machine, the script compiles fine and I can see the binary data in the raw .dtx file -- so I know the code is correct.
I can also reproduce the error simply by adding a new script task, going into "Design Script", make no changes to the default code (which is basically one line: Dts.TaskResult = Dts.Results.Success) and simply press "OK" on the Script Task Editor.
I've been trying to find some VS setting somewhere that might stop the script IDE from producing the binary code, but I can't. It doesn't seem to be project setting, because all my SSIS projects are now suffering this problem on my main dev box.
I have had SQL2005 on a server for a very short period of time. Today I found that I am not able to execute an SSIS package in debug. It give me an error stating that the evaluation period has expired for data transformation services. I went to check the version by querying, but nothing happens when I click on the Server Management Studio link.
Has anyone seen this before or how long the trial version should last?
Does anyone know what version may have been installed on my server?
We have an application in which one particular stored procedure goes from .3 second response times to 20-40 second response times, suddenly, at seemingly random times of the day. Recompiling that particular stored proc fixes the problem temporarily.
The 3 main tables have between 500K and 1.5M records, are defragged daily and contain the past 42 days of work. Old records are deleted once daily. The slowdowns do not conincide with the deletions, occuring without a clear pattern. Nothing else on going on on that server -- it is dedicated to this one app. Nothing shows in event log. It is clustered Windows 2003, with SQL Server 2005 RTM version. SP2 of SQL2005 due for an install next Monday. This behavior also was seen when the app was on a different server running SQL2K SP3, so think the underlying problem is some sort of design issue with the app, not a SQL server bug. Has anyone seen something like this and what suggestions do you have for doing a permenant fix? Think that a recompile of the sp causes a new execution plan, but why would that be necessary daily or even several times a day? 30-50 users are banging away at it. The app is an order entry system. The tables contain what are basically order histories and label data. Am running out of good ideas. Thanks for any help.
When I using Create Publication Wizard for database 'MY DATABASE', and setup 'MY SERVER' as the Distributor, then I get a message:
SQLServer Agent on 'MY SERVER' current uses a system acount, which cause replication between servers to fail. In the following dialog, specify a domain account for the Service startup account.
I don't understand this message (current in SQL Server Agent properties, tab Service startup account using System account)? Anyone can explain to me?
The problem case for transactional replication in MS SQL 7.0 is as follows: Server A (intended publisher) Server B (distributor, intended subscriber)
When configuring replication, the distributor (B) can be configured easily; the publisher (A) that uses B as its distributor will not allow for a publication to be created using either the Enterprise Manager wizards or TSQL commands. The error '627: Cannot use SAVE TRANSACTION within a distributed transaction' occurs when creating the publication.
Is it safe to change 'max text repl size (B)' on a live system while it is running with users and services running?? I'm using SQL Server 7.0 running on Windows 2000.
one week ago, for testing purpose, I set up trans repl, trans repl with updateable, snapshot repl using the same instance.
configuration is like this,
publisher and distributor are on the same server, 2 remote subscribers, one is 2000, the other is 2005.
It works OK, today I am trying to disable the replicaiton, clean up the machine. keep getting the errors:
an exception occurred while executing a T-SQL statement or batch only replicaiton jobs,or job schedules can be added, modified,dropped or viewed through replicaiton SPs could not update the distribution database subscription table, the subscription status could not be changed. changed database context to 'master',(MSSQL SERVER error 22538)
I am trying to set up a merge replication between SQL 2005 and SQL Express. When I try to apply the snapshot to the SQL Express database. I get the error "Can not access filepathSnapshot.pre due to error OS 3"
Does anyone know what OS error 3 applies to?
Also, when I try to place the snapshop in the default folder location say D:ReplicationData the create snapshot process will work. If I try to place the snapshot folder not in the default and in a UNC like this \serverIPReplicationData I get an access error. sqlAgent has access to the share, which is fully shared out. Any ideas?
I save Table size and recs. no every day. and check it some days.
... insert into @t exec sp_msforeachtable 'exec sp_spaceused ''?''' ...
But Today I saw sudden increase size in a table. about 128 MB in a day. (Average Growth fro this table was 4 or 5 MB in a day)This growth was for Only 4222 Records. While for more number of records (about 7000) in yesterday we had only 2 MB GRowth!
This Table information (Now):
sp_spaceused 'Table1'
Result:
name ---Rows --reserved --data
Table1--1021319--460328 KB --283104 KBI Try to gess The reason. I copy These new records to another table.But The result was more strange : on new table the size of these record was : < 1 MB I copied All records to another table . The size was : 148 MB (while this is 283 MB in my real database)
I am running merge replication (SQL 2000 with SP2) with an anonymous pull subscription. The application vendor has come out with update that requires adding a table to a database. The vendor has created scripts that will add the table, as well as some stored procedures. If I apply the scripts to both servers and add the table as a new article to the publication, am I going to have to apply a snapshot of the entire database (which is very large)?
I'm in trouble again. I want to make merge replication between SS 2000 Enterprise and SS 2005 Express edition. 2000 will be publisher, 2005 will be subscriber. In addition I want to use push subsription at 2000 side, very last thing is it will be download only (I know how to make a merge rep. downloadonly, thanks god!). That's all I want to do.
I made a little search, it seems what I want can't be done automatically, using SMO or scripts is suggested. In one of other forums I saw "distibuter>=publisher>=subsriber" (refering sql server versions). Is it an incontestable truth that I cannot make SS 2000 a publisher where SS 2005 Express is subscriber?
How can I achieve my goal, isn't there anyway to make 2000 publisher for merge replication while a 2005 Express is subscriber?
Hi All, We are using SQL2000 servers with transact replication to a warmstand-by server. We are seeing the following error from the Log readeragents.Does anyone have any insight on this error?The process could not execute 'sp_MSadd_repl_commands27hp'TIA Scott B.
We have VLDBs running on a SQL 2005 server. Some users have questioned speed and response times. When I investigated the server (W2K3) using the perfomrance monitor, I noticed an interesting questionabel result. The Repl. Pending Xacts showed 44,000,000 transactions waiting to be delived to the distribution databse. Problem is - we do not have replication tunred on on any of the db's on the box. In my research, I have not noticed anyone with a similiar issue. Does this amount of transaction take up space, or is it simply a counter that I don't have to be worried about?
one week ago, for testing purpose, I set up trans repl, trans repl with updateable, snapshot repl using the same instance.
configuration is like this,
publisher and distributor are on the same server, 2 remote subscribers, one is 2000, the other is 2005.
It works OK, today I am trying to disable the replicaiton, clean up the machine. keep getting the errors:
an exception occurred while executing a T-SQL statement or batch only replicaiton jobs,or job schedules can be added, modified,dropped or viewed through replicaiton SPs could not update the distribution database subscription table, the subscription status could not be changed. changed database context to 'master',(MSSQL SERVER error 22538)
I'll start off by saying these questions were submitted to Microsoft and no one can give us an answer without them doing a source code review. Their response was "these are internal BCP scripts that are used by SQL"....yes, I know that....that doesn't help to explain why the system created them. At this point, I don't want my compary to pay support hours for Microsoft personnel to learn what their software does. -------------------- We've had merge publication in place for several years.
Last year, we added some tables/articles to publications.
After the upgrade from SQL Server 2000 SP4 Standard to SQL Server 2005 SP2 Standard, when the snapshot was run, the system created some new files that had never existed before. Also, and more importantly, these files were only created for the articles added after the initial snapshot was created. Articles existing in the intial snapshot did not have these type of files created.
Examples of these files for a SA_MOVE table that was added to an existing publication:
The system wanted to apply these files before it would start synchronizing data so they had to be delivered to the subscriber. It appears it was the sysmergesubsetfilters_ files that it wanted to apply. These are all 0 in size and we do not have filter on(at least not as far as I know...see the addmergearticle code below).
New databases created this year that had all of the articles defined in the publication before the initial snapshot was taken did not get these additional files created, nor did the system want to try to send files to the subscriber before syncrhonization could begin.
This is an example of how an article had been cretead under SQL Server 2000 when adding a new database, before an initial snapshot is run:
We have a client that has a large (5Gb) database replicated to 13 subscribers, the publisher is Sql 2005, the subscribers are Sql Express. The publication has as few filtered articles too. I have found that after several months of continuous running Replication Monitor is taking a long time to report history on each subscriber.
Do people tend to rebuild the indexes on the system merge replication tables on production servers, or should the standard replication jobs take care of this?
I am setting up a combination of transactional and merge replicationon a very large database and do not want to apply the initial snapshotsince this would take to long. I am doing this with scripts that werepreviosly created when the database was not this big. I want to copythe publisher DB over to the Subscriber Server. Can I use theparameter @sync_type to 'none' when running the sp_addsubscription?Does anyone have any experience in doing this?Thanks in Advance for any help on this.Alan
We have had Merge Replication working for the past few months (SQL 2005 to SQL Mobile) and suddenly today the replication URL that points to the SqlCESA30.DLL does not work. I have tried re-running the SQL Mobile virtual folder wizard a few times....resetting IIS and even a re-boot...and still the URL is not available.
Is there anything that might provide some clue as to why this has unexpectedly failed?
I've been able to startup a transactional replication between 2 database for some tables that only have different table names. Now there are still some tables that not only have different names but also different column names, can this be done in the wizzard ??
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!
