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 Server

Hi,

As you know in SQL Server we must connect to a server to create , manage and execute databases. My problem is, I can't connect. I have searched the web for some information, they all say "write to server name your machine name to connect". I did so and get an error.

the message is ;
An error has occured while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.(provider: Named Pipes Provider,error:40-Could not open a connection to SQL Server)(Microsoft SQL Server,Error :53)

My pc is not connected to a server.

How can I make my pc act like a server so that i can connect to it.

Hi,

This is a usual problem that occurs whilst using SQL Server 2005.

You can resolve this issue by following steps...

Use Surface Area Configuration tool to manage your connection protocol Add SQL Server Service as an Exception in Windows Firewall Use MACHINE_NAME\INSTANCE_NAME to reduce conflicts.|||

firstly thanks for the advise, Now i have a proble in Surface Area Configuration tool.

when I click to surface area configuration for services and connections an error occurs.

it says.

TITLE: Surface Area Configuration

No SQL Server 2005 components were found on the specified computer. Either no components are installed, or you are not an administrator on this computer. (SQLSAC)

now how can i solve this question ?

|||

Welcome,

First, have you logged in as administrator? If not, then do so. If you are using Windows Vista, then even if you are logged in as an administrator, right click the Surface Area Configuration tool and say "Run As Administrator".

This should solve your problem.

I guess, there are no further issues related with this.

Lastly, if all fails, the genuine solution is - re-install If still you are facing problems, let me know.

Mayur Tendulkar

Microsoft Student Partner

|||

Have you installed a SQL Server Edition on your local computer?

|||

Thats a good question, you have to confirm what edition of SQL 2005 you have installed here.

http://www.sql-server-performance.com//faq/?f=143 too fyi.

|||

thanks to everyone tryin to help.

I have solved the problem

|||

Hi,

Its good to know that you have solved the problem.

But if you could post the exact cause and solution over here, then someday - somewhere, it might help others.

Mayur Tendulkar

Microsoft Student Partner

Connecting to local SQL Server

Hi,

I am having problems connecting to databases on my local SQL Server, express edition from my website.

The following is the connection string i am using:

Database=MyDatabase;Server=COMP\SQLEXPRESS;User ID=sa;

The error I get is "Login failed for sa"

I have tried other users but no luck. Also, I cant seem to create a new user as I don't have administrators rights, any ideas how i can do this?

Any ideas?

did you try it this way

<add name="CommerceTemplate" connectionString="Data Source=Comp.Name\SQLEXPRESS;Initial Catalog=dbname;Trusted_Connection=True;"
providerName="System.Data.SqlClient" />

hope this helps

|||

see

www.connectionstrings.com

for help

|||

Hi,

From your description, it seems that you are using "sa" account to log in your SQLServer, that is to say you have set your SQLServer as "SQL Mixed Authentication mode", right?

Based on the error message, there are two possibilities. First, try to log into your SQLServer by SQLServer Management Studio with the 'sa' account, to see if it can work. If not, I guess the account and password maybe invalid. Second, if you are using Windows Authentication mode, you should also make your connection string looks like:

"data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"

Thanks.

Connecting to another SQL Server

Hi,

We are using two databases Live and Contingency. Everyday Backup is
taken and restored the same on Contingency Server. Cotingency Server act
as Live when Live server goes down.

I need to Connect to Contingency Server from Live Server through my
Stored Procedure to Stop/Delete particular Job running on Contingency
Server. Please, can anyone help me in this.

Sun

*** Sent via Developersdex http://www.developersdex.com ***What about set up a Linked Server , from Contingency to LIve ?

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Sun G" <sun_x22@.yahoo.com> wrote in message
news:5SwUf.6$Ea7.783@.news.uswest.net...
> Hi,
> We are using two databases Live and Contingency. Everyday Backup is
> taken and restored the same on Contingency Server. Cotingency Server act
> as Live when Live server goes down.
> I need to Connect to Contingency Server from Live Server through my
> Stored Procedure to Stop/Delete particular Job running on Contingency
> Server. Please, can anyone help me in this.
>
> Sun
> *** Sent via Developersdex http://www.developersdex.com ***

