How do I append string to end of sql query results?

I need to append text to the end of my sql query results.

For instance, my query returns a list of user names in
firstname.lastname. I need to add @.yahoo.com to each record. So the
end result should be firstname.lastname@.yahoo.com. What should by
select statement look like?

Any help?"diesel" <dieselpb03@.yahoo.com> skrev i en meddelelse
news:854ece22.0407131219.6f38c10a@.posting.google.c om...
> I need to append text to the end of my sql query results.
> For instance, my query returns a list of user names in
> firstname.lastname. I need to add @.yahoo.com to each record. So the
> end result should be firstname.lastname@.yahoo.com. What should by
> select statement look like?
> Any help?

SELECT Firstname + '.' + Lastname + '@.yahoo.com' AS Name
FROM tblPerson

easypeasy

--
Med venlig hilsen

Tom F Jensen
FFSoft
www.ffsoft.dk|||Maybe:

SELECT name + '@.yahoo.com'
FROM YourTable

--
David Portas
SQL Server MVP
--

How do disable a user to "Generate SQL Scripts"

I have an SQLServer 2000 instance with a DataBase.
I have 2 users: sa, new_user.
The [new_user] has membership role: public, db_datareader, db_datawriter
It has permissions to manipulate some table and stored procedures. He can
not export, import, backup, restore the data base.
The point is that he can generate a script for the all database!!!
How do I disable it?
Can some one give me some help on this issue.
Thanks,
JoaoRegoSince you give the user db_datareader membership, then they can read
anything in the database, including the structure of the tables.
You can deny permissions to system tables like sysobjects to prevent listing
tables for example, like so ->
deny select on sysobjects to user1
This would prevent user1 from getting a list of tables for example.
Another common scheme is to only allow user's access to views and totally
restrict access to the actual underlying tables.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Joao Rego" <Joao Rego@.discussions.microsoft.com> wrote in message
news:4B2E9F96-E012-4CF3-8033-A19A6E0DD797@.microsoft.com...
>I have an SQLServer 2000 instance with a DataBase.
> I have 2 users: sa, new_user.
> The [new_user] has membership role: public, db_datareader, db_datawrit
er
> It has permissions to manipulate some table and stored procedures. He can
> not export, import, backup, restore the data base.
> The point is that he can generate a script for the all database!!!
> How do I disable it?
> Can some one give me some help on this issue.
> Thanks,
> JoaoRegosql

How difficult is it to uninstall/re-install a second SQL named instance?

Gurus,
Running SQL Server 2005 SP2 and using two named instances on a Windows
Server 2003 SP2 cluster. How difficult would it be to uninstall/re-install
the second SQL instance?
--
SpinIt's really no different from installing or un-installing the first
instance.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Spin" <Spin@.invalid.com> wrote in message
news:65f06eF2fjee4U1@.mid.individual.net...
Gurus,
Running SQL Server 2005 SP2 and using two named instances on a Windows
Server 2003 SP2 cluster. How difficult would it be to uninstall/re-install
the second SQL instance?
--
Spin|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iVfXfAlIHA.6092@.TK2MSFTNGP06.phx.gbl...
> It's really no different from installing or un-installing the first
> instance.
> --
> Tom
What I'm worried about is pitfalls to avoid. I want to avoid messing
something up in my cluster.|||None that I know of.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Spin" <Spin@.invalid.com> wrote in message
news:65f4a5F2dle3mU1@.mid.individual.net...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iVfXfAlIHA.6092@.TK2MSFTNGP06.phx.gbl...
> It's really no different from installing or un-installing the first
> instance.
> --
> Tom
What I'm worried about is pitfalls to avoid. I want to avoid messing
something up in my cluster.

How difficult is it to uninstall/re-install a second SQL named instance?

Gurus,
Running SQL Server 2005 SP2 and using two named instances on a Windows
Server 2003 SP2 cluster. How difficult would it be to uninstall/re-install
the second SQL instance?
Spin
It's really no different from installing or un-installing the first
instance.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Spin" <Spin@.invalid.com> wrote in message
news:65f06eF2fjee4U1@.mid.individual.net...
Gurus,
Running SQL Server 2005 SP2 and using two named instances on a Windows
Server 2003 SP2 cluster. How difficult would it be to uninstall/re-install
the second SQL instance?
Spin
|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iVfXfAlIHA.6092@.TK2MSFTNGP06.phx.gbl...
> It's really no different from installing or un-installing the first
> instance.
> --
> Tom
What I'm worried about is pitfalls to avoid. I want to avoid messing
something up in my cluster.
|||None that I know of.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Spin" <Spin@.invalid.com> wrote in message
news:65f4a5F2dle3mU1@.mid.individual.net...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iVfXfAlIHA.6092@.TK2MSFTNGP06.phx.gbl...
> It's really no different from installing or un-installing the first
> instance.
> --
> Tom
What I'm worried about is pitfalls to avoid. I want to avoid messing
something up in my cluster.

how did you upgrade from analysis services 2000 to 2005?

i have followed the instructions online ,on how to upgrade from analysis server 2000 to 2005. i install a new instance of analysis services 2005, called instance 1, migrate all the cubes over, but when it comes to using the rename instance tool, i get errors. there is a reg key missing i have found that stops this tool from working. how have you guys gotten around the migration from 2000 to 2005?

What machine are you is it 64bit platform?

There are 2 versions of ASInstanceRename utilities. You should use one that is installed along with 64bit version of Analysis Services in the C:\Program Files\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE folder.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

thats the version of the instanceRename tool but when i try to rename an instance i get this error:

Cannot open regestry key SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\90\Sql Bin under HKEY_LOCAL_MACHINE

Thats the only version of the tool i have on my server. I installed sql server 2005 enterprise edition, not the 64bit version though as the server im using is an old server ( used for testing) and is only a 32 bit server.

How define users of another domain.

Hi everybody, i should have a little problem.
I must grant the access (using odbc) to users of domain "A" to a sql server
2000 instance running in another domain (domain "B").
Well, if the users read the database like user "sa" (defined in the odbc
connection) there aren't problems but if in the odbc connection is specified
the "trust connection" flag nobody can read data.
In sql server the users are defined both username and domain\username and
permissions are defined correctly.
What could i do?
Thanks in avance for your answers.
RobyHi
If your SQL Server is in Domain A, you need to setup a trust relationship
with Domain B at Active Directory level.
Then, when use "B\user1" presents it's credentials to the SQL Server, SQL
Server passes the request to the OS, which in turn passes it to Domain A's
domain controller, who in turn passes the request to Domain B's domain
controller.
The trusting is done at OS level. If you can not setup a trust, then you
need to keep using SQL Server Security.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"robyemme" <marautor@.tiscali.it> wrote in message
news:F1657283-F17A-40FC-9061-C0BFBA7DC569@.microsoft.com...
> Hi everybody, i should have a little problem.
> I must grant the access (using odbc) to users of domain "A" to a sql
> server
> 2000 instance running in another domain (domain "B").
> Well, if the users read the database like user "sa" (defined in the odbc
> connection) there aren't problems but if in the odbc connection is
> specified
> the "trust connection" flag nobody can read data.
> In sql server the users are defined both username and domain\username and
> permissions are defined correctly.
> What could i do?
> Thanks in avance for your answers.
> Roby
>

How convert an named instance to a non named instance

Hello:
Our Application Vendor has now indicated that we need to have a non-named
instance because of their software and Analysis Services. How do we do this?
A named instance is a distinct copy of all of the sql server binaries, data
files, services, etc. If you want to put your data on the default instance,
you will need to backup your databases on the named instnace and restore them
onto the default instance. You can then use the sql server installation tool
to remove the named instance.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"Victoria Morrison" wrote:

> Hello:
> Our Application Vendor has now indicated that we need to have a non-named
> instance because of their software and Analysis Services. How do we do this?
>
|||How do I create the default instance which is on the same server. I tried
it with the sql server installation and only lets you do a named instance.
When you say sql server installation tool you mean using the CD
"AndyP" wrote:
[vbcol=seagreen]
> A named instance is a distinct copy of all of the sql server binaries, data
> files, services, etc. If you want to put your data on the default instance,
> you will need to backup your databases on the named instnace and restore them
> onto the default instance. You can then use the sql server installation tool
> to remove the named instance.
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Victoria Morrison" wrote:
|||> How do I create the default instance which is on the same server.
By installing a default instance, i.e., the same setup process as when you installed the named
instance.

> I tried
> it with the sql server installation and only lets you do a named instance.
Then you already have a default instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
news:4154AD5D-8E4A-420E-B013-9F0B94DFB787@.microsoft.com...[vbcol=seagreen]
> How do I create the default instance which is on the same server. I tried
> it with the sql server installation and only lets you do a named instance.
> When you say sql server installation tool you mean using the CD
> "AndyP" wrote:
|||That is what I tried, I put in the SQL Server CD and when it got to piece the
default was grayed out and would not let me click it. We have the instance
presently called "srvsql02\mptp" the vendor needs as a default "srvsql02". I
think 1) backup 2)uninstall 3) reinstall 4) restore each database starting
with master??
"Tibor Karaszi" wrote:

> By installing a default instance, i.e., the same setup process as when you installed the named
> instance.
>
> Then you already have a default instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
> news:4154AD5D-8E4A-420E-B013-9F0B94DFB787@.microsoft.com...
>
>
|||> That is what I tried, I put in the SQL Server CD and when it got to piece the
> default was grayed out and would not let me click it.
That would happen if you already have a default instance. You can only have one default instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
news:80C97B24-AC86-426E-955E-F57E2DBB9476@.microsoft.com...[vbcol=seagreen]
> That is what I tried, I put in the SQL Server CD and when it got to piece the
> default was grayed out and would not let me click it. We have the instance
> presently called "srvsql02\mptp" the vendor needs as a default "srvsql02". I
> think 1) backup 2)uninstall 3) reinstall 4) restore each database starting
> with master??
> "Tibor Karaszi" wrote:
|||Ok, so how do a rename it from srvsql01\mptp to simply srvsql01
"Tibor Karaszi" wrote:

> That would happen if you already have a default instance. You can only have one default instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
> news:80C97B24-AC86-426E-955E-F57E2DBB9476@.microsoft.com...
>
>
|||Victoria Morrison wrote:
> Ok, so how do a rename it from srvsql01\mptp to simply srvsql01
>
You can't rename an instance, but in your case it sounds like you
already has a default instance. If you haven't got one already, you
should be able to install one. Since you say that this option is greyed
out, it indicates that it's already there.
Have you tried to connect to just srvsql01 in your application,
Enterprise Manager or Query Analyser? If you have, do you then get an
error message of some kind?
Regards
Steen
|||Thank all of you. It was grayed out on my Personnel copy, but when I tried
in on the test real enterprise version the options was not grayed out. And
thank for how to removed an instance that worked also. The only thing to
remember is that you have to put back the packs since the used the CD which
is at spack2. I did not know how else to get the install started. Now I
am going to detach and attach the the user databases and then add the login
user and my jobs. Since that path do not match restoring master did not
work.
"Steen Persson (DK)" wrote:

> Victoria Morrison wrote:
> You can't rename an instance, but in your case it sounds like you
> already has a default instance. If you haven't got one already, you
> should be able to install one. Since you say that this option is greyed
> out, it indicates that it's already there.
> Have you tried to connect to just srvsql01 in your application,
> Enterprise Manager or Query Analyser? If you have, do you then get an
> error message of some kind?
> Regards
> Steen
>
sql

How convert an named instance to a non named instance

Hello:
Our Application Vendor has now indicated that we need to have a non-named
instance because of their software and Analysis Services. How do we do this
?A named instance is a distinct copy of all of the sql server binaries, data
files, services, etc. If you want to put your data on the default instance,
you will need to backup your databases on the named instnace and restore the
m
onto the default instance. You can then use the sql server installation tool
to remove the named instance.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"Victoria Morrison" wrote:

> Hello:
> Our Application Vendor has now indicated that we need to have a non-named
> instance because of their software and Analysis Services. How do we do th
is?
>|||How do I create the default instance which is on the same server. I tried
it with the sql server installation and only lets you do a named instance.
When you say sql server installation tool you mean using the CD
"AndyP" wrote:
[vbcol=seagreen]
> A named instance is a distinct copy of all of the sql server binaries, dat
a
> files, services, etc. If you want to put your data on the default instance
,
> you will need to backup your databases on the named instnace and restore t
hem
> onto the default instance. You can then use the sql server installation to
ol
> to remove the named instance.
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Victoria Morrison" wrote:
>|||> How do I create the default instance which is on the same server.
By installing a default instance, i.e., the same setup process as when you i
nstalled the named
instance.

