Solved

MS SQL 2008 error when trying to execute script.

Posted on 2014-07-24
3
727 Views
Last Modified: 2014-07-25
I trying to automize a SQL Backup by executing a SQL script from my pc at work but i get an error:

The batch script to start the sql script:

echo off
sqlcmd -E -S SERVERDB\MSSQLSERVER -i C:\Users\MYNAME\Desktop\TestBackupSql.sql
SET /p delExit=Press the ENTER key to exit...:

/* HResult 0x57, Level 16, State 1
SQL Server Network Interfaces: Connection string is not valid [87].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in
stance-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 i
f 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.
Press the ENTER key to exit...: */

I can open a sql session to serverdb from an instance on my pc.I guess this means there might be someting wrong with my syntax or something i've omitted from script file?
0
Comment
Question by:The_Nibbler
3 Comments
 
LVL 32

Accepted Solution

by:
ste5an earned 167 total points
ID: 40216322
You should test the connectivity in the first place:

sqlcmd -E -S <YourServerName> -q "SELECT @@VERSION;"

Open in new window


Onyl use the server name or IP. Don't use the default instance name. When also doesn't work, you need to check, whether or not your server accepts remote connections.

Here's an good tutorial with screenshots: How to enable remote connections in SQL Server 2008?
0
 
LVL 12

Assisted Solution

by:Tony303
Tony303 earned 166 total points
ID: 40216342
Hi Nibbler

OK, so your machine doesn't recognise the SQL server.
Is the SQL server a named instance? If not take away the \MSSQLSERVER from the connection string
Is your SQL Server on the network as "SERVERDB"? Probably not.

Here is the SQL connection format.
sqlcmd -S <ComputerName>\<InstanceName>

This EE article may be helpful too...
http://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_26765525.html

Hope this helps

Tony
0
 
LVL 24

Assisted Solution

by:Mohammed Khawaja
Mohammed Khawaja earned 167 total points
ID: 40216366
MSSQLSERVER is the default instance.  Go to your Control Panel and look at services.  If you only see MS SQL Server service then you need to connect to SERVERNAME only and no instance name is required.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now