asked on

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!!

Randy Downs

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

Randy Downs

You are usisng posrt 3306 so it should be like this sqlcmd -S 127.0.0.1,3306

jellis0

ASKER

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.

Randy Downs

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

jellis0

ASKER

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.

jellis0

ASKER

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.

Randy Downs

OK that's the local ip so you need the actual ip for a remote host. Whatever you had in your script above

Randy Downs

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.

jellis0

ASKER

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

Randy Downs

The other reason given realtes to credentials. Sure thos are correct?

jellis0

ASKER

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?

ASKER CERTIFIED SOLUTION

Randy Downs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

wolfgang_93

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial