DSN Connection to Remote SQL Server

Hi,

I've worked with SQL Server plenty but never in this specific manner. Hoping someone can shed some light.

I have a webform on web server A that needs to talk to a SQL server database that web server B accesses via its web application. I do not have
any privelidged access to server B or the sql server it uses (no FTP to webserver, no remote access via Enterprise Manager to the SQL server). The Admin of Server B told me that I can use a DSN connection to talk to the SQL server database that the web app on server B uses. He emailed me the names of the SQL database tables AND a copy of the web.config from server B which shows the DSN implemented like this:

<appSettings>
<add key="dsn" value="data source=sql3.domainname.net;database=dbname;User Id=dbuserid;password=dbpw;" />
<add key="default_language" value="en" />
<add key="error_email_to" value="someone@.domainname.com" />
<add key="baseHref" value="http://www.mysite.com/" />
<add key="website_name" value="$$$$$$$$$$$$$$$$$" />
</appSettings>

I was planning to run my webforms on Server A where I do have FTP, Control Panel, etc.
So, I wrote this code into my VB.NET codebehind file.

Dim oODBCConnectionAs OdbcConnection
Dim sConnStringAsString = _
"Driver={SQL Server};" & _
"Server=sql3.domainname.net;" & _
"Database=dbname;" & _
"Uid=dbuserid;" & _
"Pwd=dbpw"

oODBCConnection =New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()
... commands to access data and load something into a control on a webform
oODBCConnection.Close()

When I attempt to run the webform in my browser from Server A. I'm getting this error:
ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
The error is EXACTLY the same whether I place the appSettings section in server A's web.config or leave them out.

