connecting to sql server 2000 from sql server 2005 management studio

My web site is hosted by a third party, and the back-end is a sql server 2000 database. I was routinely running queries over the internet against the database using Query Analyzer. I just installed sql server 2005. Is it possible to run a sql query against a table in the sql server 2000 database from within sql server 2005 management studio? I haven't found anything in the docs explaining how to connect to sql server 2000. Any help would be appreciated.

You can just connect to the SQL Server 200 as you connected before and are connecting now with your Business Management Studio. Its downwards compatible.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Hi there,

If all you want to do is access your SQL Server 2000 database using SQL Server 2005 Management Studio then you just need to add another connection in your Object Explorer. I believe the steps are:

1) Go File > Connect Object Explorer (NB. alternately you can click on the "Connect Object Explorer" button in the Object Explorer window

2) A dialog will appear where you specify the server name, auth type (SQL Server, Windows), username and password.

Enter the server name (should be the same as the name of the server you connect to in Query Analyzer) and other details as appropriate (the same config you used in Query Analyzer should apply here).

3) A node for your SQL Server 2000 server should appear in the Object Explorer. To query against the database, an easy way is to click on the database (you'll need to expand a few nodes in Obejct Explorer) and then click the "New Query" button (or go File > New > Query With Current Connection).

If your intent is to have your SQL Server 2005 database and define queries for that database that reference the SQL Server 2000 database then you'll need to define a linked server from the SQL Server 2005 instance to the SQL Server 2000 instance. There should be details on how to do this in Books Online.

Hope that helps a bit but sorry if it doesn't & I've missed the mark completely.

Connecting to SQL Server & XP Service Pack 2

I installed XP Service Pack 2 on my desktop machine which had SQL Server 2000
running on it prior to the install. I have a C# web application that runs
against a local SQL Server database which I cannot get to work now that I
have installed the XP service pack. I have read all of the Microsoft "HOW To"
documents about configuring SQL Server for the Service Pack but I still
cannot get the Web Appliation to communicate with the database. I always get
the "SQL Server does not exist or access denied." error message. I have tried
the following:
1. Added Port 1433 (TCP/IP) to the Firewall Exception List (1433 is the
static port SQL Server is using)
2. Added Port 445 (TCP/IP) to the Firewall Exception List
3. Added the sqlserver.exe file to the Firewall Exception List
The database connection string I am using in the web application is:
server=local;uid=sa;pwd=<password>;database=Research
At this point I am not sure what else to try. I cannot connect to the
database if the Firewall is turned off. Should this be the case? Any
assistance is much appreciated.
Sincerely,
Tom HessenHi Tom,
I had the same problem and decided to look at the other end of the
connection. I was running SQL Server ( Service Pack 2 ). When I upgraded
to SQL Server ( Service Pack 3a ) everything started working again.
"Tom Hessen" <Tom Hessen@.discussions.microsoft.com> wrote in message news:<645A76F5-D701-4411-9720-1C6AC67A7ED5@.microsoft.com>...
> I installed XP Service Pack 2 on my desktop machine which had SQL Server 2000
> running on it prior to the install. I have a C# web application that runs
> against a local SQL Server database which I cannot get to work now that I
> have installed the XP service pack. I have read all of the Microsoft "HOW To"
> documents about configuring SQL Server for the Service Pack but I still
> cannot get the Web Appliation to communicate with the database. I always get
> the "SQL Server does not exist or access denied." error message. I have tried
> the following:
> 1. Added Port 1433 (TCP/IP) to the Firewall Exception List (1433 is the
> static port SQL Server is using)
> 2. Added Port 445 (TCP/IP) to the Firewall Exception List
> 3. Added the sqlserver.exe file to the Firewall Exception List
> The database connection string I am using in the web application is:
> server=local;uid=sa;pwd=<password>;database=Research
> At this point I am not sure what else to try. I cannot connect to the
> database if the Firewall is turned off. Should this be the case? Any
> assistance is much appreciated.
> Sincerely,
> Tom Hessen|||Hi Tom,
I've had a little trouble posting, but hopefully this will get through.
I also did everything you listed below. It did not work for me
either ( for a java client ).
I then decided to look at the other end of the connection. I was running
SQLServer ( Service Pack 2 ). When I upgraded to SQLServer ( Service Pack
3a ) everything started working again.
Hope this helps,
Mike S.
"Tom Hessen" <Tom Hessen@.discussions.microsoft.com> wrote in message news:<645A76F5-D701-4411-9720-1C6AC67A7ED5@.microsoft.com>...
> I installed XP Service Pack 2 on my desktop machine which had SQL Server 2000
> running on it prior to the install. I have a C# web application that runs
> against a local SQL Server database which I cannot get to work now that I
> have installed the XP service pack. I have read all of the Microsoft "HOW To"
> documents about configuring SQL Server for the Service Pack but I still
> cannot get the Web Appliation to communicate with the database. I always get
> the "SQL Server does not exist or access denied." error message. I have tried
> the following:
> 1. Added Port 1433 (TCP/IP) to the Firewall Exception List (1433 is the
> static port SQL Server is using)
> 2. Added Port 445 (TCP/IP) to the Firewall Exception List
> 3. Added the sqlserver.exe file to the Firewall Exception List
> The database connection string I am using in the web application is:
> server=local;uid=sa;pwd=<password>;database=Research
> At this point I am not sure what else to try. I cannot connect to the
> database if the Firewall is turned off. Should this be the case? Any
> assistance is much appreciated.
> Sincerely,
> Tom Hessen

Connecting to SQL database

Hi all,

Please help me with the following problem because I am running around in circles.

I have this problem getting a forum online.

Since it is a database related problem which I have been trying to solve at the ClubSite forum I am taking the liberty to come to this forum with this problem.

This is the story:

I have this separate SQL databasedotForum2004.mdf in App_data.

After trial and error got it working locally.

But when I put it online at my webserver (On a Stand-alone PC at my jobsite) I get an application error.(From the Club Site application)

This is the connection key which works fine locally. Is there something obvious you can point me at?

<addkey="forumDSN"value="Provider=SQLOLEDB; Server=.\SQLExpress;AttachDbFilename=|DataDirectory|dotForum2004.mdf;Trusted_Connection=Yes;" />

Thanks in advance,

Lex

Hi Lexy,

From you connection string I notice you use Windows Authentication (Trusted_Connection=Yes) to connect to database. And as I know when the project is deployed to website, it will run under an account (maybe called 'NT AUTHROTY\NETWORK SERVICE' or something like this, sorry I'm not sure) rather then under current Windows Login account. So you can check the connection error message, is it something like this?

Login failed for 'XXXXXXX'

Where 'XXXXXX' stands for an account. Then check the logins in you SQL Server instace (it is '.\SQLExpress' in your application). Add the account to SQL logins and give it proper permission to access database objects; or you can add it to 'sysadmin' role so that it can access all database objects.

|||

Hi lori_Jay,

Thanks for your response,

I put <customErrors mode="Off"/> in my web.config and now at least I see the error. (see below on this post)

But I'm afraid you lost me with the part:

Then check the logins in you SQL Server instace (it is '.\SQLExpress' in your application). Add the account to SQL logins and give it proper permission to access database objects; or you can add it to 'sysadmin' role so that it can access all database objects.

Got no idea where in the application i can check the logins like you suggest. Let alone the rest of your advice.

Lex

CREATE DATABASE permission denied in database 'master'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.OleDb.OleDbException: CREATE DATABASE permission denied in database 'master'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[OleDbException (0x80004005): CREATE DATABASE permission denied in database 'master'.] System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) +1054817 System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +53 System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.OleDb.OleDbConnection.Open() +37 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +86 dotForumClientTopic.viewAllRecords.bindData() +169 dotForumClientTopic.viewAllRecords.Page_Load(Object sender, EventArgs e) +32 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

|||

Sorry for misunderstanding^_^

Anyways we got closer to the cause. From the error message we can see the problem is the Login used in applcation connection did not have sufficient permission to CREATE DATABASE; whileCREATE DATABASE permission defaults to members of thesysadmin anddbcreator fixed server roles. So let's try to add the 'NT AUTHORITY\NETWORK SERVICE' (or 'ASPNET', something like these which used by IIS when connecting to SQL with Windows Authentication) to sysadmin role. You can do this simply in Management Studio->MyInstance->Security->Logins; or if you do not have Management Studio, just try to run this T-SQL command:

EXEC sp_addsrvrolemember 'NT AUTHORITY\NETWORK SERVICE' ,'sysadmin'

|||

Hi,

You just made me a happy man!

If you new how long this has been bugging me.

Adding Sysadmin role to <Servername>\ASPNET did the trick.

Now at last I can get on with all the other Forum problems.

Thanks again,

Lex

|||

Hi again,

Now the Forum is finally online I am trying to evaluate a number of things so I get the optimal result in learning from the problem you solved.

Is there a preferred way for Authentication.to SQLserver /express. Would it be better to go for SQL Server Authentication?

--------

I also had to addDatabase=dbname

Old:<addkey="forumDSN"value="Provider=SQLOLEDB; Server=.\SQLExpress;AttachDbFilename=|DataDirectory|dotForum2004.mdf;Trusted_Connection=Yes;" />

New: <

addkey="forumDSN"value="Provider=SQLOLEDB; Server=.\SQLExpress;AttachDbFilename=|DataDirectory|dotForum2004.mdf;Database=dbname;Trusted_Connection=Yes;Language=Dutch" />

Somehow during my various attempts (of trial and error) I must have created this database.

I tried to change that part of the key in Database=Forum

Locally a database with the name Forum appears in Management Studio, but I get an error when I try it on the webserver. I thought again this could be solved with security, so I checked.

All roles (including the ones you advised have dbcreator rights) Was this Database dbname then created manuelly at some time, or am i overlooking something else?

Thanks in advance,

Lex

Connecting to SQL 2005 server on Windows 2003

Hope this is on the correct forum, if not could the admins move please.

Im running Win XP SP2 Pro + All updates, Visual Studio .Net 2005 and SQL 2005. SQL is running on Win 2003 server. When i try to create a connection on the WinXP system using ODBC i get the following error:

Connection Failed:
SQLSTATE: '01000'
SQL Server Error: 233
[Microsoft] [ODBC SQL server driver][DBNETLIB]Connection open (PreLoginHandshake())
Connection failed:
SQLState: '08001'
SQL Server error: 11
[Microsoft] [ODBC SQL server driver][DBNETLIB]General Network error. Chcek your network documentation.

Ive searched about the error but nothing seems to work - can anyone help to resolve please?

Thanks in advance.

1) Are you making remote connection? If you have SQL 2005 installed on your XP client box, can you try Management studio to connect to remote server? what error you see?

2) Did you install SQL Express 2005 and make remote connection?If so, check this blog:

http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

But I do not think this is the case.

3) more GNE error troubleshooting blog:

http://blogs.msdn.com/sql_protocols/archive/2006/04/12/574608.aspx

Might give you some clue.

Summary, to fix your problem, please:

check our anouncement posted by Nan Tu, provided the answer to the questions for troubleshootin connectivity issue.

Thanks!

Ming.

Connecting to Server Fails

I'm running SQL Server 2005 RTM on my laptop (Dell Latitude D810) but
cannot connect to Reporting Services Server. When I try to connect to
it using SQL Server Management Studio (using windows authentication) I
get the following Error message...
TITLE: Connect to Server
--
Cannot connect to {ServerName}.
--
ADDITIONAL INFORMATION:
The attempt to connect to the report server failed. Check your
connection information and that the report server is a compatible
version. (Microsoft.SqlServer.Management.UI.RSClient)
--
The request failed with HTTP status 404: Object Not Found.
(Microsoft.SqlServer.Management.UI.RSClient)
I have visual studio 2005 on the machine (which I installed after the
SQL Server install) and have .NET Framework version 2.0.50727.42.
The OS is windows XP Professional.
Anyone know what I will need to do to connect to the server?Simon Worth wrote:
> I'm running SQL Server 2005 RTM on my laptop (Dell Latitude D810) but
> cannot connect to Reporting Services Server. When I try to connect to
> it using SQL Server Management Studio (using windows authentication) I
> get the following Error message...
> TITLE: Connect to Server
> --
> Cannot connect to {ServerName}.
> --
> ADDITIONAL INFORMATION:
> The attempt to connect to the report server failed. Check your
> connection information and that the report server is a compatible
> version. (Microsoft.SqlServer.Management.UI.RSClient)
> --
> The request failed with HTTP status 404: Object Not Found.
> (Microsoft.SqlServer.Management.UI.RSClient)
> I have visual studio 2005 on the machine (which I installed after the
> SQL Server install) and have .NET Framework version 2.0.50727.42.
> The OS is windows XP Professional.
> Anyone know what I will need to do to connect to the server?
I'm still having issues with this - if anyone can offer any insight it
would still be greatly appreciated.|||Hi,
I am also having the same problem,
and it seems that at least a few people in this newsgroup are having the
same problem.
Somebody please help us with this!|||I resolved the issue, at least for my machine. My IIS is ver. 5.1, and the
URLScan did not allow POST verb. Once I listed POST as an allowed verb in
URLScan.ini, I could successfully connect to the report manager.
For anybody having problem, as below, I suggest you check your URLScan
settings.
The problems are :
1) When you navigate to http://<localhost>/<reports>, you get, "The attempt
to connect to the report server failed. Check your connection information
and that the report server is a compatible version.
(Microsoft.SqlServer.Management.UI.RSClient)".
2) You cannot connect to your reporting services instance through the
Management Studio.

Connecting to remote Express instance using SSMS

I have a server running SQL Server 2005 Ent. Ed. SP2 and locally, I can see
the default instance and two SQLExpress instances named 'sqlexpress' and
'officeservers', all withing SSMS, and can connect and manage them.
From my workstation, running SQL 2005 Dev, SP2, I can connect to the default
instance on that server, but cannot connect to the express instances. They
both have TCP/IP protocol enabled, along with "Allow Remote COnnections".
I have done the following:
Made myself a member of the local Admin group
Tried using the sa account to connect
nothing seems to work. What am I missing?
TIA
Todd Chittenden
Is the SQLBrowser Service enabled and started?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:15C51BF6-FF25-435E-B26B-3863344FB81D@.microsoft.com...
>I have a server running SQL Server 2005 Ent. Ed. SP2 and locally, I can see
> the default instance and two SQLExpress instances named 'sqlexpress' and
> 'officeservers', all withing SSMS, and can connect and manage them.
> From my workstation, running SQL 2005 Dev, SP2, I can connect to the
> default
> instance on that server, but cannot connect to the express instances. They
> both have TCP/IP protocol enabled, along with "Allow Remote COnnections".
> I have done the following:
> Made myself a member of the local Admin group
> Tried using the sa account to connect
> nothing seems to work. What am I missing?
> TIA
> --
> Todd Chittenden
>
|||I found it not long after posting the original question:
No, the Browser service was not running. Starting that up enabled me to
connect to it. So that was the answer.
But even before the Enterprise instance was installed, (which includes the
browser service) I was not able to connect. I know I have other machines in
my domain that are running SQL Express. Should I not be able to connect to
them using my SSMS? What do I need to do to make that happen?
I assume that I need membership in the group on the local machine that bears
the SQL Express instance name?
What else? I can't rely on the Browser service in those instances.
Thanks
Todd Chittenden
"Roger Wolter[MSFT]" wrote:

> Is the SQLBrowser Service enabled and started?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> news:15C51BF6-FF25-435E-B26B-3863344FB81D@.microsoft.com...
>
|||SQL Express by default installs as a named instance. Unless you specify the
TCP/IP port in the connection string, you need the sqlbrowser service to
connect to a named instance.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:F4B981A2-4B9A-443E-855B-D53B89FCE511@.microsoft.com...[vbcol=seagreen]
>I found it not long after posting the original question:
> No, the Browser service was not running. Starting that up enabled me to
> connect to it. So that was the answer.
> But even before the Enterprise instance was installed, (which includes the
> browser service) I was not able to connect. I know I have other machines
> in
> my domain that are running SQL Express. Should I not be able to connect to
> them using my SSMS? What do I need to do to make that happen?
> I assume that I need membership in the group on the local machine that
> bears
> the SQL Express instance name?
> What else? I can't rely on the Browser service in those instances.
> Thanks
> --
> Todd Chittenden
>
> "Roger Wolter[MSFT]" wrote:

Connecting to remote Express instance using SSMS

I have a server running SQL Server 2005 Ent. Ed. SP2 and locally, I can see
the default instance and two SQLExpress instances named 'sqlexpress' and
'officeservers', all withing SSMS, and can connect and manage them.
From my workstation, running SQL 2005 Dev, SP2, I can connect to the default
instance on that server, but cannot connect to the express instances. They
both have TCP/IP protocol enabled, along with "Allow Remote COnnections".
I have done the following:
Made myself a member of the local Admin group
Tried using the sa account to connect
nothing seems to work. What am I missing?
TIA
--
Todd ChittendenIs the SQLBrowser Service enabled and started?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:15C51BF6-FF25-435E-B26B-3863344FB81D@.microsoft.com...
>I have a server running SQL Server 2005 Ent. Ed. SP2 and locally, I can see
> the default instance and two SQLExpress instances named 'sqlexpress' and
> 'officeservers', all withing SSMS, and can connect and manage them.
> From my workstation, running SQL 2005 Dev, SP2, I can connect to the
> default
> instance on that server, but cannot connect to the express instances. They
> both have TCP/IP protocol enabled, along with "Allow Remote COnnections".
> I have done the following:
> Made myself a member of the local Admin group
> Tried using the sa account to connect
> nothing seems to work. What am I missing?
> TIA
> --
> Todd Chittenden
>|||I found it not long after posting the original question:
No, the Browser service was not running. Starting that up enabled me to
connect to it. So that was the answer.
But even before the Enterprise instance was installed, (which includes the
browser service) I was not able to connect. I know I have other machines in
my domain that are running SQL Express. Should I not be able to connect to
them using my SSMS? What do I need to do to make that happen?
I assume that I need membership in the group on the local machine that bears
the SQL Express instance name?
What else? I can't rely on the Browser service in those instances.
Thanks
--
Todd Chittenden
"Roger Wolter[MSFT]" wrote:

> Is the SQLBrowser Service enabled and started?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> news:15C51BF6-FF25-435E-B26B-3863344FB81D@.microsoft.com...
>|||SQL Express by default installs as a named instance. Unless you specify the
TCP/IP port in the connection string, you need the sqlbrowser service to
connect to a named instance.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:F4B981A2-4B9A-443E-855B-D53B89FCE511@.microsoft.com...[vbcol=seagreen]
>I found it not long after posting the original question:
> No, the Browser service was not running. Starting that up enabled me to
> connect to it. So that was the answer.
> But even before the Enterprise instance was installed, (which includes the
> browser service) I was not able to connect. I know I have other machines
> in
> my domain that are running SQL Express. Should I not be able to connect to
> them using my SSMS? What do I need to do to make that happen?
> I assume that I need membership in the group on the local machine that
> bears
> the SQL Express instance name?
> What else? I can't rely on the Browser service in those instances.
> Thanks
> --
> Todd Chittenden
>
> "Roger Wolter[MSFT]" wrote:
>

Connecting to ODBC Connections with Report Services

I have just migrated from SQL 2000 to SQL 2005 and in the process upgraded to new hardware. I am now running SQL2005 (64 Bit) on Windows 2003 R2 (64 Bit). The problem is that when i deploy some reports to the new server that use ODBC to connect to the data, reporting services is erroring with :

Data source name not found and no default driver specified

The ODBC connections are set up exactly the same on the old server and the new server. Is it because it is looking for a 64 bit ODBC driver and not the 32 Bit one I have installed. If i go into the SysWOW64 and run the odbcad32.exe i can see the drivers and the connection. The connection test works fine from here.

Thanks

Has anyone got any ideas on this ?|||Did you find a resolution? I have the same problem.|||I am waiting for a company called Openlink to get back to me they are currently developing some drivers|||Thanks. My client is switching to 32-bit software to resolve this. Good luck.|||

Did anyone get this resolved? I am having the same problem. I fear I may have to revert back to the 32-Bit OS.

Any advice or suggestion are welcomed.

Thanks,

Andy

Connecting to ODBC Connections with Report Services

I have just migrated from SQL 2000 to SQL 2005 and in the process upgraded to new hardware. I am now running SQL2005 (64 Bit) on Windows 2003 R2 (64 Bit). The problem is that when i deploy some reports to the new server that use ODBC to connect to the data, reporting services is erroring with :

Data source name not found and no default driver specified

The ODBC connections are set up exactly the same on the old server and the new server. Is it because it is looking for a 64 bit ODBC driver and not the 32 Bit one I have installed. If i go into the SysWOW64 and run the odbcad32.exe i can see the drivers and the connection. The connection test works fine from here.

Thanks

Has anyone got any ideas on this ?|||Did you find a resolution? I have the same problem.|||I am waiting for a company called Openlink to get back to me they are currently developing some drivers|||Thanks. My client is switching to 32-bit software to resolve this. Good luck.|||

Did anyone get this resolved? I am having the same problem. I fear I may have to revert back to the 32-Bit OS.

Any advice or suggestion are welcomed.

Thanks,

Andy

Connecting to ODBC Connections with Report Services

I have just migrated from SQL 2000 to SQL 2005 and in the process upgraded to new hardware. I am now running SQL2005 (64 Bit) on Windows 2003 R2 (64 Bit). The problem is that when i deploy some reports to the new server that use ODBC to connect to the data, reporting services is erroring with :

Data source name not found and no default driver specified

The ODBC connections are set up exactly the same on the old server and the new server. Is it because it is looking for a 64 bit ODBC driver and not the 32 Bit one I have installed. If i go into the SysWOW64 and run the odbcad32.exe i can see the drivers and the connection. The connection test works fine from here.

Thanks

Has anyone got any ideas on this ?|||Did you find a resolution? I have the same problem.|||I am waiting for a company called Openlink to get back to me they are currently developing some drivers|||Thanks. My client is switching to 32-bit software to resolve this. Good luck.|||

Did anyone get this resolved? I am having the same problem. I fear I may have to revert back to the 32-Bit OS.

Any advice or suggestion are welcomed.

Thanks,

Andy

Connecting to multiple database

Hi, i'm a novice in all this database transactions stuff so please bear with me if my input is incorrect or out dated. I'm running into the problem of needing to compare or manipulate data that is in 2 tables in 2 separate database. Is there any way i can set up the connection string such that i can access data/tables from both database at the same time in 1 sql command (similar to accessing 2 tables of the same database in 1 sql command)?

Is this currently possible? If not, is it because the execution of sql command must be tied to only one connection string at each instance? Would be great if the wonderful folks at MICROSOFT can look into this and provide us with an upgrade to cater for such a need...

Regards

Oceandude

If the database are within the same server you can several methods, all not doing it in one command (as this not possible) but within transactions:

In a SQL Batch

BEGIN TRANSACTION

UPDATE SomeOwner.SOMETable SET SomeCol = 1

UPDATE SomeDatabase.SomeOwner.SOMETable SET SomeCol = 1

COMMIT

You can use a transaction scope to elevate the transaction to a distributed (crossdatabase transaction), but that depends on the coding language you are using the Transaction scope would be a sample for .NET.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||You can also create a synonym (if your server is SQL 2005) in one database for a table in the other.
See CREATE SYNONYM.|||

What Is your goal? You could open multiple SQL Commands or you could do any of the above suggestions, or your could define a stored process on the server that does the bulk of the work. There are many options depending on what you really want to do here.

Connecting to multiple database

Hi, i'm a novice in all this database transactions stuff so please bear with me if my input is incorrect or out dated. I'm running into the problem of needing to compare or manipulate data that is in 2 tables in 2 separate database. Is there any way i can set up the connection string such that i can access data/tables from both database at the same time in 1 sql command (similar to accessing 2 tables of the same database in 1 sql command)?

Is this currently possible? If not, is it because the execution of sql command must be tied to only one connection string at each instance? Would be great if the wonderful folks at MICROSOFT can look into this and provide us with an upgrade to cater for such a need...

Regards

Oceandude

If the database are within the same server you can several methods, all not doing it in one command (as this not possible) but within transactions:

In a SQL Batch

BEGIN TRANSACTION

UPDATE SomeOwner.SOMETable SET SomeCol = 1

UPDATE SomeDatabase.SomeOwner.SOMETable SET SomeCol = 1

COMMIT

You can use a transaction scope to elevate the transaction to a distributed (crossdatabase transaction), but that depends on the coding language you are using the Transaction scope would be a sample for .NET.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||You can also create a synonym (if your server is SQL 2005) in one database for a table in the other.
See CREATE SYNONYM.|||

What Is your goal? You could open multiple SQL Commands or you could do any of the above suggestions, or your could define a stored process on the server that does the bulk of the work. There are many options depending on what you really want to do here.

Connecting to multiple database

Hi, i'm a novice in all this database transactions stuff so please bear with me if my input is incorrect or out dated. I'm running into the problem of needing to compare or manipulate data that is in 2 tables in 2 separate database. Is there any way i can set up the connection string such that i can access data/tables from both database at the same time in 1 sql command (similar to accessing 2 tables of the same database in 1 sql command)?

Is this currently possible? If not, is it because the execution of sql command must be tied to only one connection string at each instance? Would be great if the wonderful folks at MICROSOFT can look into this and provide us with an upgrade to cater for such a need...

Regards

Oceandude

If the database are within the same server you can several methods, all not doing it in one command (as this not possible) but within transactions:

In a SQL Batch

BEGIN TRANSACTION

UPDATE SomeOwner.SOMETable SET SomeCol = 1

UPDATE SomeDatabase.SomeOwner.SOMETable SET SomeCol = 1

COMMIT

You can use a transaction scope to elevate the transaction to a distributed (crossdatabase transaction), but that depends on the coding language you are using the Transaction scope would be a sample for .NET.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||You can also create a synonym (if your server is SQL 2005) in one database for a table in the other.
See CREATE SYNONYM.|||

What Is your goal? You could open multiple SQL Commands or you could do any of the above suggestions, or your could define a stored process on the server that does the bulk of the work. There are many options depending on what you really want to do here.

connecting to MSDE

I installed MSDE 200 on my machine running XP home
edition.
I cant connect to the db using the SQL mode, but i can
connect using the in windows modes. It returns and odbc
error stae 28000 , i dont know how to correct this.
If set up and odbc connection and try to test this, it
returns the following errore
Attempting connection
[Microsoft][ODBC SQL Server Driver][SQL Server]Login
failed for user 'sa'. Reason: Not associated with a
trusted SQL Server connection.It looks like your MSDE instance is configured for Windows
Authentication only. The following article explains how to
change the authentication mode for MSDE during installation
as well as after installation:
INF: How to Change the Default Login Authentication Mode to
SQL While Installing SQL Server 2000 Desktop Engine by Using
Windows Installer
http://support.microsoft.com/?id=285097
-Sue
On Wed, 25 Feb 2004 19:31:09 -0800, "Lanre"
<lanre_makinde@.yahoo.com_remove.this> wrote:

>I installed MSDE 200 on my machine running XP home
>edition.
>I cant connect to the db using the SQL mode, but i can
>connect using the in windows modes. It returns and odbc
>error stae 28000 , i dont know how to correct this.
>If set up and odbc connection and try to test this, it
>returns the following errore
>Attempting connection
>[Microsoft][ODBC SQL Server Driver][SQL Server]Login
>failed for user 'sa'. Reason: Not associated with a
>trusted SQL Server connection.

Connecting to more Database

Hi ,

We have 10 web sites running and having 10 database stored in same server .
i need to check in all the 10 database that particular id has repated .
if it is repated i should not allow the user the create that id .

as it is we have 100k qry hitting the database per minute .. so have some performace issues .

if i write code to connect all the database is same file . will there be any performance issue .

or is there any other method to do that ...

Quote:

Originally Posted by rashmiks

Hi ,

We have 10 web sites running and having 10 database stored in same server .
i need to check in all the 10 database that particular id has repated .
if it is repated i should not allow the user the create that id .

as it is we have 100k qry hitting the database per minute .. so have some performace issues .

if i write code to connect all the database is same file . will there be any performance issue .

or is there any other method to do that ...

Hi there,

Kindly refer to below attached link. Please do not double post. Take care

Trigger

Connecting to local instance with firewall client enabled

I'm running SQL Server 2000 SP3 on my XP Pro laptop and everything
works fine without firewall client enabled. When I VPN in to the
network and enable the ISA server firewall client, I can't connect to
my local instance using Enterprise Manager or Query Analyzer, although
I can connect to another instance running on a server on the network.
If I'm VPNed in and I don't enable firewall client, I can still
connect to the local instance. And interestingly my ASP.NET
application (running locally) can still connect to the local SQL
Server instance once I changed the server name in web.config from
localhost to chris-laptop.
Any help would be greatly appreciated, as I really need to have
firewall client enabled and access to my databases through query
analyser at the same time.
Thanks
Chris BettertonIf you specifiy the ip address that SQL Server is listening on when
attempting to connect via Query Analyzer, does it allow you to connect?
If it does then you can use the SQL CLient Network Utility to create an
alias that points the server name to the ip address. This would let you
connect using the server name.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hadn't thought of that, 127.0.0.1 works fine so I can use the alias
approach. Thanks for your help.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Connecting to local instance with firewall client enabled

I'm running SQL Server 2000 SP3 on my XP Pro laptop and everything
works fine without firewall client enabled. When I VPN in to the
network and enable the ISA server firewall client, I can't connect to
my local instance using Enterprise Manager or Query Analyzer, although
I can connect to another instance running on a server on the network.
If I'm VPNed in and I don't enable firewall client, I can still
connect to the local instance. And interestingly my ASP.NET
application (running locally) can still connect to the local SQL
Server instance once I changed the server name in web.config from
localhost to chris-laptop.
Any help would be greatly appreciated, as I really need to have
firewall client enabled and access to my databases through query
analyser at the same time.
Thanks
Chris Betterton
If you specifiy the ip address that SQL Server is listening on when
attempting to connect via Query Analyzer, does it allow you to connect?
If it does then you can use the SQL CLient Network Utility to create an
alias that points the server name to the ip address. This would let you
connect using the server name.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Hadn't thought of that, 127.0.0.1 works fine so I can use the alias
approach. Thanks for your help.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Connecting to DB Engine

I am running a mirrored database using SQL2005 and whether or not mirroring has anything to do with this has not been determined. Often when a failover is necessary we are in a state where the active server responds just enough to show existence but not enough to determine database state. The SQL Management Studio can not connect to the server and the exception report from our application has the following message:

<exception type="System.Data.SqlClient.SqlException">

<message>A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)</message>
<source>.Net SqlClient Data Provider</source>
<target>System.Data.ProviderBase.DbConnectionPool::GetConnection</target>
<stack>

<trace index="0">at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)</trace>
<trace index="1">at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)</trace>
<trace index="2">at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)</trace>
<trace index="3">at System.Data.SqlClient.SqlConnection.Open()</trace>
<... application stack/>
</stack>

</exception>

When this happens, the the SqlClient does not fail over to the mirror until we stop the service on the failed SQL server. Sometimes, the mirror server doesn't full pick up until we stop the service on the failed server.

Today, out of curiosity I attemped to connect to port 1433 using TelNet to see if the server was even listening at the standard endpoint anymore (before I received the Exception Report above). It was. So how do I determine what is happening that causes the connections to close, and if at all possible - prevent it?

Thanks in advance for any advice you may have.

-Dave

Server Version:

Microsoft SQL Server 2005 - 9.00.3042.00 (X64)

Feb 10 2007 00:59:02

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

HI Dave,

Check for the following,

1.) Check the login whether it has necessary privileges on the database(default db for the login) its going to connect. Since the error shows its connected to the server and not to the default db.

2.) Check on which port Sqlserver is listening then connect using the port as servername,portno.

If possible post Errorlog & Eventlog info....
|||

Vidhya,

Yes. The login does have the rights and it is connecting to the correct port. When this occurs we (the DB admins) can not even connect via SQL Management Studio. We have to shut the service down via the Service Control Manager in Windows.

-Dave

Connecting to DB Engine

I am running a mirrored database using SQL2005 and whether or not mirroring has anything to do with this has not been determined. Often when a failover is necessary we are in a state where the active server responds just enough to show existence but not enough to determine database state. The SQL Management Studio can not connect to the server and the exception report from our application has the following message:

<exception type="System.Data.SqlClient.SqlException">

<message>A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)</message>
<source>.Net SqlClient Data Provider</source>
<target>System.Data.ProviderBase.DbConnectionPool::GetConnection</target>
<stack>

<trace index="0">at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)</trace>
<trace index="1">at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)</trace>
<trace index="2">at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)</trace>
<trace index="3">at System.Data.SqlClient.SqlConnection.Open()</trace>
<... application stack/>
</stack>

</exception>

When this happens, the the SqlClient does not fail over to the mirror until we stop the service on the failed SQL server. Sometimes, the mirror server doesn't full pick up until we stop the service on the failed server.

Today, out of curiosity I attemped to connect to port 1433 using TelNet to see if the server was even listening at the standard endpoint anymore (before I received the Exception Report above). It was. So how do I determine what is happening that causes the connections to close, and if at all possible - prevent it?

Thanks in advance for any advice you may have.

-Dave

Server Version:

Microsoft SQL Server 2005 - 9.00.3042.00 (X64)

Feb 10 2007 00:59:02

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

HI Dave,

Check for the following,

1.) Check the login whether it has necessary privileges on the database(default db for the login) its going to connect. Since the error shows its connected to the server and not to the default db.

2.) Check on which port Sqlserver is listening then connect using the port as servername,portno.

If possible post Errorlog & Eventlog info....
|||

Vidhya,

Yes. The login does have the rights and it is connecting to the correct port. When this occurs we (the DB admins) can not even connect via SQL Management Studio. We have to shut the service down via the Service Control Manager in Windows.

-Dave

connecting to database error

Hi there

i have a problem connecting creating a data source to SQL server express.

I am running winXP with service pack2

My machine is APOLLO\SQLEXPRESS

i AM USING AN ODBC CONNECTION.

i GET THE FOLLOWING ERRORS:

SQL SERVER ERROR 1231

AND

SQL SERVER ERROR 17.

Having done a google search and looked at the local security settings i am not sure what to add to the object names.

Can anybody please help.Gino

Gino,

You need to add authentication to your connection string. An application has it's own identity that SQL Server will not recognize.

Adamus

|||

Hi there again

Could you please state step by step procedure to perform this authentication. Is this authentication code based or windows based as i have mentioned previously? If this is code based could you please give some hints.

Gino