Accessing data from a programmatically created SqlDataSource

Hi

I think I've programmatically created a SqlDataSource - which is what I want to do; but I can't seem to access details from the source - row 1, column 1, for example??

IfNot Page.IsPostBackThen

'Start by determining the connection string value

Dim connStringAsNew Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

'Create a SqlConnection instance

Using connString

'Specify the SQL query

Const sqlAsString ="SELECT eventID FROM viewEvents WHERE eventID=17"

'Create a SqlCommand instance

Dim myCommandAsNew Data.SqlClient.SqlCommand(sql, connString)

'Get back a DataSet

Dim myDataSetAsNew Data.DataSet

'Create a SqlDataAdapter instance

Dim myAdapterAsNew Data.SqlClient.SqlDataAdapter(myCommand)

myAdapter.Fill(myDataSet)

Label1.Text = myAdapter.Tables.Rows(0).Item("eventID").ToString() -??????

'Close the connection

connString.Close()

EndUsing

EndIf

Thanks for any help
Richard

No, you haven't programmatically created a SqlDataSource. You have used plain ADO.NET code to create and fill a DataSet. The DataAdapter is purely a bridge between the dataset and your data source (the database). It doesn't contain tables. The Dataset does though. It holds them in a zero-based collection:

Label1.Text = MyDataSet.Tables(0).Rows(0)("eventID").ToString()

But if all you want is one value from the database, you are better off using Command.ExecuteScalar():

Dim connString As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Using connString
Const sql As String = "SELECT eventID FROM viewEvents WHERE eventID=17"
Dim myCommand As New Data.SqlClient.SqlCommand(sql, connString)
Label1.Text = mycommand.ExecuteScalar().ToString()
...etc

Access: linked tables invisible

I've recently moved a database from mssql2000 on an NT Server machine
to mssql2000 running on a Win 2003 Server machine.
Now, when I try to use Access 2002 to Link an sql table, the Access
wizard isn't showing any of the sql tables. In fact, it only shows a
couple of the system tables: dbo.sysconstraints & dbo.syssegments and
one of the views in the database.
I'm using odbc to connect Access to the SQL Server. Everyting else
seems to work ok.
Could anyone suggest whats wrong here, or how to go about finding out
whats wrong?
regards
Richard
On Fri, 06 May 2005 09:01:29 +0100, Richard <richard@.softmode.net>
wrote:
mssql2000 running on a Win 2003 Server machine.
>Now, when I try to use Access 2002 to Link an sql table, the Access
>wizard isn't showing any of the sql tables. In fact, it only shows a
>couple of the system tables: dbo.sysconstraints & dbo.syssegments and
>one of the views in the database.
>
Problem solved by installing latest mdac on workstation.
Richard

Access Violations

Hi , I've noticed this behavior and I think I pinpointed the reason. But I
cannot understand why this is happening. I would really appreciate if
someone could shed some light on this for me.
We have a table that has instead of insert trigger on it. Whenever someone
is running an insert with order by the query fails with AV. If the trigger
is disabled the query executes fine. Here is the script below to recreate
the problem. We are running sql2k sp4 on win2003
/************************************************** ******/
-- create table
CREATE TABLE [dbo].[test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[refGeoID] [int] NOT NULL ,
[Action] [char] (1) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[ID] )
ON [PRIMARY]
GO
-- create trigger
create TRIGGER test_I_Trig ON dbo.test
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
DELETE FROM dbo.test
From dbo.test JT Join inserted I On I.[ID] = JT.[ID]
INSERT INTO dbo.test (refGeoID, [Action])
SELECT i.refGeoID, i.[Action]
FROM inserted i
set nocount off
end
-- insert data
insert into test (refGeoID, [Action])
select 1,'I'
union all
select 2,'I'
union all
select 3,'I'
union all
select 4,'I'
union all
select 5,'I'
union all
select 6,'I'
union all
select 7,'I'
union all
select 8,'I'
union all
select 9,'I'
union all
select 10,'I'
union all
select 11,'I'
union all
select 12,'I'
union all
select 13,'I'
union all
select 14,'I'
union all
select 15,'I'
union all
select 16,'I'
union all
select 17,'I'
union all
select 18,'I'
union all
select 19,'I'
union all
select 20,'I'
-- try to run an insert statement - it will fail
INSERT INTO dbo.test (refGeoID, [Action])
SELECT top 10 refGeoID, [Action] from test (nolock) order by id desc
-- now run the same query without order by - it works
INSERT INTO dbo.test (refGeoID, [Action])
SELECT top 10 refGeoID, [Action] from test (nolock) -- order by id desc
-- now drop the trigger and run the insert with order by - it works
drop TRIGGER test_I_Trig
INSERT INTO dbo.test (refGeoID, [Action])
SELECT top 10 refGeoID, [Action] from test (nolock) order by id desc
thank you
Hi
If this is an insert trigger then your identity value will not be in the
inserted table, therefore you are not going to achieve what you think you
want to do!
This may be similar to something like:
http://support.microsoft.com/default...b;en-us;272418
John
"Veronika" wrote:

> Hi , I've noticed this behavior and I think I pinpointed the reason. But I
> cannot understand why this is happening. I would really appreciate if
> someone could shed some light on this for me.
> We have a table that has instead of insert trigger on it. Whenever someone
> is running an insert with order by the query fails with AV. If the trigger
> is disabled the query executes fine. Here is the script below to recreate
> the problem. We are running sql2k sp4 on win2003
> /************************************************** ******/
> -- create table
> CREATE TABLE [dbo].[test] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [refGeoID] [int] NOT NULL ,
> [Action] [char] (1) NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[test] WITH NOCHECK ADD
> CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
> (
> [ID] )
> ON [PRIMARY]
> GO
>
> -- create trigger
> create TRIGGER test_I_Trig ON dbo.test
> INSTEAD OF INSERT
> AS
> BEGIN
> SET NOCOUNT ON
> DELETE FROM dbo.test
> From dbo.test JT Join inserted I On I.[ID] = JT.[ID]
> INSERT INTO dbo.test (refGeoID, [Action])
> SELECT i.refGeoID, i.[Action]
> FROM inserted i
> set nocount off
> end
> -- insert data
> insert into test (refGeoID, [Action])
> select 1,'I'
> union all
> select 2,'I'
> union all
> select 3,'I'
> union all
> select 4,'I'
> union all
> select 5,'I'
> union all
> select 6,'I'
> union all
> select 7,'I'
> union all
> select 8,'I'
> union all
> select 9,'I'
> union all
> select 10,'I'
> union all
> select 11,'I'
> union all
> select 12,'I'
> union all
> select 13,'I'
> union all
> select 14,'I'
> union all
> select 15,'I'
> union all
> select 16,'I'
> union all
> select 17,'I'
> union all
> select 18,'I'
> union all
> select 19,'I'
> union all
> select 20,'I'
>
> -- try to run an insert statement - it will fail
> INSERT INTO dbo.test (refGeoID, [Action])
> SELECT top 10 refGeoID, [Action] from test (nolock) order by id desc
> -- now run the same query without order by - it works
> INSERT INTO dbo.test (refGeoID, [Action])
> SELECT top 10 refGeoID, [Action] from test (nolock) -- order by id desc
> -- now drop the trigger and run the insert with order by - it works
> drop TRIGGER test_I_Trig
> INSERT INTO dbo.test (refGeoID, [Action])
> SELECT top 10 refGeoID, [Action] from test (nolock) order by id desc
> thank you
>
|||Hi John, thank you for your answer.
However, it doesn't seem that it's my problem. I am not inserting the
identity value. Also, with the trigger enabled the insert query works as
long as I am not using the order by clause. The AV is only generated when I
have both instead of trigger and order by .
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> If this is an insert trigger then your identity value will not be in the
> inserted table, therefore you are not going to achieve what you think you
> want to do!
> This may be similar to something like:
> http://support.microsoft.com/default...b;en-us;272418
> John
> "Veronika" wrote:
|||Apologies if this is posted twice!!
Hi
The AV is an issue as you should be presented with a sensible error message,
but your design is flawed. With no id values the delete statement will never
be satisfied, therefore making it redundant. Removing the delete statement
from the trigger still gives the AV, but it makes the trigger redundant as
the insert can be carried out directly.
You will need to redesign the process, possibly using a temporary table or a
dummy column such as:
-- create table
CREATE TABLE [dbo].[test] (
[ID] [int] IDENTITY NOT NULL ,
[refGeoID] [int] NOT NULL ,
[Action] [char] (1) NULL,
[Oldid] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[ID] )
ON [PRIMARY]
GO
-- create trigger
create TRIGGER test_I_Trig ON dbo.test
INSTEAD OF INSERT
AS
BEGIN
IF @.@.ROWCOUNT = 0 RETURN
SET NOCOUNT ON
-- SELECT * FROM INSERTED
INSERT INTO dbo.test (refGeoID, [Action])
SELECT i.refGeoID, i.[Action]
FROM inserted i
order by i.oldid asc
DELETE FROM t
FROM dbo.test t
JOIN INSERTED i ON t.id = i.oldid
end
-- insert data
insert into test (refGeoID, [Action])
select 1,'I'
union all
select 2,'I'
union all
select 3,'I'
union all
select 4,'I'
union all
select 5,'I'
union all
select 6,'I'
union all
select 7,'I'
union all
select 8,'I'
union all
select 9,'I'
union all
select 10,'I'
union all
select 11,'I'
union all
select 12,'I'
union all
select 13,'I'
union all
select 14,'I'
union all
select 15,'I'
union all
select 16,'I'
union all
select 17,'I'
union all
select 18,'I'
union all
select 19,'I'
union all
select 20,'I'
-- try to run an insert statement - it will fail
SELECT * from test (nolock) order by id desc
ID refGeoID Action Oldid
-- -- -- --
20 20 I NULL
19 19 I NULL
18 18 I NULL
17 17 I NULL
16 16 I NULL
15 15 I NULL
14 14 I NULL
13 13 I NULL
12 12 I NULL
11 11 I NULL
10 10 I NULL
9 9 I NULL
8 8 I NULL
7 7 I NULL
6 6 I NULL
5 5 I NULL
4 4 I NULL
3 3 I NULL
2 2 I NULL
1 1 I NULL
(20 row(s) affected)
INSERT INTO dbo.test ( refGeoID, [Action], oldid)
SELECT top 10 refGeoID, [Action], id from test (nolock)
order by id desc
SELECT * from test (nolock) order by id desc
ID refGeoID Action Oldid
-- -- -- --
30 20 I NULL
29 19 I NULL
28 18 I NULL
27 17 I NULL
26 16 I NULL
25 15 I NULL
24 14 I NULL
23 13 I NULL
22 12 I NULL
21 11 I NULL
10 10 I NULL
9 9 I NULL
8 8 I NULL
7 7 I NULL
6 6 I NULL
5 5 I NULL
4 4 I NULL
3 3 I NULL
2 2 I NULL
1 1 I NULL
(20 row(s) affected)
John
"Veronika" wrote:
[vbcol=seagreen]
> Hi John, thank you for your answer.
> However, it doesn't seem that it's my problem. I am not inserting the
> identity value. Also, with the trigger enabled the insert query works as
> long as I am not using the order by clause. The AV is only generated when I
> have both instead of trigger and order by .
> "John Bell" wrote:
|||We will review the design.
thank you
"John Bell" wrote:
[vbcol=seagreen]
> Apologies if this is posted twice!!
> Hi
> The AV is an issue as you should be presented with a sensible error message,
> but your design is flawed. With no id values the delete statement will never
> be satisfied, therefore making it redundant. Removing the delete statement
> from the trigger still gives the AV, but it makes the trigger redundant as
> the insert can be carried out directly.
> You will need to redesign the process, possibly using a temporary table or a
> dummy column such as:
> -- create table
> CREATE TABLE [dbo].[test] (
> [ID] [int] IDENTITY NOT NULL ,
> [refGeoID] [int] NOT NULL ,
> [Action] [char] (1) NULL,
> [Oldid] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[test] WITH NOCHECK ADD
> CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
> (
> [ID] )
> ON [PRIMARY]
> GO
> -- create trigger
> create TRIGGER test_I_Trig ON dbo.test
> INSTEAD OF INSERT
> AS
> BEGIN
> IF @.@.ROWCOUNT = 0 RETURN
> SET NOCOUNT ON
> -- SELECT * FROM INSERTED
> INSERT INTO dbo.test (refGeoID, [Action])
> SELECT i.refGeoID, i.[Action]
> FROM inserted i
> order by i.oldid asc
> DELETE FROM t
> FROM dbo.test t
> JOIN INSERTED i ON t.id = i.oldid
> end
> -- insert data
> insert into test (refGeoID, [Action])
> select 1,'I'
> union all
> select 2,'I'
> union all
> select 3,'I'
> union all
> select 4,'I'
> union all
> select 5,'I'
> union all
> select 6,'I'
> union all
> select 7,'I'
> union all
> select 8,'I'
> union all
> select 9,'I'
> union all
> select 10,'I'
> union all
> select 11,'I'
> union all
> select 12,'I'
> union all
> select 13,'I'
> union all
> select 14,'I'
> union all
> select 15,'I'
> union all
> select 16,'I'
> union all
> select 17,'I'
> union all
> select 18,'I'
> union all
> select 19,'I'
> union all
> select 20,'I'
>
> -- try to run an insert statement - it will fail
> SELECT * from test (nolock) order by id desc
> ID refGeoID Action Oldid
> -- -- -- --
> 20 20 I NULL
> 19 19 I NULL
> 18 18 I NULL
> 17 17 I NULL
> 16 16 I NULL
> 15 15 I NULL
> 14 14 I NULL
> 13 13 I NULL
> 12 12 I NULL
> 11 11 I NULL
> 10 10 I NULL
> 9 9 I NULL
> 8 8 I NULL
> 7 7 I NULL
> 6 6 I NULL
> 5 5 I NULL
> 4 4 I NULL
> 3 3 I NULL
> 2 2 I NULL
> 1 1 I NULL
> (20 row(s) affected)
>
> INSERT INTO dbo.test ( refGeoID, [Action], oldid)
> SELECT top 10 refGeoID, [Action], id from test (nolock)
> order by id desc
> SELECT * from test (nolock) order by id desc
> ID refGeoID Action Oldid
> -- -- -- --
> 30 20 I NULL
> 29 19 I NULL
> 28 18 I NULL
> 27 17 I NULL
> 26 16 I NULL
> 25 15 I NULL
> 24 14 I NULL
> 23 13 I NULL
> 22 12 I NULL
> 21 11 I NULL
> 10 10 I NULL
> 9 9 I NULL
> 8 8 I NULL
> 7 7 I NULL
> 6 6 I NULL
> 5 5 I NULL
> 4 4 I NULL
> 3 3 I NULL
> 2 2 I NULL
> 1 1 I NULL
> (20 row(s) affected)
> John
>
> "Veronika" wrote:

Access Project with MSDE2000

Hi Gurus,
I've got a question when using Access Project with MSDE2000. I can properly install MSDE and connect a Access Project with master database, but I can't create table or modify table structure, is there any thing wrong?
My os is XP, and Access 2000.
Thanks in advance
You probably dont want to be creating tables within the master db anyway.
When you load Access, chose 'Project (New Data)'. This should allow you to
create your own db, which you should be able to create tables in...
Cheers,
James Goodman
"Frank" <anonymous@.discussions.microsoft.com> wrote in message
news:4F160968-BBDD-4BEE-A13D-03ED349A3659@.microsoft.com...
> Hi Gurus,
> I've got a question when using Access Project with MSDE2000. I can
properly install MSDE and connect a Access Project with master database, but
I can't create table or modify table structure, is there any thing wrong?
> My os is XP, and Access 2000.
> Thanks in advance
>

Access Denied when starting Log Reader

I've been trying to set up the publisher for transactional replication.
The distribution server is sp2 the subscriber is sp3.
I have set up replication on Server1 to be its own distributor.
However I have been recieving errors such as this below:
SQL Server Enterprise Manager could not start the service 'SQLSERVERAGENT' on
server 'Server1'. 5 - (Access is denied)
and
Execute permission denied on object 'sp_mshelp_logreader_agentid'.
or
The could not access database db1 on Server1.
I also checked the services msssqlserver and sqlserveragent on the other
server and the 'log on as' is the same on both which has admin rights.
I'm really stumped, I don't know what to check next.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200603/1
Right click on the log reader agent and make the job owner sa, then right
click again and restart it.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:5d9a8c1242685@.uwe...
> I've been trying to set up the publisher for transactional replication.
> The distribution server is sp2 the subscriber is sp3.
> I have set up replication on Server1 to be its own distributor.
> However I have been recieving errors such as this below:
> SQL Server Enterprise Manager could not start the service 'SQLSERVERAGENT'
> on
> server 'Server1'. 5 - (Access is denied)
> and
> Execute permission denied on object 'sp_mshelp_logreader_agentid'.
> or
> The could not access database db1 on Server1.
> I also checked the services msssqlserver and sqlserveragent on the other
> server and the 'log on as' is the same on both which has admin rights.
> I'm really stumped, I don't know what to check next.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ation/200603/1
|||Please make sure that SQL Server Agent and the SQL Server Service both use
the same domain account (in local admins).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul Ibison wrote:
>Please make sure that SQL Server Agent and the SQL Server Service both use
>the same domain account (in local admins).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
The log reader agent is set to use SA. Still can't access database.
Both the services are using the same domain account.
I'm stumped. Thanks for the help nonetheless.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200603/1
|||Is the domain account a local administrator?
Also, can you start the sql server agent service manually?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi
I had the same problem. I applied Sp3 in the distributor. It started to
work..
P. Srinivasan MCDBA
Consultant
C-13/1,GF,Tulip Garden, Ardee City,
Sector-52,
Gurgaon-122003
India
Mobile:+91-9810321551
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eN1n8ibTGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Is the domain account a local administrator?
> Also, can you start the sql server agent service manually?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Access Denied on trying to connect

I've installed the latest MSDE version Re1A and MSDE Manager (from
Vale Software) and SQL works fine on the workstation on which msde is
installed. But when i try to create a connection with another
workstation, the connection fails.
I've installed msde on WinXP Pro SP2 with the following command
setup SAPWD="AStrongSAPwd" SECURITYMODE=SQL
I could not install mdac 2.8 as it is a part of SP2 which i've
allready installed.
If i try to telnet ipadres 1433, i get:
Can not connect to host, connection failed.
Uninstall the firewall didn't help a bit.
When i setup the connection variables like
Servernaam (i use an ip-adres)
SQL username en password
I can't select a database
If i test the connection, i get an error MS Data Link Error:
[DBNETLIB][ConnectionOpen(Connect()).] The SQL server does not exist
or access denied.
I've also to set the Network Library to dbmssocn, but no luck with
that.
MSDE Manager also can't connect form the client workstation. MSDE
Manager works fine on the host.
What am i missing?
PLEASE Help me
Frank
It is possible SQL is NOT listening on TCP. Thus, connections from the SQL
Server machine are successful because on the Server a SHARED MEMORY
connection is being made. You can verify what netlibs SQL is listening on
by looking at the latest SQL Errorlog (defaut location is c:\program
files\micrososft sql server\mssql\LOG\ERRORLOG). You need to look for a
line in this error log which states:
SQL server listening on TCP, Shared Memory, Named Pipes.
If you only have Shared Memory on this list, then only local connections
are allowed. Starting with SQL Server Desktop Engine ( MSDE 2000) SP3a ,
only local connectivity is permitted, by default. If you want to permit
remote connections to SQL Server Desktop Engine, follow the steps in the
following article:
814130: How to help secure network connectivity for SQL Server 2000 local
databases
http://support.microsoft.com/?id=814130
Also, check these KB's:
How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server -
ID: 841249
http://support.microsoft.com/?id=841249
How to manually enable TCP/IP on Windows XP Service Pack 2 for SQL - ID:
841252
http://support.microsoft.com/?id=841252
You may not be able to connect to an instance of SQL Server that is - ID:
841394
http://support.microsoft.com/?id=841394
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Fany, Thanx for your reply,
It did help me get 1 step closer to have it working.
I can define a connection and test it succesfully. But when i try to do the
same with two different third party software the connection doesn't work
properly.
With MDSE manager; the program can't get a connection running, it can't get
the information of the sql host.
And practically the same happens with the second tool.
Why is it possible to test the connection succesfully but it doesn't allow
its content to be reviewed? I know I have all the usernames and passwords
correct.
I hope you can point me again in the right direction...
gtz Frank
"Fany Vargas [MSFT]" wrote:

