Could not open a connection to SQL Server

Hi,

Has somebody had a problem like this before?

Exception Details:System.Data.SqlClient.SqlException:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by thefact that under the default settings SQL Server does not allow remoteconnections. (provider: Named Pipes Provider, error: 40 - Could notopen a connection to SQL Server)

That's part of the code:

SqlConnection con;
string sql;
SqlCommand cmd;

con=new SqlConnection("Data Source=elf/SQLEXPRESS;Initial Catalog=logos;Integrated Security=SSPI;");
sql="SELECT max(id_kandydat) from Kandydat";
con.Open();
cmd=new SqlCommand(sql,con);
int id = Convert.ToInt32(cmd.ExecuteScalar());

con.Close();

SqlDataSource7.InsertCommand="INSERT INTO Kandydat_na_Kierunek(id_kandydat, id_kierunku, id_stan) VALUES ("+"'"+id+"'"+","+"'"+DropDownList1.SelectedValue+"'"+","+'1'+");";

I don't really get it. I'm nearly 100% sure that before some changes in my project this had worked perfectly. Does anybody have any idea how to make it work again? I would be very grateful for any help. And, yesy, I know it is quite common error, but proposed solution found didn't help.

Regards,

N.

Hi there,

In the SQL Management Studio, RightClick the SQL server instance, Choose
Properties, and under Select a Page list, click Connections. Now you should
see a checkbox labelled "Allow Remote Connections to This Server". Make sure
it is checked.

Check this link...

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

Hope this may helps you

Srinath

|||Hi,Thank you for your advice. The remote connection has already been set on.|||

Hi

From your description, it seems that you met error when you are trying to connect to your SQLServer,right?

First, you should make sure that if 1433 port has been enabled on your server, which is the default port for SQLServer remote connections.

And Generally, you can try the following ways to handle your problem.

1. Enable remote connections for SQL Server 2005 Express or SQL Server 2005 Developer Edition
2. Enable the SQL Server Browser service
3. Create exceptions in Windows Firewall
4. Create an exception for SQL Server 2005 in Windows Firewall
5. Create an exception for the SQL Server Browser service in Windows Firewall

More details for each step, you may see the following link:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

Thanks.

|||

Thank you. Remote connection for both servers are enabled (always have been). Windows Firewall is shut; no other firewall is running. It does in fact work locally.

Regards,

N.

|||

Hi,

Then, let's back to your connection string.

con=new SqlConnection("Data Source=elf/SQLEXPRESS;Initial Catalog=logos;Integrated Security=SSPI;");

It seems that you are connecting to a SQLExpress instance, Generally, if you are using SQLExpress, you should assign the AttachDBFilename, but not the InitialCatalog.

See the following sample string:

data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|YouDataBaseName.mdf;User Instance=true providerName="System.Data.SqlClient"

Thanks.

Could not obtain a DataReader object from the specified data flow component.

I am getting the following exception when attempting to read from a DataReaderDestination:

System.Exception was unhandled
Message="Could not obtain a DataReader object from the specified data flow component."
Source="Microsoft.SqlServer.Dts.DtsClient"
StackTrace:
at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.internalPrepare(Boolean fReaderRequired)
at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.ExecuteReaderInThread()
at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.ExecuteReader(CommandBehavior behavior)
at CA3DataImportTool.ViewSSISOutput.btnRun_Click(Object sender, EventArgs e) in C:\Documents and Settings\rhein\My Documents\Visual Studio 2005\Projects\CA3DataImportTool\CA3DataImportTool\ViewSSISOutput.cs:line 35
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at CA3DataImportTool.Program.Main() in C:\Documents and Settings\rhein\My Documents\Visual Studio 2005\Projects\CA3DataImportTool\CA3DataImportTool\Program.cs:line 18
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

If I use the example from SQL Server BOL (http://msdn2.microsoft.com/en-us/library/ms135917.aspx), and make a new package for the sample in the same project, the sample works. The only thing that I can see that is significantly different between my code and the sample is that my DataReaderDestination has a lot more data in it, but here's the relevant code:

string dtexecArgs;

string dataReaderName;

DtsConnection dtsConnection;

DtsCommand dtsCommand; //IDbCommand

IDataReader dtsDataReader;

DataTable dtsTable;

dtexecArgs = @."/FILE ""C:\Documents and Settings\rhein\My Documents\Visual Studio 2005\Projects\CA3DataImportTool\ML3000_IntegrationProject\Package.dtsx"" ";

dataReaderName = "DataReaderDest";

dtsConnection = new DtsConnection();

dtsConnection.ConnectionString = dtexecArgs;

dtsConnection.Open();

dtsCommand = new DtsCommand(dtsConnection);

dtsCommand.CommandText = dataReaderName;

dtsDataReader = dtsCommand.ExecuteReader(CommandBehavior.Default); // EXCEPTION HERE

Please help!

Richard Hein

I get this when I have the worng name, check the name of your data reader destination really is DataReaderDest.|||

Quadruple checked ... I tried changing the name, increasing the DtsCommand timeout to 30 seconds, and I added another Data Flow Task to the same package with a DataReaderDestination and it worked. Very strange ... I must be missing something!

|||I looked at the difference between the Data Flow Tasks, and after some tests, I found that Delay Validation = true causes this error. Changing it to false solved the problem.|||Ahh yes, that one is fun too. For completeness, the real error you get when you get the data reader name (CommandText) wrong is, "The specified data flow component was not found in the package."