How do I do that? Articles, code samples, etc?
Oops, the actual requirement is just to query Oracle tables from SQL Server and to query SQL Server tables from Oracle.
Setting up a linked server in SQL Server accomplishes the first and is well documented in Books Online.
The second involves using Oracle Heterogenous Services or Transparent Gateway. I have not found very good documentation about how to use those.
I am trying to connect to a ms sql database from C# code. The code is being executed on the machine where the ms sql server is installed.
Am getting this error:
System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadSni Error(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSni (DbAsyncResult asyncResult, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadPac ket(Int32 bytesExpected) at System.Data.SqlClient.TdsParserStateObject.Re...
The connection string is as followed:
connString = "initial catalog=rolesdb;data source=LOCALHOST\\OFFICESERVERS;Integrated Security=sspi";
SqlConnection conn = new SqlConnection(connString);
conn.Open();
Any idea how to fix this error.
Regards,
bernadetIt seems that user/password information is missing from the connection string?|||
Quote:
Originally Posted by bernadet
Hi all,
I am trying to connect to a ms sql database from C# code. The code is being executed on the machine where the ms sql server is installed.
Am getting this error:
System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadSni Error(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSni (DbAsyncResult asyncResult, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadPac ket(Int32 bytesExpected) at System.Data.SqlClient.TdsParserStateObject.Re...
The connection string is as followed:
connString = "initial catalog=rolesdb;data source=LOCALHOST\\OFFICESERVERS;Integrated Security=sspi";
SqlConnection conn = new SqlConnection(connString);
conn.Open();
Any idea how to fix this error.
Regards,
bernadet
Hi,
When I'm using SQL server 2005, and ODBC drive “SQL Native Client” I get an error when executing the below given code. Error Message: “Transaction cannot start because more than one ODBC connection is in use.”
But this works well with SQL Server 2000 with ODBC drive "SQL Native Client" and in SQL server 2005 also when I used ODBC drive “SQL Server”.
I used aTrgConn.Execute(“Begin Transaction”) and it's working properly. But unfortunately I cannot use this method, because I want to run the same code with Oracle ODBC as well.
Is this a known issue in SQL Native client drive with SQL server 2005? Do you know a solution for this?
Please Help..............
Thanks in advance.
Code:
Dim aTrgConn As New ADODB.connection
Dim aCmdIns As New ADODB.Command
Dim aCmdUpd As New ADODB.Command
aTrgConn.Open sConnStr
Set aCmdIns.ActiveConnection = aTrgConn
Set aCmdUpd.ActiveConnection = aTrgConn
aTrgConn.BeginTrans
Why are you using two separate commands here? Couldn't you just use one command and reset the values to execute?|||Hi John,
You are correct. but I'm afraid, it's very difficult to change the design at this stage. and it worked perfectly with SQL Server 2000.
Thank you very much for your response.
|||Hi Again,
I think the problem comes when using the provider as MSDASQL.1 , with SQL server 2005 Native Client.
When I add "Provider=SQLNCLI;Integrated Security=SSPI;DataTypeCompatibility=80;MARS Connection=True;" to my connection string, it works correctly.
But again, I need the same coding to access oracle too. so SQLNCLI won't work with that.
It's really appriciated, If anyone could find a solution to this.
Thanks
|||Hi,
I have the same problem with my application.
I began to use SQL Express 2005 with Native Client ODBC rather than MSDE and found the same error.
My application must works with several databases.
If anyone find a solution for this problem tell me too.
Thanks advanced,
Wendel
|||Do you see the same problem with SQLNCLI OLEDB provider and not using MARS?
Thanks
Waseem
Hi,
When I'm using SQL server 2005, and ODBC drive “SQL Native Client” I get an error when executing the below given code. Error Message: “Transaction cannot start because more than one ODBC connection is in use.”
But this works well with SQL Server 2000 with ODBC drive "SQL Native Client" and in SQL server 2005 also when I used ODBC drive “SQL Server”.
I used aTrgConn.Execute(“Begin Transaction”) and it's working properly. But unfortunately I cannot use this method, because I want to run the same code with Oracle ODBC as well.
Is this a known issue in SQL Native client drive with SQL server 2005? Do you know a solution for this?
Please Help..............
Thanks in advance.
Code:
Dim aTrgConn As New ADODB.connection
Dim aCmdIns As New ADODB.Command
Dim aCmdUpd As New ADODB.Command
aTrgConn.Open sConnStr
Set aCmdIns.ActiveConnection = aTrgConn
Set aCmdUpd.ActiveConnection = aTrgConn
aTrgConn.BeginTrans
Why are you using two separate commands here? Couldn't you just use one command and reset the values to execute?|||Hi John,
You are correct. but I'm afraid, it's very difficult to change the design at this stage. and it worked perfectly with SQL Server 2000.
Thank you very much for your response.
|||Hi Again,
I think the problem comes when using the provider as MSDASQL.1 , with SQL server 2005 Native Client.
When I add "Provider=SQLNCLI;Integrated Security=SSPI;DataTypeCompatibility=80;MARS Connection=True;" to my connection string, it works correctly.
But again, I need the same coding to access oracle too. so SQLNCLI won't work with that.
It's really appriciated, If anyone could find a solution to this.
Thanks
|||Hi,
I have the same problem with my application.
I began to use SQL Express 2005 with Native Client ODBC rather than MSDE and found the same error.
My application must works with several databases.
If anyone find a solution for this problem tell me too.
Thanks advanced,
Wendel
|||Do you see the same problem with SQLNCLI OLEDB provider and not using MARS?
Thanks
Waseem
Hi,
When I'm using SQL server 2005, and ODBC drive “SQL Native Client” I get an error when executing the below given code. Error Message: “Transaction cannot start because more than one ODBC connection is in use.”
But this works well with SQL Server 2000 with ODBC drive "SQL Native Client" and in SQL server 2005 also when I used ODBC drive “SQL Server”.
I used aTrgConn.Execute(“Begin Transaction”) and it's working properly. But unfortunately I cannot use this method, because I want to run the same code with Oracle ODBC as well.
Is this a known issue in SQL Native client drive with SQL server 2005? Do you know a solution for this?
Please Help..............
Thanks in advance.
Code:
Dim aTrgConn As New ADODB.connection
Dim aCmdIns As New ADODB.Command
Dim aCmdUpd As New ADODB.Command
aTrgConn.Open sConnStr
Set aCmdIns.ActiveConnection = aTrgConn
Set aCmdUpd.ActiveConnection = aTrgConn
aTrgConn.BeginTrans
Why are you using two separate commands here? Couldn't you just use one command and reset the values to execute?|||Hi John,
You are correct. but I'm afraid, it's very difficult to change the design at this stage. and it worked perfectly with SQL Server 2000.
Thank you very much for your response.
|||Hi Again,
I think the problem comes when using the provider as MSDASQL.1 , with SQL server 2005 Native Client.
When I add "Provider=SQLNCLI;Integrated Security=SSPI;DataTypeCompatibility=80;MARS Connection=True;" to my connection string, it works correctly.
But again, I need the same coding to access oracle too. so SQLNCLI won't work with that.
It's really appriciated, If anyone could find a solution to this.
Thanks
|||Hi,
I have the same problem with my application.
I began to use SQL Express 2005 with Native Client ODBC rather than MSDE and found the same error.
My application must works with several databases.
If anyone find a solution for this problem tell me too.
Thanks advanced,
Wendel
|||Do you see the same problem with SQLNCLI OLEDB provider and not using MARS?
Thanks
Waseem
Hello masters,
help me yaar
i am using dotnet 2003 and SqL SERVER 2000. BUT UNABLE TO CONNECT DATABASE using dataAdaptor(design time ) or sqlconnection any can help me
gs
whats the error?
lets see the connection string.
make an empty text file called "c:\test.udl" - double click it and try to build a SQL Ole Db Provider datalink. . . can you get that to work?
Are the TCP protocols enabled in your machines client network settings and in the servers network settings?
|||Guess you don′t have the right connection string, look at www.connectionstrings.com for appropiate connection strings.HTH, jens Suessmeyer.|||
hi i am using this code it dose not show any error as well as no output... please check it...
string conn = ("Data Source=localhost;"+
"Initial Catalog=gstest;"+
"User ID=sa;"+
"Password=logic;");
SqlConnection con = new SqlConnection();
con.ConnectionString = conn;
SqlDataAdapter da = new SqlDataAdapter("select * from tb1",con);
DataSet ds = new DataSet();
da.Fill(ds,"tb");
dataGrid1.DataSource = ds.DefaultViewManager;
|||hello i got this error now,
what to do?
An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
|||If this is a web project (?!) you have to rebind the grid to the data
dataGrid1.DataBind()
HTH, jens Suessmeyer.
|||what does this do:
try
{
DataSet ds = new DataSet();
string conn = "Data Source=localhost;Initial Catalog=gstest;User ID=sa;Password=logic;";
using(SqlConnection con = new SqlConnection(conn))
{
using(SqlDataAdapter da = new SqlDataAdapter("select * from tb1",con))
{
da.Fill(ds);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
|||
hi master
it display...
Sql server dose not exist or access denied
but my sql server is running ......?
|||The using statement is doing garbage collection after the scope is funished. Fills your dataset like the first statement.HTH, Jens Suessmeyer.|||
Jens Suessmeyer wrote:
Guess you don′t have the right connection string, look at www.connectionstrings.com for appropiate connection strings. HTH, jens Suessmeyer.
the connection string is properly formed. whether that user/pw is correct and has permissions is another story.
Is the sql server on your development machine?
on the client, check that the TCP protocols are enabled using the Client Network Utility (cliconfig.exe) and on the server there is the Server Network Utility (svrnetcn.exe)
got windows firewall on? might try turning that off.
look at services control panel applet connected to the server.
does it show:
MSSQL or MSSQLSERVER
is it running?
|||Like it says "..or access denied". Whether you are not a default instance, then you have to suffix the instance name after the servername like localhost/instancename or you use a different port than the standard port then you have to add the port after the instancename servername/instance,Port ot you have a security problem, that the password is not valid for the sa account.HTH, jens Suessmeyer.|||
still suffring
MSSQLSERVER is running on my pc i have also turn off the fire wall.
but the error is the same.
i like to know that after installing sql server is any process to configure with dotnet2003
if there is any pls inform me.....
pls.............
|||Can you use integrated authentication ? If so (and you are in the adminstrative group, which should be server administrator by default int the installation) try using instead of UserID=yadayada;Password=blabla --> "Integrated Security=true"
HTH, Jens Suessmeyer-
|||no, not really. . . did you check these:
Client Network Utility (cliconfig.exe) and Server Network Utility (svrnetcn.exe)
to make sure the tcp protocols are started?
did you make that empty udl file to check if Datalinks could connect?
|||i am working on windows 2000 prof O/S
i have just downloaded the sqlserver 2000 form the net for installing this i have used the command in dos prompt...
C:\Program Files\Microsoft Visual Studio .NET 2003\Setup\MSDE\setup sapwd=logic securitymode=sql
after this sql server is installed in my computer and then i have downloded the Database manager(trial verson) to create the database and the tabels.
using this database and the tabels are created but
i am not able to connecte using dotnet2003
and pls tell me how to check the...
Client Network Utility (cliconfig.exe) and Server Network Utility (svrnetcn.exe)
to make sure the tcp protocols are started?
Hi,
how can i connect MSSQL through System DSN. i know how to create DSN. but i don't know how to code the connection string.
regards,
SeeConnectionStrings.com.
Ryan
hi,
i have a number of interfaces in which i have used oledb source.
the problem i am facing is oledb source components code page is not configurable now if i want to deploy the interface in a different environment which has a database with a different collation it gives a error that oledb source needs new metadata.
has anybody faced this problem earlier.please give me a solution to this problem ..
thanks in advance.
srikanth
Could you change the query to use cast around any varchar columns to force them the same or do you want to keep the collation for accents etc?Try changing the AlwaysUseDefaultCodePage property to true?