> It is possible SQL is NOT listening on TCP. Thus, connections from the SQL
> Server machine are successful because on the Server a SHARED MEMORY
> connection is being made. You can verify what netlibs SQL is listening on
> by looking at the latest SQL Errorlog (defaut location is c:\program
> files\micrososft sql server\mssql\LOG\ERRORLOG). You need to look for a
> line in this error log which states:
> SQL server listening on TCP, Shared Memory, Named Pipes.
> If you only have Shared Memory on this list, then only local connections
> are allowed. Starting with SQL Server Desktop Engine ( MSDE 2000) SP3a ,
> only local connectivity is permitted, by default. If you want to permit
> remote connections to SQL Server Desktop Engine, follow the steps in the
> following article:
> 814130: How to help secure network connectivity for SQL Server 2000 local
> databases
> http://support.microsoft.com/?id=814130
> Also, check these KB's:
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server -
> ID: 841249
> http://support.microsoft.com/?id=841249
> How to manually enable TCP/IP on Windows XP Service Pack 2 for SQL - ID:
> 841252
> http://support.microsoft.com/?id=841252
> You may not be able to connect to an instance of SQL Server that is - ID:
> 841394
> http://support.microsoft.com/?id=841394
>
> Fany Vargas
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
> Microsoft software to better protect against viruses and security
> vulnerabilities. The easiest way to do this is to visit the following
> websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
|||Hi Frank,
Sorry, came late to this one and it may have been discussed before but:
1. Try connecting with the servername "(local)". This will use shared
memory.
2. Try seeing if tcp is enabled. Do this with svrnetcn.exe.
3. Try checking you don't have XP SP2 firewall enabled. If so and you want
to use tcp, you'll need to set up an exception for it.
4. Make sure you have at least MDAC 2.7 installed.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:207BFCBD-1ED7-46A9-B140-C06746927E26@.microsoft.com...[vbcol=seagreen]
> Fany, Thanx for your reply,
> It did help me get 1 step closer to have it working.
> I can define a connection and test it succesfully. But when i try to do
> the
> same with two different third party software the connection doesn't work
> properly.
> With MDSE manager; the program can't get a connection running, it can't
> get
> the information of the sql host.
> And practically the same happens with the second tool.
> Why is it possible to test the connection succesfully but it doesn't allow
> its content to be reviewed? I know I have all the usernames and passwords
> correct.
> I hope you can point me again in the right direction...
> gtz Frank
> "Fany Vargas [MSFT]" wrote:
|||Hi Greg,
Thanx for your reply,
1. using msde locally is no problem, this works fine
2. allready got that enabled
3. I got Symantec Internet Security 2003 installed and made a firewall rule
to allow full communication on ports 1433 and 1434
4. MDAC 2.8 can't be installed because MDAC is a part of SP2
I try making a connection to the server with: telnet servername 1433
But i get an error (translated): No connection can be made to the host on
port 1433. Connection failed.
To be sure is wasn't Symantec that was blocking traffic, i disabled the
firewall. But no luck there.
Is there something else i can check?
thanx,
Frank
"Greg Low [MVP]" wrote:

