Connect to ORACLE

Hello,
I created an ODBC connection to an ORACLE database that works in MS Access.
Now I would like to use the connection to link the tables in SS2K. However,
I can't figure out how to use it in SS2K. Should I be using
"sp_addlinkedserver?"
Any help would be appreciated.
--
Thanks in advance,
StevenYes. sp_addlinkedserver and sp_addlinkedsrvlogin. SQL Server Books Online
has examples specific to setting up Oracle linked servers.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Steven K" <sckeels@.lucent.com> wrote in message
news:OM5MKoOmDHA.2732@.TK2MSFTNGP11.phx.gbl...
Hello,
I created an ODBC connection to an ORACLE database that works in MS Access.
Now I would like to use the connection to link the tables in SS2K. However,
I can't figure out how to use it in SS2K. Should I be using
"sp_addlinkedserver?"
Any help would be appreciated.
Thanks in advance,
Steven|||Just want to add that you also need to install the Oracle client software on
the SQL Server box. The linked server to Oracle works fine.
You do need to watch out for performance and avoid bringing unnecessary data
to SQL instance for processing. Try to get as much processing done as
possible on Oracle and bring back only the resultset for further processing
on the SQL instance.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:eYa6FqOmDHA.2416@.TK2MSFTNGP10.phx.gbl...
> Yes. sp_addlinkedserver and sp_addlinkedsrvlogin. SQL Server Books Online
> has examples specific to setting up Oracle linked servers.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> What hardware is your SQL Server running on?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Steven K" <sckeels@.lucent.com> wrote in message
> news:OM5MKoOmDHA.2732@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I created an ODBC connection to an ORACLE database that works in MS
Access.
> Now I would like to use the connection to link the tables in SS2K.
However,
> I can't figure out how to use it in SS2K. Should I be using
> "sp_addlinkedserver?"
> Any help would be appreciated.
>
> --
> Thanks in advance,
> Steven
>
>sqlsql

Connect to ODBC DB from SQL Server SPROC

