Fail To Connect Using Sqlcmd

May 15, 2008

Hi !
I try to connect to a database on my localhost using sqlcmd. For the records: Remote connections are allowed ! I don't have any issues to establish a connection using SSMS.

S:DBWorkFolder>sqlcmd -U razorblade -P xxxxx -S localhostzen_dev4

This is what I get:

HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
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.

Any help appreciated !

Thanks: Peter

View 1 Replies


ADVERTISEMENT

SQLCMD Can Not Connect To SQLExpress 2005

Feb 26, 2007

I have installed SQL Express 2005 on a machine and I am trying to run a script file via SQLCMD from my application. I keep getting an error stating that SQLCMD can not connect to the server. Remote connections are enabled for both tcp/ip and named pipes. Sql Browser Service is enabled and running.

I am calling the script like so

sqlcmd -E -S TESTPLATFORMSQLEXPRESS -i "C:Program FilesADMS_TrialCREATE_DATABASE.sql" -o C:Error.txt


This is the error message
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
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.

I can open Management studio and run the script and it works perfectly so I don't really know what is going on. Thanks for any help.

View 3 Replies View Related

Unable To Connect To Sql Server Express Using SQLCMD

Aug 15, 2006

Hello,

I'm unable to connect to Sql Server express using SQLCMD. I've trawled through countless web pages to try to solve my problem but to no avail.

Using windows 2000.

I'm getting the message:

HResult 0x2, Level 16, State 1

Named Pipes Provider: Could not open a coonection to SQL Server...............

Sqlcmd: Error: Microsoft SQL Native Client : Login timout expired.

I've enabled the various protocols in the Sql config manager......



Please help!



Thanks,



dom

View 7 Replies View Related

Is It Possible To Have Sqlcmd Connect To Local Sqlexpress Without A Parameter?

Dec 7, 2005

(SQL Server Express 2005)

View 4 Replies View Related

Fail To Connect Via Sockets To SQL Server

Apr 24, 2000

Background:

NT EE, MSCS, SQL 7.0 in Active/Passive environment. Add'l SQL installed on Passive server is hosting our Report database(any tips on Log Shipping?).
Anyway, I am unable to configure a client to connect to the secondary server via TCP/IP sockets.(Error 10061). I use the standard port 1433 and I've tried using both the name and the IP address for the server name.

What it looks like it's doing is only listening to port 1433 on the hearbeat nic. The reason I believe this is because when I ping the server name from that server, it returns a succesful ping, but using the heartbeat(seperate subnet)nics IP address.

Ping from a remote box works fine(DNS/Wins Resolution), and I connect fine with Named Pipes.

Both the primary and secondary server display the same results, the difference appears to be that only a virtual server in a clustered environment can have TCP/IP clients connect to it.

Can anyone verify this for me? Thanks a bunch...

MS says to go Active/Active and I am willing to do so, eventually.
Looking for a short term solution.

I'm not able to listen on a different port(again, appears only a Virtual server can).

Pete Karhatsu

View 3 Replies View Related

Fail Connect To Intergrate Services

Jul 25, 2006

Message:

Cannot connect to server
additional information
- failed to retrieve data for this request.(microsoft.sqlserver.smoenum)
- connect to SSIS service on machine "server" failed:
error loading type library/dll
- connect to SSIS service on machine 'server' failed :
error loading type library/dll


any idea?

soonyu

View 3 Replies View Related

Services Fail To Connect To SQLE On Startup

Mar 6, 2006

I have an application that runs as a windows service that uses an ODBC connection. I recently upgraded
the database from MSDE -> Sql Express.

Everything works fine (connectivity, etc) when I start my application manually. When I attempt to start as
a service I get connection timeouts. After a small window of time, I can rerun my service and things connect
just fine.

As a workaround I'm planning on having my service connect, fail, slep 5 seconds and then attempt a 2nd connection.
I really hate to do this and was wondering if anyone had any ideas. Below is the error log I get after I reboot
my PC.

