asked on

How to Add Powershell Script to .Sql Script

Ok all you good sqlcmd/powershell script kiddies...

Test box: 2008R2 server running Sql Express 2008 R2. Have a .sql backup script running fine in task manager. Have a ZipFiles.ps1 using 7z running fine in Powershell.

Now the tricky part. I'm trying to add the powershell script at the end of the .sql backup script. I tested by adding the Powershell "write hello world" to the sql backup script and it ran fine. But when I try to add the .ps1 script like this:

!! PowerShell -InputFormat none -Command ".\ZipFiles.ps1"

Open in new window

I get the following error:

C:\Program Files (x86)\7-Zip\7z.exe needed
At C:\Users\Administrator\Documents\ZipFiles.ps1:1 char:62
+ if (-not (test-path "$env:ProgramFiles\7-Zip\7z.exe")) {throw <<<< "$env:Pro
gramFiles\7-Zip\7z.exe needed"}
+ CategoryInfo : OperationStopped: (C:\Program File...p\7z.exe ne
eded:String) [], RuntimeException
+ FullyQualifiedErrorId : C:\Program Files (x86)\7-Zip\7z.exe needed

The .ps1 scripts checks to see of 7z is installed. It is and is working fine when run from Powershell. Both the .sql and .ps1 scripts are in the same folder. How should the .ps1 script be referenced in the .sql script?

Thanks much!

SubSun

The script gives you error because it's is unable to find C:\Program Files (x86)\7-Zip\7z.exe ..

The following session check this and throw this error if the condition is matched..

If (-not (test-path "$env:ProgramFiles\7-Zip\7z.exe")) 
{
throw "$env:ProgramFiles\7-Zip\7z.exe needed"
}

Open in new window

Throw produces a terminating error, if you don't want that and the exception to be displayed then you can use just Echo or Write-Host

If (-not (test-path "$env:ProgramFiles\7-Zip\7z.exe")) 
{
Echo "$env:ProgramFiles\7-Zip\7z.exe needed"
}

Open in new window

You can run the script from a bat file by simply adding the following line..
powershell.exe c:\scripts\test.ps1
Ref : http://technet.microsoft.com/en-us/library/ee176949.aspx

normajm400

ASKER

Thank you for taking time to make some suggestions. As metioned the zipfiles.ps1 runs just fine when run in powershell. If I run zipfiles.ps1 in a scheduled .bat file, and the sql backup isn't done, that would be a problem. That's why I'm trying to run it as the last part of the backup .sql script. So the question remains, what is the proper syntax to run it as part of .sql? Or, possibly, do I need to add some code to the .ps1 so that it runs correctly under sqlcmd?

SubSun

Try..

EXEC xp_cmdshell 'powershell.exe c:\scripts\test.ps1'

Open in new window

normajm400

ASKER

Thanks Subsun - didn't work though, error: c:\scripts\test.ps1 is not a cmdlet, etc

SubSun

Are you sure c:\scripts\test.ps1 is the correct path to your script?

normajm400

ASKER

Path is correct - double checked it. Here is all the code, followed by the error. Test box, nothing private here...

DECLARE @BUFileName varchar(100)

SELECT @BUFileName = N'C:\Users\Public\Documents\AVWTest_' + Replace(Replace(Convert(nvarchar(20), GetDate(), 100),':','-'),' ','_') + '.bak'

BACKUP DATABASE [AdventureWorks2008R2] TO  DISK = @BUFileName WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2008R2_Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'AdventureWorks2008R2' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'AdventureWorks2008R2' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''AdventureWorks2008R2'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = @BUFileName WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

EXEC xp_cmdshell 'powershell.exe c:\Users\Administrator\Documents\ZipFiles.ps1'

Open in new window

The term 'c:\Users\Administrator\Documents\ZipFiles.ps1' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:46
+ c:\Users\Administrator\Documents\ZipFiles.ps1 <<<<
+ CategoryInfo : ObjectNotFound: (c:\Users\Admini...ts\ZipFiles.p
s1:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

SubSun

Hmm.. It should work (Working for me :-) )..

Can you try..

EXEC xp_cmdshell 'powershell -Command "c:\Users\Administrator\Documents\ZipFiles.ps1"'

or

EXEC xp_cmdshell 'powershell.exe &"c:\Users\Administrator\Documents\ZipFiles.ps1"'

or

EXEC xp_cmdshell 'powershell.exe &"c:\Users\Administrator\Documents\ZipFiles.ps1 -ExecutionPolicy Unrestricted"'

Open in new window

Also when you run the above commands directly from command prompt and see if you are able to run the script..

normajm400

ASKER

None of the three work from an "Admin" mode command prompt, "Exec not recognized." None of the three work from "Admin" mode powershell, "Exec not recognized."
In SSMS Query - the first returns same error - .ps1 not recognized, the second two run 'Executing", but never finish, so I cancel them. I waited 10 min on both. They should take 3 min max to run.

SubSun

It definitely says the script file is not available/accessible from path.. Are you able to run it from powershell console? or Are you able to open the script if you type
c:\Users\Administrator\Documents\ZipFiles.ps1 in start > run..

normajm400

ASKER

sorry for the delay in responding - haven't been able to use my PC for awhile - should be able to get back to this later this weekend

normajm400

ASKER

I can right click on the script, select "Run w/Powershell" and it runs fine; I did notice that it lists the powershell version as v1.0. The console version is 2.0. The script opens in Notepad if I type the path in the "Run" field.

I had an interesting problem with a different server - 2008. I had set up some functions and they would run from the powershell console (ver 2.0) just fine, but when I tried to run them in a .bat file, they failed. I noticed that from .bat they were running in a "ise" version of powershell. So I set up a profile and copied over the functions and permissions into the ise version and they then ran fine in the .bat file.

I have a feeling there may be something in my test box doing the same...

SubSun

Hmm.. I havn't faced any such issues with win 2008.. you can try disabling the UAC in server and see if it helps.. Or try to test it in another server..

ASKER CERTIFIED SOLUTION

Qlemo

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

normajm400

ASKER

Qlemo - Thank you...
Installed x86 7zip and all worked perfectly. I was able to use the original line of code in the sql backup script: !! PowerShell -InputFormat none -Command ".\ZipFiles.ps1

And thank you Subsun for all the troubleshooting suggestions.