Hello,
I have taken on a project where I'm wondering if I'm in a little over my
head. Using SQL Mail I need to send out e-mails every hour for orders that
have shipped. There are two databases. One is SQL Server and the other is
only accessible via ODBC (I'm not even sure what type of database it is yet,
but it's not SQL Server). The SQL Server database contains the basic order
information and the other database contains the order status.
I have the queries I need, but how do I connect via ODBC to the other
database from SQL Server? I know (very) little about SQL Mail. Do I set it up
as a job under SQL Server Agent jobs so it will run every hour? Can someone
give me a push in the right direction?
Thank you very much for any help, I really appreciate it.
Thanks,
Nick
Hello,
I think you can perform the following steps:
1. Run the query in SQL query analyzer(QA) to make sure it works fine. You
can create a stored procedure.
2. Configure the SQL mail.
3. Run xp_sendmail in SQL QA to send the query results via email.
4. Create a job in SQL agent so it will run every hour.
Generally we can create a linked server to access other data source.
However, it denpends on what database you use. If it is a third party
database, I suggest that you contact the third-party support to consult
with them abouht the issue.
The following information is for your reference:
SQL Server E-mail
http://msdn.microsoft.com/library/de...us/dnovba01/ht
ml/SQLServerE-mail.asp
INF: How to Configure SQL Mail
http://support.microsoft.com/kb/263556
Support WebCast: Troubleshooting Microsoft SQL Mail
http://support.microsoft.com/kb/324689
xp_sendmail
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
xp_aa-sz_6hbg.asp
sp_addlinkedserver
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
sp_adda_8gqa.asp
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
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.

Connect to ODBC DB from SQL Server SPROC

Hello,
I have taken on a project where I'm wondering if I'm in a little over my
head. Using SQL Mail I need to send out e-mails every hour for orders that
have shipped. There are two databases. One is SQL Server and the other is
only accessible via ODBC (I'm not even sure what type of database it is yet,
but it's not SQL Server). The SQL Server database contains the basic order
information and the other database contains the order status.
I have the queries I need, but how do I connect via ODBC to the other
database from SQL Server? I know (very) little about SQL Mail. Do I set it u
p
as a job under SQL Server Agent jobs so it will run every hour? Can someone
give me a push in the right direction?
Thank you very much for any help, I really appreciate it.
Thanks,
NickHello,
I think you can perform the following steps:
1. Run the query in SQL query analyzer(QA) to make sure it works fine. You
can create a stored procedure.
2. Configure the SQL mail.
3. Run xp_sendmail in SQL QA to send the query results via email.
4. Create a job in SQL agent so it will run every hour.
Generally we can create a linked server to access other data source.
However, it denpends on what database you use. If it is a third party
database, I suggest that you contact the third-party support to consult
with them abouht the issue.
The following information is for your reference:
SQL Server E-mail
http://msdn.microsoft.com/library/d...-us/dnovba01/ht
ml/SQLServerE-mail.asp
INF: How to Configure SQL Mail
http://support.microsoft.com/kb/263556
Support WebCast: Troubleshooting Microsoft SQL Mail
http://support.microsoft.com/kb/324689
xp_sendmail
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
xp_aa-sz_6hbg.asp
sp_addlinkedserver
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
sp_adda_8gqa.asp
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
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.

connect to MSSQL2005 via ODBC

Installed SQL2005, connect via (the new) SQL Nativ Client ODBC Vers. 2005.90.1399.00 works, just as (the old) SQL Server ODBC-Driver 2000.85.1117.00. Works fine.
But from an other machine -there is no client installed - using driver 2000.85.1117.00 I get the message "Server dosn't exist or access denied"
How I can connect ? It is due to the fact of .NET FRAMEWORK 2 ?
Thanks!Sounds like the 'other' machine is your first network client - check your enabled protocols with SQL Server Configuration Manager.

Also, check your connection details include the instance name if necessary.

Also, what are the OS levels and authentication method?|||Hi, Mulhall. Indeed, it's the first network client W2K, connections to other sql2000 servers are o.k., to SQL2005 tried using TCP/IP-Protocol, tried Named Pipes and all others. Using the standard instance, no named, using SQL-authentication such as win-authentication, tried connection by server-name and by IP-Adress. OS-Ping works normaly.|||Have you verified using the Surface Area Configuration tool that remote connections are enabled?

Thanks
Laurentiu|||Thanks Laurentiu, upon configuration Surface Area Conf.Tool the connection works. I did not know this feature

Connect to DB2 using SQL-Server

Hello there
I have SQL 7 and ODBC-Client access to connect to DB2 (AS400). I can connect
to the DB2 database using the ODBC and VB without problems but now I want t
o use the sp_addlinkedserver to link the DB2 to my SQL database using the OD
BC. Does anybody know what
parameters I have to use and what are the values to link the DB2 to my datab
ase?.
I will appreciate any help because I've navigated for the internet looking f
or this information without luck.
regards
osotoIf you install Client Access on the server, it will install
the drivers and providers for the AS400 on the SQL Server
box so that you can use linked servers. For data source,
use the IP address of the AS400. For provider string, you
need to include the library you are using, connect timeout
setting and code page. There is some documentation for the
settings in the Client Access help files.
You can also use the OLE DB provider and follow the similar
example on books online for "OLE DB Provider for DB2"
You'd want to change the provider to IBMDA400
and set @.provstr somewhat like:
InitCat=YourLibrary;CCSID=37;PCCodePage=
1252;
Data Source=xxx.xxx.xxx.xxx
Settings will depend on how your AS400 is configured. Again,
the Client Access help files have information on the
necessary connection string settings.
-Sue
On Fri, 5 Mar 2004 08:26:07 -0800, "osoto"
<anonymous@.discussions.microsoft.com> wrote:

>Hello there
>I have SQL 7 and ODBC-Client access to connect to DB2 (AS400). I can connect to the
DB2 database using the ODBC and VB without problems but now I want to use the sp_ad
dlinkedserver to link the DB2 to my SQL database using the ODBC. Does anybody know w
hat
parameters I have to use and what are the values to link the DB2 to my database?.kred">
>I will appreciate any help because I've navigated for the internet looking
for this information without luck.
>regards
>osoto
>sqlsql

Connect to DB2 in a stored procedure

Hi
I read data from a db2 database on an IBM mainframe through ODBC and
transfer it to a dabase on our SQL Server. Everything is done using a DTS and
it works fine. For serveral resons I would like to implement a stored
procedure doing the same task. Does anybody know how to solve this? Specially
the connection to the IBM mainframe is a bog question mark to me.
Any help appreciated.
Rene
Me again!
I found some information about linked server which might help. But how do I
set up a linked server to DB2? Can someone give me an example?
Regards
Rene
"René" wrote:

> Hi
> I read data from a db2 database on an IBM mainframe through ODBC and
> transfer it to a dabase on our SQL Server. Everything is done using a DTS and
> it works fine. For serveral resons I would like to implement a stored
> procedure doing the same task. Does anybody know how to solve this? Specially
> the connection to the IBM mainframe is a bog question mark to me.
> Any help appreciated.
> Rene
|||You can find an example in Books Online. From the index,
search on Linked Servers. Select the Access DB2 Database
subtopic.
-Sue
On Mon, 9 Aug 2004 04:33:05 -0700, "Ren"
<Ren@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Me again!
>I found some information about linked server which might help. But how do I
>set up a linked server to DB2? Can someone give me an example?
>Regards
>Rene
>"Ren" wrote:

Connect to DB2 in a stored procedure

Hi
I read data from a DB2 database on an IBM mainframe through ODBC and
transfer it to a dabase on our SQL Server. Everything is done using a DTS an
d
it works fine. For serveral resons I would like to implement a stored
procedure doing the same task. Does anybody know how to solve this? Speciall
y
the connection to the IBM mainframe is a bog question mark to me.
Any help appreciated.
ReneMe again!
I found some information about linked server which might help. But how do I
set up a linked server to DB2? Can someone give me an example?
Regards
Rene
"René" wrote:

> Hi
> I read data from a DB2 database on an IBM mainframe through ODBC and
> transfer it to a dabase on our SQL Server. Everything is done using a DTS
and
> it works fine. For serveral resons I would like to implement a stored
> procedure doing the same task. Does anybody know how to solve this? Specia
lly
> the connection to the IBM mainframe is a bog question mark to me.
> Any help appreciated.
> Rene|||You can find an example in Books Online. From the index,
search on Linked Servers. Select the Access DB2 Database
subtopic.
-Sue
On Mon, 9 Aug 2004 04:33:05 -0700, "Ren"
<Ren@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Me again!
>I found some information about linked server which might help. But how do I
>set up a linked server to DB2? Can someone give me an example?
>Regards
>Rene
>"Ren" wrote:
>

Connect SQL server 2005 with ODBC drive “SQL Native Client"

Hi,

When I'm using SQL server 2005, and ODBC drive “SQL Native Client” I get an error when executing the below given code. Error Message: “Transaction cannot start because more than one ODBC connection is in use.”

But this works well with SQL Server 2000 with ODBC drive "SQL Native Client" and in SQL server 2005 also when I used ODBC drive “SQL Server”.

I used aTrgConn.Execute(“Begin Transaction”) and it's working properly. But unfortunately I cannot use this method, because I want to run the same code with Oracle ODBC as well.

Is this a known issue in SQL Native client drive with SQL server 2005? Do you know a solution for this?

Please Help..............

Thanks in advance.

Code:

Dim aTrgConn As New ADODB.connection
Dim aCmdIns As New ADODB.Command
Dim aCmdUpd As New ADODB.Command

aTrgConn.Open sConnStr

Set aCmdIns.ActiveConnection = aTrgConn
Set aCmdUpd.ActiveConnection = aTrgConn

aTrgConn.BeginTrans

Why are you using two separate commands here? Couldn't you just use one command and reset the values to execute?|||

Hi John,

You are correct. but I'm afraid, it's very difficult to change the design at this stage. and it worked perfectly with SQL Server 2000.

Thank you very much for your response.

|||

Hi Again,

I think the problem comes when using the provider as MSDASQL.1 , with SQL server 2005 Native Client.

When I add "Provider=SQLNCLI;Integrated Security=SSPI;DataTypeCompatibility=80;MARS Connection=True;" to my connection string, it works correctly.

But again, I need the same coding to access oracle too. so SQLNCLI won't work with that.

It's really appriciated, If anyone could find a solution to this.

Thanks

|||

Hi,

I have the same problem with my application.

I began to use SQL Express 2005 with Native Client ODBC rather than MSDE and found the same error.

My application must works with several databases.

If anyone find a solution for this problem tell me too.

Thanks advanced,

Wendel

|||

Do you see the same problem with SQLNCLI OLEDB provider and not using MARS?

Thanks

Waseem

Connect SQL server 2005 with ODBC drive “SQL Native Client"

Hi,

When I'm using SQL server 2005, and ODBC drive “SQL Native Client” I get an error when executing the below given code. Error Message: “Transaction cannot start because more than one ODBC connection is in use.”

But this works well with SQL Server 2000 with ODBC drive "SQL Native Client" and in SQL server 2005 also when I used ODBC drive “SQL Server”.

I used aTrgConn.Execute(“Begin Transaction”) and it's working properly. But unfortunately I cannot use this method, because I want to run the same code with Oracle ODBC as well.

Is this a known issue in SQL Native client drive with SQL server 2005? Do you know a solution for this?

Please Help..............

Thanks in advance.

Code:

Dim aTrgConn As New ADODB.connection
Dim aCmdIns As New ADODB.Command
Dim aCmdUpd As New ADODB.Command

aTrgConn.Open sConnStr

Set aCmdIns.ActiveConnection = aTrgConn
Set aCmdUpd.ActiveConnection = aTrgConn

aTrgConn.BeginTrans

Why are you using two separate commands here? Couldn't you just use one command and reset the values to execute?|||

Hi John,

You are correct. but I'm afraid, it's very difficult to change the design at this stage. and it worked perfectly with SQL Server 2000.

Thank you very much for your response.

|||

Hi Again,

I think the problem comes when using the provider as MSDASQL.1 , with SQL server 2005 Native Client.

When I add "Provider=SQLNCLI;Integrated Security=SSPI;DataTypeCompatibility=80;MARS Connection=True;" to my connection string, it works correctly.

But again, I need the same coding to access oracle too. so SQLNCLI won't work with that.

It's really appriciated, If anyone could find a solution to this.

Thanks

|||

Hi,

I have the same problem with my application.

I began to use SQL Express 2005 with Native Client ODBC rather than MSDE and found the same error.

My application must works with several databases.

If anyone find a solution for this problem tell me too.

Thanks advanced,

Wendel

|||

Do you see the same problem with SQLNCLI OLEDB provider and not using MARS?

Thanks

Waseem

Connect SQL server 2005 with ODBC drive “SQL Native Client"

Hi,

When I'm using SQL server 2005, and ODBC drive “SQL Native Client” I get an error when executing the below given code. Error Message: “Transaction cannot start because more than one ODBC connection is in use.”

But this works well with SQL Server 2000 with ODBC drive "SQL Native Client" and in SQL server 2005 also when I used ODBC drive “SQL Server”.

I used aTrgConn.Execute(“Begin Transaction”) and it's working properly. But unfortunately I cannot use this method, because I want to run the same code with Oracle ODBC as well.

Is this a known issue in SQL Native client drive with SQL server 2005? Do you know a solution for this?

Please Help..............

Thanks in advance.

Code:

Dim aTrgConn As New ADODB.connection
Dim aCmdIns As New ADODB.Command
Dim aCmdUpd As New ADODB.Command

aTrgConn.Open sConnStr

Set aCmdIns.ActiveConnection = aTrgConn
Set aCmdUpd.ActiveConnection = aTrgConn

aTrgConn.BeginTrans

Why are you using two separate commands here? Couldn't you just use one command and reset the values to execute?|||

Hi John,

You are correct. but I'm afraid, it's very difficult to change the design at this stage. and it worked perfectly with SQL Server 2000.

Thank you very much for your response.

|||

Hi Again,

I think the problem comes when using the provider as MSDASQL.1 , with SQL server 2005 Native Client.

When I add "Provider=SQLNCLI;Integrated Security=SSPI;DataTypeCompatibility=80;MARS Connection=True;" to my connection string, it works correctly.

But again, I need the same coding to access oracle too. so SQLNCLI won't work with that.

It's really appriciated, If anyone could find a solution to this.

Thanks

|||

Hi,

I have the same problem with my application.

I began to use SQL Express 2005 with Native Client ODBC rather than MSDE and found the same error.

My application must works with several databases.

If anyone find a solution for this problem tell me too.

Thanks advanced,

Wendel

|||

Do you see the same problem with SQLNCLI OLEDB provider and not using MARS?

Thanks

Waseem

Connect From ASP to SQL Server Through ODBC

Hi there,
I'm a new comer to this APS language and hope sombody out there can help me with this
I'm trying to find codes for me to connect from ASP to Sql Server using ODBC.
Can anyone help me
ThanxOriginally posted by michaelfg81
Hi there,

I'm a new comer to this APS language and hope sombody out there can help me with this
I'm trying to find codes for me to connect from ASP to Sql Server using ODBC.
Can anyone help me
Thanx

Set db = Server.CreateObject("ADODB.Connection")
db.Open "Provider=SQLOLEDB; Data Source=SERVER; Initial Catalog=database; User ID=username; Password=password"

check this link :
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?lngWId=4&txtCodeId=6442

regards,
Harshal.|||Dear Harshal,

Set db = Server.CreateObject("ADODB.Connection")
db.Open "Provider=SQLOLEDB; Data Source=SERVER; Initial Catalog=database; User ID=username; Password=password"

with this script above, isn't it connecting ASP to SQL Server via OLE DB?
Is connecting via OLE DB and ODBC which i have to set in the Administrator Tool for it to work, the same?
Thanx

Connect Excel Pivot Table to SQL Server Stored Procedure

Friends,
Hi ...
I have been connecting Excel pivot tables to SQL server tables and views
through an ODBC DSN connection.
I know there is a way to connect a pivot table to a SQL Server stored
procedure, but can't figure it out. The Excel pivot table connection wizard
doesn't seem to offer stored procedures as one of the connection choices.
Anyone out there know how to do this?
Thanks, Bill Morgan
Thanks ...
Hi, again ...
Got answer from SQL Server programmer, and was able to connect Excel pivot
table to SQL Server stored procedure:
In Excel, go to Data \ Get External Data \ New Database Query. Choose SQL
Server connection and then select the appropriate DSN connection to your SQL
Server (and in the DSN options, be sure to set the DSN default to the right
database).
Follow the dialog box prompts. You may not see the right tables/views to
choose from, but select any table in order to keep going through the
successive dialog boxes. When given the choice, you want to "View Data or
Edit Query in Microsoft Query" (dialog box with 3 radio buttons - you want
the middle button in Excel 2000)
When you get to the end of the dialog boxes, go into View/SQL (or click on
SQL icon on the toolbar) and delete the SQL code that is in the SQL box. In
it's place, type EXEC your_database_name.Owner.your_stored_procedure_nam e.
Click OK when the dialog box says you cannot view your query graphically.
Once you see the query result (rows and columns of data) go to File \ Export
results to Excel. Then be sure to check the Pivot Table radio button on the
dialog box that comes up.
The pivot table is refreshable, and as long as the UserName in the DSN has
ReadOnly priveledges (and as long as you have granted EXEC permission on the
stored procedure) you can share the pivot table with other users.
"bill_morgan_3333" wrote:

> Friends,
> Hi ...
> I have been connecting Excel pivot tables to SQL server tables and views
> through an ODBC DSN connection.
> I know there is a way to connect a pivot table to a SQL Server stored
> procedure, but can't figure it out. The Excel pivot table connection wizard
> doesn't seem to offer stored procedures as one of the connection choices.
> Anyone out there know how to do this?
> Thanks, Bill Morgan
> Thanks ...
>

Connect Excel Pivot Table to SQL Server Stored Procedure

Friends,
Hi ...
I have been connecting Excel pivot tables to SQL server tables and views
through an ODBC DSN connection.
I know there is a way to connect a pivot table to a SQL Server stored
procedure, but can't figure it out. The Excel pivot table connection wizard
doesn't seem to offer stored procedures as one of the connection choices.
Anyone out there know how to do this?
Thanks, Bill Morgan
Thanks ...Hi, again ...
Got answer from SQL Server programmer, and was able to connect Excel pivot
table to SQL Server stored procedure:
In Excel, go to Data \ Get External Data \ New Database Query. Choose SQL
Server connection and then select the appropriate DSN connection to your SQL
Server (and in the DSN options, be sure to set the DSN default to the right
database).
Follow the dialog box prompts. You may not see the right tables/views to
choose from, but select any table in order to keep going through the
successive dialog boxes. When given the choice, you want to "View Data or
Edit Query in Microsoft Query" (dialog box with 3 radio buttons - you want
the middle button in Excel 2000)
When you get to the end of the dialog boxes, go into View/SQL (or click on
SQL icon on the toolbar) and delete the SQL code that is in the SQL box. In
it's place, type EXEC your_database_name.Owner.your_stored_procedure_name.
Click OK when the dialog box says you cannot view your query graphically.
Once you see the query result (rows and columns of data) go to File \ Export
results to Excel. Then be sure to check the Pivot Table radio button on the
dialog box that comes up.
The pivot table is refreshable, and as long as the UserName in the DSN has
ReadOnly priveledges (and as long as you have granted EXEC permission on the
stored procedure) you can share the pivot table with other users.
"bill_morgan_3333" wrote:

> Friends,
> Hi ...
> I have been connecting Excel pivot tables to SQL server tables and views
> through an ODBC DSN connection.
> I know there is a way to connect a pivot table to a SQL Server stored
> procedure, but can't figure it out. The Excel pivot table connection wizar
d
> doesn't seem to offer stored procedures as one of the connection choices.
> Anyone out there know how to do this?
> Thanks, Bill Morgan
> Thanks ...
>

Connect Access to SQL Server AND Local ODBC Connection

I have a customer that wants me to create a database and use a back end of
SQL Server (hosted on the Internet for his site). This would normally be
easy to create the front end with an ADP and continue life as normal. But,
in order to connect his accounting program to this program that I am
creating, I also need the ADP/MDB to connect to his local ODBC Link
(Business Vision Connect!) This is the software that is installed on the
local machine to speak to the accounting software. I can get Connect! to
work when it isn't in an ADP. I can get SQL Server to work in an ADP. So,
at this point, I can get one or the other to work...
Does anyone know of a way to get SQL Server to work with an MDB without
asking for the SQLServer Password OR a way to get his Connect (Regular ODBC
System DSN) to work in an ADP? I have also tried creating a file DSN (for
Connect!) which looks like it could work with an ADP, but since the
Connect! is rather old, it doesn't seem to like to be put into a FileDSN.
Any ideas would be great!!!
Thanks,
Shawn
You can connect an mdb to SQL Server by creating linked tables or by
using pass-through queries. You supply the connection string in code.
From the MDB, create a DAO TableDef object for the linked tables, and
QueryDef objects for the pass-through queries (which return read-only
result sets). HTH,
Mary
On Wed, 16 Jun 2004 20:27:05 -0400, "Shawn Oatley"
<notmyemail@.address.com> wrote:

>I have a customer that wants me to create a database and use a back end of
>SQL Server (hosted on the Internet for his site). This would normally be
>easy to create the front end with an ADP and continue life as normal. But,
>in order to connect his accounting program to this program that I am
>creating, I also need the ADP/MDB to connect to his local ODBC Link
>(Business Vision Connect!) This is the software that is installed on the
>local machine to speak to the accounting software. I can get Connect! to
>work when it isn't in an ADP. I can get SQL Server to work in an ADP. So,
>at this point, I can get one or the other to work...
>Does anyone know of a way to get SQL Server to work with an MDB without
>asking for the SQLServer Password OR a way to get his Connect (Regular ODBC
>System DSN) to work in an ADP? I have also tried creating a file DSN (for
>Connect!) which looks like it could work with an ADP, but since the
>Connect! is rather old, it doesn't seem to like to be put into a FileDSN.
>Any ideas would be great!!!
>Thanks,
>Shawn
>
|||Shawn,
Find a way to make the ADP work. Linked tables with mdb are a nightmare. Modifying pass through queries on the fly bloats your database and are much harder to maintain than sprocs. Linked tables create excessive connection/locking which leads to DEADLO
CKS. Linked tables should *not be even considered*when the database is accesses through the internet.
Here is a link to a site with numereous connections string examples. http://www.able-consulting.com/ADO_Conn.htm
"Shawn Oatley" wrote:

> I have a customer that wants me to create a database and use a back end of
> SQL Server (hosted on the Internet for his site). This would normally be
> easy to create the front end with an ADP and continue life as normal. But,
> in order to connect his accounting program to this program that I am
> creating, I also need the ADP/MDB to connect to his local ODBC Link
> (Business Vision Connect!) This is the software that is installed on the
> local machine to speak to the accounting software. I can get Connect! to
> work when it isn't in an ADP. I can get SQL Server to work in an ADP. So,
> at this point, I can get one or the other to work...
> Does anyone know of a way to get SQL Server to work with an MDB without
> asking for the SQLServer Password OR a way to get his Connect (Regular ODBC
> System DSN) to work in an ADP? I have also tried creating a file DSN (for
> Connect!) which looks like it could work with an ADP, but since the
> Connect! is rather old, it doesn't seem to like to be put into a FileDSN.
> Any ideas would be great!!!
> Thanks,
> Shawn
>
>

Connect Access to SQL Server AND Local ODBC Connection

I have a customer that wants me to create a database and use a back end of
SQL Server (hosted on the Internet for his site). This would normally be
easy to create the front end with an ADP and continue life as normal. But,
in order to connect his accounting program to this program that I am
creating, I also need the ADP/MDB to connect to his local ODBC Link
(Business Vision Connect!) This is the software that is installed on the
local machine to speak to the accounting software. I can get Connect! to
work when it isn't in an ADP. I can get SQL Server to work in an ADP. So,
at this point, I can get one or the other to work...
Does anyone know of a way to get SQL Server to work with an MDB without
asking for the SQLServer Password OR a way to get his Connect (Regular ODBC
System DSN) to work in an ADP? I have also tried creating a file DSN (for
Connect!) which looks like it could work with an ADP, but since the
Connect! is rather old, it doesn't seem to like to be put into a FileDSN.
Any ideas would be great!!!
Thanks,
ShawnYou can connect an mdb to SQL Server by creating linked tables or by
using pass-through queries. You supply the connection string in code.
From the MDB, create a DAO TableDef object for the linked tables, and
QueryDef objects for the pass-through queries (which return read-only
result sets). HTH,
Mary
On Wed, 16 Jun 2004 20:27:05 -0400, "Shawn Oatley"
<notmyemail@.address.com> wrote:

>I have a customer that wants me to create a database and use a back end of
>SQL Server (hosted on the Internet for his site). This would normally be
>easy to create the front end with an ADP and continue life as normal. But,
>in order to connect his accounting program to this program that I am
>creating, I also need the ADP/MDB to connect to his local ODBC Link
>(Business Vision Connect!) This is the software that is installed on the
>local machine to speak to the accounting software. I can get Connect! to
>work when it isn't in an ADP. I can get SQL Server to work in an ADP. So,
>at this point, I can get one or the other to work...
>Does anyone know of a way to get SQL Server to work with an MDB without
>asking for the SQLServer Password OR a way to get his Connect (Regular ODBC
>System DSN) to work in an ADP? I have also tried creating a file DSN (for
>Connect!) which looks like it could work with an ADP, but since the
>Connect! is rather old, it doesn't seem to like to be put into a FileDSN.
>Any ideas would be great!!!
>Thanks,
>Shawn
>|||Shawn,
Find a way to make the ADP work. Linked tables with mdb are a nightmare. M
odifying pass through queries on the fly bloats your database and are much h
arder to maintain than sprocs. Linked tables create excessive connection/lo
cking which leads to DEADLO
CKS. Linked tables should *not be even considered*when the database is acce
sses through the internet.
Here is a link to a site with numereous connections string examples. .able-consulting.com/ADO_Conn.htm" target="_blank">http://www
.able-consulting.com/ADO_Conn.htm
"Shawn Oatley" wrote:

