Hi,
Can the SQL Server Managament Studio connect to ODBC sources?
If so, how do you do it? I've not done much with ODBC before...
Thanks,
Alan
http://www.sadevelopment.com
Partition Boot Manager and Large Drive Tools utilities!
SSMS does not use the ODBC API. However, you can connect to any SQL Server
via SSMS as long as you know the server name and login credentials. You can
get that information by viewing the properties of the existing SQL Server
ODBC data source or connection string.
Hope this helps.
Dan Guzman
SQL Server MVP
"Default User" <nospam38925@.forme.com> wrote in message
news:4623694e$0$23404$c3e8da3@.news.astraweb.com...
> Hi,
> Can the SQL Server Managament Studio connect to ODBC sources?
> If so, how do you do it? I've not done much with ODBC before...
> Thanks,
> Alan
> http://www.sadevelopment.com
> Partition Boot Manager and Large Drive Tools utilities!
>
|||Hi Dan,
> SSMS does not use the ODBC API. However, you can connect to any SQL
> Server via SSMS as long as you know the server name and login credentials.
> You can get that information by viewing the properties of the existing SQL
> Server ODBC data source or connection string.
Too bad, I was hoping to connect to ODBC with it. Does anyone have any idea
what tool or tools I could use to connect and troubleshoot ODBC...
Alan
http://www.sadevelopment.com
Partition Boot Manager and Large Drive Tools utilities!
|||Can you elaborate on what you mean by "connect and troubleshoot ODBC"? If
you are trying to test ODBC data source connectivity, the ODBC data source
setup has a button to test the connection.
Hope this helps.
Dan Guzman
SQL Server MVP
"Default User" <nospam38925@.forme.com> wrote in message
news:4623b9c6$0$873$c3e8da3@.news.astraweb.com...
> Hi Dan,
>
> Too bad, I was hoping to connect to ODBC with it. Does anyone have any
> idea what tool or tools I could use to connect and troubleshoot ODBC...
> Alan
> http://www.sadevelopment.com
> Partition Boot Manager and Large Drive Tools utilities!
>
|||Hi,
> Can you elaborate on what you mean by "connect and troubleshoot ODBC"? If
> you are trying to test ODBC data source connectivity, the ODBC data source
> setup has a button to test the connection.
Yes, I'm looking for a tool that lets you connect to ODBC and use it much
the way the SQL management studio lets you connect to MSSQL...
Thanks,
Alan
|||> Yes, I'm looking for a tool that lets you connect to ODBC and use it much
> the way the SQL management studio lets you connect to MSSQL...
So you need to query ODBC data sources other than Micosoft SQL Server? If
you need only SQL Server, I still don't understand why you have the ODBC
requirement.
Hope this helps.
Dan Guzman
SQL Server MVP
"Default User" <nospam38925@.forme.com> wrote in message
news:4627a5d7$0$4674$c3e8da3@.news.astraweb.com...
> Hi,
>
> Yes, I'm looking for a tool that lets you connect to ODBC and use it much
> the way the SQL management studio lets you connect to MSSQL...
> Thanks,
> Alan
>
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Monday, March 19, 2012
Tuesday, February 14, 2012
Can OPENXML insert rows with some duplicate rows?
Hi All,
I'd like to insert rows that maybe have some duplicate rows with OPENXML
from stored procedure. In normal case, I've found that it cannot do that.
Is there anybody know how to accomplish this?
Thanks in advance,
Thana N.
I've just found that I can use "NOT IN" or "NOT EXISTS" to do it. But I
wondor about the performance of it. What is the best way for performance?
Thana N.
"Thana N." wrote:
> Hi All,
> I'd like to insert rows that maybe have some duplicate rows with OPENXML
> from stored procedure. In normal case, I've found that it cannot do that.
> Is there anybody know how to accomplish this?
> Thanks in advance,
> Thana N.
>
|||In this case you will have to decide how to deal with the duplicates.
This depends a lot on your data. If you were just inserting account
numbers then you could do:
insert into x
select acct#
from openxml(...)
group by acct#
In some cases you may want to sum the data (using sum), get the max, or
get the min. It will depend on your data.
insert into x
select acct#, sum(charges), min(timeleft), max(lastcall)
from openxml(...)
group by acct#
|||If your query would execute OpenXML more than once with the same resulting
rowset or it is large enough to benefit from an index, you may want to
insert the data from OpenXML into a temp table (and define an index if it
seems more performing).
HTH
Michael
"Thana N." <ThanaN@.discussions.microsoft.com> wrote in message
news:417C9E34-FF19-4EDB-B57C-345D36770535@.microsoft.com...[vbcol=seagreen]
> I've just found that I can use "NOT IN" or "NOT EXISTS" to do it. But I
> wondor about the performance of it. What is the best way for performance?
> Thana N.
> "Thana N." wrote:
I'd like to insert rows that maybe have some duplicate rows with OPENXML
from stored procedure. In normal case, I've found that it cannot do that.
Is there anybody know how to accomplish this?
Thanks in advance,
Thana N.
I've just found that I can use "NOT IN" or "NOT EXISTS" to do it. But I
wondor about the performance of it. What is the best way for performance?
Thana N.
"Thana N." wrote:
> Hi All,
> I'd like to insert rows that maybe have some duplicate rows with OPENXML
> from stored procedure. In normal case, I've found that it cannot do that.
> Is there anybody know how to accomplish this?
> Thanks in advance,
> Thana N.
>
|||In this case you will have to decide how to deal with the duplicates.
This depends a lot on your data. If you were just inserting account
numbers then you could do:
insert into x
select acct#
from openxml(...)
group by acct#
In some cases you may want to sum the data (using sum), get the max, or
get the min. It will depend on your data.
insert into x
select acct#, sum(charges), min(timeleft), max(lastcall)
from openxml(...)
group by acct#
|||If your query would execute OpenXML more than once with the same resulting
rowset or it is large enough to benefit from an index, you may want to
insert the data from OpenXML into a temp table (and define an index if it
seems more performing).
HTH
Michael
"Thana N." <ThanaN@.discussions.microsoft.com> wrote in message
news:417C9E34-FF19-4EDB-B57C-345D36770535@.microsoft.com...[vbcol=seagreen]
> I've just found that I can use "NOT IN" or "NOT EXISTS" to do it. But I
> wondor about the performance of it. What is the best way for performance?
> Thana N.
> "Thana N." wrote:
Can not write to a SQL table from Access 2003
I've read through many posts regarding this type of issue and all posts were
relevant but not exactly my problem.
I use Access 2003 and I have a simple linked table to a Sql2000 DB table, I
then have a form that I can use to view/amend data directly on the sql
table. However, I then have another table in the same DB that I have created
a form to view/amend the data but this table does not allow me to amend, I
can only view.
Both tables are in the same DB, and I am logged in as the same user. As far
as I can see both Tables have the same permissions. The only difference is
the table that I can not amend was created by performing a DTS Import. I
have ran the profiler and found that when accessing the table that fails to
amend it only passes a SELECT statement to the server, the table that does
amend passes a EXEC SP_PREPEXEC & SP_EXECUTESQL, so I'm confused if the
problem is in Access or on the Table in SQL. Any Help would be very much
appreciated.
Rob
SORRY, SORRY, SORRY. I've just realised what was happening, bit embarrassed
to say. I never had a primary key.
All sorted.
"Rob" <rbotterill@.hspg.com> wrote in message
news:uhZHI6lHFHA.3484@.TK2MSFTNGP12.phx.gbl...
> I've read through many posts regarding this type of issue and all posts
> were relevant but not exactly my problem.
> I use Access 2003 and I have a simple linked table to a Sql2000 DB table,
> I then have a form that I can use to view/amend data directly on the sql
> table. However, I then have another table in the same DB that I have
> created a form to view/amend the data but this table does not allow me to
> amend, I can only view.
> Both tables are in the same DB, and I am logged in as the same user. As
> far as I can see both Tables have the same permissions. The only
> difference is the table that I can not amend was created by performing a
> DTS Import. I have ran the profiler and found that when accessing the
> table that fails to amend it only passes a SELECT statement to the server,
> the table that does amend passes a EXEC SP_PREPEXEC & SP_EXECUTESQL, so
> I'm confused if the problem is in Access or on the Table in SQL. Any Help
> would be very much appreciated.
> Rob
>
|||"Rob" <rbotterill@.hspg.com> wrote in message
news:OLe0EknHFHA.2552@.TK2MSFTNGP10.phx.gbl...
> SORRY, SORRY, SORRY. I've just realised what was happening, bit
embarrassed
> to say. I never had a primary key.
Don't be sorry, this is a common mistake... you may also want to add a
column of timestamp as well.
http://support.microsoft.com/default...b;en-us;208842
Steve
relevant but not exactly my problem.
I use Access 2003 and I have a simple linked table to a Sql2000 DB table, I
then have a form that I can use to view/amend data directly on the sql
table. However, I then have another table in the same DB that I have created
a form to view/amend the data but this table does not allow me to amend, I
can only view.
Both tables are in the same DB, and I am logged in as the same user. As far
as I can see both Tables have the same permissions. The only difference is
the table that I can not amend was created by performing a DTS Import. I
have ran the profiler and found that when accessing the table that fails to
amend it only passes a SELECT statement to the server, the table that does
amend passes a EXEC SP_PREPEXEC & SP_EXECUTESQL, so I'm confused if the
problem is in Access or on the Table in SQL. Any Help would be very much
appreciated.
Rob
SORRY, SORRY, SORRY. I've just realised what was happening, bit embarrassed
to say. I never had a primary key.
All sorted.
"Rob" <rbotterill@.hspg.com> wrote in message
news:uhZHI6lHFHA.3484@.TK2MSFTNGP12.phx.gbl...
> I've read through many posts regarding this type of issue and all posts
> were relevant but not exactly my problem.
> I use Access 2003 and I have a simple linked table to a Sql2000 DB table,
> I then have a form that I can use to view/amend data directly on the sql
> table. However, I then have another table in the same DB that I have
> created a form to view/amend the data but this table does not allow me to
> amend, I can only view.
> Both tables are in the same DB, and I am logged in as the same user. As
> far as I can see both Tables have the same permissions. The only
> difference is the table that I can not amend was created by performing a
> DTS Import. I have ran the profiler and found that when accessing the
> table that fails to amend it only passes a SELECT statement to the server,
> the table that does amend passes a EXEC SP_PREPEXEC & SP_EXECUTESQL, so
> I'm confused if the problem is in Access or on the Table in SQL. Any Help
> would be very much appreciated.
> Rob
>
|||"Rob" <rbotterill@.hspg.com> wrote in message
news:OLe0EknHFHA.2552@.TK2MSFTNGP10.phx.gbl...
> SORRY, SORRY, SORRY. I've just realised what was happening, bit
embarrassed
> to say. I never had a primary key.
Don't be sorry, this is a common mistake... you may also want to add a
column of timestamp as well.
http://support.microsoft.com/default...b;en-us;208842
Steve
Sunday, February 12, 2012
Can not start tcp port 1433 on MSDE
I've got an installation of MSDE that insists on only allowing shared memory
access. This works fine but I would like to access th db via tcp. I have
ran SVRNETCN.exe and verified that TCP is selected. I have even verified
that the port number etc. is set in the registry. It just refuses to
avtivate tcp when I restart MSDE or the box it's runinning on.
Here's the errorlog:
2004-04-30 00:03:33.20 server Microsoft SQL Server 2000 - 8.00.884
(Intel X86)
Nov 29 2003 20:52:47
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine (Windows) on Windows NT 5.2 (Build 3790: )
2004-04-30 00:03:33.20 server Copyright (C) 1988-2002 Microsoft
Corporation.
2004-04-30 00:03:33.20 server All rights reserved.
2004-04-30 00:03:33.20 server Server Process ID is 4416.
2004-04-30 00:03:33.20 server Logging SQL Server messages in file
'C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\LOG\ERRORLOG'.
2004-04-30 00:03:33.30 server SQL Server is starting at priority class
'normal'(1 CPU detected).
2004-04-30 00:03:33.38 server SQL Server configured for thread mode
processing.
2004-04-30 00:03:33.38 server Using dynamic lock allocation. [500] Lock
Blocks, [1000] Lock Owner Blocks.
2004-04-30 00:03:33.40 spid3 Starting up database 'master'.
2004-04-30 00:03:34.20 server Using 'SSNETLIB.DLL' version '8.0.880'.
2004-04-30 00:03:34.21 server SQL server listening on Shared Memory.
2004-04-30 00:03:34.21 server SQL Server is ready for client connections
2004-04-30 00:03:34.21 spid5 Starting up database 'model'.
2004-04-30 00:03:34.27 spid3 Server name is 'CHEF\SHAREPOINT'.
2004-04-30 00:03:34.27 spid3 Skipping startup of clean database id 4
2004-04-30 00:03:34.27 spid3 Skipping startup of clean database id 5
2004-04-30 00:03:34.27 spid3 Skipping startup of clean database id 6
2004-04-30 00:03:34.44 spid5 Clearing tempdb database.
2004-04-30 00:03:35.35 spid5 Starting up database 'tempdb'.
2004-04-30 00:03:35.55 spid3 Recovery complete.
Where in that log do you see evidence of SQL Server "refusing to activate
tcp"? Did you really mean, "I can't connect to SQL Server on port 1433"?
If so, I would check your network/router/firewall settings before assuming
the problem is service- or machine-specific.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Karl Foley" <karl@.thefoleyhouse.co.uk> wrote in message
news:e3R9sIkLEHA.2456@.TK2MSFTNGP12.phx.gbl...
> I've got an installation of MSDE that insists on only allowing shared
> memory
> access. This works fine but I would like to access th db via tcp. I have
> ran SVRNETCN.exe and verified that TCP is selected. I have even verified
> that the port number etc. is set in the registry. It just refuses to
> avtivate tcp when I restart MSDE or the box it's runinning on.
> Here's the errorlog:
> 2004-04-30 00:03:33.20 server Microsoft SQL Server 2000 - 8.00.884
> (Intel X86)
> Nov 29 2003 20:52:47
> Copyright (c) 1988-2003 Microsoft Corporation
> Desktop Engine (Windows) on Windows NT 5.2 (Build 3790: )
> 2004-04-30 00:03:33.20 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2004-04-30 00:03:33.20 server All rights reserved.
> 2004-04-30 00:03:33.20 server Server Process ID is 4416.
> 2004-04-30 00:03:33.20 server Logging SQL Server messages in file
> 'C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\LOG\ERRORLOG'.
> 2004-04-30 00:03:33.30 server SQL Server is starting at priority class
> 'normal'(1 CPU detected).
> 2004-04-30 00:03:33.38 server SQL Server configured for thread mode
> processing.
> 2004-04-30 00:03:33.38 server Using dynamic lock allocation. [500] Lock
> Blocks, [1000] Lock Owner Blocks.
> 2004-04-30 00:03:33.40 spid3 Starting up database 'master'.
> 2004-04-30 00:03:34.20 server Using 'SSNETLIB.DLL' version '8.0.880'.
> 2004-04-30 00:03:34.21 server SQL server listening on Shared Memory.
> 2004-04-30 00:03:34.21 server SQL Server is ready for client
> connections
> 2004-04-30 00:03:34.21 spid5 Starting up database 'model'.
> 2004-04-30 00:03:34.27 spid3 Server name is 'CHEF\SHAREPOINT'.
> 2004-04-30 00:03:34.27 spid3 Skipping startup of clean database id 4
> 2004-04-30 00:03:34.27 spid3 Skipping startup of clean database id 5
> 2004-04-30 00:03:34.27 spid3 Skipping startup of clean database id 6
> 2004-04-30 00:03:34.44 spid5 Clearing tempdb database.
> 2004-04-30 00:03:35.35 spid5 Starting up database 'tempdb'.
> 2004-04-30 00:03:35.55 spid3 Recovery complete.
>
>
|||"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%237IBIxnLEHA.2592@.tk2msftngp13.phx.gbl...
> Where in that log do you see evidence of SQL Server "refusing to activate
> tcp"?
Thanks for the offer of help, I'm just abut ready to give up on this!
Just about here:
2004-04-30 00:03:34.20 server Using 'SSNETLIB.DLL' version '8.0.880'.
2004-04-30 00:03:34.21 server SQL server listening on Shared Memory.
2004-04-30 00:03:34.21 server SQL Server is ready for client connections
You can see it's only using shared memory.
> Did you really mean, "I can't connect to SQL Server on port 1433"?
> If so, I would check your network/router/firewall settings before assuming
> the problem is service- or machine-specific.
No. I've tried to read all the posts on here regarding the problem and have
done multiple searches on google to try and solve this problem. No matter
what I do, MSDE will not listen on TCP port 1433. Here's a quick snip of
the output of netstat -an:
Active Connections
Proto Local Address Foreign Address State
TCP 0.0.0.0:25 0.0.0.0:0 LISTENING
TCP 0.0.0.0:53 0.0.0.0:0 LISTENING
[Snip]
TCP 0.0.0.0:691 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1025 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1028 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1533 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1723 0.0.0.0:0 LISTENING
[Snip]
As you can see, no port 1433 is listening here. I have only tried
connecting to port 1433 from this machine but can't see the point of trying
another machine if I can see the port is not open. This machine does not
have any firewall software enabled\installed on it. That functionality is
being provided by a hardware device.
As the version of MSDE seems so much newer than anything else I've seen
posted I am beginning to wonder if it is some kind of security feature...
|||> As the version of MSDE seems so much newer
Yes, 880 is quite a recent patch. Care to share where you obtained it?
Anyway, run svrnetcn.exe (it should execute from command line just like
that, otherwise it's in SQL Server's Tools\Binn folder) and make sure TCP/IP
is on the enabled side. If it isn't, move it over, hit apply, and restart
SQL Server. If that doesn't help, highlight TCP/IP and hit properties.
Make sure hide server is unchecked, then try changing the default TCP port
to some other number, hit apply, restart SQL Server, then change the default
TCP port back to 1433, hit apply, and restart SQL Server.
If TCP/IP is the ONLY enabled protocol, you might try enabling named pipes
just for fun, and then see http://support.microsoft.com/?id=306865 for a
registry setting that may need a fix.
If this is the way it has worked since you installed MSDE, you might
consider uninstalling and reinstalling (from SP3a) making sure to set
DISABLENETWORKPROTOCOL=0 in the setup ini file. Your patch level of 880
leads me to believe that installing from scratch would be the cleanest
solution anyway... sometimes these one-off patches have various side effects
that simply haven't been tested (which is why they are typically not
announced and readily available to the masses).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||You might also need to check to see if it is truly MSDE or is it WMSDE.. If
it is WMSDE it can only be accessed from the same machine. Based on the
instance name of SharePoint I would assume that it is really WMSDE..
http://www.microsoft.com/resources/d...us/stsf17.mspx
If you need to access/use it for other purposes and/or from other machines
you will need to upgrade the instance to full SQL server.. or another
possible option would be to use a different instance of MSDE for your other
needs.
Hope that helps,
David Copeland
Microsoft Small Business Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:u0HZpsyLEHA.2148@.TK2MSFTNGP09.phx.gbl...
> Yes, 880 is quite a recent patch. Care to share where you obtained it?
> Anyway, run svrnetcn.exe (it should execute from command line just like
> that, otherwise it's in SQL Server's Tools\Binn folder) and make sure
TCP/IP
> is on the enabled side. If it isn't, move it over, hit apply, and restart
> SQL Server. If that doesn't help, highlight TCP/IP and hit properties.
> Make sure hide server is unchecked, then try changing the default TCP port
> to some other number, hit apply, restart SQL Server, then change the
default
> TCP port back to 1433, hit apply, and restart SQL Server.
> If TCP/IP is the ONLY enabled protocol, you might try enabling named pipes
> just for fun, and then see http://support.microsoft.com/?id=306865 for a
> registry setting that may need a fix.
> If this is the way it has worked since you installed MSDE, you might
> consider uninstalling and reinstalling (from SP3a) making sure to set
> DISABLENETWORKPROTOCOL=0 in the setup ini file. Your patch level of 880
> leads me to believe that installing from scratch would be the cleanest
> solution anyway... sometimes these one-off patches have various side
effects
> that simply haven't been tested (which is why they are typically not
> announced and readily available to the masses).
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
|||"David Copeland [MSFT]" <davidcop@.online.microsoft.com> wrote in message
news:e7Aoio6LEHA.3216@.TK2MSFTNGP12.phx.gbl...
> You might also need to check to see if it is truly MSDE or is it WMSDE..
If
> it is WMSDE it can only be accessed from the same machine. Based on the
> instance name of SharePoint I would assume that it is really WMSDE..
Fantastic - That's exactly it.
I installed another instance of MSDE and this runs perfectly over TCP port
1433 as expected. I found it confusing because:
I didn't know that WMSDE was a different product to MSDE or that WMSDE
indeed even existed.
The tools are all there to enable network connectivity even though WMSDE
doesn't use them.
Thanks everyone.
access. This works fine but I would like to access th db via tcp. I have
ran SVRNETCN.exe and verified that TCP is selected. I have even verified
that the port number etc. is set in the registry. It just refuses to
avtivate tcp when I restart MSDE or the box it's runinning on.
Here's the errorlog:
2004-04-30 00:03:33.20 server Microsoft SQL Server 2000 - 8.00.884
(Intel X86)
Nov 29 2003 20:52:47
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine (Windows) on Windows NT 5.2 (Build 3790: )
2004-04-30 00:03:33.20 server Copyright (C) 1988-2002 Microsoft
Corporation.
2004-04-30 00:03:33.20 server All rights reserved.
2004-04-30 00:03:33.20 server Server Process ID is 4416.
2004-04-30 00:03:33.20 server Logging SQL Server messages in file
'C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\LOG\ERRORLOG'.
2004-04-30 00:03:33.30 server SQL Server is starting at priority class
'normal'(1 CPU detected).
2004-04-30 00:03:33.38 server SQL Server configured for thread mode
processing.
2004-04-30 00:03:33.38 server Using dynamic lock allocation. [500] Lock
Blocks, [1000] Lock Owner Blocks.
2004-04-30 00:03:33.40 spid3 Starting up database 'master'.
2004-04-30 00:03:34.20 server Using 'SSNETLIB.DLL' version '8.0.880'.
2004-04-30 00:03:34.21 server SQL server listening on Shared Memory.
2004-04-30 00:03:34.21 server SQL Server is ready for client connections
2004-04-30 00:03:34.21 spid5 Starting up database 'model'.
2004-04-30 00:03:34.27 spid3 Server name is 'CHEF\SHAREPOINT'.
2004-04-30 00:03:34.27 spid3 Skipping startup of clean database id 4
2004-04-30 00:03:34.27 spid3 Skipping startup of clean database id 5
2004-04-30 00:03:34.27 spid3 Skipping startup of clean database id 6
2004-04-30 00:03:34.44 spid5 Clearing tempdb database.
2004-04-30 00:03:35.35 spid5 Starting up database 'tempdb'.
2004-04-30 00:03:35.55 spid3 Recovery complete.
Where in that log do you see evidence of SQL Server "refusing to activate
tcp"? Did you really mean, "I can't connect to SQL Server on port 1433"?
If so, I would check your network/router/firewall settings before assuming
the problem is service- or machine-specific.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Karl Foley" <karl@.thefoleyhouse.co.uk> wrote in message
news:e3R9sIkLEHA.2456@.TK2MSFTNGP12.phx.gbl...
> I've got an installation of MSDE that insists on only allowing shared
> memory
> access. This works fine but I would like to access th db via tcp. I have
> ran SVRNETCN.exe and verified that TCP is selected. I have even verified
> that the port number etc. is set in the registry. It just refuses to
> avtivate tcp when I restart MSDE or the box it's runinning on.
> Here's the errorlog:
> 2004-04-30 00:03:33.20 server Microsoft SQL Server 2000 - 8.00.884
> (Intel X86)
> Nov 29 2003 20:52:47
> Copyright (c) 1988-2003 Microsoft Corporation
> Desktop Engine (Windows) on Windows NT 5.2 (Build 3790: )
> 2004-04-30 00:03:33.20 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2004-04-30 00:03:33.20 server All rights reserved.
> 2004-04-30 00:03:33.20 server Server Process ID is 4416.
> 2004-04-30 00:03:33.20 server Logging SQL Server messages in file
> 'C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\LOG\ERRORLOG'.
> 2004-04-30 00:03:33.30 server SQL Server is starting at priority class
> 'normal'(1 CPU detected).
> 2004-04-30 00:03:33.38 server SQL Server configured for thread mode
> processing.
> 2004-04-30 00:03:33.38 server Using dynamic lock allocation. [500] Lock
> Blocks, [1000] Lock Owner Blocks.
> 2004-04-30 00:03:33.40 spid3 Starting up database 'master'.
> 2004-04-30 00:03:34.20 server Using 'SSNETLIB.DLL' version '8.0.880'.
> 2004-04-30 00:03:34.21 server SQL server listening on Shared Memory.
> 2004-04-30 00:03:34.21 server SQL Server is ready for client
> connections
> 2004-04-30 00:03:34.21 spid5 Starting up database 'model'.
> 2004-04-30 00:03:34.27 spid3 Server name is 'CHEF\SHAREPOINT'.
> 2004-04-30 00:03:34.27 spid3 Skipping startup of clean database id 4
> 2004-04-30 00:03:34.27 spid3 Skipping startup of clean database id 5
> 2004-04-30 00:03:34.27 spid3 Skipping startup of clean database id 6
> 2004-04-30 00:03:34.44 spid5 Clearing tempdb database.
> 2004-04-30 00:03:35.35 spid5 Starting up database 'tempdb'.
> 2004-04-30 00:03:35.55 spid3 Recovery complete.
>
>
|||"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%237IBIxnLEHA.2592@.tk2msftngp13.phx.gbl...
> Where in that log do you see evidence of SQL Server "refusing to activate
> tcp"?
Thanks for the offer of help, I'm just abut ready to give up on this!
Just about here:
2004-04-30 00:03:34.20 server Using 'SSNETLIB.DLL' version '8.0.880'.
2004-04-30 00:03:34.21 server SQL server listening on Shared Memory.
2004-04-30 00:03:34.21 server SQL Server is ready for client connections
You can see it's only using shared memory.
> Did you really mean, "I can't connect to SQL Server on port 1433"?
> If so, I would check your network/router/firewall settings before assuming
> the problem is service- or machine-specific.
No. I've tried to read all the posts on here regarding the problem and have
done multiple searches on google to try and solve this problem. No matter
what I do, MSDE will not listen on TCP port 1433. Here's a quick snip of
the output of netstat -an:
Active Connections
Proto Local Address Foreign Address State
TCP 0.0.0.0:25 0.0.0.0:0 LISTENING
TCP 0.0.0.0:53 0.0.0.0:0 LISTENING
[Snip]
TCP 0.0.0.0:691 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1025 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1028 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1533 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1723 0.0.0.0:0 LISTENING
[Snip]
As you can see, no port 1433 is listening here. I have only tried
connecting to port 1433 from this machine but can't see the point of trying
another machine if I can see the port is not open. This machine does not
have any firewall software enabled\installed on it. That functionality is
being provided by a hardware device.
As the version of MSDE seems so much newer than anything else I've seen
posted I am beginning to wonder if it is some kind of security feature...
|||> As the version of MSDE seems so much newer
Yes, 880 is quite a recent patch. Care to share where you obtained it?
Anyway, run svrnetcn.exe (it should execute from command line just like
that, otherwise it's in SQL Server's Tools\Binn folder) and make sure TCP/IP
is on the enabled side. If it isn't, move it over, hit apply, and restart
SQL Server. If that doesn't help, highlight TCP/IP and hit properties.
Make sure hide server is unchecked, then try changing the default TCP port
to some other number, hit apply, restart SQL Server, then change the default
TCP port back to 1433, hit apply, and restart SQL Server.
If TCP/IP is the ONLY enabled protocol, you might try enabling named pipes
just for fun, and then see http://support.microsoft.com/?id=306865 for a
registry setting that may need a fix.
If this is the way it has worked since you installed MSDE, you might
consider uninstalling and reinstalling (from SP3a) making sure to set
DISABLENETWORKPROTOCOL=0 in the setup ini file. Your patch level of 880
leads me to believe that installing from scratch would be the cleanest
solution anyway... sometimes these one-off patches have various side effects
that simply haven't been tested (which is why they are typically not
announced and readily available to the masses).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||You might also need to check to see if it is truly MSDE or is it WMSDE.. If
it is WMSDE it can only be accessed from the same machine. Based on the
instance name of SharePoint I would assume that it is really WMSDE..
http://www.microsoft.com/resources/d...us/stsf17.mspx
If you need to access/use it for other purposes and/or from other machines
you will need to upgrade the instance to full SQL server.. or another
possible option would be to use a different instance of MSDE for your other
needs.
Hope that helps,
David Copeland
Microsoft Small Business Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:u0HZpsyLEHA.2148@.TK2MSFTNGP09.phx.gbl...
> Yes, 880 is quite a recent patch. Care to share where you obtained it?
> Anyway, run svrnetcn.exe (it should execute from command line just like
> that, otherwise it's in SQL Server's Tools\Binn folder) and make sure
TCP/IP
> is on the enabled side. If it isn't, move it over, hit apply, and restart
> SQL Server. If that doesn't help, highlight TCP/IP and hit properties.
> Make sure hide server is unchecked, then try changing the default TCP port
> to some other number, hit apply, restart SQL Server, then change the
default
> TCP port back to 1433, hit apply, and restart SQL Server.
> If TCP/IP is the ONLY enabled protocol, you might try enabling named pipes
> just for fun, and then see http://support.microsoft.com/?id=306865 for a
> registry setting that may need a fix.
> If this is the way it has worked since you installed MSDE, you might
> consider uninstalling and reinstalling (from SP3a) making sure to set
> DISABLENETWORKPROTOCOL=0 in the setup ini file. Your patch level of 880
> leads me to believe that installing from scratch would be the cleanest
> solution anyway... sometimes these one-off patches have various side
effects
> that simply haven't been tested (which is why they are typically not
> announced and readily available to the masses).
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
|||"David Copeland [MSFT]" <davidcop@.online.microsoft.com> wrote in message
news:e7Aoio6LEHA.3216@.TK2MSFTNGP12.phx.gbl...
> You might also need to check to see if it is truly MSDE or is it WMSDE..
If
> it is WMSDE it can only be accessed from the same machine. Based on the
> instance name of SharePoint I would assume that it is really WMSDE..
Fantastic - That's exactly it.
I installed another instance of MSDE and this runs perfectly over TCP port
1433 as expected. I found it confusing because:
I didn't know that WMSDE was a different product to MSDE or that WMSDE
indeed even existed.
The tools are all there to enable network connectivity even though WMSDE
doesn't use them.
Thanks everyone.
Subscribe to:
Posts (Atom)