Connnecting to an MS Access DB with an MS SQL 2000 Stored Proc

I would like to access a table in MS Access from a stored procedure on MS
SQL 2000. The MS Access DB is running on a diferent computer attached on the
same network. I think I need to create an ODBC connection from the MS SQL
2000 to the MS Access DB but not sure. Secondly how do I refer to the table
in the MS SQL stored proc?

Thanks for your input,

Joe SeamourYou need to add a linked server, and then you can query the Access database
directly. The BOL entry for sp_addlinkedserver shows an example, and the
topic "OLE DB Provider for Jet" has more details.

I've never set this up to use an Access DB on a different server myself, but
I think it should work if the MSSQL service account has permission to access
the share where the .mdb file is.

When the linked server is configured, you can query it like this.

SELECT * FROM MyLinkedServer...Table

Simon

"Joe Seamour" <jseamour@.attbi.com> wrote in message
news:1MYKa.33514$Bg.16441@.rwcrnsc54...
> I would like to access a table in MS Access from a stored procedure on MS
> SQL 2000. The MS Access DB is running on a diferent computer attached on
the
> same network. I think I need to create an ODBC connection from the MS SQL
> 2000 to the MS Access DB but not sure. Secondly how do I refer to the
table
> in the MS SQL stored proc?
> Thanks for your input,
> Joe Seamour

Connection Timeout

Is there any configuration option that controls how long
a user stays connected to mssql server? I have an
application that logs into mssql server and then uses
that one login for all work never logging out, after
about 7 days(fairly consistently) the connection to the
mssql gets killed and the application has to be
restarted. I was just curious if there is some kind of
parameter within mssql server that kills logins after x
number of days?
Thanks,
CarlHi,
There is no parameter in SQL server which will kill the user after x number
of days.
Rather you can write ur own code based on the login_time in
master..sysprocesses table and compare with getdate() for each process id
available in the list.
If any of the SPID is older then x days you can use
KILL SPID to kill the user. SPID also you can take it from
master..sysprocesses tables
Thanks
Hari
MCDBA
"z-man" <anonymous@.discussions.microsoft.com> wrote in message
news:817d01c4076d$e655aa20$a601280a@.phx.gbl...
> Is there any configuration option that controls how long
> a user stays connected to mssql server? I have an
> application that logs into mssql server and then uses
> that one login for all work never logging out, after
> about 7 days(fairly consistently) the connection to the
> mssql gets killed and the application has to be
> restarted. I was just curious if there is some kind of
> parameter within mssql server that kills logins after x
> number of days?
> Thanks,
> Carl
>|||There isn't anything like that built into SQL Server. You
could write your own procedure to do something like that but
nothing built into SQL Server.
If the application is on PC, server other than SQL Server,
you may want to look into network connectivity issues. You
may want to check the event logs where the application is
and see if you can find any clues in the logs.
-Sue
On Thu, 11 Mar 2004 05:36:47 -0800, "z-man"
<anonymous@.discussions.microsoft.com> wrote:

>Is there any configuration option that controls how long
>a user stays connected to mssql server? I have an
>application that logs into mssql server and then uses
>that one login for all work never logging out, after
>about 7 days(fairly consistently) the connection to the
>mssql gets killed and the application has to be
>restarted. I was just curious if there is some kind of
>parameter within mssql server that kills logins after x
>number of days?
>Thanks,
>Carl|||There is a 2 hour tcp session timeout and 1 hour for name pipe sessions.
But, this is controlled by the OS, not SQL.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||That's a good point...and looking at the keepalive settings
could be an option. After thinking about it more and that if
this happens about every 7 days, it may more likely be
related to a weekly process or something similar that is
hosing the connection. I think I'd go through the logs and
look for activity around the time the connection gets hosed.
-Sue
On Thu, 11 Mar 2004 21:02:41 GMT, kevmc@.online.microsoft.com
(Kevin McDonnell [MSFT]) wrote:

>There is a 2 hour tcp session timeout and 1 hour for name pipe sessions.
>But, this is controlled by the OS, not SQL.
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and confers no rights.
>

Connection String MSSQL2005

Dear all,
Do you people know how to connect MSSQL 2005 express edition with Vb6
ADODB? As I know MSSQL 2005 express connection string is specific the
database path. How do I write the connection string for ADODB?
Thanks in advance,
Goh
Hi Goh,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to connect to SQL
Server 2005 Express via VB6. If I have misunderstood your concern, please
feel free to point it out.
We have a MSDN article that describes how to connect SQL Server 2005
Express from VB6.
Using SQL Express from Visual Basic 6
http://msdn.microsoft.com/vbrun/vbfu...ingsqlexpress/
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Connection String MSSQL2005

Dear all,
Do you people know how to connect MSSQL 2005 express edition with Vb6
ADODB? As I know MSSQL 2005 express connection string is specific the
database path. How do I write the connection string for ADODB?
Thanks in advance,
GohHi Goh,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to connect to SQL
Server 2005 Express via VB6. If I have misunderstood your concern, please
feel free to point it out.
We have a MSDN article that describes how to connect SQL Server 2005
Express from VB6.
Using SQL Express from Visual Basic 6
http://msdn.microsoft.com/vbrun/vbf...singsqlexpress/
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

connection string

my web site is in a host. my mssql db is in other host. how I write the connection string. Please help.

You should bookmark this page:http://www.connectionstrings.com/

Now you will never have to ask about a connection string again

|||

<

configuration><connectionStrings>

<

addname="connectionName"connectionString="Data Source=ServerName;Initial Catalog=DBName;
User ID=userName;Password=p@.ssw0rd;Persist Security Info=True;"providerName="System.Data.SqlClient"/>

</

connectionStrings>

HTH

|||

JRICE:

<configuration>

<connectionStrings>

<addname="connectionName"connectionString="Data Source=ServerName;Initial Catalog=DBName;
User ID=userName;Password=p@.ssw0rd;Persist Security Info=True;"providerName="System.Data.SqlClient"/>

</connectionStrings>

My understanding of the original question is that the poster wants to connect to a remote instance of SQL Server.

|||

Mikesdotnetting:

JRICE:

<configuration>

<connectionStrings>

<addname="connectionName"connectionString="Data Source=ServerName;Initial Catalog=DBName;
User ID=userName;Password=p@.ssw0rd;Persist Security Info=True;"providerName="System.Data.SqlClient"/>

</connectionStrings>

My understanding of the original question is that the poster wants to connect to a remote instance of SQL Server.

in theData Source=ServerName .. place you database server ip with the port ... e.g if your server is in 192.168.2.100 ... and sql port is 9989 then use

Data Source=192.168.2.100,9989 ......

hope it helps you..

|||Thanks for your helping.|||Thank U too.

connection problem MSSQL SERVER & PWS same machine

Im getting an error when i try to connect to my MS SQL database

0x80040e21 ... multiple-step ole db operation generated errors ...check each ole db status value

this code works connecting to database via web hosting, only things i changed was to replace the server with "(local)" [also tried ip no joy there either] and add integrated security to the connection string

ms sql server and IIS, personal web server all on same laptop, i can access the same database with same details no problem to login it gives up on the subsequent login

code (generally)

Set objconn=Server.CreateObject("ADODB.Connection")
objconn.ConnectionString="SERVER=(local);Database=blah;uid=blah1;pwd=blah2;Integrated Security=true;"

objconn.open

can anybody help

ta, bIf you are using Driver={SQL Server} then instead use the native OLEDB connection style of

Provider=SQLOLEDB.1;UID=user;Password=password;Initial Catalog=DBName;Data Source=Servername

Then try your code again.

Check the following URL:

