Network access to Sql Server 2005 Express

I have an application written in VB Express and uses SQL Server 2005 Express that runs on my local machine (name JERRY). I published it onto a CD and installed it on another computer (JKNETWORK) on my home network.

I've already modified SQL Server Configuration Manager to enable TCP/IP and Shared Memory. I have also added sqlservr.exe to the exceptions in the Microsoft firewall exceptions list.

The application opens with a login form that asks for username and password and uses the following connection string:

modUserName = txtUserName.Text

modPassWord = txtPassWord.Text

Dim ConnectionStringMaster As String =

_"Server=jerry\SQLEXPRESS;" & _

"DataBase=master;" & _

"user ID=" & modUserName & ";password=" & modPassWord

This all works great on JERRY but doesn't work from JKNETWORK. I get an error message that contains:

...When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. ....

Can someone help me figure out how to get the remote access to work?

Thanks,

JerryK

Did you enable Remote Connections? If not, see Surface Area Configuraiton tool for more info: http://msdn2.microsoft.com/en-us/library/ms161956.aspx.

|||

Hi Greg,

Yes, I did enable Remote Connections on the Surface Area Configuraiton tool. It's set for Local and Remote and with TCP/IP and Named Pipes.

If you have any other ideas, I'd certainly appreciate the help.

Thanks,

JerryK

|||

Can you access sql server on your machine from a remote machine using osql.exe? If so, then it's something with your app. If not, then it's some machine/sql configuration. Are you using winxp sp2? See if the suggestions in this thread can help. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192102&SiteID=1

|||

Greg,

I looked at the thread and have this question: What are Server Client Tools, where do I get them from to put them on the client?

I tried copying osql.exe to the client, but it looks like I need more than that because I got an error message about a missing file.

I might add that the server machine (JERRY) is a Window XP Home machine and not the Professional. But, the client machine is Windows XP Professional. Also, MSDE is still on the server machine. Could either of those circumstances have anything to do with it?

Thanks again,

JerryK

|||

Hi Jerry,

Your problem is likely caused by not having SQL Browser turned on and making an Exception for Browser in the firewall. When you are trying to connect to a named instance such as JerryK\sqlexpress, you need to have SQL Browser running on the server in order for the instance name to be recognized unless you are connecting to the server using a specific port number. Since SQL Browser lisents on it's own port, you also have to make the Exception for Browser in the firewall.

Once you've done this, you should be good to go.

Regards,
Mike Wachal
SQL Express

|||

Hi Mike,

I made sure that the browser is turned on and sqlservr.exe is in the exclude list. But, same error is reported. If I turn the Microsoft firewall off, there is no problem. If I turn on the Norton firewall it is ok too.

Are there any other settings related to the Microsoft firewall I should be concerned about?

My home network is a cable modem connected to a LinkSys router. Could there be some conflict here?

Thanks,

JerryK

|||

Jerry,

In addition to sqlservr.exe you need to add %Program Files%\Microsoft SQL Server\90\Shared\sqlbrowser.exe to the exception list.

Check out the following blog for more information: https://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Cheers,
Dan

|||

Thank you Dan. That was the missing piece..

Thanks again,

jerryK

nesting limit exceeded...but I don't understand why.

I have written a recursive function that generates an XML hierarchy. I have gone through the data being selected and I have verified that the hierarchy is only 19 levels at its deepest. (there are several thousand records involved, and 1 level may have several hundred records). But, I am receiving this error:
"Msg 217, Level 16, State 1, Line 1
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."

Here is the function I have written:

ALTER FUNCTION [dbo].[fn_WPMTREE](@.SceneID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN
RETURN
(SELECT s.ID As "@.id",
s.TITLE as "@.title",
s.CLASS_ID as "@.clsID",
CASE WHEN s.PARENT_ID=@.SceneID
THEN dbo.fn_WPMTREE(s.ID)
END
FROM SCENE s
WHERE s.PARENT_ID = @.SceneID
FOR XML PATH('Scene'), TYPE)
END

Does anyone have any suggestions as to why I may be getting this error, or how to debug for it or work around it?

Thank you for any advice you can give.

Do you have any records in which ID = PARENT_ID?

Run SELECT ID, PARENT_ID FROM SCENE WHERE ID = PARENT_ID and see.

If so, you might want to consider adding a check constraint to the table that would forbid such a circumstance. Another alternative might be to exit the function if the function detects ID = PARENT_ID.

|||

When I mockup with:

create table dbo.scene
( id integer,
parent_id integer,
class_id integer,
title varchar(20)
)
go

insert into dbo.scene values (1, null, 1, 'This is a test')
insert into dbo.scene values (2, 1, 1, 'Record #2')
insert into dbo.scene values (3, 2, 1, 'Record #3')

and run:

select dbo.fn_WPMTREE (1) as [the Scene]

I get:

-- the Scene
-- -
-- <Scene id="2" title="Record #2" clsID="1"><Scene id="3" title="Record #3" clsID="1" /></Scene>

Is this what you expect?

|||Absolutely brilliant.

I had a scene that had a PARENT_ID = ID. And, that caused the infinite loop. I thought it was a loop being created somewhere, but I thought it was of the type Scene1.Parent_ID = Scene2.ID; Scene2.Parent_ID = Scene1.ID.
But, it was even more direct than that.

Thanks so much!
Sincerely.
roger

Need to understand writing to the Eventlog.

SQL2K
SP4
Howdy all. Im trying to have info written to the Eventlog when a deadlock
occurs, and growing frustrated trying to understand. My original
understanding was that I should RClick the server/ all tasks/ Manage
messages/ 1205 in the Error number/ Find/ Edit/ check Always write to
Eventlog/ OK/ OK.
I took those actions and purposely generated some deadlocks to no avail.
Then I found out what I really need to do is start SQL server by doing
"sqlservr -c -T1204" as instructed in
http://support.microsoft.com/defaul...b;en-us;169960.
Is this the only way? I really dont want to have to bounce all my production
boxes to accomplish this task.
TIA, ChrisR.You don't need to use -T1204 or run sqlsrvr -c -T1204 in order to log the
event in the app eventlog. The trace flag is to log a deadlock graph.
To log the deadlock in the app eventlog, it's sufficient to change the
logging behavior of error 1205 to 'Always write to eventlog'. This is the
same as executing the following:
EXEC sp_altermessage 1205, 'with_log', 'true'
But my experience is that after you have made that change, you need to
recycle the SQL Server instance. I have never had luck not recycling the SQL
instance, and I don't know whether this is a bug or a feature by design.
Linchi
"ChrisR" wrote:

> SQL2K
> SP4
> Howdy all. Im trying to have info written to the Eventlog when a deadlock
> occurs, and growing frustrated trying to understand. My original
> understanding was that I should RClick the server/ all tasks/ Manage
> messages/ 1205 in the Error number/ Find/ Edit/ check Always write to
> Eventlog/ OK/ OK.
> I took those actions and purposely generated some deadlocks to no avail.
> Then I found out what I really need to do is start SQL server by doing
> "sqlservr -c -T1204" as instructed in
> http://support.microsoft.com/defaul...b;en-us;169960.
> Is this the only way? I really dont want to have to bounce all my producti
on
> boxes to accomplish this task.
> TIA, ChrisR.
>|||Perfect, thanks.
"Linchi Shea" wrote:
[vbcol=seagreen]
> You don't need to use -T1204 or run sqlsrvr -c -T1204 in order to log the
> event in the app eventlog. The trace flag is to log a deadlock graph.
> To log the deadlock in the app eventlog, it's sufficient to change the
> logging behavior of error 1205 to 'Always write to eventlog'. This is the
> same as executing the following:
> EXEC sp_altermessage 1205, 'with_log', 'true'
> But my experience is that after you have made that change, you need to
> recycle the SQL Server instance. I have never had luck not recycling the S
QL
> instance, and I don't know whether this is a bug or a feature by design.
> Linchi
> "ChrisR" wrote:
>

Need to understand writing to the Eventlog.

SQL2K
SP4
Howdy all. Im trying to have info written to the Eventlog when a deadlock
occurs, and growing frustrated trying to understand. My original
understanding was that I should RClick the server/ all tasks/ Manage
messages/ 1205 in the Error number/ Find/ Edit/ check Always write to
Eventlog/ OK/ OK.
I took those actions and purposely generated some deadlocks to no avail.
Then I found out what I really need to do is start SQL server by doing
"sqlservr -c -T1204" as instructed in
http://support.microsoft.com/default.aspx?scid=kb;en-us;169960.
Is this the only way? I really dont want to have to bounce all my production
boxes to accomplish this task.
TIA, ChrisR.You don't need to use -T1204 or run sqlsrvr -c -T1204 in order to log the
event in the app eventlog. The trace flag is to log a deadlock graph.
To log the deadlock in the app eventlog, it's sufficient to change the
logging behavior of error 1205 to 'Always write to eventlog'. This is the
same as executing the following:
EXEC sp_altermessage 1205, 'with_log', 'true'
But my experience is that after you have made that change, you need to
recycle the SQL Server instance. I have never had luck not recycling the SQL
instance, and I don't know whether this is a bug or a feature by design.
Linchi
"ChrisR" wrote:
> SQL2K
> SP4
> Howdy all. Im trying to have info written to the Eventlog when a deadlock
> occurs, and growing frustrated trying to understand. My original
> understanding was that I should RClick the server/ all tasks/ Manage
> messages/ 1205 in the Error number/ Find/ Edit/ check Always write to
> Eventlog/ OK/ OK.
> I took those actions and purposely generated some deadlocks to no avail.
> Then I found out what I really need to do is start SQL server by doing
> "sqlservr -c -T1204" as instructed in
> http://support.microsoft.com/default.aspx?scid=kb;en-us;169960.
> Is this the only way? I really dont want to have to bounce all my production
> boxes to accomplish this task.
> TIA, ChrisR.
>|||Perfect, thanks.
"Linchi Shea" wrote:
> You don't need to use -T1204 or run sqlsrvr -c -T1204 in order to log the
> event in the app eventlog. The trace flag is to log a deadlock graph.
> To log the deadlock in the app eventlog, it's sufficient to change the
> logging behavior of error 1205 to 'Always write to eventlog'. This is the
> same as executing the following:
> EXEC sp_altermessage 1205, 'with_log', 'true'
> But my experience is that after you have made that change, you need to
> recycle the SQL Server instance. I have never had luck not recycling the SQL
> instance, and I don't know whether this is a bug or a feature by design.
> Linchi
> "ChrisR" wrote:
> > SQL2K
> > SP4
> >
> > Howdy all. Im trying to have info written to the Eventlog when a deadlock
> > occurs, and growing frustrated trying to understand. My original
> > understanding was that I should RClick the server/ all tasks/ Manage
> > messages/ 1205 in the Error number/ Find/ Edit/ check Always write to
> > Eventlog/ OK/ OK.
> > I took those actions and purposely generated some deadlocks to no avail.
> > Then I found out what I really need to do is start SQL server by doing
> > "sqlservr -c -T1204" as instructed in
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;169960.
> >
> > Is this the only way? I really dont want to have to bounce all my production
> > boxes to accomplish this task.
> >
> > TIA, ChrisR.
> >

Need to store duplicate values to DB

Hi,

I have written a stored procedure to store values from a report i generated to the DB. Now there is a column PKID which is the primary key but also needs to be repeated at times. I tried to clear the memory that the same PKID has already been entered for which I wrote another SP.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate](
@.ReportDate smalldatetime,
@.SiteID int,
@.DataFeedID int,
@.FromCode varchar,
@.Sent int,
@.Delivered int,
@.TotalOpens REAL,
@.UniqueUserOpens REAL,
@.UniqueUserMessageClicks REAL,
@.Unsubscribes REAL,
@.Bounces REAL,
@.UniqueUserLinkClicks REAL,
@.TotalLinkClicks REAL,
@.SpamComplaints int,
@.Cost int
)
AS
SET NOCOUNT ON

DECLARE @.PKID INT
DECLARE @.TagID INT

SELECT @.TagID=ID FROM Tag WHERE SiteID=@.SiteID AND FromCode=@.FromCode

SELECT @.PKID=PKID FROM DimTag
WHERE TagID=@.TagID AND StartDate<=@.ReportDate AND @.ReportDate< ISNULL(EndDate,'12/31/2050')
IF @.PKID IS NULL BEGIN
SELECT TOP 1 @.PKID=PKID FROM DimTag WHERE TagID=@.TagID AND SiteID=@.SiteID

DECLARE @.LastReportDate smalldatetime, @.LastSent INT, @.LastDelivered INT, @.LastTotalOpens Real,
@.LastUniqueUserOpens Real, @.LastUniqueUserMessageClicks Real, @.LastUniqueUserLinkClicks Real,
@.LastTotalLinkClicks Real, @.LastUnsubscribes Real, @.LastBounces Real, @.LastSpamComplaints INT, @.LastCost INT

SELECT @.Sent=@.Sent-Sent,@.Delivered=@.Delivered-Delivered,@.TotalOpens=@.TotalOpens-TotalOpens,
@.UniqueUserOpens=@.UniqueUserOpens-UniqueUserOpens,@.UniqueUserMessageClicks=@.UniqueUserMessageClicks-UniqueUserMessageClicks,
@.UniqueUserLinkClicks=@.UniqueUserLinkClicks-UniqueUserLinkClicks,@.TotalLinkClicks=@.TotalLinkClicks-TotalLinkClicks,
@.Unsubscribes=@.Unsubscribes-Unsubscribes,@.Bounces=@.Bounces-Bounces,@.SpamComplaints=@.SpamComplaints-SpamComplaints,
@.Cost=@.Cost-Cost
FROM CrmPublisherSummary
WHERE @.LastReportDate < @.ReportDate
AND SiteID=@.SiteID
AND TagPKID=@.PKID

UPDATE CrmPublisherSummary SET
Sent=@.Sent,
Delivered=@.Delivered,
TotalOpens=@.TotalOpens,
UniqueUserOpens=@.UniqueUserOpens,
UniqueUserMessageClicks=@.UniqueUserMessageClicks,
UniqueUserLinkClicks=@.UniqueUserLinkClicks,
TotalLinkClicks=@.TotalLinkClicks,
Unsubscribes=@.Unsubscribes,
Bounces=@.Bounces,
SpamComplaints=@.SpamComplaints,
Cost=@.Cost,
TagID=@.TagID
WHERE ReportDate=@.ReportDate
AND SiteID=@.SiteID
AND TagPKID=@.PKID
END

ELSE

INSERT INTO CrmPublisherSummary(
ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens,
UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes,
Bounces, SpamComplaints, Cost, DataFeedID, TagID)

VALUES(
@.ReportDate, @.SiteID, @.PKID, @.Sent, @.Delivered, @.TotalOpens, @.UniqueUserOpens,
@.UniqueUserMessageClicks, @.UniqueUserLinkClicks, @.TotalLinkClicks, @.Unsubscribes,
@.Bounces, @.SpamComplaints, @.Cost, @.DataFeedID, @.TagID)

SET NOCOUNT OFF

this is the one to clear:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[spCRMPublisherSummaryClear](
@.SiteID INT,
@.DataFeedID INT,
@.ReportDate SMALLDATETIME) AS

DELETE LandingSiteSummary
WHERE SiteID=@.SiteID AND ReportDate=@.ReportDate

but it doesnt seem to be working.

Please suggest.

avidyarthi:

I have written a stored procedure to store values from a report i generated to the DB. Now there is a column PKID which is the primary key but also needs to be repeated at times.

you cannot repeat a value in a column that is set as your primary key. If you need to repeat values in that column, then you need to remove its designation as your primary key.

|||

hey,

thanks i worked my way around it.