Thursday, March 29, 2012
Can't connect as SA through ODBC manager...why?
I can connect to my MSDE server by logging in as SA from the command line
but not through the ODBC manager utility in windows. I need to setup system
DSNs for a few applications but can't because they require I use SQL
authentication. When I reach that step in the ODBC manager I type in SA and
the password and hit next I get a 18456 "login failed for sa" error. My
server is in mixed mode (I checked the registry), the domain admins group has
login privilages to MSDE, I have no firewall active on any NICs, and none of
my passwords contain a semicolon. I have no clue what I'm missing here. Can
some one point me in the right direction or link me to a good article on how
to setup system dsns through the command line?
Thanks,
Prikly Pete Peterson
MSDE can only be used as the source of the data. It's not licensed to host
Reporting Services (as we discuss in our Reporting Services
book --www.sqlreportingservices.net). Chapter 2 (freely downloadable from
the site) explains why.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
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.
__________________________________
"ComfortablySAD" <ComfortablySAD@.discussions.microsoft.com> wrote in message
news:32E686BF-63A6-4DFF-9A7D-A00E8B6CAE09@.microsoft.com...
> Hi All,
> I can connect to my MSDE server by logging in as SA from the command line
> but not through the ODBC manager utility in windows. I need to setup
> system
> DSNs for a few applications but can't because they require I use SQL
> authentication. When I reach that step in the ODBC manager I type in SA
> and
> the password and hit next I get a 18456 "login failed for sa" error. My
> server is in mixed mode (I checked the registry), the domain admins group
> has
> login privilages to MSDE, I have no firewall active on any NICs, and none
> of
> my passwords contain a semicolon. I have no clue what I'm missing here.
> Can
> some one point me in the right direction or link me to a good article on
> how
> to setup system dsns through the command line?
> Thanks,
> Prikly Pete Peterson
sql
Cant Connect anymore
(ON THE SERVER..went into SQL Config manager >
SQL 2005 Net Config / TCPIP Properties
Changed TCP Port to 9601
Changed TCP PORT for IP2 section to 9601 where ip is 127.0.0.1
Changed TCP for IPAll section to 9601
Dynamic TCP Port was unchanged and remained blank..
Under SQL Config Manager > SQL Native Client Network
Changed TCP PORT to 9601
--
On my laptop went to
SQL Server Config manager
Changed TCP Port to 9601
under SQL Native Client /
Client Protocols / TCPIP..
REBOOTED EVERYONE..
I have a vb.net app, tried to access SQL DB and it failed, so I added the
new port (9601) in the connect
string, and then it worked, so I know the server port was changed to 9601.
However, can no longer connect VIA SQL 2005 Management STUDIO, says
An error has occ while establich connect... TCP Provider error: No
connection could be
made.. target machine actively refused it. Error #10061).
The vb.net app works with new port, why doesn't SQL Management studio?
Changed back to 1433 and everything works again. I've got to change the
port,
How can I and get SQL Management studio to work with a non default port?
Tried the Management studio on another machine.. same error, although vb.net
app connects fine under new port. This is crazy..
The server has an internet Ip address and I am accesing it via .com name (
in the vb.net app too)
( I tried the straight IP address, didn't work either)
Thanks BUCI stumbled across the answer, if I change the default port, I HAVE to create
an alias name and use it in the login box instead of the servername itself..
BUC
<Buc> wrote in message news:%23rm9$GUSGHA.4600@.TK2MSFTNGP11.phx.gbl...
> Need help. I changed my TCP connect port for SQL2005 to port 9601.
> (ON THE SERVER..went into SQL Config manager >
> SQL 2005 Net Config / TCPIP Properties
> Changed TCP Port to 9601
> Changed TCP PORT for IP2 section to 9601 where ip is 127.0.0.1
> Changed TCP for IPAll section to 9601
> Dynamic TCP Port was unchanged and remained blank..
> Under SQL Config Manager > SQL Native Client Network
> Changed TCP PORT to 9601
> --
> On my laptop went to
> SQL Server Config manager
> Changed TCP Port to 9601
> under SQL Native Client /
> Client Protocols / TCPIP..
> REBOOTED EVERYONE..
> I have a vb.net app, tried to access SQL DB and it failed, so I added the
> new port (9601) in the connect
> string, and then it worked, so I know the server port was changed to 9601.
> However, can no longer connect VIA SQL 2005 Management STUDIO, says
> An error has occ while establich connect... TCP Provider error: No
> connection could be
> made.. target machine actively refused it. Error #10061).
> The vb.net app works with new port, why doesn't SQL Management studio?
> Changed back to 1433 and everything works again. I've got to change the
> port,
> How can I and get SQL Management studio to work with a non default port?
> Tried the Management studio on another machine.. same error, although
> vb.net app connects fine under new port. This is crazy..
> The server has an internet Ip address and I am accesing it via .com name
> ( in the vb.net app too)
> ( I tried the straight IP address, didn't work either)
> Thanks BUC
>|||Sounds like you don't have the sqlbrowser service running. The browser
service maps incoming requests to the port where the database is listening
so without the sqlbrowser service running, you have to specify the port in
the connect string.
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
<Buc> wrote in message news:OzOFXlUSGHA.524@.TK2MSFTNGP10.phx.gbl...
>I stumbled across the answer, if I change the default port, I HAVE to
>create an alias name and use it in the login box instead of the servername
>itself..
> BUC
> <Buc> wrote in message news:%23rm9$GUSGHA.4600@.TK2MSFTNGP11.phx.gbl...
>
Tuesday, March 27, 2012
cant comm with RS
Error says...
Unable to communicate with report server. Please verify that the report
server is operational.
The report server service is running. I rebooted after installing sp1.
Any ideas?
Thanks,
GaryVerify that you can access http://<machinename>/ReportServer. If you can,
make sure this URL is specified in the config file of Report Manager
(RSWebApplication.config).
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Gary D." <Dean@.nospam.com> wrote in message
news:#IQgxtIhEHA.140@.TK2MSFTNGP12.phx.gbl...
> After installing SP1 Report Manager can't communicate with Report Server.
> Error says...
> Unable to communicate with report server. Please verify that the report
> server is operational.
> The report server service is running. I rebooted after installing sp1.
> Any ideas?
> Thanks,
> Gary
>
Can't change TCP Port using configuration manager for x64 SQL 2005 SP1 virtual instance
Hi, I hope someone can help me! I changed the TCP port that the default instance of SQL 2005 x64 SP1 is listening on and now SQL server won't start as the port is being used - no problem I say to myself, I'll just change it again, however I can't!
My problem is that I can not change the TCP Port back again using Configuration Manager - no matter what values I put in, after a restart of the instance it comes up with this in the IP Addresses tab for TCP/IP Properties and SQL Server won't start:
IP1
Active Yes
Enabled No
IP Address aaa.bbb.ccc.111
TCP Dynamic Ports
TCP Port 0
IP2
Active Yes
Enabled No
IP Address aaa.bbb.ccc.222
TCP Dynamic Ports
TCP Port 0
IP3
Active Yes
Enabled No
IP Address aaa.bbb.ccc.333
TCP Dynamic Ports
TCP Port 0
IP4
Active Yes
Enabled No
IP Address 10.1.1.1
TCP Dynamic Ports
TCP Port 0
IP5
Active Yes
Enabled No
IP Address 127.0.0.1
TCP Dynamic Ports
TCP Port 0
IPALL
TCP Dynamic Ports 2020
TCP Port 2020
Interestingly, the ip address that the server is listening on aaa.bbb.ccc.444 is not reported in the list of IP addresses in the configuration manager for this instance, yet it is when I view the available IP addresses for the other two instances - is this significant?
I have tried failing to the other node, changing it there, changing it at the console and also while logged onto the the virtual machine remote desktop, rebooting both nodes, etc, etc. There are two other instances on this cluster that are still working fine but I hvae not attempted to change the TCP ports they listen on.
I used the same method to change this default instance as I did for a named instance on a similarly configured cluster and it worked fine. How to fix this?
Regards, Mike
2007-02-27 12:03:17.69 Server Server is listening on [ aaa.bbb.ccc.444<ipv4> 2020].
2007-02-27 12:03:17.71 spid9s Starting up database 'tempdb'.
2007-02-27 12:03:17.72 Server Error: 26023, Severity: 16, State: 1.
2007-02-27 12:03:17.72 Server Server TCP provider failed to listen on [ aaa.bbb.ccc.444<ipv4> 2020]. Tcp port is already in use.
2007-02-27 12:03:17.75 Server Error: 17182, Severity: 16, State: 1.
2007-02-27 12:03:17.75 Server TDSSNIClient initialization failed with error 0x2740, status code 0xa.
2007-02-27 12:03:17.75 Server Error: 17182, Severity: 16, State: 1.
2007-02-27 12:03:17.75 Server TDSSNIClient initialization failed with error 0x2740, status code 0x1.
2007-02-27 12:03:17.75 Server Error: 17826, Severity: 18, State: 3.
2007-02-27 12:03:17.75 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2007-02-27 12:03:17.77 Server Error: 17120, Severity: 16, State: 1.
2007-02-27 12:03:17.77 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
BWAHAHAHAHAHAHA!!!!! The same thing happened to me today! What a nightmare! I came about 10min away from nuking my virtual server instance and restoring 50+ databases. It was supposed to be such a simple change... I even tested it on a non-clustered box first. Oh well.
I had *exactly* the same setup as you, except 32bit. I think the problem was that I made the change on my passive node. My cluster has multiple instances spread over all nodes, but the virtual instance I was changing wasn't changed on the active node. I think that's what did me in.
In my situation, the IP settings I ented in caused the SQL Server instance to not start (too lazy to check the actual error at the moment). What happens is that the cluster service is configured to replicate the registry settings that govern the TCP/IP paramenters. If you edit the parameters incorrectly (or at least the way I did) then the cluster service continues to replicate those parameters to all nodes. Mind you, I used the SSCM too! The registry replication occurs when the cluster group changes nodes. If the SQL Server service does not start, then the cluster quorum logs never get cleared. In other words, the cluster doesn't think the registry params got applied correctly. No matter what you do to the registry on each node, the cluster quorum logs will continue to apply the replicated settings until SQL starts successfully (which never happens).
To fix it, I had to stop MSCS from replicating the bad registry params so that I could manually rebuild them. Here's how I did it, YMMV:
0. (I forgot a step) Set your broken SQL instance cluster resource to not restart automatically in MSCS
1. Stop cluster service on all nodes
2. Locate the SQL Server instance cluster resource you're having issues with here: HKLM\Cluster\Resources
Change the following parameter from this:
Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER
to this:
Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER\CurrentVersion
on all nodes
3. Fix the following registry branch on all nodes to what they were before (use your other instances as a guide if necessary):
Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER\supersocketnetlib\TCP
4. Start the cluster service on all nodes.
5. SQL Server will not start because you've messed with the registry replication params. (but it'll be a different error than before)
6. Move this cluster group to all nodes and let it fail on all nodes.
6b. Verify that the TCP/IP parameters are still good on all nodes.
7. Take your group completely offline.
8. Revert the registry replication changes you made in step 2 back to what they should be.
9. Clean up Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER\CurrentVersion. (It'll be messed up from the forced registry replication. Do this on all nodes.
10. cycle the SQL browser service on all nodes for good measure.
11. Bring your broken SQL instance back to life. I had to cycle SQL Browser one more time while I was doing this.
12. Verify that the SQL instance comes back on all nodes.
This worked for me. Good luck. Oh, and for my gratification, how about you try changing the IP address parameters from the Active node using SSCM while SQL is up? Let me know it if works :) and I'll attempt it on my cluster again.
-EDF
This sucks Beavis.
|||Thanks EDF. This looks very promising. I actually made my changes while RDP'd to the virtual instance name so it must have been the active node but maybe making the change through Remote Desktop stuffed it. I'll give it a go tomorrow and try my IP config changes again from the active node at the console and let everyone know how it goes. Thanks again.|||I tried the above changes but it did not help. I am going to raise a call with MS Support and I'll post the resolution.|||
Gengleman, did either of you successfully change the IP address of the cluster? We are currently attempting this and although I did it once before I think I got lucky. I'd love to see the steps followed.
UJ
|||Yeah, I did. It turns out that "Listen on All IP Addresses" *really* means "Listen on All IP Addresses in the cluster group". Move all your SQL instances to one node. Log into that node and start the IP binding configuration tool, SSCM. Leave the entry for "listen all"=Yes (totally against all that is good & right in me...). I wanted all my SQL instances to listen on the same port (2048), but diff IP addresses. On the IP Addresses tab, I set all my IP addresses entries to Enabled=No. At the bottom, I set IPAll entry so that TCP Dynamic Ports was NULL (not 0) and TCP Port=2048. On start up, each SQL instance then only attempts to bind port 2048 to it's own IP address. Certainly NOT what I would have expected, but I suppose it makes sense.|||Hi, I was trying to change the port, not the IP address, when somehow it got screwed up - I guess I mis-typed something. I ended up calling in PSS and they fixed it for me as I couldn't reproduce the fix that fischere suggested. It is very easy to screw up your TCP/IP config with the SSCM - the MS tech used it to corrupt his installation to reproduce my problem so it would be a good idea to play around with it on an environment that isn't important first and then make sure you follow the process exactly on your other instances.
If you end up screwing up the instance (by changing IP addresses, ports, etc) so that it won't start, you can reset it back to what it was using the Cluster Server Recovery Utility (ClusterRecovery.exe) which is available for download from the MS website. It's main purpose seems to be to recover from failed drives, but it has the ability to override the registry entries that cluster services wants to apply to a node using the "checkpoint" option. This allows you to overcome the problem of not being able to reset the values that the SSCM tries to write to the registry on each node because when you try and bring an instance on line or fail to another node the settings get restored from cluster services as fischere points out. To fix a failed instance using this tool, follow these steps:
1. move the failed instance to a node
2. change the registry settings to what they should be on that node, eg HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll
3. checkpoint the cluster using the tool on that node
4. bring the instance on line
5. repeat this process on each node.
I hope this helps someone else!
|||Thanks for posting this solution. It worked like a charm for me.sql
Can't change TCP Port using configuration manager for x64 SQL 2005 SP1 virtual instance
Hi, I hope someone can help me! I changed the TCP port that the default instance of SQL 2005 x64 SP1 is listening on and now SQL server won't start as the port is being used - no problem I say to myself, I'll just change it again, however I can't!
My problem is that I can not change the TCP Port back again using Configuration Manager - no matter what values I put in, after a restart of the instance it comes up with this in the IP Addresses tab for TCP/IP Properties and SQL Server won't start:
IP1
Active Yes
Enabled No
IP Address aaa.bbb.ccc.111
TCP Dynamic Ports
TCP Port 0
IP2
Active Yes
Enabled No
IP Address aaa.bbb.ccc.222
TCP Dynamic Ports
TCP Port 0
IP3
Active Yes
Enabled No
IP Address aaa.bbb.ccc.333
TCP Dynamic Ports
TCP Port 0
IP4
Active Yes
Enabled No
IP Address 10.1.1.1
TCP Dynamic Ports
TCP Port 0
IP5
Active Yes
Enabled No
IP Address 127.0.0.1
TCP Dynamic Ports
TCP Port 0
IPALL
TCP Dynamic Ports 2020
TCP Port 2020
Interestingly, the ip address that the server is listening on aaa.bbb.ccc.444 is not reported in the list of IP addresses in the configuration manager for this instance, yet it is when I view the available IP addresses for the other two instances - is this significant?
I have tried failing to the other node, changing it there, changing it at the console and also while logged onto the the virtual machine remote desktop, rebooting both nodes, etc, etc. There are two other instances on this cluster that are still working fine but I hvae not attempted to change the TCP ports they listen on.
I used the same method to change this default instance as I did for a named instance on a similarly configured cluster and it worked fine. How to fix this?
Regards, Mike
2007-02-27 12:03:17.69 Server Server is listening on [ aaa.bbb.ccc.444<ipv4> 2020].
2007-02-27 12:03:17.71 spid9s Starting up database 'tempdb'.
2007-02-27 12:03:17.72 Server Error: 26023, Severity: 16, State: 1.
2007-02-27 12:03:17.72 Server Server TCP provider failed to listen on [ aaa.bbb.ccc.444<ipv4> 2020]. Tcp port is already in use.
2007-02-27 12:03:17.75 Server Error: 17182, Severity: 16, State: 1.
2007-02-27 12:03:17.75 Server TDSSNIClient initialization failed with error 0x2740, status code 0xa.
2007-02-27 12:03:17.75 Server Error: 17182, Severity: 16, State: 1.
2007-02-27 12:03:17.75 Server TDSSNIClient initialization failed with error 0x2740, status code 0x1.
2007-02-27 12:03:17.75 Server Error: 17826, Severity: 18, State: 3.
2007-02-27 12:03:17.75 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2007-02-27 12:03:17.77 Server Error: 17120, Severity: 16, State: 1.
2007-02-27 12:03:17.77 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
BWAHAHAHAHAHAHA!!!!! The same thing happened to me today! What a nightmare! I came about 10min away from nuking my virtual server instance and restoring 50+ databases. It was supposed to be such a simple change... I even tested it on a non-clustered box first. Oh well.
I had *exactly* the same setup as you, except 32bit. I think the problem was that I made the change on my passive node. My cluster has multiple instances spread over all nodes, but the virtual instance I was changing wasn't changed on the active node. I think that's what did me in.
In my situation, the IP settings I ented in caused the SQL Server instance to not start (too lazy to check the actual error at the moment). What happens is that the cluster service is configured to replicate the registry settings that govern the TCP/IP paramenters. If you edit the parameters incorrectly (or at least the way I did) then the cluster service continues to replicate those parameters to all nodes. Mind you, I used the SSCM too! The registry replication occurs when the cluster group changes nodes. If the SQL Server service does not start, then the cluster quorum logs never get cleared. In other words, the cluster doesn't think the registry params got applied correctly. No matter what you do to the registry on each node, the cluster quorum logs will continue to apply the replicated settings until SQL starts successfully (which never happens).
To fix it, I had to stop MSCS from replicating the bad registry params so that I could manually rebuild them. Here's how I did it, YMMV:
0. (I forgot a step) Set your broken SQL instance cluster resource to not restart automatically in MSCS
1. Stop cluster service on all nodes
2. Locate the SQL Server instance cluster resource you're having issues with here: HKLM\Cluster\Resources
Change the following parameter from this:
Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER
to this:
Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER\CurrentVersion
on all nodes
3. Fix the following registry branch on all nodes to what they were before (use your other instances as a guide if necessary):
Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER\supersocketnetlib\TCP
4. Start the cluster service on all nodes.
5. SQL Server will not start because you've messed with the registry replication params. (but it'll be a different error than before)
6. Move this cluster group to all nodes and let it fail on all nodes.
6b. Verify that the TCP/IP parameters are still good on all nodes.
7. Take your group completely offline.
8. Revert the registry replication changes you made in step 2 back to what they should be.
9. Clean up Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER\CurrentVersion. (It'll be messed up from the forced registry replication. Do this on all nodes.
10. cycle the SQL browser service on all nodes for good measure.
11. Bring your broken SQL instance back to life. I had to cycle SQL Browser one more time while I was doing this.
12. Verify that the SQL instance comes back on all nodes.
This worked for me. Good luck. Oh, and for my gratification, how about you try changing the IP address parameters from the Active node using SSCM while SQL is up? Let me know it if works :) and I'll attempt it on my cluster again.
-EDF
This sucks Beavis.
|||Thanks EDF. This looks very promising. I actually made my changes while RDP'd to the virtual instance name so it must have been the active node but maybe making the change through Remote Desktop stuffed it. I'll give it a go tomorrow and try my IP config changes again from the active node at the console and let everyone know how it goes. Thanks again.|||I tried the above changes but it did not help. I am going to raise a call with MS Support and I'll post the resolution.|||
Gengleman, did either of you successfully change the IP address of the cluster? We are currently attempting this and although I did it once before I think I got lucky. I'd love to see the steps followed.
UJ
|||Yeah, I did. It turns out that "Listen on All IP Addresses" *really* means "Listen on All IP Addresses in the cluster group". Move all your SQL instances to one node. Log into that node and start the IP binding configuration tool, SSCM. Leave the entry for "listen all"=Yes (totally against all that is good & right in me...). I wanted all my SQL instances to listen on the same port (2048), but diff IP addresses. On the IP Addresses tab, I set all my IP addresses entries to Enabled=No. At the bottom, I set IPAll entry so that TCP Dynamic Ports was NULL (not 0) and TCP Port=2048. On start up, each SQL instance then only attempts to bind port 2048 to it's own IP address. Certainly NOT what I would have expected, but I suppose it makes sense.|||Hi, I was trying to change the port, not the IP address, when somehow it got screwed up - I guess I mis-typed something. I ended up calling in PSS and they fixed it for me as I couldn't reproduce the fix that fischere suggested. It is very easy to screw up your TCP/IP config with the SSCM - the MS tech used it to corrupt his installation to reproduce my problem so it would be a good idea to play around with it on an environment that isn't important first and then make sure you follow the process exactly on your other instances.
If you end up screwing up the instance (by changing IP addresses, ports, etc) so that it won't start, you can reset it back to what it was using the Cluster Server Recovery Utility (ClusterRecovery.exe) which is available for download from the MS website. It's main purpose seems to be to recover from failed drives, but it has the ability to override the registry entries that cluster services wants to apply to a node using the "checkpoint" option. This allows you to overcome the problem of not being able to reset the values that the SSCM tries to write to the registry on each node because when you try and bring an instance on line or fail to another node the settings get restored from cluster services as fischere points out. To fix a failed instance using this tool, follow these steps:
1. move the failed instance to a node
2. change the registry settings to what they should be on that node, eg HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll
3. checkpoint the cluster using the tool on that node
4. bring the instance on line
5. repeat this process on each node.
I hope this helps someone else!
|||Thanks for posting this solution. It worked like a charm for me.
Can't change TCP Port using configuration manager for x64 SQL 2005 SP1 virtual instance
Hi, I hope someone can help me! I changed the TCP port that the default instance of SQL 2005 x64 SP1 is listening on and now SQL server won't start as the port is being used - no problem I say to myself, I'll just change it again, however I can't!
My problem is that I can not change the TCP Port back again using Configuration Manager - no matter what values I put in, after a restart of the instance it comes up with this in the IP Addresses tab for TCP/IP Properties and SQL Server won't start:
IP1
Active Yes
Enabled No
IP Address aaa.bbb.ccc.111
TCP Dynamic Ports
TCP Port 0
IP2
Active Yes
Enabled No
IP Address aaa.bbb.ccc.222
TCP Dynamic Ports
TCP Port 0
IP3
Active Yes
Enabled No
IP Address aaa.bbb.ccc.333
TCP Dynamic Ports
TCP Port 0
IP4
Active Yes
Enabled No
IP Address 10.1.1.1
TCP Dynamic Ports
TCP Port 0
IP5
Active Yes
Enabled No
IP Address 127.0.0.1
TCP Dynamic Ports
TCP Port 0
IPALL
TCP Dynamic Ports 2020
TCP Port 2020
Interestingly, the ip address that the server is listening on aaa.bbb.ccc.444 is not reported in the list of IP addresses in the configuration manager for this instance, yet it is when I view the available IP addresses for the other two instances - is this significant?
I have tried failing to the other node, changing it there, changing it at the console and also while logged onto the the virtual machine remote desktop, rebooting both nodes, etc, etc. There are two other instances on this cluster that are still working fine but I hvae not attempted to change the TCP ports they listen on.
I used the same method to change this default instance as I did for a named instance on a similarly configured cluster and it worked fine. How to fix this?
Regards, Mike
2007-02-27 12:03:17.69 Server Server is listening on [ aaa.bbb.ccc.444<ipv4> 2020].
2007-02-27 12:03:17.71 spid9s Starting up database 'tempdb'.
2007-02-27 12:03:17.72 Server Error: 26023, Severity: 16, State: 1.
2007-02-27 12:03:17.72 Server Server TCP provider failed to listen on [ aaa.bbb.ccc.444<ipv4> 2020]. Tcp port is already in use.
2007-02-27 12:03:17.75 Server Error: 17182, Severity: 16, State: 1.
2007-02-27 12:03:17.75 Server TDSSNIClient initialization failed with error 0x2740, status code 0xa.
2007-02-27 12:03:17.75 Server Error: 17182, Severity: 16, State: 1.
2007-02-27 12:03:17.75 Server TDSSNIClient initialization failed with error 0x2740, status code 0x1.
2007-02-27 12:03:17.75 Server Error: 17826, Severity: 18, State: 3.
2007-02-27 12:03:17.75 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2007-02-27 12:03:17.77 Server Error: 17120, Severity: 16, State: 1.
2007-02-27 12:03:17.77 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
BWAHAHAHAHAHAHA!!!!! The same thing happened to me today! What a nightmare! I came about 10min away from nuking my virtual server instance and restoring 50+ databases. It was supposed to be such a simple change... I even tested it on a non-clustered box first. Oh well.
I had *exactly* the same setup as you, except 32bit. I think the problem was that I made the change on my passive node. My cluster has multiple instances spread over all nodes, but the virtual instance I was changing wasn't changed on the active node. I think that's what did me in.
In my situation, the IP settings I ented in caused the SQL Server instance to not start (too lazy to check the actual error at the moment). What happens is that the cluster service is configured to replicate the registry settings that govern the TCP/IP paramenters. If you edit the parameters incorrectly (or at least the way I did) then the cluster service continues to replicate those parameters to all nodes. Mind you, I used the SSCM too! The registry replication occurs when the cluster group changes nodes. If the SQL Server service does not start, then the cluster quorum logs never get cleared. In other words, the cluster doesn't think the registry params got applied correctly. No matter what you do to the registry on each node, the cluster quorum logs will continue to apply the replicated settings until SQL starts successfully (which never happens).
To fix it, I had to stop MSCS from replicating the bad registry params so that I could manually rebuild them. Here's how I did it, YMMV:
0. (I forgot a step) Set your broken SQL instance cluster resource to not restart automatically in MSCS
1. Stop cluster service on all nodes
2. Locate the SQL Server instance cluster resource you're having issues with here: HKLM\Cluster\Resources
Change the following parameter from this:
Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER
to this:
Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER\CurrentVersion
on all nodes
3. Fix the following registry branch on all nodes to what they were before (use your other instances as a guide if necessary):
Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER\supersocketnetlib\TCP
4. Start the cluster service on all nodes.
5. SQL Server will not start because you've messed with the registry replication params. (but it'll be a different error than before)
6. Move this cluster group to all nodes and let it fail on all nodes.
6b. Verify that the TCP/IP parameters are still good on all nodes.
7. Take your group completely offline.
8. Revert the registry replication changes you made in step 2 back to what they should be.
9. Clean up Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER\CurrentVersion. (It'll be messed up from the forced registry replication. Do this on all nodes.
10. cycle the SQL browser service on all nodes for good measure.
11. Bring your broken SQL instance back to life. I had to cycle SQL Browser one more time while I was doing this.
12. Verify that the SQL instance comes back on all nodes.
This worked for me. Good luck. Oh, and for my gratification, how about you try changing the IP address parameters from the Active node using SSCM while SQL is up? Let me know it if works :) and I'll attempt it on my cluster again.
-EDF
This sucks Beavis.
|||Thanks EDF. This looks very promising. I actually made my changes while RDP'd to the virtual instance name so it must have been the active node but maybe making the change through Remote Desktop stuffed it. I'll give it a go tomorrow and try my IP config changes again from the active node at the console and let everyone know how it goes. Thanks again.|||I tried the above changes but it did not help. I am going to raise a call with MS Support and I'll post the resolution.|||
Gengleman, did either of you successfully change the IP address of the cluster? We are currently attempting this and although I did it once before I think I got lucky. I'd love to see the steps followed.
UJ
|||Yeah, I did. It turns out that "Listen on All IP Addresses" *really* means "Listen on All IP Addresses in the cluster group". Move all your SQL instances to one node. Log into that node and start the IP binding configuration tool, SSCM. Leave the entry for "listen all"=Yes (totally against all that is good & right in me...). I wanted all my SQL instances to listen on the same port (2048), but diff IP addresses. On the IP Addresses tab, I set all my IP addresses entries to Enabled=No. At the bottom, I set IPAll entry so that TCP Dynamic Ports was NULL (not 0) and TCP Port=2048. On start up, each SQL instance then only attempts to bind port 2048 to it's own IP address. Certainly NOT what I would have expected, but I suppose it makes sense.|||Hi, I was trying to change the port, not the IP address, when somehow it got screwed up - I guess I mis-typed something. I ended up calling in PSS and they fixed it for me as I couldn't reproduce the fix that fischere suggested. It is very easy to screw up your TCP/IP config with the SSCM - the MS tech used it to corrupt his installation to reproduce my problem so it would be a good idea to play around with it on an environment that isn't important first and then make sure you follow the process exactly on your other instances.
If you end up screwing up the instance (by changing IP addresses, ports, etc) so that it won't start, you can reset it back to what it was using the Cluster Server Recovery Utility (ClusterRecovery.exe) which is available for download from the MS website. It's main purpose seems to be to recover from failed drives, but it has the ability to override the registry entries that cluster services wants to apply to a node using the "checkpoint" option. This allows you to overcome the problem of not being able to reset the values that the SSCM tries to write to the registry on each node because when you try and bring an instance on line or fail to another node the settings get restored from cluster services as fischere points out. To fix a failed instance using this tool, follow these steps:
1. move the failed instance to a node
2. change the registry settings to what they should be on that node, eg HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll
3. checkpoint the cluster using the tool on that node
4. bring the instance on line
5. repeat this process on each node.
I hope this helps someone else!
|||Thanks for posting this solution. It worked like a charm for me.
Can't change TCP Port using configuration manager for x64 SQL 2005 SP1 virtual instance
Hi, I hope someone can help me! I changed the TCP port that the default instance of SQL 2005 x64 SP1 is listening on and now SQL server won't start as the port is being used - no problem I say to myself, I'll just change it again, however I can't!
My problem is that I can not change the TCP Port back again using Configuration Manager - no matter what values I put in, after a restart of the instance it comes up with this in the IP Addresses tab for TCP/IP Properties and SQL Server won't start:
IP1
Active Yes
Enabled No
IP Address aaa.bbb.ccc.111
TCP Dynamic Ports
TCP Port 0
IP2
Active Yes
Enabled No
IP Address aaa.bbb.ccc.222
TCP Dynamic Ports
TCP Port 0
IP3
Active Yes
Enabled No
IP Address aaa.bbb.ccc.333
TCP Dynamic Ports
TCP Port 0
IP4
Active Yes
Enabled No
IP Address 10.1.1.1
TCP Dynamic Ports
TCP Port 0
IP5
Active Yes
Enabled No
IP Address 127.0.0.1
TCP Dynamic Ports
TCP Port 0
IPALL
TCP Dynamic Ports 2020
TCP Port 2020
Interestingly, the ip address that the server is listening on aaa.bbb.ccc.444 is not reported in the list of IP addresses in the configuration manager for this instance, yet it is when I view the available IP addresses for the other two instances - is this significant?
I have tried failing to the other node, changing it there, changing it at the console and also while logged onto the the virtual machine remote desktop, rebooting both nodes, etc, etc. There are two other instances on this cluster that are still working fine but I hvae not attempted to change the TCP ports they listen on.
I used the same method to change this default instance as I did for a named instance on a similarly configured cluster and it worked fine. How to fix this?
Regards, Mike
2007-02-27 12:03:17.69 Server Server is listening on [ aaa.bbb.ccc.444<ipv4> 2020].
2007-02-27 12:03:17.71 spid9s Starting up database 'tempdb'.
2007-02-27 12:03:17.72 Server Error: 26023, Severity: 16, State: 1.
2007-02-27 12:03:17.72 Server Server TCP provider failed to listen on [ aaa.bbb.ccc.444<ipv4> 2020]. Tcp port is already in use.
2007-02-27 12:03:17.75 Server Error: 17182, Severity: 16, State: 1.
2007-02-27 12:03:17.75 Server TDSSNIClient initialization failed with error 0x2740, status code 0xa.
2007-02-27 12:03:17.75 Server Error: 17182, Severity: 16, State: 1.
2007-02-27 12:03:17.75 Server TDSSNIClient initialization failed with error 0x2740, status code 0x1.
2007-02-27 12:03:17.75 Server Error: 17826, Severity: 18, State: 3.
2007-02-27 12:03:17.75 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2007-02-27 12:03:17.77 Server Error: 17120, Severity: 16, State: 1.
2007-02-27 12:03:17.77 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
BWAHAHAHAHAHAHA!!!!! The same thing happened to me today! What a nightmare! I came about 10min away from nuking my virtual server instance and restoring 50+ databases. It was supposed to be such a simple change... I even tested it on a non-clustered box first. Oh well.
I had *exactly* the same setup as you, except 32bit. I think the problem was that I made the change on my passive node. My cluster has multiple instances spread over all nodes, but the virtual instance I was changing wasn't changed on the active node. I think that's what did me in.
In my situation, the IP settings I ented in caused the SQL Server instance to not start (too lazy to check the actual error at the moment). What happens is that the cluster service is configured to replicate the registry settings that govern the TCP/IP paramenters. If you edit the parameters incorrectly (or at least the way I did) then the cluster service continues to replicate those parameters to all nodes. Mind you, I used the SSCM too! The registry replication occurs when the cluster group changes nodes. If the SQL Server service does not start, then the cluster quorum logs never get cleared. In other words, the cluster doesn't think the registry params got applied correctly. No matter what you do to the registry on each node, the cluster quorum logs will continue to apply the replicated settings until SQL starts successfully (which never happens).
To fix it, I had to stop MSCS from replicating the bad registry params so that I could manually rebuild them. Here's how I did it, YMMV:
0. (I forgot a step) Set your broken SQL instance cluster resource to not restart automatically in MSCS
1. Stop cluster service on all nodes
2. Locate the SQL Server instance cluster resource you're having issues with here: HKLM\Cluster\Resources
Change the following parameter from this:
Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER
to this:
Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER\CurrentVersion
on all nodes
3. Fix the following registry branch on all nodes to what they were before (use your other instances as a guide if necessary):
Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER\supersocketnetlib\TCP
4. Start the cluster service on all nodes.
5. SQL Server will not start because you've messed with the registry replication params. (but it'll be a different error than before)
6. Move this cluster group to all nodes and let it fail on all nodes.
6b. Verify that the TCP/IP parameters are still good on all nodes.
7. Take your group completely offline.
8. Revert the registry replication changes you made in step 2 back to what they should be.
9. Clean up Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER\CurrentVersion. (It'll be messed up from the forced registry replication. Do this on all nodes.
10. cycle the SQL browser service on all nodes for good measure.
11. Bring your broken SQL instance back to life. I had to cycle SQL Browser one more time while I was doing this.
12. Verify that the SQL instance comes back on all nodes.
This worked for me. Good luck. Oh, and for my gratification, how about you try changing the IP address parameters from the Active node using SSCM while SQL is up? Let me know it if works :) and I'll attempt it on my cluster again.
-EDF
This sucks Beavis.
|||Thanks EDF. This looks very promising. I actually made my changes while RDP'd to the virtual instance name so it must have been the active node but maybe making the change through Remote Desktop stuffed it. I'll give it a go tomorrow and try my IP config changes again from the active node at the console and let everyone know how it goes. Thanks again.|||I tried the above changes but it did not help. I am going to raise a call with MS Support and I'll post the resolution.|||
Gengleman, did either of you successfully change the IP address of the cluster? We are currently attempting this and although I did it once before I think I got lucky. I'd love to see the steps followed.
UJ
|||Yeah, I did. It turns out that "Listen on All IP Addresses" *really* means "Listen on All IP Addresses in the cluster group". Move all your SQL instances to one node. Log into that node and start the IP binding configuration tool, SSCM. Leave the entry for "listen all"=Yes (totally against all that is good & right in me...). I wanted all my SQL instances to listen on the same port (2048), but diff IP addresses. On the IP Addresses tab, I set all my IP addresses entries to Enabled=No. At the bottom, I set IPAll entry so that TCP Dynamic Ports was NULL (not 0) and TCP Port=2048. On start up, each SQL instance then only attempts to bind port 2048 to it's own IP address. Certainly NOT what I would have expected, but I suppose it makes sense.|||Hi, I was trying to change the port, not the IP address, when somehow it got screwed up - I guess I mis-typed something. I ended up calling in PSS and they fixed it for me as I couldn't reproduce the fix that fischere suggested. It is very easy to screw up your TCP/IP config with the SSCM - the MS tech used it to corrupt his installation to reproduce my problem so it would be a good idea to play around with it on an environment that isn't important first and then make sure you follow the process exactly on your other instances.
If you end up screwing up the instance (by changing IP addresses, ports, etc) so that it won't start, you can reset it back to what it was using the Cluster Server Recovery Utility (ClusterRecovery.exe) which is available for download from the MS website. It's main purpose seems to be to recover from failed drives, but it has the ability to override the registry entries that cluster services wants to apply to a node using the "checkpoint" option. This allows you to overcome the problem of not being able to reset the values that the SSCM tries to write to the registry on each node because when you try and bring an instance on line or fail to another node the settings get restored from cluster services as fischere points out. To fix a failed instance using this tool, follow these steps:
1. move the failed instance to a node
2. change the registry settings to what they should be on that node, eg HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll
3. checkpoint the cluster using the tool on that node
4. bring the instance on line
5. repeat this process on each node.
I hope this helps someone else!
|||Thanks for posting this solution. It worked like a charm for me.
Can't backup Remote database
and it tells me the path for the backup is no good, but it will not
let me change it, any ideas.
Rick
hi Rick,
Rick wrote:
> I am using the SQL express manager to try to backup a remote database
> and it tells me the path for the backup is no good, but it will not
> let me change it, any ideas.
>
try using SQL Server Management Studio Express you can download from
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=6053c6f8-82c8-479c-b25b-9aca13141c9e,
instead of SQLExpress Manager..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||On Jun 15, 12:59 pm, "Andrea Montanari" <andrea.sql...@.virgilio.it>
wrote:
> hi Rick,
> Rick wrote:
> try using SQL Server Management Studio Express you can download fromhttp://www.microsoft.com/downloads/details.aspx?displaylang=en&Family...,
> instead of SQLExpress Manager..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
I tried that with same results, If I remove the backup device and try
to add another one, I get the error Property Backupdirectory is not
available for settings "microsoft.sqlserver.management.smo.settings"
Rick
|||hi Rick,
Rick wrote:
> I tried that with same results, If I remove the backup device and try
> to add another one, I get the error Property Backupdirectory is not
> available for settings "microsoft.sqlserver.management.smo.settings"
>
please verify the account running SQLExpress has enought permissions on the
backup device at the NTFS file system level as well..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||On Jun 15, 3:40 pm, "Andrea Montanari" <andrea.sql...@.virgilio.it>
wrote:
> hi Rick,
> Rick wrote:
> please verify the account running SQLExpress has enought permissions on the
> backup device at the NTFS file system level as well..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
i had to add a backupDevice key on the sqlserver pointing to c:, that
solved the problem
thanks
Rick
Sunday, March 25, 2012
Can't assign connection manager object to DataReader Source
I'm trying to create a DataReader source object using a working ADO.NET Oracle Client connection object that exists in the connection manager.
Problem:
When I open the editor the DataReaderSrc component shows only one row under the Connection Managers tab:
Name=IDbConnection
Connection Manager=blank
Description=Managed connection manager
I cannot assign the ADO.NET connection (or any connections). I see a warning at the bottom of the Connection Managers tab:
"Not all connection managers have been set. Set all connection managers."
When I click refresh the warning message changes to:
"The runtime connection manager with the ID "" cannot be found. Verify that the connection manager collection has a connection manager with that ID."
I am prevented from removing the IDbConnection or assigning my Connection Manager object to the DataReader Source.
Thanks, -- Mike
I also have the same problem, I'm sure it must be some tiny detail somewhere / something very basic, but I have been unable to fix it. Can someone help? Thanks!
Hector
|||I had the same problem because I was using the wrong Provider when specifying my Data Source.
Hope it helps,
Nuno
|||I had the same problem. In my case the 'advanced editor for datareader source' is very, very confusing. First of all, you have to create a connection manager, which I did already. But, it was not at all intuitive that you have to click on the box under 'connection manager' and select a connection manager. By default the box is black and grayed out like both the other fields which you cannot change. Gee, thanks redmond.
|||Thanks for taking the time to post this answer. I finally learned this last week at a SQL Server training class. The instructor pointed out that you actually need to change the disabled looking box.|||Hello.
I read that you found a solution, but I didn't understand it very well.
Could you tell me more how to resolve this problem. I have create the connection manager and a cant set it into the DataReader Source
Thank you.
|||Data Reader source accepts connection manager of Type ADO.NET only.
so First you have to create a Connection Manager to your Data Base using ADO.net Provider ONLY.
open the advanced editior of data reader source, go to connection managers tab, you will see IdbConnection , beside that there will be another column named connection manger, select your created connection manager there (ADO.Net one). Then go to Component properties tab and in sql command enter your sql query. go to columns mapping tab ..now you can see your columns.
|||thank you for your reply. i resolve the problem.|||hello,
i thought that I resolve the problem of the connection but there was another problem that didn't permit me to save the connection to the advanced editor.
the error is "Component is already connected. The component needs to be disconnected before attempting to connect it."
Can you help me?
Can't assign connection manager object to DataReader Source
I'm trying to create a DataReader source object using a working ADO.NET Oracle Client connection object that exists in the connection manager.
Problem:
When I open the editor the DataReaderSrc component shows only one row under the Connection Managers tab:
Name=IDbConnection
Connection Manager=blank
Description=Managed connection manager
I cannot assign the ADO.NET connection (or any connections). I see a warning at the bottom of the Connection Managers tab:
"Not all connection managers have been set. Set all connection managers."
When I click refresh the warning message changes to:
"The runtime connection manager with the ID "" cannot be found. Verify that the connection manager collection has a connection manager with that ID."
I am prevented from removing the IDbConnection or assigning my Connection Manager object to the DataReader Source.
Thanks, -- Mike
I also have the same problem, I'm sure it must be some tiny detail somewhere / something very basic, but I have been unable to fix it. Can someone help? Thanks!
Hector
|||I had the same problem because I was using the wrong Provider when specifying my Data Source.
Hope it helps,
Nuno
|||I had the same problem. In my case the 'advanced editor for datareader source' is very, very confusing. First of all, you have to create a connection manager, which I did already. But, it was not at all intuitive that you have to click on the box under 'connection manager' and select a connection manager. By default the box is black and grayed out like both the other fields which you cannot change. Gee, thanks redmond.
|||Thanks for taking the time to post this answer. I finally learned this last week at a SQL Server training class. The instructor pointed out that you actually need to change the disabled looking box.|||Hello.
I read that you found a solution, but I didn't understand it very well.
Could you tell me more how to resolve this problem. I have create the connection manager and a cant set it into the DataReader Source
Thank you.
|||Data Reader source accepts connection manager of Type ADO.NET only.
so First you have to create a Connection Manager to your Data Base using ADO.net Provider ONLY.
open the advanced editior of data reader source, go to connection managers tab, you will see IdbConnection , beside that there will be another column named connection manger, select your created connection manager there (ADO.Net one). Then go to Component properties tab and in sql command enter your sql query. go to columns mapping tab ..now you can see your columns.
|||thank you for your reply. i resolve the problem.|||hello,
i thought that I resolve the problem of the connection but there was another problem that didn't permit me to save the connection to the advanced editor.
the error is "Component is already connected. The component needs to be disconnected before attempting to connect it."
Can you help me?
Can't assign connection manager object to DataReader Source
I'm trying to create a DataReader source object using a working ADO.NET Oracle Client connection object that exists in the connection manager.
Problem:
When I open the editor the DataReaderSrc component shows only one row under the Connection Managers tab:
Name=IDbConnection
Connection Manager=blank
Description=Managed connection manager
I cannot assign the ADO.NET connection (or any connections). I see a warning at the bottom of the Connection Managers tab:
"Not all connection managers have been set. Set all connection managers."
When I click refresh the warning message changes to:
"The runtime connection manager with the ID "" cannot be found. Verify that the connection manager collection has a connection manager with that ID."
I am prevented from removing the IDbConnection or assigning my Connection Manager object to the DataReader Source.
Thanks, -- Mike
I also have the same problem, I'm sure it must be some tiny detail somewhere / something very basic, but I have been unable to fix it. Can someone help? Thanks!
Hector
|||I had the same problem because I was using the wrong Provider when specifying my Data Source.
Hope it helps,
Nuno
|||I had the same problem. In my case the 'advanced editor for datareader source' is very, very confusing. First of all, you have to create a connection manager, which I did already. But, it was not at all intuitive that you have to click on the box under 'connection manager' and select a connection manager. By default the box is black and grayed out like both the other fields which you cannot change. Gee, thanks redmond.
|||Thanks for taking the time to post this answer. I finally learned this last week at a SQL Server training class. The instructor pointed out that you actually need to change the disabled looking box.|||Hello.
I read that you found a solution, but I didn't understand it very well.
Could you tell me more how to resolve this problem. I have create the connection manager and a cant set it into the DataReader Source
Thank you.
|||Data Reader source accepts connection manager of Type ADO.NET only.
so First you have to create a Connection Manager to your Data Base using ADO.net Provider ONLY.
open the advanced editior of data reader source, go to connection managers tab, you will see IdbConnection , beside that there will be another column named connection manger, select your created connection manager there (ADO.Net one). Then go to Component properties tab and in sql command enter your sql query. go to columns mapping tab ..now you can see your columns.
|||thank you for your reply. i resolve the problem.|||hello,
i thought that I resolve the problem of the connection but there was another problem that didn't permit me to save the connection to the advanced editor.
the error is "Component is already connected. The component needs to be disconnected before attempting to connect it."
Can you help me?
Can't assign connection manager object to DataReader Source
I'm trying to create a DataReader source object using a working ADO.NET Oracle Client connection object that exists in the connection manager.
Problem:
When I open the editor the DataReaderSrc component shows only one row under the Connection Managers tab:
Name=IDbConnection
Connection Manager=blank
Description=Managed connection manager
I cannot assign the ADO.NET connection (or any connections). I see a warning at the bottom of the Connection Managers tab:
"Not all connection managers have been set. Set all connection managers."
When I click refresh the warning message changes to:
"The runtime connection manager with the ID "" cannot be found. Verify that the connection manager collection has a connection manager with that ID."
I am prevented from removing the IDbConnection or assigning my Connection Manager object to the DataReader Source.
Thanks, -- Mike
I also have the same problem, I'm sure it must be some tiny detail somewhere / something very basic, but I have been unable to fix it. Can someone help? Thanks!
Hector
|||I had the same problem because I was using the wrong Provider when specifying my Data Source.
Hope it helps,
Nuno
|||I had the same problem. In my case the 'advanced editor for datareader source' is very, very confusing. First of all, you have to create a connection manager, which I did already. But, it was not at all intuitive that you have to click on the box under 'connection manager' and select a connection manager. By default the box is black and grayed out like both the other fields which you cannot change. Gee, thanks redmond.
|||Thanks for taking the time to post this answer. I finally learned this last week at a SQL Server training class. The instructor pointed out that you actually need to change the disabled looking box.|||Hello.
I read that you found a solution, but I didn't understand it very well.
Could you tell me more how to resolve this problem. I have create the connection manager and a cant set it into the DataReader Source
Thank you.
|||Data Reader source accepts connection manager of Type ADO.NET only.
so First you have to create a Connection Manager to your Data Base using ADO.net Provider ONLY.
open the advanced editior of data reader source, go to connection managers tab, you will see IdbConnection , beside that there will be another column named connection manger, select your created connection manager there (ADO.Net one). Then go to Component properties tab and in sql command enter your sql query. go to columns mapping tab ..now you can see your columns.
|||thank you for your reply. i resolve the problem.|||hello,
i thought that I resolve the problem of the connection but there was another problem that didn't permit me to save the connection to the advanced editor.
the error is "Component is already connected. The component needs to be disconnected before attempting to connect it."
Can you help me?
Can't assign connection manager object to DataReader Source
I'm trying to create a DataReader source object using a working ADO.NET Oracle Client connection object that exists in the connection manager.
Problem:
When I open the editor the DataReaderSrc component shows only one row under the Connection Managers tab:
Name=IDbConnection
Connection Manager=blank
Description=Managed connection manager
I cannot assign the ADO.NET connection (or any connections). I see a warning at the bottom of the Connection Managers tab:
"Not all connection managers have been set. Set all connection managers."
When I click refresh the warning message changes to:
"The runtime connection manager with the ID "" cannot be found. Verify that the connection manager collection has a connection manager with that ID."
I am prevented from removing the IDbConnection or assigning my Connection Manager object to the DataReader Source.
Thanks, -- Mike
I also have the same problem, I'm sure it must be some tiny detail somewhere / something very basic, but I have been unable to fix it. Can someone help? Thanks!
Hector
|||I had the same problem because I was using the wrong Provider when specifying my Data Source.
Hope it helps,
Nuno
|||I had the same problem. In my case the 'advanced editor for datareader source' is very, very confusing. First of all, you have to create a connection manager, which I did already. But, it was not at all intuitive that you have to click on the box under 'connection manager' and select a connection manager. By default the box is black and grayed out like both the other fields which you cannot change. Gee, thanks redmond.
|||Thanks for taking the time to post this answer. I finally learned this last week at a SQL Server training class. The instructor pointed out that you actually need to change the disabled looking box.|||Hello.
I read that you found a solution, but I didn't understand it very well.
Could you tell me more how to resolve this problem. I have create the connection manager and a cant set it into the DataReader Source
Thank you.
|||Data Reader source accepts connection manager of Type ADO.NET only.
so First you have to create a Connection Manager to your Data Base using ADO.net Provider ONLY.
open the advanced editior of data reader source, go to connection managers tab, you will see IdbConnection , beside that there will be another column named connection manger, select your created connection manager there (ADO.Net one). Then go to Component properties tab and in sql command enter your sql query. go to columns mapping tab ..now you can see your columns.
|||thank you for your reply. i resolve the problem.|||hello,
i thought that I resolve the problem of the connection but there was another problem that didn't permit me to save the connection to the advanced editor.
the error is "Component is already connected. The component needs to be disconnected before attempting to connect it."
Can you help me?
sqlcant aquire a managed connection, SSIS
manager. I tested it and the connection passed. I then wend to the data
flow source and added a data reader source but get the error , cannot aquire
a managed connection from the runtime connection manager. I am trying to
create a simple package that will read data from a database using a query and
then write it out to an excel file. I am familiar with the old 2000 DTS
package development.
thanks.
--
Paul G
Software engineer.Paul,
I'm in the same boat and ended up having to go buy the Microsoft Press SSIS
book. I'm still going through it.
You probably will have to do the same as SSIS help seems to be limited in
here.
Good luck,
Jay
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:722EC193-D7B7-4B20-AB4F-1303F4AB625C@.microsoft.com...
> HI I am creating an integration service and have created a connection
> manager. I tested it and the connection passed. I then wend to the data
> flow source and added a data reader source but get the error , cannot
> aquire
> a managed connection from the runtime connection manager. I am trying to
> create a simple package that will read data from a database using a query
> and
> then write it out to an excel file. I am familiar with the old 2000 DTS
> package development.
> thanks.
> --
> Paul G
> Software engineer.|||Hi Jay I was able to get it working. I now have an Integrated Services
packages that moves data from one database to another and if successful
updates a table in the original database. Also if successful I have it write
out in a message box using vbscript. Also if it fails I have it wright out
an error message in a message box.
If I remember correctly I selected the connection from the properties of the
dataflow task. Also on the connection I used the ADO.net. In addition I
changed the authentication mode to mixed (this is in the properties of the
database). Hope this helps.
--
Paul G
Software engineer.
"Jay" wrote:
> Paul,
> I'm in the same boat and ended up having to go buy the Microsoft Press SSIS
> book. I'm still going through it.
> You probably will have to do the same as SSIS help seems to be limited in
> here.
> Good luck,
> Jay
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:722EC193-D7B7-4B20-AB4F-1303F4AB625C@.microsoft.com...
> > HI I am creating an integration service and have created a connection
> > manager. I tested it and the connection passed. I then wend to the data
> > flow source and added a data reader source but get the error , cannot
> > aquire
> > a managed connection from the runtime connection manager. I am trying to
> > create a simple package that will read data from a database using a query
> > and
> > then write it out to an excel file. I am familiar with the old 2000 DTS
> > package development.
> > thanks.
> > --
> > Paul G
> > Software engineer.
>
>
Can't apply transaction logs after restoring a database in enterprise manager
I created Full Backup of database and transaction logs in Enterprise manager
.. Restore of the database worked fine, but when i'm trying to apply transact
ion logs i get the followin error:
code:Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 7000000063500001, which is too
early to apply to the database. A more recent log backup that includes LSN 7
000000063700001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Why is the possible cause of this problem?
Thank you.
Seems like you try to apply an incorrect transaction log backup. If you can reproduce this using
TSQL code (BACKUP and RESTORE commands) against a test database, we could test and see what the
problem might be.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Francelj Smuk" <Francelj.Smuk@.email.si> wrote in message
news:pan.2004.09.08.08.32.08.781516@.email.si...
> Hi!
> I created Full Backup of database and transaction logs in Enterprise manager
> . Restore of the database worked fine, but when i'm trying to apply transact
> ion logs i get the followin error:
>
> code:Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 7000000063500001, which is too
> early to apply to the database. A more recent log backup that includes LSN 7
> 000000063700001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
>
> Why is the possible cause of this problem?
> Thank you.
Can't apply transaction logs after restoring a database in enterprise manager
I created Full Backup of database and transaction logs in Enterprise manager
. Restore of the database worked fine, but when i'm trying to apply transact
ion logs i get the followin error:
code:Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 7000000063500001, which is too
early to apply to the database. A more recent log backup that includes LSN 7
000000063700001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Why is the possible cause of this problem?
Thank you.Seems like you try to apply an incorrect transaction log backup. If you can reproduce this using
TSQL code (BACKUP and RESTORE commands) against a test database, we could test and see what the
problem might be.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Francelj Smuk" <Francelj.Smuk@.email.si> wrote in message
news:pan.2004.09.08.08.32.08.781516@.email.si...
> Hi!
> I created Full Backup of database and transaction logs in Enterprise manager
> . Restore of the database worked fine, but when i'm trying to apply transact
> ion logs i get the followin error:
>
> code:Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 7000000063500001, which is too
> early to apply to the database. A more recent log backup that includes LSN 7
> 000000063700001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
>
> Why is the possible cause of this problem?
> Thank you.
Thursday, March 22, 2012
Can't Add or Delete new role for new users
User Home -> Properties -> New Role Assignment
Then enter user's doman and id information and check the role. However, it
shows ERROR.
The error message:
"Can't find the role '?' (rsRoleNotFound).
When i tried to delete one of the current user, it shows the same error
message.
The error message:
"Can't find the role '?' (rsRoleNotFound).
However, in my roles table, all roles information are all complete and
correct.
Can anyone tell me what's wrong?"roles" here refers to active directory user. You must add user you want to
the active directory (using control panel->management tools->computer
management->users)
than you can use the user you have added in the service now
"Lisa" wrote:
> I've tried to add or delete a user to read report by using report manager.
> User Home -> Properties -> New Role Assignment
> Then enter user's doman and id information and check the role. However, it
> shows ERROR.
> The error message:
> "Can't find the role '?' (rsRoleNotFound).
> When i tried to delete one of the current user, it shows the same error
> message.
> The error message:
> "Can't find the role '?' (rsRoleNotFound).
> However, in my roles table, all roles information are all complete and
> correct.
> Can anyone tell me what's wrong?
>
>
>|||Hi,
The user i want to add has already added in the management tools.
Is there anyother possiblie way to fix this?
"yuanxm" <yuanxm@.discussions.microsoft.com> ¦b¶l¥ó
news:95A7D902-5815-4F26-8A75-FFB17C05C974@.microsoft.com ¤¤¼¶¼g...
> "roles" here refers to active directory user. You must add user you want
to
> the active directory (using control panel->management tools->computer
> management->users)
> than you can use the user you have added in the service now
> "Lisa" wrote:
> > I've tried to add or delete a user to read report by using report
manager.
> > User Home -> Properties -> New Role Assignment
> > Then enter user's doman and id information and check the role. However,
it
> > shows ERROR.
> > The error message:
> > "Can't find the role '?' (rsRoleNotFound).
> > When i tried to delete one of the current user, it shows the same error
> > message.
> > The error message:
> > "Can't find the role '?' (rsRoleNotFound).
> >
> > However, in my roles table, all roles information are all complete and
> > correct.
> >
> > Can anyone tell me what's wrong?
> >
> >
> >
> >
> >
Cant add bit column to unique index
This is for SQL 2000 (SP 2) using Enterprise Manager. I have a table with a unique index comprised of several int fields. The index needs to include an additional bit field that is part of the table. But when I go to modify the index, the bit field name doesn't appear in the Column Name list.
Can anyone shed any light on the problem?
Thanks.
You can not create index on BIT data type and that's the reason you can not see column.|||Where can I find documentation on that? Why is that restriction in place?
|||JigneshP, you definitely can create a unique index using bit fields. I was able to do it via TSQL in Query Analyzer. Here's the sql I used:
DROP INDEX [dbo].[Material].IX_MaterialCREATE UNIQUE INDEX [IX_Material]ON [dbo].[Material] ([MaterialID], [MyBitField])ON [PRIMARY]GO
The bit field is MyBitField. I then verified the index works by inserting data that duplicated another row except for the bit field.
I'm still looking for someone to tell me why I can't add the bit column to an index via Enterprise Manager.|||
I found this link (http://sqlserver2000.databases.aspfaq.com/can-i-create-an-index-on-a-bit-column.html) that shows how to do it via Enterprise Manager. You have to do it from the Tasks menu / Manage Index.
|||Oh Thanks ZLA. Sorry about that.Cant access table due to no owner (I think)
{SQL-DMO} The name 'Table Name' was not found in the tables collection. If the name is a qualified name, use [] to seperate various parts of the name, and the try again.
Have tried to change the owner by using sp_changeobjectowner but I am either getting the syntax wrong or it ain't working.
Any ideas??
c8lWhen you look in Enterprise Manager, what do you see?|||Hi,
The only thing I see is the table name (TABLE1), the type (USER) and the creation date. The owner column is blank. Normally you would expect it to say dbo.|||Blank?
What happends when you run this in QA?
SELECT * FROM INFORMATION_SCHEMA.Tables
Maybe you need to close EM and relaunch it. ...|||Thanks for the help but the problem has been resolved, I had to manually update the sysobject table so that the UID for each of the objects that could not be accessed was set to 1. For some reason this was 6.
Cheers
c8l
Cant Access SQL Server (on Vista) from other Network PC (XP)
I have just installed Vista on one of my home network pcs. I also installed Outlook 2005 with Business Contact Manager. It installs an instance of SQL Server Express.
I have created another database on this engine that I need to access from other PCs on my network.
The server address is PCNAME\MSSMLBIZ and I can access the data the database using this name from the the Vista PC (PCNAME).
When I try to acess the database using the same credentials (server, database, username (sa) and password) from an XP PC on the network - it times out and returns an error . My guess is that it is not seeing the database service at all.
I have SQL Server Management Studio installed on the Vista PC and have full access to the instance.
Server authentication is set for SQL Server and Windows Authentication mode.
Can anyone help me?. I need to access the SQL Server from the XP PC.
Ian
Make sure that SQL Server Browser is running, you firewall is configured properly to allow the SQL Server traffic and that Remote connections are enabled for SQL Server. (See the screencats on my site for more information).
Firewall configuration: http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b914277
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Yes - that worked - as soon as I set remote access on Surface Area Configuration for Services and Connections page.
Regards Ian
|||I have just installed SQL Server 2005 Developer Edition on Windows Vista Ultimate, but i cant access and see "sql server managment studio" The only things i have are these >>>