Sqlcmd Question

SQLCMD
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 Replies !   View Related

---

SQLCMD
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 Replies !   View Related

---

SQLCMD
 

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 Replies !   View Related

---

SQLCMD.exe
 

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 Replies !   View Related

---

Need Example -- SQLCMD -y
Hi,

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

 

 

 

 

View Replies !   View Related

---

Sqlcmd Question
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 Replies !   View Related

---

Sqlcmd Syntax
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 Replies !   View Related

---

SQLCMD And The Use Of The :XML ON|OFF Command
 

Where do I place the XML: ON command in the sqlcmd string to get it to work. So far I have been fruitless.

 
I am trying to export out the single nVarChar(MAX) column from the table listed. It is over 8000 chars long and am

told by developers it could generate a file upwards of 50MB.

 
SO I figured the :XML sqlcmd command may do the trick.

 
If someone knows of a better way, please drop that to me also.

 
Here is the code I just attempted:


CODE===============

 
EXEC Master..xp_CmdShell 'sqlcmd  -S(local) -E -Q":XML ON SET NOCOUNT ON SELECT * FROM MyDB.dbo.MyTable" -

oC:MyFile.txt -h-1'

 
CODE===============

 
OK, a bit of history here. The column we are pulling from is set as a nVarChar(MAX).

 
The column is populated by another SP that dynamically builds an XML string. Some of the XML strings that are built

