OK, so here's the scenario. CruiseControl.NET (Version : 1.5.7256.1) runs a project to rebuild a database. The CruiseControl.NET windows service is running as a windows user we created for CC.NET. There are several tasks in the project but they all run fine. However, one of the tasks that runs is a console based utility I wrote in C# th...
i would like to have a BAT file open a sql server script currently i have this code in the sql file: declare @path varchar(255), @mydb varchar(50) SELECT @mydb = 'timeclockplus' select @path = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\' + @mydb + '-' + convert(varchar(8),getdate(),112) + '.bak' BACKUP DATABASE @mydb T...
I'd like to issue the following command through SQLCMD: IF DB_ID('My_DB') IS NOT NULL DROP DATABASE My_DB There are a few caveats though... The database name is dynamic The program which is running the command (BuildForge) will not replace a variable if it appears within single quotes So, I can't use: -Q"IF DB_ID('${db_name}') IS...
Coming from an Oracle background, Oracle's SQLPlus would let you indicate a variable. If the variable wasn't set, you'd be prompted to provide a value. I'm using SQLCMD, using the $([var_name]) syntax. In SSMS SQLCMD mode, I get: A fatal scripting error occurred. Variable tbl_name is not defined. ...for trying to run: SELECT ...
I'm using visual studio 2010 database edition and running a schema compare against a SQL database instance to sync up new objects on the database which arent yet in my project (dbproj). My solution contains many projects some of which reference each other via database references. When I write updated from my schema compare to my target p...
We're using Visual Studio Database Professional and it makes heavy use of SQLCMD variables to differentiate between environments while deploying. I know there's several directives available for setting context (like :connect for server name). Is there a way within the script itself to force SQLCMD mode for execution? Part of our deploym...
When using Invokve Expression sqlcmd -i script.sql it seems to only display the first 2 lines returned. When I run in Query Analyser the script it returns: (2 rows affected) Changed database context to 'ShopStore'. (5 rows affected) Changed database context to 'ShopStore'. However from powershell I only get: (2 rows affected) Changed...
Hi All I am Writing a Batch Script Which has to read a set of SQL Files which exists in a Folder then Execute Them Using SQLCMD utiliy. When I am Trying to execute it does not create any output file. I am not sure where I am wrong and I am not sure how to debug the script. Can someone help me out with script? @echo off FOR %F IN (C:\S...
How do I write and run an upgrade script for .sdf databases? Would sqlcmd be able to do that? I am not trying to upgrade the version of the database file. I am trying to upgrade the schema and data inside of it. ...
I can't seem to get NAnt to abort my build when it encounters an error. Here's one of many sqlcmd.exe executions: <property name="test" value=""/> <exec program="sqlcmd.exe" resultproperty="test" failonerror="true" basedir="${sqlcmd.dir}" commandline="${commandline.script}" verbose="true"> <arg value="${filename}" /> <arg v...
Im trying to write a powershell script which will execute sqlcmd.exe to run a sql script. The script contains a SQLCMD variable which I want to pass in on the command line via sqlcmd's -v switch. The problem is that powershell is doing something weird when I pass a path as the value of one of my variables which in turn causes the SQL scr...
I have a script file e.g. test.sql. I want to call this from another script, say caller.sql, in sqlcmd mode using :r test.sql. This works fine, but I want to use a scripting variable in test.sql. When I call test.sql from caller.sql I can set the scripting variable and all is well. However, I want to use a default value for the scrip...
Hi all I have a pretty simple question (and these are typically the ones I spend most of my time tearing my hair out about). I am using a batch file to execute all the .sql queries that are in the same directory as the batch, and to save all their results to various .csv file. Here is my code: @echo off REM Check that all parameters ...
Hi all I am sure these has been asked before but cannot find clear instruction how to create a batch file lets call it "Update Database" this batch file should Execute sql scripts located in different folders Execute another 3 bat files. Any quick examples how to do it?Never done it before thanks a lot EDITED Can I do this? :On ...
Is it possible to pass parameters to a SQL Server script? I have a script that creates a database. It is called from a batch file using sqlcmd. Part of that SQL script is as follows: CREATE DATABASE [SAMPLE] ON PRIMARY ( NAME = N'SAMPLE', FILENAME = N'c:\dev\SAMPLE.mdf' , SIZE = 23552KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LO...
SQLCMD supports the -s parameter to specify the column separator, but I couldn't figure how how to represent the tab (CHAR(9)) character. I have tried the following but both don't work: sqlcmd -S ServerName -E -Q"select * from mytable" -s"\t" -o results.txt sqlcmd -S ServerName -E -Q"select * from mytable" -s'\t' -o results.txt Any i...
We have batch files to compile all the sql scripts in a folder against a database. Problem is that we have a couple of thousand scripts and it makes an output file for each script. It makes it difficult to check for errors and a lot to do cleanup on. Is there any way to output all of the results to one file? Here is the script we use: ...
I'm trying to export data from SQL Server into CSV format. I have a bat task to do this that's run at regular intervals. Command is: C:\Program Files\Microsoft SQL Server\[...]\SQLCMD.EXE" -d [db details] -i c:\export.sql -o c:\export.csv -s"," -W The SQL file is just a SELECT * from a view. This works except that some of the rows ...
Is there a way to tell if a variable is defined on command line using SQLCMD? Here is my command line: sqlcmd -vDB="EduC_E100" -i"Z:\SQL Common\Admin\ProdToTest_DB.sql" Inside ProdToTest_DB.sql I would like to set some sort of conditional IF to check if the variable does not exists and define it if it does not. IF NOT $(DB) :set...
I am having 2 instances of SQL Server 2008R2 and instance of SQL Server Express 2008. SQLCMD, etc., works [1]. But "SQLCMD -L" and "OSQL -L" fail [2]. Why? What am I missing in SQLCMD use? How to use SQLCMD to get servers? [1] Test results of SQLCMD >sqlcmd 1> use AdventureWorks2008R2; 2> SELECT TOP(2) BusinessEntityID, FirstNa...