32bit sql on 64bit windows?

We have new servers with 16GB RAM. I want the OS and SQL to use as much of it
as possible. I can't put 64bit SQL on the box because the vendor doesn't
support that. Can I install 64bit 2003 and 32bit SQL 2000? If so, will SQL be
able to use all of the memory or just the OS?
Thanks
Roger
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:8796554C-FC5D-44F4-A150-2E867AA9F7AB@.microsoft.com...
> We have new servers with 16GB RAM. I want the OS and SQL to use as much of
> it
> as possible. I can't put 64bit SQL on the box because the vendor doesn't
> support that. Can I install 64bit 2003 and 32bit SQL 2000? If so, will SQL
> be
> able to use all of the memory or just the OS?
>
SQL Server 2000 SP4 is supported on Windows Server 2003 x64 Edition. SQL
Server 2000 Standard Edition is limited to 2GB of RAM. SQL Server 2000
Enterprise Edition on 64bit Windows can use 4GB of ram without AWE, and can
use more with AWE.
David
|||Interesting...
May I ask, how you go about setting up AWE in a 64-bit Windows? Does it need
to be set up from Windows side? Or is it just one needs to do sp_configure
'awe enabled', 1, from SQL Server side, will that be sufficient?
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e8tawkn3GHA.3828@.TK2MSFTNGP06.phx.gbl...
>
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:8796554C-FC5D-44F4-A150-2E867AA9F7AB@.microsoft.com...
> SQL Server 2000 SP4 is supported on Windows Server 2003 x64 Edition. SQL
> Server 2000 Standard Edition is limited to 2GB of RAM. SQL Server 2000
> Enterprise Edition on 64bit Windows can use 4GB of ram without AWE, and
> can use more with AWE.
> David
|||Nothing at the OS level, and same thing with sp_configure 'awe enabled' in
SQL Server.
Linchi
"Sean T Shen" wrote:

> Interesting...
> May I ask, how you go about setting up AWE in a 64-bit Windows? Does it need
> to be set up from Windows side? Or is it just one needs to do sp_configure
> 'awe enabled', 1, from SQL Server side, will that be sufficient?
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:e8tawkn3GHA.3828@.TK2MSFTNGP06.phx.gbl...
>
>
|||So, there is no solution for 32bit SQL 2000 Standard Edition to allow more
than 2GB on Windows 2003 Enterprise 64bit environment with AWE setup?
Alex
"Linchi Shea" wrote:
[vbcol=seagreen]
> Nothing at the OS level, and same thing with sp_configure 'awe enabled' in
> SQL Server.
> Linchi
> "Sean T Shen" wrote:
|||I am having the same issue. 3 new servers,
Quad DualCore Opteron 880 2.4 gHz w/ 16 GB RAM
Windows 2003 R2 Enterprise x64 Edition SP1
SQL Server 2000 EE SP4
AWE Enabled, 14GB Dynamically allocated to SQL Server
Oddly 2 servers are running just fine, but I can't find the difference to
the 3rd and it will not recognize beyond 3.55GB of memory in SQL Server.
Anyone have any experience with this who could offer any suggestions would
be appreciated.

2GB Limit in MSDE 2000

I have a database that has reached the MSDE 2GB limit.
I have tried running the sp_spaceused command and I can't see how the
results equalls 2GB
Can someone explain what values are used by MSDE to determine that the
database has reached the 2GB limit?
Sp_SpaceUsed Resuls:
Database Size 1903.38MB
Unused 0.16MB
Reserved 1947864KB
Data 1038296KB
Index Size 204584KB
unused 704984KB
Hi
Database Size 1903.38MB
If you have it set to 100Mb autogrow, it can't grow any more as it would
exceed 2GB.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"azar" <azar@.discussions.microsoft.com> wrote in message
news:8FBCCD5B-F8C7-4F91-8CCB-F94A1151BD8D@.microsoft.com...
>I have a database that has reached the MSDE 2GB limit.
> I have tried running the sp_spaceused command and I can't see how the
> results equalls 2GB
> Can someone explain what values are used by MSDE to determine that the
> database has reached the 2GB limit?
> Sp_SpaceUsed Resuls:
> Database Size 1903.38MB
> Unused 0.16MB
> Reserved 1947864KB
> Data 1038296KB
> Index Size 204584KB
> unused 704984KB
|||What are you storing in the database that has pushed it to this limit?
Pictures? Documents?
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"azar" <azar@.discussions.microsoft.com> wrote in message
news:8FBCCD5B-F8C7-4F91-8CCB-F94A1151BD8D@.microsoft.com...
>I have a database that has reached the MSDE 2GB limit.
> I have tried running the sp_spaceused command and I can't see how the
> results equalls 2GB
> Can someone explain what values are used by MSDE to determine that the
> database has reached the 2GB limit?
> Sp_SpaceUsed Resuls:
> Database Size 1903.38MB
> Unused 0.16MB
> Reserved 1947864KB
> Data 1038296KB
> Index Size 204584KB
> unused 704984KB

