?
Solved

Pass Environment Variable into SQL Script

Posted on 2009-04-18
4
Medium Priority
?
1,431 Views
Last Modified: 2013-11-09
I am having some issues trying to pass an environment variable into a SQL Script, however, I the SET @ContactID = %ContactID% statement returns %C instead of 10.

I can get it to work, but I have to change the batch file to this:

SET ContactID=10
SQLCMD -S . -E -Q "DECLARE @contactid varchar(2) SET @contactid = '%Contact%' PRINT @contactID"

I also tried this, but it doesn't work:

SQLCMD -S . -E -Q "DECLARE @contactid varchar(2) SET @contactid = '%Contact%' :r test.sql"

However, I would like to keep it in the external SQL Script. Is there anyway to do this, maybe using POWERSHELL?
-- This is the batch File Code
 
SET ContactID=10
 
SQLCMD -S . -E -itest.sql
 
pause
 
-- This is the SQL File Code (test.sql)
 
DECLARE @ContactID VARCHAR(2)
SET @ContactID = '%ContactID%'
PRINT @ContactID

Open in new window

0
Comment
Question by:computerstreber
  • 2
  • 2
4 Comments
 
LVL 9

Accepted Solution

by:
Sander Stad earned 1000 total points
ID: 24182281
There is no easy way to put the value you want into a textfile other than that you create the sql file everytime you start the Powershell script.
Another way you can do this is by creating a stored procedure in SQL Server that accepts a parameter. You can pass the contact value to the stored procedure that will execute your query. It may be a little overdone to create a stored procedure for something so small but it's possible.
If you don't want to use the stored procedure use your own query in the Powershell script.

You can use this script to get the evironment variable and execute the storedp procedure. Execute the sql script in SSMS or through the commandline using SQLCMD.

Put the Powershell script in a file and execute it. It will than execute your stored procedure.

###### SQL Script##########################################################
USE YourDatabase;
GO
IF OBJECT_ID ( 'YourDatabase.NameOfStoredProcedure', 'P' ) IS NOT NULL 
    DROP PROCEDURE YourDatabase.NameOfStoredProcedure;
GO
CREATE PROCEDURE YourDatabase.NameOfStoredProcedure
AS
    PRINT @ContactID
GO
##########################################################################
###### Powershell Script #################################################
$contactId = [Environment]::GetEnvironmentVariable("Contact","User")
 
SQLCMD -S . -E -q "EXEC YourDatabase.NameOfStoredProcedure @ContactId = N$contectId"
##########################################################################

Open in new window

0
 
LVL 9

Expert Comment

by:Sander Stad
ID: 24182283
Little typo at the Powershell script.

###### Powershell Script #################################################
$contactId = [Environment]::GetEnvironmentVariable("Contact","User")
 
SQLCMD -S . -E -q "EXEC YourDatabase.NameOfStoredProcedure @ContactId = N$contactId"
##########################################################################

Open in new window

0
 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 1000 total points
ID: 24896755
sqlcmd has the ability to set and use script variables. You would use

 sqlcmd -v ContactID=10 -itest.sql

and in itest.sql:

PRINT $(ContactID)

0
 
LVL 71

Expert Comment

by:Qlemo
ID: 24896795
If you want to use batch variables (environment variables):

set ContactID=10
sqlcmd -itest.sql

test.sql would be the same.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses
Course of the Month16 days, 20 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question