I'm attempting to connect as: "TestMachineNameLocalServerUserNameHERE" to the DB of "MyDatabase". (Names have been
changed so I don't catch flack from my company for posting specific details :))

Connection is done via ODBC via a system DSN to "localhost". I can sucessfully test connect here, but I can only
do this after the services have all started.

Any ideas?

Thanks,

Andy


========================================================================================
========================================================================================
Error log after startup
========================================================================================
========================================================================================

2006-07-06 08:09:39.00 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2006-07-06 08:09:39.00 Server (c) 2005 Microsoft Corporation.
2006-07-06 08:09:39.00 Server All rights reserved.
2006-07-06 08:09:39.00 Server Server process ID is 460.
2006-07-06 08:09:39.00 Server Logging SQL Server messages in file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG'.
2006-07-06 08:09:39.00 Server This instance of SQL Server last reported using a process ID of 3188 at 7/6/2006 8:08:04 AM (local) 7/6/2006 12:08:04 PM (UTC). This is an informational message only; no user action is required.
2006-07-06 08:09:39.00 Server Registry startup parameters:
2006-07-06 08:09:39.00 Server -d C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf
2006-07-06 08:09:39.00 Server -e C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG
2006-07-06 08:09:39.00 Server -l C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf
2006-07-06 08:09:39.09 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2006-07-06 08:09:39.09 Server Detected 1 CPUs. This is an informational message; no user action is required.
2006-07-06 08:09:39.34 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2006-07-06 08:09:39.35 Server Database Mirroring Transport is disabled in the endpoint configuration.
2006-07-06 08:09:39.37 spid5s Starting up database 'master'.
2006-07-06 08:09:39.79 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2006-07-06 08:09:40.09 spid5s SQL Trace ID 1 was started by login "sa".
2006-07-06 08:09:40.29 spid5s Starting up database 'mssqlsystemresource'.
2006-07-06 08:09:41.17 spid8s Starting up database 'model'.
2006-07-06 08:09:41.17 spid5s Server name is 'TestMachineName'. This is an informational message only. No user action is required.
2006-07-06 08:09:41.18 spid5s Starting up database 'msdb'.
2006-07-06 08:09:41.48 Server A self-generated certificate was successfully loaded for encryption.
2006-07-06 08:09:41.48 Server Server is listening on [ 'any' <ipv6> 1433].
2006-07-06 08:09:41.50 Server Server is listening on [ 'any' <ipv4> 1433].
2006-07-06 08:09:41.50 Server Server local connection provider is ready to accept connection on [ \.pipeSQLLocalMSSQLSERVER ].
2006-07-06 08:09:41.50 Server Server local connection provider is ready to accept connection on [ \.pipesqlquery ].
2006-07-06 08:09:41.50 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2006-07-06 08:09:41.51 Server The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2006-07-06 08:09:41.51 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2006-07-06 08:09:42.29 Logon Error: 18456, Severity: 14, State: 16.
2006-07-06 08:09:42.29 Logon Login failed for user 'TestMachineNameLocalServerUserNameHERE'. [CLIENT: <local machine>]
2006-07-06 08:09:42.29 Logon Error: 18456, Severity: 14, State: 16.
2006-07-06 08:09:42.29 Logon Login failed for user 'TestMachineNameLocalServerUserNameHERE'. [CLIENT: 192.168.1.31]
2006-07-06 08:09:43.48 Logon Error: 18456, Severity: 14, State: 16.
2006-07-06 08:09:43.48 Logon Login failed for user 'TestMachineNameLocalServerUserNameHERE'. [CLIENT: <local machine>]
2006-07-06 08:09:43.48 Logon Error: 18456, Severity: 14, State: 16.
2006-07-06 08:09:43.48 Logon Login failed for user 'TestMachineNameLocalServerUserNameHERE'. [CLIENT: <local machine>]
2006-07-06 08:09:43.89 Logon Error: 18456, Severity: 14, State: 16.
2006-07-06 08:09:43.89 Logon Login failed for user 'TestMachineNameLocalServerUserNameHERE'. [CLIENT: <local machine>]
2006-07-06 08:09:44.00 Logon Error: 18456, Severity: 14, State: 16.
2006-07-06 08:09:44.00 Logon Login failed for user 'TestMachineNameLocalServerUserNameHERE'. [CLIENT: <local machine>]
2006-07-06 08:09:44.15 spid8s Clearing tempdb database.
2006-07-06 08:09:44.21 Logon Error: 18456, Severity: 14, State: 16.
2006-07-06 08:09:44.21 Logon Login failed for user 'TestMachineNameLocalServerUserNameHERE'. [CLIENT: <local machine>]
2006-07-06 08:09:44.25 Logon Error: 18456, Severity: 14, State: 16.
2006-07-06 08:09:44.25 Logon Login failed for user 'TestMachineNameLocalServerUserNameHERE'. [CLIENT: <local machine>]
2006-07-06 08:09:44.96 Logon Error: 18456, Severity: 14, State: 16.
2006-07-06 08:09:44.96 Logon Login failed for user 'TestMachineNameLocalServerUserNameHERE'. [CLIENT: <local machine>]
2006-07-06 08:09:44.96 spid8s Starting up database 'tempdb'.
2006-07-06 08:09:45.32 spid5s Recovery is complete. This is an informational message only. No user action is required.
2006-07-06 08:09:45.32 spid11s The Service Broker protocol transport is disabled or not configured.
2006-07-06 08:09:45.32 spid11s The Database Mirroring protocol transport is disabled or not configured.
2006-07-06 08:09:45.46 spid11s Service Broker manager has started.
2006-07-06 08:09:46.00 spid51 Starting up database 'MyDatabase'.
2006-07-06 08:09:46.10 spid51 Recovery is writing a checkpoint in database 'MyDatabase' (6). This is an informational message only. No user action is required.

View 4 Replies View Related

SQLCMD

May 4, 2007

I cannot get SQLCMD to run. When I run it, it appears for a few seconds then dissappears. I tried running it in cmd and it says it cannot run because the default setting for SQL server does not allow remote connections. Please help.

View 11 Replies View Related

SQLCMD

May 2, 2008

When connecting to SQL using the SQLCMD
Is there a way to do a select and it output in DOS mode...

Even have the message return - completed just as if you were in sql query.

View 1 Replies View Related

SQLCMD

May 22, 2008



Hello,

Does anyone know how to execute a stored procedure in a SQLCMD script? Here is what I have so far and it doesn't seem to be working...

:r $(rootPath)"Script.sql"
GO
exec sp_Proc
GO


Also,

Is there a way that I can pass the $(rootpath) as a parameter in my stored proc?

Thanks!

View 3 Replies View Related

SQLCMD.exe

Feb 21, 2008



I am trying to backup/restore a sql db using sqlcmd. My question is, the box that I am trying to run sqlcmd on does not have SQL server 2005 installed, can I just copy SQLCMD.exe to the box instead of Installing it? if so, are there any other files that I need to copy as well?

I have another instance where I was trying to backup/restore a 2000 sql db and all I did was copy the osql.exe and resource files to the box and was able to run this command with out installing SQL server 2000. So, did something change with SQL serve 2005?

Thanks for any suggestions

View 6 Replies View Related

Need Example -- SQLCMD -y

Aug 23, 2006

Hi,

Please can any one let me know jhow to use -y option with SQLCMD.







View 11 Replies View Related

Sqlcmd Syntax

Jul 20, 2007

How can I use sqlcmd to execute a simple stored procedure, I dont need to pass parameters in or out, its just for backups.

Thanks

View 2 Replies View Related

Sqlcmd Question

Mar 3, 2008

Greetings all!

i am using the follownig sqlcmd command to output some data :

sqlcmd -E -S myServermyInstance -s "," -W -d myDB -Q " set nocount on select ltrim('""' + cast(PersonId as varchar) +
'-' + cast(DatabaseId as varchar) + '""') as 'URN-DBID','""' + EmailAddress + '""' as EmailAddress from myDB.dbo.myTable where
DTS > dateadd(dd, -7, getdate()) set nocount off " > "\tfsvrCompanyDatabase DevelopmentTestReports"myRecords-20080303.txt

The output looks something like this :

URN-DBID,EmailAddress
--------,------------
"5-1","blahblah@HOTMAIL.COM"
"264541-1","blahblah@HOTMAIL.COM"
"1107457-1","blahblah@HOTMAIL.COM"
"6133752-1","blahblah@HOTMAIL.COM"

Is it possible to keep the headnigs but remove the dashed line?

Your help would be much appreciated.

View 5 Replies View Related

Sqlcmd Question

Mar 3, 2008

Greetings all!

i am using the follownig sqlcmd command to output some data :


sqlcmd -E -S myServermyInstance -s "," -W -d myDB -Q " set nocount on select ltrim('""' + cast(PersonId as varchar) +
'-' + cast(DatabaseId as varchar) + '""') as 'URN-DBID','""' + EmailAddress + '""' as EmailAddress from myDB.dbo.myTable where
DTS > dateadd(dd, -7, getdate()) set nocount off " > "\mynetworkdrivemyCompanymyDatabase DevelopmentmyTestReports"myRecords-20080303.txt


The output looks something like this :

URN-DBID,EmailAddress
--------,------------
"5-1","blahblah@HOTMAIL.COM"
"264541-1","blahblah@HOTMAIL.COM"
"1107457-1","blahblah@HOTMAIL.COM"
"6133752-1","blahblah@HOTMAIL.COM"

Is it possible to keep the headnigs but remove the dashed line?


Your help would be much appreciated.

View 4 Replies View Related

Sqlcmd - View

Mar 21, 2006

how can i change the view in sqlcmd?

such as changing the font or the window size so that a command such as sp_tables will list on one row rather than several. Also I would only like to see 10 records or so at the same time. Currently in default setting, if i run sp_tables I can really only view the last few records.

thanks!

View 1 Replies View Related

What Is The Alternative To Go Under Sqlcmd?

Oct 8, 2006

Hi

I found it a bit annoying to type Go after some very simple query and I wonder is there a short cut to execute the query i type right after I press enter?

1> select * from Table
2> go <enter>

instead, how to you execute line 1 without entering go?

Thank you

View 7 Replies View Related

Backups From SQLcmd ??

Nov 9, 2006

I am working with SQL Express and found myself surprised when I tried to automate a backup... No Agent.

So I am trying to create a simple backup script to run from the command line and probably schedule through the scheduled task manager.

here is my problem. I get the following error :

Msg 2812, Level 16, State 62, Server FIREFLYSQLEXPRESS, Line 1
Could not find stored procedure 'B'.

Here is the batch file

sqlcmd -i c: emp estDBbackup.sql -o c: emp estoutput.txt -S FIREFLYSQLEXPRESS

And here is the sql input file :

BACKUP DATABASE [DNNDEV] TO DISK = 'C: emp estdnndev.bak' WITH NOFORMAT, NOINIT, NAME = 'dnndev-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

If I put a USE statement in front of my backup statement - the error message changes to

Could not find stored procedure 'U'.

So it would appear to list the first character encountered.

As a sanity check I created a similar process which does a select * from a table and I do not get an error saying :Could not find stored procedure 'S'. - instead all is well and I get my output and no error

If I try the backup command in SQLCMD interactively it works.

so what the heck am I missing ?

Thank you for any help...

View 4 Replies View Related

SQLCMD Error

Apr 14, 2008

Dear profetionlas:
I cannot run sqlcmd in my computer but i can connect to my SQL Server2005 through SSIS
This is the error I see .
Please give me some recommandation


C:Documents and Settings est>sqlcmd
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re 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.


I would like also metion that
1)windows firewall is off
2)All SQL server Services are running
3)This is my default instance also I have another named instance in the server

