| Groups | Blog | Home |
sql server (alternate) : Fundamental help required with SQL connection
I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
server. When I create and instance of the server, it has a the format machinename/instancename This is fine for the developement machine but how does my code connect to the server when it is on another machine ? When an instance of MSDE is run on the target, it will produce... anotherPCname/instancename Thanks for any help
The 'Data Source' keyword in the connection string specifies the SQL Server
instance you want to connect to. A best practice is to store the connection string externally (e.g. config file) rather than hard-code it in your app so that you can connect to different servers without changing code. Connection string examples: Local default instance: Data Source=ThisServer;Initial Catalog=MyDatabase;Integrated Security=SSPI Local named instance: Data Source=ThisServer\ThisInstance;Initial Catalog=MyDatabase;Integrated Security=SSPI Remote default instance: Data Source=OtherServer;Initial Catalog=MyDatabase;Integrated Security=SSPI Remote named instance: Data Source=OtherServer\OtherInstance;Initial Catalog=MyDatabase;Integrated Security=SSPI -- Hope this helps. Dan Guzman SQL Server MVP [quoted text, click to view] "David" <david@orbitcoms.com> wrote in message news:YNvEc.71214$sj4.25491@news-server.bigpond.net.au... > I am writing SQL data apps using VB.NET 2003, MSDE is being used as the > server. > > When I create and instance of the server, it has a the format > machinename/instancename > > This is fine for the developement machine but how does my code connect to > the server when it is on another machine ? When an instance of MSDE is run > on the target, it will produce... > anotherPCname/instancename > > Thanks for any help > > > >
Dan,
Thanks for the reply. So, I just need to create a text file with the connection string with say the local machine ID and database name. The application loads this at boot and connects to the named server. When I deploy the application, I have the client alter the text file by finding the machine name and modifying the text to suit ?? I suppose I could use a Try Catch and have try with the local settings and if it fails it loads the file in the catch sub (or maybe the string is stored in registry). One thing I am also unable to find out is. How does MSDE know where the file is located on the third party machine ? Should the Initial Catalog = Path+dasename ? Thanks [quoted text, click to view] "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message news:FayEc.70$oD3.17@newsread1.news.pas.earthlink.net... > The 'Data Source' keyword in the connection string specifies the SQL Server > instance you want to connect to. A best practice is to store the connection > string externally (e.g. config file) rather than hard-code it in your app so > that you can connect to different servers without changing code. Connection > string examples: > > Local default instance: > Data Source=ThisServer;Initial Catalog=MyDatabase;Integrated Security=SSPI > > Local named instance: > Data Source=ThisServer\ThisInstance;Initial Catalog=MyDatabase;Integrated > Security=SSPI > > Remote default instance: > Data Source=OtherServer;Initial Catalog=MyDatabase;Integrated Security=SSPI > > Remote named instance: > Data Source=OtherServer\OtherInstance;Initial Catalog=MyDatabase;Integrated > Security=SSPI > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "David" <david@orbitcoms.com> wrote in message > news:YNvEc.71214$sj4.25491@news-server.bigpond.net.au... > > I am writing SQL data apps using VB.NET 2003, MSDE is being used as the > > server. > > > > When I create and instance of the server, it has a the format > > machinename/instancename > > > > This is fine for the developement machine but how does my code connect to > > the server when it is on another machine ? When an instance of MSDE is run > > on the target, it will produce... > > anotherPCname/instancename > > > > Thanks for any help > > > > > > > > > >
Hi
To connect to a different instance/server that is running your database then the connection string will need to be changed. Quite often this sort of thing is held in the registry and you configure it on installation. Alternatively you may want to provide a small application to configure the settings. http://www.microsoft.com/sql/msde/techinfo/MSDEintegration.asp John [quoted text, click to view] "David" <david@orbitcoms.com> wrote in message news:YNvEc.71214$sj4.25491@news-server.bigpond.net.au... > I am writing SQL data apps using VB.NET 2003, MSDE is being used as the > server. > > When I create and instance of the server, it has a the format > machinename/instancename > > This is fine for the developement machine but how does my code connect to > the server when it is on another machine ? When an instance of MSDE is run > on the target, it will produce... > anotherPCname/instancename > > Thanks for any help > > > >
Thanks again for the replies.
I have tried a hard-coded option of connection and it worked. ie. TRY sqlconnectionstring = ........ originalmachinename.......password=..." CATCH sqlconnectionstring = ........ newmachinename.......password=..." END TRY I tried to hook this into a config file but I had trouble(I used the wizard in VB.NET to make the connection string and edited it but the area the code was placed did not seem to like me playing with the code adding file open command etc. I will try by completely coding the connection in the main form load routine. Like this TRY Load my original instance for development and test CATCH read registry and see if validconnect string exists if so then try to connect else prompt user for PC name save name to registry try to connect FINALLY give up END TRY would this approach seem reasonable ? [quoted text, click to view] "John Bell" <jbellnewsposts@hotmail.com> wrote in message news:_FyEc.6675$%N6.73110644@news-text.cableinet.net... > Hi > > To connect to a different instance/server that is running your database then > the connection string will need to be changed. Quite often this sort of > thing is held in the registry and you configure it on installation. > Alternatively you may want to provide a small application to configure the > settings. > > http://www.microsoft.com/sql/msde/techinfo/MSDEintegration.asp > > John > > "David" <david@orbitcoms.com> wrote in message > news:YNvEc.71214$sj4.25491@news-server.bigpond.net.au... > > I am writing SQL data apps using VB.NET 2003, MSDE is being used as the > > server. > > > > When I create and instance of the server, it has a the format > > machinename/instancename > > > > This is fine for the developement machine but how does my code connect to > > the server when it is on another machine ? When an instance of MSDE is run > > on the target, it will produce... > > anotherPCname/instancename > > > > Thanks for any help > > > > > > > > > >
Hi
This may help: http://support.microsoft.com/default.aspx?scid=kb;en-us;309485 http://support.microsoft.com/default.aspx?scid=kb;EN-US;283245 John [quoted text, click to view] "David" <david@orbitcoms.com> wrote in message news:qazEc.71562$sj4.10264@news-server.bigpond.net.au... > Thanks again for the replies. > > I have tried a hard-coded option of connection and it worked. > > ie. > > TRY > sqlconnectionstring = ........ originalmachinename.......password=..." > CATCH > sqlconnectionstring = ........ newmachinename.......password=..." > END TRY > > I tried to hook this into a config file but I had trouble(I used the wizard > in VB.NET to make the connection string and edited it but the area the code > was placed did not seem to like me playing with the code adding file open > command etc. > > I will try by completely coding the connection in the main form load > routine. > Like this > > TRY > Load my original instance for development and test > CATCH > read registry and see if validconnect string exists > if so then try to connect else > prompt user for PC name > save name to registry > try to connect > FINALLY > give up > END TRY > > would this approach seem reasonable ? > > "John Bell" <jbellnewsposts@hotmail.com> wrote in message > news:_FyEc.6675$%N6.73110644@news-text.cableinet.net... > > Hi > > > > To connect to a different instance/server that is running your database > then > > the connection string will need to be changed. Quite often this sort of > > thing is held in the registry and you configure it on installation. > > Alternatively you may want to provide a small application to configure the > > settings. > > > > http://www.microsoft.com/sql/msde/techinfo/MSDEintegration.asp > > > > John > > > > "David" <david@orbitcoms.com> wrote in message > > news:YNvEc.71214$sj4.25491@news-server.bigpond.net.au... > > > I am writing SQL data apps using VB.NET 2003, MSDE is being used as the > > > server. > > > > > > When I create and instance of the server, it has a the format > > > machinename/instancename > > > > > > This is fine for the developement machine but how does my code connect > to > > > the server when it is on another machine ? When an instance of MSDE is > run > > > on the target, it will produce... > > > anotherPCname/instancename > > > > > > Thanks for any help > > > > > > > > > > > > > > > > > >
I finally have the problem figured out. I needed to ATTACH the database to
sql server (MSDE2000) using the osql utility. Now, would someone be able to explain how I can automate the attachment at the customer end. Preferably, I would like my VN.NET application install to copy the unattached database files and attach them to the MSDE server. Thanks [quoted text, click to view] "David" <david@orbitcoms.com> wrote in message news:YNvEc.71214$sj4.25491@news-server.bigpond.net.au... > I am writing SQL data apps using VB.NET 2003, MSDE is being used as the > server. > > When I create and instance of the server, it has a the format > machinename/instancename > > This is fine for the developement machine but how does my code connect to > the server when it is on another machine ? When an instance of MSDE is run > on the target, it will produce... > anotherPCname/instancename > > Thanks for any help > > > >
Hi
I am totally new to the VB.net, only learn this about two weeks. I used codes from MS to create a file upload class then add function to store the uploaded filename to a table in SQL server. However I kept getting the error message about "cannot connect to database" No matter what format I used. I have tried at least two dozen connection stringe formats either in Sqlconnection or OleDBConnection. Here is the basic formats that I used: strCon = "Data Source =localhost;Password=;User ID=sa;Initial Catalog=Northwind" for SqlConnection strCon = "Provider=SQLOLEDB.1;User ID=sa;Password=;Persist Security Info=True;Initial Catalog=Northwind;Data Source=FAL-L6388" for OleDbConnection. I use SQL 7, which was installed by default, i,e only have user ID = sa, no password, use the default Northwind database. That's all. My Server is local named FAL-L688. I have replaced the Data Source with Server= but still give me same error. I even used an udl to create the OleDB coonnection and put into my string, still NO. I have exhauseted all alternatives. Does anyone have clue? Is anyway to debug the trasaction? so i can find out what went wrong?
Here is my codes:
Imports System.Data.OleDb Imports System.Configuration .... Dim oCon As New OleDbConnection Dim oCmd As New OleDbCommand oCon = New OleDbConnection(ConfigurationSettings.AppSettings("strCon")) oCon.Open() oCmd.CommandType = CommandType.StoredProcedure oCmd.CommandText = "set_order_attachments" oCmd.Parameters.Add("@cart_id", OleDbType.VarChar, 15, cart_id) oCmd.Parameters.Add("@attachment", OleDbType.VarChar, 255, strFilenamesCollection) oCmd.Connection = oCon Try oCon.Open() oCmd.ExecuteNonQuery() .... And I have fololowing setting in the Web.config file under the <configuration> <appSettings> <add key="strCon" value="Provider=SQLOLEDB.1;User ID=sa;Password=;Initial Catalog=Northwind;Server=(local);" /> </appSettings> Also tried to use "Data Source=.." and enter my local machine name there. None of them work , came back the same "Non DB connection" C Chang
There was a duplicated "oCon.Open()" in my codes, but removal it does
not help. However, I tried to use the OracleCommand with the SQL query script directly and IT WORKS!. Does anyone know why it does not work with the Oracle procedure. I hate to change all my procedure calls to in-line scripts. Besides if there is a complicated procedure then the query becomes a problem.
(chi-soon_x_chang@raytheon.com) writes:
[quoted text, click to view] > There was a duplicated "oCon.Open()" in my codes, but removal it does > not help. However, I tried to use the OracleCommand with the SQL query > script directly and IT WORKS!. Does anyone know why it does not work > with the Oracle procedure. I hate to change all my procedure calls to > in-line scripts. Besides if there is a complicated procedure then the > query becomes a problem. I'm getting confused, are you connecting to SQL Server or to Oracle? To go back to your original code, the error message seems like it would from your own code. That is, I cannot really recall any error that says "Cannot connect to database". It would help, if you could get hold of the actual message from the client library. To that end, it would help if you posted more parts of the code, and also if you found out on exactly which statement, things go wrong. Stupid check: you have SQL Server running, haven't you? -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at
Hi
In addition to Erlands comments, you may want to move the try block earlier to catch the connection error. Also check out http://www.connectionstrings.com/ for the connection string, I think you should be using Data Source instead of Server. Try using the IP address instead of the name if necessary. John [quoted text, click to view] <chi-soon_x_chang@raytheon.com> wrote in message news:1103838243.185277.162240@z14g2000cwz.googlegroups.com... > There was a duplicated "oCon.Open()" in my codes, but removal it does > not help. However, I tried to use the OracleCommand with the SQL query > script directly and IT WORKS!. Does anyone know why it does not work > with the Oracle procedure. I hate to change all my procedure calls to > in-line scripts. Besides if there is a complicated procedure then the > query becomes a problem. >
hai please help me
From http://www.google.co.in/search?hl=en&client=firefox-a&channel=s&rls=org.mozilla:en-US:official&hs=ZDv&q=i+have+database+connection+in+Registry+file+so+how+can+i+add+the+database+when+i+create+setup+file+++vn.net&btnG=Search&meta= Posted via DevelopmentNow.com Groups
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |