Running SQL Scripts

[Rahvyn]

Hello All;

I am having an issue running SQL scripts to create a database and the tables within. My sequence of events on the install is this:
1 - Install SQL Express
2 - Run batch file with the following commands:
@echo off
sqlcmd -S .\SQLEXPRESS -i CreateDatabase.sql -U sa -P sql

The CreateDatabase.sql file contains all the sql to do what I need. The batch file is called from the On Post Install event. The SQL Express install goes fine, and the batch file gets called, but I recieve the following error in the cmd window when it executes the batch script:
'sqlcmd' is not recognized as an internal or external command, operable program or batch file.

However, if I execute that batch file after the install has completed, it works just fine. Can I not call sqlcmd from a batch file? Is there an easier way to execute SQL statments during an install?

[Jason Pate]

Rahvyn,

I have not tried "sqlcmd", you can post the code around your launch of your bach file but I would also suggest you look at SQL Packager (http://www.red-gate.com/products/SQL_Packager/index.htm). I use it to install my SQL 2000/2005 DB.

[pww]

I guess this error means sqlcmd.exe can't be found.

In the .bat file try to replace "sqlcmd" with the full path to sqlcmd.exe enclosed in double quotes.

Alternatively you may use the File.Run script action instead of a .bat file.

[Rahvyn]

Well, I changed the way I am doing this and just put the database creation into .sql files, which I run from a C# command line application. Still having a problem somewhere, but not sure where yet. Something I think to do with the table creation, as the database creation portion succeeds.

Here is the code I am using if anyone is interested:

static void Main(string[] args)
{
string fileName = args[0] + @"\Database.sql";
ProcessStartInfo info = new ProcessStartInfo("sqlcmd", @" -S .\SQLEXPRESS -U sa -P sql -o C:\sqlout.txt -i """ + @fileName + @"""");
info.UseShellExecute = false;
info.CreateNoWindow = true;
info.WindowStyle = ProcessWindowStyle.Hidden;
info.RedirectStandardOutput = true;
Process p = new Process();
p.StartInfo = info;
p.Start();
}

There is more, but that is the main execution portion. I call the exe and pass in the temp folder created by SF. Seems like the database gets created, but when I go to create a table, it cant find the database I just created, and decides to create the table on the master database.

[Jason Pate]

Have to force the script to change database, in sql there is a

USE

command that can be used to force the script to apply to a specific database.

[Rahvyn]

Yeah, I'm using that. Here's an example of a script:

USE [SmartShip]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [SmartShip].[dbo].[BatchConfigurations](
[BatchID] [uniqueidentifier] ROWGUIDCOL NULL CONSTRAINT [DF_BatchConfigurations_BatchID] DEFAULT (newid()),
[BatchName] [varchar](50) NULL,
[FileType] [nchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO


I am wondering if when I create the database, if there is something I need to set as far as permissions. Even when I re-run the setup, and it re executes the scripts, they still fail to find the database that definetely exists, as I can see it using a cmd window and sqlcmd. But this doesnt seem to make much sense, as I am using the sa account to access the sqlcmd.

[Jason Pate]

Are you getting an error returned?

[Rahvyn]

Yes:

Msg 2702, Level 16, State 2, Server DAVEXP\SQLEXPRESS, Line 1
Database 'SmartShip' does not exist.

Getting that in the output file. Its like it cant find the database. But I can run both of those command lines from above directly in the cmd window, and they will succeed. Perhaps I should drop this post on a C# forum, but was hoping someone here had messed around with something like this before.

[Jason Pate]

I man this is why I use Red-Gate SQL packager, with that error message has to be one of two things either the DB create failed or your user cant connect to it. Might try and create the empty DB and skip the create db script as a debug test.

[Rahvyn]

Finally figured it out. The second command was executing before the first had finished, so after adding p.WaitForExit(), it now works just fine. Thanks for all the suggestions and help.

[boopathiraja]

I guess this error means sqlcmd.exe can't be found.

In the .bat file try to replace "sqlcmd" with the full path to sqlcmd.exe enclosed in double quotes.

Alternatively you may use the File.Run script action instead of a .bat file.


Actually i m also facing this error..how to create file.run script and how to use it


i am in a critical position..i must attach the database through command prompt . iwrote the sql query in a.sql file..and installing the sqlserver2005 express from command prompt...the sequence is

1. install sql express ...
(using sqlcmd command)
working
2.attach DB
(sqlcmd error ..)

when running the batch file again it is working


pls help me