I have a system use MS SQL 2005 & .NET 2.0, my tables don't have rowversion, but I heard SQL 2005 manage a rowversion by itself, can I use this to do a "ConflictDetection".
All I try to do is I want to get a error when I try to update a row which been modified by someone else after I read row.
Thanks.
I have a db to manage the creation of invoice number designed for a web application.
My problem is how to manage the concurrency when the users need to create an invoice number.
I have a SQL Server 2005 Express database that was designed to be used by one client. What is the best way to change the design so it can contain multiple clients that can only see data entered by users of each client organization?
Also I'm using the asp.net membership database to handle login and profiles. Can this be used with my multi client database?
Hi all,I've got a question concerning synchronization of multiple database clients.Consider a database, accessed by two clients. Is it guarantted, that ifclient 1 succefully commits a transaction, client 2 _instantly_ sees thechanges made by client 1?thx and regards,StephanP.S..: For MS Jet databases I know there _is_ a delay between writing datato the database and being able to read this updated value from anotherconnection,so the question refers to "real" DBMs (esp. SQL Server) only ;)
View 1 Replies View RelatedWe have a situation where multiple-random client connections to SQL server get disconnected.
The workstations are not consistent, the time is not consistent, and the functions being run are not consistent. One thing that we can reproduce is that sometime, but not always, if a workstation runs a function that calls a specific stored procedure (SQL native client, ADO, SQL Server 2005 SP2), during the course of the parameter validation (after the .execute) SQL sends an ACK, RST followed by two RST TCP packet back to the client and disconnects the connection.
At the same time, several other connections across multiple computers are also dropped by SQL. However, some computers may have multiple connections to SQL and some, but not all of these will be dropped. Other computers will
not have their connections dropped at all.
No SQL server errors are logged. Trace flags 4029 and 3689 have been set.
We have the network packet traces and the SQL profiler output to show this.
This is not reproducable at any other site.
Any ideas on what this could be would be greatly appreciated.
--
Dana Comolli
MS ISV Partner
Hi all,
I've been running SQL Server 2005 for over three months, and have got the database up to speed. Using SSMSE for an interface.
I've have been trying to get the Intranet to link to the database (ASP), but after a little research i found we needed to change the Authentication from 'Windows Authentication' to a mix of windows auth and sql login. Needing to restart the server for the changes to take effect.
After the restart things started to go pear shaped.
Before the restart we were able to run queries from different clients at the same time (4 workstations). Now we are unable to access anything on the database if a query is running on a seperate client. As in table properties, running a query. As i've said, we were able to do this perfectly fine before the restart.
We also use other programs to generate records for the database. These programs now have difficulty connecting to the database. We've gone through the wizards and as far as we are aware things should be working. We have created extra username/logins for these programs so not one computer/client/program uses the same connection login. However, they are unable to connect even after the wizard says there is no problem connecting to the server.
Is there any *restore to factory/default settings* button? We've even tried reverting to Windows Authentication to try and solve the problem, but it didn't work, we're pretty dependant on the database for day to day operations.
Anyone with an idea as to what's wrong?
Thanks in advance
Gary.
Hi Everyone,I have one database file which needs to be accesable to both a .net website and a vb.net application.The application collects data from the 'real world', processes it and adds the data to the database. The Website reads data from the database and displays the data in a graphical format.Now for the problem; While the Application is connected to the database (adding data), the Website is denied acces to the database. All connections to the DB are disposed when the Application is not uploading data, but the Website continues to be locked out (making it pretty useless) Closing the Application makes the Website work fine, but of course this means no 'real world' data can be updated.Can anyone suggest what is happening here, and more importantly how to fix it? A walkthrough must be available for this problem somewhere,... I just can't find it :(Thanks all,*The Stressed One*
View 11 Replies View RelatedI have a script that needs to be run for 50 different @ClientID. I dont want to run this script individually for each clientid. Would 'SET @clientID in (111, 222, 333) work? I've been told that it wouldn't. Short version of the script is.....
DECLARE @ClientID varchar (MAX)
DECLARE @UserID varchar (MAX)
SET @ClientID = 111 -- Replace with Client ID
SET @UserID = 5656 -- Replace with your ID
[Code] .....
I think I will have a problem with Id’s when I do merge replication. The problem is, I have several clients (local shops) who can do additions to a (Customer) table. Other tables will link to that table (things that a customer buys) with their foreign key, so the id of this table is very important. Now, twice a day that table with customers is merged from all clients to the central server. (the table with things that the customer buys, does not needs to be merged as it only remains local).
Possible problem:
if we assign ranges to identifiers for each client (thus local shop), which will be the best option we found until so far, how can we then detect the doubles? (with doubles, we mean that when the same customer is added in two local shops for a first time, then we will have the same customer twice in the central database)(afterwards, when we delete one customer row in the central database, we might have a id conflict when we merge back to the clients -as a customer might have already bought some goods at the two different shops meanwhile-)
What’s the common practice to resolve this?
(we need to do Merge Replication, because during the day, our local shops are disconnected from the central server)
Hi!
We have multiple databases (1 per client) and we want to use reporting services for ad-hoc reporting. Our authentication is done by using client's database so security is custom.
What is the best way to set this up? Is it possible to somehow allow users/admins to use only their database and never see any others? I know we can install a separate instance of RS and do that but I am wondering if there is a better way to run multiple clients doing different reports using exclusively their databases without having to create a new instance.
Thanks
Hello,I'm looking into offering a custom data driven web app that I wrote for an organization that I'm apart of to other similar organizations. I would be hosting the data and web application code on my dedicated server. This application is using the membership api supplied in .NET 2.0 and also has my own custom data tables within it.My question is what would be the best way to add clients to this? Should I simply create a new database for each new client like so: ACME_Database, ABC_Database, AAA_Database etc. Or should I add some sort of client "Tag" (tag meaning column within each datatable) to these databases and then update my SQL queries to process them accordingly. I imagine I could do both but I guess I need some advice from people that already had experiance with providing this kind of service. Thanks!Jason
View 4 Replies View RelatedI have to maintain several SQL Server 7 DBs across multiple NT Domains (same network). How do I access the SQL Server on the second domain from enterprise manager?
Thanks!
Lia
I am using SQL Server 2000.
I am working on a project where there will be multiple databases on a single instance of SQL Server. Each database will have the exact same schema but will be accessed by different groups of users.
What is the easiest way to sychronize changes between the databases, so that if I add a column to one database, it will be reflected in the other databases. If I add, remove, or alter a stored procedure, I want the change to be made in the other databases. I want the data in each database to remain isolated. In other words, I do not want replication of the data, only the schema of the databases. I would like to have a single "master" database that I use to make any schema changes and all the other databases be schema mirrors of this database each with their own data.
I have looked into SQL Server replication, but this didn't seem to work the way I wanted and I wasn't able to publish column changes etc.
Hi ...
This is a C++ / ADO / SQL question. Maybe not the right forum but I am guessing there are some programmers out there ...
I am trying to use ADO disconnected recordset to insert data into a sql table. I am using AddNew(vField, vValue) with UpdateBatch(). The code below does not throw any exceptions ... but does not add data to the table.
Any comments are appreciated,
Thanks,
Chris
void CTestApp::TestDatabaseUpdateBatch1a(void)
{
int nDataCount = 0;
long nIndex = 0;
long nIndex2 = 0;
CString csMessage;
CString csErrorMessage;
CString csTemp;
CString csSQL;
BOOL bIsOpen;
BOOL bIsEmpty;
long nCount = 0;
int nTemp = 0;
int nLimit = 0;
int nTempInt = 0;
long nTempLong = 0;
double nTempDouble = 0;
HRESULT hResult;
SYSTEMTIME st;
int i = 0;
string strTemp;
_variant_t sval;
_variant_t vNull;
vNull.vt = VT_ERROR;
vNull.scode = DISP_E_PARAMNOTFOUND;
COleSafeArray colesaFieldList;
COleSafeArray colesaDataList;
vector<COleSafeArray> *pvecDataList;
pvecDataList = new vector<COleSafeArray>;
COleDateTime oledtCurrentDate = COleDateTime::GetCurrentTime();
// Convert the OleDateTime to the varient
// COleVariant vCurrentDateTime(oledtCurrentDate);
COleVariant vCurrentDateTime;
CMxTextParse *pMxTextParse = NULL;
CMainFrame *pMainFrame = (CMainFrame *)AfxGetMainWnd();
CFrameWnd* pChild = pMainFrame->GetActiveFrame();
CTestMeteorlogixView* pView = (CTestMeteorlogixView*)pChild->GetActiveView();
pView->WriteLog("Start TestDatabaseUpdateBatch1a.");
pView->WriteLog("Load table using AddNew() and UpdateBatch().");
// Define ADO connection pointers
_ConnectionPtr pConnection = NULL;
_RecordsetPtr pRecordset = NULL;
try
{
// When we open the application we will open the ADO connection
pConnection.CreateInstance(__uuidof(Connection));
// Replace Data Source value with your server name.
bstr_t bstrConnect("Provider='sqloledb';Data Source='SQLDEV';"
"Initial Catalog='AlphaNumericData';"
"User Id=cmacgowan;Password=cmacgowan");
// Open the ado connection
pConnection->Open(bstrConnect,"","",adConnectUnspecified);
// Create an instance of the database
pRecordset.CreateInstance(__uuidof(Recordset));
// Select the correct sql string. Note that we are creating an
// empty string by doing a select on the primary key. We are only
// doing inserts and we do not want to bring data back from the
// server
csSQL = "SELECT * FROM dbo.AAMacgowanTest WHERE RecordId IS NULL";
// csSQL = "SELECT * FROM dbo.DICastRaw1Hr";
pRecordset->PutRefActiveConnection(pConnection);
pRecordset->CursorLocation = adUseClient;
pRecordset->Open(csSQL.AllocSysString(), vNull, adOpenStatic, adLockOptimistic, -1);
// Test to see if the recordset is connected
if(pRecordset->GetState() != adStateClosed)
{
// The recordset is connected, we will see if we are
// at the end
if((pRecordset->BOF) && (pRecordset->GetadoEOF()))
{
// The recordset is empty
bIsEmpty = false;
}
if(pRecordset->GetadoEOF())
{
bIsOpen = false;
}
else
{
// disconnect the database
pRecordset->PutRefActiveConnection(NULL);
}
}
// disconnect the database
// pRecordset->PutRefActiveConnection(NULL);
// Disassociate the connection from the recordset.
pRecordset->PutRefActiveConnection(NULL);
// Set the count
nCount = 1;
// now we will scroll through the file
while(nCount > 0)
{
nCount--;
nDataCount = 10;
// test that we got some data
if (nDataCount >= 0)
{
// Start the insert process
// m_pRecordset->AddNew();
COleSafeArray warningList;
//int index, listIndex = -1, bitIndex; // indexing variables
// long lowIndex, highIndex, arrayIndex[2];
VARIANT vFieldList[25];
VARIANT vValueList[25];
int nFieldIndex = 0;
int nValueIndex = 0;
// Setup the fields
vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Name");
nFieldIndex++;
vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Section");
nFieldIndex++;
vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Code");
nFieldIndex++;
vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Latitude");
nFieldIndex++;
vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Longitude");
nFieldIndex++;
pView->WriteLog("Set data using AddNew() ...");
// COleDateTime is a wrapper for VARIANT's DATE type. COleVariant is
// a wrapper for VARIANTs themselves. If you need to create a
// variant, you can say:
COleDateTime oledtCurrentDate2 = COleDateTime::GetCurrentTime();
// Convert the OleDateTime to the varient
COleVariant vCurrentDateTime2(oledtCurrentDate2);
//Set the DATE variant data type.
memset(&st, 0, sizeof(SYSTEMTIME));
st.wYear = 2000;
st.wMonth = 1;
st.wDay = 1;
st.wHour = 12;
// vect is a vector of COleSafeArrays containing the records
for(i = 0; i < 10; i++)
{
// Setup the data
nValueIndex = 0;
vValueList[nValueIndex].vt = VT_BSTR;
vValueList[nValueIndex].bstrVal = ::SysAllocString(L"BLUE");
nValueIndex++;
vValueList[nValueIndex].vt = VT_BSTR;
vValueList[nValueIndex].bstrVal = ::SysAllocString(L"KSTP");
nValueIndex++;
vValueList[nValueIndex].vt = VT_I4;
vValueList[nValueIndex].dblVal = 100 + nFieldIndex;
nValueIndex++;
vValueList[nValueIndex].vt = VT_R8;
vValueList[nValueIndex].dblVal = 11.11 + nFieldIndex;
nValueIndex++;
vValueList[nValueIndex].vt = VT_R8;
vValueList[nValueIndex].dblVal = 22.22 + nFieldIndex;
nValueIndex++;
// Add the record to the recordset
pRecordset->AddNew(vFieldList, vValueList);
}
pView->WriteLog("Call UpdateBatch().");
// Re-connect.
pRecordset->PutRefActiveConnection(pConnection);
// Send updates.
pRecordset->UpdateBatch(adAffectAll);
// Close the recordset and the connection
pRecordset->Close();
pConnection->Close();
}
}
}
catch(_com_error *e)
{
CString Error = e->ErrorMessage();
AfxMessageBox(e->ErrorMessage());
pView->WriteLog("Error processing TestDatabase().");
}
catch(...)
{
csMessage = "Undefined exception handled. Error message details ";
hResult = GetAdoErrorMessage(m_pConnection,
&csErrorMessage);
csMessage += csErrorMessage;
csMessage += "method: CTestMeteorlogixApp::OnTestDatabaseAdoBulkload()";
AfxMessageBox(csMessage);
}
csTemp.Format("Last Row %03d DIcastId = %s ", nIndex, strTemp.c_str());
pView->WriteLog(csTemp);
pView->WriteLog("End TestDatabaseUpdateBatch1.");
}
We're constructing a three-tier application. We want the middle tier to
extract a recordset from the database, disconnect from the database,
then pass the recordset to the front tier. After changes have been made
by the front tier, it will pass the recordset back to the middle tier,
which will reconnect, and update the database.
The problem:
Using stored procedures, the recordset is no longer available
once the connection has been closed. Is there any way, using stored
procedures, to be able to keep the recordset available after the
connection has been closed, either by preserving it, copying it, or
otherwise?
Hi ,
I have maintance plan to rebuld indexes and reorg data on all db
it fails with Error
[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0: This server has been disconnected. You must reconnect to perform this operation.
What could be the problem?
Thank you
Alex
I've an issue while calling Oracle Stored procedure from MS SQL Server 2012 using LINKED SERVER, It gets connected and do the execution, But sometimes, it was disconnected and says this message 'A severe error occurred on the current command. The result if any should be discarded'.
View 2 Replies View Related
Hi,
We have two disconnected tables as shown below.
Table A Table B
Fields Class_NO Class_NameT State Class_No ColA ColB ColC State
Values 1 x S 3 xO e e S
2 XR S 4 UI re er S
9 YU w re S
8 OP we we S
We want to display the data from table A and table B as below.
Class_No Class_Name State Calss_NO Cola ColB ColC State
1 x S 3 xo e e S
2 XR S 4 UI re er S
9 YU w re S
8 OP we we S
Can it be done ? What should be sql query.
Hello,
I'm having a problem with two mirrored databases, using SQL Server 2005 SP2, that autmatically failed over last night for unknown reasons. I was trying to fail them back over to the primary SQL server because it says the mirror is disconnected and out of sync. Other databases failed over too, but I was able to fail those back over without a problem. It's just these two. I removed the mirror from the secondary server thinking that this would allow me to restore the database back on the primary server, but that didn't help.
These two databases show a status of (Principal, Disconnected / In Recovery) and I still cannot do anything with the two databases on the primary server. When I try to pull up the properties for them, I get the following error:
"Database <Name> is enabled for Database Mirroring, but neither the partner nor the witness server instances are available: the database cannot be opened.(Microsoft SQL Server, Error: 955)"
I cannot delete, detach, Alter or do anything with the databases. If I could just delete them that would be fine so I can just do a restore, but I can't seem to do anything.
Does anyone know what I can do to resolve this problem.
Thanks in advance!
-Jay
I have a mobile application written in vb.net using MsSQL and I want to use disconnected datasets to sync up to a remote MySQL database. is this possible? Its a two way sync, i.e I download and upload info.
Any suggestions, articles, etc would be great.
Its been almost 10 years since I have had to do work with good-old RecordSet objects...
I am filling a RecordSet with data returned from a SQL server via a stored procedure. I then set the ActiveConnection property to Nothing in order to disconnect it so I can make changes to it.
But when I try to set the value on a given row I get back a "Multiple-step operation generated errors. Check each status value" error message. My understanding is that this is indicative of trying to use the wrong datatype. I have verified that the type is correct (I am dealing with integers) so I am at a loss for what the problem could be.
Here is my code:
Set rsPackages = CreateObject("ADODB.RecordSet")
rsPackages.CursorLocation = adUseClient
rsPackages.LockType = adLockBatchOptimistic
rsPackages.Open "EXECUTE stp_FetchPackageData", myConn
rsPackages.ActiveConnection = Nothing
Response.Write("Value: " & rsPackages("TotalCount")) ' returns 0
Response.Write("Data Type : " & rsPackages.Fields("TotalCount").Type) ' returns 3 = adInteger
rsPackages("TotalCount") = 1 ' throws multi-step error
Oddly enough, when I look at the Attribute property of the field I get back a value of 112. When you break it down I think that value indicates the row value is read-only? (Could that be my problem? Just a really bad/unhelpful error message?) But if I try to change it I get back a message saying cannot be done since RecordSet is already open.
Thanks,
Jason
I have a .Net database application that we've successfully deployed in a connected environment. Now we have a client that has the need to store data on a central SQL Server and publish that data out to tablet PC's that will be able to disconnect from the central SQL Server. At some point those tablet PC's will come back in and connect to the central server via VPN and will need to push their changes back to the server. Some fundamental questions:
1) Am I correct in assuming that replication is the best way to accomplish this?
If so,
2) Which replication type sounds appropriate to the above scenario?
3) Am I correct in assuming that the tablet PC's will need some version of SQL Server to support editing of the data in the disconnected state?
My perhaps incorrect first take on this was that we could use a licensed SQL Server on the central server and SQL Server Express as a replication subscriber on the tablet PC's.
Any guidance greatly appreciated!
Hi
While a database upgrade schema changes were being made the Mirror became disconnected will this recover itself when it reconnects or will it be the case that we will have to copy the db files to the Mirror and set it up from scratch.
It was set up as a synchronise mirror
Hi All,
I'd like to throw this idea 'out there' to see if I'm missing something I'll later regret.
I'm looking to resolve a scalability issue within our point-of-sale program. Currently the PK on transactional tables (sales and orders) is created by the application layer using a 'MAX(PKCol) + 1' mechanism. Obviously this requires that all users of the system, whether they're local or remote, have current data at any time they wish to insert. It's this limitation I'd like to remove. Most sites are using MS SQL Server 2000. No sites use anything specific to a later version.
By having a PK that can be generated independently of a 'master' database we can overcome this issue. The PK values will need to be unique within a 'group' of shops and able to be generated by a program operating at any level. From 'head office' which manages a number of shops, to the server at a given shop and even the register / till itself should be able to create ID's while disconnected from the server (using a local database).
It seems there's three main ways to accomplish this:
- Identities,
- MachineID, CurrentPK composite.
- GUID's
Identities: I've ruled out identities as I believe the administration overhead of dealing with them makes them impractical (there may be several hundred registers and therefore as many ranges to be set up within a group).
MachineID, CurrentPK composite: The MachineID references a Machine table which has an entry for each ethernet MAC address which connects to the database. The reason I chose to store the MAC in another table rather than simply using it as column is that I'm fetching it from sysprocesses.net_address(nchar(12)) and believe it's computationally cheaper to use an int than a text column. This mechanism means that we can still expose the PK to the user in some cases (eg: InvoiceNumber printed on a receipt). When the local database is not up to date (usually due to network problems) there will be cases where the CurrentPK will be duplicated but kept unique since it's coupled with the new MachineID. The big drawback to this method is that all current code will need to be revised to deal with the composite keys (this will be a significant amount of development).
GUIDs: Ugly to look at and time-consuming to type. They're not something which you'd expose to a user unmodified so realistically this means altering existing code to use a new 'user friendly' number where the PK is currently exposed to them. The use of GUIDs rule-out the use of clustered indecies on tables they're the PK for lest most inserts cause a page split. The splits would also necessitate more frequent index defrags / rebuilds. Using a non-clustered index incurs a penalty Vs a non-fragmented clustered one (doesn't it?) so while this avoids page-splits it comes at a cost.
After all that I think the best solution is to use GUIDs with a non-clustered index for each of the PK's. While it might not be the fastest of the options (slower reads/joins Vs composite PK) it will be significantly faster to develop while maintaining acceptable performance.
Thoughts?
Hello, Code below returns the following error:
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done
<%
Const adLockBatchOptimistic = 4
Const adUseClient = 3
strDataBase = "somedb.mdb"
set cnTraining = server.CreateObject("ADODB.Connection")
cnnstr="Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" & server.mappath(strDataBase) & ";Persist Security Info=False"
cnTraining.Mode = 3
cnTraining.Open cnnstr
set rsSearch = Server.CreateObject("ADODB.Recordset")
rsSearch.CursorLocation = adUseClient
rsSearch.LockType = adLockBatchOptimistic
sSql = "some sql stmt" 'this works fine on its own
rsSearch.Open sSql, cnTraining
set rsSearch.ActiveConnection = nothing
cnTraining.Close
set cnTraining = nothing
%>
My goal is to get a disconnected recordset. The problem here occurs when i try to use the adUseClient value (3) for the CursorLocation. If I don't use a 3 in the CursorLocation it works fine. Hoewever i'm almost certain i have to use the 3 in order to disconnect the recordset. Any ideas? Is my connection string not set up properly to get a disconnected recordset?
We have mirroring set up on SQL 2005(SP2) on windows server 2003 servers for 6 production servers to DR servers. It is high performance mode(asynchonous). We are using fully qualified server names with default 5022 port. Prod and DR are using same domain service account.
We had network outage for about 30 minutes last weekend and after that network was restored back to normal. Also few SQL 2000 servers have logshipping and it automatically started syncing up after network was restored. However mirroring didn't start automatically. Partners were in disconnected state and endpoints were stopped. For one database, log grew to 40 GB using most of the disk space.
Then we maually had to run
ALTER ENDPOINT Mirroring STATE = STARTED
and then it started syncing up.
Now the question is why it doesn't start syncing up automatically after network is back to normal and recognize the partner?
Is there anyway we can setup timeout or any parameter like that to specify how many times or how long partners try to connect with no luck and then give up?
Mintu
We're using ADO disconnected recordsets. On SQL 2000, we could update these on the client (without propagating the changes to the server) even if the underlying view or table was non-updatable.
When running our apps against SQL 2005 (using the same client-side environment), we can no longer change any attributes of those disconnected recordsets, that connect to a non-updatable database object (the rest of the app runs fine, we can update all updatable database objects through disconnected recordsets) . Does SQL 2005 respond to such calls differently from SQL 2000, so that ADO recordsets are built in a new way (which makes them read-only in our setting)?
Thanks for any suggestions.
Rene
Hello all,
View 12 Replies View RelatedI have an update query in an OLE DB Destination (access mode: SQL Command) that updates a table with an INNER JOIN from another table in another database. I'm getting the error, "No disconnected recordset available for the specified SQL statement". Does this have to do with the SQL query trying to access the other database? How can I get around this error?
View 4 Replies View RelatedWhat is the most efficient standalone .NET class that can hold a single disconnected record? The class must also retain column names, but other schema is not relevant (.NET data type is sufficient).If I understand System.Data.Common.DbDataRecord, it provides an interface on a DbDataReader, and has no storage of its own.I'm familiar with DataSet, is that the only .NET-standard class to do this? Thank you,Shannon
View 7 Replies View RelatedI am having trouble setting up my Pull Subscription and I am new to replication.
I have several servers hosting a databased website that will be the same, except for user input and traffic. Quite simply, I need to copy most tables, SPs and data from network to network. I can't use FTP/Web synch ... as I mentioned the networks do not touch eachother or the internet.
On server Web1, it was easy to create a Publication called Pub via the wizard for my database: TheDB. Then on Web1, again, I added a Subscription to the Publication, indicating my second server, Web2, and the same database name: TheDB (I have already backed up and restored TheDB to all my servers). Here's one of the sp's I ran on Web1:
use [TheDB]
exec sp_addsubscription @publication = N'Pub', @subscriber = N'Web2'', @destination_db = N'TheDB', @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'read only'
GO
This is where I feel stuck. Using the wizard on Web2 doesn't allow me to see Web1. So I tried the following on Web2:
use [TheDB]
exec sp_addpullsubscription @publisher = N'Web1', @publication = N'Pub', @publisher_db = N'TheDB', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 1
exec sp_addpullsubscription_agent @publisher = N'Web1', @publisher_db = N'TheDB', @publication = N'Pub', @distributor = N'Web1', @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 0, @active_start_date = 0, @active_end_date = 19950101, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'True', @job_login = null, @job_password = null, @publication_type = 0
GO
I copied the snapshot folder, ie. 20070709134423, onto CD and moved it into Web2's default replication folder, but I always receive: cannot connect to Distibutor. I've tried using an Alias, as well, but don't understand exactly how I should point that either. I checked the publication's PAL and my Web2 user has rights and is an owner of the Web2 TheDB database.
Any help is appreciated.
Nate
I setup an availability Group. (Only 2 servers - Primary And secondary) -- 21 , 22
I also define an listener . IP .. 23
1- In First step I connected To Listener (23) And in a while I inserted A record to a table .
While 1=1
insert into Tbl_T1(f1,f2) Values (1,2)
2- in second, I Stop the primary .
- I expected this while whitout disconnect, continue.
3- The while code stopped whit this message :
Msg 64, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
4- I execute again the script, And it worked in new primary.
My questions :
1- is the listener disconnected between switched primary and secondary ? OR have we data loss between switching?
2- I did some huge update on Primary that fill the Log fiel space. And in last Update I got this error :
Msg 9002, Level 17, State 2, Line 27
The transaction log for database 'Your_DB' is full due to 'LOG_BACKUP'.
Is this (Fill All space) a reason to switch primary? Or not ?
I'm trying to get the last ten unique clients viewed by each user. I have tried using the TOP (N) but it does not show the last ten just any ten.
When a user views a client record, a record is saved to the tblUserRecentViewedClients table and includes logID (key), client_id (int), username (nvarchar), lastviewed (datetime).
Here is what I have so far. Can anyone offer any suggestions?SELECT TOP (10) tblUserRecentViewedClients.UserName, tblUserRecentViewedClients.Client_ID, tblClient.FirstName, tblClient.LastName,
tblClient.CompanyName
FROM tblUserRecentViewedClients INNER JOIN
tblClient ON tblUserRecentViewedClients.client_ID = tblClient.client_ID
WHERE tblUserRecentViewedClients.UserName=@UserName
GROUP BY tblUserRecentViewedClients.UserName, tblUserRecentViewedClients.Client_ID, tblClient.FirstName, tblClient.LastName,
tblClient.CompanyName