> Hi Frank,
> Sorry, came late to this one and it may have been discussed before but:
> 1. Try connecting with the servername "(local)". This will use shared
> memory.
> 2. Try seeing if tcp is enabled. Do this with svrnetcn.exe.
> 3. Try checking you don't have XP SP2 firewall enabled. If so and you want
> to use tcp, you'll need to set up an exception for it.
> 4. Make sure you have at least MDAC 2.7 installed.
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
|||When you state "I can define a connection and test it succesfully. ", do
you mean from a remote machine? How did you "test and define" the
connection? For example did you create an ODBC DSN and that tested
successfully? If telnet failed then, etheir SQL is NOT listening on TCP,
there's a firewall blocking sql port 1433, or sql may not be listening on
port 1433 and may instead be listening on a different port.
1. Is SQL a default or a named instance?
2. Test the following commands from both a local and a remote server, if
sql is a named instance then in the OSQL commands below replace
<ipaddressofsqlmachine> with <ipaddressofsqlmachine\INSTANCENAME> and
replace <sqlserver_machine_name> with <sqlserver_machine_name\INSTANCE_NAME>
telnet ipaddressofsqlmachine 1433
telnet sqlserver_machine_name 1433
ping -a ipaddressofsqlmachine
ping sqlserver_machine_name
osql.exe -Stcp:ipaddressofsqlmachine -E -Q"select getdate()"
osql.exe -Stcp:ipaddressofsqlmachine,1433 -E -Q"select getdate()"
osql.exe -Sipaddressofsqlmachine -E -Q"select getdate()"
osql.exe -Slpc:ipaddressofsqlmachine -E -Q"select getdate()"
osql.exe -Snp:ipaddressofsqlmachine -E -Q"select getdate()"
osql.exe -Ssqlserver_machine_name -E -Q"select getdate()"
3. What does the latest sql errorlog (file named ERRORLOG with no
extension) state sql is listening on? Does it say tcp, shared memory and
named pipes? This errorlog will also state what port sql is listening on.
4. It seems the problem is SQL isn't listening on TCP. So please ensure
that SQL is listening on TCP (you can use the sql server network utility or
sql errorlog to do this)
5. Also, below is a good article on how to troubleshoot these kinds of
issues: How to troubleshoot connectivity issues in SQL Server 2000 - ID:
827422, http://support.microsoft.com/?id=827422
Best of luck!
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx