Environment Variables Within SQL

Is there some way I can access system environment variables within a SQL script? For example, if I have a script "foo.sql" that I'm calling from isql, I want to be able to substitute <hostname> with an env variable I set at the command line:

foo.sql:

update tblFoo set HostName = <hostname>

Thanks.

TerenceQ1 Is there some way I can access system environment variables within a SQL script?

A1 Yes.

Note: The following is in regard to osql (however it should hold true for isql as well)

If issuing queries from osql, (also should work using batch files), one may use environment variables i.e.( %variablename% ) directly. For example:

Define the following two environment variables:
Set TargetDB = Pubs
Set TargetTable = Authors

Then run the following example (replace SqlServer with your SqlServer instance name before running) from the command prompt:

Example:

osql SSqlServer -E -dPubs -q"exit(Select Au_LName from %TargetDB%..%TargetTable% Order By Au_LName Go Select Count(*) As 'AuthorsCount' From %TargetTable%)"|||Note that DBA's suggestion works because all the variables have been substituted PRIOR to running osql.exe. This method won't work with a script. To my knowledge there is no way to reference environment variables from SQL server.|||RE:
Note that DBA's suggestion works because all the variables have been substituted PRIOR to running osql.exe. This method won't work with a script. To my knowledge there is no way to reference environment variables from SQL server.

A good point. If the simple approach demonstrated is unworkable for the requirements at hand; another approach to consider may be to create one or more stored procedures which may be executed such that the desired results may be achieved indirectly.

For example, several utility procs may be created which shell out to the OS and execute OS commands directly or that call short VB scripts to gather, set, and / or otherwise manipulate the environment variables as required.

environment variable

hi,

can you show me how to get the value of an environment variable from a script task?
thanks!

Try Environment.GetEnvironmentVariable method
http://msdn2.microsoft.com/en-us/library/system.environment.getenvironmentvariable.aspx|||

Public Class ScriptMain
Public Sub Main()

Dim home As String = System.Environment.GetEnvironmentVariable("HOMEDRIVE") + System.Environment.GetEnvironmentVariable("HOMEPATH")

Dts.Variables("MyDocuments").Value = Path.Combine(home, "My Documents\")

Dts.TaskResult = Dts.Results.Success
End Sub

There is a sample package that illustrates the method in action here -

Environment Variables- Raw Files
(http://wiki.sqlis.com/default.aspx/SQLISWiki/EnvironmentVariables-RawFiles.html)

Environment discrepencies concerning stored procedures & data types

Im trying to move a database from a NT4 SQL2K server over to a WIN2K SQL2K box.

When i copy the stored procedures over via a generated script, 1 procedure fails to create itself, and the error i get is:

The text, ntext, and image data types are invalid in this subquery or aggregate

the error is supposedly the top line here:

SELECT *, 'cms_Document_Contents_ID'='', 'cms_Language_ID'='', 'Title'='', 'XML'='', 'search_text'='',
'File_XML'=(SELECT TOP 1 File_XML FROM cms_Document_Contents d
INNER JOIN cms_Document_Sections e ON d.cms_Document_Id=e.cms_Document_Id
WHERE d.cms_Language_ID=9 AND e.cms_Document_Section_ID=@.DocumentSectionId) ,
c.active, c.sequence, c.cms_Document_Section_ID, c.cms_Section_ID, c.cms_Document_Type_ID
FROM cms_Documents a
INNER JOIN cms_Document_Sections c ON a.cms_Document_ID=c.cms_Document_ID
WHERE c.cms_Document_Section_ID=@.DocumentSectionId
END

GO

wtf is going on? this works fine on the old server. i tried running it back there and it just complains that the stored procedure already exists.
i am not familiar with SQL, this is someone elses code, i dont understand the error and NEED all the help i can get.Somehow you got single quotes stuck around your column names.

Select 'cms_Document_Contents_ID'=''
...makes no sense. I assume the procedure is createing an empty column that will be populated later. Try this and see if you get the same error:

SELECT *,
cms_Document_Contents_ID='',
cms_Language_ID='',
Title='',
XML='',
search_text='',
File_XML= (SELECT TOP 1 File_XML FROM cms_Document_Contents d
INNER JOIN cms_Document_Sections e ON d.cms_Document_Id = e.cms_Document_Id WHERE d.cms_Language_ID=9 AND e.cms_Document_Section_ID=@.DocumentSectionId),
c.active,
c.sequence,
c.cms_Document_Section_ID,
c.cms_Section_ID,
c.cms_Document_Type_ID
FROM cms_Documents a
INNER JOIN cms_Document_Sections c ON a.cms_Document_ID=c.cms_Document_ID
WHERE c.cms_Document_Section_ID=@.DocumentSectionId

blindman|||Stop the SQL Server, exit the Service Manager, copy the data and log files (C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDB_Data.mdf, & C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDB_Log.ldf on my machine respectivley) to your destination server, on that machine you can import (attatch) the data and log files into SQL Server with the following command in the query analyzer (substituing MyDB and the 2 paths with your equivalents):

sp_attatch_db @.dbname = 'MyDB',
@.filename1 = 'c:\path\to\datafilename.mdf',
@.filename2 = 'c:\path\to\datafilename.mdf'

if that is successful id say your home free (refresh your database list), if not, post on this forum, they can help...

.^sUbMSg-\/.

see: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp for info on this stored procedure.

ps: f*ck that DTS export b*llocks im sticking with the raw data files.

The text, ntext, and image data types are invalid in this subquery or aggregate expression.

export copy move transfer transport duplicate mirror import mdf sql database db attach stored procedure retain keep error 2000 7 6.5 8 backup restore detatch

Enterprise Manager 2000 vs 2005

Maybe I just haven't found the right place yet, but...
1. In 2000 you could hold down the control key, select several tables and
script them out into 1 file... why doesn't this work in 2005 (or how do you
do it in 2005) ?
2. In 2000 you could right click on a table and quickly export its
contents... how in 2005 ?
When using SSIS...
So I connect to Integration services... I expand my stored packages... MSDB
folder... I see the packages there... Is there a way to "open them" in
design view from EM ?
Thanks !Hi Rob
"Rob" wrote:
> Maybe I just haven't found the right place yet, but...
> 1. In 2000 you could hold down the control key, select several tables and
> script them out into 1 file... why doesn't this work in 2005 (or how do you
> do it in 2005) ?
You can script multiple objects by clicking the database then choosing
task/generate scripts and choose from them, a mixture of multiple objects of
different types can be chosen.
> 2. In 2000 you could right click on a table and quickly export its
> contents... how in 2005 ?
Again at database level, tasks and export data to get the wizard..
> When using SSIS...
> So I connect to Integration services... I expand my stored packages... MSDB
> folder... I see the packages there... Is there a way to "open them" in
> design view from EM ?
No, you need to used BIDS ( SQL Server Business Intelligence Development
Studio )
> Thanks !
>
John|||Thanks for the response John...
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:DEF8B71A-5370-4A3F-899C-B8D8D3296D0B@.microsoft.com...
> Hi Rob
> "Rob" wrote:
>> Maybe I just haven't found the right place yet, but...
>> 1. In 2000 you could hold down the control key, select several tables and
>> script them out into 1 file... why doesn't this work in 2005 (or how do
>> you
>> do it in 2005) ?
> You can script multiple objects by clicking the database then choosing
> task/generate scripts and choose from them, a mixture of multiple objects
> of
> different types can be chosen.
>> 2. In 2000 you could right click on a table and quickly export its
>> contents... how in 2005 ?
> Again at database level, tasks and export data to get the wizard..
>> When using SSIS...
>> So I connect to Integration services... I expand my stored packages...
>> MSDB
>> folder... I see the packages there... Is there a way to "open them" in
>> design view from EM ?
> No, you need to used BIDS ( SQL Server Business Intelligence Development
> Studio )
>> Thanks !
> John

Enterprise Manager - Meta Data Error

When trying to browse my meta data repository in EM, i
get the following error:
Internet Explorer Script Error
Line: 1
Char: 138
Error: Cannot use parentheses when calling a sub
Code: 0
URL: res://C:\Program%20Files\Microsoft%20SQL%20Server\80
\Tools\Binn\Resources\1033\sqlmmc.rll/repgeneral.htm
Do you want to continue running scripts on this page?
--
After answering yes, you are taken to the next screen and
are not able to update information in the Description and
Comments fields. Everyone else in my group has this
problem too. SQL 2000, sp3. IE 6 sp 1. Now, If i go to
an old machine with SQL 2000 sp 3, IE 5, then I can use
this page with no problems.
Has anyone else run into this. Does Microsoft have a
fix? What should I do other than uninstall IE 6 and
install IE 5. <-- Doesn't sound like a good solution to
me.
Thanks.There's probably a better answer to this, but...
this sounds similair to an issue when using taskpad view. You sometimes get
a similiar message...
in that case if you switch to "large fonts" view and then back to task pad
it works. You might try that. Sorry this wasn't more helpful...
--
Brian
"Brian Mitchell" <brianwmitchell@.hotamail.com> wrote in message
news:059d01c38d9b$976e9580$a001280a@.phx.gbl...
> When trying to browse my meta data repository in EM, i
> get the following error:
> Internet Explorer Script Error
> Line: 1
> Char: 138
> Error: Cannot use parentheses when calling a sub
> Code: 0
> URL: res://C:\Program%20Files\Microsoft%20SQL%20Server\80
> \Tools\Binn\Resources\1033\sqlmmc.rll/repgeneral.htm
> Do you want to continue running scripts on this page?
> --
> After answering yes, you are taken to the next screen and
> are not able to update information in the Description and
> Comments fields. Everyone else in my group has this
> problem too. SQL 2000, sp3. IE 6 sp 1. Now, If i go to
> an old machine with SQL 2000 sp 3, IE 5, then I can use
> this page with no problems.
> Has anyone else run into this. Does Microsoft have a
> fix? What should I do other than uninstall IE 6 and
> install IE 5. <-- Doesn't sound like a good solution to
> me.
> Thanks.|||That is exactly what I was hoping for. I, like everyone
else, have dealt with that annoying problem for a while
now. When I saw the large icons tip in SQL Server Mag a
couple months ago, I rejoiced.
Problem is, that does not help here. It seems obvious to
me that this is a bug that has been introduced by either
IE 6, a service pack, or a security patch.
I know a couple other people have seen this problem. You
will find them if you do a google search. No one has
come up with an answer to it yet though.