I started to play with mirroring with a fresh SQLServer 2005 but I can't get
it to work. This is the scenario:
server1.mydomain - Primary - Hosts "MyDatabase" - Primary Windows 2003
domain controller
server2.mydomain - Future mirror - Secondary Windows 2003 domain controller
server2.mydomain/secondary - a secondary instance for the witness (I don't
have a third machine to test)
1) I backup MyDatabase with SQL Management Studio (SMS).
2) I restore the database from the file (backup.bak) into server2 with
NORECOVERY (SMS shows "Recovering...")
3) From server1 I use the "Configure Security Wizard" to create endpoints in
ports 6000 for server1, 6000 for server2, 6001 for server2/secondary.
Endpoints are succesfully created.
4) All three servers use the same accout (MYDOMAIN\Administrator), used to
create the endpoints.
5) Then in SMS I click "Start Mirroring" and, after 10 sec. aprox, I get an
error "TCP://server1.mydomain.com:6000" could not be reached".
6) In server2, SMS now shows "In recovery" for Mydatabase, but mirroring is
not working. If I click "Start Mirroring" in server1 again, I get a "Neither
the partner nor the witness server instance for database MyDatabase is
available"
What is the problem? From Server1 I tried telneting server2:6000, and
server2:6001 and both connections are accepted. I also tried setting
encryption on/off but I can't fix the errors.
Any ideas? Thanks a lot!
Check the Surface Area configuration and make sure that each instance has
remote connections enabled.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Kirsten" <noreply@.nospam.com> wrote in message
news:OXsjAkVFGHA.2036@.TK2MSFTNGP14.phx.gbl...
>I started to play with mirroring with a fresh SQLServer 2005 but I can't
>get it to work. This is the scenario:
> server1.mydomain - Primary - Hosts "MyDatabase" - Primary Windows 2003
> domain controller
> server2.mydomain - Future mirror - Secondary Windows 2003 domain
> controller
> server2.mydomain/secondary - a secondary instance for the witness (I don't
> have a third machine to test)
> 1) I backup MyDatabase with SQL Management Studio (SMS).
> 2) I restore the database from the file (backup.bak) into server2 with
> NORECOVERY (SMS shows "Recovering...")
> 3) From server1 I use the "Configure Security Wizard" to create endpoints
> in ports 6000 for server1, 6000 for server2, 6001 for server2/secondary.
> Endpoints are succesfully created.
> 4) All three servers use the same accout (MYDOMAIN\Administrator), used to
> create the endpoints.
> 5) Then in SMS I click "Start Mirroring" and, after 10 sec. aprox, I get
> an error "TCP://server1.mydomain.com:6000" could not be reached".
> 6) In server2, SMS now shows "In recovery" for Mydatabase, but mirroring
> is not working. If I click "Start Mirroring" in server1 again, I get a
> "Neither the partner nor the witness server instance for database
> MyDatabase is available"
> What is the problem? From Server1 I tried telneting server2:6000, and
> server2:6001 and both connections are accepted. I also tried setting
> encryption on/off but I can't fix the errors.
> Any ideas? Thanks a lot!
>
|||Remote connections are enabled. Other ideas?
Thanks a lot.
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:%23N5dhRWFGHA.3920@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Check the Surface Area configuration and make sure that each instance has
> remote connections enabled.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Kirsten" <noreply@.nospam.com> wrote in message
> news:OXsjAkVFGHA.2036@.TK2MSFTNGP14.phx.gbl...
don't[vbcol=seagreen]
endpoints[vbcol=seagreen]
to
>
|||Done. Something was wrong with DNS. I tried with TCP://192.168.0.xxx
instead of name and it worked.
Cheers.
"Kirsten" <email@.emailnospam.com> escribi en el mensaje
news:u4H4zBYFGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Remote connections are enabled. Other ideas?
> Thanks a lot.
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:%23N5dhRWFGHA.3920@.TK2MSFTNGP09.phx.gbl...
> don't
> endpoints
> to
>
sql
Showing posts with label sqlserver. Show all posts
Showing posts with label sqlserver. Show all posts
Wednesday, March 21, 2012
Monday, March 12, 2012
Placement of database (newbie question)
I want to create a new database and place it in a drive
and directory other than C:\Program files\microsoft SQL
Server\MSSQL\Data. When I attempt this I get Error 5123:
CREATE FILE encountered operating system error5(Access
denied).....
Must I create the new database in the C: as above? If not
how do I get around the above. I can create files and
folders in that drive from any other program.
The reason for creating in a different drive is space
issues.
Anon,
What account rights is the SQL Server service running with? Are you running
as a local admin? Or a specific account?
What this is telling you is that that account does not have rights to your
destination. You should be able to grant rights to the directory to your
SQL Server account and all will be fine.
The following article on MSDN has a lot of server setup information:
Microsoft SQL Server 2000 Scalability Project-Server Consolidation
http://tinyurl.com/5asz7
Russell Fields
<anonymous@.discussions.microsoft.com> wrote in message
news:506001c4911c$c657a140$a501280a@.phx.gbl...
> I want to create a new database and place it in a drive
> and directory other than C:\Program files\microsoft SQL
> Server\MSSQL\Data. When I attempt this I get Error 5123:
> CREATE FILE encountered operating system error5(Access
> denied).....
> Must I create the new database in the C: as above? If not
> how do I get around the above. I can create files and
> folders in that drive from any other program.
> The reason for creating in a different drive is space
> issues.
and directory other than C:\Program files\microsoft SQL
Server\MSSQL\Data. When I attempt this I get Error 5123:
CREATE FILE encountered operating system error5(Access
denied).....
Must I create the new database in the C: as above? If not
how do I get around the above. I can create files and
folders in that drive from any other program.
The reason for creating in a different drive is space
issues.
Anon,
What account rights is the SQL Server service running with? Are you running
as a local admin? Or a specific account?
What this is telling you is that that account does not have rights to your
destination. You should be able to grant rights to the directory to your
SQL Server account and all will be fine.
The following article on MSDN has a lot of server setup information:
Microsoft SQL Server 2000 Scalability Project-Server Consolidation
http://tinyurl.com/5asz7
Russell Fields
<anonymous@.discussions.microsoft.com> wrote in message
news:506001c4911c$c657a140$a501280a@.phx.gbl...
> I want to create a new database and place it in a drive
> and directory other than C:\Program files\microsoft SQL
> Server\MSSQL\Data. When I attempt this I get Error 5123:
> CREATE FILE encountered operating system error5(Access
> denied).....
> Must I create the new database in the C: as above? If not
> how do I get around the above. I can create files and
> folders in that drive from any other program.
> The reason for creating in a different drive is space
> issues.
Placement of database (newbie question)
I want to create a new database and place it in a drive
and directory other than C:\Program files\microsoft SQL
Server\MSSQL\Data. When I attempt this I get Error 5123:
CREATE FILE encountered operating system error5(Access
denied).....
Must I create the new database in the C: as above? If not
how do I get around the above. I can create files and
folders in that drive from any other program.
The reason for creating in a different drive is space
issues.Anon,
What account rights is the SQL Server service running with? Are you running
as a local admin? Or a specific account?
What this is telling you is that that account does not have rights to your
destination. You should be able to grant rights to the directory to your
SQL Server account and all will be fine.
The following article on MSDN has a lot of server setup information:
Microsoft SQL Server 2000 Scalability Project-Server Consolidation
http://tinyurl.com/5asz7
Russell Fields
<anonymous@.discussions.microsoft.com> wrote in message
news:506001c4911c$c657a140$a501280a@.phx.gbl...
> I want to create a new database and place it in a drive
> and directory other than C:\Program files\microsoft SQL
> Server\MSSQL\Data. When I attempt this I get Error 5123:
> CREATE FILE encountered operating system error5(Access
> denied).....
> Must I create the new database in the C: as above? If not
> how do I get around the above. I can create files and
> folders in that drive from any other program.
> The reason for creating in a different drive is space
> issues.
and directory other than C:\Program files\microsoft SQL
Server\MSSQL\Data. When I attempt this I get Error 5123:
CREATE FILE encountered operating system error5(Access
denied).....
Must I create the new database in the C: as above? If not
how do I get around the above. I can create files and
folders in that drive from any other program.
The reason for creating in a different drive is space
issues.Anon,
What account rights is the SQL Server service running with? Are you running
as a local admin? Or a specific account?
What this is telling you is that that account does not have rights to your
destination. You should be able to grant rights to the directory to your
SQL Server account and all will be fine.
The following article on MSDN has a lot of server setup information:
Microsoft SQL Server 2000 Scalability Project-Server Consolidation
http://tinyurl.com/5asz7
Russell Fields
<anonymous@.discussions.microsoft.com> wrote in message
news:506001c4911c$c657a140$a501280a@.phx.gbl...
> I want to create a new database and place it in a drive
> and directory other than C:\Program files\microsoft SQL
> Server\MSSQL\Data. When I attempt this I get Error 5123:
> CREATE FILE encountered operating system error5(Access
> denied).....
> Must I create the new database in the C: as above? If not
> how do I get around the above. I can create files and
> folders in that drive from any other program.
> The reason for creating in a different drive is space
> issues.
Monday, February 20, 2012
PIVOT operator for variable number of transformations
Hi, i'm trying to port a pivot query from access to sqlserver.
I'm trying this query:
SELECT IDMerce, [1] AS [Department-1], [2] AS [Department-2], [3] AS
[Department-3], [4] AS [Department-4]
FROM (SELECT IDMerce, Pezzi, IDMagazzino
FROM Disponibilita) p PIVOT (sum(Pezzi) FOR
IDMagazzino IN ([1], [2], [3], [4])) AS pvt
this works, but in my case i don't know in advance how many transformations
i need, so there is a solution?
Thanks2005 has support for pivot or "crosstab" queries, though SQL Server 2000 did not. In my opinion, they should have left it that way. Most application interfaces and reporting tools depend upon knowning in advance the layout of the recordsets they are going to receive, and certainly any sql views or procedures must be able to depend on getting consistent recordsets from the objects they call. Dynamic pivots and crosstabs by definition have variable record layouts.
Pivoting the data is arguable a matter of presentation, not data storage or retrieval or business rules. For this reason, you should pull your recordset as a flatfile and let your application or reporting engine handle the pivoting. Most reporting applications (Crystal, Access, Active Reports...) can easily create dynamic crosstabs from datasets, as this is designed as part of their functionality.
I'm trying this query:
SELECT IDMerce, [1] AS [Department-1], [2] AS [Department-2], [3] AS
[Department-3], [4] AS [Department-4]
FROM (SELECT IDMerce, Pezzi, IDMagazzino
FROM Disponibilita) p PIVOT (sum(Pezzi) FOR
IDMagazzino IN ([1], [2], [3], [4])) AS pvt
this works, but in my case i don't know in advance how many transformations
i need, so there is a solution?
Thanks2005 has support for pivot or "crosstab" queries, though SQL Server 2000 did not. In my opinion, they should have left it that way. Most application interfaces and reporting tools depend upon knowning in advance the layout of the recordsets they are going to receive, and certainly any sql views or procedures must be able to depend on getting consistent recordsets from the objects they call. Dynamic pivots and crosstabs by definition have variable record layouts.
Pivoting the data is arguable a matter of presentation, not data storage or retrieval or business rules. For this reason, you should pull your recordset as a flatfile and let your application or reporting engine handle the pivoting. Most reporting applications (Crystal, Access, Active Reports...) can easily create dynamic crosstabs from datasets, as this is designed as part of their functionality.
Subscribe to:
Posts (Atom)