Alternatives to sqlcmd in sql server 2008 SP2

Question

Are there any alternatives to the sqlcmd utility for creating a db out of a .sql file?

I get these syntax errors due to characters present in the table data columns. My .sql file is 8GB in size and even I can fix a record that sqlcmd thinks is bad, it takes a while to save and re-open the file if there are more records with columns containing data like a % sign or "(" , which sqlcmd does not like. I used the On Error Ignore directive but that did not help much.

Here is my other post regarding the same issue:

sqlcmd runs into error while parsing my .sql file

Your problem is not sqlcmd, it's that you have a single 8GB SQL script. I understand that it may be nice to have everything in one place for deployment (I assume that's your goal here), but it's simply impossible to maintain and troubleshoot. Why not split it into multiple files, one per object or per object type (tables.sql etc.) and have a wrapper script to execute them all? — Pondlife, Jul 27 '12 at 20:24
It would be better to fix the problem with SSMS when it generates the script than to ignore statements with a '%' and '(' in them. Isn't the real question how to make SSMS script runnable insert statements when the data contains a '%' or a '('? — James L., Jul 27 '12 at 20:56
@Code12 : does your file need to be actual SQL statments? If not, there are other ways to extract the schema and data such that the size _should_ not be a problem and the data itself should be fine as it would be in a binary format. I am thinking of SQL Server Data Tools (SSDT) ( http://msdn.microsoft.com/en-us/data/gg427686 ). It is free, can hook into Visual Studio Express (also free) and allows you to extract all, or some, tables (with or without data) into a binary `.dacpac` file that can be imported via another connection string. — Solomon Rutzky, Sep 26 '14 at 20:20
Maybe you could use a .sql file to create the schema and use bcp to insert the data? http://msdn.microsoft.com/en-us/library/aa196743(v=sql.80).aspx — David Atkinson, Jul 29 '12 at 11:54

score 0 · Answer 1 · answered Sep 26 '14 at 19:39

This is my custom solution. It's just a powershell script that can execute gigantic .sql files.

$filePath = "GiganticFile.sql";
$stream = [System.IO.File]::OpenText($filePath);
$connection = New-Object System.Data.SqlClient.SqlConnection;
$connection.ConnectionString = "Persist Security Info=True;User ID=<user>;Password=<password>;Initial Catalog=<database>;Data Source=<server>";
$connection.Open();
$command = New-Object System.Data.SqlClient.SqlCommand;
$command.Connection = $connection;
$statement = "";
$statementsProcessed = 0;
$totalBytes = (New-Object System.IO.FileInfo($filePath)).Length;
$bytesProcessed = 0;

while (-not $stream.EndOfStream) {
    $line = $stream.ReadLine();
    $bytesProcessed += $line.Length;
    if ($line.Trim() -eq "GO") {
        try {
            $command.CommandText = $statement;
            [void]$command.ExecuteNonQuery();
            $statement = "";
            "{0} statements processed ({1:f} MB) - {2:f}%" -f (++$statementsProcessed), ($bytesProcessed / 1024 / 1024), ($bytesProcessed / $totalBytes * 100);
        }
        catch {
            "!!! ERROR !!!";
            $Error;
        }
    }
    else {
        $statement += $line + [Environment]::NewLine;
    }
}    
$stream.Close();

Output sample:

137238 statements processed (544.54 MB) - 7.57%
137239 statements processed (544.55 MB) - 7.57%
137240 statements processed (544.56 MB) - 7.57%
137241 statements processed (544.57 MB) - 7.57%
137242 statements processed (544.58 MB) - 7.57%
137243 statements processed (544.59 MB) - 7.57%
137244 statements processed (544.60 MB) - 7.57%
137245 statements processed (544.61 MB) - 7.57%
137246 statements processed (544.63 MB) - 7.58%
137247 statements processed (544.64 MB) - 7.58%
137248 statements processed (544.65 MB) - 7.58%
137249 statements processed (544.66 MB) - 7.58%
137250 statements processed (544.67 MB) - 7.58%
137251 statements processed (544.68 MB) - 7.58%
137252 statements processed (544.69 MB) - 7.58%
137253 statements processed (544.70 MB) - 7.58%

score 0 · Answer 2 · answered Jul 11 '18 at 09:12

0

There is a free tool "SQLSPlus" (on http://www.memfix.com ) which is like Oracle SQLPlus for SQL Server. Works with all SQL Server versions. It should do the job.

answered Jul 11 '18 at 09:12

Ron Warshawsky

314
2
11

Alternatives to sqlcmd in sql server 2008 SP2

2 Answers2