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 variables

Well, having only one disk partition, and only one directory, and running only one application probably ROCKS too, at least for that one application :)
But if you're unfortunate enough to be required to use multiple applications, you might be saddened by certain aspects of environment variables, such as them living in one globally competitive namespace, or them being not covered by the NT security model (AFAIK).
But I grant you, that they solve the portability problem with package configurations, so I was happy to use them nonetheless.
I'd use environment variables (or any other hack in all likelihood), if I could find a solution for the lack of reusability in SSIS -- that is driving us to move as much code as possible outside of SSIS for maintainability. :(

We're always looking for product feedback; what are the aspects of your packages that you wish you could re-use? (Also, it sounds like you're replying to another thread... Was this post misplaced?)

|||

Cim Ryan wrote:

We're always looking for product feedback; what are the aspects of your packages that you wish you could re-use? (Also, it sounds like you're replying to another thread... Was this post misplaced?)

Cim/Perry,
I'm jumping in on your thread here, sorry about that.

Here's a list of things that could, IMO, be made reusable:
-Data-Flows (the most obvious one)
-Tasks (e.g. an Execute SQL task that calls a auditing sproc)
-A sequence container that carries out a single unit of work
-A For/ForEach Loop (including all tasks within it)
-A configured component
-A group of configured components (http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx)
The important point to make is that they shouldn't just be made reusable in the same package, they should be reusable in ANY package. In that sense, each one of them could be a deployable object. Currently the only deployable object is a package - why should that be the case? Why not deploy (for example) a task, or even a component, and then use that in any package?

-Jamie

|||To add to Jamie's excellent list at the lower level,
expressions
I say this because copying and pasting transformations from one Derived Column task to another is tedious, and I tend to believe that copy&paste leads to poor maintainability and scalability.

|||Also, the Aggregate defaults most columns to Group By, it usually misses one out of a long list, and for some reason it always misses the ErrorCode and ErrorNumber, when they are added at the bottom of the output column list from a Lookup Error, so when you finish checking all the columns to group by, you track the Validation error back and find that the Aggregate left those columns unspecified -- which means it is in an invalid state -- so you have to manually set those to Group By.
Actually, sounds more like a bug than an RFE to me, but I don't care what list it goes on if it gets fixed :)
sql

Environment Variables

Hi everyone...

I'm trying to create a database with the parent directory being a environment variable,

something like this:

Code Snippet

CREATE DATABASE mydatabase
ON
PRIMARY(NAME = myDataBase,
FILENAME = '%PARENTDIRECTORY%\mydatabase.mdf',

the problem is I don't know to get an environment variable in transact SQL...

I know that in C# we can get it with %PARENTDIRECTORY%...

Thanx in advance

The following batch may help you...

Code Snippet

Create Table #Result

(

Data varchar(8000)

);

Insert Into #Result

Exec master..xp_cmdshell 'echo %TEMP%';

Declare @.ParentDirectory as nvarchar(256);

Select Top 1 @.ParentDirectory = Data From #result;

Select @.ParentDirectory = @.ParentDirectory + '\mydatabase.mdf'

Exec ('CREATE DATABASE mydatabase

ON

PRIMARY(NAME = myDataBase,

FILENAME = ''' + @.ParentDirectory + ''')')

Drop table #result

|||Thanx Manivannan the code above does the job, but doesn't the xp_cmdshell work only for XP users?

|||

sqlclr's udf can imprement below,

This assembly's permission need to 'EXTERNAL_ACCESS'.

Usage:

Code Snippet

select dbo.GetEnvironmentVariable('temp');

C# Source file:

Code Snippet

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.None)]
public static SqlString GetEnvironmentVariable(string variable)
{
string value = Environment.GetEnvironmentVariable(variable);
return new SqlString(value);
}
};

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 users and logins

