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.

Enumerating Xml elements and inserting

I have an untyped XML variable that for example holds the following data:

<Customer>

<FirstName>John</FirstName>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

</Address>

<Office>

<Telephone>0208123456789</Telephone>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

</Address>

</Office>

</Customer>

<Customer>

<FirstName>Adam</FirstName>

<Address>

<AddressLine1>19 Another road</Addressline1>

<AddressLine2>Hemel</Addressline2>

<AddressLine3>London</Addressline3>

</Address>

<Office>

<Telephone>0208123456222</Telephone>

<Address>

<AddressLine1>5 The road</Addressline1>

<AddressLine2>Hatfield</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>2</ExternalId>

</Address>

</Office>

</Customer>

Using SQL DML and\or XQuery I would like to enumerate the XML elements and add an <ExternalId> element to any <Address> element if one doesn't exist with a value of NewId().

So the end result would be:

<Customer>

<FirstName>John</FirstName>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>QW34-122132WE-12334343A</ExternalId>

</Address>

<Office>

<Telephone>0208123456789</Telephone>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>QW34-122132WE-333333</ExternalId>

</Address>

</Office>

</Customer>

<Customer>

<FirstName>Adam</FirstName>

<Address>

<AddressLine1>19 Another road</Addressline1>

<AddressLine2>Hemel</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>QW34-122132WE-12312312</ExternalId>

</Address>

<Office>

<Telephone>0208123456222</Telephone>

<Address>

<AddressLine1>5 The road</Addressline1>

<AddressLine2>Hatfield</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>2</ExternalId>

</Address>

</Office>

</Customer>

Any help would be greatly appreciated.

Regards

Why are you using a unique identifier. Are you familiar with the performance hit that comes with using a unique identifier? I admit there are reasons to want to use a unique identifier and yours may be one of them, but frequently I see unique identifiers used without an understanding of the consequences to performance. Here are some previous threads related to this issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=430995&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1544519&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=304764&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1493312&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1525445&SiteID=1

|||

Thanks for the reply.

Perfomance is not an issue in this instance as it will be run once in a blue moon. But thanks for the heads-up on the performance issues. It doesn't have to be NewID() it can be any globally unique number.

Regards

sql

Enumerate All Database Files in Server

Did lots of searching, couldn't find the answer.

I am looking to enumerate all databases and their files on a given server. For example:

Database File Name File Path master master C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf master mastlog C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf msdb MSDBData C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf msdb MSDBLog C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf MyDB MyDB_Data C:\Databases\MyDB.mdf MyDB MyDB_Log C:\Databases\MyDB.ldf

I know you can use sys.database_files to pull this information database-by-database. But I am looking for a sql query that will generate this information at a server level.

TIA.

In SQL Server 2005, this is a snap.

select *

from sys.master_files

This doesn't suffer from any of the limitations of sys.database_files in that sys.database_files can be wrong if the database was restored and read only.

|||

Here you go.

Warning: sp_msforeachdb is undocumented procedure. Use it for single use. Don't stick on this SP.

Code Snippet

Create Table #Databases

(

[Database] nvarchar(1000),

[File Name] nvarchar(1000),

[File Path] nvarchar(1000)

);

Exec sp_msforeachdb 'Insert Into #Databases select ''?'', name,filename from [?]..sysfiles'

select * from #Databases

|||Thanks. Just what I was looking for.|||

Thanks for info on sp_msforeachdb. Very helpful.

Appreciate your time.

Enum datatype in SQL Server?

hi guys,

is it possible to do a enum datatype in SQL Server? if so can anyone point me to an example please?

thanks,

benny

No - there is no Enum Type is sql server.

Generally an Enum may be expressed in code as an Enum of Integers. Then in Sql Server, you would store the specific Integer that was selected. If you wish to insure that a value cannot be stored in your database unless it has been defined as allowable in sql server, then you could add a Constraint to your sql table for that particular Integer column to require specific values.

|||

thanks for your help :)

|||

I've found that it's effective enough to store the values of .NET Enums as int datatypes in SQL Server. Given that you have properties on your classes that are of type MyEnum, those int values will natively convert to their corresponding enum values (which are Integer values anyway).

Alternatively, if you store those values as varchar you can do an [Enum].Parse(GetType(MyEnum), VarcharValue) to convert it from its corresponding string.

Hope that helps.

Enterprise manager performance

