After I watch a video for how to create MS SQL Replication, I configure distribution and create the publication. The problem comes from subscription. If I create a "PUSH" subscription, it works fine. However, when I create a "PULL" subscription, I got the following error. Where I should look for solution for this error.
View 5 RepliesHi,
I tried to backup the master key by the following syntax :
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'
BACKUP MASTER KEY TO FILE = 'c: empmaster' ENCRYPTION BY PASSWORD = 'mypassword'
but it failed and i got the following message:
Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
NB: I am using the "sa" user to execute this command.
I know that we have a security permission issue , but where and how ?
Regards,
Tarek Ghazali
SQL Server MVP
I actually am just looking for some supporting documentation on some facets of SQL Server.As far as I have always known, when anyone does a READ from a SQL Server database (SELCT * from <TABLE>), SQL Server does not create a log record...since there's no data or database structure being modified. A colleague is under the impression READ's are logged operations.
View 5 Replies View RelatedHi ,
I got problem in production server at client place(No backup copy & not replicated,it's a SQL SERVER 2000 Enterprise server),by mistake client updated the data without using where condition then updated lakhs of rows (in SQL server autocommited),Now I need to recover this data from LOG file(.ldf).I tried with LOG EXPLORER(Third party tool) Trail version recovered from default database(Northwind,Pubs).But client not willing purchase this S/W for simple cause,How can we recover the data from LOG file.
1.Can we write the Program in C# to read the SQL Server Log and show the past transactions?
2.Is There any Stored procedures exist in SQL Server to read the day transactions in log file and take the backup?
3. How to read Transactions Log file in SQL Server 2000?
It's very Urgent,I am not expert in SQL Server 2000.
Could U Please Help me with possible solutions.
Regards,
Harikrishna.
I get the following error when trying to delete a recordset from an SQL Server DB:
ADODB.Recordset
error '800a0cb3' <o:p></o:p>
Current Recordset does not support updating. This may be a limitation of the provider,
or of the selected locktype. <o:p></o:p>
/sqlwishlist/Database2_interface/wishlists1/editor/deletionverified.asp,
line 33
The code is:
<BASEFONT FACE="Comic Sans MS" COLOR="DarkBlue">
<HTML>
<HEAD>
<TITLE>WISHLIST RECORD DELETION VERIFICATION</TITLE>
[Code] ....
Hi,
i am working in VC# 2005
I am using Merge Replication between SQL Seerver 2005 & SQL Server 2005 Mobile Edition
I want to Update a single Record from a particular table
So I want to ask that, what controls I should use?? DataSet and SqlCeDataAdapter etc.
Example Code will be much appreciated
thanks
Hi All,
I was using VB6 to access a MS SQL Server database. The code worked and works fine. I then decided to migrate the code to C#.Net 2005 using ADO 2.8 (not ADO.Net). Doing that yields with the same exact code the error message, "Current Recordset does not support updating".
I did a whole bunch of Google searches and didn't see anything useful. Mainly the advice from Microsoft and others is to make sure the mode on the connection string is set to "ReadWrite", as the default is "Read Only" and to make sure to set the lock type to either optimistic or pessimistic. Still others said that the code should set the CursorLocation property of the recordset.
I can safely say that I have been setting the mode to "Read/Write" since the start and have played around with the lock type, cursor location, and open method. Nothing works on C#, BUT VB6 is so totally happy with everything.
The provider works fine, as VB6 works fine, and the lock type is also fine, so therefore the built in suggestions do not apply.
My code is:
// Connection string template. Filled in properly in real code.
strConnect = "Server={0};Database={1};"
// Set the connection properties.
this.SQLConnection.ConnectionString = strConnect;
this.SQLConnection.Provider = "SQLOLEDB";
this.SQLConnection.Mode = adModeReadWrite;
// Open the connection.
this.SQLConnection.Open(strConnect, strUserName, strPassword, -1);
=================
// Create the ADO objects needed.
dbRSAdd = new Recordset();
// Open the recordset.
dbRSAdd.Open(strTable, dbCatalog.ActiveConnection, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic, (int)CommandTypeEnum.adCmdTable);
// Cycle through each record to add.
dbRS.MoveFirst();
for (lRecord = 0; lRecord < dbRS.RecordCount; lRecord++)
{
// Add a new record.
dbRS.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value);
...
}
// NOTE: The code crashes with the call to 'AddNew'.
Any advice?
SQL 2012 Ent SP2
Database is in simple recovery mode, and published with transaction replication push subscription, just one subscriber but the database is huge. I don't want to overwrite the schema at the subscriber either.
I had to run an alter database command on a published database, it created so many logs that an extra drive had to be added along with an extra log file to accommodate all the logs.
The problem I have is I'd like to know clear the file of logs so I can drop the temporary log file, and give the drive back, but I cannot.
I have tried dbcc shrinkfile with the emptyfile option but it never clears, I have also tried it with notruncate and truncateonly options (mainly out of desperation).
I do not need to worry about point in time restore as a full backup is taken before and after the operation. After which the database will be put back into Full recovery mode.
I have looked at log_reuse_wait_desc and the file says 'Replication', so I am now thinking the file cannot empty because replication is keeping one of the VLFs active. I tried dropping and recreating the subscription hoping it might free something up and I could get somewhere, but it made no difference.
Do I have to remove replication completely to get round this? Surely not.
I have also tried putting the database back into full recovery mode, doing a full DB backup, and a transaction log backup, but its made no difference, which is also what makes me think a portion of the log is still active because of replication, and perhaps the transactions have not gone through to the subscriber, which raises another question, why not?
I have not tried restarting SQL server, as I'd like to know a way out of this without having to do that, plus I do not think it would make any difference anyway.
I keep getting this error when I try to update
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
It worked fine in Access, but when I switched to SQL Server it stopped working.
Can someone help! Please!
thx
Justin
Hi all -
I know this is prolly an old one but I would certainly appreciate some assistance =)
environment:
SERVER (IIS6, .NET2.0, SQL2005)
CLIENT (WIN2000,IE6,VBScript)
I have an aspx that is invoking ado on the clientside. I read somewhere that javascript does not support connecting to SQL clientside. I may be wrong. In any case I am using VBScript on the client.
I am using something like this to invoke the script
Code Snippet
<a href="#" onclick="doReport()">Click Here</< FONT>a>
doReport() looks something like this ...
Code Snippet
' ========================================================
sub doReport()
Dim stSQL, rs, oWord, oDoc, oRng
Dim stCurrentSection, stTemplatePath, dtNow, dtSOR, dtLastDataPoint
Dim iLastDataPoint
set oWord = CreateObject("Word.Application")
stTemplatePath = "http://crivm-ccdev/ccprocharts/supportfiles/CCWordReport.doc"
set oDoc = oWord.Documents.Open(stTemplatePath)
oWord.visible = true
stCurrentSection = "a"
loadDebug
'loadLive
openConnection()
' == THIS DB CALL GETS 2 RESULT SETS
'set rs = getrsCustomerInfoReport(mstUnits, left(mstCycles,3), mstAppName)
set rs = getReadOnlyRS("sCC_GetCustInfo_Report '" & mstUnits & "','" & left(mstCycles,3) & "','" & mstAppName & "'")
' == POPULATE VARS FROM 1st RESULT SET
dtLastDataPoint = rs(0)
iLastDataPoint = rs(1)
set rs = rs.NextRecordset ' <== THIS IS WHERE IS ERROR IS
msgbox rs(0)
end sub
here is my openconnection sub ... oConn is global
Code Snippet
<script type="text/vbscript" language="vbscript">
'== Cursor Location
CONST adUseClient = 3
CONST adUseServer = 2
' == Cursor Type
CONST adOpenStatic = 3
CONST adOpenForwardOnly = 0
CONST adOpenDynamic = 2
' == Lock Type
CONST adLockReadOnly = 1
CONST adLockOptimistic = 3
CONST adLockPessimistic = 2
' ========================================================
sub openConnection()
Dim stConn
set oConn = CreateObject("ADODB.Connection")
'stConn = "DRIVER={sql server};Server=crivm-ccdevsql2005;Database=catcheck;Integrated Security=SSPI"
stConn = "Provider=SQLOLEDB;Server=crivm-ccdevsql2005;Database=catcheck;Integrated Security=SSPI"
'msgbox oConn.state & vbcrlf & stConn1
oConn.open(stConn)
'msgbox oConn.state
ApplySecurity
end sub
' ============================================================
Sub ApplySecurity
'gbhasDBAccess is a global variable on both client and server sides.
'Server side is set in seccheck.asp, which should be at the top of every page
'Client side is set in ApplySecurity() of ConnectServer.asp
Dim appRole
appRole = "appWriters,(tsvc123)"
oConn.Execute "sp_setapprole '" & split(appRole,",")(0) & "','" & split(appRole,",")(1) & "'"
End Sub
</< FONT></script>
Before we moved to SQL2005 I always used this
Code Snippet
' ========================================================
Function getReadOnlyMultRS(strSQL)
set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient
rs.Open strSQL, oConn, adOpenForwardOnly, adLockReadOnly
'Disconnect the Recordset
Set rs.ActiveConnection = Nothing
'Return the Recordset
Set getReadOnlyMultRS = rs
End Function
and this always allowed me to use set rs = rs.nextresultset
but now that we switched to SQL2005 it does not seem to work. I have verified in sql studio that this sp does indeed return 2 resultsets
I need to know, how to rename a file, how to delete a file. how to create a file, all programmatically in Vista.
Thanks in advance,
Frank
I posted this in the MS Access group, but no luck.------------------------------------------I've got another stored procedure in the same app that returns multiplerecordsets and the code works.But now I've written another SP and the code traps out with the 3251 message.The SP is writing two recordsets.When I run the SP in Query Analyzer, both recordsets appear.But when I step through the code, when the first RS should be there, it's"Closed" and nothing I've tried will make it open.Provider=SQLOLEDB.1 (which works on the other screen...)Seems like I've been here before, but I can't remember what the problem was.--PeteCresswell
View 7 Replies View RelatedWith the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
Hi,I have 2 stored procedure 1st insert the data in parent tables and return the Id. and second insert child table data using that parent table id as paramenter. I have foreign key relationship between these two tables also.my data layer methods somewhat looks likepublic void Save(order value){using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required)) { int orderId = SaveOrderMaster(value); value.OrderId = orderid; int childId = SaveOrderDetails(value); //complete the transaction transactionScope.Complete(); }}here 1. SaveOrderMaster() calls an stored procedure InserOrderData which insert a new record in order table and return the orderId which is identity column in Order table.2. SaveOrderDetails() call another sotored procedure which insert order details in to table "orderdetail" using the foreign key "orderid".My Problem:Some time the above method works correctly but when i call it repeatledly (in a loop) with data, some time it gives me foreign key error which state that orderid is not existsin table Order. This will happen only randomly. I am not able to figureout the reason. does some one face the same problem. if yes, what could be the reason and/or solution.
View 5 Replies View RelatedDear all,
We have connected a Access to a MS SQL Server 2005 as a Linked Server with the followoing settings:
1) Provider: Microsoft Jet 4.0 OLE DB Provider
2) Product Name: Access
3) Data source: X:XXXXX.mdb
4) Provider string: ;pwd=YYYYYY;
5) Collation Compatible: False
6) Data Access: True
7) Rpc: False
8) Rpc Out: False
9) Use Remote Collation: True
10) Collation Name:
11) Connection Timeout: 0
12) Query Timeout: 0
We found that when cannot have any insert/update/delete statement for this linked server if transaction is began. Otherwise, we will have the following exception.
============
Msg 7390, Level 16, State 2, Line 1
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ZZZZZZ" does not support the required transaction interface.===========
We wonder whether the problem is because Access does not support distributed transaction. If so, do you know any workaround will work for us?
Thanks and regards,
William
I am trying to determine if it is possible to add transaction support for a SSIS package. Here are the basic tasks:
Read New/Updated records from SQL Server
Update changed records in Access
Insert new records into Access
Update new records in SQL Server with Access key assigned on insert in previous step
Reset dirty_flag
I would like to wrap a transaction around this. If the package fails in the task that updates sql server with the Access key, the next time it runs, it duplicates the new records in Access because it did not finish the process. Is it possible to have a transaction wrap this entire process so I can undo the inserts to Access if I fail any subsequent tasks?
Thanks,
Steve
I am attempting to install SQL Server Express 2012. Th setup support rule to "restart computer" continues to fail.
I tried to locate and change PendingFileRenameOperations under HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession Manager.
I am not able to locate PendingFileRenameOperations both when following the registry path and even searching at the HKEY levels.
I want to list out the pending transaction for transaction replication by publication.
Help needed.
Gurus,How do I choose multi-name 'columns' like "Current LSN" or "TransactionID" from ::fn_dblog?I CAN select single 'column' names like 'Operation' and 'Context'.I do NOT want all 'columns' returned:- I don't want Select (*) from ::fn_dblogDoug
View 2 Replies View RelatedHere is the text I do not believe is correct or at the least misleading...
"If you want to terminate the external transaction from within your stored procedure or function. In this case, you call the Transaction.Current.Rollback method. "
You cannot terminate transactions declared in TSQL in SQLCLR routines.
I'm trying to run an SSIS package. The package runs on an SQL 2005 server on Win2k3 server. The package tries to connect to another win2k3 server with sql 2000 to retrieve some data. However, I recieve the errormessage shown in the topic.
I found info about modifying the MSDTC security settings under "component services" and did so. I made sure everything was allowed. However, the result was the same. Does anyone have any other idéa about what could cause this problem?
PS. The package works fine if I set up both databases on the same physical machine...
Regards Andreas
Hi Everyone-
i wonder how can i get the current transaction ID during the running of transaction
thanx
Maylo
I am working on establishing a merge replication process between SQL Server 2005 and SQL Mobile 2005. I started with a new SQL Server 2005 instance and went through the Sample for SQL Mobile merge replication. So far so good.
Today, I tried to drop all support for replication on my current SQL 2005 test instance, so that I can start from a fresh instance and do another. I used Management Studio to drop all publisher and distribution settings and had several errors occur, where some roles were not allowed to be dropped because they had membership in them.
I dropped all the users that I added to the SQL logins and tried again.
Now I am trying to run the following script:
use AdventureWorks
exec sp_replicationdboption @dbname = N'AdventureWorks', @optname = N'merge publish', @value = N'false', @ignore_distributor = 'true'
-- Dropping the distribution publishers
exec sp_dropdistpublisher @publisher = N'XP-MIKED-LAPTOP'
GO
-- Dropping the distribution databases
use master
exec sp_dropdistributiondb @database = N'distribution'
GO
/****** Uninstalling the server XP-MIKED-LAPTOP as a Distributor. Script Date: 1/14/2006 2:16:29 PM ******/
use master
exec sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
GO
The error I am getting from the first batch (sp_replicationdboption) is this:
Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
Invalid object name 'dbo.sysmergesubscriptions'.
To me it looks like all the publication objects have been already removed from AdventureWorks, but in sysdatabases, the category column still says 4 (merge publication). Since I can't just do this anymore:
UPDATE MASTER.DBO.SYSDATABASES
set category = 0
where dbid=8
I just don't know what I can do at this point. I can't even create a new publication in AdventureWorks because it thinks there is a sysmergepublications table in there and fails when there isn't.
Been fiddling with SQL 2008 Nov CTP but now looking to deploy a 3.5 .sdf project using Merge replication. The 3.1 install sets up a web endpoint with sqlcesa30.dll and I have been running subscriptions just fine with that. But now I want to be able to create and manage my 3.5 publicatoin/subscriptions under SQL 2005 management console. I don't see how to create 3.5 sql ce databases there or to use 'configure web synchronization' to get a 3.5 web endpoint for syncing.
Am I missing something simple like registering the SQL CE 3.5 dlls to work with SQL management studio?
I want to install to this a production server with the smallest possible footprint and I do NOT want to inject SQL 2008 Nov CTP bits into that production server at all! So, how to deploy SQL CE 3.5 to production and set up 3.5 merge replication under SQL 2005?
Hi,
I try to setup a transactional push replication from MS SQL Server 2005 to Sybase ASE 12.5.3. ASE is known to MS SQL Server as a linked server using OleDB.
As table schema and data are valid at subscriber site I don't need a initial snapshot for the replication.
So I do some tests with sync_type (sp_addsubscription). First I tried 'none'. That works fine.
But the documentation says that it is depricated an I should use 'replication support only'.
But with this syn_type replication stops because is sends the following commands to the subscriber:
--
-- Transactional replication custom procedures for publication 'kunktable_pub' from database 'kunktest2':
--
----
---- Replication custom procedures will not be scripted for article 'kunktable' because the auto-generate custom procedures schema option is not enabled.
----
IF (@@microsoftversion >= 0x09000000) BEGIN EXEC sys.sp_MSrepl_setNFR @schema = N'dbo', @object_name = N'kunktable' END
Of cause Sybase ASE throws an error 137 'Must declare variable '@@microsoftversion' because it has no system variable @@microsoftversion. Furthermore a procedure sys.sp_MSrepl_setNFR is not known at ASE site.
So it seems, that sync_type ''replication support only' only works for MS subscriber but not for ODBC or OLEDB based subscriber like SYBASE.
Question:
Do I miss something ?
Is this a bug ?
Which sync_type should I use ?
Regards
Wolfgang
I have an SSIS package with its TransactionOption set to 'Required'. A data flow in the package has a script component that performs updates to a database table. The TransactionOption on the data flow is set to 'Supported'. However the script component apparently does no get enlisted to the current transaction because the updates made in the script component do not get rolled back when the package fails.
Documentation about the AquireConnection method indicates passing a 'transaction handle' parm to the AquireConnection method. I'm thinking that I need to pass this 'transaction handle' to the AquireConnection method of the connection used by the script in order for the script to become part of the current transaction.
How do I determine it what the transaction handle is? I have no idea what value I should be passing. I am new to Sql Server beginning with Sql Server 2005. I have no background with DTC.
My code in the script currently is:
dbConnMgr = Me.Connections.ADONetConnection
dbConnection = CType(dbConnMgr.AcquireConnection(Nothing), SqlConnection)
I need to pass the transaction handle rather than 'Nothing' to the AquireConnection method but don't know what the transaction handle is.
Thanks.
D.
I am trying to configure distributed transaction and XA support using Microsoft SQL Server 2005 JDBC Driver. I have coppied SQLJDBC_XA.dll from XA directory and placed in my sql server binn directory and trying to run the script xa_install.sql from binn directory with command as below :
C:Program FilesMicrosoft SQL Server80ToolsBinn>
osql -U sa -n -P admin -S localhost -i C:JavaLibrariesMS SQL Driversqljdbc_1.2enuxa xa_install.sql
But I am getting error saying :
[DBNETLIB]SQL Server does not exist or access denied.
[DBNETLIB]ConnectionOpen (Connect()).
when I replaced local host with the machine name it gives error :
[Shared Memory]SQL Server does not exist or access denied.
[Shared Memory]ConnectionOpen (Connect()).
where in I am able to test connection from Websphere using the same connection.
Please help some one ....... I am in URGENT need.... I need to enable XA suport for my application to run........
Thanks ----
Hi Everyone,
Can anyone help me with this little problem that im stuck with.
As part of the initial snapshot using merge replication, i am creating the initial schema on the subscriber database however it seems as though it does not like UDDTs or defaults and hence does not create them on the subscribers.
Its looking likely that I may have to create a script which executes on the subscriber which creates the initial schema then use merge replication to transfer data.
Does Merge Replication support UDTs/Defaults? If so how does it work?
Ive search many places though have come up with nothing.
Please help. = (
Hi guys,
View 29 Replies View RelatedI am developping a pocket pc windows application, using VS 2005. The SQL Ce that VS 2K5 uses is the mobile SQL CE 2005 (3.0). I uses SQL Server 2000 as the publisher. Is it possible to replicate SQL Ce 3.0 with SQL Server 2000?
View 1 Replies View RelatedHi,
I have one database configured with the Recovery Model "Simple".
I am getting alot of full transaction log messages... is this supposed to happen?
Another question is:
Imagine i am in a middle of a big select into statement... and in another query i run the backup truncate log... am i going to loose information on the other batch ("select into?")??
Kind Regards,
Luis Simões
Hi,
It is possible to write an application which monitors the Microsoft SQL Server 2005 database's transaction log and captures data modified (values after modification) by transactions? Can you point me to some tutorial and API specification?
Thanks,
Rafal
Hi,
It is possible to write an application which monitors the Microsoft SQL Server 2005 database's transaction log and captures data modified (values after modification) by transactions? Can you point me to some tutorial and API specification?
Thanks,
Rafal