Connecting to a remote OLE DB

Hey, all. I've mainly worked with MySQL databases before. I'm working on an (old) ASP page that uses this connection string:

strConnect ="Provider=SQLOLEDB; Data Source=SQL1.MYDBSERVER.NET; Initial Catalog=...; User ID=...; Password=..."

It works fine on the production server, but the local copy fails if I try to access the Products page (which uses the database), with this error:

Microsoft OLE DB Providerfor SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
/html/products.asp, line 19

Similarly, if I plug the connection info into Tools/Connect to Database in Visual Studio, I get:

Connection failed:
SQLState:'08001'SQL Server Error: 17[DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied.

Am I doing something wrong? Do I need more info from the hosting provider to connect remotely? Or is the database not accepting remote connections at all?

Thanks in advance for your help!

It might be that you do not have permission to access it or it might be that database is old (version 7.0) and that you cannot connect from Management studio. Check it with hosting provider.

connecting to 2 diffrent sql servers databases from single web application

i am having 2 sql servers one is web sql server server and other server is my local sql server. i am making web application through which i want to connect both sql servers at a time and by selecting data from web server i want to insert it into my local sql server both having same database on which i want to work , i want to know how it can be done

my idea as follows in web.config i specify 2 connection string and by selecting data from websql server database table i going to store it in dataset and then i return dataset to another function which connects to local sqlserver database.but database records are much large. can any body guide me .

i am going to place my web application on local server (C# asp.net 2.0) .

thank you,

i tried this code

c# file

public partialclass _Default : System.Web.UI.Page {protected void Page_Load(object sender, EventArgs e) { }protected void Button1_Click(object sender, EventArgs e) { Uupdatedata(); }public void Uupdatedata() {string constr1=ConfigurationSettings.AppSettings["abccon1"];string constr2 = ConfigurationSettings.AppSettings["abccon2"]; System.Data.SqlClient.SqlConnection dbcon1 =new System.Data.SqlClient.SqlConnection(constr1); dbcon1.Open(); System.Data.SqlClient.SqlConnection dbcon2 =new System.Data.SqlClient.SqlConnection(constr2); dbcon2.Open(); SqlDataReader dr; SqlCommand cmd =new SqlCommand("select * from Unit_Master",dbcon1); dr = cmd.ExecuteReader();while (dr.Read()) {string Sql ="insert into Table1(Unit_Id,Unit_Desc) values(" + dr.GetInt32(0).ToString() +"," + dr.GetString(1) +")"; SqlCommand cmd1=new SqlCommand (Sql,dbcon2);//cmd1.EndExecuteNonQuery (); }//dr.Close(); //dbcon2.Close(); }

web.config

<configuration><appSettings><add key="abccon1" value="Data Source=comp01;Initial Catalog=abc;User Id=sa ; Password=sa"/><add key="abccon2" value="Data Source=comp01;Initial Catalog=abctest;User Id=sa ; Password=sa"/></appSettings><connectionStrings/>

this 2 databases are from single my loacl server

it wont work it gives exception

please healp me.

What line is the exception thrown on?

|||

And also, I'm going to assume that your obfuscating your connection information, because your just using "comp01" for both, and you said that 1 of the DB's is on a remote machine, correct?

|||

i pasted code of testing application i making and exception is not thrown but when i debug at that time in second connection ie dbcon2 properties shows exception otherwisecode works fine but nothing inserted in the second table,please healp me.

|||

Hi,

i specify 2 connection string and by selecting data from websql server database table i going to store it in dataset and then i return dataset to another function which connects to local sqlserver database

From your code you provided, it seems that you did not follow the way you mentioned, instead, you are using datareader to retrieve those data and create each insert command and execute it for each row, right? After you have create the sqlcommand, why don't use call the ExecuteNonQuery() method to execute the command.

Besides, you mentioned that the records in database are much large. Then you'll loop many times while retrieving your data from datareader, right? Then in your code, the sqlcommand object instance would be created repeatly. It would damage the performance. So what I suggest is create the instance of sqlcommand once, and modify the commandtext of sqlcommand and execute it in each loop.

Thanks.

connecting to 2 databases inside one query

hi there,
I saw a similar thread before but i've tried it and no luck. I want run a query off a database (stawebdata.branchinfo) then a join into bluepages.phonelist

i've tired

JOIN bluepages.phonelist p

Incorrect syntax near the keyword 'JOIN'.

any ideas... is this possible?

complete code below:

select DISTINCT b.name, b.tel1, b.id, b.telprivate,b.sc, b.manager,b.branchEmail
from branchInfo b
inner
JOIN bluepages.phonelist p on b.name = p.subdepartment
where b.name like 'Sarah' or b.id LIKE '%@.keyword%' OR b.county LIKE '%@.keyword%' OR b.Area LIKE '%@.keyword%' OR b.Manager LIKE '%@.keyword%' OR b.AssistantManager LIKE '%@.keyword%' OR b.Postcode LIKE '%@.keyword%' OR b.PseudoCity LIKE '%@.keyword%' OR b.TicketingTA LIKE '%@.keyword%' OR b.SLC LIKE '%@.keyword%' OR b.ItineraryTA LIKE '%@.keyword%' OR b.TIDS LIKE '%@.keyword%' OR b.ABTA LIKE '%@.keyword%' and
p.department = 'UK Branch' and
b.isbranch <> 'False' or
(p.lastName = '@.keyword' or p.firstname = '@.keyword')
order by b.name asc
GOyou have to put

JOIN bluepages.dbo.phonelist

i thought "dbo" was a param!

derrr|||How about putting two dots:

JOIN bluepages..phonelist

Roshmi Choudhury

Connecting a vb6 app to a sequel 2005 database

We are currently migrating our sequel 6.5 databases to sequel 2005
We have a handful of vb6 apps that connect to the current 6.5 database.
Can anyone recommend the "best practice" for connecting vb6 apps to sequel
2005 databases ?
Thanks in advance.
Sure, but my books only discuss SQL Server, not "sequel" ;)
The ConnectionString used to connect to a SQL Server 2005 engine is very
similar to the one you're using to access a SQL Server 6.5 engine with a
couple of differences:
* First, use the new SNAC (SQL Native Client) provider instead of
SQLOLEDB. This is installed with all versions of SQL Server 2005. See
http://msdn2.microsoft.com/en-us/data/aa937705.aspx for more information.
* Next, consider that SQL Server 2005 can be installed as a named
instance. This permits you to have both SQL Server 6.5 and SQL Server 2005
installed on the same server. If you have used a named instance, you can
address it using the machine/server name followed by the instance name--like
this:
Dim con As New ADODB.Connection
con.ConnectionString = "Provider=SQLNCLI;" _
& "Server=MyServer/MySQLServer2005InstanceName;" _
& "Database=AdventureWorks;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"
con.Open
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"PhilEvans" <PhilEvans@.discussions.microsoft.com> wrote in message
news:409E884C-5165-48D1-A88B-77E802BCFC40@.microsoft.com...
> We are currently migrating our sequel 6.5 databases to sequel 2005
> We have a handful of vb6 apps that connect to the current 6.5 database.
> Can anyone recommend the "best practice" for connecting vb6 apps to sequel
> 2005 databases ?
> Thanks in advance.
>

Connecting a vb6 app to a sequel 2005 database

We are currently migrating our sequel 6.5 databases to sequel 2005
We have a handful of vb6 apps that connect to the current 6.5 database.
Can anyone recommend the "best practice" for connecting vb6 apps to sequel
2005 databases ?
Thanks in advance.Sure, but my books only discuss SQL Server, not "sequel" ;)
The ConnectionString used to connect to a SQL Server 2005 engine is very
similar to the one you're using to access a SQL Server 6.5 engine with a
couple of differences:
* First, use the new SNAC (SQL Native Client) provider instead of
SQLOLEDB. This is installed with all versions of SQL Server 2005. See
http://msdn2.microsoft.com/en-us/data/aa937705.aspx for more information.
* Next, consider that SQL Server 2005 can be installed as a named
instance. This permits you to have both SQL Server 6.5 and SQL Server 2005
installed on the same server. If you have used a named instance, you can
address it using the machine/server name followed by the instance name--like
this:
Dim con As New ADODB.Connection
con.ConnectionString = "Provider=SQLNCLI;" _
& "Server=MyServer/MySQLServer2005InstanceName;" _
& "Database=AdventureWorks;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"
con.Open
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"PhilEvans" <PhilEvans@.discussions.microsoft.com> wrote in message
news:409E884C-5165-48D1-A88B-77E802BCFC40@.microsoft.com...
> We are currently migrating our sequel 6.5 databases to sequel 2005
> We have a handful of vb6 apps that connect to the current 6.5 database.
> Can anyone recommend the "best practice" for connecting vb6 apps to sequel
> 2005 databases ?
> Thanks in advance.
>

Connect Two Databases Together

I am interested in having two databases that are on the same server
connected together. I have the individual databases but I also want to have
a common database that keeps stored procedures to do things such as changing
password. I don't want to cut and paste them into each database each time
plus if I ever made a change I am creating a lot of work for myself.
Users would have the same login and password for the two databases. What is
the simpliest way to set this up?
Thanks
Can
There are several options, ranging from linking tables, to managing them
both with common scripts. It really depends on why you want them to be
separate databases instead of a single database, and then why you want
certain things in common -- ease of maintenance, security, or something
else?
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
"Can" <Can@.discussions.microsoft.com> wrote in message
news:74E21166-FD11-46E5-AF30-A7415D2D331D@.microsoft.com...
> I am interested in having two databases that are on the same server
> connected together. I have the individual databases but I also want to
> have
> a common database that keeps stored procedures to do things such as
> changing
> password. I don't want to cut and paste them into each database each time
> plus if I ever made a change I am creating a lot of work for myself.
> Users would have the same login and password for the two databases. What
> is
> the simpliest way to set this up?
> Thanks
> Can
>
|||I am looking to llink stored procedures, not tables, from one database to
another for ease of maintenance. What direction do you suggest?
"Jeff A. Stucker" wrote:

> There are several options, ranging from linking tables, to managing them
> both with common scripts. It really depends on why you want them to be
> separate databases instead of a single database, and then why you want
> certain things in common -- ease of maintenance, security, or something
> else?
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Senior Consultant
> www.rapidigm.com
> "Can" <Can@.discussions.microsoft.com> wrote in message
> news:74E21166-FD11-46E5-AF30-A7415D2D331D@.microsoft.com...
>
>

Connect Two Databases Together

I am interested in having two databases that are on the same server
connected together. I have the individual databases but I also want to have
a common database that keeps stored procedures to do things such as changing
password. I don't want to cut and paste them into each database each time
plus if I ever made a change I am creating a lot of work for myself.
Users would have the same login and password for the two databases. What is
the simpliest way to set this up?
Thanks
CanThere are several options, ranging from linking tables, to managing them
both with common scripts. It really depends on why you want them to be
separate databases instead of a single database, and then why you want
certain things in common -- ease of maintenance, security, or something
else?
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
"Can" <Can@.discussions.microsoft.com> wrote in message
news:74E21166-FD11-46E5-AF30-A7415D2D331D@.microsoft.com...
> I am interested in having two databases that are on the same server
> connected together. I have the individual databases but I also want to
> have
> a common database that keeps stored procedures to do things such as
> changing
> password. I don't want to cut and paste them into each database each time
> plus if I ever made a change I am creating a lot of work for myself.
> Users would have the same login and password for the two databases. What
> is
> the simpliest way to set this up?
> Thanks
> Can
>|||I am looking to llink stored procedures, not tables, from one database to
another for ease of maintenance. What direction do you suggest?
"Jeff A. Stucker" wrote:

> There are several options, ranging from linking tables, to managing them
> both with common scripts. It really depends on why you want them to be
> separate databases instead of a single database, and then why you want
> certain things in common -- ease of maintenance, security, or something
> else?
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Senior Consultant
> www.rapidigm.com
> "Can" <Can@.discussions.microsoft.com> wrote in message
> news:74E21166-FD11-46E5-AF30-A7415D2D331D@.microsoft.com...
>
>

Connect to sqlexpress

Hi, I have SQL 2005 developer edition on my laptop. I am wondering if it is
possible to work with databases created for sql express edition. What I mean,
is that I have VS 2005, I created a Personal web site starter kit. The wizard
creates database files in the app_data directory.
So is it possible to connect to this "personal.mdf" while we have the
developer edition and the express edition. If yes, how can we do that
In the web.config I have
connectionString="Data Source=.\sqlexpress;Integrated security=True;User
instance=True;AttachDBFilename=|datadirecor...... ."
Thanks for your help
For security/performance reasons, the dynamic attachment of a MDF file using
the parameter AttachDBFilename in the connection string is only available
with SQL-2005 Express and not with the other editions of SQL-2005.
So you will have to permanently attach the mdf file to the Developer
instance or install an instance of SQL-Express on your machine. You can
have both instances running at the same time but you cannot have the same
mdf file attached to both instances at the same time.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"SalamElias" <eliassal@.online.nospam> wrote in message
news:B4E5748E-176A-427E-8507-912A020D11EB@.microsoft.com...
> Hi, I have SQL 2005 developer edition on my laptop. I am wondering if it
> is
> possible to work with databases created for sql express edition. What I
> mean,
> is that I have VS 2005, I created a Personal web site starter kit. The
> wizard
> creates database files in the app_data directory.
> So is it possible to connect to this "personal.mdf" while we have the
> developer edition and the express edition. If yes, how can we do that
> In the web.config I have
> connectionString="Data Source=.\sqlexpress;Integrated security=True;User
> instance=True;AttachDBFilename=|datadirecor...... ."
> Thanks for your help

Connect to sqlexpress

Hi, I have SQL 2005 developer edition on my laptop. I am wondering if it is
possible to work with databases created for sql express edition. What I mean
,
is that I have VS 2005, I created a Personal web site starter kit. The wizar
d
creates database files in the app_data directory.
So is it possible to connect to this "personal.mdf" while we have the
developer edition and the express edition. If yes, how can we do that
In the web.config I have
connectionString="Data Source=.\sqlexpress;Integrated security=True;User
instance=True;AttachDBFilename=|datadire
cor......"
Thanks for your helpFor security/performance reasons, the dynamic attachment of a MDF file using
the parameter AttachDBFilename in the connection string is only available
with SQL-2005 Express and not with the other editions of SQL-2005.
So you will have to permanently attach the mdf file to the Developer
instance or install an instance of SQL-Express on your machine. You can
have both instances running at the same time but you cannot have the same
mdf file attached to both instances at the same time.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"SalamElias" <eliassal@.online.nospam> wrote in message
news:B4E5748E-176A-427E-8507-912A020D11EB@.microsoft.com...
> Hi, I have SQL 2005 developer edition on my laptop. I am wondering if it
> is
> possible to work with databases created for sql express edition. What I
> mean,
> is that I have VS 2005, I created a Personal web site starter kit. The
> wizard
> creates database files in the app_data directory.
> So is it possible to connect to this "personal.mdf" while we have the
> developer edition and the express edition. If yes, how can we do that
> In the web.config I have
> connectionString="Data Source=.\sqlexpress;Integrated security=True;User
> instance=True;AttachDBFilename=|datadire
cor......"
> Thanks for your help