Running Upgrade Advisor Against Production

Is it safe to run the Upgrade Advisor against production servers?

Thanks in advance!

My suggestion would be to backup your DB and capture a trace of your daily activity. Then run upgrade advisor on the DB and trace file.

Thanks,
Sam Lester (MSFT)

Running the same SQL on several servers

I frequently do updates to Stored Procedures (and other types of SQL queries
too) on serveral server installations across our LAN/WAN.
Currently, I connect to each server in SSMS and run the code in a new query
window for each, which is a bit of a pain. Is there a way I can run the
script in one query window, but run it several times - each pointing to
different server. For example, is there an equivalent to the USE DB_NAME
statement, that reflects the server as well?"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:OG0O3eRSGHA.1608@.TK2MSFTNGP09.phx.gbl...
>I frequently do updates to Stored Procedures (and other types of SQL
>queries too) on serveral server installations across our LAN/WAN.
> Currently, I connect to each server in SSMS and run the code in a new
> query window for each, which is a bit of a pain. Is there a way I can run
> the script in one query window, but run it several times - each pointing
> to different server. For example, is there an equivalent to the USE
> DB_NAME statement, that reflects the server as well?
>
Right-click on the query window and choose Connection\Change Connection, or
use SqlCmd.
David

Running the same query on multiple servers

Hi, I periodically run the same query on multiple servers. Currently I
do this via query analyzer and would like to cut down the time it takes
to switch servers.
Is there a tool I can configure multiple connections on and run the same
query on selected servers.
Thanks
Dilan
create linked servers and run the queries as
select ... from servername.databasename.objectname
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT Community Leader]
SQL Server Technologist
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Dilan A" <dilan.a@.youtelus.net> wrote in message
news:up7hf.129920$S4.96172@.edtnps84...
> Hi, I periodically run the same query on multiple servers. Currently I do
> this via query analyzer and would like to cut down the time it takes to
> switch servers.
> Is there a tool I can configure multiple connections on and run the same
> query on selected servers.
> Thanks
> Dilan
|||Dandy Weyn [Dandyman] wrote:
> create linked servers and run the queries as
> select ... from servername.databasename.objectname
>
Thanks. I neglected to mention that the statements are always almost
updates to existing stored procedures and are supplied by our vendor as
patches. I received them as attachments in emails and have to save them
and then run them on about 8 servers.
Needless to say I am looking to click and go...
|||You can either use DMO or oSql to do this pretty easily. Both methods allow
you to easily connect to another server and issue the same commands. You do
have to write some code but it should only be a few lines and a google
search will most likely find some examples.
Andrew J. Kelly SQL MVP
"Dilan A" <dilan.a@.youtelus.net> wrote in message
news:up7hf.129920$S4.96172@.edtnps84...
> Hi, I periodically run the same query on multiple servers. Currently I do
> this via query analyzer and would like to cut down the time it takes to
> switch servers.
> Is there a tool I can configure multiple connections on and run the same
> query on selected servers.
> Thanks
> Dilan
|||I tend to just use a batch file e.g. create 3 folders Output,Servers,Source
under a main folder (in my example it's DBADeploy). Create a batchfile as
below called deploy.bat in C:\DBADeploy
@.echo off
FOR /F %%f in (C:\DBADeploy\Servers\servers.txt) do
C:\DBADeploy\Source\Update.bat %%f
In the Servers folder create a file called servers.txt which is a list of
the target servers
In the Source folder create a file called update.sql with you code in it
(including USE database statements) and a batch file called Update.bat with
the contents below
@.echo off
set server=%1
set outputtemp=%1
set outputtemp=%outputtemp:\=_%
set outputfile="C:\DBADeploy\Output\%outputtemp%.txt"
osql -S %server% -d master -n -E -w 200 -h-1 -l 15 -i
"C:\DBADeploy\Source\update.sql" -o %outputfile%
echo Completed %1
Once that's setup then anything you want to deploy you can just stick in
update.sql and run deploy.bat and you're done. You can check the output for
each server in the Output folder.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dilan A" <dilan.a@.youtelus.net> wrote in message
news:up7hf.129920$S4.96172@.edtnps84...
> Hi, I periodically run the same query on multiple servers. Currently I do
> this via query analyzer and would like to cut down the time it takes to
> switch servers.
> Is there a tool I can configure multiple connections on and run the same
> query on selected servers.
> Thanks
> Dilan

Running Stored Procedures on Linked servers from Microsoft SQL server

Hi,
I am trying to invoke storedprocedures on a linked server from MS SQL SERVER and it fails to recognise the object as a SP rather it gives me a message saying that the object does not have any columns. The Linked server is a SYBASE server. I can access all the tables and views but not the procedures. By the way, I have set the following server options on for the linked sybase server --
RPC OUT
RPC
But the Use Remote Collations is not turned on.

Please help!!!!!!!!!!!!!!!

Regards

JCI ran into something similar before when trying to execute SPs on a linked server (although they both were MS SQL Server)...

I remember having to use a fully qualified procedure name when I executed it:

EXEC LinkedServerName.DbName.DbOwner.SPName xxx,xxx,...

I dunno if this is applicable with your situation as I have never linked to another db platform other than from MS SQL Server to MS Access. I've use Sybase ASE before...but never in a linked scenario...

Kael|||I got your email stating that using a fully qualified name didn't work.

An idea I have is maybe examining the parameters that were used for the sp_addlinkedserver stored proc when the link was made from Sybase to MS SQL Server. Try dropping the linked server and recreating it.

Also, you can try using the OpenQuery function in the FROM clause on SQL Server rather than using a fully qualified name.

Are you using the OLE DB Provider for Sybase SQL Server (or named something like that) as the data provider when the linked server SP was used? Maybe try updating the DLL for that provider. I'd use OLE DB rather than a generic provider since it will expose more functionality.

Good luck!

Kael

running ssis package with ssis run time compoenents and sql server 2000...

running ssis package with ssis run time compoenents and sql server 2000...

Is it possible to run ssis packages that point to servers on sql server 2000
without installing sql server 2005 ?

Can we just install runtime for ssis and run the packages ?

Please explian with links if possible

thanks a lot

Simple answer. Yes, SSIS can access SQL Server 2000. And yes, you can install SSIS only without SQL Server databsae engine.

-Jamie

|||

Hi,

Something like how to do the installation of the SSIS package with the entire sql 2005 server engine? How to install it besides the SQL 2000 server? Are there any configurations to be done while installing SSIS or any setting in any config files to make them work toghether.

Thanks,

$wapnil

|||

spattewar wrote:

Hi,

Something like how to do the installation of the SSIS package with the entire sql 2005 server engine? How to install it besides the SQL 2000 server? Are there any configurations to be done while installing SSIS or any setting in any config files to make them work toghether.

Thanks,

$wapnil

The install is fairly straight forward. I don't think there are any special switches that you need to flick under the circumstances that you talk about.

One thing, If you you are installing a named instance of the SQL Server engine then you will need to change the SSIS Service configuration. See here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/06/08/15765.aspx

-Jamie

|||

Thank you.

But If I have to run a SSIS package with SQL 2K instance? Where will be the package stored - in msdb? or it can run if from a file?

If you want me to create a new thread for this then let me know.

Thanks for your time.

$wapnil

|||

spattewar wrote:

Thank you.

But If I have to run a SSIS package with SQL 2K instance? Where will be the package stored - in msdb? or it can run if from a file?

If you want to store the package in msdb then you will need a SQL 2005 instance.

Although yes, you can run from a file, and in that scenario you don't need ANY SQL Server instances. I always choose to store in files by the way.

spattewar wrote:

If you want me to create a new thread for this then let me know.

No worries. I can split the thread if necassary.

-Jamie

|||

This is great.

So to just summarize.

1) On the production machine that I have SQL 2000 server installed, I will install SSIS component only.

2) Develop the SSIS packages on my machine using VS 2.0 and SSIS.

3) Save the packages in files with *.dtsx and configuration files *.xml

4) Move the files to the production machine and execute the files using DTExec.

IS this correct

Thanks again.

$wapnil

|||

spattewar wrote:

This is great.

So to just summarize.

1) On the production machine that I have SQL 2000 server installed, I will install SSIS component only.

2) Develop the SSIS packages on my machine using VS 2.0 and SSIS.

3) Save the packages in files with *.dtsx and configuration files *.xml

4) Move the files to the production machine and execute the files using DTExec.

IS this correct

Thanks again.

$wapnil

It sounds fine to me, yes.

-Jamie

|||thanks guys.this is exactly what I was looking for.|||

This thread help me to find some answers. We have several clients and some of them are still with SQL 2000. I was asked to develop ETL which needs to work on SQL 2005 and SQL 2000. Are SSIS runtime components are downloadable with free of charge or is it possible that we can package with our installer (are these can be redistributable with free of charge). Answer to this question helps me whether I need to develop ETL using DTS or SSIS. I am not sure whether I need to post this one as a seperate thread or not.

Thanks

|||

rao_ssis_dts wrote:

This thread help me to find some answers. We have several clients and some of them are still with SQL 2000. I was asked to develop ETL which needs to work on SQL 2005 and SQL 2000. Are SSIS runtime components are downloadable with free of charge or is it possible that we can package with our installer (are these can be redistributable with free of charge). Answer to this question helps me whether I need to develop ETL using DTS or SSIS. I am not sure whether I need to post this one as a seperate thread or not.

Thanks

SSIS is not free, it is not downloadable; it comes part of SQL Server Standard, Developer, or Enterprise editions. SSIS is not redistributable either, unlike DTS. A license is required wherever SSIS may be installed.|||

Thank you.

I guess then I don't have choice, I have to write ETL using DTS.

|||

Hi Jamie ,

I am facing the same scenario as above ,with the benefit of a client that is willing to pay for CAL's for ssis ,is there any way I can bootstrap the install of SSIS into a install for an application

Thanks in advance

Cedric

running ssis package with ssis run time compoenents and sql server 2000...

running ssis package with ssis run time compoenents and sql server 2000...

Is it possible to run ssis packages that point to servers on sql server 2000
without installing sql server 2005 ?

Can we just install runtime for ssis and run the packages ?

Please explian with links if possible

thanks a lot

Simple answer. Yes, SSIS can access SQL Server 2000. And yes, you can install SSIS only without SQL Server databsae engine.

-Jamie

|||

Hi,

Something like how to do the installation of the SSIS package with the entire sql 2005 server engine? How to install it besides the SQL 2000 server? Are there any configurations to be done while installing SSIS or any setting in any config files to make them work toghether.

Thanks,

$wapnil

|||

spattewar wrote:

Hi,

Something like how to do the installation of the SSIS package with the entire sql 2005 server engine? How to install it besides the SQL 2000 server? Are there any configurations to be done while installing SSIS or any setting in any config files to make them work toghether.

Thanks,

$wapnil

The install is fairly straight forward. I don't think there are any special switches that you need to flick under the circumstances that you talk about.

One thing, If you you are installing a named instance of the SQL Server engine then you will need to change the SSIS Service configuration. See here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/06/08/15765.aspx

-Jamie

|||

Thank you.

But If I have to run a SSIS package with SQL 2K instance? Where will be the package stored - in msdb? or it can run if from a file?

If you want me to create a new thread for this then let me know.

Thanks for your time.

$wapnil

|||

spattewar wrote:

Thank you.

But If I have to run a SSIS package with SQL 2K instance? Where will be the package stored - in msdb? or it can run if from a file?

If you want to store the package in msdb then you will need a SQL 2005 instance.

Although yes, you can run from a file, and in that scenario you don't need ANY SQL Server instances. I always choose to store in files by the way.

spattewar wrote:

If you want me to create a new thread for this then let me know.

No worries. I can split the thread if necassary.

-Jamie

|||

This is great.

So to just summarize.

1) On the production machine that I have SQL 2000 server installed, I will install SSIS component only.

2) Develop the SSIS packages on my machine using VS 2.0 and SSIS.

3) Save the packages in files with *.dtsx and configuration files *.xml

4) Move the files to the production machine and execute the files using DTExec.

IS this correct

Thanks again.

$wapnil

|||

spattewar wrote:

This is great.

So to just summarize.

1) On the production machine that I have SQL 2000 server installed, I will install SSIS component only.

2) Develop the SSIS packages on my machine using VS 2.0 and SSIS.

3) Save the packages in files with *.dtsx and configuration files *.xml

4) Move the files to the production machine and execute the files using DTExec.

IS this correct

Thanks again.

$wapnil

It sounds fine to me, yes.

-Jamie

|||thanks guys.this is exactly what I was looking for.|||

This thread help me to find some answers. We have several clients and some of them are still with SQL 2000. I was asked to develop ETL which needs to work on SQL 2005 and SQL 2000. Are SSIS runtime components are downloadable with free of charge or is it possible that we can package with our installer (are these can be redistributable with free of charge). Answer to this question helps me whether I need to develop ETL using DTS or SSIS. I am not sure whether I need to post this one as a seperate thread or not.

Thanks

|||

rao_ssis_dts wrote:

This thread help me to find some answers. We have several clients and some of them are still with SQL 2000. I was asked to develop ETL which needs to work on SQL 2005 and SQL 2000. Are SSIS runtime components are downloadable with free of charge or is it possible that we can package with our installer (are these can be redistributable with free of charge). Answer to this question helps me whether I need to develop ETL using DTS or SSIS. I am not sure whether I need to post this one as a seperate thread or not.

Thanks

SSIS is not free, it is not downloadable; it comes part of SQL Server Standard, Developer, or Enterprise editions. SSIS is not redistributable either, unlike DTS. A license is required wherever SSIS may be installed.|||

Thank you.

I guess then I don't have choice, I have to write ETL using DTS.

|||

Hi Jamie ,

I am facing the same scenario as above ,with the benefit of a client that is willing to pay for CAL's for ssis ,is there any way I can bootstrap the install of SSIS into a install for an application

Thanks in advance

Cedric

Running SQLServer and SQLServer Agent as Power User

We're trying to limit the number of user accounts with Admin level permissio
n
on our Win2K servers, especially SQL servers. We have created a domain level
account to run SQLServer and SQLAgent. We'd like to limit it to Power User
status instead of Admin status on the servers, but we cannot seem to start
and stop the services from SEM with only Power User status. We've checked
registry key permissions and everything seems to be configured properly. Is
this configuration even possible? Or does this account NEED to be local admi
n
on the server? Help would be appreciated. Thanks.The account that starts the services needs to have the "log on as a service"
right. Without this MSSQLServer and MS SQL Server Agent will not start.
It would also be more secure to use a domain account for this rather than a
local account as SQL then benefits from the integrated security of Windows
2000.
Also why would you want end users to have admin rights on the server at all?
This defeats the object of system security and resource accessibility. It
is best that they are Doman Users only then assign access rights to shares
on the servers.
HTH
Regards
Dazza
"gbledsoe" <gbledsoe@.discussions.microsoft.com> wrote in message
news:72CDD311-3C73-480E-9734-3E6F0E76DB09@.microsoft.com...
> We're trying to limit the number of user accounts with Admin level
> permission
> on our Win2K servers, especially SQL servers. We have created a domain
> level
> account to run SQLServer and SQLAgent. We'd like to limit it to Power User
> status instead of Admin status on the servers, but we cannot seem to start
> and stop the services from SEM with only Power User status. We've checked
> registry key permissions and everything seems to be configured properly.
> Is
> this configuration even possible? Or does this account NEED to be local
> admin
> on the server? Help would be appreciated. Thanks.|||We've following the instructions in MS article 283811 and ensured that the
account has all necessary extended user rights, such as act as part of
operating system, logon as batch job, logon as service. The fundamental
question is whether the account can run as Power User or does it need to be
Administrator? If it does not NEED to be Administrator, what other
configuration is necessary to let us use that account to stop and start the
SQLServer service, since Power User does not seem to have the rights. Thanks
.
"Dazza" wrote:

> The account that starts the services needs to have the "log on as a servic
e"
> right. Without this MSSQLServer and MS SQL Server Agent will not start.
> It would also be more secure to use a domain account for this rather than
a
> local account as SQL then benefits from the integrated security of Windows
> 2000.
> Also why would you want end users to have admin rights on the server at al
l?
> This defeats the object of system security and resource accessibility. It
> is best that they are Doman Users only then assign access rights to shares
> on the servers.
> HTH
> Regards
> Dazza
>
> "gbledsoe" <gbledsoe@.discussions.microsoft.com> wrote in message
> news:72CDD311-3C73-480E-9734-3E6F0E76DB09@.microsoft.com...
>
>|||gbledsoe wrote:
> We're trying to limit the number of user accounts with Admin level permiss
ion
> on our Win2K servers, especially SQL servers. We have created a domain lev
el
> account to run SQLServer and SQLAgent. We'd like to limit it to Power User
> status instead of Admin status on the servers, but we cannot seem to start
> and stop the services from SEM with only Power User status. We've checked
> registry key permissions and everything seems to be configured properly. I
s
> this configuration even possible? Or does this account NEED to be local ad
min
> on the server? Help would be appreciated. Thanks.
It should be, although not all sql feature are available. I am running
multiple instances with different plain domain user accounts. When you are
not sure about registry, user and ntfs permission change the account using
the enterprise manager.
When you need the proxy account to run scheduled dts packages create a
separate account for the sql agent service and make it local admin, unless
someone here can explain how to accomplice this without local admin rights.
have a look at this one:
http://support.microsoft.com/defaul...;283811&sd=tech
Hans

Running SQL Mail in developers edition

We have SQL Mail running on our servers, but Im often offline during
development (although my machine is on the internet). Can SQL Mail be
configured to run locally in SQL Server 200 Developers Edition? The
configuration docs for pop accounts are confusing in that regard (at least to
me), and I was hoping for some advice - if this is even possible.
Thanks, Mark
Hi
If you setup SQL Server to run under a domain account and use Outlook 2000
you should be ok see http://support.microsoft.com/kb/263556/. You should be
able to log out (but not shutdown!) and emails will still be processed.
John
"MarkAurit" wrote:

> We have SQL Mail running on our servers, but Im often offline during
> development (although my machine is on the internet). Can SQL Mail be
> configured to run locally in SQL Server 200 Developers Edition? The
> configuration docs for pop accounts are confusing in that regard (at least to
> me), and I was hoping for some advice - if this is even possible.
> Thanks, Mark

Running SQL Mail in developers edition

We have SQL Mail running on our servers, but Im often offline during
development (although my machine is on the internet). Can SQL Mail be
configured to run locally in SQL Server 200 Developers Edition? The
configuration docs for pop accounts are confusing in that regard (at least to
me), and I was hoping for some advice - if this is even possible.
Thanks, MarkHi
If you setup SQL Server to run under a domain account and use Outlook 2000
you should be ok see http://support.microsoft.com/kb/263556/. You should be
able to log out (but not shutdown!) and emails will still be processed.
John
"MarkAurit" wrote:
> We have SQL Mail running on our servers, but Im often offline during
> development (although my machine is on the internet). Can SQL Mail be
> configured to run locally in SQL Server 200 Developers Edition? The
> configuration docs for pop accounts are confusing in that regard (at least to
> me), and I was hoping for some advice - if this is even possible.
> Thanks, Mark

Running SQL Mail in developers edition

We have SQL Mail running on our servers, but Im often offline during
development (although my machine is on the internet). Can SQL Mail be
configured to run locally in SQL Server 200 Developers Edition? The
configuration docs for pop accounts are confusing in that regard (at least t
o
me), and I was hoping for some advice - if this is even possible.
Thanks, MarkHi
If you setup SQL Server to run under a domain account and use Outlook 2000
you should be ok see http://support.microsoft.com/kb/263556/. You should be
able to log out (but not shutdown!) and emails will still be processed.
John
"MarkAurit" wrote:

> We have SQL Mail running on our servers, but Im often offline during
> development (although my machine is on the internet). Can SQL Mail be
> configured to run locally in SQL Server 200 Developers Edition? The
> configuration docs for pop accounts are confusing in that regard (at least
to
> me), and I was hoping for some advice - if this is even possible.
> Thanks, Mark

Running SQL and Oracle on the same server

I know that this is primarily a SQL server group, but I am hoping
someone has experience in running Oracle alongside SQL servers. We
currently only have SQL server installations in our organization.
However one of our department is considering buying a third-party
application that runs on Oracle. We are interested to find out any
problems that people might have experienced in such an environment. I
am doing some research on this subject to find out what I can, but I
thought I would look to the experts on this.

Any input would be greatly appreciated.

Thanks

KarthikaI did that at several customer site, due to the lack of budget of the
customers and it worked. They are using a different port, so network
traffic will be separated. The only thing that would make me thinking
of a dedicated box would be performance, which can lousy if the
database are load intensive and the feeling of a Single point of
failure.

HTH, jens Suessmeyer.

--
http://www.sqlserver2005.de
--|||KR wrote:
> I know that this is primarily a SQL server group, but I am hoping
> someone has experience in running Oracle alongside SQL servers. We
> currently only have SQL server installations in our organization.
> However one of our department is considering buying a third-party
> application that runs on Oracle. We are interested to find out any
> problems that people might have experienced in such an environment. I
> am doing some research on this subject to find out what I can, but I
> thought I would look to the experts on this.
> Any input would be greatly appreciated.
> Thanks
> Karthika

We do it a lot out here. In fact most companies run some combination
of databases: Many Oracle with SQL Server.

The only real issue is training. The underlying architecture,
concepts, and design of Oracle are substantially different from
SQL Server which is based on the Ingres model. If you need someone
to provide SQL Server to Oracle training contact me off-line and I
will see if I know someone in your area.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org