2005 SP1 install, another can't authenticate...

win 2003 sp1 with sql 2005 enterprise.

Tried SA.. Tried AD Admin, tried local administrator. Set up in mixed mode so i tried windows and sql authen... I can log in normally with SA and the password and the windows admin account.

Nothing works, tried services off and on. Tried clearing everything

Reinstalled completely...

Only the SQL Server part fails because it requires authentication. All the other parts succeed.

I don't get a log since it just thinks im typing things wrong. Just this

06/06/2006 21:24:59.641 Authenticating user using Windows Authentication
06/06/2006 21:24:59.641 SQL Service MSSQLServer was previously running, ready for authentication
06/06/2006 21:24:59.641 SQL Agent Service SQLSERVERAGENT was not previously running
06/06/2006 21:24:59.782 User authentication failed. Please check to see if the password provided is correct or check to see if the SQL Server Instance is set to Mixed Authentication
06/06/2006 21:25:07.860 Hotfix package closed

basically this is what I get and i am forced to cancel...

Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790

another log, and yes its set to mixed authentication.

06/07/2006 10:08:25.416
06/07/2006 10:09:48.447 Authenticating user using Windows Authentication
06/07/2006 10:09:48.447 SQL Service MSSQLServer was previously running, ready for authentication
06/07/2006 10:09:48.447 SQL Agent Service SQLSERVERAGENT was previously running
06/07/2006 10:09:48.540 User authentication failed. Please check to see if the password provided is correct or check to see if the SQL Server Instance is set to Mixed Authentication
06/07/2006 10:09:55.572 Authenticating user using SAPWD
06/07/2006 10:09:55.572 SQL Service MSSQLServer was previously running, ready for authentication
06/07/2006 10:09:55.572 SQL Agent Service SQLSERVERAGENT was previously running
06/07/2006 10:09:55.650 User authentication failed. Please check to see if the password provided is correct or check to see if the SQL Server Instance is set to Mixed Authentication
06/07/2006 10:09:59.103 Hotfix package closed

and another

06/07/2006 09:09:47.704 Authenticating user using Windows Authentication
06/07/2006 09:09:47.704 SQL Service MSSQLServer was not previously running, started for authentication
06/07/2006 09:09:47.704 Attempting to start service: MSSQLServer
06/07/2006 09:09:51.579 Successfully started service: MSSQLServer
06/07/2006 09:09:51.579 SQL Agent Service SQLSERVERAGENT was not previously running
06/07/2006 09:09:51.688 User authentication failed. Please check to see if the password provided is correct or check to see if the SQL Server Instance is set to Mixed Authentication
06/07/2006 09:09:51.688 Attempting to stop service: MSSQLServer
06/07/2006 09:09:53.688 Successfully stopped service: MSSQLServer
06/07/2006 09:10:19.470 Hotfix package closed

|||

This was a an upgrade that blew up btw and i removed everything and resinstalled.

I'm not a fan of trying to upgrade... but

I've spent many hours searching and tried profiling and I find I can't connect remotely to the server...

So profiler cannot connect and its set to allow remote connections.

I get...

'===================================

Cannot connect to SERVERNAME.

===================================

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(pfutil90)

Program Location:

at CProfilerSqlDbConnection.Open()
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectorThread()

and this error also

===================================

Cannot connect to SERVERNAME.

===================================

Unknown error. (pfutil90)

Program Location:

at CProfilerSqlDbConnection.Open()
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectorThread()

Update:

Set the trace to use TCPIP and i can connect... The default protocol isnt working for some reason... and of course i can't specify HOW to connect to apply the patch so thats why its failing.

Update:

Can connect fully to DB's from remote PC's Profiler also. So were down the "default" server protocol selection is the trouble... now im trying to find out where I set that. I

|||

still can't get connected...

played around with all the protocols. Shared memory on and off, named pipes, ip etc etc

Still no authorization.

I did get the authorization to "hang" by starting the service manually while the patch tried to authorize.

|||

I'm moving your thread to the Data Access forum. Hopefully those folks will be able to help you debug this problem.

Paul

|||

Can you clarify what is your problem now?

1) remote or local connection?
2) connection string?
3) protocols enabled on the server?
4) firewall issue? put your port number to exception if needed.

Thanks,

|||

Fixed. This actually was the native client.