View 4 Replies View Related

Sqlcmd Not Recognized

Oct 9, 2006

Just installed SQL Server 2005 Express along with the Management Studio. The Management Studio, at least what I've seen so far, seems to work fine. But when I type "sqlcmd" at the command prompt, I get "sqlcmd is not a recognized internal or external command, operable program or batch file." What gives???

View 4 Replies View Related

SQLCMD Wont Start

Mar 1, 2008

 GreetingsI'm trying to follow the tutorial at http://www.asp.net/Guided%2DTour/s2.aspx  Part way through the demo it asks me to run the following command as part of building  a Demo web page access SQL  dataC:Program FilesMicrosoft SQL Server90ToolsinnSQLCMD"
-S "machine-nameSqlExpress"  I am then supposed to  paste  some script into  that window, but cant  get  that  far. The black Command window opens, but the SQL > prompt doesn't appear, and  the  window closes. I had previously installed a demo version of Web  Developer Express from my Action  Pack,  on a SBS Server  Premium 2003 R2, clean installation. When I got to the  above step  in the  demo there was no ...90Toolsinn folder so, I uninstalled the  Action  Pack version and the versions of  SQL server that  looked like they  did not belong to the  original  SBS Server installation.  I then downloaded the version from Microsofts website, and selected .Net SP1 and SQL Server  Express ( or lite or whatever it's called )  that were part of the  install dialog box. When checking  for  updates, the .NET  SP1 and  Visual Studio SP1 initial  failed. Rebooting  installed  .Net  SP1, but  I had  to do  a hard reboot as the  installation still wasn't  complete after 2 hours. I  assumed it was hung. After  restarting, I was  able  to go to  MSUpdates and  the  installation was successful.Currently the add remove programs shows  a ton of  SQL stuff, ..I'm guessing we  have  version conflicts or something  but I don't know.. Microsoft  SQL Server 2005Microsoft SQL Server Desktop engine  "                             Native Client  "                             Setup Support files  "                             VSS Writer Visual  Studio  seems to  run okay, but I  would  like to  be able to do SQL  database  part of the  tutorial, since I  don't know  much  about it as you can tell, and I would like to see it work with web sites. TimeTraveller    

View 1 Replies View Related

Sqlcmd And Escape Characters

Nov 22, 2007

The command below runs fine from within Management Studioexec sp_MSforeachtable @command1="exec sp_spaceused '?' "However, I'd like to run it via sqlcmd. I've tried variouscombinations of escaping the doulbe and single quotes but withoutsuccess.sqlcmd -E -Q "sp_MSforeachtable @command1="exec sp_spaceused '?'""Sqlcmd: 'exec sp_spaceused '?'""': Unexpected argument. Enter '-?'for help.Any ideas?Thanks,M

View 2 Replies View Related

Problem In Mirroring When Using SQLCMD

May 4, 2007

Dear all,

I'm having a problem with configure Mirroring. When I use graphical inteface to config the Mirror, it is ok. But when using sql, there are an error that I can not understand.

Here is the code:

ETVAR PrincipalServer DEMOPARTNERA
ETVAR MirrorServer DEMOPARTNERB
ETVAR WitnessServer DEMOWITNESS
ETVAR Database2Mirror AdventureWorksDW
go

N ERROR EXIT
go

:CONNECT $(PrincipalServer)

-- Mirroring ONLY supports the FULL Recovery Model
ALTER DATABASE $(Database2Mirror)
SET RECOVERY FULL
go

USE $(Database2Mirror)
go

CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO

:CONNECT $(MirrorServer)

CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5023)
FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO

:CONNECT $(WitnessServer)

CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5024)
FOR DATABASE_MIRRORING (ROLE=WITNESS)
GO

:CONNECT $(PrincipalServer)

BACKUP DATABASE $(Database2Mirror)
TO DISK = 'C:TechReady$(Database2Mirror).bak'
WITH INIT
GO

:CONNECT $(MirrorServer)

