com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover

Hi all

I have written a shell script that connects to a SQL Server 2005 database from Linux in order to monitor various areas of SQL.

One of the databases that are being monitored is mirrored, which is no problem in itself as I use the failoverPartner property in my connection string before I pass the TSQL. Unfortunately when the principal/mirror status changes, I get a constant stream of Failure Audit (Login failure) messages in the Mirror server Windows event log even though the failoverPartner property works and redirects to the partner, returning the correct information.

Here is a trace of the connection:

22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property : serverName Value:SERVER1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:databaseNameValue:TESTDATABASE
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:failoverPartnerValue:SERVER2
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:integratedSecurityValue:false
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:loginTimeoutValue:3
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connect
FINE: Calling securityManager.checkConnect(SERVER1,1433)
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connect
FINE: securityManager.checkConnect succeeded.
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: Start time: 1172160120083 Time out time: 1172160123083 Timeout Unit Interval: 240
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt server name: SERVER1 port: 1433
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt endtime: 1172160120323
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt No: 0
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connectHelper
FINE: Connecting with server: SERVER1 port: 1433 Timeout slice: 232 Timeout Full: 3
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerException logException
FINE: *** SQLException:[Thread[main,5,main], IO:5571e, Dbc:a8327] com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "TESTDATABASE" requested by the login. The login failed. Msg 4060, Level 11, State 1, Cannot open database "TESTDATABASE" requested by the login. The login failed.
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt server name: SERVER2 port: 1433
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt endtime: 1172160120369
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt No: 1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connectHelper
FINE: Connecting with server: SERVER2 port: 1433 Timeout slice: 237 Timeout Full: 3
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: adding new failover info server: SERVER1 instance: null database: TESTDATABASE server provided failover: SERVER1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.FailoverInfo failoverAdd
FINE: Failover detected. failover partner=SERVER1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.FailoverMapSingleton putFailoverInfo
FINE: Failover map add server: SERVER1; database:TESTDATABASE; Mirror:SERVER1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connect
FINE: End of connect
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerStatement <init>
FINE: Statement properties ID:0 Connection:1 Result type:1003 (2003) Concurrency:1007 Fetchsize:128 bIsClosed:false tdsVersion:com.microsoft.sqlserver.jdbc.TDSVersion@.15fea60 bCp1252:false useLastUpdateCount:true isServerSideCursor:false
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerStatement doExecuteStatement
FINE: Executing (not server cursor)
USE TESTDATABASE
SELECT "TESTCOLUMN" FROM TEST_TABLE ORDER BY DateTime
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection close
FINE: Closing connection ID:1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property : serverName Value:SERVER1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:databaseNameValue:TESTDATABASE
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:failoverPartnerValue:SERVER2
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:integratedSecurityValue:false
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:loginTimeoutValue:3
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connect
FINE: Calling securityManager.checkConnect(SERVER1,1433)
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connect
FINE: securityManager.checkConnect succeeded.
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: Start time: 1172160120736 Time out time: 1172160123736 Timeout Unit Interval: 240
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt server name: SERVER1 port: 1433
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt endtime: 1172160120976
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt No: 0
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connectHelper
FINE: Connecting with server: SERVER1 port: 1433 Timeout slice: 233 Timeout Full: 3
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerException logException
FINE: *** SQLException:[Thread[main,5,main], IO:5571e, Dbc:a8327] com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "TESTDATABASE" requested by the login. The login failed. Msg 4060, Level 11, State 1, Cannot open database "TESTDATABASE" requested by the login. The login failed.
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt server name: SERVER2 port: 1433
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt endtime: 1172160121031
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt No: 1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connectHelper
FINE: Connecting with server: SERVER2 port: 1433 Timeout slice: 236 Timeout Full: 3
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: adding new failover info server: SERVER1 instance: null database: TESTDATABASE server provided failover: SERVER1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.FailoverInfo failoverAdd
FINE: Failover detected. failover partner=SERVER1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.FailoverMapSingleton putFailoverInfo
FINE: Failover map add server: SERVER1; database:TESTDATABASE; Mirror:SERVER1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connect
FINE: End of connect
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerStatement <init>
FINE: Statement properties ID:0 Connection:1 Result type:1003 (2003) Concurrency:1007 Fetchsize:128 bIsClosed:false tdsVersion:com.microsoft.sqlserver.jdbc.TDSVersion@.15fea60 bCp1252:false useLastUpdateCount:true isServerSideCursor:false
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerStatement doExecuteStatement
FINE: Executing (not server cursor)
USE TESTDATABASE
SELECT DATEDIFF(SECOND, DATETIME, GETDATE())
FROM TEST_TABLE
22-Feb-2007 16:02:01 com.microsoft.sqlserver.jdbc.SQLServerConnection close
FINE: Closing connection ID:1

Connect string:

java -classpath /conf/javasql/sqljdbc_1.1/enu/sqljdbc.jar:/conf/javasql/jisql/lib/jisql.jar com.xigole.util
.sql.Jisql -user SOMEUSER -password SOMEPASSWORD -driver com.microsoft.sqlserver.jdbc.SQLServerDriver -input $QUERYFILE -cstring jdbc:sqlserver://SERVER1;DataBaseName=TESTDATABASE;failoverPartner=SERVER2;loginTimeout=3

I cant supply a native database in the connection string (i.e Master) and then switch to the mirrored database in TSQL because the failoverPartner property does not apply to the session, only to the initial connection.

Has anyone got any suggestions?

Thanks

The client will try to connect to the last successfully connected server in the current session. If the database on that server is currently inactive (it is the case when a the current active database becomes inactive and the mirror takes over) a login failure occurs, this gets logged in the windows even log as a login failure event. The driver will catch this and retry the connection to the mirror and the connection succeeds. This behavior is expected. What is your worry here? You do not want to see this error messages in the log? Or you are simply worried about the these messages and wants to understand what is going on.

Mugunthan

Column Name as Variable

Greetings,

I have a table that contains various columns in it totalling 12,000 rows of data. For example;

site_ref, account_title, gl_code, period1, period2, period3 etc through to period12

I wish to write a query that will allow me to search for specific site_ref, acount_title etc and then only one of the period columns. This period column will be specified by the user at the time of submitting the query through reporting services. How do I assign a column to a variable so that the user can set it in the report parameters and then the code will run against that specific column for the period?

Example would be to see everything for site_ref = 'tb', account_title = 'gross rent' and the financial figures within the column titled 'period10' or the next time they run the report they may wish to run it against the values in period7.

Any pointers would be appreciated

Regards

Hello Toni,

You can use dynamic code, look at the exec statement at BOL. Basically, you form the code based on user choices and then pass it to exec statement.
exec 'select * from ATable where site_ref='tb' and period3=3 '. Surf this forum for more info about using "exec", including security issues. I believe this has been discussed a lot.

Let me suggest you another solution. Maybe you should reorganize your table schema like this: remove period columns from 1-12, make one "period" column and add another one, PeriodID. For example, let you have a row with these values:

site_ref, account_title, gl_code, period1, period2, period3 ...
'tb' , 'gross rent' , 1234 , 111 , 222 , 333 ...

After reorganization, you will have 12 rows instead of this one:

site_ref, account_title, gl_code, period, PeriodID
'tb' , 'gross rent' , 1234 , 111 ,1
'tb' , 'gross rent' , 1234 , 222 ,2
'tb' , 'gross rent' , 1234 , 333 ,3
...

This schema is a little bit redundant, but it is indeed much easier for use. Good luck!

|||

Hello and thanks for the response. May I be cheeky and ask how I could achieve your idea. I too was thinking of re-organising the data into the format you suggested, only because historically I have always found it easier to deal with data in this way. The problem I have is that every month this data will change. the figures for the next period will be added to the source table and then this is to be over written into the existing table.

If I were to say have a table as it stands now with site_ref, account_title, gl_code, period1, period2, period3 etc etc which is updated monthly by my accounts team. How can I create a T_SQL statement that would look at the source data as is exampled in the beginning of this paragraph, manipulate the data to the way that you are suggesting. If I understand you right you are looking to take my current 12,000 rows and duplicate them, once for every month and add the figures for the next period into the next set of 12,000 records and then add in the new column period2, 3 etc etc.

Normally I would do this manually in Excel but seen as we are dealing with multiples of 12,000 I would reach the 65K limit in Excel pretty quickly, Plus I would rather the routine had some for of error checking. For example, it would look at the value in the row for three columns, which would always be unique to that row and then place the relevant value for the next period in the value column, and then add the periodID too

Example

Owner,Site_Ref,GL_Code,Account_Type,Period1, Period2, Period3

EXON,TB,1234,Gross Rent,79354,80587,0

EXON,TB,1236,Gross Rent,37000,39000,0

to

Owner,Site_Ref,GL_Code,Account_Type,Value, Period

EXON,TB,1234,Gross Rent,79354,1

EXON,TB,1234,Gross Rent,80587,2

