connection error

hi,

I installed sql server 2005 ,when i try to connect to sql server management studio iam getting this error.

An error has occurred 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) (.Net SqlClient Data Provider)

I searched internet and found this :under sql server surface area configuration

So I followed the following steps to change the setting from local connection to local and remote connection:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration. 2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections. 3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.

Note Click OK when you receive the following message:

Changes to Connection Settings will not take effect until you restart the Database Engine service.

4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

But there is no DATABASE ENGINE It contain ANALYSIS SERVICES.

Then How Get rid from the above error.

Thanks in advance.

hi,

I installed sql server 2005 ,when i try to connect to sql server management studio iam getting this error.

An error has occurred 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) (.Net SqlClient Data Provider)

I searched internet and found this :under sql server surface area configuration

So I followed the following steps to change the setting from local connection to local and remote connection:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration. 2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections. 3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.

Note Click OK when you receive the following message:

Changes to Connection Settings will not take effect until you restart the Database Engine service.

4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

But there is no DATABASE ENGINE It contain ANALYSIS SERVICES.

Then How Get rid from the above error.

Thanks in advance.

|||

Look in the SCM (service Control manager of Windows) There you will find the SQL Server Service "SQL Server (MSSQLSERVER)" (or probably and instance name in the parenth.) Restart this service and you′ll be fine.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Connection

iam using an execute sql task

ineed the connection to be daynamic ,changed at run time ..

how can i do that

Depending on your scenario you may have more than one option. Serach in package configurations or "http://msdn2.microsoft.com/en-us/library/ms141682.aspx">http://msdn2.microsoft.com/en-us/library/ms141682.aspx

"http://msdn2.microsoft.com/en-us/library/ms141232.aspx">http://msdn2.microsoft.com/en-us/library/ms141232.aspx

|||

Batool wrote:

iam using an execute sql task

ineed the connection to be daynamic ,changed at run time ..

how can i do that

Use an "http://blogs.conchango.com/jamiethomson/archive/2006/10/05/SSIS-Nugget_3A00_-Dynamically-set-a-logfile-name.aspx">http://blogs.conchango.com/jamiethomson/archive/2006/10/05/SSIS-Nugget_3A00_-Dynamically-set-a-logfile-name.aspx

talks about setting an expressoin on the ConnectionString property of a file conenction manager rather than an OLE DB Conenction Manager, but the concept is the same.

-Jamie

|||

Hi,

The easiest way to set dynamic connection at run time is by declaring two SSIS variables

1. DBName
2. DBServer

In connection properties open the "InitialCatalog" = "@.[User::DBName]" and "ServerName" = "@.[User::DBServer]".While running the package if you just set the value of variables dynamically the connection to the DB will be created accordingly. Using this connection in SQL task will server your purpose. This works with both OLEDB and ADO.NET connections.

Thanks

Mohit

|||thx for ur replay and i think its my solution but,can we read theses variable from the registry|||

Hi,

Using follwoing in Script tasks will retrieve value from registry

Imports Microsoft.Win32

VarValue = Registry.LocalMachine.GetValue(<Registry Key>)

After getting the value you can set this in SSIS variables using Dts.Variables("DBServer").Value = VarValue will set the value from registry

Thanks

Mohit

|||Sorry that seems a bit mad. Why write code when there is a built in mechanism, see Configurations. You can use the registry as a source for a configuration. It also happens before the package starts executing, so things start working from the very begining. Often changing a connection part way can be an issue, as tasks have started to validate or do other preparation.|||

I PUT THE NEEDED VALUES IN THE REGISTRY

(DB NAME AND SERVER NAME )

BUT I DID NOT KNOW HOW TO RETRIVE THESE VALUES AND MADE THE CONNECTION STRING

|||

Independently where you want to store your configuration values; in the case of connection managers you can better store the whole connection string, instead of Server name and DB name; and then use that value against the connection string property.

|||

There is a section in Books Online that describes the registry format. Seems that you must use one value per key, and that value must be called Value. It also seems to be limited to values within HKEY_CURRENT_USER. In retrospect the Script task method may be better after all with these rather restrictive limitations.

Package Configurations
(http://msdn2.microsoft.com/en-us/library/d20e0311-1fc9-4ddc-a381-6d127cf11b69.aspx)

Added Connect Feedback on these restrictions - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=250871