I had to adjust the registry values for the native client.

|||

What did you adjust? I have the same problem.. or at least the sympoms are the same.

-jr

2005 SP1 install, another can't authenticate...

win 2003 sp1 with sql 2005 enterprise.

Tried SA.. Tried AD Admin, tried local administrator. Set up in mixed mode so i tried windows and sql authen... I can log in normally with SA and the password and the windows admin account.

Nothing works, tried services off and on. Tried clearing everything

Reinstalled completely...

Only the SQL Server part fails because it requires authentication. All the other parts succeed.

I don't get a log since it just thinks im typing things wrong. Just this

06/06/2006 21:24:59.641 Authenticating user using Windows Authentication
06/06/2006 21:24:59.641 SQL Service MSSQLServer was previously running, ready for authentication
06/06/2006 21:24:59.641 SQL Agent Service SQLSERVERAGENT was not previously running
06/06/2006 21:24:59.782 User authentication failed. Please check to see if the password provided is correct or check to see if the SQL Server Instance is set to Mixed Authentication
06/06/2006 21:25:07.860 Hotfix package closed

basically this is what I get and i am forced to cancel...

Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790

another log, and yes its set to mixed authentication.

06/07/2006 10:08:25.416
06/07/2006 10:09:48.447 Authenticating user using Windows Authentication
06/07/2006 10:09:48.447 SQL Service MSSQLServer was previously running, ready for authentication
06/07/2006 10:09:48.447 SQL Agent Service SQLSERVERAGENT was previously running
06/07/2006 10:09:48.540 User authentication failed. Please check to see if the password provided is correct or check to see if the SQL Server Instance is set to Mixed Authentication
06/07/2006 10:09:55.572 Authenticating user using SAPWD
06/07/2006 10:09:55.572 SQL Service MSSQLServer was previously running, ready for authentication
06/07/2006 10:09:55.572 SQL Agent Service SQLSERVERAGENT was previously running
06/07/2006 10:09:55.650 User authentication failed. Please check to see if the password provided is correct or check to see if the SQL Server Instance is set to Mixed Authentication
06/07/2006 10:09:59.103 Hotfix package closed

and another

06/07/2006 09:09:47.704 Authenticating user using Windows Authentication
06/07/2006 09:09:47.704 SQL Service MSSQLServer was not previously running, started for authentication
06/07/2006 09:09:47.704 Attempting to start service: MSSQLServer
06/07/2006 09:09:51.579 Successfully started service: MSSQLServer
06/07/2006 09:09:51.579 SQL Agent Service SQLSERVERAGENT was not previously running
06/07/2006 09:09:51.688 User authentication failed. Please check to see if the password provided is correct or check to see if the SQL Server Instance is set to Mixed Authentication
06/07/2006 09:09:51.688 Attempting to stop service: MSSQLServer
06/07/2006 09:09:53.688 Successfully stopped service: MSSQLServer
06/07/2006 09:10:19.470 Hotfix package closed

|||

This was a an upgrade that blew up btw and i removed everything and resinstalled.

I'm not a fan of trying to upgrade... but

I've spent many hours searching and tried profiling and I find I can't connect remotely to the server...

So profiler cannot connect and its set to allow remote connections.

I get...

'===================================

Cannot connect to SERVERNAME.

===================================

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(pfutil90)

Program Location:

at CProfilerSqlDbConnection.Open()
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectorThread()

and this error also

===================================

Cannot connect to SERVERNAME.

===================================

Unknown error. (pfutil90)

Program Location:

at CProfilerSqlDbConnection.Open()
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectorThread()

Update:

Set the trace to use TCPIP and i can connect... The default protocol isnt working for some reason... and of course i can't specify HOW to connect to apply the patch so thats why its failing.

Update:

Can connect fully to DB's from remote PC's Profiler also. So were down the "default" server protocol selection is the trouble... now im trying to find out where I set that. I

|||

still can't get connected...

played around with all the protocols. Shared memory on and off, named pipes, ip etc etc

Still no authorization.

I did get the authorization to "hang" by starting the service manually while the patch tried to authorize.

|||

I'm moving your thread to the Data Access forum. Hopefully those folks will be able to help you debug this problem.

Paul

|||

Can you clarify what is your problem now?

1) remote or local connection?
2) connection string?
3) protocols enabled on the server?
4) firewall issue? put your port number to exception if needed.

Thanks,

|||

Fixed. This actually was the native client.

I had to adjust the registry values for the native client.

|||

What did you adjust? I have the same problem.. or at least the sympoms are the same.

-jr

2005 selected databas monitoring - how?

I have three databases with same user login. How can I monitor activity only
for one database (logins, transactions) in SQL 2005 server?
I can't change the user for this database.
Hi
You can filter the database by db_id within sql server profiler
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I have three databases with same user login. How can I monitor activity
>only for one database (logins, transactions) in SQL 2005 server?
> I can't change the user for this database.
>
|||Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that it
was in 2000 profiler, but it's like here is some changes in 2005.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
> Hi
> You can filter the database by db_id within sql server profiler
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>
|||No, just check all events and all columns and you will see
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
> Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that
> it was in 2000 profiler, but it's like here is some changes in 2005.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
>
|||Thank You. I found it.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:urV6K7WZHHA.4396@.TK2MSFTNGP06.phx.gbl...
> No, just check all events and all columns and you will see
>
>
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
>

2005 selected databas monitoring - how?

I have three databases with same user login. How can I monitor activity only
for one database (logins, transactions) in SQL 2005 server?
I can't change the user for this database.
Hi
You can filter the database by db_id within sql server profiler
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>I have three databases with same user login. How can I monitor activity
>only for one database (logins, transactions) in SQL 2005 server?
> I can't change the user for this database.
>
|||Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that it
was in 2000 profiler, but it's like here is some changes in 2005.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
> Hi
> You can filter the database by db_id within sql server profiler
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:OFp1vMWZHHA.1220@.TK2MSFTNGP03.phx.gbl...
>
|||No, just check all events and all columns and you will see
"Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
> Unfortunately I can't find DB_ID field in 2005 SQL Profiler. I know that
> it was in 2000 profiler, but it's like here is some changes in 2005.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ePqsQWWZHHA.2552@.TK2MSFTNGP06.phx.gbl...
>
|||Thank You. I found it.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:urV6K7WZHHA.4396@.TK2MSFTNGP06.phx.gbl...
> No, just check all events and all columns and you will see
>
>
>
> "Sergejs Degtjars" <sergeydeg@.inbox.lv> wrote in message
> news:uwTjr1WZHHA.5080@.TK2MSFTNGP02.phx.gbl...
>

2005 restored msdb, cant see SSIS packages ?

We recently rebuilt a dev server, and as part of that process, I restored
msdb (successfully) from backup.
But now I can't see any SSIS packages from Management Studio !
If I run this query ->
use msdb
select *
from sysdtspackages90
then I can see them all !
How do I get Management Studio to see them as well ?
TIAHi Jim
My guess this is possibly related to ownership. Did you restore the master
database as well?
John
"Jim Trowbridge" wrote:
> We recently rebuilt a dev server, and as part of that process, I restored
> msdb (successfully) from backup.
> But now I can't see any SSIS packages from Management Studio !
> If I run this query ->
> use msdb
> select *
> from sysdtspackages90
> then I can see them all !
> How do I get Management Studio to see them as well ?
> TIA|||John,
I didn't restore master. It turned out to be a problem with the registry
setting that points to the (cluster resource copy of the ) MsDtsSrvr.ini.xml
file had the wrong address. Thats why the tree didn't even have the usual
File System and MSDB sub-tree entries.
Once that registry setting was fixed and the Integration Services service
was bounced, everything behaved properly.
"John Bell" wrote:
> Hi Jim
> My guess this is possibly related to ownership. Did you restore the master
> database as well?
> John
> "Jim Trowbridge" wrote:
> > We recently rebuilt a dev server, and as part of that process, I restored
> > msdb (successfully) from backup.
> > But now I can't see any SSIS packages from Management Studio !
> > If I run this query ->
> > use msdb
> > select *
> > from sysdtspackages90
> > then I can see them all !
> >
> > How do I get Management Studio to see them as well ?
> > TIA|||Hi Jim
Thanks for the information, it's good to know you found the solution.
John
"Jim Trowbridge" wrote:
> John,
> I didn't restore master. It turned out to be a problem with the registry
> setting that points to the (cluster resource copy of the ) MsDtsSrvr.ini.xml
> file had the wrong address. Thats why the tree didn't even have the usual
> File System and MSDB sub-tree entries.
> Once that registry setting was fixed and the Integration Services service
> was bounced, everything behaved properly.
> "John Bell" wrote:
> > Hi Jim
> >
> > My guess this is possibly related to ownership. Did you restore the master
> > database as well?
> >
> > John
> >
> > "Jim Trowbridge" wrote:
> >
> > > We recently rebuilt a dev server, and as part of that process, I restored
> > > msdb (successfully) from backup.
> > > But now I can't see any SSIS packages from Management Studio !
> > > If I run this query ->
> > > use msdb
> > > select *
> > > from sysdtspackages90
> > > then I can see them all !
> > >
> > > How do I get Management Studio to see them as well ?
> > > TIA

2005 replication subscriber can't see 2000 publisher - why?

I enabled publishing on the SQL 2000 box so that I can do replication from
2000 to 2005. During the wizard setup, the 2005 subscriber gets this error:
---
Cannot connect to Server1. Additional information: Failed to connection to
server Server1 (Microsoft.SqlServer.ConnectionInfo) An error has occurred
while establishing a connection tot he server. When connecting to SQL
Server 2005, this failure may be caused by the fact that under the default
settings SQL Server does not allow remote connections. (provider: Named
Pipes Provider, error 40 - Could not open a connection to SQL Server)
(Microsoft SQL Server, Error: 52).
---
Both boxes are on the same LAN subnet with no firewall between them. I
added an entry to my HOSTS file on the 2005 server so it can see the 2000
server by name (the wizard won't let you type in an IP address). Pinging
that machine works fine.
I don't know why this happens. I've tried windows auth and SQL auth.
What's the trick? Is 2005 using some new communications method that 2000
doesn't understand? I don't even know of a log file that would help but
maybe someone knows the answer or where there is some log info.Open the Surface Area Configuration on the 2005 machine and enable remote
connections. By default a newly installed SQL Server 2005 instance will
only respond to requests sent from the physical machine it was installed on.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"HK" <replywithingroup@.notreal.com> wrote in message
news:ZSbKf.13005$Ou1.8109@.tornado.socal.rr.com...
>I enabled publishing on the SQL 2000 box so that I can do replication from
> 2000 to 2005. During the wizard setup, the 2005 subscriber gets this
> error:
> ---
> Cannot connect to Server1. Additional information: Failed to connection
> to
> server Server1 (Microsoft.SqlServer.ConnectionInfo) An error has occurred
> while establishing a connection tot he server. When connecting to SQL
> Server 2005, this failure may be caused by the fact that under the default
> settings SQL Server does not allow remote connections. (provider: Named
> Pipes Provider, error 40 - Could not open a connection to SQL Server)
> (Microsoft SQL Server, Error: 52).
> ---
> Both boxes are on the same LAN subnet with no firewall between them. I
> added an entry to my HOSTS file on the 2005 server so it can see the 2000
> server by name (the wizard won't let you type in an IP address). Pinging
> that machine works fine.
> I don't know why this happens. I've tried windows auth and SQL auth.
> What's the trick? Is 2005 using some new communications method that 2000
> doesn't understand? I don't even know of a log file that would help but
> maybe someone knows the answer or where there is some log info.
>|||On the subscriber machine? That sounds like something I would do on the
publisher but the publisher is SQL 2000.
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:uHHA4ZpNGHA.3460@.TK2MSFTNGP15.phx.gbl...
> Open the Surface Area Configuration on the 2005 machine and enable remote
> connections. By default a newly installed SQL Server 2005 instance will
> only respond to requests sent from the physical machine it was installed
on.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:ZSbKf.13005$Ou1.8109@.tornado.socal.rr.com...
> >I enabled publishing on the SQL 2000 box so that I can do replication
from
> > 2000 to 2005. During the wizard setup, the 2005 subscriber gets this
> > error:
> >
> > ---
> > Cannot connect to Server1. Additional information: Failed to
connection
> > to
> > server Server1 (Microsoft.SqlServer.ConnectionInfo) An error has
occurred
> > while establishing a connection tot he server. When connecting to SQL
> > Server 2005, this failure may be caused by the fact that under the
default
> > settings SQL Server does not allow remote connections. (provider:
Named
> > Pipes Provider, error 40 - Could not open a connection to SQL Server)
> > (Microsoft SQL Server, Error: 52).
> > ---
> >
> > Both boxes are on the same LAN subnet with no firewall between them. I
> > added an entry to my HOSTS file on the 2005 server so it can see the
2000
> > server by name (the wizard won't let you type in an IP address).
Pinging
> > that machine works fine.
> >
> > I don't know why this happens. I've tried windows auth and SQL auth.
> > What's the trick? Is 2005 using some new communications method that
2000
> > doesn't understand? I don't even know of a log file that would help but
> > maybe someone knows the answer or where there is some log info.
> >
> >
>|||The setting Mike told you about is to allow the remote machine (publisher)
to connect to the local (Subscriber - 2005) machine. It is from the
viewpoint of the 2005 machine.
--
Andrew J. Kelly SQL MVP
"HK" <replywithingroup@.notreal.com> wrote in message
news:J9HKf.8983$Jg.197@.tornado.socal.rr.com...
> On the subscriber machine? That sounds like something I would do on the
> publisher but the publisher is SQL 2000.
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:uHHA4ZpNGHA.3460@.TK2MSFTNGP15.phx.gbl...
>> Open the Surface Area Configuration on the 2005 machine and enable remote
>> connections. By default a newly installed SQL Server 2005 instance will
>> only respond to requests sent from the physical machine it was installed
> on.
>> --
>> Mike
>> http://www.solidqualitylearning.com
>> Disclaimer: This communication is an original work and represents my sole
>> views on the subject. It does not represent the views of any other
>> person
>> or entity either by inference or direct reference.
>>
>> "HK" <replywithingroup@.notreal.com> wrote in message
>> news:ZSbKf.13005$Ou1.8109@.tornado.socal.rr.com...
>> >I enabled publishing on the SQL 2000 box so that I can do replication
> from
>> > 2000 to 2005. During the wizard setup, the 2005 subscriber gets this
>> > error:
>> >
>> > ---
>> > Cannot connect to Server1. Additional information: Failed to
> connection
>> > to
>> > server Server1 (Microsoft.SqlServer.ConnectionInfo) An error has
> occurred
>> > while establishing a connection tot he server. When connecting to SQL
>> > Server 2005, this failure may be caused by the fact that under the
> default
>> > settings SQL Server does not allow remote connections. (provider:
> Named
>> > Pipes Provider, error 40 - Could not open a connection to SQL Server)
>> > (Microsoft SQL Server, Error: 52).
>> > ---
>> >
>> > Both boxes are on the same LAN subnet with no firewall between them. I
>> > added an entry to my HOSTS file on the 2005 server so it can see the
> 2000
>> > server by name (the wizard won't let you type in an IP address).
> Pinging
>> > that machine works fine.
>> >
>> > I don't know why this happens. I've tried windows auth and SQL auth.
>> > What's the trick? Is 2005 using some new communications method that
> 2000
>> > doesn't understand? I don't even know of a log file that would help
>> > but
>> > maybe someone knows the answer or where there is some log info.
>> >
>> >
>>
>

2005 Management studio can't connect to an alternate port?!?

In enterprise manager, I could define my sql server registration with
xxx.xxx.xxx.xxx, pppp where pppp is the non standard port number that
sql server is listening on.
Trying this in 2005 Management studio gives me:
TITLE: New Server Registration
---
Testing the registered server failed. Verify the server name, login
credentials, and database, and then click Test again.
---
ADDITIONAL INFORMATION:
Unknown ProviderConnection string is not valid (Microsoft SQL Server,
Error: 87)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=87&LinkId=20476
Any ideas? It will be bad if sql2k5 management tools can't connect to
servers on alternate ports. Is there some new way to define a port?Probably better to post SQL Server 2005 questions in the dedicated SQL
Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
On 3/2/05 7:15 PM, in article
1109808938.817178.192760@.z14g2000cwz.googlegroups.com, "Josh Goodwin"
<jsgoods@.gmail.com> wrote:
> In enterprise manager, I could define my sql server registration with
> xxx.xxx.xxx.xxx, pppp where pppp is the non standard port number that
> sql server is listening on.
> Trying this in 2005 Management studio gives me:
> TITLE: New Server Registration
> ---
> Testing the registered server failed. Verify the server name, login
> credentials, and database, and then click Test again.
> ---
> ADDITIONAL INFORMATION:
> Unknown ProviderConnection string is not valid (Microsoft SQL Server,
> Error: 87)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLS
> erver&EvtID=87&LinkId=20476
>
> Any ideas? It will be bad if sql2k5 management tools can't connect to
> servers on alternate ports. Is there some new way to define a port?
>|||Try to creat a client-side Server Alias with the Client Configuration
Utility.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE4BD4ED.1C64%ten.xoc@.dnartreb.noraa...
Probably better to post SQL Server 2005 questions in the dedicated SQL
Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
On 3/2/05 7:15 PM, in article
1109808938.817178.192760@.z14g2000cwz.googlegroups.com, "Josh Goodwin"
<jsgoods@.gmail.com> wrote:
> In enterprise manager, I could define my sql server registration with
> xxx.xxx.xxx.xxx, pppp where pppp is the non standard port number that
> sql server is listening on.
> Trying this in 2005 Management studio gives me:
> TITLE: New Server Registration
> ---
> Testing the registered server failed. Verify the server name, login
> credentials, and database, and then click Test again.
> ---
> ADDITIONAL INFORMATION:
> Unknown ProviderConnection string is not valid (Microsoft SQL Server,
> Error: 87)
> For help, click:
>
http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLS
> erver&EvtID=87&LinkId=20476
>
> Any ideas? It will be bad if sql2k5 management tools can't connect to
> servers on alternate ports. Is there some new way to define a port?
>

2005 Fully qualified names.

In SQL 2005 I have a server with a username of JM. This username created and
maintains a database. When I login via query analyser I can`t run queries on
a table unless I qualify it JM.tablename. How do I alter the login to ensure
i can query just on tablename ?
Simon
But I`m logging onto query analyzer as JM. So I would expect not to have to
qualify the name. If I changed the database ownership to dbo then wouldn`t
that ensure that I`d have to use dbo.tablename ?
Si
"vt" wrote:

> Hi
> This is because JM is the owner of the object ,
> use sp_changeobjectowner to change the owner form jm to dbo
> e.g
> sp_changeobjectowner 'Table' , 'dbo'
>
> Regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>
>
|||Simon
In SQL Server 2005 MS has introduced SCHEMA that all database objects belong
to. Think about a container that holds objects.
You will have to be a memeber of sysadmin server role as well as db_owner
database role.
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon
|||Ok thats cool, I understand this and have changed my ownership accordingly.
Is there an easy way to alter the qualified names in strored procs and views ?
Si
"Simon" wrote:

> In SQL 2005 I have a server with a username of JM. This username created and
> maintains a database. When I login via query analyser I can`t run queries on
> a table unless I qualify it JM.tablename. How do I alter the login to ensure
> i can query just on tablename ?
> Simon
|||It is a good idea to always schema qualify all objects anyway.
Andrew J. Kelly SQL MVP
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon
|||I teach my clients that it is MANDATORY to qualify every database object.
:-) There is just no valid reason not to IMHO, and it does save the engine
some effort.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> It is a good idea to always schema qualify all objects anyway.
> --
> Andrew J. Kelly SQL MVP
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>
|||I guess I understated that some. I always tell people to qualify objects
no matter what as well.
Andrew J. Kelly SQL MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:eX7ZDnZuHHA.5028@.TK2MSFTNGP02.phx.gbl...
>I teach my clients that it is MANDATORY to qualify every database object.
>:-) There is just no valid reason not to IMHO, and it does save the engine
>some effort.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
>

2005 Fully qualified names.

In SQL 2005 I have a server with a username of JM. This username created and
maintains a database. When I login via query analyser I can`t run queries on
a table unless I qualify it JM.tablename. How do I alter the login to ensure
i can query just on tablename ?
SimonHi
This is because JM is the owner of the object ,
use sp_changeobjectowner to change the owner form jm to dbo
e.g
sp_changeobjectowner 'Table' , 'dbo'
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||But I`m logging onto query analyzer as JM. So I would expect not to have to
qualify the name. If I changed the database ownership to dbo then wouldn`t
that ensure that I`d have to use dbo.tablename ?
Si
"vt" wrote:

> Hi
> This is because JM is the owner of the object ,
> use sp_changeobjectowner to change the owner form jm to dbo
> e.g
> sp_changeobjectowner 'Table' , 'dbo'
>
> Regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>
>|||Simon
In SQL Server 2005 MS has introduced SCHEMA that all database objects belong
to. Think about a container that holds objects.
You will have to be a memeber of sysadmin server role as well as db_owner
database role.
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||Ok thats cool, I understand this and have changed my ownership accordingly.
Is there an easy way to alter the qualified names in strored procs and views
?
Si
"Simon" wrote:

> In SQL 2005 I have a server with a username of JM. This username created a
nd
> maintains a database. When I login via query analyser I can`t run queries
on
> a table unless I qualify it JM.tablename. How do I alter the login to ensu
re
> i can query just on tablename ?
> Simon|||It is a good idea to always schema qualify all objects anyway.
Andrew J. Kelly SQL MVP
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||I teach my clients that it is MANDATORY to qualify every database object.
:-) There is just no valid reason not to IMHO, and it does save the engine
some effort.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> It is a good idea to always schema qualify all objects anyway.
> --
> Andrew J. Kelly SQL MVP
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>|||I guess I understated that some. I always tell people to qualify objects
no matter what as well.
Andrew J. Kelly SQL MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:eX7ZDnZuHHA.5028@.TK2MSFTNGP02.phx.gbl...
>I teach my clients that it is MANDATORY to qualify every database object.
>:-) There is just no valid reason not to IMHO, and it does save the engine
>some effort.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
>

2005 Fully qualified names.

In SQL 2005 I have a server with a username of JM. This username created and
maintains a database. When I login via query analyser I can`t run queries on
a table unless I qualify it JM.tablename. How do I alter the login to ensure
i can query just on tablename ?
SimonHi
This is because JM is the owner of the object ,
use sp_changeobjectowner to change the owner form jm to dbo
e.g
sp_changeobjectowner 'Table' , 'dbo'
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||But I`m logging onto query analyzer as JM. So I would expect not to have to
qualify the name. If I changed the database ownership to dbo then wouldn`t
that ensure that I`d have to use dbo.tablename ?
Si
"vt" wrote:
> Hi
> This is because JM is the owner of the object ,
> use sp_changeobjectowner to change the owner form jm to dbo
> e.g
> sp_changeobjectowner 'Table' , 'dbo'
>
> Regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> > In SQL 2005 I have a server with a username of JM. This username created
> > and
> > maintains a database. When I login via query analyser I can`t run queries
> > on
> > a table unless I qualify it JM.tablename. How do I alter the login to
> > ensure
> > i can query just on tablename ?
> >
> > Simon
>
>|||Simon
In SQL Server 2005 MS has introduced SCHEMA that all database objects belong
to. Think about a container that holds objects.
You will have to be a memeber of sysadmin server role as well as db_owner
database role.
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||Ok thats cool, I understand this and have changed my ownership accordingly.
Is there an easy way to alter the qualified names in strored procs and views ?
Si
"Simon" wrote:
> In SQL 2005 I have a server with a username of JM. This username created and
> maintains a database. When I login via query analyser I can`t run queries on
> a table unless I qualify it JM.tablename. How do I alter the login to ensure
> i can query just on tablename ?
> Simon|||It is a good idea to always schema qualify all objects anyway.
--
Andrew J. Kelly SQL MVP
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||I teach my clients that it is MANDATORY to qualify every database object.
:-) There is just no valid reason not to IMHO, and it does save the engine
some effort.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> It is a good idea to always schema qualify all objects anyway.
> --
> Andrew J. Kelly SQL MVP
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>> In SQL 2005 I have a server with a username of JM. This username created
>> and
>> maintains a database. When I login via query analyser I can`t run queries
>> on
>> a table unless I qualify it JM.tablename. How do I alter the login to
>> ensure
>> i can query just on tablename ?
>> Simon
>|||I guess I understated that some:). I always tell people to qualify objects
no matter what as well.
--
Andrew J. Kelly SQL MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:eX7ZDnZuHHA.5028@.TK2MSFTNGP02.phx.gbl...
>I teach my clients that it is MANDATORY to qualify every database object.
>:-) There is just no valid reason not to IMHO, and it does save the engine
>some effort.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
>> It is a good idea to always schema qualify all objects anyway.
>> --
>> Andrew J. Kelly SQL MVP
>> "Simon" <Simon@.discussions.microsoft.com> wrote in message
>> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>> In SQL 2005 I have a server with a username of JM. This username created
>> and
>> maintains a database. When I login via query analyser I can`t run
>> queries on
>> a table unless I qualify it JM.tablename. How do I alter the login to
>> ensure
>> i can query just on tablename ?
>> Simon
>>
>

2005 Express Error: Could not open a connection to SQL Server

Hi, I can't connect to my remote SQL Server 2005 Express - can anyone
help?
SETTINGS:
Server Type: Database Engine
Server Name: {my remote IP}
Authentication: SQL Authentication
User Name: {my Username}
Password: {my Password}
ERROR:
Error: Testing the registered server failed. Verify the server name,
login credentials, and database, and then click test again.
Additional Informtion: An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections. (provider: Named Piper
Provider, error:40 - Could not open a connection to SQL Server)
(Microsoft SQL Server, Error: 53)
TROUBLESHOOTING:
- Windows firewall is not enabled on the server.
- When I test "telnet {my remote IP} 1433", i get "Could not open
connection to the host, on port 1433: Connect Failed".
- When pinging my ip, I get 0% loss.
- I have established via the SQL Server Surface Area Configuration
tool that Local and remote connections, using both TCP/IP and named
pipes, is enabled.
- The SQL Server Browser service is started.
Cheers.
Sorry, I also established through the SQL Server Configuration Manager
> SQL Server 2005 Network Configuration > Protocols for MSSQLSERVER
that TCP/IP is enabled and Listen All is set to Yes. Under the 'IP
Addresses' tab, TCP Port for IP1 and IP2 (which are different from my
server IP ?) is set to 1433,2433.
|||Do you have \SQLEXPRESS on the end of the server name? Is the SQL Browser
running on the server where SQL Express is running?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"iporter" <isporter@.gmail.com> wrote in message
news:1175251815.059956.99630@.o5g2000hsb.googlegrou ps.com...
> Sorry, I also established through the SQL Server Configuration Manager
> that TCP/IP is enabled and Listen All is set to Yes. Under the 'IP
> Addresses' tab, TCP Port for IP1 and IP2 (which are different from my
> server IP ?) is set to 1433,2433.
>