I have a merge publication that I always create with a script but has now stopped working with what appears to be a complete server problem.
The actual command i am executing is a simple
sp_addpublication_snapshot
but it fails with
Msg 50000, Level 16, State 1, Procedure sp_add_jobstep_internal, Line 253
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3931, Level 16, State 1, Procedure sp_MSadd_repl_job_unsafe, Line 376
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.
I have tried recreating the database from scratch, renaming it, renaming the publication, even disabling the publications altogether but it makes no difference. There is nothing in the sql or event logs and I cant do any work until Ive got past it - help!
I am using SQL 2005 SP2 if that makes any difference.
Our stored procedure error handling is not working now but did before.
When a severity level 16 (Miscellaneous user error) occurs the batch (stored procedure) exits without processing the next statement which is the error check routine. This is a simple error processing routine and used to work. What could have changed?
I have searched books online and have found nothing on abnormal termination of a SQL batch. I would appreciate some help here.
I am having a real problem opening a database on a SQL server through EM. The DB is on the machine as I can use EM on another machine and login from there. However, when I try to login to the DB via EM on the actual server I cannot and get an error saying 'Connection failed. Check SQL Server registration properties'.
Even if I change the reg properties it still will not let me access the database, it just keeps giving the same error.
The following query has been working for months and the other day it just stopped. I get no error, it just never finishes. It used to take 20 minutes. Nothing has changed that I know of.
The query is designed to insert the new records from the t_DTM_DATA_STAGING into t_DTM_DATA_STAGING2 using the t_DTM_DATA_1 as the outer join.
Average record count for t_DTM_DATA_STAGING is 2 Million Current record count in t_DTM_DATA_1 - 267 Million Both tables have clustered indexes made up of the 10 fields in the join below.
Any Ideas??
SET QUOTED_IDENTIFIER ON INSERT INTO [DTM].[dbo].[t_DTM_DATA_STAGING2] ([CP] , ,[MAJ] ,[MINR] ,[LOCN] ,[DPT] ,[YEAR] ,[PD] ,[WK] ,[TRDT] ,[SYSTEM] ,[AMOUNT] ,[DESCRIPTION] ,[GROUP] ,[VENDOR] ,[INVOICE] ,[IDAT] ,[PO_NUMBER] ,[DDAT] ,[RCV#] ,[RDAT] ,[RSP] ,[EXPLANATION] ,[UPLOAD_DATE] ,[UPLOAD_USER] ,[UPLOAD_NAME] ,[RELEASE_DATE] ,[RELEASE_USER] ,[RELEASE_NAME] ,[TRTM]) SELECT t_DTM_DATA_STAGING.CP, t_DTM_DATA_STAGING.CO, t_DTM_DATA_STAGING.MAJ, t_DTM_DATA_STAGING.MINR, t_DTM_DATA_STAGING.LOCN, t_DTM_DATA_STAGING.DPT, t_DTM_DATA_STAGING.YEAR, t_DTM_DATA_STAGING.PD, t_DTM_DATA_STAGING.WK, t_DTM_DATA_STAGING.TRDT, t_DTM_DATA_STAGING.SYSTEM, t_DTM_DATA_STAGING.AMOUNT, t_DTM_DATA_STAGING.DESCRIPTION, t_DTM_DATA_STAGING.[GROUP], t_DTM_DATA_STAGING.VENDOR, t_DTM_DATA_STAGING.INVOICE, t_DTM_DATA_STAGING.IDAT, t_DTM_DATA_STAGING.PO_NUMBER, t_DTM_DATA_STAGING.DDAT, t_DTM_DATA_STAGING.RCV#, t_DTM_DATA_STAGING.RDAT, t_DTM_DATA_STAGING.RSP, t_DTM_DATA_STAGING.EXPLANATION, t_DTM_DATA_STAGING.UPLOAD_DATE, t_DTM_DATA_STAGING.UPLOAD_USER, t_DTM_DATA_STAGING.UPLOAD_NAME, t_DTM_DATA_STAGING.RELEASE_DATE, t_DTM_DATA_STAGING.RELEASE_USER, t_DTM_DATA_STAGING.RELEASE_NAME, t_DTM_DATA_STAGING.TRTM FROM t_DTM_DATA_STAGING LEFT OUTER JOIN t_DTM_DATA AS t_DTM_DATA_1 ON t_DTM_DATA_STAGING.TRTM = t_DTM_DATA_1.TRTM AND t_DTM_DATA_STAGING.TRDT = t_DTM_DATA_1.TRDT AND t_DTM_DATA_STAGING.PD = t_DTM_DATA_1.PD AND t_DTM_DATA_STAGING.YEAR = t_DTM_DATA_1.YEAR AND t_DTM_DATA_STAGING.DPT = t_DTM_DATA_1.DPT AND t_DTM_DATA_STAGING.LOCN = t_DTM_DATA_1.LOCN AND t_DTM_DATA_STAGING.MINR = t_DTM_DATA_1.MINR AND t_DTM_DATA_STAGING.MAJ = t_DTM_DATA_1.MAJ AND t_DTM_DATA_STAGING.CO = t_DTM_DATA_1.CO AND t_DTM_DATA_STAGING.CP = t_DTM_DATA_1.CP WHERE (t_DTM_DATA_1.CP IS NULL)
I had a Database maintenance plan setup to do complete backup of my SQL Server 2000 database. Same thing was done for the transaction logs as well. And they had resulted in successful backups sometime ago.
But I have noticed that Backups are no longer happening. I cannot find the backup files where they are supposed to land. Some how, I cannot find any error messages relating why the backups are not getting created.
I do not know where I can look up the logs/reports of what possibly is going wrong. I have looked at the usual places and they are not there, for the times I have deliberately tried to submit the jobs.
I'm just moving over one of our databases from SQL2000 to 2005. Everything is working fine, and all web sites are working, however I just can't seem to get xp_cmdshell to function on the 2005 server.
I have enabled xp_cmdshell, and when I run a stored procedure, which writes the contents of one column to a text file, it says "invalid object name". This table is definitely there, and is actually updated earlier on in the same SP, so permissions for that are fine.
This is the line causing the problem (if I take it out, the rest of the SP works no problems):
--write the text file EXEC master.sys.xp_cmdshell 'bcp "SELECT newslettertext FROM mydatabase.dbo.newsletters" queryout D:ewslettertext extbody.txt -U -P -c'
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'mydatabase.dbo.newsletters'.
SQLState = 37000, NativeError = 8180 Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. NULL
Could it be to do with permissions on the master.sys.xp_cmdshell? If so, wouldn't it give me a "permission denied" error?
I've got a stored procedure in database A that calls the sp_start_job stored procedure in msdb as follows:
CREATE PROCEDURE xxxxx WITH EXECUTE AS 'domainusername'
AS
EXEC msdb.dbo.sp_start_job B'jobname' ;
RETURN
The domainusername is the in the database sysadmin role and the owner of the job. To make this work originally, I had to change the msdb database to be trusted.
This worked for the past several months.
Now it doesn't work (perhaps after a reboot but not sure). The error I get is "The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'
I looked to make sure that the account had grant execute rights and it does. I tried setting it via GRANT statement and it was granted successfully yet the error still occurs. I've tried changing accounts and anything else I can think of to no avail.
Any ideas how to troubleshoot this issue. I've tried all the tricks I can think of.
I'm using query notifications and it worked fine as long as I just edited the data that the "query points to" (one table). That is, my graphical represenation of the data (in a gridview) was updated correctly with the new data 1ms after I changed it, just like it should.
When I inserted a new row to the table the query notification stopped working, meaning my gridview wasn't updated. Any idea of why? Also, after this, not even editing of the data made the query notification trigger so it must have totally stopped.
I had a Send email task linked to my Sequence Containers in my package and it was working fine. Everytime the container fails it would send an email to myself.
At some point all Failure constraints stopped working. Failure constraints work if I add brand new tasks, but with the existing tasks, they don't work. The Task which fails, turns red and execution stops. Next failure task is not executed.
I am not sure what triggered it to stop working. I cannot get anything on the log
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?
Hi, I used to be able to debug stored procedures via Visual Studio.net 2003. However, this has stopped working. It does not produce an error just simply doesn't work anymore i.e. the breakpoints are by-passed. I have the correct settings in the Debug configuration section. If any-one knows how to rectify this your help would be appreciated. I have thought about re-installing the remote debugging functionality on the server. However, our Visual Studio.net discs are with a developer who is away at present.
I'm trying to retrieve native character data (SQL_WCHAR) through ODBC into an SQL_C_CHAR buffer using SQLBindCol and SQLFetch.
The database is SQL Server 2005 Developer Edition.
If I use DRIVER={SQL Server}, i.e., use SQLSRV32, everything gets converted properly.
If I use DRIVER={SQL Native Client}, i.e., use SQLCNLI, I apparently get garbage. This is true for ntext, nchar and nvarchar. I also have problems with xml, but I haven't gotten far enough to isolate what's going on with it.
Dear Experts, i've tried to add two new articles to the publisher.i failed there. now i've dropped the articles, but still replication is not working. please let me know what should i do
Arnav Even you learn 1%, Learn it with 100% confidence.
I have eight SQL Servers 7.0 and transactional replication is working fine across these servers except one server. This one server (SERVERX) is not able to become subscriber. Tried everything even re-installed Windows 2000/SQL 7 (S.P2) but I am getting this error in msrepl_errors table "Login failed for user 'sa'". SERVERX is working fine as a publisher but not as a subscriber. The distribution agent displays an error "unable to connect to SERVERX"
Hi, I have the following setup Merge replication with Web Synchronization: Server: SQL Server 2005 Subscriber: SQL Server Express edition
The system uses parametrized filters based on HOST_NAME and I have selected 'Automatically define a partition and generate a snapshot when a new subscriber tries to synchronize'
To reduce the size of the initial snapshot I have selected to 'compress' the snapshot files.
It works fine in most of the cases but sometimes when I initialize a new subscriber, replication process fails with the error:
Message Replication-Replication Merge Subsystem: agent 25BCC8D2-968E-4CEE-B408-2DEF3210F682 failed. The Merge Agent failed to retrieve the snapshot schema script file '\db101BQ_REPL_SHAREuncDB101_BQDB_QA_BQ_WEB_REPL20070411090593dynsnapvalidation.tok'. Run the Snapshot Agent to regenerate the snapshot files for this publication.
If I regenerate the snapshot for the subscriber, and then replicate again it works fine. This is turning out to be an issue as I have to compress the snapshot files as they are huge.
And I cannot use 'Initializing a subscription without snapshot' due to this error: <http://forums.microsoft.com/msdn/ShowPost.aspx?postid=1458147&siteid=1>
Help me
PS: (Applied SP2 Patch, but the problem still persists) and the process works fine if i use pusg subscription
For reference here is the t-sql subscription code, and the vb.net that initiates replication:
Dim subSqlCon As SqlConnection = New SqlConnection(m_subSqlConStr) Dim pubSqlCon As SqlConnection = New SqlConnection(m_pubSqlConStr) Dim subscriberConn As ServerConnection = New ServerConnection(subSqlCon) Dim publisherConn As ServerConnection = New ServerConnection(pubSqlCon)
subscriberConn.Connect() Dim subscription As MergePullSubscription = New MergePullSubscription(m_subscriberDBName, m_publisherMachineName, m_publisherDBName, m_publicationName, subscriberConn)
If subscription.LoadProperties() Then If Not subscription.PublisherSecurity Is Nothing Then Dim agent As MergeSynchronizationAgent = subscription.SynchronizationAgent
I`m having a problem setting up replication on a newly installed 6.5 sp4 machine. When I try to create a publication, I get a message telling me that my chosen signon doesn`t have premissions to create a file in the working directory (though I know this isn`t the case). The publication does show up in the tree list in the Publications folder of the database, but when I try to subscribe to the publication, I get an Error 14096. Bummer. Anybody have any ideas? Thanks.
I've been trying to get my merge replication to work with a sql ce 2.0 on sql server 2005, but it keeps generating shapshot scripts my pocket pc can't execute.
Example of my table.sch file in my snapshot folder: drop Table [dbo].[Application] go
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Application]( [AppID] [nvarchar](20) NOT NULL, [AddOnInfo] [nvarchar](50) NULL, [MaxClients] [int] NULL, [AppName] [nvarchar](255) NULL, [NbrDaysHistory] [int] NOT NULL CONSTRAINT [DF_Application_NbrDaysHistory] DEFAULT (10), [NbrDaysFuture] [int] NOT NULL CONSTRAINT [DF_Application_NbrDaysFuture] DEFAULT (10), [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF__Applicati__rowgu__4C364F0E] DEFAULT (newid()) )
GO SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
ALTER TABLE [dbo].[Application] ADD CONSTRAINT [Application_PK] PRIMARY KEY CLUSTERED ( [AppID] ) GO
I've selected that I was setting up a merge replication that needs to be compatible with sql ce. But it doesn't seem to do this...
I've tried to set the compatibility level to 80SP3 manually but it remains 80RTM... I've tried everything.....
Does anyone have any ideas what could be causing this?
If I remove these lines from the sch file everything works jsut fine:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
ALTER TABLE [dbo].[Application] ADD CONSTRAINT [Application_PK] PRIMARY KEY CLUSTERED ( [AppID] ) GO
Even if I make a publication for sql mobile 2005 it generates the same script.... It's almost as if SQL 2005 is ignoring my settings...
I have a publisher, distributor and subscribed in transactional replication topology, each on a different MS Windows 2008 server. SQL server version is 2008. I changed the password of the service account used to the the sql server and sql server agent services on all 3 servers. But now replication stopped working (Distributor to publishers shows errors in replication monitor for all publications).
I restarted the services and also tried restarting the servers. I did "update replication passwords" and also used sp_changereplicationserverpasswords to change replication passwords. But it doesn't seem to work.
From [MSrepl_errors], i get the below errors:
The process could not connect to Subscriber 'XXX'.
Cannot generate SSPI context
SQL Server Network Interfaces: The target principal name is incorrect.
I have a complex join filtering on a replicated sql server database which was working fine in previous versions of sql compact. The query is something like the following:
SELECT <published columns> FROM <filtered table> INNER JOIN <child table> ON <child table>.ID = <filtered table>.ID and <child table>.date > getdate()-30 After I upgraded to compact databse 3.5, for some weird reason whichever tables have both these Join filter and article filter together behaving improperly. If I insert any row in any of these table, the row is replicated properly to the server, but it does not send the new row to any other users. Again this thing works fine in older version. I have switched back tyo the old version of sql ce and again it's started working.
How to check if web merge sync is working between a subscriber and publisher thru HTTPS ? SQL port 1433 at subscriber is blocked so no direct connection to subscriber.
please i need your help on that sudenly me SQLserveragent stopped and when i try to start it again it give me that error: Event Type:Error Event Source:SQLSERVERAGENT Event Category:Service Control Event ID:103 Date:2/27/2006 Time:11:00:19 AM User:N/A Computer:EGDC2 Description: SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).
I have sql server installed on my laptop..It is a student evaluation version. Today morning,the sql server did not start and when attempted to start..I get the following message: " SQL server started and stopped immediately. Some services stop automatically if they have no work to do , for example the performance logs and alerts."
This might be kinda long so please bear with me. I have a SSIS package that creates 2 other packages to pull in data from our as400. To do this I have a SQL 2005 table setup with as400 table names and columns and the matching sql tables names (I reuse the column names). I also use a flag to determine if the table name should be added to the data retreival that way if there is a need I can just run the procedure for a specific as400 table instead of all of them. The main SSIS package loops through the table to create the package to pull in the as400 data and put it into staging database. Then the main package creates another package that pushes the data from the "staging" sql to the "live" tables. This has been working great for about 5 months now. Yesterday I went to create a new job that would allow me to run the same jobs just with making change to the flags so that only certain tables are pulled in at certain times. This works very well in our test environment. For some reason it appears that it has corrupted the production packages. I can not get them to run at all now. The untouched jobs all of a sudden quit. I changed the new job to run as operating system and not SSIS and it says that it can not find it. I can see the file, I am using the SSIS on the file system. I can open the file and see all of the properties, I can copy and paste the file. I just can not run it. I was able to finally get the package to run using the command line but now there is something odd that it is sending to the as400. The kicker to me is that nothing changed with the SSIS package and nothing changed with the existing jobs I just added new ones and now it does not work. Could this be some corruption? Anyone else seen similar behaviors?
SQL mail has stopped functioning. I have checked the profile in exchnage. All is ok. I have tested the MAPI profiles in SQLAgent and SQLMail they all give success. When I run xp_sendmail the process just runs and runs without sending a mail. I end up having to kill the prorcess. Any ideas?
I tried to stop SQLServer romotely through Enterprice Manager. It used to work. But, this time I got a message "a error 5 - (Access is denied) occurred while perfrming this service oetation on the MSSQLServer service." and it could not be stopped.