http://www.adopenstatic.com/faq/80040e21.asp|||thanks om but im afraid i still havent got it working :( had seen that link already but thank u

i have tried all concievable options at this stage ?? eg

http://www.tnsites.com/support/s_databaseconn.asp#SQL

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "Driver={SQL Server};Provider=SQLOLEDB.1;SERVER=136.xxx.xxx.xxx;DSN=localhostname;UID=uuu;PWD=ppp;Initial Catalog=bmdb; Integrated Security=true"

Error Type:
Provider (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/DotNetNuke/jobid/sel_services.asp, line 12

Any further suggestions?

I have no problem hooking up to same database in the web.config file --

<add key="connectionString" value="Server=136.xxx.xxx.xxx;Database=bmdb;uid=uuu;pwd=ppp;Integrated Security=true;" /
--

AND it worked previously on the web4life internet hosting server --

Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="DRIVER={SQL Server};SERVER=xxx.xxxxx.com;Database=bmdb;uid=uuu;pwd=ppp"
objConn.open

--

hosted on the local machine browsing locally
STILL ERROR (0x80040E21)

B|||...

asp or asp.net? and pws? <add key=.. shows it's asp.net, but you're using normal ado. i'm confused.|||ive just started asp.net, so the best I can say is that its an asp.net vb project running on a (pws) personal web server

its no wonder your confused seeing as im confused|||... pws? I'm still borked out on this. I didn't know ASP.NET can run on PWS.

First of all, if you're using an ado connection object like that, then you're better off using an ADO based connection string. Secondly, if you use .net, you should research ado.net under the System.Data namespace.

connection problem

Hello!
i'm having a problem with my web aplication. I'm using a net 2.o login in my apliction and mssql express 2K3 server and app_data i have the aspnetdb where are logins for page. When i'm usinng a web form in localhost works fine but from the internet i'm getting a error :

Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

This is just a piece of error, have anyone some idea what's wrong?

It sounds like your aspnet app is using windows authentication which in the context of the Internet would only work if you assign your web server's logon account access. See http://support.microsoft.com/default.aspx?scid=kb;en-us;247931&Product=sql2k for more information.

Derek

|||did you resolve your connection issue? and if so please mark an answer.|||No i didn't. I believe the problem is combination the mssql express and 2k3, because on my win xp server the aplication works normal without any problems. Have somebody any experiences about that to?|||OK! problem solves, i found a solution in another post, tnx anyway :)|||

Could you please next time crosslink the answer to this post, as if another person will search for the answer to your problem he will find it faster.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||OK!
I found a solution on this post : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=130715&SiteID=1&PageID=1

Connection problem

I have connection to MSSQL server.
When I read the data from the database, I close the connection (conn.Close() and conn.Dispose()).
After several page loads, it gets an error:
Database error. Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
I found the way to fix this by compiling the webapp in release, butagain, after several page loads problem still occurs. Then I have tocompile it in debug and over and over again.
This is somehow OK if Iam on local so I can do the compile, but if Iam on the net (my web page) I cant do this.
I realised that when I make a change to a connection string (connectionstring is in appSettings in web.config) I don't get the above error(just like I copiled the project).
Can I some how prevent this error to occur?
Can I use Pooling=false in connection string to solve the problem?
Hi there,
to me it seems that your application is not closing SQL connections properly... I will advie to go over your code again..
In some cases you may have a function which is returning values fromdatabase if you return a value even before you close the connection,your SQL connection will still remain active... this is just a smallpointer for you to get started.
Cheers!
|||Thanks for the reply.
I will take a look at the code, but Iam convinced that I have closed the connection every time I use it.
About Pooling=false in ConnectionString. I tried to load a page inlocalhost many time in a roud, but nothing happens, no error, butstill, I want to use the ConnectionString without Pooling=false.
As I said, I will take a look at the code again.
Thanks mate

|||I have put the Pooling=false in ConnectionString and error doesen't appear anymore until I see where the problem.