T-SQL (SS2K8) :: Specifying Failover Partner In OpenRowset
Oct 11, 2012
We have a database,which has been mirrored.Also,We have an application which uses OpenRowSet to connect to this database.
Is it possible to set "Failover partner" in OpenRowSet connection string,so when we failover from Prinicple server to the mirrored database,The application still will continue to work?
Example:
select
*
from openrowset(
'SQLOLEDB',
'Data Source=Server1;Failover Partner=Server2;trusted_connection=yes;','select top 10 from Database1.dbo.Table1'
) temp
View 1 Replies
ADVERTISEMENT
May 24, 2006
I need to connect to mirrored SQL servers (Developer Edition) using OLE DB, I tried both OLE DB and ODBC, but it doesn't work
I used connection ODBC string:
Driver={SQL Native Client};Server=10.0.1.161;Failover Partner=10.0.1.162;Uid=test;Pwd=test;Database=TestDB
if server 161 is principal and server 162 mirror, it connects ok, but
when I exchange server roles, connect fails (the error message is:
Cannot open database "TestDB" requested by the login. The login failed.
in LOGIN)
the connect string using OLE DB is:
Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=test;Password=test;Failover Partner=10.0.1.162;Initial
Catalog=TestDB;Data Source=10.0.1.161;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with
column collation when possible=False
error message is the same
when I try to connect using VS 2005 using connection string
Database=TestDB;User Id=test;Password=test;Server=10.0.1.161;Failover
Partner=10.0.1.162, it works OK
i have installed SQL server 2005 (on local - client machine) with SQL Native Client and also
SQL Server service pack 1
Is there any way how to connect from OLE DB?
Thanks
View 3 Replies
View Related
May 10, 2007
Hi
Sincere Apologies for the cross posting. Did not realize that there is a specific DB Mirroring group and so posted initially in the High Availability group. Here is the original post
Hi
I am trying to test DB Mirroring connectivity and running into a road block. using SQLOLDB in my connection string the failover partner keyword seems to be not recognised when the failover occurs and the connectivity fails. The same however works with the SQL Native client driver.
Can any expert please let me know what I am doing wrong and what is the right connection string for the OLEDB one?. I also tried using different flavors of FailoverPartner (like Failover Partner, FailoverPartner etc) to make it work with OLEDB but still could not connect with SQLOLEDB provider.
SQLNCLI works with no issues at all.
Connection string code samples included.
--Code that does not work
Code Snippetconnstring = "Provider=SQLOLEDB;network=dbmssocn;Data Source=Server1SQLInst1;FailoverPartner=Server2SQLInst2;Initial catalog=mydb;INTEGRATED SECURITY=SSPI;"
Code Snippetconnstring = "Provider=SQLNCLI;network=dbmssocn;Data Source=Server1SQLInst1;FailoverPartner=Server2SQLInst2;Initial catalog=mydb;INTEGRATED SECURITY=SSPI;"
Any help is appreciated.
Thanks
AK
View 3 Replies
View Related
Jul 22, 2014
it is possible to create Linked server with Failover partner option. I can query when primary server and getting the error when I set the DB Fail over. I have tried with following script and also gone through different sources, but failed. Please see the script and error below.
EXEC master.dbo.sp_addlinkedserver
@server = N'MIRRORLink',
@srvproduct=N'',
@provider=N'SQLOLEDB',
@provstr=N'Server=primary;FailoverPartner=mirror;network=dbmssocn;',
[code].....
View 2 Replies
View Related
May 22, 2014
I'm trying to access Excel file from SQL Server management studio using OPENROWSET using the below query but getting the error listed below.
Query:
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=E:TestFilesExcelFile.xlsx',
'SELECT * FROM [Sheet1$]'
)
Error:
Msg 7415, Level 16, State 1, Line 2
Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.
View 9 Replies
View Related
Sep 7, 2011
I've created a stored procedure that accepts two arguments: the name of an Excel workbook (@workbookBillRun), and the name of the first worksheet found in that workbook (@worksheetBillRun).
Through dynamic SQL, I'm able to construct a statement that pulls out data.
SELECT @sqlBillRun = '
SELECT [ACCTNUM], [INVNUM], [RECURRING], [NON RECURRING], [USAGE],[DISCOUNT],[TAXES]
FROM OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;;Database=' +@workbookBillRun+ ';HDR=YES'',
''SELECT * from [' +@worksheetBillRun+ '$]'')'
The workbook name will always be known: when the user uploads the file, I change the name to match a certain pattern. Example: 2011-08 AUG.xlsx
However, I won't always know the worksheet name. The workbook *should* contain just one worksheet; and the worksheet *should* be named identically to the workbook (2011-08 AUG), but it may not be named as such. And if I pass an invalid worksheet name, the code above will fail.
I've created error handling to alert the user of an incorrectly named sheet. However, I was curious whether I could extract the name of the first worksheet in the workbook from within T-SQL.
View 6 Replies
View Related
Oct 29, 2015
1. Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.
2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.
3. after fail over done to 2nd secondary node what mode in production(readonly or read write).
4. how to rollback to production primary ,will change data in secondary will get updated in primary.
View 5 Replies
View Related
Mar 3, 2007
We have set up Mirroring with a witness server and everything works fine when we failover from the SQL Management console.
However, if we failover when our Maccola client is connected, the client blows up - clearly because it can no longer connect to the database.
The ODBC DSN used by the Maccola client shows a checkbox for the 'select a failover server' but the checkbox is grayed out.
Also the summary of settings for the DSN at the end of the wizard reveals that the failover to server (y/N) option is set to N.
The default setting for this DSN is 'populate the remaining values by querying the server' but it doesn't appear to be getting the settings for failover from the server or any other interactive DSN settings either. The server is clearly set for mirroring.
Another suspicious item is that the DSN cannot connect to the server with SA permissions, even though the server is set to mixed security and we use the correct authentication.
Is it possible that the client MACHINE is not authenticating with the domain or sql server properly. We are logged into the client with the domain account that is the SQL admin account on the sql server box.
We should be able to interact with the sql server settings through the ODBC DSN on the client shoulnd't we?
Are we missing a service pack on the client?
Thanks,
Kimball
View 1 Replies
View Related
Oct 29, 2015
1. In alwaysON fail over cluster, Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.
2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.
3. After fail over done to 2nd secondary node what mode in production(readonly or read write).
4. How to rollback to production primary ,will change data in secondary will get updated in primary.
View 3 Replies
View Related
Jul 23, 2005
Hi all,Here is the table and DML statmentsCREATE TABLE [jatpartnerMst] ([rowid] [int] ,[partnerid] [int] NULL ,[mcstat] [int] DEFAULT (1), -- 1 Pending ,2 Approved[sf] [varchar] (20))INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(1,1,2,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(2,1,2,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(3,1,2,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(4,1,2,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(5,1,1,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(6,1,2,'inactive')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(7,1,2,'inactive')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(8,1,2,'inactive')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(9,2,2,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(10,2,1,'active')INSERT INTO [jatpartnerMst]([rowid],[partnerid],[mcstat],[sf])VALUES(11,1,2,'active')What I wish to find is the latest record on the top and it's otherrecordse.g If partnerID 1 is changed it goes to the bottom of the table , atany given time I am interested only in max(rowid) for a partner withstat 1 or 2I am using this queryselect * from jatpartnerMst where rowid in (select max(rowid) fromjatpartnermst where mcstat in (1,2) group by partnerid,mcstat )This query does not give me the latest.On using this queryselect * from jatpartnerMst where rowid in (select max(rowid) fromjatpartnermst where mcstat in (1,2) group by partnerid,mcstat )order by rowid descThe partner's two records get seperated . I wish to show them followingone another.So the output should be1112active511active922active1021active11 & 5 rowids are following each other because they are rows of samepartner and 11 is the most recent row [ because new rows are insertedat the end]Is it possible to do the above using single queryI am using cursor to do the same.With Warm regardsJatinder
View 11 Replies
View Related
Apr 25, 2007
Hi, i am working with SMO object,
At the time of accessing remote(LAN) database server I encountered the
following message: "MirroringPartnerInstance" : Undefined error.
I m not able to figure out whats the exact problem is..
i had gone through the following link too but doesn't make any sense to me :
msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.mirroringpartnerinstance.aspx
All i understand is i need to set some property of mirroring partner.
Does any on have any idea how to resolve this problem
Thanking you all in advance
Vivek
View 2 Replies
View Related
Oct 25, 2006
I've read that when this run's, it removes all db mirroring information on that db. What exactly does it remove?
Here's my senario:
We are using SQL 2005€™s db mirroring process. We are using the certificate method of authentication between the principle and the mirror db€™s.
My question is that when the ALTER DATABASE dbname SET PARTNER OFF is run, does it remove these certificate settings as well? In other words when I want to enable the db mirroring, will I need to recreate these certificates or just recreate the endpoints to use these certificates?
View 5 Replies
View Related
Apr 21, 2006
Hi!
I have the following error during setting partner on mirror server
Msg 1431, Level 16, State 4, Line 1
Neither the partner nor the witness server instance for database "masterserver" is available. Reissue the command when at least one of the instances becomes available.
The partner is available through telnet. I've also checked ports vai netstat and have no found errors.
There are two noteworthy erros in the error log at mirror server
Error: 9642, Severity: 16, State: 3.
and
An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')
Security settings it seems are set accurately.
View 1 Replies
View Related
Mar 27, 2008
We have a pair of SQL 2005 SP2 with Rollups clusters. We have a series of DB's that we are migrating from an existing SQL 2000 cluster. I have scripted the process, however on one of the test DB's, it goes to "In Recovery" as soon as I issue the Set Partner statement. There are other DB's on the same cluster mirrored with no problems. As we have a bunch of DB's to migrate, I want to figure out what would cause it to start a recovery. After the initial restores are done, it is in "Restoring" for a status so everything works up to that point.
Thanks
Jon Macy
View 3 Replies
View Related
Oct 25, 2007
Hello,
Am I reading it wrong or can I have a developer edition as a partner?
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx Please take a look at the Database Mirroring and SQL Server 2005 Editions section.
Anyone knows?
Thank you.
View 6 Replies
View Related
Dec 27, 2006
i tried to set up mirroring having only principal and mirror. mirroring endpoints were easily created for both servers. when i try to add the partner name it responds with
Msg 1418, Level 16, State 1, Line 1The server network address "TCP://machine1.domain.com:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.
On the log of machine1 i see,
Database Mirroring login attempt by user 'NT AUTHORITYANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITYANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'.
i have similar problems as in
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1020144&SiteID=1
i am a sysadmin on both SQL Servers and have connect permission on both end points.
Both the endpoints are in started state and listening to all IP's
Both services are as 'Local System'
As per what was mentioned in the above post to add a 'domain/machine$' user did not help.
Tried using setspn.exe to add a new SPN MSSQLsvc/<HOST>:<MIRRORINGPORT> did not work. an ERROR comes 'not enough privileges'
setup shows the following SPN only on both
HOST/Machine name
HOST/Machine name.Domin.com
MSSQLSvc/Machine name.Domin.com:1433
SMTPSVC/Machine name
SMTPSVC/machine name.Domain.com
and on second machine
HOST/Machine name
HOST/Machine name.Domin.com
MSSQLSvc/Machine name.Domin.com:1433
any Clues?
View 1 Replies
View Related
Jan 27, 2006
I am getting this error while trying to setup mirroring.
"Neither the partner nor the witness server instance for database "shop" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"
I am just using principal and mirror server, there is no witness server. I have tried fully qualified name and ip for the servers also. I can connect from the principal server to the mirror server using the management studio and it also creates endpoints on prinicipal and witness. But then gives me an error that it cannot find the instance of the mirror server and if I try again it gives the error above. I can also telnet to port 5022.
I have used -T1400 as the startup parameter.
I did the backup and recovery based on this link http://msdn2.microsoft.com/en-us/library/ms189053.aspx and the mirror server is in recovery mode now.
View 20 Replies
View Related
Jun 23, 2014
Running this query in DR server to start SQL mirroring but encountered an error below.
Query:
use master
go
alter database test set partner= N'TCP://HOSTNAME.DOMAIN.GROUP.INTRANET:5023'
go
Error:
Msg 1452, Level 16, State 6, Line 2
The partner server instance name must be distinct from the server instance that manages the database. The ALTER DATABASE SET PARTNER command failed.
View 2 Replies
View Related
Nov 7, 2006
We were having problems setting up the mirroring, so I did it via command lines. I found out the "alter...set partnership" command works on the mirror server going to the principal, but gets a 1418 error when going from the principal to the mirror.
So if A is the principal and B is the mirror, A to B fails but B to A works.
If I reverse it so that B is the principal and A is the mirror, B to A fails and A to B works.
Any suggestions?
View 3 Replies
View Related
Aug 23, 2006
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
View 3 Replies
View Related
Nov 20, 2002
How come when I run the script:
select * from openrowset ('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=\inawwwpro01d$atrUploadACK102.xls' , 'select * from [sheet1$]') from Server A I get a RESULT, and when I run from Server B I get the following error:
Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server.
Both servers are using IDENTICAL SQL Logins.
Both servers are SQL 2000 SP2.
When I run the script on Server B logged in as SA, then I get a Result!!!
View 4 Replies
View Related
Mar 7, 2005
I can successfully retreive data using:
Declare @Path varchar(100)
Declare @CommandString varchar(100)
Set @Path = ''
Set @CommandString = 'Select * from [myTable.csv]'
EXEC('SELECT *
from OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=\myServermySharedb' + @Path + ';'',''' + @CommandString + ''')')
Does anyone know an easy method to get column names into a table using OpenRowset :confused:
View 1 Replies
View Related
Jan 14, 2008
how can i use openrowset. my aim is to develop a import and export data from diff. server . i am using vb.net 2003 , sql sever 2000.
when i am running
openroeset function with window or server authontication it is showing
'OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any information about the error.
anybuddy help me , pls. tell me the right solution.
View 3 Replies
View Related
Aug 21, 2007
Hi!
I decided to use OPENROWSET for importing data from an excel file into a sql table.When I import the data,I have a problem:
-not all the data is imported to my sql table
-some values in the sql table are different from the ones in the excel file
For example,a value that in the excel file is:87878787 will be in the SQL table:8.78788e+007.
Can somebody pls tell me what's the problem
View 1 Replies
View Related
Jul 23, 2005
Hi Folks,I am trying to load data from a table in MS Access to SQL Server 2000using T-SQL OPENROWSET. When I select data from the remote database (MSAccess) using SQL Query analyzer, the columns do NOT appear in the sameorder as seen in Access directly.For e.g. if Access table has columns Cy, Cx, Cz the output in Queryanalyzer appears as Cx, Cy, Cz. It appears to arrange the fieldsalphabetically. This causes problems when I do a 'insert into select *from' as the field definitions do not agree.Is this a bug or is there a setting in Access/SQL which I am missing?Also, please let me know if there is a workaround for this issue.Thanks in advance!Bhaskar
View 3 Replies
View Related
Oct 15, 2014
I've got some text files that I need to read and make some calculations, before inserting data into a table.
When I use:
Code:
select BulkColumn as txt from openrowset(BULK 'c:Tempdata35.txt', SINGLE_NCLOB) as text
It only retrieves the first row. How can I select all the lines in the file?
View 1 Replies
View Related
Mar 25, 2004
I am trying to use OPENROWSET in SQL Server to connect to my Access database, but I keep getting the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file 'X:SetupDatabaseKDB_X2.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
I have checked the database, it is not in use, and there is no password set on the database that would prevent me from getting access to it. Any ideas? Here's the syntax I'm using:
SELECT *
FROM OpenRowset('Microsoft.Jet.OLEDB.4.0',
'X:SetupDatabaseKDB_X2.mdb';'Admin';'', subPSEL_PList))
View 3 Replies
View Related
Jun 20, 2008
select @xml = bulkcolumn from openrowset(bulk 'C:Documents and
SettingsKasiDesktopewsrss.xml' , single_blob) as channel
here after bulk instead of giving path, we have to give parameter so that the paramter takes the value from the table.
the tables contains paths of xml files
View 2 Replies
View Related
Nov 7, 2006
I having an excel file called TEST.XLS (in c: drive) which has 3 columns (c0,c1,c2) & 8 rows
c0 c1 c2
1 a A
2 b B
3 c C
4 d D
5 e E
6 f F
7 g G
8 h H
In sql server i have one table called TEST which has three columns
c0 char(10)
c1 char(10)
c2 char(10)
when i runnig the following query
select *
into TEST from openrowset('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:TEST.xls;HDR=YES',
'select * from [Sheet1$]')
i am getting error like this
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
Version : Sql server 2000 & Excel 2002
Anybody having any idea why this error occuring?
thanks in advance...
Philkos
View 2 Replies
View Related
Jan 14, 2007
Hi Anyone I am new member and happy to be here.
I am having trouble exporting the correct data from sql server to an excel spreadsheet using 'OPENROWSET'
The problem is that although the data from my sql table is say :
'a',1,2,3 , the excel spreadsheet sees the data as :
'a','1,'2,'3 ..1,2,3 are of course numbers NOT text, its just that the driver has put a single appostraphy before the number !
I know there is a bug with the ISAM driver but has anyone managed to solve this or has anyone have any alternatives ?
best regards
Reza
R Khan
View 3 Replies
View Related
Apr 17, 2007
I'm doing an openrowset query on an excel sheet. (Using SQL Server 2005) Everything works great, except that I have one column that has both numeric and text data in the spreadsheet. The query returns that column as datatype varchar but puts nulls in the rows that have numeric data in the spreadsheet.
Any suggestions?
I run:
select *
from openrowset('microsoft.jet.oledb.4.0',
'Excel 8.0;database=[filepathandname],
'select * from [Sheet1$A4:G5000]')
on spreadsheet data that looks like:
Model_id Model_name
. . .
123 t4556
124 x225
125 455
. . .
and get something like . . . .
Model_id Model_name
. . .
123 t4556
124 x225
125 NULL
. . .
View 1 Replies
View Related
Nov 30, 2007
Hi I have a table like:
LIB TABLENAME TABLEDESCR FIELDNAME FIELDDESCR
BISxxSETALPA0AsometableBS12A0somefield
BISxxSETALPA0AsometablePERCA0somotherfield
BISxxSETALPA1AsomeothertableCMPNA1somefield2
BISxxSETALPA1AsomeothertableMERIA1someotherfield2
BISFFBUAALPA1AanothertablePAARSsomefield3
and would like to create views for each distinct table, using openrowset.
An added complexity is that the library name depends on the company code (i.e. BISxxSET.ALPA0A turns to BIS03SET.ALPA0A for company 03 whereas BSFFBUA.ALPA1A remains intact).
Thanks in advance
Kyriakos
View 4 Replies
View Related
