Executing a SQL script using Windows Powershell

Question

I have a "SQL" script that needs to be run through Windows Powershell in order to create a database. The script already has the drop and create function in so no need to add a database name at the end of the command line. when trying to execute the command line I get an error that says

"Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server
The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)
At line:1 char:14
+ invoke-sqlcmd <<<<  -inputfile "c:\A.sql" -serverinstance "Louis_dev\MSSQLSERVER"
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Invoke-Sqlcmd :
At line:1 char:14
+ invoke-sqlcmd <<<<  -inputfile "c:\A.sql" -serverinstance "Louis_dev\MSSQLSERVER"
    + CategoryInfo          : ParserError: (:) [Invoke-Sqlcmd], ParserException
    + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand"

not sure if it is the invoke command and I have already followed the steps in this link How to execute .sql file using powershell? . Is there by any chance that there could be a workaround from this error. SQLSERVER instance has been configured to LocalSystem and not to Network instance

Your sql instance have enabled tcp/ip and named pipes protocols? — CB., Jan 14 '13 at 10:48
According to this my SQLServer instance is installed as LocalSystem. must i change this to Network Service? — Louis, Jan 14 '13 at 11:01
check this: http://msdn.microsoft.com/en-us/library/bb909712(v=vs.90).aspx and this http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/31d57870-1faa-4e14-8527-ce77b1ff40e4 — CB., Jan 14 '13 at 11:04
Are you able to use other tools successfully i.e. SSMS, sqlcmd.exe and even setup a test DSN? — Chad Miller, Jan 14 '13 at 16:49
yes all that i am able to do. but unfortunately the task at hand is to execute through Powershell — Louis, Jan 15 '13 at 04:22
So in addition to the normal invoke-sqlcmd I had to enter a tcp after -severinstance tcp:"Servername","portnumber"\"servicename". now it opens the script but it tells that i do not have permission to change or alter the database. Is there a line I can use that you can specify a username? Currently this is what I have: invoke-sqlcmd -inputfile "C:\A.sql" -serverinstance tcp:"LOUIS_DEV-PC,1433\MSSQLSERVER" — Louis, Jan 15 '13 at 06:59
To put it more clearly here is the error I'm receiving after that command : Invoke-Sqlcmd : User does not have permission to alter database 'MSOEDW', the database does not exist, or the database is not in a state that allows access checks. ALTER DATABASE statement failed. At line:1 char:14 + invoke-sqlcmd <<<< -inputfile "C:\A.sql" -serverinstance tcp:"LOUIS_DEV-PC,1433\MSSQLSERVER" + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand — Louis, Jan 15 '13 at 07:30

Louis · Answer 1 · 2013-06-05T10:28:37.897

I have figured out what went wrong. When creating the database there was a line in the script that was giving a problem, when i removed the line from the script it went through with no problems. The Problem came in where:

   /****** Object:  Database [dbname]    Script Date: 01/03/2013 06:53:20 ******/
   CREATE DATABASE [Mdbname] -- ON  PRIMARY 
    **--( NAME = N'dbname', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\dbname.mdf' , SIZE = 30417920KB , MAXSIZE =   UNLIMITED, FILEGROWTH = 1024KB )
   -- LOG ON 
   --( NAME = N'dbname_log', FILENAME = N'C:\Program Files\Microsoft SQL  Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\dbname_1.ldf' , SIZE = 28333632KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 --GO**. Normally  its not the best idea to comment those sections out, but i had no          other choice. Databases were created and tables, stored procs, functions and views were all put into their rightful places.

Executing a SQL script using Windows Powershell

1 Answers1