Strange Login Issue With SQL Server 2005 Mirroring
Mar 27, 2008
Hi,
we have setup synchronous mirroring with witness server.We ran into problem when we stoped the SQLService of Primary Server. Failover Occured and Witness Server made Mirror Server Primary.
The User assosciated with the Database become Orphaned and no one is able to connect to the Database .
We recove login failed error for all login for mirrored database.
We have to manually run the below comand and reset the password for each login.
1.To Detect Orphaned Users
sp_change_users_login @Action='Report';
2.To Resolve an Orphaned User
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';
GO
Please let me know why sql server mirroring show such strange behavior. If we manually failove every thing work fine.
Regards
Sufian
View 3 Replies
ADVERTISEMENT
Apr 16, 2008
Hi all,
Have just tried my sql server 2005 connected asp classic website on II7 for the first time and have gotten some errors.
First off I have unchecked show friendly errors in IE and have enabled ASP to send error messages to the browser.
My error I get is when I execute a basic .asp page:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "mydatabasename" requested by the login. The login failed.
/index.asp, line 10 which is dbConnect.Open cstring
from this peice of code:
sub dbOpen()
set dbConnect = Server.CreateObject("ADODB.Connection")
cstring = "DSN=mysqldsn; Trusted Connection=yes"
dbConnect.Open cstring
end sub
I have gone into ODBC and have setup both a user dsn and file dsn that equals mysqldsn and points to my database (I know you only need to set up one DSN but I'm not sure which)
I also notice under mywebsite home panel there is a connection string option. Do I do anything with that?
Definatley a lot more to it than XP's II6!
Any help or advice would be greatly appreciated.
View 3 Replies
View Related
Nov 24, 2005
create login dave with password='abc', default_database=tempdb
View 6 Replies
View Related
Oct 8, 2007
Hi,
I am using SQL Server 2005 at home.
My problem is I forgot my password to log in to my server.
I only remember user name is 'sa'.
I haven't used it for two to three months. So I forgot password.
Previously, When I used sql server 2000, my login is as windows login. so no problem.
But this time, I set seperate log in and I got this problem.
Any help will be appreciated.
Thanks
George
View 11 Replies
View Related
Feb 26, 2007
Hello everyone,
Since last so many days i am seeing that someone is trying to login to sql server through login name sa.but that login is failed.and this is occuring constantly.
Earlier i had same login failed message for another ip address.i have blocked that ip address through access-list in firewall.but now i am seeing the same message for another ip address.
(1).how can i permenently remove this message from Event log.
(2).What is the impact of this on sql server performance.is sql server performance degraded by this kind of contineous attack?
here i am attaching that report from event viewer log file.
2/26/2007 1:48:18 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:17 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:17 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:17 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:16 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:16 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:16 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:15 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:15 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:15 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:14 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:14 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:14 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:13 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:13 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:12 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:12 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:12 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:11 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:11 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:11 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:10 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:10 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:10 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:09 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:09 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:09 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:08 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:08 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:08 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:07 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:07 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:07 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:06 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:06 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:06 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:05 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:05 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:05 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:04 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:04 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:04 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:03 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:02 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:02 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:02 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:01 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:01 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:01 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:00 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:00 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:48:00 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:59 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:59 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:59 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:58 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:58 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:57 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:57 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:57 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:56 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:56 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:55 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:55 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:54 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:54 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:54 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:53 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:53 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:53 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:52 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:52 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:52 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:51 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:51 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:50 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:50 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:50 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:49 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:49 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:49 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:48 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:48 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:48 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:48 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:47 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:47 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:47 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:46 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:46 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:45 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:45 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:45 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:44 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:44 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:44 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:43 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:43 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
2/26/2007 1:47:43 AM MSSQL$CASHMAX Failure Audit (4) 18456 N/A CASHMAX Login failed for user 'sa'. [CLIENT: 192.192.81.126]
Needs your help to resolve this issue.
Thanks,
Bharat
View 3 Replies
View Related
Nov 27, 2006
I am trying to use a condition in a query with CTE but I got an error.It is simple enough but I just cannot get it work. Any help will be greatly appreciated!
Msg 156, Level 15, State 1, Line 8Incorrect syntax near the keyword 'if'. declare @i as int
set @i=1;
with CTE_A as
(
select 'B'
)
if @i=1 ---this part get problem
select * from CTE_A
else
select 'B'
View 2 Replies
View Related
Jun 23, 2008
Dear Friends
I am trying to create a trriger in sql server 2005 but it gives me error. i am unable to understand the problem . here is detail about it
Database name :- Orders
table name :- Currency_Master
Columns are :- Currency_Symbol, Currency_Name, Value_in_Rs
The create trigger query is
CREATE TRIGGER FOR ORDERS ON CURRENCY_MASTER
AFTER INSERT
AS SELECT 'CURRENCY_SyMBOL'
Can any body tell that why it gives an error and how to rectify it.
Thanks in advance.
Shivpreet2k1
View 5 Replies
View Related
Sep 15, 2006
We have a stored proc that accepts a date and uses that date in a filter. This procedure worked fine for a couple of weeks then started hanging. Basically, this date parameter is used in a where clause.
Our workaround: when we declare a new variable, set it equal to the parameter, then use the declared variable - that fixes it. Take away the declared variable and use the parameter instead and the proc chokes. But the paremeter and the declare variable are both datetime typed. Weird.
We are worried (partly because it worked without hanging for a while) that we are experiencing a larger issue. We are reporting off a database we denormalize and populate nightly with transactional data. A poor man's warehouse if you will, staging the data in a fast reporting format with the prep done each night. This database is wiped and re-populated each night. We have been testing this process with the same source data, so we are not growing our data at all (net result at end of each night is same data as previous day).
I think the source code here is overkill, but I enclosed the proc anyway. The way it is written below, it hangs. Replace these two uses of @AsOf in the proc with @test and the procedure almost instantly returns data. There is a third use of @AsOf that doesn't affect the issue one way or the other. This line is where the replacement fixes the problem:
--Breaks
Where (OpenedDt < DateAdd(day, 1, @AsOf) or ClosedDt < DateAdd(day, 1, @AsOf)) and (PlanType = @PlanType or @PlanType is null)
--Fixed
Where (OpenedDt < DateAdd(day, 1, @test) or ClosedDt < DateAdd(day, 1, @test)) and (PlanType = @PlanType or @PlanType is null)
If anyone has experienced this or knows some other symptoms to check for, we are all ears. This is scaring us because otherwise we have a working system on our hands and are near a delivery point. Help would be greatly appreciated.
Here is the complete proc...
ALTER PROCEDURE [dbo].[ClaimCountsByLineOfBusiness]
-- Add the parameters for the stored procedure here
@AsOf datetime,
@PlanType varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @DataFreshness datetime
Select @DataFreshness = DataFreshness From metaReportList Where StoredProcedure = 'ClaimCountsByLineOfBusiness'
If @AsOf is null
SET @AsOf = Convert(datetime, Convert(varchar(10), GetDate(), 101))
DECLARE @test datetime
SET @test = @AsOf
-- Insert statements for procedure here
Select PolicyGroup, Lines,
SUM(CASE WHEN HasPayment = 1 and OpenClosed <= 0 THEN 1 ELSE 0 END) as ClosedClaimswithPayment,
SUM(CASE WHEN HasPayment = 0 and OpenClosed <= 0 THEN 1 ELSE 0 END) as ClosedClaimswithoutPayment,
SUM(CASE WHEN OpenClosed > 0 THEN 1 ELSE 0 END) as ClaimsOpen,
Count(ClaimSummary.ClaimNumber) as ClaimsReported,
AccidentYear, ReportingYear,
--Return the value of the parameters in a friendly way (if null)
Convert(varchar(10), @Asof, 101) as AsOfParameter,
IsNull(@PlanType, 'any value') as PlanTypeParameter,
@DataFreshness as DataFreshness
FROM
(Select ClaimNumber, Max(OpenedDt) as OpenedDt, HasPayment, PolicyGroup, Lines, AccidentYear, ReportingYear
From dbo.factClaimSummary
Where (OpenedDt < DateAdd(day, 1, @AsOf) or ClosedDt < DateAdd(day, 1, @AsOf)) and (PlanType = @PlanType or @PlanType is null)
Group by ClaimNumber, HasPayment, PolicyGroup, Lines, AccidentYear, ReportingYear) as ClaimSummary
INNER JOIN
(Select ClaimNumber, SUM(CASE WHEN OpenedDt is not null THEN 1 ELSE -1 END) as OpenClosed
From dbo.factClaimSummary
Group by claimnumber) Status ON ClaimSummary.ClaimNumber = Status.ClaimNumber
Group by PolicyGroup, Lines, AccidentYear, ReportingYear
Order by PolicyGroup, Lines
END
View 2 Replies
View Related
Jul 20, 2007
I am working on a proof of concept to take to our Technical Board.
This POC is concearned with SQL server 2005 Mirroring for High Availability and Failover in a Highly Critical business sector.
The problem is that we use custom web pages to give the Operations Center an overview of the status of the system and that there are so many metrics available (not may are well documented). I have looked at the following
sys.database_mirroring_witnesses
is_suspended to show the state of the Sync
database_name to return the name of the mirrored database
safety_level_desc to show that the Safety level is at Full
Principal_server_name to show the name of the Principal server
Mirror_server_name to Show the Name of the Mirror Server.
I am at a loss as to what is the best approach as I do not want to display too much Information and confuse.
The Servers are locked down to remove any permissions not required so I have to create a SQL account with only acces to the required sys views. one issue is that the following SQL only works on the Principal
Code SnippetSelect * from sys.database_mirroring where mirroring_guid is not null
The Principal returns
Code Snippet
7 267CF8D3-8066-44EA-84BD-B7EA25C3F779 4 SYNCHRONIZED 2 MIRROR 20 2 FULL 1 TCP://DNGLHRSQL051.XX.XX.XXX:5022 DNGLHRSQL051 TCP://LHRSQL029.XX.XXX.XXX:5023 1 CONNECTED 40000000010300001 10 NULL UNLIMITED
where the Mirror will return nothing..
The configuration of the User is identical on both Principal and Mirror and is as follows
Master Public
MSDB Public, dbm_monitor
I have read that the User also requires "view any Database" but on the Mirror this database is not available on the Mirror as it is in Sync.
Is this an undocumented Feature?
Any help greatly appreciated
Regards
Andy
View 4 Replies
View Related
Aug 16, 2006
I am trying to set up a SQL mirroring senario - I have some questions
Do I need to purchase an extra SQL Server license for the SQL server instance on the Mirror? what about the Witness does that also need a separate license/
Can the witness be a virtual server on the Mirror box itself? Do I still require a license if the witness is on a virtual server?
Thanks
jawahar
View 3 Replies
View Related
Feb 13, 2006
I saw something strange this morning that I can not work out in my head.
Customer contacted us saying that the login timeout for one of our web apps was timing out.
The login does a simple check of user name and password against a table and inserts the event into a tracking table. I traced the login routine and the query took zero seconds in the QA on the db server. Everything looked fine in the Task Manager and the Performance monitor except that the disk que spiked out when the login page was used. I pinged between the web and database server and that was fine. The disk had plenty of free space for the log and the mdf files. The db had about of 15mb of unallocated space. Nothing seemed to work until I allocated another 100 MB to the db on their test site. Suddenly no more time out. I tested the live site again and it was still happening (same db and web server). Bumped up the db size on the live site and boom no more problem.
The problem is "fixed" for the moment but I can not seem to reason why so I can actually fix the problem in the long run. I do not know why a simple little password routine would care about the difference between 15mb of unallocated space and 115 mb of unallocated space.
View 3 Replies
View Related
Jun 5, 2007
I have SQL 2000 Server that had a database called ABC and it has been moved to another server on 5-15-2007
I kept ABC database in Read_only mode for few days (just in case) on old server and finally dropped it on 5-20-2007 and I think I forgot to drop the associated logins. I started seeing login failure for user 'xyz' in error logs
When I first noticed the login failure error in SQL Server log for login xyz, I deleted xyz login but it did not stop the errors.
I have been trying from then and no luck in identifying the cause/ resolving this issue.
I have ran SQL profiler trace and caught the user hostnames, NTusername in few cases and Application Name and contacted the Application owner & user (who are in the trace) to stop windows service/ schedule jobs..anything that is pointing to old server but the bad luck is they are not aware of anything running or pointing to old server. The worst part is the user whose hostnames are shown in the trace have never used ABC database and do not have any idea.
Here is what I found in the profiler trace:
TextData LoginName NTUserName HostName ApplicationName
Login failed for user 'xyz' xyz AB00007 WAB000007 Microsoft (r) Windows Script Host
Today I have created the xyz login in the server and assigned model database with reader permission to see if log some different error but nothing new (the same login failure error)
Finally, I had no solution other than restoring the ABC database back to my old server and set it in to Read_Only mode to stop these errors and Now I see the login 'xyz' firing the query against the database
* Any hint or pointers why this is happening and any possible solution on this?
View 1 Replies
View Related
Dec 10, 2006
Hello,
Wondering if anyone can help with a strange exception thrown while using the SqlBulkCopy class. I am using the class to transfer records from a DataTable in memory (approx 11,000 rows) into a SQL Server 2005 table.
Initially, the WriteToServer method was timing out a la KB913177 (http://support.microsoft.com/default.aspx/kb/913177), however I downloaded the hotfix, which eliminated this issue.
Now, I get a new exception thrown, as follows:
System.Data.SqlClient.SqlException: OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].Power_Avg'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
I can't see anything wrong with the data I have. The column "Power_Avg" is of type "float". When forming the DataTable, I cast my data to float expcilitly in C#.
Other things to know:
I am using SQL Server Express (2005)
This same code works fine with SQL Server 2000 (MSDE)
My code makes all modifications inside a single transaction of type "Snapshot" (I have activated SNAPSHOT READ COMMITTED in the database)
I have not activated MARS in the connection
Any ideas / suggestions?
Thanks,
Nick
View 7 Replies
View Related
Mar 16, 2007
Hi, im trying to mirror a database with SQL server 2005. I have completed all the setup for this, including attacthing a copy of the principal database and all the other settings. When I completed the Mirroring wizard, it prompted me to start mirroring and when I accepted, it gave to me the following error:
TITLE: Database Properties
------------------------------
An error occurred while starting mirroring.
------------------------------
ADDITIONAL INFORMATION:
Alter failed for Database 'CTM'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server network address "TCP://DBNAME.domain1.local: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. (Microsoft SQL Server, Error: 1418)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476
Can anyone help me withthe problem.
View 3 Replies
View Related
May 10, 2006
Hi....
I have sql server 2005 evaluation edition. And This version doesn't provides Mirroring option.Even after installation of service Pack1. Is there any other edition (Freeware or Evaluation) which provides the Database Mirroring.
waiting for the prompt reply.
Thanks
View 3 Replies
View Related
Jan 24, 2015
I have created a new login in principle server and provided dbowner permission to principle db.
How do I transfer this login to mirror server and assign the same permission to mirror dd?
View 5 Replies
View Related
Mar 8, 2007
Hello, My question is simply: according to BOL, a DB snapshot taken from a Mirror is read-only. Say I wanted a temporary (disposable) read-write DB created from the snapshot. Is there a way to generate a writable temp DB (that's not tempdb) from the snapshot? Thanks,AK
View 1 Replies
View Related
Sep 28, 2007
I am developing an enterprise class solution using SQL Server 2005 and MS .NET v2 and am tying determine if SQL Server 2005 (which edition and if so how) would be adequate for my proposed solution. Any feedback, tips, comments would be greatly appreciated.
As a background the solution I am developing will be web services based and used by multiple offices around the globe by over 500 users. I have already developed a prototype using a single SQL Server 2005 instance but as this solution is going to be used by offices around the world I want to have an IIS Server and SQL Server 2005 server instance in each office with "links" back to the primary SQL Server 2005 cluster in Australia.
One of my thoughts was to set up replication between the offices that would happen at midnight remote office local time and then set up triggers to update the primary cluster when assoociated data was changed on the remote sites or on the primary cluster. Does anyone know or can anyone suggest alternatives to this strategy?
I effectively need some sort of inter site caching functionality with store and foreward capabilities ...
Thanks
Andrew
View 6 Replies
View Related
Sep 27, 2006
Hi All,
I get a general question about the feacture "Database Mirroring". Is it support by SQL Server Standard. Edition without SP1?? Of i still need to upgrade the server from SQL Server 2005 to SQL Server 2005 with SP1?
Another question: are there any problem if the one machine(server) has installed SQL server 2005 Standard edition and the other with "Developer Editon".
I hope some one can answer my question. Thanks anyway!
Regards,
Pat
View 5 Replies
View Related
May 3, 2008
Server A = primary SQL DBs (mirroring origination)
Server B = failover SQL DBs (mirroring destination)
For database mirroring a witness is required.
Can the witness live in another instance of SQL on server B?
View 7 Replies
View Related
May 30, 2006
I am trying to enable database mirroring for 100 database.
It goes error free till 59 databases (some times 60 databases) with the
status (principal, synchronized) on principal. on the 60th or 61st database
it gave the status (principal, disconnected). Also mirror starts acting
abnormal. connection to mirror starts to give connection timeout and it is
not enabling database mirroring on any more databases. I have SQL SERVER
2005 Enterprise with SP1 on the servers. witness is not included yet.
this are my test servers... i have more than 500 databases on my production
servers.
principal and mirror both are using port 5022 for ENDPOINT communication.
View 1 Replies
View Related
Jul 17, 2007
Dear All,
I have a download "SQL Server 2005 Evalution Copy". I installed it to 3 machine, 1 - XP Professional (Witness) and 2 - Windows Server 2000 (Principle and Mirror) . And I want to try Database mirroring.
However, after restore the Log into mirror server I try to execute the following statement in Principle Server:
ALTER DATABASE TestDB
SET PARTNER=N'TCP://[10.0.200.2]:5022'
GO
The above 10.0.200.2 is the mirror server IP address.
it show error message as:
Msg 1416, Level 16, State 31, Line 1
Database "SCMPGroup" is not configured for database mirroring.
In Miorror Server
I execute the statement :
ALTER DATABASE TestDB
SET PARTNER=N'TCP://[10.0.200.1]:5022'
The above 10.0.200.1 is the principle server IP address.
It shows sucess
ALTER DATABASE TestDB
SET WITNESS=N'TCP://[10.0.15.146]:5022'
it show error message as:
Msg 1416, Level 16, State 1, Line 1
Database "SCMPGroup" is not configured for database mirroring.
Please help me! I down't know if NT4 domain or evalution copy problem in there.
View 3 Replies
View Related
Jun 1, 2006
I am trying to enable database mirroring for 100 database.
It goes error free till 59 databases (some times 60 databases) with the
status (principal, synchronized) on principal. on the 60th or 61st database
it gave the status (principal, disconnected). Also mirror starts acting
abnormal. connection to mirror starts to give connection timeout and it is
not enabling database mirroring on any more databases. I have SQL SERVER
2005 Enterprise with SP1 on the servers. witness is not included yet.
these are my test servers... i have more than 500 databases on my production
servers.
principal and mirror both are using port 5022 for ENDPOINT communication.
All of the databases are critical and all must be included in the Database Mirroring.
so, after that I tried to implement database mirroring again......
System has 3 GB of RAM, SQL SERVER (Mirror) using 85 MB of RAM but still
giving this error while trying to enable database mirroring for 37th
Database.....
"There is insufficient system Memory to run this query"
WHY?
View 19 Replies
View Related
Jan 3, 2007
I tried to set up mirroring on my laptop.
I have got two instances of SQL Server 2005 SP2 on my laptop (the first one is the default instance).
Checked configuration running the following scripts
SELECT type_desc, port FROM sys.tcp_endpoints;
GO
SELECT state_desc FROM sys.database_mirroring_endpoints
go
SELECT role FROM sys.database_mirroring_endpoints;
GO
SELECT 'Metadata Check';
SELECT EP.name, SP.STATE,
CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
AS GRANTOR,
SP.TYPE AS PERMISSION,
CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
AS GRANTEE
FROM sys.server_permissions SP , sys.endpoints EP
WHERE SP.major_id = EP.endpoint_id
ORDER BY Permission,grantor, grantee;
GO
Everything is OK on both instances. Tried to start mirroring and I have got this error message:
TITLE: Database Properties
------------------------------
An error occurred while starting mirroring.
------------------------------
ADDITIONAL INFORMATION:
Alter failed for Database 'Northwind'. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The server network address "TCP://kookaburra.sydney.ssw.com.au: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. (Microsoft SQL Server, Error: 1418)
I could connect to both instances:
C:Documents and SettingsSergeiTchernykh.SSW2000>sqlcmd -U sa -P <password> -S k
ookaburra,5022
1> exit
C:Documents and SettingsSergeiTchernykh.SSW2000>sqlcmd -U sa -P <password> -S k
ookaburrasydney2005,5023
1> exit
I could ping my laptop
C:Documents and SettingsSergeiTchernykh.SSW2000>ping -a kookaburra
Pinging kookaburra.sydney.ssw.com.au [10.0.0.1] with 32 bytes of data:
Reply from 10.0.0.1: bytes=32 time<1ms TTL=128
Reply from 10.0.0.1: bytes=32 time<1ms TTL=128
Reply from 10.0.0.1: bytes=32 time<1ms TTL=128
Reply from 10.0.0.1: bytes=32 time<1ms TTL=128
Ping statistics for 10.0.0.1:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
Thank you.
View 4 Replies
View Related
Jan 16, 2007
HI,
Does SQL server 2005 Express Edition or
Does SQL server 2005 Express Edition Sp1 supports Data base Mirroring?
View 3 Replies
View Related
Sep 16, 2005
Can someone at Microsoft comment on this article, specifically, how it relates to SQL Replication? Will SQL Replication fall into the category of what this article describes, or only the mirroring feature?
View 1 Replies
View Related
May 30, 2008
Just installed SQL Server 2005 and tested my old VS 2005 ASPnet websites, which were connected toSQL Server 2000 databases before. All my websites were created for local HTTP applications, coded with a connection string with User ID and Password. The SQL Server 2000 was configured as mixed authentication (i.e. with User ID and Password). These websites worked very well with SQL Server 2000 database server.Testing my old VS 2005 websites with SQL Server 2005 database, showed a 'Login failed for a User ''.' error msgalthough the 'Test Connection' on VS 2005 design showed a perfect query for e.g. a GridView control. I have assured that the UserID/Password were correct for Management Studio Security object'slogin and Database Security's user. The server instance was configured with mixed authentication mode. One thing I am not sure of is, when clicking the Management Studio icon, a Connection dialog showed up, asking for a Windows or MIxed authentication? I just stayed with the Windows authentication. What does the authenticationmode have anything to do with the VS 2005 website connection? Should I change to mixed authetnication mode?TIA,Jeffrey
View 3 Replies
View Related
Sep 19, 2006
I need to create a new login that can only see 2 Views.
I have tried everything, but when I connect to the server with MS Access or MS Excel, and sign in using that Login, I can still a bunch of views. I only want this Login to be able to run either view and not see anything else.
This is what I have done so far:
1) Created Login zzz (w/ SQL Ser Auth)
2) Set the default database to database abc (Nothing is selected in Server Roles)
3) Set User Mapping to database abc, user = zzz
4) Database roll membership is public. (It won't let me change it)
5) Added user zzz to the two Views (Granted Select only)
Now when I connect to the server from Excel
Select new database query, add new data source (connection tests successful)
When I go to select the default table I can see the two views, but I can also see a whole list of other views (all_columns, all_objects, etc..)
It looks like it's everything in the System Views Folder. (Not only can I see the other views, I can query them)
I can individually remove the Public User from each view in the System View folder and it disappears from the default table list..
Do I need to do that for each item in the system views folder? There has to be upwards of 200 views. Anyway to change them all at once?
And then there is the views listed under Information_Schema, the public user isn't setup on those. I can't find a way to get rid of these.
View 6 Replies
View Related
May 9, 2008
I installed SQL Server 2005 Developer edition on my machine a couple months ago, and now I can't remember how to log in. I don't remember setting up any log in accounts, and whatever I might have set I apparently didn't write down.
Are there default user names and passwords that I could use to get in?
I'm a SQL Server newbie.
View 3 Replies
View Related
Apr 17, 2008
In 2005 the security login will not allow me to add a user, database role, application role or schema manually. If I use a Transact-SQL in Query Editor to create a Login with a password, it said it was successful yet The T-SQL added my login as an application role is this correct? and why can I not add manually? is there another function that must be performed first?
I am very new to the environment but know 2000 very well and miss it being so user friendly.
View 3 Replies
View Related
Oct 18, 2007
I just set it up on my laptop, its the express edition.
During setup I specified both auth (windows and login) and wrote down the username and password. I can connect through Management Studio with windows auth but not with the login.
I want to find the username and password. What do I need to do?
Thanks
View 3 Replies
View Related
Jun 12, 2007
I've got something up with SQL logins I think, or may be I'm just not totally au fait with it all, which is equally if not more possible.
Basically I have a login in SQL that is based on an active directory domain account. I wanted to use a domain group, but have experienced login problems with that anyway, so am using a domain user account.
This all works fine.
However, I've had cause to remove this login from the database by doing a DROP LOGIN. This completes okay, but then when I try to connect to SQL (and indeed a particular database) with a client using the login that I've just dropped I am able to not only login to SQL (I can check activity okay for that user) but am also able to execute stuff on the database that previously had a user mapped to the login.
Since both user and login had been dropped, why am I able to get back on eventhough SQL Enterprise Manager and "select * from syslogins" are reporting no login and sp_helplogins reports both no login and no user mappings?
If I just disable the login rather than drop it, everything works as expected and my client applications database action gets bounced.
So, what gives?
Thanks,
CD
View 7 Replies
View Related
Jun 29, 2007
Hello,
I recently installed SQL2005 and SQL2000. I only created SA login permissions with Admin access to the database (There are no Windows Users that have access).
I then installed SQL2000 and it overwrote the "mixed connection mode" . whenever I try to connect with the sa login I get the following error
Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
The problem is explained in a fix http://support.microsoft.com/kb/269587
The only problem is that the fix requires access rights that I don't have. My only logins are "BuiltInUsers" that have public access rights and then a "sa" login (that doesn't work) that has Admin rights within the db engine.
So my question is... Can I create an sysadmin account with windows authentication (for a normal Windows Admin Account) without having the proper rights within the database engine?
-Tom
View 1 Replies
View Related
