Advice on connection isssues

I have a SQL server which Authentication is set to Windows only, and all our
software that connects to the server uses 'trusted' connections
My question is if i connect to the server using a VPN , how can i make the
cnnection a trusted source so i can run software from a remote locationMap a drive on the client machine to a share on the SQL Server. This causes
the SQL Server to use those credentials when talking with the specific
client.
The share can contain nothing.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:42FF3626-5197-4D5D-95D5-25AAC2640747@.microsoft.com...
> I have a SQL server which Authentication is set to Windows only, and all
our
> software that connects to the server uses 'trusted' connections
> My question is if i connect to the server using a VPN , how can i make the
> cnnection a trusted source so i can run software from a remote location|||Mike,
does this work when connectiing to the SQL server though a VPN ?
"Mike Epprecht (SQL MVP)" wrote:

> Map a drive on the client machine to a share on the SQL Server. This cause
s
> the SQL Server to use those credentials when talking with the specific
> client.
> The share can contain nothing.
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:42FF3626-5197-4D5D-95D5-25AAC2640747@.microsoft.com...
> our
>
>|||When the client establishes a VPN do they authenticate to your DC?
If so, then they should be able to use Windows Authentication. If the user
is never challenged for his credentials,
then the connection is likely to fail with Login failed for user 'null'.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Advice

I have an application that connects to my database and
stores in my database ICONS. The application is using the
data type SQL_VARIANT. Was wondering, is sql_variant "ok"
to use... is there anything better... how about pointers?
Can anyone give me some advice on this...
ThanxsIf you by icons mean images (pictures), then I guess you should be using the
image datatype or varbinary. Only
use sql_variant if you really need *different* underlying datatypes for each
row. I've never had to use
sql_variant, btw.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:1696601c4172c$9b93c890$a101280a@.phx
.gbl...
> I have an application that connects to my database and
> stores in my database ICONS. The application is using the
> data type SQL_VARIANT. Was wondering, is sql_variant "ok"
> to use... is there anything better... how about pointers?
> Can anyone give me some advice on this...
> Thanxs

Advanced Join?

I have two tables X and Y which need to join together. Table X has an ID field which connects to an ID field in Table Y. My problem is the ID field in Table X can contain multiple ID's EX:

Table X
ID
-
2,1,4,
2,5,
1,
3,1,2,4,
ect...

whereas the ID field in Table Y contains one ID in each row EX:

Table Y
ID Color
- -
1 Green
2 Blue
3 Red
4 Yellow
5 Orange
ect...

Is there a way to join these two tables together? I need the output to be...

ID Color
-
2,1,4, Blue,Green,Yellow
2,5, Blue, Orange
1, Green
3,1,2,4, Red, Green, Blue, Yellow
ect...

Yes, but it will be messy and inefficient. And most likely, it will ignore any indexing.

You have violated a cardinal rule with ID columns. In order to efficiently link tables, they must be individual values -NOT simulated arrays of values. This is really is a bad design that will continue to haunt you throughout the project lifecycle.

SELECT

x.ID,

y.Color

FROM Table_X x

JOIN Table_Y x

ON ( ',' + x.ID ) LIKE ( ',' + y.ID + ',' )

There are some other methods as well, one of which would create a #Temp table, split apart the separate ID's in Table_X, and then JOIN that #Temp table with these two tables. Here is a link to that approach. And another.

You really 'shouldn't do that kind of 'trickery' with key values. The continual hassle with the data just isn't worth the 'buzz' gained from being so clever...

It's NOT an advanced JOIN, it is a very, very silly table design!

|||If you think this is "silly" you should see the rest of the database! The sad part about this whole thing is that our company purchased this application from a leading medical management software company for 1,$$$,$$$. Your example doesn't give me the output I was looking for but I will try the example from the link you provided on Monday when I get back to work. Thanks for your help.
|||

I see that I left out an important piece of the code. My apologies.

SELECT

x.ID,

y.Color

FROM Table_X x

JOIN Table_Y x

ON ( ',' + x.ID ) LIKE ( '%,' + y.ID + ',%' )

That 'should' work a bit better.

I understand. Sometimes I just don't get how seemingly intelligent folks spend so much money for such poorly designed software. But it happens every day. And then we have to support it.

|||

Your query gives me....

Table_X.ID Table_Y.Color

2,1,4, Blue
2,1,4, Green
2,1,4, Yellow
2,5, Blue
2,5, Orange
1, Green
3,1,2,4, Red
3,1,2,4, Green
3,1,2,4, Blue
3,1,2,4, Yellow

How do you get it to.?.?.?.

Table_X.ID Table_Y.Color
-
2,1,4, Blue,Green,Yellow
2,5, Blue, Orange
1, Green
3,1,2,4, Red, Green, Blue, Yellow

|||

What you are now requesting is a function for the display tool. The client application 'should' be creating a string array to put those values together. Folks often forget, SQL Server excels at storing and retrieving data, not at creating displays. But so many application are being created by folks that really aren't very good programmers at all, so things like this get stuffed into SQL Server.

You can use one of the methods listed in these resources.

