Connecting to SQL dbfs with existing code

No need to setup the web server as a primary domain
controller for the SQL Server connectivity to work.
After you configured the system DSN on the web server, did
you test the DSN as the very last step of setting up the
DSN? If yes, what error message did you get?
Linchi

quote:

---

>--Original Message--
>We recently moved our Access databases to SQL and are
>trying to get our pages to open the connections to the
>server. I've set the system DSNs on the server as needed.
>However, no connections are made. After reviewing several
>books, microsoft.com, etc...I'm severely frustrated. The
>only thing I can think that would possibly be an issue is
>that our webserver (Win2003) is not setup as a Primary
>Domain Server and we have to connect via http.
>Any suggestions are appreciated. THANKS!
>.
>

---

The test was completed successfully. That's what is perplexing.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Does the connection actually hit SQL Server?
Enable auditing for failed logins on SQL , then stop n start MSSQLServer
service.
Check the SQL Errorlogs after the failed connection attempt.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.sqlsql

Connecting to SQL dbfs with existing code

We recently moved our Access databases to SQL and are
trying to get our pages to open the connections to the
server. I've set the system DSNs on the server as needed.
However, no connections are made. After reviewing several
books, microsoft.com, etc...I'm severely frustrated. The
only thing I can think that would possibly be an issue is
that our webserver (Win2003) is not setup as a Primary
Domain Server and we have to connect via http.
Any suggestions are appreciated. THANKS!Does the System DSN connect if you Test it from the IIS server?
If not what is the OS error returned?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Connecting to Linked Servers

I know this is an easy one for most of you but this is new territory for me.

I am trying to link a production application database to an existing SMS database.

Both of these databases are SQL 2000. I have sa access to both of them.

I have an accreditation application that deals with internal processes within our active directory network. We are using SMS database collections to provide the discovery for reporting of this application database and I want to compare the collection to the existing data between the 2 databases.

For simplicity I will define them both.

Application Database we will call “A-Database”

SMS Database we will call “B-Database”

I have gone into the A-Database and gone to linked Servers under the security node in enterprise manager. I created a new linked database and selected the SQL Server option in the general Tab.

In the Security Tab I selected the “Be made using the security context” and used “sa” for the Remote login and the CORRECT sa password for the With password option.

The server options tab remain the same with ”RPC, RPC Out, & Use Remote Collation” selected.

It appears to connect but there is not any Table or Views showing up. What am I doing wrong here and what do I need to do to link these 2 database engines so I can move forward?

Thanks,

Gene Godsey

In the properties of the Linked Servers I am looking at trying to change my connection by selecting the general tab and selecting the option for Other Data Sources. I have selected the Microsoft OLE DB Provider for SQL Server and then I have to provide data for 3 fields.

In the Product Name field I just put the database name I want to use from the server. In the Data Source name I insert serverName\DataSource name.

The Privider string has me stumped.

The location is grayed out but I believe that this will open up when I have a provider name. I do not know what goes there either.

It says that the catelog name is optional. I am not worried about that field.

Any suggestions.

Thanks,

|||Hi Geneway,

Im not sure why you are putting the DB name in the Product Name? I dont understand that bit. Where is it saying Product Name?

Your datasource would be the server name.

Your catalog would be the database.

Keep us updated.

Barry Andrew (Pace)|||

In the enterprise manager of SQL 2000 go to the security node. Under this you would go to the linked servers. If there is a linked server I would right click and select the properties link.

When the properties dialog box opens there are 3 tabs. General/Security/Server Options.

I select the General tab and the\n I will select the “Other Data Source” option.

From here I would select the “Microsoft OLE DB Provider for SQL Server” Dropdown. At this point there are 3 text boxes that are available to insert data.

1: Product name:

2: Data Source:

3: Provider String:

I must have this information incorrect because I cannot see tables or view from this.

I have tried to select the SQL Server option but I cannot control what database that is selected in that option.

Any suggestions,

Thanks,

|||Hi,

Do as you have been doing and use the following;

1) SQL Server - this will do fine
2) NetworkNameHere!!! - IE the server name the sql database is running on
3) a) Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
b)Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

A and B are trusted and standard authentication methods respectively

You may also find the website; www.connectionstrings.com useful.

hth

Pace|||

I figured it out!

YEA!!!!!

I created an datareader account in the SMS database. I selected the connect to SQL Server option button in the general tab location.

Under security I selected the sa account but the remopte user was the datareader account that I created in the SMS Database. It was really easy but this is not very well documented.

Connecting an existing database to Management Studio

Hello,

I use visual studio express 2005, and just downloaded Management Studio Express. (I have SQL Express).

I've already created my projects and created database tables and everything but I do not know how to connect an existing database to management studio. Can someone tell me how to connect an existing database to management studio?

Thanks,

Computergirl

Here are few links that can help you

http://technet.microsoft.com/en-us/library/ms167593.aspx

http://technet.microsoft.com/en-us/library/ms170681.aspx

Connecting a user from a backup database to a new login

All,
In SQL Server 2000, I loaded a backup file into a new local database.
The database has an existing user which owns several stored procedures,
tables, etc.
Using SQL Server authentication, I want to create a login of the same
name as the user, so that when I login, I have access to the objects
owned by that user without having to prefix the objects with the owner
name.
For instance exec sp_mystoredproc instead of exec
ownername.sp_mystoredproc. (In fact, several of the stored procedures
exec other procedures without the owner name prefix and to go add the
owner will be a tedious process)
When I try to add login and grant the login name to the existing user
it tells me it already exists.
I cannot drop the existing user because it owns a whole bunch of
objects.
What can I do to work around this? How do I connect a login to an
existing database user? Is there a system table where I can just map
the two?
Brad> What can I do to work around this? How do I connect a login to an
> existing database user? Is there a system table where I can just map
> the two?
See the sp_change_users_login stored procedure in the Books Online. Also,
the Best Practice is to always schema-qualify objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"brad" <brad.eckrose@.gmail.com> wrote in message
news:1168719405.103237.233140@.38g2000cwa.googlegroups.com...
> All,
> In SQL Server 2000, I loaded a backup file into a new local database.
> The database has an existing user which owns several stored procedures,
> tables, etc.
> Using SQL Server authentication, I want to create a login of the same
> name as the user, so that when I login, I have access to the objects
> owned by that user without having to prefix the objects with the owner
> name.
> For instance exec sp_mystoredproc instead of exec
> ownername.sp_mystoredproc. (In fact, several of the stored procedures
> exec other procedures without the owner name prefix and to go add the
> owner will be a tedious process)
> When I try to add login and grant the login name to the existing user
> it tells me it already exists.
> I cannot drop the existing user because it owns a whole bunch of
> objects.
> What can I do to work around this? How do I connect a login to an
> existing database user? Is there a system table where I can just map
> the two?
> Brad
>