DECLARE @InstanceName sql_variant,
@InstanceDir sql_variant,
@SQLDataRoot nvarchar(512),
@ExecStr nvarchar(max)

SELECT @InstanceName = ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLServer')

EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWAREMicrosoftMicrosoft SQL ServerInstance NamesSQL',
@InstanceName, @InstanceDir OUTPUT

SELECT @ExecStr = 'EXECUTE master.dbo.xp_regread '
+ '''HKEY_LOCAL_MACHINE'', '
+ '''SOFTWAREMicrosoftMicrosoft SQL Server'
+ convert(varchar, @InstanceDir)
+ 'Setup'', ''SQLDataRoot'', @SQLDataRoot OUTPUT'

EXEC master.dbo.sp_executesql @ExecStr
, N'@SQLDataRoot nvarchar(512) OUTPUT'
, @SQLDataRoot OUTPUT

IF @SQLDataRoot IS NULL
BEGIN
RAISERROR ('Did not find the correct SQL Data Root Directory. Cannot proceed. Databases backed up but not yet restored.', 16, -1)
END

CREATE TABLE #BackupFileList
( LogicalName sysname NULL
, PhysicalName sysname NULL
, [Type] char(1)
, FileGroupName sysname NULL
, Size bigint
, MaxSize bigint
, FileId smallint
, CreateLSN numeric(25,0)
, DropLSN numeric(25,0)
, UniqueId uniqueidentifier
, ReadOnlyLSN numeric(25,0)
, ReadWriteLSN numeric(25,0)
, BackupSizeInBytes bigint
, SourceBlockSize bigint
, FileGroupId smallint
, LogGroupGUID uniqueidentifier
, DifferentialBaseLSN numeric(25,0)
, DifferentialBaseGUID uniqueidentifier
, IsReadOnly bit
, IsPresent bit
)

INSERT #BackupFileList
EXEC('LOAD FILELISTONLY FROM DISK = ''C:TechReady$(Database2Mirror).bak''')

UPDATE #BackupFileList
SET PhysicalName
= @SQLDataRoot
+ N'Data'
+ REVERSE(SUBSTRING(REVERSE(PhysicalName)
, 1, PATINDEX('%\%', REVERSE(PhysicalName)) -1))

DECLARE @LogicalName sysname
, @PhysicalName sysname

DECLARE FileListCursor CURSOR FAST_FORWARD FOR
SELECT LogicalName, PhysicalName
FROM #BackupFileList

OPEN FileListCursor

FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

