Re: Using SQLCMD to test Connection
- From: "Rubens" <rubensrose@xxxxxxxxxxx>
- Date: Tue, 4 Mar 2008 08:39:46 -0500
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@xxxxxxxxxx> wrote in message news:OoQ4YPafIHA.4376@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxx> wrote in message news:%23iVf9EYfIHA.1212@xxxxxxxxxxxxxxxxxxxxxxxOk, 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@xxxxxxxxxxxxxxxxxx> wrote in message news:5E327AF6-FC0B-45BA-8473-242D50828C80@xxxxxxxxxxxxxxxxI 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@xxxxxxxxxxx> wrote in message news:eZF12sWfIHA.3724@xxxxxxxxxxxxxxxxxxxxxxxHmmmm, 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@xxxxxxxxxxxxxxxxxx> wrote in message news:80A1D3B3-074E-4ACF-AEBC-CDD347CE54BA@xxxxxxxxxxxxxxxxThanks 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@xxxxxxxxxxx> wrote in message news:BEB5AF0C-B17A-4E4D-8DD7-1078827CE69A@xxxxxxxxxxxxxxxxThanks 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@xxxxxxxxxxx> wrote in message news:2CD4AE4A-33C1-4341-A197-56AF30B930D9@xxxxxxxxxxxxxxxxHi, It should be like this:
SQLCMD -S tcp:ServerName -E
"Rubens" <Rubens@xxxxxxxxxxxxxxxxxxxxxxxxx> ???? news:7C95C60D-3331-4481-9392-B9900CAB60C2@xxxxxxxxxxxxxxxxI 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
- References:
- Re: Using SQLCMD to test Connection
- From: Rubens
- Re: Using SQLCMD to test Connection
- From: Tibor Karaszi
- Re: Using SQLCMD to test Connection
- From: Rubens
- Re: Using SQLCMD to test Connection
- From: Tibor Karaszi
- Re: Using SQLCMD to test Connection
- From: Rubens
- Re: Using SQLCMD to test Connection
- From: Jesse Hersch
- Re: Using SQLCMD to test Connection
- Prev by Date: Problem with Named Pipes
- Next by Date: Re: problem with connecting to sql server via a wireless applicati
- Previous by thread: Re: Using SQLCMD to test Connection
- Next by thread: Problem with Named Pipes
- Index(es):
Relevant Pages
|