can become very large (from our dev team, they can generate a file nearing 50MB in some cases.

 
Now the XML string that was dynamically build and which is contained in the nVarChar(MAX) field looks GREAT!

 
I am able to use sqlcmd to export it to a text file and once in the file ... it looks GREAT there too.

 
I used the -y0 param (width_display) w/ sqlcmd to generate the file and overcome the default 256 character limit on

the file size:


CODE===============

 
EXEC Master..xp_CmdShell 'sqlcmd -S(local) -E -Q"SET NOCOUNT ON SELECT * FROM M360.dbo.tBackupAssociationAsXML" -

oC:BackupAssociationAsXML.txt -h-1 -y0'

 
CODE===============

 
The issue we have here is that I am reading the -y0 param is good up to a 1MB file.

 
The file size generated by what I currently have in the nVarChar(MAX) field was only 290KB.

 
Here is what I pulled from BOL:

 
  If display_width is 0, the output is truncated at 1 MB. You can use the :XML ON command to prevent truncation of 

  the output. The :XML ON command is described later in this topic.


So now I am back to needing to use the :XML command and then of course needing to know where it is place in the

sqlcmd string.

 
I am also reading in BOL that:

 
  Each command must be on a separate line. A command cannot be followed by a Transact-SQL statement or another  

command.

 
So how is this accomplished in a stored procedure using SQLCMD?

 
Thanks for all the help in advance!

View Replies !   View Related

---

SQLCMD Error
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 Replies !   View Related

---

Backups From SQLcmd ??
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 Replies !   View Related

---

Sqlcmd Not Recognized
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 Replies !   View Related

---

Sqlcmd - View
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 Replies !   View Related

---

SQLCMD Wont Start
 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 Replies !   View Related

---

Sqlcmd And Escape Characters
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 Replies !   View Related

---

Fail To Connect Using Sqlcmd
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 Replies !   View Related

---

Using Extended SQLCmd Commands With T-SQL
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 Replies !   View Related

---

Can Sqlcmd Be Used On A SQL Server 2000 Box?
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 Replies !   View Related

---

Cannot Access Database Using SQLCMD
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 Replies !   View Related

---

Using SqlCmd To Create Objects In CE
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 Replies !   View Related

---

Problem In Mirroring When Using SQLCMD
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 Replies !   View Related

---

How To Do A Sqlcmd Inline Commands
 

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 Replies !   View Related

---

Sqlcmd.exe Login Problem
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 Replies !   View Related

---

SQLCMD Does Not Work From Commandline
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 Replies !   View Related

---

Sqlcmd - Empty Serverlist
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 Replies !   View Related

---

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

Thanks

View Replies !   View Related

---

Sqlcmd Exits With Error
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 Replies !   View Related

---

ALTER TABLE From Sqlcmd Script
Hi,I'm trying to add a column to a table, then update that column with aquery. This is all within a single batch. Sqlcmd gives me an error onthe update, saying "invalid column xxx", because it doesn't know thecolumn got added. We used to get around this in "osql" by using theEXECUTE command, like: EXEC ("ALTER TABLE tbl ADD newfield varchar(255)not null default ' '")However, it looks like sqlcmd actually checks each query within thescript before it starts running, and throws the error because the fieldisn't there at the time.If need be I can just do a SELECT INTO and add the column there, butit's a pain in the butt and I'm moving a LOT of data just to do what Iwant. And no, I can't go back to where the table is created and addthe column. Does anyone have any suggestions? TIA!- Jeff

View Replies !   View Related

---

Problem With Passing Password To Sqlcmd
I don't know how to pass strange looking passwords to sqlcmd. In sql I use'pass' and that works fine, but what to do with command line applicationslike sqlcmd?if password contains no " sumbol I can use "password"but what if the password contains " . / - or spaces?ex.password = 'a-d ". a"'sqlcmd -S myServer -U myUser -P a-d ". a" -d "myDb" -i"C:myProcedure.sql" -o "C: empmyOutput.out" --->whon't work..How to pass strange looking passwords to sqlcmd?

View Replies !   View Related

---

SQLCMD Can Not Connect To SQLExpress 2005
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 Replies !   View Related

---

Trapping Sqlcmd Errors When Executed From A Job
Hi I am running some scripts in files using sqlcmd via a SQL Server Agent job. If sqlcmd generates an error (for example if it is unable to connect) then the job fails. However, if the T-SQL within the script is invalid (syntax, name resolution etc etc) the job completes reporting success. If sqlcmd is invoked directly via the query window then no error is raised however there is a result set returned reporting the error. Anyone know why and whether is it possible to get the error to be recognised by the job? invalid_sql.sql--The below is not actually valid SQL.do SOME stuff, innit! sqlcmdEXEC master.dbo.xp_cmdshell "sqlcmd -S my_server -i C:invalid_sql.sql" Cheers

View Replies !   View Related

---

SQLCMD Missing Input File Name
hello,
I am new to SQL sever and would like to connect to a particular database on the server using SQL. I have looked at various SQL sites with how to and none mention where I can locate the Input File name.

View Replies !   View Related

---

Sqlcmd Opens Blank And Then Just Closes.
Hey guys i have installed SQL Server Express and getting a problem. Every time I use the runcommand and enter sqlcmd. It opens the window but then I cannot enter any information into it. And after a while the window just closes.

Anyone know the reason for this?

Dallr

View Replies !   View Related

---

Calling SQLCMD From Within An Executing Procedure
Is there a way to call SQLCMD from within an executing procedure?
 

View Replies !   View Related

---

How Do I Install Sqlcmd.exe In My SQL Server Client
I have SQL Server 2000 client installed in my machine and I need to call stored procedures thru sqlcmd utility. When I tried using this utility, I got an error message from command prompt sqlcmd command does not exist.

I have osql utility in my client version, but i need to have sqlcmd utility.



Please advice how do I install sqlcmd.exe in my SQL Server client?

View Replies !   View Related

---

SQLCMD - Copy All Procedures &&amp; Code
Hi All,

Our Management studio for sql2005 is damaged and we only have way to copy through SQLCMD - command line.

Please can any one provide me the script to copy all the user defined Procedure , Functions & Triggers code from database.

Thanks in advance

 

 

 

View Replies !   View Related

---

Deployment 'utility' Script Using Sqlcmd
Hi all,



I'm looking at creating a sample utility script that will invoking
scripts to deploy some SQL code. For example, a utlity script that will
run a SQL script, and on successful completion, execute the next script.



Having not used SQLCMD at all before, and being very new to SQL2005
(< 1 month) please guide me if there is a better way of invoking
this... For example, a way of avoiding the xp_cmdshell invocation!



The following code invokes a script, but I'm trying to find a way of
getting a return code back from sqlcmd, so I can progress and do the
next, or fail if the return code <> 0 (success).



[code]

--Process to create DB, Tables, and Stored Procedures

set nocount on

DECLARE

    @Error            int,

    @ExecCommand    varchar(512),

    @FullFilePath    varchar(255)



--create the database

BEGIN TRY

    SET @FullFilePath    =
'D:DocumentationProjectsIntegration ServicesBIDS ProjectsTesco DNF
Integration ServicesTescoDNF ProductPromoSQL CodeOBJECTSCreate DB
TescoDNF_SSISPackageManager.sql'

    SET @ExecCommand    = 'xp_cmdshell ''sqlcmd -S RgalbraithSQL2005_1 -i "'+@FullFilePath+'"'' '



    SELECT @FullFilePath

    UNION

    SELECT @ExecCommand



    EXEC (@ExecCommand)

    SELECT @@ERROR

    SELECT @Error

END TRY

BEGIN CATCH

    SELECT

        ERROR_NUMBER() AS ErrorNumber,

        ERROR_SEVERITY() AS ErrorSeverity,

        ERROR_STATE() AS ErrorState,

        ERROR_PROCEDURE() AS ErrorProcedure,

        ERROR_LINE() AS ErrorLine,

        ERROR_MESSAGE() AS ErrorMessage;

    GOTO ErrorAbort

END CATCH







ErrorAbort:

[/code]

View Replies !   View Related

---

How Not To Display Number Of Records In SQLCMD
I would like to run a SELECT statement using the SQLCMD, however, I do not like the count of records be displayed after the execution.  How do I do that?
 
Thanks in advance.

View Replies !   View Related

---

How To Pass Strange Looking Passwords To SQLCMD ?
I have problems with passing strange looking passwords from my win.form application to sqlcmd.
I tried to do this:
sqlcmd -S myServer -U myUser -P  2pQ-""xELfG~fze<= -d "McSpalDB" -i "C:myProcedure.sql" -o "C: empmyOutput.out"
but it fails, so I tried to encapsolate the passwoard in "":
sqlcmd -S myServer -U myUser -P  "2pQ-""xELfG~fze<=" -d "McSpalDB" -i "C:myProcedure.sql" -o "C: empmyOutput.out"
but it fails too, because i have "" in my pass, since " isn't a forbiden symbol.

How to pass strange looking passwoards to sqlcmd?

View Replies !   View Related

---

Failed Remote Connections With Sqlcmd??
I'm trying to connect to an instance of SQL Server from a remote client using sqlcmd.  I've read the issues on the developer edition of SQL server and the fact that it doesn't allow remote connections.  I've followed the advice in the kb article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

This article was very informative and after following all the adivce, including the bit on the firewall, I am able to connect to the SQL Server from the remote machine via SQL Server Management Studio.  (Basically I have identical machines, both running Server 2003 operating system and SQL Server 2005 with visual Studio 2005).  Even though I can connect via SQL Server Management Studio, I still cannot connect via sqlcmd?

 

The error I get is:

 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.

If I run the same command line on the local machine it runs fine.  Note that I'm trying to execute sqlCmd on the remote machine (i.e. a client executes a batch of Transact commands contained in a file on the remote SQL Server.)  I've created an exception in the firewall to execute this sqlcmd on the remote machine but still get the same error. 

 

I've also tried to execute sqlCmd locally (on the client rather than using the instance of sqlCMD on the remote SQL Server) and have it execute the script on the remote database and still get the same error. 

Basically I'm asking why isn't sqlCmd and MSSQLSERVER configured together. 

Oh, I've also enabled ad-hoc queries on the MSSQLSERVER gussing this might be the problem but it didn't help.

I hope I've explained enough about the problem so that someone can answer me. 

 

Thanks

View Replies !   View Related

---

Cannot Paste Into Sqlcmd Please Help? SQL Server 2008
"When attempting to run this installer (SQLCMD T-SQL execution command line utility), I get the DOS screen but not the 1> command prompt. If I leave the screen alone, the DOD command window closes after about 5 seconds."C:Program FilesMicrosoft SQL Server90ToolsinnSQLCMD" -S "machine-nameSqlExpress""
nuno vieira




Hi All
ive just got a new machine and not had this problem before, at one point the error which appeared briefly before the command prompt window disappeared said it was the namepipe, so i enabled this, that didnt work so i uninstalled, and reinstalled SQL Server 2005.
The next error message i received when the command prompt was closing was (in brief)
HRESULT 0xFFFFFFF Level 16, State 1
an error occurred whilst establishing a connection to the server
Sqlcmd: Error: Microsoft Native Client : Login timeout expired.

I have just updated to sql server 2008 and the same thing happened. I cant paste into the SQLCMD window and I havent got a 1> command prompt.
I tried to drop down into dos prompt and it wouldn't accept it said "the filename, directory name or volume syntax is incorrect".
I turned on iis, ive tried everything I can think of, i have come to the conclusion it must be something to do with the version of Windows Vista Home Premium I am running, I haven't had any problems on any other machines XP or Vista.
Any help would be gratefully accepted.
Cheers
sgblank

View Replies !   View Related

---

Sqlcmd –S .\%INSTANCENAME%... Fails After Install
I'm new to sql, so go easy ;)

I'm attempting to install sql for a BCM 2007 installation, and I'm using this document: http://www.microsoft.com/downloads/details.aspx?familyid=f24267ee-9ad5-4be5-b888-c9a50ae395ca&displaylang=en

Now, I'm trying to do this on Windows 2003 Standard server, which may be the problem, but perhaps not?  Anyway, the command I'm attempting to run is:
sqlcmd €“S .\%INSTANCENAME% €“E €“Q €œexec sp_grantlogin [%DOMAIN%\%USER%]; grant create database to [%DOMAIN%\%USER%]€?
 
which is basic enough (and I did replace the %%'s with the correct info) but I'm getting this error back:
 
Sqlcmd: '-S': Unknown Option. Enter '-?' for help.
 
Any ideas???
 
edit: I did just notice this from the sql express readme, so I may try reinstalling... advice would still be appreciated!


 
3.10 Installing SQL Server Express on a Windows Domain Controller
Security Note:
We recommend against running SQL Server Express on a domain controller.
It is possible to install SQL Server Express on a Windows domain controller; however, it cannot run on a Windows Server 2003 domain controller as Local Service or Network Service. SQL Server service accounts should run as Windows domain user accounts. It is also possible to install SQL Server service accounts to run as Local System, but this option is not recommended.
Do not change the role of the server after you install SQL Server Express. For example, if you install SQL Server Express on a member server, do not use the Dcpromo tool to promote the server to a domain controller. Or, if you install SQL Server Express on a domain controller, do not use Dcpromo to demote the server to a member server. Changing the role of a server after you install SQL Server Express can result in loss of functionality and is not supported.

View Replies !   View Related

---

Question About Instance Name On Sqlserver 2005 And Sqlcmd
Hello,
I am using sqlcmd for SQL server 2005. I install sql2005 with default instance name. It should be MSSQLSERVER.
However, when using sqlcmd, like sqlcmd -S (local)MSSQLSERVER, it always aborts and suggests a wrong connection string.
I try to find any instance name under servername, but I can't find a command argument with sqlcmd.
So how can I solve the connection string problem? I can connect to the server using "sqlcmd -s (local)" though.
Or, how can I find the instance name of the server?  Or maybe any gramma error using sqlcmd to access instance name?
 
Thanks for your any inputs.
 
Regards,
Aaron

View Replies !   View Related

---

Export Table To A Text File Using Sqlcmd
How do i use sqlcmd to export the rows in a table to a comma-delimited text file?

Thanks,
Sean

View Replies !   View Related

---

Working With SQLCMD: Variables And Table Permissions
Nice simple problem, trying to set the guest user with SQLCMD to have select permissions on an object:
-- Code below
-- Line below should read "colon"setvar MAD guest
: setvar MAD guest

SELECT $(MAD)

GRANT SELECT ON OBJECT::dbo.ErrorLog TO $(MAD)

 
 
Unfortunatly I keep getting the following error being thrown:

Msg 207, Level 16, State 1, Line 1

Invalid column name 'guest'.

 
 
Any ideas?
 
Thanks,
 
d

View Replies !   View Related

---

SQLCMD Error - Working With Variables And Synonyms
Hi All,
 
Been doing some testing with SQLCMD and variables for installation scripts, enclosed below is some source code.
The one problem, I've been getting is the following error:

Incorrect syntax near 'certification'
 
This has been tested only in SSMS utilising the run in SQLCMD mode.
 
This error only occurs on the SQLCMD variable replacement, where it replaces $(Cert_Schema) for the word Certification.
Running the CREATE Synonym with the word Certification hard-coded into the script works.
 
Any ideas?
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)   Mar 23 2007 16:28:52   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
 
Best Regards,
 
D
 

USE [Testing]

GO

/****** Object: Table [certification].[Table_1] Script Date: 01/03/2008 11:22:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [certification].[Table_1](

[PKId] [smallint] NOT NULL,

[Name] [nchar](10) NULL,

CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED

(

[PKId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]
 
--- SQLCMD Script below:
-- line below should read, colon setvar cert_schema 'certification'

 etvar Cert_Schema 'certification'
-- The line below fails to work for some reason

CREATE SYNONYM crt_asa FOR $(Cert_Schema).Table_1

DROP SYNONYM crt_asa
 
-- Hard-coded equivilant line works

CREATE SYNONYM crt_asa FOR certification.Table_1

DROP SYNONYM crt_asa

View Replies !   View Related

---

Problems With SQLCMD Prompt SQL Express 2008
"When attempting to run this installer (SQLCMD T-SQL execution command line utility), I get the DOS screen but not the 1> command prompt. If I leave the screen alone, the DOD command window closes after about 5 seconds."C:Program FilesMicrosoft SQL Server90ToolsinnSQLCMD" -S "machine-nameSqlExpress""
nuno vieira




Hi All
Can someone help me please?
ive just got a new machine and not had this problem before, at one point the error which appeared briefly before the command prompt window disappeared said it was the namepipe, so i enabled this, that didnt work so i uninstalled, and reinstalled SQL Server 2005.
The next error message i received when the command prompt was closing was (in brief)
HRESULT 0xFFFFFFF Level 16, State 1
an error occurred whilst establishing a connection to the server
Sqlcmd: Error: Microsoft Native Client : Login timeout expired.

I have just updated to sql server 2008 and the same thing happened. I cant paste into the SQLCMD window and I havent got a 1> command prompt.
I tried to drop down into dos prompt and it wouldn't accept it said "the filename, directory name or volume syntax is incorrect".
I turned on iis, ive tried everything I can think of, i have come to the conclusion it must be something to do with the version of Windows Vista Home Premium I am running, I haven't had any problems on any other machines XP or Vista.
Any help would be gratefully accepted.
Cheers
sgblank

View Replies !   View Related

---

Unable To Get SQLCMD Utility To Work Properly.
I try to go to a command prompt to get this to run right with no luck.  Is there something that I am not doing right?  It gives me the help library, but thats about it.  Also, I have dealt with Oracle in the past.  And was wondering is there a way to access the SQLCMD utility on a workstation with a login of some kind?

--David

View Replies !   View Related

---

Unable To Connect To Sql Server Express Using SQLCMD
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 Replies !   View Related

---