Hi
We are currently migrating from MSSQL 2000 enterprice to MSSQL 2005
Enterprice, and with that doing a revision of our users.
We would like to be able to do a restore a backup of Production to test.
We would like user privileges on the servers to differ so a given user can
execute some stored procedures on one environment but not the other. Say, we
don’t want a service in test with the wrong connection string to be have
access to production.
We have previously done this by using local user groups on the servers, but
we loose the mapping between the Login name and the user name when we do the
restores.
Any ideas or best practices?
Best regards
MikaelMikael
> We would like to be able to do a restore a backup of Production to test.
No problem, just RESTORE command

> We would like user privileges on the servers to differ so a given user can
> execute some stored procedures on one environment but not the other. Say,
> we
> dont want a service in test with the wrong connection string to be have
> access to production.
MS has introduced in SS2005 lots of new features, like SCHEMA for example.
I'd suggets you to spend a hew days to study it

> We have previously done this by using local user groups on the servers,
> but
> we loose the mapping between the Login name and the user name when we do
> the
> restores.
http://blogs.msdn.com/lcris/archive.../03/567680.aspx
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:48261D3D-86AC-4B6F-8B0A-380101564866@.microsoft.com...
> Hi
> We are currently migrating from MSSQL 2000 enterprice to MSSQL 2005
> Enterprice, and with that doing a revision of our users.
> We would like to be able to do a restore a backup of Production to test.
> We would like user privileges on the servers to differ so a given user can
> execute some stored procedures on one environment but not the other. Say,
> we
> dont want a service in test with the wrong connection string to be have
> access to production.
> We have previously done this by using local user groups on the servers,
> but
> we loose the mapping between the Login name and the user name when we do
> the
> restores.
> Any ideas or best practices?
>
> --
> Best regards
> Mikael

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

Environment and Application Domain between ASP.NET & SQL Server

Hi all,

I have posted the same topic under ASP-NET-Developers but had no reply;
I then posted it under ASP.Net Community and had also no reply.
Hence, i'm posting it here to prevent the impression of spam post ...

I have created a SqlContextTrigger using ADO.NET 2.0 to create a
trigger on
SQL Server 2005.

In the class, I create a file and specify its path to be in the
Application
Domain base directory such as:

String Path = AppDomain.CurrentDomain.BaseDirectory.ToString()

However, the path is always the SQL Server Binn Directory. I'd like to
tell
the program that I want the path to be related not to SQL Server Binn
Directory but to Visual Studio Solution's Project Bin Directory.

How do I do that?

Best regards> However, the path is always the SQL Server Binn Directory. I'd like to
> tell
> the program that I want the path to be related not to SQL Server Binn
> Directory but to Visual Studio Solution's Project Bin Directory.

I doubt that you will find anything built-in that will do this. The trigger
thread is running in SQL Server and has no knowledge of your client folder
structure nor a means to access it.

One method to accomplish the desired result is to store the desired target
path in a table so that you can retrieve the value from within your trigger
code. If the path is remote, you'll need to use a UNC path and additional
security considerations apply.

I'm not sure what you are trying to do but you might take a look at Service
Broker.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"coosa" <coosa76@.gmail.com> wrote in message
news:1136546993.487438.187050@.o13g2000cwo.googlegr oups.com...
> Hi all,
> I have posted the same topic under ASP-NET-Developers but had no reply;
> I then posted it under ASP.Net Community and had also no reply.
> Hence, i'm posting it here to prevent the impression of spam post ...
> I have created a SqlContextTrigger using ADO.NET 2.0 to create a
> trigger on
> SQL Server 2005.
> In the class, I create a file and specify its path to be in the
> Application
> Domain base directory such as:
> String Path = AppDomain.CurrentDomain.BaseDirectory.ToString()
> However, the path is always the SQL Server Binn Directory. I'd like to
> tell
> the program that I want the path to be related not to SQL Server Binn
> Directory but to Visual Studio Solution's Project Bin Directory.
> How do I do that?
> Best regardssql

Enterprise Manager over a VPN gets No items

I have an environment that has 18 separate SQL server machines. When
connected to the LAN I can connect to each and expand the databases tab no
problem. When I try the same over a VPN I find that three of my servers will
connect but on expanding the databases I get a long pause then 'No items'.
I've checked around and found some references to the same problem (SQL
server mag site) but no resolutions. I'm running XP pro and my SQL is sp3.
The three servers in question have only TCP and named pipes in the net lib,
they are all on Win2K one is on sp3, one on sp2 and one is 'out of the box'.
The VPN has ports 1433 and 1434 udp and tcp allowed.

