| Author |
Topic |
|
HDADave
Starting Member
13 Posts |
 Posted - 2011-08-13 : 21:02:43
|
| I'm redesigning an old website from a Windows Server. I work on a Unix server. I'm used to using phpMyAdmin when setting up databases. I need to export the data from the old database for use in the new. I know nothing about using SQL with SQLOLEDB. The old database manager gave me the following information but I have no idea how to use it:"Provider =SQLOLEDB;Data Source=XXXXXXXX.XX.net;Initial Catalog=XXXXX; User ID = XXXXXX; Password = XXXXXXX"I thought I'd be signing onto some admin program, selecting the database and exporting the tables into Excel.Can someone please give me some instructions? I've looked around on google but can't seem to find anything I can follow. Don't know how to start!Thanks for any help. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
HDADave
Starting Member
13 Posts |
Posted - 2011-08-13 : 23:28:48
|
Thanks for responding Tara.I just want to download the data and use it to create new tables using phpMyadmin on a MySQL database. Will the SQL Server Express application do that for me?quote:
Originally posted by tkizer
That's just a connection string that you would use in whatever application you would be designing. If you don't intend to design an application, then how about downloading and using SQL Server Express? You can export data via bcp.exe, which is a command line tool.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-08-14 : 12:21:02
|
| So you aren't using Microsoft SQL Server at all? I couldn't tell from your original post as you only mentioned SQLOLEDB, which is often used in conjunction with a SQL Server database. I ask this question because SQLTeam.com is for Microsoft SQL Server. We don't have MySql or php experience here.You can use SQL Server's SSIS tool to do what you want, but you would need to purchase a license which probably doesn't make sense if you don't plan on using SQL Server at all.So "download the data"...is that from a SQL Server database or not?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
HDADave
Starting Member
13 Posts |
Posted - 2011-08-14 : 12:50:28
|
The database is on a Windows Server and the connection string I included in my origional post was sent to me by the guy who is administering the database. I just want to download the table contents so I can build the new database on a Linux server using phpMyAdmin. I don't know anything about Microsoft SQL and am thus having a dificult time conveying what I need and understanding how to do it. Any help would be greatly appreciated.quote:
Originally posted by tkizer
So you aren't using Microsoft SQL Server at all? I couldn't tell from your original post as you only mentioned SQLOLEDB, which is often used in conjunction with a SQL Server database. I ask this question because SQLTeam.com is for Microsoft SQL Server. We don't have MySql or php experience here.You can use SQL Server's SSIS tool to do what you want, but you would need to purchase a license which probably doesn't make sense if you don't plan on using SQL Server at all.So "download the data"...is that from a SQL Server database or not?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
HDADave
Starting Member
13 Posts |
Posted - 2011-08-15 : 00:49:29
|
That sounds like what I first asked my contact to have the programmer do. I asked for the URL and sign-in info for their DB admin program so I could download the DB contents. What I received was the connection string. That's where all my confusion started. My lack of knowledge and understanding of the Windows community lingo with regard to internet servers and DBs and proceedures has clouded what I thought was a very simple task.Will the "client tools" have a data export function that will download the data in an Excel or comma delimited format?Thanks for your help Tara. I'm sorry I couldn't communicate what I needed in a more direct way. I was confused by what the programmer had sent me.quote:
Originally posted by tkizer
I would recommend checking with the administrator of the Windows server if you can get access to the client tools of the dbms he supports. Providing just a connection string is not enough to complete your task.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-08-15 : 00:53:38
|
| SQL Server has very powerful tools to import and export data, but you need more than a connection string. I would check if your company has a license for you to install the SQL client tools. If not, then I'd suggest downloading and installing SQL Server Express edition. Once you have that installed, you could then use bcp.exe from the command-line to export the data to csv files, which hopefully php can import easily. We can help with the bcp.exe syntax once you get it installed.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
HDADave
Starting Member
13 Posts |
Posted - 2011-08-15 : 12:28:56
|
I downloaded Microsoft Server 2008 R2 for this project. Is that different than the SQL Server Express? I'd like to download whatever I need and then contact the programmer with an email to ask for exactly what I need to use bcp.exe as you suggest. I'll need some help to do so and am gratefuly you are willing to help. Please let me know if I need to install the Express edition to use the bcp.exe program. Thanksquote:
Originally posted by tkizer
SQL Server has very powerful tools to import and export data, but you need more than a connection string. I would check if your company has a license for you to install the SQL client tools. If not, then I'd suggest downloading and installing SQL Server Express edition. Once you have that installed, you could then use bcp.exe from the command-line to export the data to csv files, which hopefully php can import easily. We can help with the bcp.exe syntax once you get it installed.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
HDADave
Starting Member
13 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
HDADave
Starting Member
13 Posts |
Posted - 2011-08-15 : 15:28:53
|
OK the Db1.dbo.Tb11 part... the programer sent me a connection string with Source=clientdb.hostname.net; Initial Catalog=name. Are these part of the Db1.dbo.Tb11. Do I need to get the Table Names? Or can I do a bulk download. ThanksMaybe I should have asked you something like this so I can follow the format of this command statement. If my values are like below, what would the bcp command statement look like?User ID = JohnPassword = gojohnData Source = clientdb.myhost.netProvider = SQLOLEDBInitial Catalog = dietcokeThese are from the connection string the programmer sent me. It's all I have so far, if I need something else from him, what should I ask for?quote:
Originally posted by tkizer
Here's an example bcp.exe command to export data for a specific table:Windows authentication:bcp Db1.dbo.Tbl1 out c:\Tbl1.csv -Sserver1\instance1 -T -c -t, -r\r\nSQL authentication:bcp Db1.dbo.Tbl1 out c:\Tbl1.csv -Sserver1\instance1 -Uuser1 -Ppassword1 -c -t, -r\r\nTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-08-15 : 18:56:47
|
| You can get the table names via sqlcmd.exe, which is a command line tool that allows you to query the server. Better yet, you could use Management Studio Express.For sqlcmd.exe:sqlcmd -E -Sserver1\instance1>select table_name from dietcoke.information_schema.tables>goYou'll then get the output to the screen.clientdb.myhost.net doesn't sound right for the data source, but maybe you would use that instead of server1\instance1 in my examples.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
HDADave
Starting Member
13 Posts |
Posted - 2011-08-15 : 22:11:11
|
Hi Tara,I tried the sqlcmd and got the following message:C:\Users\Dave>sqlcmd -E -Sclientdb.XXXX.netHResult 0x35, Level 16, State 1Named Pipes Provider: Could not open a connection to SQL Server [53].Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.quote:
Originally posted by tkizer
You can get the table names via sqlcmd.exe, which is a command line tool that allows you to query the server. Better yet, you could use Management Studio Express.For sqlcmd.exe:sqlcmd -E -Sserver1\instance1>select table_name from dietcoke.information_schema.tables>goYou'll then get the output to the screen.clientdb.myhost.net doesn't sound right for the data source, but maybe you would use that instead of server1\instance1 in my examples.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
HDADave
Starting Member
13 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-08-16 : 13:43:37
|
| Yes it does help. I assume you installed the express edition, which usually has EXPRESS as the instance name. This won't be the easiest in the world to do via command line, so you may want to get Management Studio Express installed. Otherwise, we may be going back and forth on this a bit.Let's see if we can at least get connected to your local engine:From a cmd window, type in hostname and hit enter. You'll need that info for the next command:sqlcmd -E -SyourHostName\ExpressIf you get this >, you are connected. Let me know.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
HDADave
Starting Member
13 Posts |
Posted - 2011-08-16 : 13:50:18
|
Here's what I get:C:\Users\Dave>sqlcmd -E -SAcer-laptop\ExpressHResult 0xFFFFFFFF, Level 16, State 1SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.C:\Users\Dave>quote:
Originally posted by tkizer
Yes it does help. I assume you installed the express edition, which usually has EXPRESS as the instance name. This won't be the easiest in the world to do via command line, so you may want to get Management Studio Express installed. Otherwise, we may be going back and forth on this a bit.Let's see if we can at least get connected to your local engine:From a cmd window, type in hostname and hit enter. You'll need that info for the next command:sqlcmd -E -SyourHostName\ExpressIf you get this >, you are connected. Let me know.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
HDADave
Starting Member
13 Posts |
Posted - 2011-08-25 : 21:18:23
|
I don't know why you stop helping me but I thought I'd let you know that the problem is solved. The host who sent me the connection string neglected to open a port for my IP. Once he did this I had no problem connecting with Express and downloading what I needed. The Express program was very easy to figure out and use. Exporting in Excel format makes it very easy to then import with phpMyAdmin. Thanks for the time you spent trying to help me.quote:
Originally posted by tkizer
Great, that will help out a lot. It sounds like you don't have Express installed, so we now need to figure out what did get installed. Please check Services in Control Panel and let me know what you see for services that start with SQL Server.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
Next Page
|
Extracting SQLOLEDB data