Hello,
When I use EM to examine a local db in SQLServer, it takes 1 - 3 minutes to
display the stored procedures (for example).
I just clicked on the "Tables" node in the treeview, and it took over a
minute to list the tables.
Is there any way to improve the performance? As it stands, it borders on
the unusable...
Thanks for any suggestions.
Check the properties of your database. Is autoclose turned on?
By the way, you can find out lots of information via Query Analyzer. It can
do almost everything that Enterprise Manager does -- often it can do it
faster.
you can execute stored procedures to find out information
exec sp_tables
exec sp_stored_procedures
or you can execute queries
SELECT * FROM INFORMATION_SCHEMA.TABLES /*WHERE ...*/
SELECT * FROM INFORMATION_SCHEMA.COLUMNS /*WHERE ...*/
use northwind
go
exec sp_tables
go
exec sp_columns 'Orders'
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Orders'
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Orders'
Keith
"Developer" <wanderer@.mapinfo.nope.com> wrote in message
news:uXmNY7bwEHA.3716@.TK2MSFTNGP15.phx.gbl...
> Hello,
> When I use EM to examine a local db in SQLServer, it takes 1 - 3 minutes
to
> display the stored procedures (for example).
> I just clicked on the "Tables" node in the treeview, and it took over a
> minute to list the tables.
> Is there any way to improve the performance? As it stands, it borders on
> the unusable...
> Thanks for any suggestions.
>
|||Thanks for the reply. I'll try Query Analyzer.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uwwAbcewEHA.1988@.TK2MSFTNGP12.phx.gbl...
> Check the properties of your database. Is autoclose turned on?
> By the way, you can find out lots of information via Query Analyzer. It
can[vbcol=seagreen]
> do almost everything that Enterprise Manager does -- often it can do it
> faster.
> you can execute stored procedures to find out information
> exec sp_tables
> exec sp_stored_procedures
> or you can execute queries
> SELECT * FROM INFORMATION_SCHEMA.TABLES /*WHERE ...*/
> SELECT * FROM INFORMATION_SCHEMA.COLUMNS /*WHERE ...*/
>
> use northwind
> go
> exec sp_tables
> go
> exec sp_columns 'Orders'
> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Orders'
> SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Orders'
> --
> Keith
>
> "Developer" <wanderer@.mapinfo.nope.com> wrote in message
> news:uXmNY7bwEHA.3716@.TK2MSFTNGP15.phx.gbl...
> to
on
>
|||Hi,
Have you found any valueable information in Query Analyzer? If you have
many tables, it might really be time-consuming.
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||QA will be sufficient.
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:XQ5gLYwwEHA.2916@.cpmsftngxa10.phx.gbl...
> Hi,
> Have you found any valueable information in Query Analyzer? If you have
> many tables, it might really be time-consuming.
> I wanted to post a quick note to see if you would like additional
> assistance or information regarding this particular issue. We appreciate
> your patience and look forward to hearing from you!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||This is ridiculous -- I just waited 4 minutes for EM to open the property
sheet for a local database.
P4, 2.8 gig, 1 gig o' ram and a big hard disk.
There must be some sort of tuning of SQLServer (or EM) available to make it
respond like a modern application.
"Developer" <wanderer@.mapinfo.nope.com> wrote in message
news:uXmNY7bwEHA.3716@.TK2MSFTNGP15.phx.gbl...
> Hello,
> When I use EM to examine a local db in SQLServer, it takes 1 - 3 minutes
to
> display the stored procedures (for example).
> I just clicked on the "Tables" node in the treeview, and it took over a
> minute to list the tables.
> Is there any way to improve the performance? As it stands, it borders on
> the unusable...
> Thanks for any suggestions.
>
|||Hi,
I understood that it must be frustrating!
Would you please use Profiler to trace the process when SQL Server
Enterprise Manager was opening? Send the Profiler Logs directly to me
v-mingqc@.online.microsoft.com (please remove 'online' as it's only for
SPAM). I would like to make further investigate.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi,
Thanks for sending me the Profiler Trace!
1. I find the statement exec sp_dboption N'CareInsite', N'autoclose',
N'true' in the trace, so that please make sure AUTO_CLOSE option for the
database was set to false(Not Selected)
2. Check if ODBC Trace is turned off, using ODBCAD32.EXE and see Tracing tab
BTW, you are using SQL Server 2000 SP3?
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||1. I have unchecked "Auto close" on the Options tab in the property sheet
for my db.
2. It appears ODBC tracing was on; I clicked the "Stop Tracing Now" button
on the Tracing tab of "ODBC Data Source Administrator".
"About SQL Server Service Manager" says it is version 8.00.760. I believe
it is SQL Server 2000 SP3.
Thanks for following up...
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:ta%23zQ%23IyEHA.3388@.cpmsftngxa10.phx.gbl...
> Hi,
> Thanks for sending me the Profiler Trace!
> 1. I find the statement exec sp_dboption N'CareInsite', N'autoclose',
> N'true' in the trace, so that please make sure AUTO_CLOSE option for the
> database was set to false(Not Selected)
> 2. Check if ODBC Trace is turned off, using ODBCAD32.EXE and see Tracing
tab
> BTW, you are using SQL Server 2000 SP3?
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||I suspect that turning off ODBC tracing made the difference; EM and Query
Analyzer are responsive and useful.
Thanks.
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:ta%23zQ%23IyEHA.3388@.cpmsftngxa10.phx.gbl...
> Hi,
> Thanks for sending me the Profiler Trace!
> 1. I find the statement exec sp_dboption N'CareInsite', N'autoclose',
> N'true' in the trace, so that please make sure AUTO_CLOSE option for the
> database was set to false(Not Selected)
> 2. Check if ODBC Trace is turned off, using ODBCAD32.EXE and see Tracing
tab
> BTW, you are using SQL Server 2000 SP3?
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>

Enterprise Manager

Hi everyone,
I want to become an enterprise manager jedi in SQL Server, for example if I go to All tasks --> Generate SQL scripts --> options tab, I want to see understand all the options and what they mean... eg File Format etcOriginally posted by krugga
Hi everyone,
I want to become an enterprise manager jedi in SQL Server, for example if I go to All tasks --> Generate SQL scripts --> options tab, I want to see understand all the options and what they mean... eg File Format etc

Sorry what I wanted to ask was... whats a good resource for this sort of thing? eg a cool website... like a how to do things in Enterprise Manager type website?