Does anyone have any suggestions as to what is causing this, its got to be
something simple but I've been looking at this so long now I cant see the
wood for the trees.

Help appreciated.There is a least one known issue that can produce this:

http://support.microsoft.com/default.aspx?scid=kb;[LN];Q315523

Here are some comments from an MS engineer on it:

http://groups.google.ch/groups?hl=e...%40cpmsftngxa06

There are several Google groups hits on this, by the way:

http://groups.google.com/groups?hl=...ems&sa=N&tab=wg

Simon

"Simon" <spamplin@.hss.com> wrote in message
news:bdpe27$k71$1@.hercules.btinternet.com...
> I have an environment that has 18 separate SQL server machines. When
> connected to the LAN I can connect to each and expand the databases tab no
> problem. When I try the same over a VPN I find that three of my servers
will
> connect but on expanding the databases I get a long pause then 'No items'.
> I've checked around and found some references to the same problem (SQL
> server mag site) but no resolutions. I'm running XP pro and my SQL is sp3.
> The three servers in question have only TCP and named pipes in the net
lib,
> they are all on Win2K one is on sp3, one on sp2 and one is 'out of the
box'.
> The VPN has ports 1433 and 1434 udp and tcp allowed.
> Does anyone have any suggestions as to what is causing this, its got to be
> something simple but I've been looking at this so long now I cant see the
> wood for the trees.
> Help appreciated.

Enterprise Manager Behaviour for SQL 2000

Thanks in Advance,
I am seeing a strange behavior in the query interface on EM that is
new for me, and it has only occurred in a new environment. So I
believe that this is a configuration setting that one of you can point
out to me.
We I have multiple query windows open, if I turn my attention to
another task in remote desktop, the reminder that a query window has
not been in use for sometime pops up and asks if it can clear the
results. In this environment, that window somehow gets trapped behind
the query windows. This creates a catch 22. I cannot move the focus
to close any of the query windows to reveal the system message, and I
cannot get to the system message to allow me to move the focus.
Is there a configuration solution here?
Mark Simmerman
Napa, CADon't use "Open Table" in Enterprise Manager. Run a SELECT query in Query
Analyzer.
In Enteprise Manager, you are maintaining an association with the source
table (so that you can treat SQL Server like Excel, and edit a "cell"), and
this causes resource drain no matter how long you let it sit there.
<xxxdbaxxx@.gmail.com> wrote in message
news:1170262471.222466.129540@.l53g2000cwa.googlegroups.com...
> Thanks in Advance,
> I am seeing a strange behavior in the query interface on EM that is
> new for me, and it has only occurred in a new environment. So I
> believe that this is a configuration setting that one of you can point
> out to me.
> We I have multiple query windows open, if I turn my attention to
> another task in remote desktop, the reminder that a query window has
> not been in use for sometime pops up and asks if it can clear the
> results. In this environment, that window somehow gets trapped behind
> the query windows. This creates a catch 22. I cannot move the focus
> to close any of the query windows to reveal the system message, and I
> cannot get to the system message to allow me to move the focus.
> Is there a configuration solution here?
> Mark Simmerman
> Napa, CA
>|||On Jan 31, 10:52 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Don't use "Open Table" in Enterprise Manager. Run a SELECT query in Query
> Analyzer.
> In Enteprise Manager, you are maintaining an association with the source
> table (so that you can treat SQL Server like Excel, and edit a "cell"), and
> this causes resource drain no matter how long you let it sit there.
> <xxxdba...@.gmail.com> wrote in message
> news:1170262471.222466.129540@.l53g2000cwa.googlegroups.com...
>
> > Thanks in Advance,
> > I am seeing a strange behavior in the query interface on EM that is
> > new for me, and it has only occurred in a new environment. So I
> > believe that this is a configuration setting that one of you can point
> > out to me.
> > We I have multiple query windows open, if I turn my attention to
> > another task in remote desktop, the reminder that a query window has
> > not been in use for sometime pops up and asks if it can clear the
> > results. In this environment, that window somehow gets trapped behind
> > the query windows. This creates a catch 22. I cannot move the focus
> > to close any of the query windows to reveal the system message, and I
> > cannot get to the system message to allow me to move the focus.
> > Is there a configuration solution here?
> > Mark Simmerman
> > Napa, CA- Hide quoted text -
> - Show quoted text -
OK. Other than telling me to use another tool (in SQL 2005 they are
one and the same), is there any solution or configuration setting that
can address this?
Mark|||> OK. Other than telling me to use another tool (in SQL 2005 they are
> one and the same),
Yes, but you still have different options, like Open Table (bad), and query
windows (good).
> is there any solution or configuration setting that
> can address this?
Not that I know of.|||xxxdbaxxx@.gmail.com wrote:
> OK. Other than telling me to use another tool (in SQL 2005 they are
> one and the same), is there any solution or configuration setting that
> can address this?
> Mark
>
No, they're not really "one and the same". You can "open" tables using
a GUI, ala Enterprise Manager, or you can query them properly ala Query
Analyzer. The only difference in 2005 is that you don't "launch" two
seperate applications.
The limitations are the same - opening a table through the GUI is a
heavy process, prone to blocking. The GUI tools are acceptable for a
development environment, but should not be used against a production
system. Manipulating your tables properly, using T-SQL commands, is the
preferred method.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Feb 1, 7:34 am, Tracy McKibben <t...@.realsqlguy.com> wrote:
> xxxdba...@.gmail.com wrote:
> > OK. Other than telling me to use another tool (in SQL 2005 they are
> > one and the same), is there any solution or configuration setting that
> > can address this?
> > Mark
> No, they're not really "one and the same". You can "open" tables using
> a GUI, ala Enterprise Manager, or you can query them properly ala Query
> Analyzer. The only difference in 2005 is that you don't "launch" two
> seperate applications.
> The limitations are the same - opening a table through the GUI is a
> heavy process, prone to blocking. The GUI tools are acceptable for a
> development environment, but should not be used against a production
> system. Manipulating your tables properly, using T-SQL commands, is the
> preferred method.
> --
> Tracy McKibben
> MCDBAhttp://www.realsqlguy.com
OK. I am in a development environment. I would still like to know if
anyone has an answer to the question. It is an annoying behavior on
the part of the GUI. Do you guys get points for non-responsive
responses?
Mark|||> OK. I am in a development environment. I would still like to know if
> anyone has an answer to the question. It is an annoying behavior on
> the part of the GUI. Do you guys get points for non-responsive
> responses?
We don't get "points" for anything. We are here, volunteering our time to
help other people solve problems we might have insight on. Every once in a
while, the recipients feigns appreciation.
If "not that I know of" is not responsive enough for you, then maybe you
know of a better place to get more responsive answers, and should try there.
<shrug><sigh><resisting smacking own forehead>

Enterprise Manager Behaviour for SQL 2000

