How do I connect to a remote DB with SQLCMD

I have a remote database that I connect to and run some simple select statements to retrieve data.  I want to automate this process using the sqlcmd.  Currently, I use MySQL Query Browser to connect to the db with the following settings:
Server Host: xx.xx.xxx.xx (ip address)
Port:3306
Username:<username>
Password:<password>
Default Schema: <schema_name>
From there I can execute what I need to and get my data.  
I have tried the following cmd and it does not work:
Sqlcmd -Stcp:<IP Address>\<schema_name>:3306 -U<username> -P<password> -Q"SELECT first_name, last_name, address1, address2, city, state, zip, regular_address, email, can_email FROM <table_name> WHERE DateDiff(d,DatePosted, GetDate())=0;" -o \\<server>\transfer\cr.csv -h-1 -s"," -w 700

*In the command above I substituted the actual <ip address> from the MySQL connection as well as the <schema_name> from the connection properties.

Any help would be greatly appreciated!!
jellis0Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy DownsOWNERCommented:
Maybe this will help

http://msdn.microsoft.com/en-us/library/ms188247(SQL.90).aspx

sqlcmd -S ComputerA,1433
sqlcmd -S ComputerA,1691
sqlcmd -S 127.0.0.1,1433
sqlcmd -S 127.0.0.1,1691
0
Randy DownsOWNERCommented:
You are usisng posrt 3306 so it should be like this sqlcmd -S 127.0.0.1,3306
0
jellis0Author Commented:
When I use the command like this: Sqlcmd -Stcp:<ip address>\<schema name>,3306
I get this error:
Sqlcmd: Error: Microsoft SQL Native Client : Protocol error in TDS stream.
Sqlcmd: Error: Microsoft SQL Native Client : Client unable to establish connection due to prelogin failure.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Randy DownsOWNERCommented:
According to the page referenced above you should use one of the following. Try putting a space between -S and tcp as in the 2nd example.

Connect to the Database Engine by specifying the TCP\IP port number:

sqlcmd -S 127.0.0.1,3306

or

Connect to a named instance:

sqlcmd -S tcp:127.0.0.1,1691
0
jellis0Author Commented:
I tried that (sqlcmd -S 127.0.0.1,3306) and it resulted in the same error:
Sqlcmd: Error: Microsoft SQL Native Client : Protocol error in TDS stream.
Sqlcmd: Error: Microsoft SQL Native Client : Client unable to establish connection due to prelogin failure.
0
jellis0Author Commented:
It seems like it doesn't even attempt to connect using this command sqlcmd -S tcp:127.0.0.1,3306 but when I use this command sqlcmd -S tcp:127.0.0.1:3306 it attempts to connect and I get this error:
HResult 0x2AF9, Level 16, State 1
TCP Provider: No such host is known.

Sqlcmd: Error: Microsoft SQL Native Client : 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..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
0
Randy DownsOWNERCommented:
OK that's the local ip so you need the actual ip for a remote host. Whatever you had in your script above
0
Randy DownsOWNERCommented:
You may also have to configure the server to allow remote connections

http://www.google.com/url?sa=t&rct=j&q=sql%20server%20does%20not%20allow%20remote%20connections&source=web&cd=1&ved=0CCoQFjAA&url=http%3A%2F%2Fsupport.microsoft.com%2Fkb%2F914277&ei=SS3yTsWZHYni2QWmz6WkAg&usg=AFQjCNG_upVJvf4KMvZWlRmWmR8fWoWOQg

Enable remote connections for SQL Server 2005 Express or SQL Server 2005 Developer Edition

You must enable remote connections for each instance of SQL Server 2005 that you want to connect to from a remote computer. To do this, follow these steps:
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.
0
jellis0Author Commented:
Yes, I'm using the remote ip and I got the sql error:
Sqlcmd: Error: Microsoft SQL Native Client : 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..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

As far as configuring remote connections, that is already configured as I can connect to it via MySQL Query Browser on port 3306
0
Randy DownsOWNERCommented:
The other reason given realtes to credentials. Sure thos are correct?
0
jellis0Author Commented:
Yes, I'm positive.  I use the same creds to connect via MySQL query browser.

On another note, MySQL uses an ODBC driver (v 5.1) and SQLCMD uses the native SQL client.  What are your thoughts on that?
Is there any way to connect SQLCMD to a system DSN?
0
Randy DownsOWNERCommented:
I don't think you can connect with SQLCMD using a system DSN.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=106377

Maybe this will help

http://msdn.microsoft.com/en-us/library/ms190181.aspx

"Sqlcmd: Error: Microsoft SQL Server Native Client : Login timeout expired."

"Could not open a connection to SQL Server"

"An error has occurred while establishing a connection to the server. When connecting to SQL Server, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections."

Typical Causes of This Error
--------------------------------------------------------------------------------

Cause
 Resolution
 
Server name was typed incorrectly.
 Try again with the correct server name.
 
The SQL Server service on the server is not running.
 Start the instance of SQL Server Database Engine.
 
The TCP/IP port for the Database Engine instance is blocked by a firewall.
 Configure the firewall to permit access to the Database Engine.
 
Database Engine is not listening on port 1433 because it has been changed, or because it is not the default instance, and the SQL Server Browser service is not running.
 Either start the SQL Server Browser service, or connect specifying the TCP/IP port number.
 
The SQL Server Browser service is running but UDP port 1434 is blocked by a firewall.
 Either configure the firewall to permit access to the UPD port 1434 on the server, or connect specifying the TCP/IP port number.
 
The client and server are not configured to use the same network protocol.
 Using SQL Server Configuration Manager, confirm that both the server and the client computers have at least one enabled protocol in common.
 
The network cannot resolve the server name to an IP address. This can be tested using the PING program.
 Fix the computer name resolution problem on your network or connect using the IP address of the server. This is not a SQL Server problem. For assistance, see your Windows documentation or your network administrator.
 
The network cannot connect using the IP address. This can be tested using the PING program.
 Fix the TCP/IP problem on your network. This is not a SQL Server problem. For assistance, see your Windows documentation or your network administrator.


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wolfgang_93Commented:
Check that your MySQL user id has permission to be accessed remotely by doing this:

Log onto your remote server and open a command prompt

Log in as root:
    mysql -u root -p

Issue commands:
    use mysql;
    select user,host from user where user='...username...'   <-- fill in actual ...username...

If it shows "host" as being "localhost", then it will NOT allow access remotely.

To change it to allow access remotely from any other server which is allowed to
connect to this one via TCP/IP and port 3306, you can do this:
   update user set host='%' where user='...username...';
   flush privileges;

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.