Hi there.
With Client-Side Redirect, connections can be dinamically redirected in case of failover. But if a long transaction is running on the principal, when it fails the redirect doesn´t work with the current ado.net connection, connected to the database running that transaction.
So if the principal fails, current running transaction will be lost right ?
Thanks in advance.
Hi there.
I have a database mirroring session running, and both principal and mirror are working fine. But when i unplug the network cable from principal server, creating a failover scenario, the client doesn´t get redirected to the mirror.
I get an error saying: "A transport-level error has ocurred when received results from the server. The specified network name is no longer available."
connection = "Data Source=SISAD;Failover Partner=Projecto-SWS1;Initial Catalog=SIERE;Persist Security Info=True;User ID=sa;Password=testing;Connection Timeout=100";
//this method is called many times by a thread, emulating some request to the server
public static void Inserir(int codAutor)
{
SqlConnection sqlConnection = new SqlConnection(connection);
SqlCommand command = new SqlCommand("insert into Autor values(@codAutor,'test')", sqlConnection);
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@codAutor", codAutor.ToString());
command.CommandTimeout = 200;
sqlConnection.Open();
command.ExecuteNonQuery();
sqlConnection.Close();
}
I think the error is because the ExecuteNonQuery() method return info about the rows affected on the server. But in this case how can i create a failover scenario with client-side redirect ?
Thanks in advance.
Hi All,
My apologies if this question seems abit basic, but I'm a DBA by trade and programming .Net isn't my strong point ;)
I've enabled database mirroring on 3 SQL 2005 servers, a principal, a mirror and a witness.
Principal - SQL 2005 Enterprise Edition, SP1
Mirror - SQL 2005 Enterprise Edition, SP1
Witness - SQL 2005 Express, SP1
I've written some test code to test the mirroring but as soon as the connection is pulled from the principal, the client re-direct doesn't work and the program bombs. I'd be grateful fi someone could have a look at my code below and tell me if there's any schoolboy errors??
(NB, the user running the code is a sys admin on all 3 servers)
Thanks in advance.
Imports System.Data.SqlClient
Public Class Form1
Dim DCTADS As New SqlClient.SqlConnection
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DCTADS.ConnectionString = "Network=dbmssocn; Server=STUD; Failover Partner=DBDRTEST; Database=mirrortest; Integrated Security=True"
DCTADS.Open()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cmd As New SqlClient.SqlCommand
cmd.CommandText = "Select fulldesc from unclproduct where internal = '20000110'"
cmd.Connection = DCTADS
Dim dr As SqlClient.SqlDataReader
dr = cmd.ExecuteReader
MsgBox(DCTADS.DataSource.ToString)
If dr.Read Then
MsgBox(dr("fulldesc").ToString)
End If
dr.Close()
End Sub
End Class
Hello friends....
my question is as follows:
How do I send the data from the server side to the client side specifically in what format and what kind of connection do I use?
waiting for answers.....
Hi,
In Analysis Services there is an option to enforce a role either on the client side or the server side.
Can someone kindly guide what's the recommended approach and what's really the difference between the two options.
Thanks.
We have some developers who manage SQL2000 servers from their WinXP workstations using Enterprise Manager. We have now rolled out our first SQL Server 2005.
Can we install the SQL2005 Management Studio side-by-side with Enterprise Manager on these WinXP workstations?
If not, what are most people doing in these cases?
Thanks!
Mike Hiland
Hi All,
My apologies if this question seems abit basic, but I'm a DBA by trade and programming .Net isn't my strong point ;)
I've enabled database mirroring on 3 SQL 2005 servers, a principal, a mirror and a witness.
Principal - SQL 2005 Enterprise Edition, SP1
Mirror - SQL 2005 Enterprise Edition, SP1
Witness - SQL 2005 Express, SP1
I've written some test code to test the mirroring but as soon as the connection is pulled from the principal, the client re-direct doesn't work and the program bombs. I'd be grateful fi someone could have a look at my code below and tell me if there's any schoolboy errors??
(NB, the user running the code is a sys admin on all 3 servers)
Thanks in advance.
Imports System.Data.SqlClient
Public Class Form1
Dim DCTADS As New SqlClient.SqlConnection
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DCTADS.ConnectionString = "Network=dbmssocn; Server=STUD; Failover Partner=DBDRTEST; Database=mirrortest; Integrated Security=True"
DCTADS.Open()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cmd As New SqlClient.SqlCommand
cmd.CommandText = "Select fulldesc from unclproduct where internal = '20000110'"
cmd.Connection = DCTADS
Dim dr As SqlClient.SqlDataReader
dr = cmd.ExecuteReader
MsgBox(DCTADS.DataSource.ToString)
If dr.Read Then
MsgBox(dr("fulldesc").ToString)
End If
dr.Close()
End Sub
End Class
Will this work with replication too. I see it with a mirror database, but could it work if I was keeping two copies of the database and wanted to transparently reroute the connection to a surviving server?
View 3 Replies View RelatedI having a difficult time here trying to figure out what to do here.I need a way to scroll through a recordset and display the resultswith both forward and backward movement on a web page(PHP usingADO/COM)..I know that if I use a client side cursor all the records get shovedto the client everytime that stored procedure is executed..if thisdatabase grows big wont that be an issue?..I know that I can set up a server side cursor that will only send therecord I need to the front end but..Ive been reading around and a lot of people have been saying never touse a server side cursor because of peformance issues.So i guess im weighing network performance needs with the client sidecursor vs server performance with the server side cursor..I am reallyconfused..which one should I use?-Jim
View 1 Replies View RelatedI have a DLL that I have written in VB to automate certain functions in Mapinfo. I can use the DLL from Access or a VB project fine but I have problems in SQL Server 6.5. I can create an instance of the DLL ok using sp_OACreate, but when I call the function to create a Mapinfo Object in the DLL I get a VB error back 'Permission Denied' and my object is not created. Even so, the instance of Mapinfo is started (I can view it in my task manager)which I subsequently cannot close because according to the DLL it was never created!! I think this must be a SQL Server related problem since it works fine in VB/Access.
Any Ideas ??
Scott.
avaj writes "I was running SQL on my laptop (xp pro) when I load disk it tells me client side only I say okay and it loads using my local machine as server. This worked fine. Then it didn't. Everything was working; then I logged on and received the error message
"sql server does not exist or access is denied"
I have looked online for help and seen this error elsewhere but never in quite the same context as my problem; and the fixes I've read have not worked.
I've uninstalled, deleted and then reloaded the disk; still did not work. I'm at my wits end - I've been trying to fix this for a week.
Please, please help."
in merge replication using sql server 2005 and sql server compact, is it possible to have triggers (or business logic) for any changes on the client side? how does a client app know an insert/update/delete has been made? also, if the article is set to download-only, can the client app also be nofied of any changes?
thanks,
bryan
Anyone know of any kind of little script or utility that can be placed on an end users PC to backup a specific database whenever they want? All I want it to do is to name it according to the date, place it in a predefined directory, and tell the user that it worked or failed. I am not very good at programing. Also, this is MS SQL 7. This tool needs to be fairly idiot proof. Any ideas?
Thanks,
Tim
hi friends;I have problem about sql server insert and update in client side.I am coding windows application with c#.When I run my code in databaseserver computer all is fine.I can see data, insert and update.But whenI run same code in client side I can see data but not insert notupdate what can I do about this problem .I use sql server 2000database.Client side connections ok. :( thanks for all
View 6 Replies View RelatedWhy I couldn't find any datebase engine setup?
SQL Server 2005 Developer
Windows xp
Hi!
I posted this on the adonet forum but didn't get any response so I might as well post it here. Hope anyone can help me.
-------------------------------------------------------
I have set up mirroring and trying to get it to work from my .NET
application. Mirroring seems to work ok.
When I do a manual failover the witness is updated properly in the
sys.database_mirroring_witnesses table so it seems to be configured
correctly. I also can shutdown the master database and the mirror takes over.
My application is configured with Server set to my master database and
FailOverPartner to my mirror database.
If I am connected to the master database and do a manual fail-over all my
attempts to communicate with the database fail with an exception, even after
repeated retries.
(System.Data.SqlClient.SqlException: A transport-level error has occurred
when sending the request to the server. (provider: TCP Provider, error: 0 -
An existing connection was forcibly closed by the remote host.).)
If I do a manual fail-over and restart my application it connects to the
mirror database and everything is working. If I now do a manual fail-over I
get the same exception, but after a few retries it connects to the master
database.
The field Connection.Datasource always shows the master database regardless
if it's connected to the mirror.
What am I doing wrong? Is this how it's supposed to work?
-------------------------------------------------------
I have been pulling my hair over this problem and I just can't get it to work.
I'm using SQL Server Authentication to access the database and I can connect
to the master when it's acting as principal and I can connect to the mirror
when it's acting as principal, but when doing a fail-over, manual or shutting
down the master server it never tries to connect automatically to the mirror
even if it's changed role to principal.
Do the client need access to the witness in any way?
I'm using version 2.0.50727 of System.Data.dll.
Hi,
We just moved from SQL 2000 to SQL 2005 Ent. To use SSIS service, I need install this package on client side. But when I install the 2005 on my PC(Win XP Prof), I did not find any place I can include it. Anyone has similar experience or know how to do it?
Thanks in advance,
Jie
Hi
I know this is a nasty way of doing but I can't think of another way. I'm retrieving an ID from a treeview when it's clicked client side, and I want to pass this to a SQL Procedure and retrieve some other data relating to it and fill a list box. Can this be done?
Thanks
Lbob
Good Day guys, sorry i'm just new in SQL Server.
I want to know what are the configuration I need
for the client side to connect to SQL Server Express 2005.
Because i'm studying Network Application using SQL Server
Express 2005.
Please help me guys, i really appriciated everything.
Thanks,
Ero-Sennin26
Hi,
I have a problem while rendering a report which returns around 5000 rows. At first the server is busy to process the request, when the server is done the rendering client side takes 100% of CPU and never displays the result ("IE is not responding"). It seems that the ReportViewer has trouble to handle the server response.
Do you have any idea why ?
Sébastien.
Hi everyone,
Primary platform is XP as client side and 2003 as server.
When I press F5 in order to see my report from IE appears this error:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'SQL1.BDADMIN'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
However, preview button works properly and data are showed.
Let me know where am I failing.
Regards,
Until now we worked with SQL Server 7.0, now we're migrating to SQL Server 2005.
With SQL Server 7.0 we had an MS Access application (located on a file server) used by several users, from which's form a batchfile (located on the same server) can be started. The Batchfile deletes an existing TXT-file called import_result.txt, then invokes a DTS-Package (located on a SQL Database server) which imports data to the Access-DB and creates a new file with the result of the DTS-execution (again called import_result.txt). Like that the user gets some information about success (or failure) of the data import.
How could this be implemented with SQL Server 2005 without having to install (and to licencse) SQL Express Engine on the clients? I found some threads about how to invoke an SQL Agent Job from the client to execute the SSIS-Package on the server, but I think that's not exactly what we need. Besides I'm not quite sure how exactly the SQL Agent Job could be invoked from Access.
Do you have any proposals?
Meicee
Hello!
I developed database driven .NET application and I need to deploy it. I faced a problem, which is "how to protect my database against direct access". I use MS SQL Server 2005 Express Edition as a DBMS and appropriate database.
I want to make possible to manipulate with data in my database only through my client application.
1. How do I define SA password and instance name in silent mode of MS SQL 2005 EE installation with Mixed type of Authentication?
1.1. Can I change SA password after the installation?
2. If my database be attached to my new instance... Is it possible to copy my database, attach it to another instance and get a direct access to its objects?
Is there solution that make impossible to connect to my database on third-partie's side Idirectly, without using the client application?
I appriciate for a help.
We are currently testing a Replication topology as follows:
SQL Server 2005 Developer as publisher and distributor of a push updatable transaction replication
2 SQL Server 2005 Express clients as subscribers.
The replication is working great. If the connection is lost, the subscribers switch from Immediate to Queued Updating, and seem to shift back to Immediate when they regain the connection.
My question is to determine if there is a way that I can ask SQL Express on the client which mode is it currently running, or ask SQL Express if it has a current live connection to the distributor.
I know that they are usually mutliple ways to accomplish tasks, and I would be open any suggestions, preferrable programming in VB (such as with SMO or RMO) or using stored procedures or functions.
I hope this give some people something to chew on during the weekend and thank you to all that have taking the time to read this.
I have transaction table I am pulling transactions from and can't quite figure out how to get a sum based on a few items.
I am trying to figure out how to show the sum of the InvestorAssumption and group it based on the DealId, then DealTransType and show the sum of the InvestorAssumption.
So I want to see a total of the InvestorAssumption by quarter split up by DealTransType, then DealId.
Not sure how to do this and any help would be greatly appreciated.
Here is my current view.
Code:
SELECT TOP 100 PERCENT dbo.DealTransactions.DealTransId, dbo.DealTransactions.DealId, dbo.DSGvwInvestorPercentage.InvestorID, DATEADD(quarter,
DATEDIFF(quarter, 0, dbo.DealTransactions.DealTransDate), 0) AS TransDateQuart, dbo.DealTransactions.DealTransType,
dbo.DealTransTypes.DealTransTypeName AS TransactionType, dbo.DealTransactions.DealTransAmount,
dbo.DSGvwInvestorPercentage.InvestorPercentage * dbo.DealTransactions.DealTransAmount / 100 AS InvestorAssumption,
dbo.DSGvwInvestorPercentage.InvestorPercentage, dbo.DealTransactions.DealTransDate AS TransDateActual
FROM dbo.DealTransactions INNER JOIN
dbo.DSGvwInvestorPercentage ON dbo.DealTransactions.DealId = dbo.DSGvwInvestorPercentage.DealID INNER JOIN
dbo.DealTransTypes ON dbo.DealTransactions.DealTransType = dbo.DealTransTypes.DealTransTypeId
GROUP BY dbo.DealTransTypes.DealTransTypeName, dbo.DealTransactions.DealTransAmount, dbo.DealTransactions.DealId,
dbo.DSGvwInvestorPercentage.InvestorID, dbo.DSGvwInvestorPercentage.InvestorPercentage * dbo.DealTransactions.DealTransAmount / 100,
dbo.DealTransactions.DealTransType, dbo.DSGvwInvestorPercentage.InvestorPercentage, dbo.DealTransactions.DealTransId,
dbo.DealTransactions.DealTransDate
ORDER BY dbo.DealTransactions.DealId, dbo.DSGvwInvestorPercentage.InvestorID, dbo.DealTransTypes.DealTransTypeName,
dbo.DealTransactions.DealTransAmount
If the mirror server goes down for any reason, then what happens to client transactions against the principle?
Will any client transactions fail?
Thanks in advance
I am running SQL2k on Win2k sp1. I need to match a transaction record (using txn_date) to client data as it would have appeared on the date of the transaction (using client_last_update).
A new row is inserted into my client table every time any part of the clients information changes, thereby leaving the change history in tact. The client_last_update field holds the date the change took place. It is required that the user be provided the client information that was current on the day of the transaction.
Example: A client record (name, address, accountno) was inserted to the client table on 2/1/2000 (the last_update field is empty because it's a new record).
The client record is modified on 4/15/2000 for a name change (making the last_update = 4/15/2000), and again on 12/5/2000 (making the last_update = 12/5/2000).
Transactions are made by the client on 2/5/2000, 6/10/2000, and 3/25/2001. These dates are held in the txn_date field in the transaction table.
The client now has 3 transaction records in the transaction table for his/her account number and 3 client records in the client table.
The transaction on 2/5/2000 should be joined to the initial client record ((2/5/2000 > empty) AND (2/5/2000 < 4/15/2000))
The transaction on 6/10/2000 should be joined to the client record having the last_update = 4/15/2000 (6/10/2000 > 4/15/2000) AND (6/10/2000 < 12/5/2000))
The last transaction, executed on 3/25/2001 should be joined to the most current client record, which has last_update = 12/5/2000 (3/25/2001 > 12/5/2000).
I have been experimenting with the code below, and found it to only return client data where the client record update_date = '4/18/2000'.
select * from transaction t Left Outer Join client c On t.SEC_ACCT = c.Account_No and t.Txn_Date > coalesce(c.Last_Update_Date, '1/1/1900') and t.Txn_Date < (select min(c2.Last_Update_Date) from client c2 where c2.Last_Update_Date > coalesce(c.Last_Update_Date, '1/1/1900'))
An example of a record that DID get returned carried a txn_date of '1/2/2001', and possible client update_dates of: NULL, '5/13/1999', and '4/18/2000'. The row returned carried a client update_date of '4/18/2001', and rightly so.
An example of a record that DID NOT get returned carried a txn_date = '1/2/2001' and possible client update_dates of: NULL, and '6/11/1999. This row was not returned.
We have just updated our sql 2005 server with sql SP 1. Should we apply this service pack to the client boxes that access this server or is that unnecessary?
TIA,
barkingdog
I have a Windows 2003 Server running IIS 6.0 and SQL Server 2005. I have just (Today) deployed the first ASP.NET application (developed in Visual Studio 2003 and, as such, dependent on .NET Framework 1.1) on this server and am getting HTTP Error 403 (You are not authorized to view this page). This ASP.NET application runs fine on another server that is configured with the OS=Windows Server 2003, IIS=6.0 but without SQL Server 2005 and without the .NET Framework version 2.0. I found in the IIS Application configuration (on the troublesome site) that my app (in the Default Web Site folder) was pointing to the version 2.0 aspnet_isapi.dll. All efforts to use aspnet_regiis to "re-align" my app with version 1.1 have been fruitless to eliminate the HTTP Error 403. I granted the NETWORK SERVICE account privledges to the wwwroot folder. At this point, I don't know if I have a Framework error or a privileges problem.
Please help.
Thanks,
Rob
I am trying to migrate from my current system, where I do merge replication from Windows Mobile devices running SQL Server CE 2.0 to a central database running SQL Server 2000 sp3a. I want eventually to move to a system running SQL Server 2005 CE replicating to a SQL Server 2005 back-end. But the transition will need to be gradual, and I may have to support both systems for a while until I can convert all clients from the old system to the new. I also need to do thorough testing.
So ... I'm trying to set up a test environment giving me the maximum possible flexibility to do my testing. Ideally, I'd like to set up SQL Server 2000 and SQL Server 2005 on a side-by-side basis, in a manner that would potentially allow mobile devices running both SQL Server CE 2.0 and SQL Server 2005 CE to sync with either back-end server.
Can someone provide me with guidance as what is possible to set up here? I know that SQL Server 2000 and 2005 can be installed side-by-side on the same server. It also appears that you can set up SQL Server 2000 so that EITHER SQL Server CE 2.0 OR SQL Server 2005 CE can sync with SQL Server 2000 (see www.microsoft.com/sql/editions/sqlmobile/connectivity-tools.mspx), but I don't know if it's possible for BOTH SQL Server CE 2.0 AND SQL Server 2005 CE to sync to the same SQL Server 2000. As for SQL Server 2005 ... it appears to be possible to set up SQL Server 2005 so that BOTH SQL Server CE 2.0 devices AND SQL Server 2005 CE devices can sync to the same SQL Server 2005 (see web page cited above). However, I don't know if it's possible to set up a SQL Server 2005 server installation in this manner while at the same time having a side-by-side SQL Server 2000 installation supporting any level of mobile merge replication.
HELP!!!!
I'm using SQL Server 2008 R2 BIDS to accomplish this.
View 7 Replies View RelatedI will soon be embarking on an upgrade of a transaction replicated database (Push) from 2005 to 2012 SP2.The publisher, distributor and subscriber are 3 separate machines.There databases will be detached, MDF and LDF will be copied across. Once attached, replication will be set up from scratch.
The name of the new servers are NOT the same as the existing ones.Should I completely remove transaction replication user the wizard and unticking the DB from being a database for transactional replication or just stop the log reader agent?How can I make sure the last of the transactions have gone across before I detach?Should I detach the subscriber first or the publication? Does it even matter?
Is there a better way of moving across the databases? I for one would have preferred to backup (.bak) then restore on the other side. I'd love to hear opinions on this as well.
I am working to create a phone list that will contain Last Name, First Name, and Phone Number sorted by last name. For printing purposes I would like to have three columns of data instead of the standard of one column.
Is it possible to create a query to present data in three columns showing the data side by side?