help connecting via vbscript to sql 2005 express edition

Hi guys,
I need to connect to a newly created sql 2005 express edition database.
I tried to recycle the connection string used with sql 2000 server but
it doesn't work anymore:
dbConn.Connectionstring="DRIVER=SQL
Server;Trusted_Connection=Yes;DATABASE=" & sSQLDB & ";SERVER=" &
sSQLServer
database, ssqldb and ssqlserver are variables that correspond to
database names, table names, ecc.
IP and NamedPipes are enabled, so are remote connections (I'm actually
trying this from the same computer).
Any help ?Try specifying:
DRIVER={SQL Server};
Notice the parenthesis.
http://www.connectionstrings.com/
http://www.carlprothman.net/Default.aspx?tabid=81
<zerbie45@.gmail.com> wrote in message
news:1140536817.286087.242470@.g47g2000cwa.googlegroups.com...
> Hi guys,
> I need to connect to a newly created sql 2005 express edition database.
> I tried to recycle the connection string used with sql 2000 server but
> it doesn't work anymore:
> dbConn.Connectionstring="DRIVER=SQL
> Server;Trusted_Connection=Yes;DATABASE=" & sSQLDB & ";SERVER=" &
> sSQLServer
> database, ssqldb and ssqlserver are variables that correspond to
> database names, table names, ecc.
> IP and NamedPipes are enabled, so are remote connections (I'm actually
> trying this from the same computer).
> Any help ?
>|||Where are you trying to connect from? Are you sure the app code is running
as a windows authenticated user that has been granted access to the
database?
Also, I would use (more out of habit than anything):
"Provider=SQLOLEDB.1; Data Source=" & sSQLServer & "; Initial Catalog=" &
sSQLDB & "; Trusted_Connection=Yes;"
And I would try using SQL Authentication first, because you would have had
to do a lot more on purpose in order to get a trusted connection working.
<zerbie45@.gmail.com> wrote in message
news:1140536817.286087.242470@.g47g2000cwa.googlegroups.com...
> Hi guys,
> I need to connect to a newly created sql 2005 express edition database.
> I tried to recycle the connection string used with sql 2000 server but
> it doesn't work anymore:
> dbConn.Connectionstring="DRIVER=SQL
> Server;Trusted_Connection=Yes;DATABASE=" & sSQLDB & ";SERVER=" &
> sSQLServer
> database, ssqldb and ssqlserver are variables that correspond to
> database names, table names, ecc.
> IP and NamedPipes are enabled, so are remote connections (I'm actually
> trying this from the same computer).
> Any help ?
>|||Thanks for your answers. I'll give it a try tomorrow at work.
I'm actually trying to connect from the same machine where sql server
2005 express ed is installed. Sql server is using windows auth (I tried
configuring it to use both windows auth and sql auth). The user is
domain administrator, so it should not be a permission issue.
Thanks again for your attention.

Help connecting to SQLEXPRESS using asp.net

WE have just upgraded our database from sql 2000 to sql express.
Nowthe asp.net code doesn't work. I tried many different things, no
luck. Please help! thanks very much.
Here is the connection code:
conn = New SqlConnection( "Server=xxx.xxx.net,1433; UID=User;
PWD=lxxx2007;Database=mssql3" )
strSelect = " Select * from dbo.New_Broker_Table INNER JOIN
dbo.UserList ON dbo.New_Broker_Table.Broker_ID = dbo.UserList.Broker_ID WHERE dbo.UserList.User_ID =@.userID "
cmdSelect = New SqlCommand( strSelect, conn )
cmdSelect.Parameters.Add( "@.userID", session("userid") )
conn.Open()
dtrEmployee = cmdSelect.ExecuteReader( CommandBehavior.SingleRow )
i keep getting the SQL Server does not exist or access denied error.If you are trying to connect remotely, then the problem could be that SQL
Server Express installs by default with disabled remote connections. Here is
an article that outlines the steps to allow remote connections, as well as
setting up exception in the firewall and starting the SQL Server Browser
service, which could be the problem too:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
Next is to check the correct server instance. By default SQL Server Express
installs under a named instance SQLEXPRESS, so you may need to add that to
the Server parameter in your connection string. You can also remove the hard
coded port number in the connection string.
If all that fails then you can see some good connectivity troubleshooting
steps here:
http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Addition to Plamen's msg.
You receive a "SQL Server does not exist or access denied" error message
when you try to connect to a SQL Server named instance in a cluster by using
TCP/IP sockets:
http://support.microsoft.com/kb/888228/en-us
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
<lytung@.gmail.com> wrote in message
news:1193969000.898641.140040@.e9g2000prf.googlegroups.com...
> WE have just upgraded our database from sql 2000 to sql express.
> Nowthe asp.net code doesn't work. I tried many different things, no
> luck. Please help! thanks very much.
> Here is the connection code:
> conn = New SqlConnection( "Server=xxx.xxx.net,1433; UID=User;
> PWD=lxxx2007;Database=mssql3" )
> strSelect = " Select * from dbo.New_Broker_Table INNER JOIN
> dbo.UserList ON dbo.New_Broker_Table.Broker_ID => dbo.UserList.Broker_ID WHERE dbo.UserList.User_ID =@.userID "
> cmdSelect = New SqlCommand( strSelect, conn )
> cmdSelect.Parameters.Add( "@.userID", session("userid") )
> conn.Open()
> dtrEmployee = cmdSelect.ExecuteReader( CommandBehavior.SingleRow )
> i keep getting the SQL Server does not exist or access denied error.
>

Help connecting to SQL Database

Hi. I'm totally new here, and I'm not an expert when it comes to SQL, so bear with me. This is my dilemma:

I use Access 2000 to connect to my Microsoft SQL Server 2000 database. Recently, our provider switched us from a shared server to a dedicated server. The site is up and running perfectly, but I can't connect to my database via Access anymore. Once I received the new location of the database, I ran odbcad32.exe and configured the database's DSN info where appropriate. I run the "Test Data Source..." function and the tests are seemingly successful. When I open my database using Access, I'm able to log in successfully, but when I click on any one of my linked tables, I get the following error:

Could not execute query; could not find linked table.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'NAMEOFDATABASE.NAMEOFTABLE'. (#208)

The support team at my provider are not exactly geniuses, so who knows if and when they'll be getting back to me. In the meantime, does anyone have a clue what might be causing this? Since the site is working perfectly, I assume the connection strings are okay and the database is perfectly intact (I even checked to see that the most recent entries were live), so what gives? If there's any more info I can provide, let me know. Thanks in advance.This is a pain. You have to view the linked tables in design view. Right-click on the title bar and select properties. You can then change the connection string to what it needs to be. The connection string gets hard-coded. Isn't that nice. :)

It's usually easier to just drop the linked tables and recreate all of them.|||Thank you. I was able to open each table in design view, but when I right clicked on the title bar and selected propeterties, I wasn't able to change the connection string under Description. This is so weird to me, since I'm using the same version of Access I used to access the SQL database when it was on a different server. I'm also able to make the connection and open the database using SQL Query Tool, but that obviously doesn't help me when it comes to inputing data. How do I exactly drop the tables and recreate them? :(|||delete the linked tables in access (only the linked tables!!). this does NOT delete the real tables on your server, only the links to them.

...then relink to them in their new location.

izy

LATER: i mean delete the links to the linked tables... ie in A's database window / tables, use the DEL button on all the linked tables|||Thanks izyrider. Repulsion you are just deleting the links like he said.|||Thanks derrickleggett and izyrider for bearing with me. That was a big help and with a little experimenting I was able to delete the linked talbes in Access, relinked to them in their new location, and lo and behold: I don't get the error message anymore. I can see all my tables, but now I can't input, change or delete data. The User and Group Permissions function under Tools-->Secruity seems to be locked. Is there something I can do on my end to actually use my database now, or do I have to contact my provider to deal with permission settings?|||If it's a third-party database contact your provider.sql

Help connecting SQL

I have an application that connect to a local SQL server, but i need
to install the same application in another office outside of the
office. How can i make my application connect to my SQL server
remotely to make transactions?The app connects to your SQL Server using TCP/IP and some port number. If it is a default instance,
then the port is by default 1433. If it is a named instance, then you should look up what port
number the instance is using (pls post what version of SQL Server). So the app need to be able to
connect using this port, either by opening for the port in your firewall (NOT RECOMMENDED!!!) or
using VPN or similar.
Of course, you can re-architecture your app into either a n-tier app or by using HTTP endpoints
(assuming 2005), but I have a feeling that re-writing your app isn't an option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Macana" <leninarias@.gmail.com> wrote in message
news:14ead399-dab5-4826-95c4-c280f8a485f3@.l32g2000hse.googlegroups.com...
>I have an application that connect to a local SQL server, but i need
> to install the same application in another office outside of the
> office. How can i make my application connect to my SQL server
> remotely to make transactions?

Help connecting pages which has Database connection.

I am a completely new asp.net / SQL server user.
I've been only working with PHP + MYSQL server before which is very different.

There are databases which come with the SQLserver, such as "master"
and there are databases which i create by my own.

When using "master" database, everything works fine.
here's a screenshot.
http://img528.imageshack.us/img528/57/d1if7.jpg

but when using database which i created by my own, i get an error
whether i use ip or localhost.
http://img513.imageshack.us/img513/4598/d2iz7.jpg

only when i use the debugger, it gives me some port, then i am able to connect but only using localhost... therefore i can't give the adress to anyone else.
http://img528.imageshack.us/img528/3394/d3jn6.jpg

What's the difference between the Built in databases and those i create?
how can i make that i could access pages which has database connection to databases i created by my own??

Sorry for my English, i know it's not really understandable.Anyone? it seems like something which everyone who uses asp.net with sql server for a while should know.
ty.

Help connecting ole/db linked server to msaccess database in a different machine than sql server

Hi,

I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.

Thank's for all the help/clues you can give me.

Hi!

This is usually a permissions issue. The account that the SQL Server runs under needs to have NT permissions to the directory where the MDB is stored.

|||

Thank's for your help Cindy, but the case is that I can't figure out what to do to solve the problem.

I am sorry but I don't know exactly what do you mean by "the account that SQL Server runs".

The users that are running the sp that reports the error all have rights in the local network directory where the mdb is stored.

Could you please be kind enough to continue helping me on this?

|||Running SQL Server Service needs a service account to run with. The account can be determined using e.g. the Service Control manager. The access for file access is impersonated using the service account which is running SQL Server. If this has no access to the UNC share (e.g. System Account) you won′t be able to access the file / establish a link to the "server" / Access database. In addition to the explanation you should not use a mapped drive letter always use UNC paths working with SQL Server. it cannot be guranteed that the account which is running the process has also the mapped drived letter assigned to his profile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The service account SQL Server is using is the network login account of the users.
All of them have access to the network drive that is referenced in the linked server by the UNC path.

This is the code I used to set the linked server:

USE master
GO
EXEC sp_addlinkedserver
@.server = 'CRED85', -- Name of the linked Server
@.provider = 'Microsoft.Jet.OLEDB.4.0', -- Access Provider
@.srvproduct = 'dbaccess', -- may be anything
@.datasrc = '\\Gcxncliflsv301h\Aplic\AplicDep\DAI\APLIC\Produtos\Cred85.mdb' -- UNC path + Access db name
GO

and after that:

exec sp_addlinkedsrvlogin 'dbaccess', false, 'GRUPOCGD\c008645', 'Admin', NULL

So, my question remains:
- Why does it work only when the front end is executed in the machine where resides the server (with any user)
and NOT in any other machine? What's wrong?

Help connecting ole/db linked server to msaccess database in a different machine than sql server

Hi,

I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.

Thank's for all the help/clues you can give me.

Hi!

This is usually a permissions issue. The account that the SQL Server runs under needs to have NT permissions to the directory where the MDB is stored.

|||

Thank's for your help Cindy, but the case is that I can't figure out what to do to solve the problem.

I am sorry but I don't know exactly what do you mean by "the account that SQL Server runs".

The users that are running the sp that reports the error all have rights in the local network directory where the mdb is stored.

Could you please be kind enough to continue helping me on this?

|||Running SQL Server Service needs a service account to run with. The account can be determined using e.g. the Service Control manager. The access for file access is impersonated using the service account which is running SQL Server. If this has no access to the UNC share (e.g. System Account) you won′t be able to access the file / establish a link to the "server" / Access database. In addition to the explanation you should not use a mapped drive letter always use UNC paths working with SQL Server. it cannot be guranteed that the account which is running the process has also the mapped drived letter assigned to his profile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The service account SQL Server is using is the network login account of the users.
All of them have access to the network drive that is referenced in the linked server by the UNC path.

This is the code I used to set the linked server:

USE master
GO
EXEC sp_addlinkedserver
@.server = 'CRED85', -- Name of the linked Server
@.provider = 'Microsoft.Jet.OLEDB.4.0', -- Access Provider
@.srvproduct = 'dbaccess', -- may be anything
@.datasrc = '\\Gcxncliflsv301h\Aplic\AplicDep\DAI\APLIC\Produtos\Cred85.mdb' -- UNC path + Access db name
GO

and after that:

exec sp_addlinkedsrvlogin 'dbaccess', false, 'GRUPOCGD\c008645', 'Admin', NULL

So, my question remains:
- Why does it work only when the front end is executed in the machine where resides the server (with any user)
and NOT in any other machine? What's wrong?

HELP - Connecting SQL Express Edition

I have two machines and same problem.
I installed SQL EE on XP HOME EDITION it is OK !
Localy work OK !
I try to connect from another machine with SQL Server Management Studio and
I can't.
I some how successed to connect with named pipes but TCP/IP is not working.
I turn on all protocols with Menager. Shout down firewall on both machines.
I tried with SQL Native Client from code but nothing.
For now anly named pipes working.
What is wrong ?Have a look at these:
http://www.datamasker.com/SSE2005_NetworkCfg.htm Configuring Express
for Remote Access
https://blogs.msdn.com/sqlexpress/a.../05/415084.aspx
Configuring Remote Access
http://www.kbalertz.com/Feedback_914277.aspx Configuring SQL2005 for
remote access
http://msdn.microsoft.com/library/d...r />
rview.asp
Express Overview
http://msdn2.microsoft.com/en-us/library/ms165672.aspx Comparing Express
with MSDE
http://msdn.microsoft.com/sql/defau... />
erinst.asp
User Instances
http://msdn.microsoft.com/sql/expre...qlexcustapp.asp
Embedding Express in Apps
http://blogs.msdn.com/rogerwolterbl.../13/575974.aspx
Backing up Express
Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
Andrew J. Kelly SQL MVP
"Soko" <sokcic@.hotmail.com> wrote in message
news:eQqmarSiGHA.4660@.TK2MSFTNGP03.phx.gbl...
>I have two machines and same problem.
> I installed SQL EE on XP HOME EDITION it is OK !
> Localy work OK !
> I try to connect from another machine with SQL Server Management Studio
> and I can't.
> I some how successed to connect with named pipes but TCP/IP is not
> working.
> I turn on all protocols with Menager. Shout down firewall on both
> machines.
> I tried with SQL Native Client from code but nothing.
> For now anly named pipes working.
> What is wrong ?
>|||Hi Soko,
I have a very similar (if not the same problem). Have you actually solve it?
Thanks
Zib
From http://www.developmentnow.com/g/100...ess-Edition.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com|||SQL Server Express installs as a named instance. Unless you change it the
instance is called <computername>\SQLExpress.
The SQL Server Browser service resolves the name, to the TCP/IP port number.
I suspect the SQL Server Browser service is not running on your computer. If
you start it, then you may be able to connect.
"Soko" <sokcic@.hotmail.com> wrote in message
news:eQqmarSiGHA.4660@.TK2MSFTNGP03.phx.gbl...
>I have two machines and same problem.
> I installed SQL EE on XP HOME EDITION it is OK !
> Localy work OK !
> I try to connect from another machine with SQL Server Management Studio
> and I can't.
> I some how successed to connect with named pipes but TCP/IP is not
> working.
> I turn on all protocols with Menager. Shout down firewall on both
> machines.
> I tried with SQL Native Client from code but nothing.
> For now anly named pipes working.
> What is wrong ?
>