Using SQLCMD to test Connection

#91

Ok, I am embarrassed. I was getting a syntax error in SSMS, but the batch
file went to the first line after retyping the command in manually. Not
sure what I was expecting, perhaps a big "YOU ARE NOW CONNECTED" sign to
appear in front of my forehead? Haha, so that is now working, thank-you.

I did a little reading and I think I now understand the difference between
the two.

SQLCMD (or SQLCMD utility) allows you to enter T-SQL statements from the
command prompt. While SQLCMD scripts allow you to enter T-SQL scripts as
well as operating system commands in the query editor window.

Is that a fair summary?

Thanks,
Rubens

"Jesse Hersch" <nospam (AT) nospam (DOT) com> wrote

Quote:

sqlcmd is a console app. it's not something you invoke from SSMS. to run
it, do this:

1. start/run
2. type "cmd" and press enter to get a console window
3. type "SQLCMD -SMIS-RROSE1 -E" into the console and press enter

alternatively you could put "SQLCMD -SMIS-RROSE1 -E" in a
test_connection.bat file and run that.

What's confusing you is that there is a "sqlcmd mode" in SSMS. but that's
something else. read BOL for details.

--
http://elsasoft.org

"Rubens" <rubensrose (AT) hotmail (DOT) com> wrote in message
news:%23iVf9EYfIHA.1212 (AT) TK2MSFTNGP05 (DOT) phx.gbl...
Ok, let me take a step back here and tell you what I am trying to
accomplish. I was reading in a SQL 2005 textbook how its helpful to have
a few tools for testing client connectivity. It states:

Start Quote

You can also use SQLCMD with the -S parameter to connect to a particular
server. This is the syntax:

SQLCMD -Sprotocol_prefix:ServerName,PortNumber -E

In this syntax, protocol_prefix takes one of the following values:

np (for named pipes)
tcp (for TCP/IP)
lpc (for shared memory)
via (for VIA)

In the following example, -E indicates trusted connection use:

SQLCMD -Stcp:.\SQL2005, 1435 -E

End Quote

So ideally I'd like to be able to setup a batch file for certain servers
that I keep in a central location as a means of testing connectivity,
some of which connect with the default instance, others might be
different from the default. I thought I could switch SSMS to SQLCMD
mode, then run the previous script to do this, at which point I get the
error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Stcp:'.

Can I run the above statement in SQLCMD mode from SSMS? And if so, how?
Also, I need to know what syntax to put into a batch file.

I hope that helps, and thanks for your replies Tibor.

Rubens

"Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote
in message news:5E327AF6-FC0B-45BA-8473-242D50828C80 (AT) microsoft (DOT) com...
I put that command into a batch file and it still generates a syntax
error, so I must be doing something wrong...

Assuming you really execute the batch file from the operating system
command interpreter, then you should now have some other error compared
to the one you posted earlier. If you post that, we might be able to
assist.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Rubens" <rubensrose (AT) hotmail (DOT) com> wrote in message
news:eZF12sWfIHA.3724 (AT) TK2MSFTNGP02 (DOT) phx.gbl...
Hmmmm, I think I am going to have to doing some more reading about
SQLCMD. I put that command into a batch file and it still generates a
syntax error, so I must be doing something wrong...

Rubens

"Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com
wrote in message
news:80A1D3B3-074E-4ACF-AEBC-CDD347CE54BA (AT) microsoft (DOT) com...
Thanks all, but I still must be doing something wrong. I've switched
SSMS into SQLCMD mode and tried running all of the following.
...
Each of these generate the following error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'S'.

Above is because you are connected to SQL Server from SSMS, and SQL
Server expects a SQL command. SQLCMD is not a SQL command, it is an
EXE file. Run it from a bat file.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Rubens" <rubensrose (AT) hotmail (DOT) com> wrote in message
news:BEB5AF0C-B17A-4E4D-8DD7-1078827CE69A (AT) microsoft (DOT) com...
Thanks all, but I still must be doing something wrong. I've switched
SSMS into SQLCMD mode and tried running all of the following.

SQLCMD -Stcp:MIS-RROSE1 -E
SQLCMD -S tcp:MIS-RROSE1 -E
SQLCMD -S tcp:{MIS-RROSE1} -E
SQLCMD -S tcp:[MIS-RROSE1] -E

SQLCMD -SMIS-RROSE1 -E
SQLCMD -S MIS-RROSE1 -E
SQLCMD -S {MIS-RROSE1} -E
SQLCMD -S [MIS-RROSE1] -E

Each of these generate the following error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'S'.

Any idea? FYI... the reason I had initially tested forcing tcp on
port 1433 is I'd like to verify connectivity using this specific
connection. And going forward, I'd like to be able to test this way
if we change the default port.

Thank-you,
Rubens

"Hans" <hansxlion (AT) hotmail (DOT) com> wrote in message
news:2CD4AE4A-33C1-4341-A197-56AF30B930D9 (AT) microsoft (DOT) com...
Hi, It should be like this:

SQLCMD -S tcp:ServerName -E

"Rubens" <Rubens (AT) discussions (DOT) microsoft.com> ????
news:7C95C60D-3331-4481-9392-B9900CAB60C2 (AT) microsoft (DOT) com...
I am using the following comand to test a SQL 2005 connection, but
I seem to
have the syntax incorrect.

SQLCMD -Stcp:ServerName,1433 -E

What am I doing wrong?

Thanks,
Rubens

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Using SQLCMD to test Connection

microsoft.public.sqlserver.clients microsoft.public.sqlserver.clients

Re: Using SQLCMD to test Connection - 03-04-2008 , 07:39 AM