Thanks in Advance,
I am seeing a strange behavior in the query interface on EM that is
new for me, and it has only occurred in a new environment. So I
believe that this is a configuration setting that one of you can point
out to me.
We I have multiple query windows open, if I turn my attention to
another task in remote desktop, the reminder that a query window has
not been in use for sometime pops up and asks if it can clear the
results. In this environment, that window somehow gets trapped behind
the query windows. This creates a catch 22. I cannot move the focus
to close any of the query windows to reveal the system message, and I
cannot get to the system message to allow me to move the focus.
Is there a configuration solution here?
Mark Simmerman
Napa, CA
Don't use "Open Table" in Enterprise Manager. Run a SELECT query in Query
Analyzer.
In Enteprise Manager, you are maintaining an association with the source
table (so that you can treat SQL Server like Excel, and edit a "cell"), and
this causes resource drain no matter how long you let it sit there.
<xxxdbaxxx@.gmail.com> wrote in message
news:1170262471.222466.129540@.l53g2000cwa.googlegr oups.com...
> Thanks in Advance,
> I am seeing a strange behavior in the query interface on EM that is
> new for me, and it has only occurred in a new environment. So I
> believe that this is a configuration setting that one of you can point
> out to me.
> We I have multiple query windows open, if I turn my attention to
> another task in remote desktop, the reminder that a query window has
> not been in use for sometime pops up and asks if it can clear the
> results. In this environment, that window somehow gets trapped behind
> the query windows. This creates a catch 22. I cannot move the focus
> to close any of the query windows to reveal the system message, and I
> cannot get to the system message to allow me to move the focus.
> Is there a configuration solution here?
> Mark Simmerman
> Napa, CA
>
|||On Jan 31, 10:52 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Don't use "Open Table" in Enterprise Manager. Run a SELECT query in Query
> Analyzer.
> In Enteprise Manager, you are maintaining an association with the source
> table (so that you can treat SQL Server like Excel, and edit a "cell"), and
> this causes resource drain no matter how long you let it sit there.
> <xxxdba...@.gmail.com> wrote in message
> news:1170262471.222466.129540@.l53g2000cwa.googlegr oups.com...
>
>
>
>
> - Show quoted text -
OK. Other than telling me to use another tool (in SQL 2005 they are
one and the same), is there any solution or configuration setting that
can address this?
Mark
|||> OK. Other than telling me to use another tool (in SQL 2005 they are
> one and the same),
Yes, but you still have different options, like Open Table (bad), and query
windows (good).

> is there any solution or configuration setting that
> can address this?
Not that I know of.
|||xxxdbaxxx@.gmail.com wrote:
> OK. Other than telling me to use another tool (in SQL 2005 they are
> one and the same), is there any solution or configuration setting that
> can address this?
> Mark
>
No, they're not really "one and the same". You can "open" tables using
a GUI, ala Enterprise Manager, or you can query them properly ala Query
Analyzer. The only difference in 2005 is that you don't "launch" two
seperate applications.
The limitations are the same - opening a table through the GUI is a
heavy process, prone to blocking. The GUI tools are acceptable for a
development environment, but should not be used against a production
system. Manipulating your tables properly, using T-SQL commands, is the
preferred method.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||On Feb 1, 7:34 am, Tracy McKibben <t...@.realsqlguy.com> wrote:
> xxxdba...@.gmail.com wrote:
>
> No, they're not really "one and the same". You can "open" tables using
> a GUI, ala Enterprise Manager, or you can query them properly ala Query
> Analyzer. The only difference in 2005 is that you don't "launch" two
> seperate applications.
> The limitations are the same - opening a table through the GUI is a
> heavy process, prone to blocking. The GUI tools are acceptable for a
> development environment, but should not be used against a production
> system. Manipulating your tables properly, using T-SQL commands, is the
> preferred method.
> --
> Tracy McKibben
> MCDBAhttp://www.realsqlguy.com
OK. I am in a development environment. I would still like to know if
anyone has an answer to the question. It is an annoying behavior on
the part of the GUI. Do you guys get points for non-responsive
responses?
Mark
|||> OK. I am in a development environment. I would still like to know if
> anyone has an answer to the question. It is an annoying behavior on
> the part of the GUI. Do you guys get points for non-responsive
> responses?
We don't get "points" for anything. We are here, volunteering our time to
help other people solve problems we might have insight on. Every once in a
while, the recipients feigns appreciation.
If "not that I know of" is not responsive enough for you, then maybe you
know of a better place to get more responsive answers, and should try there.
<shrug><sigh><resisting smacking own forehead>

Enterprise Manager Behaviour for SQL 2000

Thanks in Advance,
I am seeing a strange behavior in the query interface on EM that is
new for me, and it has only occurred in a new environment. So I
believe that this is a configuration setting that one of you can point
out to me.
We I have multiple query windows open, if I turn my attention to
another task in remote desktop, the reminder that a query window has
not been in use for sometime pops up and asks if it can clear the
results. In this environment, that window somehow gets trapped behind
the query windows. This creates a catch 22. I cannot move the focus
to close any of the query windows to reveal the system message, and I
cannot get to the system message to allow me to move the focus.
Is there a configuration solution here?
Mark Simmerman
Napa, CADon't use "Open Table" in Enterprise Manager. Run a SELECT query in Query
Analyzer.
In Enteprise Manager, you are maintaining an association with the source
table (so that you can treat SQL Server like Excel, and edit a "cell"), and
this causes resource drain no matter how long you let it sit there.
<xxxdbaxxx@.gmail.com> wrote in message
news:1170262471.222466.129540@.l53g2000cwa.googlegroups.com...
> Thanks in Advance,
> I am seeing a strange behavior in the query interface on EM that is
> new for me, and it has only occurred in a new environment. So I
> believe that this is a configuration setting that one of you can point
> out to me.
> We I have multiple query windows open, if I turn my attention to
> another task in remote desktop, the reminder that a query window has
> not been in use for sometime pops up and asks if it can clear the
> results. In this environment, that window somehow gets trapped behind
> the query windows. This creates a catch 22. I cannot move the focus
> to close any of the query windows to reveal the system message, and I
> cannot get to the system message to allow me to move the focus.
> Is there a configuration solution here?
> Mark Simmerman
> Napa, CA
>|||On Jan 31, 10:52 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Don't use "Open Table" in Enterprise Manager. Run a SELECT query in Query
> Analyzer.
> In Enteprise Manager, you are maintaining an association with the source
> table (so that you can treat SQL Server like Excel, and edit a "cell"), an
d
> this causes resource drain no matter how long you let it sit there.
> <xxxdba...@.gmail.com> wrote in message
> news:1170262471.222466.129540@.l53g2000cwa.googlegroups.com...
>
>
>
>
>
>
> - Show quoted text -
OK. Other than telling me to use another tool (in SQL 2005 they are
one and the same), is there any solution or configuration setting that
can address this?
Mark|||> OK. Other than telling me to use another tool (in SQL 2005 they are
> one and the same),
Yes, but you still have different options, like Open Table (bad), and query
windows (good).

> is there any solution or configuration setting that
> can address this?
Not that I know of.|||xxxdbaxxx@.gmail.com wrote:
> OK. Other than telling me to use another tool (in SQL 2005 they are
> one and the same), is there any solution or configuration setting that
> can address this?
> Mark
>
No, they're not really "one and the same". You can "open" tables using
a GUI, ala Enterprise Manager, or you can query them properly ala Query
Analyzer. The only difference in 2005 is that you don't "launch" two
seperate applications.
The limitations are the same - opening a table through the GUI is a
heavy process, prone to blocking. The GUI tools are acceptable for a
development environment, but should not be used against a production
system. Manipulating your tables properly, using T-SQL commands, is the
preferred method.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Feb 1, 7:34 am, Tracy McKibben <t...@.realsqlguy.com> wrote:
> xxxdba...@.gmail.com wrote:
>
>
> No, they're not really "one and the same". You can "open" tables using
> a GUI, ala Enterprise Manager, or you can query them properly ala Query
> Analyzer. The only difference in 2005 is that you don't "launch" two
> seperate applications.
> The limitations are the same - opening a table through the GUI is a
> heavy process, prone to blocking. The GUI tools are acceptable for a
> development environment, but should not be used against a production
> system. Manipulating your tables properly, using T-SQL commands, is the
> preferred method.
> --
> Tracy McKibben
> MCDBAhttp://www.realsqlguy.com
OK. I am in a development environment. I would still like to know if
anyone has an answer to the question. It is an annoying behavior on
the part of the GUI. Do you guys get points for non-responsive
responses?
Mark|||> OK. I am in a development environment. I would still like to know if
> anyone has an answer to the question. It is an annoying behavior on
> the part of the GUI. Do you guys get points for non-responsive
> responses?
We don't get "points" for anything. We are here, volunteering our time to
help other people solve problems we might have insight on. Every once in a
while, the recipients feigns appreciation.
If "not that I know of" is not responsive enough for you, then maybe you
know of a better place to get more responsive answers, and should try there.
<shrug><sigh><resisting smacking own forehead>

Enterprise Manager - SQL Server does not exist or access denied

Hi there,

We have a production server in our environment that I am the only one in the company who is having a problem hitting with Enterprise manager. The server is called 'PRODSQLSRV'. When I ping the server, I get the IP. When I try to connect to it using Enterprise manager, it shows up on the list of available servers, but then when I move it from the left box to the right box of the 'New SQL Server Registration' process, take the rest of the defaults, I get the message 'PRODSQLSRV - SQL Server does not exist or access denied'.

I have checked and I have both TCP/IP and Named Pipes enabled. At the suggestion of the Network admin, I manually typed in the server and domain name as 'PRODSQLSRV.VOLCANO.COM' (all names have been changed to protect the innocent) which is the server name + domain name. With this 'Enterprise manager works fine getting to the server. All this would be fine and good I guess, but none of our vendor written software which accesses that server works on my machine either. And they are not enclined to change their software just for my machine.

If anyone has any suggestions, it would be greatly appreciated. Thanks! - Eric-

I am not sure what is cuasing.. it may be dns resolution issue...

You can add the name in host file or

You can create an alias in sql server "Client Network utility" to resolve this issue.

|||I tried both the HOST file entry and the Client Network Utility suggestion and neither one resolved the issue. Sorry. |||

looks more of a DNS issue but did you try registering with IP address only and not with the name, checking the TCPIP port at the server and client, all the required services at the server running and if other ppl in your network able to get it in their EM

|||Turned out that we had two machines with the same IP hard coded in them. Thanks for pointing me in the right direction. _- Eric -

Enterprise Manager - SQL Server does not exist or access denied

Hi there,

We have a production server in our environment that I am the only one in the company who is having a problem hitting with Enterprise manager. The server is called 'PRODSQLSRV'. When I ping the server, I get the IP. When I try to connect to it using Enterprise manager, it shows up on the list of available servers, but then when I move it from the left box to the right box of the 'New SQL Server Registration' process, take the rest of the defaults, I get the message 'PRODSQLSRV - SQL Server does not exist or access denied'.

I have checked and I have both TCP/IP and Named Pipes enabled. At the suggestion of the Network admin, I manually typed in the server and domain name as 'PRODSQLSRV.VOLCANO.COM' (all names have been changed to protect the innocent) which is the server name + domain name. With this 'Enterprise manager works fine getting to the server. All this would be fine and good I guess, but none of our vendor written software which accesses that server works on my machine either. And they are not enclined to change their software just for my machine.

If anyone has any suggestions, it would be greatly appreciated. Thanks! - Eric-

I am not sure what is cuasing.. it may be dns resolution issue...

You can add the name in host file or

You can create an alias in sql server "Client Network utility" to resolve this issue.

|||I tried both the HOST file entry and the Client Network Utility suggestion and neither one resolved the issue. Sorry. |||

looks more of a DNS issue but did you try registering with IP address only and not with the name, checking the TCPIP port at the server and client, all the required services at the server running and if other ppl in your network able to get it in their EM

|||Turned out that we had two machines with the same IP hard coded in them. Thanks for pointing me in the right direction. _- Eric -

Enterprise Deployment of SSIS

Hello, we are interested in upgrading our Data Warehouse environment to SQL Server 2005 to take advantage of SSIS. Our current data warehouse environment consists of 2 production servers with over 900 DTS packages between the 2 servers.

Are there any white papers out there detailing what type of deployment works well for an Enterprise wide use of SSIS? We're trying to determine if we should use SSIS on the same server hosting our databases, or separate SSIS onto its own dedicated box. If we went the dedicated route, what type of horsepower would we need on the SSIS box (procs, RAM, etc.)? Also, if we went the dedicated route, does it make sense to have 1 SSIS server that handles the data warehouse packages and the OLTP packages, or should that be separated into 2 SSIS servers having one server for each environment?

Thanks for any help you can provide on this subject!

Eric

the 32 bit version of ssis can use a maximum 3 GB of RAM. because of this limitation, i recommend using the 64 bit version instead (if possible).