> I have a customer that wants me to create a database and use a back end of
> SQL Server (hosted on the Internet for his site). This would normally be
> easy to create the front end with an ADP and continue life as normal. But
,
> in order to connect his accounting program to this program that I am
> creating, I also need the ADP/MDB to connect to his local ODBC Link
> (Business Vision Connect!) This is the software that is installed on the
> local machine to speak to the accounting software. I can get Connect! to
> work when it isn't in an ADP. I can get SQL Server to work in an ADP. So,
> at this point, I can get one or the other to work...
> Does anyone know of a way to get SQL Server to work with an MDB without
> asking for the SQLServer Password OR a way to get his Connect (Regular ODB
C
> System DSN) to work in an ADP? I have also tried creating a file DSN (fo
r
> Connect!) which looks like it could work with an ADP, but since the
> Connect! is rather old, it doesn't seem to like to be put into a FileDSN.
> Any ideas would be great!!!
> Thanks,
> Shawn
>
>

connec to sql express

at work, I'm trying to use Native client under ODBC to connect to another pc have the SQl Expess server, but never get connect. the message said the sql express may not allow remote connect.

I run a test, install sql express at home and install native client on the second pc and get connect easily.

So what do I miss here. Please help. thanks

You need to explicitly enable remote connections for SQL Express since they are off by default. You can enable them during install using the DISABLENETWORKPROTOCOLS flag:

(From template.ini)

;--
; The DISABLENETWORKPROTOCOLS switch is used to disable network protocol for SQL Server instance.
; Set DISABLENETWORKPROTOCOLS = 0; for Shared Memory= On, Named Pipe= On, TCP= On
; Set DISABLENETWORKPROTOCOLS = 1; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= Off
; Set DISABLENETWORKPROTOCOLS = 2; for Shared Memory= On, Named Pipe= Off (Local Only), TCP= On

; Note: DISABLENETWORKPROTOCOLS if not specified has the following defaults.
; Default value for SQL Server Express/Evaluation/Developer: DISABLENETWORKPROTOCOLS =1
; Default value for Enterprise/Standard /Workgroup: DISABLENETWORKPROTOCOLS =2

or after install using TSQL or following this blog:

http://support.microsoft.com/kb/914277

Thanks,
Sam Lester (MSFT)

|||

Tried it, still not connect. Any other way? Thanks

confused connection-SQL7

I just had a very strange (and scary) experience; on my test machine I have one ODBC set up to connect to my test SQL server. I just realized that some of the tables I have linked to this SQL server in an Access database are actually linked to the SQL se
rver on the production database. How can this happen? I don't have an ODBC set up for the production database on the test machine at all. I did have the same passwords in both SQL servers and I just changed one but what else can I do to protect against
this?
Unless you have the same name or IP address or changed the Test server to
Production and kept the same name I'm not sure how this could happend.
Unless there was a change to DNS or WINS that changed the way the machine
names are resolved...
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||It may have just been viewing the tables based on the meta
data stored on the Access side - did you actually select,
insert, update or delete from the tables? .
You can get around this and other issues with linked tables
by using code to reattach the tables when the application
starts.
-Sue
On Sat, 17 Jul 2004 06:38:01 -0700, "PatO"
<PatO@.discussions.microsoft.com> wrote:

>I just had a very strange (and scary) experience; on my test machine I have one ODBC set up to connect to my test SQL server. I just realized that some of the tables I have linked to this SQL server in an Access database are actually linked to the SQL s
erver on the production database. How can this happen? I don't have an ODBC set up for the production database on the test machine at all. I did have the same passwords in both SQL servers and I just changed one but what else can I do to protect agains
t this?
|||Thanks for your input. I realized last night that the production server is listed as a remote server on the test server. Could this be how this happened?
Pat
"Kevin McDonnell [MSFT]" wrote:

> Unless you have the same name or IP address or changed the Test server to
> Production and kept the same name I'm not sure how this could happend.
> Unless there was a change to DNS or WINS that changed the way the machine
> names are resolved...
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||Thanks for your reply. Yes I actually did update the production tables through Access.
"Sue Hoegemeier" wrote:
[vbcol=seagreen]
> It may have just been viewing the tables based on the meta
> data stored on the Access side - did you actually select,
> insert, update or delete from the tables? .
> You can get around this and other issues with linked tables
> by using code to reattach the tables when the application
> starts.
> -Sue
> On Sat, 17 Jul 2004 06:38:01 -0700, "PatO"
> <PatO@.discussions.microsoft.com> wrote:
server on the production database. How can this happen? I don't have an ODBC set up for the production database on the test machine at all. I did have the same passwords in both SQL servers and I just changed one but what else can I do to protect agai
nst this?
>
|||Yes, but you would have had to choose this server during the linking step
in Access.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Then you have the same DSN on the machine which points to
the production server. Or you linked to the production
server. You can find the string used to link the tables in
MSysObjects under the Connect column.
-Sue
On Tue, 20 Jul 2004 05:28:02 -0700, "PatO"
<PatO@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for your reply. Yes I actually did update the production tables through Access.
>"Sue Hoegemeier" wrote:
L server on the production database. How can this happen? I don't have an ODBC set up for the production database on the test machine at all. I did have the same passwords in both SQL servers and I just changed one but what else can I do to protect aga
inst this?[vbcol=seagreen]

confused connection-SQL7

I just had a very strange (and scary) experience; on my test machine I have
one ODBC set up to connect to my test SQL server. I just realized that some
of the tables I have linked to this SQL server in an Access database are ac
tually linked to the SQL se
rver on the production database. How can this happen? I don't have an ODBC
set up for the production database on the test machine at all. I did have
the same passwords in both SQL servers and I just changed one but what else
can I do to protect against
this?Unless you have the same name or IP address or changed the Test server to
Production and kept the same name I'm not sure how this could happend.
Unless there was a change to DNS or WINS that changed the way the machine
names are resolved...
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||It may have just been viewing the tables based on the meta
data stored on the Access side - did you actually select,
insert, update or delete from the tables? .
You can get around this and other issues with linked tables
by using code to reattach the tables when the application
starts.
-Sue
On Sat, 17 Jul 2004 06:38:01 -0700, "PatO"
<PatO@.discussions.microsoft.com> wrote:

>I just had a very strange (and scary) experience; on my test machine I have one ODB
C set up to connect to my test SQL server. I just realized that some of the tables
I have linked to this SQL server in an Access database are actually linked to the SQ
L s
erver on the production database. How can this happen? I don't have an ODB
C set up for the production database on the test machine at all. I did have
the same passwords in both SQL servers and I just changed one but what else
can I do to protect agains
t this?|||Thanks for your input. I realized last night that the production server is
listed as a remote server on the test server. Could this be how this happen
ed?
Pat
"Kevin McDonnell [MSFT]" wrote:

> Unless you have the same name or IP address or changed the Test server to
> Production and kept the same name I'm not sure how this could happend.
> Unless there was a change to DNS or WINS that changed the way the machine
> names are resolved...
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Thanks for your reply. Yes I actually did update the production tables thro
ugh Access.
"Sue Hoegemeier" wrote:

> It may have just been viewing the tables based on the meta
> data stored on the Access side - did you actually select,
> insert, update or delete from the tables? .
> You can get around this and other issues with linked tables
> by using code to reattach the tables when the application
> starts.
> -Sue
> On Sat, 17 Jul 2004 06:38:01 -0700, "PatO"
> <PatO@.discussions.microsoft.com> wrote:
>
server on the production database. How can this happen? I don't have an OD
BC set up for the production database on the test machine at all. I did hav
e the same passwords in both SQL servers and I just changed one but what els
e can I do to protect agai
nst this?[vbcol=seagreen]
>|||Yes, but you would have had to choose this server during the linking step
in Access.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Then you have the same DSN on the machine which points to
the production server. Or you linked to the production
server. You can find the string used to link the tables in
MSysObjects under the Connect column.
-Sue
On Tue, 20 Jul 2004 05:28:02 -0700, "PatO"
<PatO@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for your reply. Yes I actually did update the production tables thr
ough Access.
>"Sue Hoegemeier" wrote:
>
L server on the production database. How can this happen? I don't have an
ODBC set up for the production database on the test machine at all. I did h
ave the same passwords in both SQL servers and I just changed one but what e
lse can I do to protect aga
inst this?[vbcol=seagreen]

Configuring client acces to SQL Server

I have just installed on a server, Sql Server 7.0.

I want to acces to that server from a client. When I try to configure on the client the ODBC connection to the databases of my sql server, it says I don't have license.

Have I to configure something on the client or on the server to be able to acces from a client?

Thanks and best regards
:confused:Can you acces the SQL server from EM or QA?|||what license mode have you selected at the time of installation? also, check if licensing service is running on the server and through control panel enter the actual number of licenses that you purchased.