I have obvious questions about the error like...
Might sql3.domainname.net simply be refusing communication requests that don't come from the Server B domain?
Might the sql3.domainname.net be setup so that I need to use another protocol (not tcp/ip)?
Might my code need to be changed to support this type of connection (i.e. I didn't code it right)?

I spent some time readinghttp://support.microsoft.com/kb/328306/en-us but I'm at a loss as to what the best course is to take for debugging this.
Thanks in advance for any insight you can offer.

Stewart

Hi,

I think you should get the remote server's ip address and user account to creat a local DSN and call the DSN in your application. Another easy solution is to use SqlConnection rather then ODBCConnection and modify your connection string and make it look like "DataSource=IP address;Initial Catelog=DBName;User Id=;Password="

Thanks.

DROPCLEANBUFFERS/FREEPROCCACHE

I've been doing some stored proc tuning and I want to ensure that after the
changes I make to the query the execution times are accurate. So basically,
if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
users, am I clearing ALL buffers/proccache that SQL is using, or just the
ones tied to my connection?
Everything for the server.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> I've been doing some stored proc tuning and I want to ensure that after
the
> changes I make to the query the execution times are accurate. So
basically,
> if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> users, am I clearing ALL buffers/proccache that SQL is using, or just the
> ones tied to my connection?
|||There is no need to issue FREEPROCCACHE as each time you edit the sp it will
invalidate the plan already in cache anyway. DropCleanBuffers is data
related and has nothing to do with the sp execution plan chosen.
Andrew J. Kelly SQL MVP
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> I've been doing some stored proc tuning and I want to ensure that after
> the
> changes I make to the query the execution times are accurate. So
> basically,
> if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> users, am I clearing ALL buffers/proccache that SQL is using, or just the
> ones tied to my connection?
|||well, i thought so about DROPCLEANBUFFERS but the proc ran quicker (without
change) every time I ran it after the first time. So SQL must have been
acessing the data from the buffer? Thanks for your quick response.
"Andrew J. Kelly" wrote:

> There is no need to issue FREEPROCCACHE as each time you edit the sp it will
> invalidate the plan already in cache anyway. DropCleanBuffers is data
> related and has nothing to do with the sp execution plan chosen.
> --
> Andrew J. Kelly SQL MVP
>
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
>
>
|||"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:43E5BEE0-D6D4-4D78-A6E0-6F128AA7B961@.microsoft.com...
> well, i thought so about DROPCLEANBUFFERS but the proc ran quicker
(without
> change) every time I ran it after the first time. So SQL must have been
> acessing the data from the buffer? Thanks for your quick response.
Andrew may not agree with the method, but I personally always do a
CHECKPOINT then run DROPCLEANBUFFERS and FREEPROCCACHE when I'm testing
stored procedure changes for single-run performance -- I want to work with a
totally clean slate.
Of course, a single-run doesn't tell you much; you might instead want to
try running it 10 or 20 times and taking the average. I've found this gives
much more interesting results. Sometimes a single run can be skewed by
something else on the server running (or not running, thereby making you
think you've done better than you really have). If you run it several times
and take an average, that's less likely to affect your results.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
|||It is not that I don't agree with doing that but it is important to
understand what happens when you do that and how it may or may not affect
your results. But it's rare any more that I do a single test like that
since it rarely mimics real life conditions on busy systems. I can usually
tell more from the query plan than anything else. But That does not mean
this can not be useful information.
Andrew J. Kelly SQL MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uC9JouXMFHA.2252@.TK2MSFTNGP15.phx.gbl...
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:43E5BEE0-D6D4-4D78-A6E0-6F128AA7B961@.microsoft.com...
> (without
> Andrew may not agree with the method, but I personally always do a
> CHECKPOINT then run DROPCLEANBUFFERS and FREEPROCCACHE when I'm testing
> stored procedure changes for single-run performance -- I want to work with
> a
> totally clean slate.
> Of course, a single-run doesn't tell you much; you might instead want
> to
> try running it 10 or 20 times and taking the average. I've found this
> gives
> much more interesting results. Sometimes a single run can be skewed by
> something else on the server running (or not running, thereby making you
> think you've done better than you really have). If you run it several
> times
> and take an average, that's less likely to affect your results.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>

drop constraint hanging.

I'm trying to fix a corrupt database, complains about indexes. I've been
waiting for drop constraint to come back, its been running for 1.5 hrs and
the drive activity is at 100%. Is this normal?
Everything is possible once you have a corrupt database. Check your system
event log for errors as well. It is possible for disk I/O to slow down
significantly if there are hardware problems.
Going forward, your course of action should be to identify what caused
corruption and restore your database from the last good backup.
Adrian
"michael" <michael@.discussions.microsoft.com> wrote in message
news:D044278A-7F75-43E9-A1F4-C5D0CE807397@.microsoft.com...
> I'm trying to fix a corrupt database, complains about indexes. I've been
> waiting for drop constraint to come back, its been running for 1.5 hrs and
> the drive activity is at 100%. Is this normal?

Drop All DB Indexes

Hi, All
I need to drop all indexes in a DB and then recreate them with a script. The
recreate is not a problem, but I've struggling to find an example script of
how to drop all user indexes in a DB. I will need to know if the index is
being used to enforce a primary key and if so I'll have to remove any
contraints before attempting the drop.
The other thing I've notice when selecting all tbl's from sysobject where
type='U' it's showing a table called dtProperties which is not a user table.
This table shows as a system table in the enterprise manager. Why is it
specified as 'U' in the sysobjects?
I'd really appreciate any help I can get
Thanks
Antony
If you automate the drop, how will you automate the re-create? The re-create is the difficult part
as you need to know the columns, whether it is unique, whether it is clustered, PK, UNIQUE etc.
Anyhow, some tips on automating the DROP:

> I need to drop all indexes in a DB and then recreate them with a script. The
> recreate is not a problem, but I've struggling to find an example script of
> how to drop all user indexes in a DB.
Use a cursor to loop sysindexes and inside the cursor use dynamic SQL to drop the indexes. Use
INDEXPROPERTY to filter out statistics and hypothetical indexes. Also, OBJECTPROPERTY to filter out
non-system tables.

> I will need to know if the index is
> being used to enforce a primary key and if so I'll have to remove any
> contraints before attempting the drop.
Use the status column for this (see Books Online, sysindexes).

> The other thing I've notice when selecting all tbl's from sysobject where
> type='U' it's showing a table called dtProperties which is not a user table.
> This table shows as a system table in the enterprise manager. Why is it
> specified as 'U' in the sysobjects?
Historical screw-up. Use OBJECTPROPERTY and the IsMsShipped option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Antony" <Antony@.discussions.microsoft.com> wrote in message
news:C5FC191E-E418-44CA-9DA4-1C4B13C7C4CD@.microsoft.com...
> Hi, All
> I need to drop all indexes in a DB and then recreate them with a script. The
> recreate is not a problem, but I've struggling to find an example script of
> how to drop all user indexes in a DB. I will need to know if the index is
> being used to enforce a primary key and if so I'll have to remove any
> contraints before attempting the drop.
> The other thing I've notice when selecting all tbl's from sysobject where
> type='U' it's showing a table called dtProperties which is not a user table.
> This table shows as a system table in the enterprise manager. Why is it
> specified as 'U' in the sysobjects?
> I'd really appreciate any help I can get
> Thanks
> Antony