EXON,TB,1234,Gross Rent,0,3

and then the next month the last line of the example would be updated with the value relevant to that period and so on

Thanks for your help so far

Regards

|||

Toni,

Sorry I couldn't answer you earlier.
When answering your question I assumed you can change the schema whatever you want, but it seems that other team is using the table too.
Ask them if you can change the tabse schema. If you can, then just rewrite the existing queries and, then write a "change" script that reads the old table data and inserts all rows from it to the newer one. If you need some help in this, then we could try it together.
If they cannot, then keeping 2 tables, reading the data from one into another might prove as painful as running quieries against the old table "as it is".
I have an impression that I might have understood the case not correctly-if this is true,feel free to reformulate the question and we will try to find an answer.

Column Name as Variable

Greetings,

I have a table that contains various columns in it totalling 12,000 rows of data. For example;

site_ref, account_title, gl_code, period1, period2, period3 etc through to period12

I wish to write a query that will allow me to search for specific site_ref, acount_title etc and then only one of the period columns. This period column will be specified by the user at the time of submitting the query through reporting services. How do I assign a column to a variable so that the user can set it in the report parameters and then the code will run against that specific column for the period?

Example would be to see everything for site_ref = 'tb', account_title = 'gross rent' and the financial figures within the column titled 'period10' or the next time they run the report they may wish to run it against the values in period7.

Any pointers would be appreciated

Regards

Hello Toni,

You can use dynamic code, look at the exec statement at BOL. Basically, you form the code based on user choices and then pass it to exec statement.
exec 'select * from ATable where site_ref='tb' and period3=3 '. Surf this forum for more info about using "exec", including security issues. I believe this has been discussed a lot.

Let me suggest you another solution. Maybe you should reorganize your table schema like this: remove period columns from 1-12, make one "period" column and add another one, PeriodID. For example, let you have a row with these values:

site_ref, account_title, gl_code, period1, period2, period3 ...
'tb' , 'gross rent' , 1234 , 111 , 222 , 333 ...

After reorganization, you will have 12 rows instead of this one:

site_ref, account_title, gl_code, period, PeriodID
'tb' , 'gross rent' , 1234 , 111 ,1
'tb' , 'gross rent' , 1234 , 222 ,2
'tb' , 'gross rent' , 1234 , 333 ,3
...

This schema is a little bit redundant, but it is indeed much easier for use. Good luck!

|||

Hello and thanks for the response. May I be cheeky and ask how I could achieve your idea. I too was thinking of re-organising the data into the format you suggested, only because historically I have always found it easier to deal with data in this way. The problem I have is that every month this data will change. the figures for the next period will be added to the source table and then this is to be over written into the existing table.

If I were to say have a table as it stands now with site_ref, account_title, gl_code, period1, period2, period3 etc etc which is updated monthly by my accounts team. How can I create a T_SQL statement that would look at the source data as is exampled in the beginning of this paragraph, manipulate the data to the way that you are suggesting. If I understand you right you are looking to take my current 12,000 rows and duplicate them, once for every month and add the figures for the next period into the next set of 12,000 records and then add in the new column period2, 3 etc etc.

Normally I would do this manually in Excel but seen as we are dealing with multiples of 12,000 I would reach the 65K limit in Excel pretty quickly, Plus I would rather the routine had some for of error checking. For example, it would look at the value in the row for three columns, which would always be unique to that row and then place the relevant value for the next period in the value column, and then add the periodID too

Example

Owner,Site_Ref,GL_Code,Account_Type,Period1, Period2, Period3

EXON,TB,1234,Gross Rent,79354,80587,0

EXON,TB,1236,Gross Rent,37000,39000,0

to

Owner,Site_Ref,GL_Code,Account_Type,Value, Period

EXON,TB,1234,Gross Rent,79354,1

EXON,TB,1234,Gross Rent,80587,2

EXON,TB,1234,Gross Rent,0,3

and then the next month the last line of the example would be updated with the value relevant to that period and so on

Thanks for your help so far

Regards

|||

Toni,

Sorry I couldn't answer you earlier.
When answering your question I assumed you can change the schema whatever you want, but it seems that other team is using the table too.
Ask them if you can change the tabse schema. If you can, then just rewrite the existing queries and, then write a "change" script that reads the old table data and inserts all rows from it to the newer one. If you need some help in this, then we could try it together.
If they cannot, then keeping 2 tables, reading the data from one into another might prove as painful as running quieries against the old table "as it is".
I have an impression that I might have understood the case not correctly-if this is true,feel free to reformulate the question and we will try to find an answer.