> I tried
> it with the sql server installation and only lets you do a named instance.
Then you already have a default instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in me
ssage
news:4154AD5D-8E4A-420E-B013-9F0B94DFB787@.microsoft.com...[vbcol=seagreen]
> How do I create the default instance which is on the same server. I trie
d
> it with the sql server installation and only lets you do a named instance.
> When you say sql server installation tool you mean using the CD
> "AndyP" wrote:
>|||That is what I tried, I put in the SQL Server CD and when it got to piece th
e
default was grayed out and would not let me click it. We have the instance
presently called "srvsql02\mptp" the vendor needs as a default "srvsql02".
I
think 1) backup 2)uninstall 3) reinstall 4) restore each database starting
with master'?
"Tibor Karaszi" wrote:

> By installing a default instance, i.e., the same setup process as when you
installed the named
> instance.
>
> Then you already have a default instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in
message
> news:4154AD5D-8E4A-420E-B013-9F0B94DFB787@.microsoft.com...
>
>|||> That is what I tried, I put in the SQL Server CD and when it got to piece theen">
> default was grayed out and would not let me click it.
That would happen if you already have a default instance. You can only have
one default instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in me
ssage
news:80C97B24-AC86-426E-955E-F57E2DBB9476@.microsoft.com...[vbcol=seagreen]
> That is what I tried, I put in the SQL Server CD and when it got to piece
the
> default was grayed out and would not let me click it. We have the instan
ce
> presently called "srvsql02\mptp" the vendor needs as a default "srvsql02".
I
> think 1) backup 2)uninstall 3) reinstall 4) restore each database starting
> with master'?
> "Tibor Karaszi" wrote:
>|||Ok, so how do a rename it from srvsql01\mptp to simply srvsql01
"Tibor Karaszi" wrote:

> That would happen if you already have a default instance. You can only hav
e one default instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in
message
> news:80C97B24-AC86-426E-955E-F57E2DBB9476@.microsoft.com...
>
>|||Victoria Morrison wrote:
> Ok, so how do a rename it from srvsql01\mptp to simply srvsql01
>
You can't rename an instance, but in your case it sounds like you
already has a default instance. If you haven't got one already, you
should be able to install one. Since you say that this option is greyed
out, it indicates that it's already there.
Have you tried to connect to just srvsql01 in your application,
Enterprise Manager or Query Analyser? If you have, do you then get an
error message of some kind?
Regards
Steen|||Thank all of you. It was grayed out on my Personnel copy, but when I tried
in on the test real enterprise version the options was not grayed out. And
thank for how to removed an instance that worked also. The only thing to
remember is that you have to put back the packs since the used the CD which
is at spack2. I did not know how else to get the install started. Now
I
am going to detach and attach the the user databases and then add the login
user and my jobs. Since that path do not match restoring master did not
work.
"Steen Persson (DK)" wrote:

> Victoria Morrison wrote:
> You can't rename an instance, but in your case it sounds like you
> already has a default instance. If you haven't got one already, you
> should be able to install one. Since you say that this option is greyed
> out, it indicates that it's already there.
> Have you tried to connect to just srvsql01 in your application,
> Enterprise Manager or Query Analyser? If you have, do you then get an
> error message of some kind?
> Regards
> Steen
>

How convert an named instance to a non named instance

Hello:
Our Application Vendor has now indicated that we need to have a non-named
instance because of their software and Analysis Services. How do we do this?A named instance is a distinct copy of all of the sql server binaries, data
files, services, etc. If you want to put your data on the default instance,
you will need to backup your databases on the named instnace and restore them
onto the default instance. You can then use the sql server installation tool
to remove the named instance.
--
AndyP,
Sr. Database Administrator,
MCDBA 2003
"Victoria Morrison" wrote:
> Hello:
> Our Application Vendor has now indicated that we need to have a non-named
> instance because of their software and Analysis Services. How do we do this?
>|||How do I create the default instance which is on the same server. I tried
it with the sql server installation and only lets you do a named instance.
When you say sql server installation tool you mean using the CD
"AndyP" wrote:
> A named instance is a distinct copy of all of the sql server binaries, data
> files, services, etc. If you want to put your data on the default instance,
> you will need to backup your databases on the named instnace and restore them
> onto the default instance. You can then use the sql server installation tool
> to remove the named instance.
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Victoria Morrison" wrote:
> > Hello:
> > Our Application Vendor has now indicated that we need to have a non-named
> > instance because of their software and Analysis Services. How do we do this?
> >|||> How do I create the default instance which is on the same server.
By installing a default instance, i.e., the same setup process as when you installed the named
instance.
> I tried
> it with the sql server installation and only lets you do a named instance.
Then you already have a default instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
news:4154AD5D-8E4A-420E-B013-9F0B94DFB787@.microsoft.com...
> How do I create the default instance which is on the same server. I tried
> it with the sql server installation and only lets you do a named instance.
> When you say sql server installation tool you mean using the CD
> "AndyP" wrote:
>> A named instance is a distinct copy of all of the sql server binaries, data
>> files, services, etc. If you want to put your data on the default instance,
>> you will need to backup your databases on the named instnace and restore them
>> onto the default instance. You can then use the sql server installation tool
>> to remove the named instance.
>> --
>> AndyP,
>> Sr. Database Administrator,
>> MCDBA 2003
>>
>> "Victoria Morrison" wrote:
>> > Hello:
>> > Our Application Vendor has now indicated that we need to have a non-named
>> > instance because of their software and Analysis Services. How do we do this?
>> >|||That is what I tried, I put in the SQL Server CD and when it got to piece the
default was grayed out and would not let me click it. We have the instance
presently called "srvsql02\mptp" the vendor needs as a default "srvsql02". I
think 1) backup 2)uninstall 3) reinstall 4) restore each database starting
with master'?
"Tibor Karaszi" wrote:
> > How do I create the default instance which is on the same server.
> By installing a default instance, i.e., the same setup process as when you installed the named
> instance.
>
> > I tried
> > it with the sql server installation and only lets you do a named instance.
> Then you already have a default instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
> news:4154AD5D-8E4A-420E-B013-9F0B94DFB787@.microsoft.com...
> > How do I create the default instance which is on the same server. I tried
> > it with the sql server installation and only lets you do a named instance.
> > When you say sql server installation tool you mean using the CD
> >
> > "AndyP" wrote:
> >
> >> A named instance is a distinct copy of all of the sql server binaries, data
> >> files, services, etc. If you want to put your data on the default instance,
> >> you will need to backup your databases on the named instnace and restore them
> >> onto the default instance. You can then use the sql server installation tool
> >> to remove the named instance.
> >>
> >> --
> >> AndyP,
> >> Sr. Database Administrator,
> >> MCDBA 2003
> >>
> >>
> >> "Victoria Morrison" wrote:
> >>
> >> > Hello:
> >> > Our Application Vendor has now indicated that we need to have a non-named
> >> > instance because of their software and Analysis Services. How do we do this?
> >> >
>
>|||> That is what I tried, I put in the SQL Server CD and when it got to piece the
> default was grayed out and would not let me click it.
That would happen if you already have a default instance. You can only have one default instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
news:80C97B24-AC86-426E-955E-F57E2DBB9476@.microsoft.com...
> That is what I tried, I put in the SQL Server CD and when it got to piece the
> default was grayed out and would not let me click it. We have the instance
> presently called "srvsql02\mptp" the vendor needs as a default "srvsql02". I
> think 1) backup 2)uninstall 3) reinstall 4) restore each database starting
> with master'?
> "Tibor Karaszi" wrote:
>> > How do I create the default instance which is on the same server.
>> By installing a default instance, i.e., the same setup process as when you installed the named
>> instance.
>>
>> > I tried
>> > it with the sql server installation and only lets you do a named instance.
>> Then you already have a default instance.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
>> news:4154AD5D-8E4A-420E-B013-9F0B94DFB787@.microsoft.com...
>> > How do I create the default instance which is on the same server. I tried
>> > it with the sql server installation and only lets you do a named instance.
>> > When you say sql server installation tool you mean using the CD
>> >
>> > "AndyP" wrote:
>> >
>> >> A named instance is a distinct copy of all of the sql server binaries, data
>> >> files, services, etc. If you want to put your data on the default instance,
>> >> you will need to backup your databases on the named instnace and restore them
>> >> onto the default instance. You can then use the sql server installation tool
>> >> to remove the named instance.
>> >>
>> >> --
>> >> AndyP,
>> >> Sr. Database Administrator,
>> >> MCDBA 2003
>> >>
>> >>
>> >> "Victoria Morrison" wrote:
>> >>
>> >> > Hello:
>> >> > Our Application Vendor has now indicated that we need to have a non-named
>> >> > instance because of their software and Analysis Services. How do we do this?
>> >> >
>>|||Ok, so how do a rename it from srvsql01\mptp to simply srvsql01
"Tibor Karaszi" wrote:
> > That is what I tried, I put in the SQL Server CD and when it got to piece the
> > default was grayed out and would not let me click it.
> That would happen if you already have a default instance. You can only have one default instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
> news:80C97B24-AC86-426E-955E-F57E2DBB9476@.microsoft.com...
> > That is what I tried, I put in the SQL Server CD and when it got to piece the
> > default was grayed out and would not let me click it. We have the instance
> > presently called "srvsql02\mptp" the vendor needs as a default "srvsql02". I
> > think 1) backup 2)uninstall 3) reinstall 4) restore each database starting
> > with master'?
> >
> > "Tibor Karaszi" wrote:
> >
> >> > How do I create the default instance which is on the same server.
> >>
> >> By installing a default instance, i.e., the same setup process as when you installed the named
> >> instance.
> >>
> >>
> >> > I tried
> >> > it with the sql server installation and only lets you do a named instance.
> >>
> >> Then you already have a default instance.
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
> >> news:4154AD5D-8E4A-420E-B013-9F0B94DFB787@.microsoft.com...
> >> > How do I create the default instance which is on the same server. I tried
> >> > it with the sql server installation and only lets you do a named instance.
> >> > When you say sql server installation tool you mean using the CD
> >> >
> >> > "AndyP" wrote:
> >> >
> >> >> A named instance is a distinct copy of all of the sql server binaries, data
> >> >> files, services, etc. If you want to put your data on the default instance,
> >> >> you will need to backup your databases on the named instnace and restore them
> >> >> onto the default instance. You can then use the sql server installation tool
> >> >> to remove the named instance.
> >> >>
> >> >> --
> >> >> AndyP,
> >> >> Sr. Database Administrator,
> >> >> MCDBA 2003
> >> >>
> >> >>
> >> >> "Victoria Morrison" wrote:
> >> >>
> >> >> > Hello:
> >> >> > Our Application Vendor has now indicated that we need to have a non-named
> >> >> > instance because of their software and Analysis Services. How do we do this?
> >> >> >
> >>
> >>
> >>
>
>|||Victoria Morrison wrote:
> Ok, so how do a rename it from srvsql01\mptp to simply srvsql01
>
You can't rename an instance, but in your case it sounds like you
already has a default instance. If you haven't got one already, you
should be able to install one. Since you say that this option is greyed
out, it indicates that it's already there.
Have you tried to connect to just srvsql01 in your application,
Enterprise Manager or Query Analyser? If you have, do you then get an
error message of some kind?
Regards
Steen|||Thank all of you. It was grayed out on my Personnel copy, but when I tried
in on the test real enterprise version the options was not grayed out. And
thank for how to removed an instance that worked also. The only thing to
remember is that you have to put back the packs since the used the CD which
is at spack2. I did not know how else to get the install started. Now I
am going to detach and attach the the user databases and then add the login
user and my jobs. Since that path do not match restoring master did not
work.
"Steen Persson (DK)" wrote:
> Victoria Morrison wrote:
> > Ok, so how do a rename it from srvsql01\mptp to simply srvsql01
> >
> You can't rename an instance, but in your case it sounds like you
> already has a default instance. If you haven't got one already, you
> should be able to install one. Since you say that this option is greyed
> out, it indicates that it's already there.
> Have you tried to connect to just srvsql01 in your application,
> Enterprise Manager or Query Analyser? If you have, do you then get an
> error message of some kind?
> Regards
> Steen
>

How can you mass-validate stored procedures, views, etc?

I am looking for a way to validate all objects in the database that is is
possible for in some automated fashion. For instance, if a column is droppe
d
from a table stored procedures that rely on it will fail on the next
execution, but I want to be able to run a process that will reveal that prio
r
to execution. I would want to do that for views and user defined functions
as well. Other than executing every procedure and function and selecting
from every view does anyone know of a way to do this?
I thought I'd just script the objects out for ALTER and run that script
against the database but it appears you can only create an ALTER script one
object at a time through EM and with thousands of objects I really don't wan
t
to go through that.
I created a script that ran sp_recompile against every stored procedure,
then another block that executed every stored procedure without supplying an
y
parameters and that seemed to catch stored procedures that relied on missing
columns. The problem with that is it makes the assumption that the stored
procedure would either fail when no parameters were supplied, or would be
harmless when the stored procedure executed either because it required no
parameters or had defaults for all parameters. Of course this also only
worked for stored procedures and not the functions and views.
I made the mistake of changing a view without realizing that it was used in
a stored procedure and when that stored procedure executed it failed. You
can't depend on the dependencies that SQL maintains since they are not alway
s
correct, so you can't even make note of the dependent objects and selectivel
y
check only those objects without risking missing a dependency.
Any suggestions would be greatly appreciated.Byron (Byron@.discussions.microsoft.com) writes:
> I am looking for a way to validate all objects in the database that is
> is possible for in some automated fashion. For instance, if a column is
> dropped from a table stored procedures that rely on it will fail on the
> next execution, but I want to be able to run a process that will reveal
> that prior to execution. I would want to do that for views and user
> defined functions as well. Other than executing every procedure and
> function and selecting from every view does anyone know of a way to do
> this?
> I thought I'd just script the objects out for ALTER and run that script
> against the database but it appears you can only create an ALTER script
> one object at a time through EM and with thousands of objects I really
> don't want to go through that.
>...
This is indeed a difficult situation, and I don't really think there
is a single good way around it.
The method in the above paragraph is fairly easy to apply: use Generate
Scripts from Enterprise Manager to script the entire database. Then
open the generated script in a an editor and change CREATE PROCEDURE
to ALTER PROCEDURE.
However, you may not catch all errors this way. Say that you have:
INSERT #temp (...)
SELECT ..., missing_column
FROM ...
Because of deferred name resolution, SQL Server will not alert you of the
missing column. It sees that #temp is missing, and will defer compilation
of that statement until #temp has been created, and thus the error will
not occur until run-time.
The approach I use myself is based on the fact that we have all code
under version control. We also have a build tool that goes head over
heels to nullify the effect of deferred name resolution, by creating
all temp tables in an SP before loading the procedure. Thus a missing
column is noticed directly. Furthermore, it's easy for me to build an
empty database from SourceSafe, which will give me a complete sysdepends
that I can rely on.

> I created a script that ran sp_recompile against every stored procedure,
> then another block that executed every stored procedure without
> supplying any parameters and that seemed to catch stored procedures that
> relied on missing columns. The problem with that is it makes the
> assumption that the stored procedure would either fail when no
> parameters were supplied, or would be harmless when the stored procedure
> executed either because it required no parameters or had defaults for
> all parameters. Of course this also only worked for stored procedures
> and not the functions and views.
Our build-and-load tool is available for free download on
http://www.abaris.se/abaperls/. To get started with it, may be overmuch.
However, the toolset in includes a free-standing tool SPTRITEST which
performs essentially the above, but also provides dummy value for all
parameters, so it permits you test far more procedures. It goes without
saying that you should run this on a *copy* of the database, or else
you get a load of junk in it.
And it may still not find all cases of missing columns, since if there
are IF-ELSE branches, you are likely to pass over only one of them.
I also need to add the disclaimer that I wrote it in 6.5 days, and I
have not used it myself for ages.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How can we avoid somebody to access the MDF data by doing User Instance connection?

I created a database that will be distributed to my customers. This database is running on an Instance of SQL Server 2005 Express edition. I removed the admin logins from my SQL Server Instance so in theory, only my application connecting itself using the Sql Server autenthication will be able to be access the data (using "sa" having a password that I set at the installation).

For now, all this is working fine and after some tests, I haven't been able to access the data in any ways except by using the "sa" and the password my app is the only one to know.

But the problem is coming from a security leak when using User Instance. Indeed, I've been able to create a program getting the content from my MDF file. If somebody try to get connected using User Instance on his own SQL Server instance, he will be able to reach the data.

How could I prevent this to happend? Is there a property or something that could be set into the database that would prevent the database (mdf file) to be used with User Instance?

Thanks!

Hi,

AFAIK their is no way to restrict access to MDF/LDF files physically... refer below thread which has discussion the same issue.

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

Hemantgiri S. Goswami

|||

Alright, but what about the User Instance? Is there a way to prevent somebody of getting connected on a server using our MDF file and the User Instance option into his connection string?

|||No, you wil lhave to do this via NTFS permissions.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How can use 2 instance of SSRS?(WMI error code 80040219)

When I try to connect through SQL Server Management Studio,
the browser in the connect dialog can see the Reporting Services 2005 instance,
but when I try to connect, I get an error dialog with the message,
"An Unknown error has occured in WMI provider. error code 80040219."

it gives me no options for which instance to access
and connects to the default instance.
How do I connect to a 2nd named instance of SSRS via Management Studio?

i must use 2 different instance. (ex : 1) default instance 2) named instance) on the same physical server.
physical server os is cluster.
so i cant't remove one instance.

How can use 2 instance of SSRS via Management Studio?

Hi Jisungmon,

If you have installed on your machine named <Machine_Name> a second instance of Reporting Services named <Named_Instance>, you can connect to it using SQL Management Studio by following these steps:

- launch SQL Management Studio

- for Server type select: Reporting Services

- for Server name type: <Machine_Name>\<Named_Instance> ; or you can select <Browse for more ...>, expand the Reporting Services node, and select the same name from the list provided to you, then press OK

- for Authentication select what you prefer and eventually specify the parameters

- press Connect

Please let me know whether this is working for you.

Mihaela

This posting is provided "AS IS" with no warranties, and confers no rights.

How can the Subject field within emails sent by the SQL Server 2005 Agent be customized

I have a fully functioning SQL Server 2005 SE instance in terms of Database Mail, Maintenance Plans and the SQL Server Agent. All emails are working fine.

All my jobs send email based on jobs completing.

My question is how can I customize the Subject field of emails so that the Status (i.e. Success, Failed) value can be included?

I get this:

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P

I really would like to get this:

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P - Success

-OR-

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P - Failed

Thanks

Frank

You can declare a variable and dynamically assign the value "success or failure", then call the variable in mail procedure as below

Eg:

Code Snippet

Declare @.test varchar(20)
select @.test=count(*) from master..sysdatabases -- you can provide your command here

exec msdb.dbo.sp_send_mail @.profile_name='Profile Name',
@.recipients='email@.email.com',
@.subject=@.test

|||

Hi Vidhya,

I generated the following code from one of my subtasks of my maintenance plan (see below).

How can I use your solution with it?

It looks like the actual mail is sent from within the execution of the job.

Thanks,

Frank

USE [msdb]
GO
/****** Object: Job [ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup] Script Date: 09/13/2007 17:59:07 ******/
BEGIN TRANSACTION
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 09/13/2007 17:59:07 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @.ReturnCode = msdb.dbo.sp_add_category @.class=N'JOB', @.type=N'LOCAL', @.name=N'Database Maintenance'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @.jobId BINARY(16)
EXEC @.ReturnCode = msdb.dbo.sp_add_job @.job_name=N'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup',
@.enabled=1,
@.notify_level_eventlog=2,
@.notify_level_email=3,
@.notify_level_netsend=0,
@.notify_level_page=0,
@.delete_level=0,
@.description=N'No description available.',
@.category_name=N'Database Maintenance',
@.owner_login_name=N'sa',
@.notify_email_operator_name=N'sqlsrv_servicesadmin_oper', @.job_id = @.jobId OUTPUT
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Database_and_Transaction_Log_Backup] Script Date: 09/13/2007 17:59:08 ******/
EXEC @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id=@.jobId, @.step_name=N'Database_and_Transaction_Log_Backup',
@.step_id=1,
@.cmdexec_success_code=0,
@.on_success_action=1,
@.on_success_step_id=0,
@.on_fail_action=2,
@.on_fail_step_id=0,
@.retry_attempts=0,
@.retry_interval=0,
@.os_run_priority=0, @.subsystem=N'SSIS',
@.command=N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\ACCTV9P_Instance_Maintenance_Plan" /set "\Package\Database_and_Transaction_Log_Backup.Disable;false"',
@.flags=0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.jobId, @.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id=@.jobId, @.name=N'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup',
@.enabled=1,
@.freq_type=4,
@.freq_interval=1,
@.freq_subday_type=1,
@.freq_subday_interval=0,
@.freq_relative_interval=0,
@.freq_recurrence_factor=0,
@.active_start_date=20070728,
@.active_end_date=99991231,
@.active_start_time=210000,
@.active_end_time=235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.jobId, @.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

|||Hi,

In the code above you are using Email operator (@.notify_email_operator_name=N'sqlsrv_servicesadmin_oper') to send mail. There are three options available to send mail, you can use any one condition that suites you.

a.) When the job fails
b.) when the job succeeds
c.) when the job completes

Goto Job properties, then click on Notifications from there you can get these there options. If this doesnt work, reply me i'll write a code and give it to you.

We can also use the mail procedure as i said earlier by checking sysjobhistory and then to drop a mail.
|||

I selected "When Job Completes" when defining the subtask. By selecting this, the subject only includes a "completed on" phrase in the email subject.

What I want to do is set some property or select a setting that can place the status of the job that is in the email body in the subject.

For example, the following email has a STATUS of Succeeded in the body. What I want to do is have this redundantly included in the subject.

Subject:

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P

Body:

JOB RUN: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' was run on 9/13/2007 at 9:00:00 PM

DURATION: 0 hours, 2 minutes, 9 seconds

STATUS: Succeeded

MESSAGES: The job succeeded. The Job was invoked by Schedule 4 (ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup). The last step to run was step 1 (Database_and_Transaction_Log_Backup).

In SQL Server 2000, the email subject includes the status (see example below):

Subject:

SQL Server DB Maintenance Report - CEPROD6\BOPRODDB\BOPRODDB Instance Maintenance Plan (Success)

Thanks for your help,

Frank

|||Hi,

1.) Disable the Email operator in job first
2.) Use the below code as step 2 in the job "ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup".
3.) Please update your email address shown in bold letters

Code Snippet

declare @.sub varchar(200),@.complete varchar(20), @.bdy varchar(250)
declare @.stat char(1), @.rundate varchar(10), @.runtime varchar(10), @.rundur varchar(15)
declare @.mess varchar(300)
select @.stat=max(run_status),@.rundate=max(run_date),@.rundur=max(run_duration),@.mess=max(message),@.runtime=max(run_time) from sysjobhistory where job_id=(select job_id from msdb..sysjobs where name='ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup') and step_id=1
select @.complete = case @.stat when 1 then 'Succeeded' else 'Failed' end
select @.sub ='SQL Server Job System: ''ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup job '''+ @.complete + ' on ' + convert(varchar(25),@.@.servername) + ' at ' + @.rundate
select @.bdy='JOB RUN: ''ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup'' was run on '+ @.rundate + ' at ' + @.runtime + char(10)
+'DURATION:'+ @.rundur + ' Seconds' + char(10)+ 'STATUS:' +@.complete+char(10)+ @.mess
Exec msdb.dbo.sp_send_dbmail @.profile_name='CITOS',
@.recipients='emailaddress',
@.subject=@.sub,
@.body=@.bdy

Now try whether you are getting the mail as mentioned.

How can the Subject field within emails sent by the SQL Server 2005 Agent be customized

I have a fully functioning SQL Server 2005 SE instance in terms of Database Mail, Maintenance Plans and the SQL Server Agent. All emails are working fine.

All my jobs send email based on jobs completing.

My question is how can I customize the Subject field of emails so that the Status (i.e. Success, Failed) value can be included?

I get this:

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P

I really would like to get this:

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P - Success

-OR-

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P - Failed

Thanks

Frank

You can declare a variable and dynamically assign the value "success or failure", then call the variable in mail procedure as below

Eg:

Code Snippet

Declare @.test varchar(20)
select @.test=count(*) from master..sysdatabases -- you can provide your command here

exec msdb.dbo.sp_send_mail @.profile_name='Profile Name',
@.recipients='email@.email.com',
@.subject=@.test

|||

Hi Vidhya,

I generated the following code from one of my subtasks of my maintenance plan (see below).

How can I use your solution with it?

It looks like the actual mail is sent from within the execution of the job.

Thanks,

Frank

USE [msdb]
GO
/****** Object: Job [ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup] Script Date: 09/13/2007 17:59:07 ******/
BEGIN TRANSACTION
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 09/13/2007 17:59:07 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @.ReturnCode = msdb.dbo.sp_add_category @.class=N'JOB', @.type=N'LOCAL', @.name=N'Database Maintenance'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @.jobId BINARY(16)
EXEC @.ReturnCode = msdb.dbo.sp_add_job @.job_name=N'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup',
@.enabled=1,
@.notify_level_eventlog=2,
@.notify_level_email=3,
@.notify_level_netsend=0,
@.notify_level_page=0,
@.delete_level=0,
@.description=N'No description available.',
@.category_name=N'Database Maintenance',
@.owner_login_name=N'sa',
@.notify_email_operator_name=N'sqlsrv_servicesadmin_oper', @.job_id = @.jobId OUTPUT
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Database_and_Transaction_Log_Backup] Script Date: 09/13/2007 17:59:08 ******/
EXEC @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id=@.jobId, @.step_name=N'Database_and_Transaction_Log_Backup',
@.step_id=1,
@.cmdexec_success_code=0,
@.on_success_action=1,
@.on_success_step_id=0,
@.on_fail_action=2,
@.on_fail_step_id=0,
@.retry_attempts=0,
@.retry_interval=0,
@.os_run_priority=0, @.subsystem=N'SSIS',
@.command=N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\ACCTV9P_Instance_Maintenance_Plan" /set "\Package\Database_and_Transaction_Log_Backup.Disable;false"',
@.flags=0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.jobId, @.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id=@.jobId, @.name=N'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup',
@.enabled=1,
@.freq_type=4,
@.freq_interval=1,
@.freq_subday_type=1,
@.freq_subday_interval=0,
@.freq_relative_interval=0,
@.freq_recurrence_factor=0,
@.active_start_date=20070728,
@.active_end_date=99991231,
@.active_start_time=210000,
@.active_end_time=235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.jobId, @.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

|||Hi,

In the code above you are using Email operator (@.notify_email_operator_name=N'sqlsrv_servicesadmin_oper') to send mail. There are three options available to send mail, you can use any one condition that suites you.

a.) When the job fails
b.) when the job succeeds
c.) when the job completes

Goto Job properties, then click on Notifications from there you can get these there options. If this doesnt work, reply me i'll write a code and give it to you.

We can also use the mail procedure as i said earlier by checking sysjobhistory and then to drop a mail.
|||

I selected "When Job Completes" when defining the subtask. By selecting this, the subject only includes a "completed on" phrase in the email subject.

What I want to do is set some property or select a setting that can place the status of the job that is in the email body in the subject.

For example, the following email has a STATUS of Succeeded in the body. What I want to do is have this redundantly included in the subject.

Subject:

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P

Body:

JOB RUN: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' was run on 9/13/2007 at 9:00:00 PM

DURATION: 0 hours, 2 minutes, 9 seconds

STATUS: Succeeded

MESSAGES: The job succeeded. The Job was invoked by Schedule 4 (ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup). The last step to run was step 1 (Database_and_Transaction_Log_Backup).

In SQL Server 2000, the email subject includes the status (see example below):

Subject:

SQL Server DB Maintenance Report - CEPROD6\BOPRODDB\BOPRODDB Instance Maintenance Plan (Success)

Thanks for your help,

Frank

|||Hi,

1.) Disable the Email operator in job first
2.) Use the below code as step 2 in the job "ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup".
3.) Please update your email address shown in bold letters

Code Snippet

declare @.sub varchar(200),@.complete varchar(20), @.bdy varchar(250)
declare @.stat char(1), @.rundate varchar(10), @.runtime varchar(10), @.rundur varchar(15)
declare @.mess varchar(300)
select @.stat=max(run_status),@.rundate=max(run_date),@.rundur=max(run_duration),@.mess=max(message),@.runtime=max(run_time) from sysjobhistory where job_id=(select job_id from msdb..sysjobs where name='ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup') and step_id=1
select @.complete = case @.stat when 1 then 'Succeeded' else 'Failed' end
select @.sub ='SQL Server Job System: ''ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup job '''+ @.complete + ' on ' + convert(varchar(25),@.@.servername) + ' at ' + @.rundate
select @.bdy='JOB RUN: ''ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup'' was run on '+ @.rundate + ' at ' + @.runtime + char(10)
+'DURATION:'+ @.rundur + ' Seconds' + char(10)+ 'STATUS:' +@.complete+char(10)+ @.mess
Exec msdb.dbo.sp_send_dbmail @.profile_name='CITOS',
@.recipients='emailaddress',
@.subject=@.sub,
@.body=@.bdy

Now try whether you are getting the mail as mentioned.

How can the Subject field within emails sent by the SQL Server 2005 Agent be customized

I have a fully functioning SQL Server 2005 SE instance in terms of Database Mail, Maintenance Plans and the SQL Server Agent. All emails are working fine.

All my jobs send email based on jobs completing.

My question is how can I customize the Subject field of emails so that the Status (i.e. Success, Failed) value can be included?

I get this:

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P

I really would like to get this:

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P - Success

-OR-

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P - Failed

Thanks

Frank

You can declare a variable and dynamically assign the value "success or failure", then call the variable in mail procedure as below

Eg:

Code Snippet

Declare @.test varchar(20)
select @.test=count(*) from master..sysdatabases -- you can provide your command here

exec msdb.dbo.sp_send_mail @.profile_name='Profile Name',
@.recipients='email@.email.com',
@.subject=@.test

|||

Hi Vidhya,

I generated the following code from one of my subtasks of my maintenance plan (see below).

How can I use your solution with it?

It looks like the actual mail is sent from within the execution of the job.

Thanks,

Frank

USE [msdb]
GO
/****** Object: Job [ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup] Script Date: 09/13/2007 17:59:07 ******/
BEGIN TRANSACTION
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 09/13/2007 17:59:07 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @.ReturnCode = msdb.dbo.sp_add_category @.class=N'JOB', @.type=N'LOCAL', @.name=N'Database Maintenance'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @.jobId BINARY(16)
EXEC @.ReturnCode = msdb.dbo.sp_add_job @.job_name=N'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup',
@.enabled=1,
@.notify_level_eventlog=2,
@.notify_level_email=3,
@.notify_level_netsend=0,
@.notify_level_page=0,
@.delete_level=0,
@.description=N'No description available.',
@.category_name=N'Database Maintenance',
@.owner_login_name=N'sa',
@.notify_email_operator_name=N'sqlsrv_servicesadmin_oper', @.job_id = @.jobId OUTPUT
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Database_and_Transaction_Log_Backup] Script Date: 09/13/2007 17:59:08 ******/
EXEC @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id=@.jobId, @.step_name=N'Database_and_Transaction_Log_Backup',
@.step_id=1,
@.cmdexec_success_code=0,
@.on_success_action=1,
@.on_success_step_id=0,
@.on_fail_action=2,
@.on_fail_step_id=0,
@.retry_attempts=0,
@.retry_interval=0,
@.os_run_priority=0, @.subsystem=N'SSIS',
@.command=N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\ACCTV9P_Instance_Maintenance_Plan" /set "\Package\Database_and_Transaction_Log_Backup.Disable;false"',
@.flags=0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.jobId, @.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id=@.jobId, @.name=N'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup',
@.enabled=1,
@.freq_type=4,
@.freq_interval=1,
@.freq_subday_type=1,
@.freq_subday_interval=0,
@.freq_relative_interval=0,
@.freq_recurrence_factor=0,
@.active_start_date=20070728,
@.active_end_date=99991231,
@.active_start_time=210000,
@.active_end_time=235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.jobId, @.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

|||Hi,

In the code above you are using Email operator (@.notify_email_operator_name=N'sqlsrv_servicesadmin_oper') to send mail. There are three options available to send mail, you can use any one condition that suites you.

a.) When the job fails
b.) when the job succeeds
c.) when the job completes

Goto Job properties, then click on Notifications from there you can get these there options. If this doesnt work, reply me i'll write a code and give it to you.

We can also use the mail procedure as i said earlier by checking sysjobhistory and then to drop a mail.
|||

I selected "When Job Completes" when defining the subtask. By selecting this, the subject only includes a "completed on" phrase in the email subject.

What I want to do is set some property or select a setting that can place the status of the job that is in the email body in the subject.

For example, the following email has a STATUS of Succeeded in the body. What I want to do is have this redundantly included in the subject.

Subject:

SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P

Body:

JOB RUN: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' was run on 9/13/2007 at 9:00:00 PM

DURATION: 0 hours, 2 minutes, 9 seconds

STATUS: Succeeded

MESSAGES: The job succeeded. The Job was invoked by Schedule 4 (ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup). The last step to run was step 1 (Database_and_Transaction_Log_Backup).

In SQL Server 2000, the email subject includes the status (see example below):

Subject:

SQL Server DB Maintenance Report - CEPROD6\BOPRODDB\BOPRODDB Instance Maintenance Plan (Success)

Thanks for your help,

Frank

|||Hi,

1.) Disable the Email operator in job first
2.) Use the below code as step 2 in the job "ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup".
3.) Please update your email address shown in bold letters

Code Snippet

declare @.sub varchar(200),@.complete varchar(20), @.bdy varchar(250)
declare @.stat char(1), @.rundate varchar(10), @.runtime varchar(10), @.rundur varchar(15)
declare @.mess varchar(300)
select @.stat=max(run_status),@.rundate=max(run_date),@.rundur=max(run_duration),@.mess=max(message),@.runtime=max(run_time) from sysjobhistory where job_id=(select job_id from msdb..sysjobs where name='ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup') and step_id=1
select @.complete = case @.stat when 1 then 'Succeeded' else 'Failed' end
select @.sub ='SQL Server Job System: ''ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup job '''+ @.complete + ' on ' + convert(varchar(25),@.@.servername) + ' at ' + @.rundate
select @.bdy='JOB RUN: ''ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup'' was run on '+ @.rundate + ' at ' + @.runtime + char(10)
+'DURATION:'+ @.rundur + ' Seconds' + char(10)+ 'STATUS:' +@.complete+char(10)+ @.mess
Exec msdb.dbo.sp_send_dbmail @.profile_name='CITOS',
@.recipients='emailaddress',
@.subject=@.sub,
@.body=@.bdy

Now try whether you are getting the mail as mentioned.

How can I track the queries being issued against my sql server 2000 instance?

I have an ASP.NET app built on top of SQL Server 2000. My app is running slowly and I think I'm issuing too many queries to the database.

How can I track the queries, and when they are being issued, against my sql server 2000 instance? Is there a tool to view the queries, or is it all in a log file somewhere?

This will help me tune my ASP.NET caching strategy.

Any help is greatly appreciated!

Franco

Use SQL Server Profiler|||Choose: SQL Server, Tools, SQL Profile, File, New, Trace, Choose Server...

On the filters tab of the new window you see you can choose various filters. These include database name, application name, NT UserName, there are many to choose from.

SQL Profiler is an amazing tool for seeing whats happening "under the hood" I suggest you really read up on it in SQL Books Online as its extremely powerful.

Keep us all up to date with how you are getting on.

hth

Pace
|||Fantastic... just what I needed. Thanks!

How can I track the queries being issued against my sql server 2000 instance?

I have an ASP.NET app built on top of SQL Server 2000. My app is running slowly and I think I'm issuing too many queries to the database.

How can I track the queries, and when they are being issued, against my sql server 2000 instance? Is there a tool to view the queries, or is it all in a log file somewhere?

This will help me tune my ASP.NET caching strategy.

Any help is greatly appreciated!

Franco

Use SQL Server Profiler|||Choose: SQL Server, Tools, SQL Profile, File, New, Trace, Choose Server...

On the filters tab of the new window you see you can choose various filters. These include database name, application name, NT UserName, there are many to choose from.

SQL Profiler is an amazing tool for seeing whats happening "under the hood" I suggest you really read up on it in SQL Books Online as its extremely powerful.

Keep us all up to date with how you are getting on.

hth

Pace
|||Fantastic... just what I needed. Thanks!