Lists -Field Concatenation, One Field to Itself for string
SQL 2000 http://omnibuzz-sql.blogspot.com/2006/06/concatenate-values-in-column-in-sql.html
SQL 2005 http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx
http://www.projectdmx.com/tsql/rowconcatenate.aspx

|||

Thanks for all your help!!!

ADO/VB6 Glitch

I am connecting a data grid to a vb6 form using the following code (the connection is fine-it connects to a sql server db).

Set rs = New ADODB.Recordset
rs.Open TableName, conn, adOpenKeyset, adLockOptimistic
Set dgPartData.DataSource = rs
dgPartData.Caption = "Event Registration for " & sEventName

The table name is syntactically correct. What happens is if the first record in the database table is selected all is fine. However, if the user scrolls beyond the first record then my form gets all weird with phantom objects appearing through the text boxes, etc..

I have traced the issue to the 'Set dgPartData.DataSource = rs' line. Is there something I should do when connecting a grid to a db in code that I am not doing?

Thanks!What data types used in that table?
How about collation settings on SQL Server and on Windows on App./Web server?|||THe data types are mostly varchar, int and bigint. I am not sure what you mean by collation settings.

I have discovered that it seems to be connected to a bug in the refresh method of the adodc data object that is fired when using the adCmdTable setting and then trying to call the refresh method of the data object.

I have appeared to have worked around it but it is a little awkward. Let me know what you are thinking with the settings questions, please.

Thanks!!|||IF its something to do from VB side then I don't interfere and comment.
As far as SQL server is concerned if you do not find any information from SQL Error log then simply follow the workaround you have adopted.

In general COLLATION setup is used to specify code page and sort order for character data. Where it does deal with Windows collation & SQL Collations, more about this topic can be found from BOOKS ONLINE.

BTW< what is the service pack level on SQL Server & OS?|||I am not sure. How do I find that out? I am using SQL Server 2000 and Windows XP. I am getting the latest updates on the OS automatically. Where do I find the latest SQL Server SPs?

Thanks a ton! I'll look into collation!|||From SQL Server query analyzer run SELECT @.@.VERSION and let me know the result.

You can get information on SPs from MS SQL (http://www.microsoft.com/sql) website and download'em.|||Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)|||should download XP ServicePack 1 and SQL Server Service Pack 3a|||No its SQL 2000 with NO SP!

Refer to the above link and download SP3 and apply, its recommended to fix issues and get rid of hackers.|||aha... do read to SP3a readme file or fixlist before applying SP3a, otherwise SP3 is enough to apply.

ADO doesnt connect without network!

Hi!

I wrote an application which connects to a SQL Server / MSDE 2000 DB through ADO components. Now the application doesn't start if I don't have an active network connection; as far as I try to connect, the error I get is:

[DBNETLIB][ConnectionOpen (Connect()).].......

Everything is ok if I simply plug the network cable and let the PC connect to the TCP/IP network.

Hope someone can help me!
Thanks

Umberto...Of course the DB is located on the same PC than the application!

Umberto|||If I write

Data Source=PC_NAME

instead of

Data Source=127.0.0.1

it works fine! Now the question is: WHY??

Umberto|||Is it MSDE or SQL Server Std ?

If MSDE, have you installed Netlib ? It is lhe parameter
DISABLENETWORKPROTOCOL=0
in setup.ini or command line parameters|||Finally I solved the problem by writing:

....;Data Source=(local);....

in the connection string!

Thanks
Umberto

ADO Connection terminates automatically after one hour

Hi,
I am facing some problem with ADO connection to SQL Server.
My application connects to SQL Server through ADO, excute some query and
then for more than an hour application does not use this connection.
It does something else.
But when again it tries to use that connection, that connection is no longer
exist and SQL Server gives following error:
"General network error. Check your network documentation."
Through profiler I have find out that SQL Server terminates that connection
exactly after one hour.
Can anyone explain this behavior of ADO/SQL Server?
What shall I do to avoid this?
Thanks in advance.
PushkarHave your application check the status of the connection, and open it if it
is not already open.
Soon as you are done with the connection close it until you need it again.
Keeping the same connection open for an hour when it is not in use is going
to waste resources.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:usNGiGviGHA.3572@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am facing some problem with ADO connection to SQL Server.
> My application connects to SQL Server through ADO, excute some query and
> then for more than an hour application does not use this connection.
> It does something else.
> But when again it tries to use that connection, that connection is no
longer
> exist and SQL Server gives following error:
> "General network error. Check your network documentation."
> Through profiler I have find out that SQL Server terminates that
connection
> exactly after one hour.
> Can anyone explain this behavior of ADO/SQL Server?
> What shall I do to avoid this?
> Thanks in advance.
> Pushkar
>|||Hi
Have a look at your network. You might have routers that close ports when
inactive for a long time.
SQL Server and ADO do not automatically close connections.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:usNGiGviGHA.3572@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am facing some problem with ADO connection to SQL Server.
> My application connects to SQL Server through ADO, excute some query and
> then for more than an hour application does not use this connection.
> It does something else.
> But when again it tries to use that connection, that connection is no
> longer exist and SQL Server gives following error:
> "General network error. Check your network documentation."
> Through profiler I have find out that SQL Server terminates that
> connection exactly after one hour.
> Can anyone explain this behavior of ADO/SQL Server?
> What shall I do to avoid this?
> Thanks in advance.
> Pushkar
>

ado connection

i have developed an application that connects to SQL Server 2000.

on my machine as well as 90 % of our company's machines it works fine. but the rest (xp machines) it doesn't want to work. it keeps telling me my provider is not installed correctly. i have tried various installation of mdac but they don't install.

i see on msdn they say it could be a localization error but the workaround they give doesn't work.

what else can i try

Hi,

whats the excat error message you are gettin g?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

hi

it says "Provider cannot be found. it may not be properly installed"

|||

i found the solution

just repair XP

it was the only thing i found that worked.

ADO client disconnects after running a long query

I am having a problem executing long running queries from an ASP application
which connects to SQL Server 2000. Basically, I have batches of queries that
are run using ADO in a loop written in VBScript. This works pretty well
until the execution time of a single query starts to exceed some threshold,
which I am trying to narrow down. I can typically run 2 - 10 queries in a
loop, with the run time being anywhere from under a minute to an hour or
more. Now that this application is being subjected to run against some large
databases (25 - 40G), I'm having problems getting the application to
continue beyond the first query if it takes a while to run.

I used SQL Profiler to try to diagnose what was going on. I can see the
query executes to completion, but immediately after completing I can see an
"Audit Logout" message, which apparently means that the client has
disconnected. The query durations vary from 45 or 50 minutes to up to over
90 minutes. I have the ADO connection and query timeouts set to very large
values, e.g. 1000 minutes, so I can't think its that. My guess is that there
is some IIS setting or timeout that I am running up against and the
connection to SQL Server is just dropped for some reason.

The configuration is

NT 4.0 SP6
SQL Server 2000 SP3
IIS 4.0
Internet Explorer 5.5

I'm only running into this problem on the very largest databases we run
against. The vast majority continue to function properly, but this is going
to happen more often as time goes on the databases continue to grow in size.

Any advice is appreciated,

-GaryGary wrote:
> I am having a problem executing long running queries from an ASP
> application which connects to SQL Server 2000. Basically, I have
> batches of queries that are run using ADO in a loop written in
> VBScript. This works pretty well until the execution time of a single
> query starts to exceed some threshold, which I am trying to narrow
> down. I can typically run 2 - 10 queries in a loop, with the run time
> being anywhere from under a minute to an hour or more. Now that this
> application is being subjected to run against some large databases
> (25 - 40G), I'm having problems getting the application to continue
> beyond the first query if it takes a while to run.
> I used SQL Profiler to try to diagnose what was going on. I can see
> the query executes to completion, but immediately after completing I
> can see an "Audit Logout" message, which apparently means that the
> client has disconnected. The query durations vary from 45 or 50
> minutes to up to over 90 minutes.

!??!?
Don't use ASP for this. Why tie up a thread on your web server for that
long??

I'm willing to be that this entire task could be done within a scheduled
stored procedure.

> I have the ADO connection and query
> timeouts set to very large values, e.g. 1000 minutes, so I can't
> think its that. My guess is that there is some IIS setting or timeout

Probably ScriptTimeout

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:udDZnd$fEHA.1972@.TK2MSFTNGP09.phx.gbl...
> Don't use ASP for this. Why tie up a thread on your web server for that
> long??

I have no choice at the moment. I realize it's not an optimal solution. Also
there are rarely more than a couple requests at a time going on.

> I'm willing to be that this entire task could be done within a scheduled
> stored procedure.

Some of it is scheduled, but it's designed to work on-demand as well.

> > I have the ADO connection and query
> > timeouts set to very large values, e.g. 1000 minutes, so I can't
> > think its that. My guess is that there is some IIS setting or timeout
> Probably ScriptTimeout

This is already set to a really large value. (way more than an hour) But
thanks for the suggestions.

-Gary|||"Gary" wrote:
> My guess is that there is some IIS setting or
> timeout that I am running up against and the
> connection to SQL Server is just dropped for
> some reason.

Gary,

If your ASP times out, you definitely want the connection to stop since
there's no reason for it run anymore :)

In any event, there is an IIS timeout setting, but you can change it in your
script:

<%
Server.ScriptTimeout = Number_Of_Seconds
%
Craig|||Gary wrote:
> "Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
> news:udDZnd$fEHA.1972@.TK2MSFTNGP09.phx.gbl...
>> Don't use ASP for this. Why tie up a thread on your web server for
>> that long??
>>
> I have no choice at the moment. I realize it's not an optimal
> solution. Also there are rarely more than a couple requests at a time
> going on.
>> I'm willing to be that this entire task could be done within a
>> scheduled stored procedure.
> Some of it is scheduled, but it's designed to work on-demand as well.

You can kick off a scheduled job that runs a stored procedure using
sp_start_job. To pass parameter values to the procedure, you can insert them
into a control table where they can be read by the stored procedure.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.