SELECT @ExecStr = N'RESTORE DATABASE $(Database2Mirror)' +
N' FROM DISK = ''c:TechReady$(Database2Mirror).bak''' +
N' WITH MOVE ''' + @LogicalName + N''' TO ''' + @PhysicalName + N''''

FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @ExecStr = @ExecStr + N', MOVE ''' + @LogicalName
+ ''' TO ''' + @PhysicalName + ''''
FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName
END

-- NORECOVERY is required for Database Mirroring, replace is not.
-- Replace is used here solely to allow repetitive use of this script.
SELECT @ExecStr = @ExecStr + N' , NORECOVERY, REPLACE'

-- Useful for testing
-- Only return the string and then comment out the EXEC line below.
-- SELECT @ExecStr

EXEC (@ExecStr)

DEALLOCATE FileListCursor
GO

:CONNECT $(PrincipalServer)
SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns ONLINE
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
go

:CONNECT $(MirrorServer)
SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns RESTORING
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
go

:CONNECT $(MirrorServer)

ALTER DATABASE $(Database2Mirror)
SET PARTNER = 'TCP://DEMO:5023'
-- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5091'
-- SET PARTNER = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5091'
GO

:CONNECT $(PrincipalServer)

ALTER DATABASE $(Database2Mirror)
SET PARTNER = 'TCP://DEMO:5022'
-- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5092'
-- SET PARTNER = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5092'
GO

ALTER DATABASE $(Database2Mirror)
SET WITNESS = 'TCP://DEMO:5024'
-- SET WITNESS = 'TCP://Server.fully.qualified.dns.name:5090'
-- SET WITNESS = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5090'
GO

SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')

and the log is:

Msg 1452, Level 16, State 6, Line 3
The partner server instance name must be distinct from the server instance that manages the database. The ALTER DATABASE SET PARTNER command failed.
** An error was encountered during execution of batch. Exiting.

I Think the error is begin at:

ALTER DATABASE $(Database2Mirror)
SET PARTNER = 'TCP://DEMO:5023'
-- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5091'
-- SET PARTNER = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5091'
GO

:CONNECT $(PrincipalServer)

ALTER DATABASE $(Database2Mirror)
SET PARTNER = 'TCP://DEMO:5022'
-- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5092'
-- SET PARTNER = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5092'
GO

ALTER DATABASE $(Database2Mirror)
SET WITNESS = 'TCP://DEMO:5024'
-- SET WITNESS = 'TCP://Server.fully.qualified.dns.name:5090'
-- SET WITNESS = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5090'
GO

but I can not find out the solution. Turn back to grafical interface, everything is done

Please help!

View 6 Replies View Related

Sqlcmd.exe Login Problem

Jun 19, 2007

I can Add a connection to the database in VisualStudio 2005 in the Solution Explorer and access the database. Connection string copied from VS "Data Source=.SQLEXPRESS;AttachDbFilename=C:TempMyDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True".



If i start the command prompt and type:

sqlcmd -SlocalhostSQLEXPRESS -E -d c: empMyDB.mdf



I get the error "Login failed for user 'MyDomainPer'"



As I see it integrated authentication with the same user is used in both cases so they should give the same result. What am I doing wrong?

View 3 Replies View Related

How To Do A Sqlcmd Inline Commands

May 24, 2008



hi,

i am not sure if this forum is right place to ask this question..
i am trying right a dos batch file to do setup of sql commands run by sqlcmd , run some dos commands etc

net start mssql$server
sqlcmd -E .....


net stop mssql$server...

sqlcmd -E ....


in unix you can run isql with the sql commands place inside..


isql -Uuser -S server <<EOF
select 1
select 2
go
EOF


you can put above in a shell and it will run.

i am trying to do similar stuff in windows for sqlcmd.. how can i do it

only option i have is to create lot of .sql files and
run with -i option on sqlcmd..


can some body let me know how to do a inline commands in dos?
thx
AK

View 1 Replies View Related

Sqlcmd - Empty Serverlist

Dec 18, 2006

At the end of my rope here ... I was using SQL Express 2005 without trouble, until one day I couldn't access my database. I spent a day hunting down the issue without success. I uninstalled and reinstalled SQL Server many times, each time hoping it would somehow help ...

I can access SQL Server just fine through SQL Server Management Studio Express. I can get in by typing "sqlcmd -S {my machine name}SQLEXPRESS". However, I am working on a FoxPro application, and I cannot connect using FoxPro. It doesn't even seem to recognize the existence of SQL Server on the machine. Also, when I log in using sqlcmd, and type ":serverlist", I would expect my SQLEXPRESS instance to show up. It doesn't - the list of servers is empty. I suspect this is the problem, but have no idea what may be causing it.

Cumulatively, I've probably spent 3 work days trying to figure this out. Does anyone have any pointers or ideas that might help me figure out why ":serverlist" doesn't list my machineSQLEXPRESS, even though I can log in fine using Management Studio? Any help would be very much appreciated. Thank you,

matt

View 3 Replies View Related

Can Sqlcmd Be Used On A SQL Server 2000 Box?

Feb 7, 2008

I have a SQL server 2000 box. I saw a post some time ago in MSDN Forums that the sqlcmd utility could be used on SQL Server 2000 as well. However, when I tried to install sqlcmd using file SQLServer2005_SQLCMD.msi downloaded from Microsoft, I got following error:


Setup is missing prerequisites:- SQL Native Client

Does this mean that I need to install SQL 2005 Client? How do I make sqlcmd work on a SQL Server 2005 box?

Please advise. Thanks.

Jim

View 3 Replies View Related

Sqlcmd Exits With Error

Apr 20, 2006

As a complete beginner regarding SQL Server, I installed SQLExpress and lots of other things Microsoft told me to (without understanding why) accepting all the defaults offered.

I can make a connection to the server with "SQL Server Management Studio Express", create a database and add one two tables and views.

But all other things I've tried seem to go wrong without me having the slightest idea why, e.g.:

% SQLCMD.EXE -S swansqlexpress
HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF].

The above for instance when the protocol "Shared memory is enabled". If any combination of the other protocols are enabled, then I get a different error message.

I also cannot use the ODBC Management Tools to define a DSN connecting to SQLExpress ("Server does not exist or connection refused).

What I really want to do is to import data, using ODBC, from a different RDBMS into SQLExpress. Before writing my own scripts to do this, I had hoped that there would have been some GUI interface to SQLExpress with a nice little button saying "import external data". Is their such a beast?

Malcolm Agnew



View 1 Replies View Related

Cannot Access Database Using SQLCMD

Mar 16, 2006

Hello,

I am using SQL Server 2005 Express. I need to run a transact-sql script file to update the database. I am getting a "Login Failed" error when I use the SQLCMD utility. I use Windows Authentication.

Here is the command I use:
sqlcmd -S .SQLEXPRESS -d C:Progra~1MyProgramDataMyData.MDF -i MyUpg.SQL

Here is the message I am getting:

Msg 4060, Level 11, State 1, Server VAIOSQLEXPRESS, Line 1
Cannot open database "C:Progra~1 MyProgram Data MyData.MDF" requested by the login.
The login failed.
Msg 18456, Level 14, State 1, Server VAIOSQLEXPRESS, Line 1
Login failed for user 'VAIOUser Name€™.

Actually, it seems to work on my development PC, but have problem when using on a customer's machine where the product has been installed.

Any help will be appreciated.

Dots

View 7 Replies View Related

Using SqlCmd To Create Objects In CE

Jun 20, 2007

Hi, can we use sqlcmd.exe (successsor to osql) to run sql scripts against a compact edition database? Would like to use the output from Visual Studio Team Edition for Software Professionals to build the database structure on compact edition database.

thanks,

dan

View 4 Replies View Related

SQLCMD Does Not Work From Commandline

Jan 26, 2006

SQLCMD works in the Studio if SQLCMD Mode option is chosen.



OSQL and SQLCMD does not work from commandline and produces this constant burnt in message.

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



Cleint and Server have the same set of protocols

Studio has been added to the list of those permitted by Firewall

Tried to connect through tcp, lpc

Remote has been enabled.

SQLNativeClient does not have a property or method by name 'timeout'.

Let me know if something else is needed?

View 3 Replies View Related

Using Extended SQLCmd Commands With T-SQL

Jun 7, 2006

Are the extended command always executed even if it is contained within an IF statement???

I created test.sql script as follows:

if '$(CreateA)' = 'Y'
begin
:out $(myDir)testY.log
print 'CreateA is yes'
end
else
begin
:out $(myDir)testN.log
print 'CreateA is no'
end

At the command line prompt enter:
sqlcmd -i test.sql -v CreateA="Y" MyDir="C:Temp"

I expected the file C:Temp estY.log to be created with the line "CreateA is yes", which occurred successfully. However, C:Temp estN.log was created as well, but it was empty.

I deleted the log files and executed the following sqlcmd statement:
sqlcmd -i test.sql -v CreateA="N" MyDir="C:Temp"

The C:Temp estN.log was created with the line "CreateA is no", but C:Temp estY.log was also created.

Is there any way to conditionally execute a sqlcmd extended commands?

Thanks!

N Camp

View 3 Replies View Related

Restart Sql Express From Sqlcmd

Jul 12, 2006

Is it possible to restart the Sql Express engine from the sqlcmd utility?

Thanks

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved