Database Name as a Parameter

Hello folks,

I am in the process of writing a stored proc that selects data from a set of tables. Since this procedure needs to run in multiple databases, I wanted to store database names in a table and have the proc. retrieve the names dynamically.

In short, I want to execute the following stt:
select 'Conversion 1,
'Query 1',
(select count(*) from @.testDB.student)

(in this case @.testDB could be "parul1.dbo" or "parul2.dbo" etc.

How can this functionality be achieved through dynamic SQL?

Thanks so much!
-Paruldeclare @.sql nvarchar(1000),@.testDB varchar(100)
set @.testDB='parul1'
set @.sql='select ''Conversion 1'',''Query 1'',count(*) from '+@.testDB+'.dbo.student'

print @.sql

exec sp_executesql @.sql|||Thanks, that worked!
I have another question - how can i put the results from "EXEC sp_executesql @.SQLSTRING" into a temp table?

How can I get this to work?
Insert #temp1
select @.stage,
'Query 1',
@.sqlStringQuery1,
EXEC sp_executesql @.sqlStringQuery1

Thanks so much!

-Parul|||create a procedure which takes in DB name as parameter...do the select and the insert inside the procedure.|||Thanks, do you have sample code?|||Insert JUST the EXEC into a temp table first. Then, insert into a second temp table the results of that table joined to whatever else you need to insert.

Database Name

Hi,

Can anyone tell me how to return the current database name using T-SQL.

I have a stored proc that runs in a number of databases and would like qualify some dynamic sql with the database name. I know DB_Name exists but requires the DB_ID and DB_ID needs the DB_Name. Is there a function that just returns the current database i.e.

USE TestDB

GO

SELECT currentDBName?

Use the following function..

Select db_name()

|||

Cheers...

sql

database mirroring -- failing over in stored procedures

If I have a stored procedure that accesses two databases, both
databases are on server#1 (primary) and server#2 (backup)
if database #1 fails over to server#2, what is my stored procedure
going to do?
SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
How do i configure my stored procedure so that it is intelligent
enough to recognize that databaseA has failed over and it knows to go
to server#2?
I have decided to have all databases on one server merged into one
database so i can do mirroring.
but i still see a potential problem ... if i have a stored procedure
on a linked server that points to my singular database on a primary
mirror server, what happens when it fails over?
how is the stored procedure going to know where the backup database
is? i am sure this must be addressed somewhere? of course, this
problem still exists today without the mirrored database. but since
we are on the subject of high availability, how can a SP be highly
available if it can not failover to a different database? i am
thinking there must be some setting for linked servers whereby ("if
linked server A is offline, use B") but i can not find this?
otherwise it seems you can not access a mirrored database through a
linked server because that linked server might be gone? and if that
linked server is "offline" how does a SP know to go to the backup
server? this is easily solved with most connections because you
specify in the connection string a primary/failover server, but how to
do this in a SP?
On Apr 12, 7:42 pm, "Greg Linwood" <g_linw...@.hotmail.com> wrote:
> This is why DBMirroring is not recommended for applications which access
> multiple databases concurrently.
> It's important to keep in mind that DBMirroring is a database level
> technology & therefore problems like yours really can't be solved fully..
> Regards,
> Greg LinwoodSQLServerMVPhttp://blogs.sqlserver.org.au/blogs/greg_linwood
>

database mirroring -- failing over in stored procedures

If I have a stored procedure that accesses two databases, both
databases are on server#1 (primary) and server#2 (backup)
if database #1 fails over to server#2, what is my stored procedure
going to do?
SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
How do i configure my stored procedure so that it is intelligent
enough to recognize that databaseA has failed over and it knows to go
to server#2'This is why DBMirroring is not recommended for applications which access
multiple databases concurrently.
It's important to keep in mind that DBMirroring is a database level
technology & therefore problems like yours really can't be solved fully..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
<stevehunter_1@.hotmail.com> wrote in message
news:1176431795.881113.109140@.n76g2000hsh.googlegroups.com...
> If I have a stored procedure that accesses two databases, both
> databases are on server#1 (primary) and server#2 (backup)
> if database #1 fails over to server#2, what is my stored procedure
> going to do?
> SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
>
> How do i configure my stored procedure so that it is intelligent
> enough to recognize that databaseA has failed over and it knows to go
> to server#2'
>|||I have decided to have all databases on one server merged into one
database so i can do mirroring.
but i still see a potential problem ... if i have a stored procedure
on a linked server that points to my singular database on a primary
mirror server, what happens when it fails over?
how is the stored procedure going to know where the backup database
is? i am sure this must be addressed somewhere? of course, this
problem still exists today without the mirrored database. but since
we are on the subject of high availability, how can a SP be highly
available if it can not failover to a different database? i am
thinking there must be some setting for linked servers whereby ("if
linked server A is offline, use B") but i can not find this?
otherwise it seems you can not access a mirrored database through a
linked server because that linked server might be gone? and if that
linked server is "offline" how does a SP know to go to the backup
server? this is easily solved with most connections because you
specify in the connection string a primary/failover server, but how to
do this in a SP?
On Apr 12, 7:42 pm, "Greg Linwood" <g_linw...@.hotmail.com> wrote:
> This is why DBMirroring is not recommended for applications which access
> multiple databases concurrently.
> It's important to keep in mind that DBMirroring is a database level
> technology & therefore problems like yours really can't be solved fully..
> Regards,
> Greg LinwoodSQLServerMVPhttp://blogs.sqlserver.org.au/blogs/greg_linwood
>

database mirroring -- failing over in stored procedures

If I have a stored procedure that accesses two databases, both
databases are on server#1 (primary) and server#2 (backup)
if database #1 fails over to server#2, what is my stored procedure
going to do?
SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
How do i configure my stored procedure so that it is intelligent
enough to recognize that databaseA has failed over and it knows to go
to server#2'This is why DBMirroring is not recommended for applications which access
multiple databases concurrently.
It's important to keep in mind that DBMirroring is a database level
technology & therefore problems like yours really can't be solved fully..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
<stevehunter_1@.hotmail.com> wrote in message
news:1176431795.881113.109140@.n76g2000hsh.googlegroups.com...
> If I have a stored procedure that accesses two databases, both
> databases are on server#1 (primary) and server#2 (backup)
> if database #1 fails over to server#2, what is my stored procedure
> going to do?
> SET @.value = SELECT FirstName FROM [nameofserver].databaseA.table
>
> How do i configure my stored procedure so that it is intelligent
> enough to recognize that databaseA has failed over and it knows to go
> to server#2'
>|||I have decided to have all databases on one server merged into one
database so i can do mirroring.
but i still see a potential problem ... if i have a stored procedure
on a linked server that points to my singular database on a primary
mirror server, what happens when it fails over?
how is the stored procedure going to know where the backup database
is? i am sure this must be addressed somewhere? of course, this
problem still exists today without the mirrored database. but since
we are on the subject of high availability, how can a SP be highly
available if it can not failover to a different database? i am
thinking there must be some setting for linked servers whereby ("if
linked server A is offline, use B") but i can not find this?
otherwise it seems you can not access a mirrored database through a
linked server because that linked server might be gone? and if that
linked server is "offline" how does a SP know to go to the backup
server? this is easily solved with most connections because you
specify in the connection string a primary/failover server, but how to
do this in a SP?
On Apr 12, 7:42 pm, "Greg Linwood" <g_linw...@.hotmail.com> wrote:
> This is why DBMirroring is not recommended for applications which access
> multiple databases concurrently.
> It's important to keep in mind that DBMirroring is a database level
> technology & therefore problems like yours really can't be solved fully..
> Regards,
> Greg LinwoodSQLServerMVPhttp://blogs.sqlserver.org.au/blogs/greg_linwood
>sql

Database Mirroring - DDL Updates

I wanted to confirm that when using Database Mirroring that DDL Updates such as table and stored procedure updates on the principle server are replicated to the mirror server.

I thought I read that non-logged operations would not be replicated to the mirror server. What would be some examples of that. At the moment...you would think every entry in the the database would be saved into a table and that would be replicated.

...cordell...

Hi Cordell. Any logged operations are shipped to the mirror for replay, which includes standard database DDL (alter, create, etc.). For all intents and purposes, think of mirroring as being real-time log shipping...anything that can be recovered in a database/log restore of a backup will be replayed at the mirror.

The terem "non-logged operation" is a bit misleading really, because Sql always writes log records so changes are recoverable, it can just perform minimal-logged operations in appropriate scenarios. Without getting into that however, given that the recovery model for the principal database must be 'full' for mirroring to work, you won't be able to perform minimally-logged operations anyhow.

HTH,

|||http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx in addition to explanation above.

Database migration...

I just migrated database A from Server A to Server B.
Server B has a database B on it. Database B has a stored
procedure that has a cross server transaction.
Now that both the databases are on Server B, I am trying
to execute that stored procedure. The procedure works fine
if I run it through Query analyzer logged in as a regular
user.
If I login in as an Application Role it gives me the
following error message...
Server: Msg 916, Level 14, State 1, Procedure
P_Transfer_to_DBNew, Line 173
Server user 'transferuser' is not a valid user in
database 'DatabaseNew'.
Any thoughts or suggestions are welcome.
Thank you
AjayFigured it out, thank you
The guest login needed to be given permissions.
>--Original Message--
>I just migrated database A from Server A to Server B.
>Server B has a database B on it. Database B has a stored
>procedure that has a cross server transaction.
>Now that both the databases are on Server B, I am trying
>to execute that stored procedure. The procedure works
fine
>if I run it through Query analyzer logged in as a regular
>user.
>If I login in as an Application Role it gives me the
>following error message...
>Server: Msg 916, Level 14, State 1, Procedure
>P_Transfer_to_DBNew, Line 173
>Server user 'transferuser' is not a valid user in
>database 'DatabaseNew'.
>
>Any thoughts or suggestions are welcome.
>Thank you
>Ajay
>.
>

Database maintenance using DBCC DBREINDEX

Hi,
Currently, I use sp_msForEachTable to reindex every table in one of my
databases. However I did not find any information regarding this stored
procedure in BOL. Is it safe to use, or why isn't it documented?
EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
Yours sincerely,
Jo Segers.Hi,
The procedure sp_MSforeachtable executes a set of given commands against all
the user tables in the current database.
Have a look into this link for detailed information and usage.
http://www.bstsoftware.com/tsug/Nov99/sp_MSforeachtable.html
Thanks
Hari
MCDBA
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>|||Hi Jo,
The command sp_msForEachTable is no longer supported my
Microsoft. This means on the next release of SQL Server it
may or may not be part of it.
If its not supported then there are no BOL's for it.
J
>--Original Message--
>Hi,
>Currently, I use sp_msForEachTable to reindex every table
in one of my
>databases. However I did not find any information
regarding this stored
>procedure in BOL. Is it safe to use, or why isn't it
documented?
>EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
>Yours sincerely,
>Jo Segers.
>
>.
>|||Adding to the other posts:
That procedure only contains a cursor which loops all tables in the database and executes the specified
command against the tables. You can easily write such a cursor yourself. The procedure is likely to work the
same way in future service packs of SQL2K, and when Yukon comes, you want to look at these things anyhow.
Still, it is not documented, not supported, use at own "risk", which only you can assess.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>|||Thanks,
Do you know if there is a similar stored procedure that is supported?
Jo.
"Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
> Hi Jo,
> The command sp_msForEachTable is no longer supported my
> Microsoft. This means on the next release of SQL Server it
> may or may not be part of it.
> If its not supported then there are no BOL's for it.
> J
>
> >--Original Message--
> >Hi,
> >
> >Currently, I use sp_msForEachTable to reindex every table
> in one of my
> >databases. However I did not find any information
> regarding this stored
> >procedure in BOL. Is it safe to use, or why isn't it
> documented?
> >
> >EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> >
> >Yours sincerely,
> >
> >Jo Segers.
> >
> >
> >.
> >|||I'm sure there exists such on the Net, but if they are free, I doubt that you will have formal support form
the one who wrote it. AFAIK, there is no such in SQL Server. But, again, it is not difficult to write one
yourself, using a cursor, if you have a little bit if TSQL programming experience.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:%23lKZD$4JEHA.2624@.TK2MSFTNGP09.phx.gbl...
> Thanks,
> Do you know if there is a similar stored procedure that is supported?
> Jo.
> "Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
> news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
> > Hi Jo,
> >
> > The command sp_msForEachTable is no longer supported my
> > Microsoft. This means on the next release of SQL Server it
> > may or may not be part of it.
> >
> > If its not supported then there are no BOL's for it.
> >
> > J
> >
> >
> > >--Original Message--
> > >Hi,
> > >
> > >Currently, I use sp_msForEachTable to reindex every table
> > in one of my
> > >databases. However I did not find any information
> > regarding this stored
> > >procedure in BOL. Is it safe to use, or why isn't it
> > documented?
> > >
> > >EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> > >
> > >Yours sincerely,
> > >
> > >Jo Segers.
> > >
> > >
> > >.
> > >
>|||Ok,
Thanks for the information, I will write one myself. I wonder why they
didn't implement this sp in SQLserver 2000 because it is quite usefull.
Thanks for the support,
Jo.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
in bericht news:uYzs9B5JEHA.208@.tk2msftngp13.phx.gbl...
> I'm sure there exists such on the Net, but if they are free, I doubt that
you will have formal support form
> the one who wrote it. AFAIK, there is no such in SQL Server. But, again,
it is not difficult to write one
> yourself, using a cursor, if you have a little bit if TSQL programming
experience.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23lKZD$4JEHA.2624@.TK2MSFTNGP09.phx.gbl...
> > Thanks,
> >
> > Do you know if there is a similar stored procedure that is supported?
> >
> > Jo.
> >
> > "Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
> > news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
> > > Hi Jo,
> > >
> > > The command sp_msForEachTable is no longer supported my
> > > Microsoft. This means on the next release of SQL Server it
> > > may or may not be part of it.
> > >
> > > If its not supported then there are no BOL's for it.
> > >
> > > J
> > >
> > >
> > > >--Original Message--
> > > >Hi,
> > > >
> > > >Currently, I use sp_msForEachTable to reindex every table
> > > in one of my
> > > >databases. However I did not find any information
> > > regarding this stored
> > > >procedure in BOL. Is it safe to use, or why isn't it
> > > documented?
> > > >
> > > >EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> > > >
> > > >Yours sincerely,
> > > >
> > > >Jo Segers.
> > > >
> > > >
> > > >.
> > > >
> >
> >
>|||Hi Jo,
If you want a way to run a command against all fragmented indexes in a
databases, check out example E in the Books Online for DBCC SHOWCONTIG. You
should not use sp_msForEachTable.
I'd like to go to the root of your problem, which is wanting to reindex all
the tables in one of your database. I take it you're doing this for
performance reasons. The only way you'll increase performance by doing this
is if the index (clustered or non-clustered) has logical fragmentation and
is used for a range scan. I'm guessing that not all the indexes you're
rebuilding are used in this fashion. Also, sometimes it can be better to run
DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
I recommend you read the whitepaper on this topic at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Let me know if you have any more questions.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>|||Hi Paul,
I'm still reading the whitepaper, but the example in BOL helped a lot. I am
going to adjust the code in the example for use in my produktion database.
I am just curious: why isn't sp_msForEachTable supported anymore?
Yours sincerely,
Jo Segers.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
news:O2t8tk5JEHA.2376@.tk2msftngp13.phx.gbl...
> Hi Jo,
> If you want a way to run a command against all fragmented indexes in a
> databases, check out example E in the Books Online for DBCC SHOWCONTIG.
You
> should not use sp_msForEachTable.
> I'd like to go to the root of your problem, which is wanting to reindex
all
> the tables in one of your database. I take it you're doing this for
> performance reasons. The only way you'll increase performance by doing
this
> is if the index (clustered or non-clustered) has logical fragmentation and
> is used for a range scan. I'm guessing that not all the indexes you're
> rebuilding are used in this fashion. Also, sometimes it can be better to
run
> DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
> I recommend you read the whitepaper on this topic at
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Let me know if you have any more questions.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > Currently, I use sp_msForEachTable to reindex every table in one of my
> > databases. However I did not find any information regarding this stored
> > procedure in BOL. Is it safe to use, or why isn't it documented?
> >
> > EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> >
> > Yours sincerely,
> >
> > Jo Segers.
> >
> >
>|||As far as I know there is no Microsoft official version
that does the same, sorry.
However you can get all the user tables from your database
by doing
select Name from sysobjects where xtype = 'U'
J
>--Original Message--
>Thanks,
>Do you know if there is a similar stored procedure that
is supported?
>Jo.
>"Julie" <anonymous@.discussions.microsoft.com> schreef in
bericht
>news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
>> Hi Jo,
>> The command sp_msForEachTable is no longer supported my
>> Microsoft. This means on the next release of SQL Server
it
>> may or may not be part of it.
>> If its not supported then there are no BOL's for it.
>> J
>>
>> >--Original Message--
>> >Hi,
>> >
>> >Currently, I use sp_msForEachTable to reindex every
table
>> in one of my
>> >databases. However I did not find any information
>> regarding this stored
>> >procedure in BOL. Is it safe to use, or why isn't it
>> documented?
>> >
>> >EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
>> >
>> >Yours sincerely,
>> >
>> >Jo Segers.
>> >
>> >
>> >.
>> >
>
>.
>|||Thanks for the help.
"Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
news:216f01c427a7$9b1e6a60$a601280a@.phx.gbl...
> As far as I know there is no Microsoft official version
> that does the same, sorry.
> However you can get all the user tables from your database
> by doing
> select Name from sysobjects where xtype = 'U'
> J
>
> >--Original Message--
> >Thanks,
> >
> >Do you know if there is a similar stored procedure that
> is supported?
> >
> >Jo.
> >
> >"Julie" <anonymous@.discussions.microsoft.com> schreef in
> bericht
> >news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
> >> Hi Jo,
> >>
> >> The command sp_msForEachTable is no longer supported my
> >> Microsoft. This means on the next release of SQL Server
> it
> >> may or may not be part of it.
> >>
> >> If its not supported then there are no BOL's for it.
> >>
> >> J
> >>
> >>
> >> >--Original Message--
> >> >Hi,
> >> >
> >> >Currently, I use sp_msForEachTable to reindex every
> table
> >> in one of my
> >> >databases. However I did not find any information
> >> regarding this stored
> >> >procedure in BOL. Is it safe to use, or why isn't it
> >> documented?
> >> >
> >> >EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> >> >
> >> >Yours sincerely,
> >> >
> >> >Jo Segers.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Jo - It never was supported. It's always been an undocumented SP and so
liable to change or removal with no notice.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:edRau75JEHA.628@.TK2MSFTNGP11.phx.gbl...
> Hi Paul,
> I'm still reading the whitepaper, but the example in BOL helped a lot. I
am
> going to adjust the code in the example for use in my produktion database.
> I am just curious: why isn't sp_msForEachTable supported anymore?
> Yours sincerely,
> Jo Segers.
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
> news:O2t8tk5JEHA.2376@.tk2msftngp13.phx.gbl...
> > Hi Jo,
> >
> > If you want a way to run a command against all fragmented indexes in a
> > databases, check out example E in the Books Online for DBCC SHOWCONTIG.
> You
> > should not use sp_msForEachTable.
> >
> > I'd like to go to the root of your problem, which is wanting to reindex
> all
> > the tables in one of your database. I take it you're doing this for
> > performance reasons. The only way you'll increase performance by doing
> this
> > is if the index (clustered or non-clustered) has logical fragmentation
and
> > is used for a range scan. I'm guessing that not all the indexes you're
> > rebuilding are used in this fashion. Also, sometimes it can be better to
> run
> > DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
> >
> > I recommend you read the whitepaper on this topic at
> >
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >
> > Let me know if you have any more questions.
> >
> > Regards
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> > news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > Currently, I use sp_msForEachTable to reindex every table in one of my
> > > databases. However I did not find any information regarding this
stored
> > > procedure in BOL. Is it safe to use, or why isn't it documented?
> > >
> > > EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> > >
> > > Yours sincerely,
> > >
> > > Jo Segers.
> > >
> > >
> >
> >
>|||Thanks for your help and time Paul, I appreciate it very much.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
news:uI%23gsb9JEHA.204@.TK2MSFTNGP10.phx.gbl...
> Jo - It never was supported. It's always been an undocumented SP and so
> liable to change or removal with no notice.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:edRau75JEHA.628@.TK2MSFTNGP11.phx.gbl...
> > Hi Paul,
> >
> > I'm still reading the whitepaper, but the example in BOL helped a lot. I
> am
> > going to adjust the code in the example for use in my produktion
database.
> >
> > I am just curious: why isn't sp_msForEachTable supported anymore?
> >
> > Yours sincerely,
> >
> > Jo Segers.
> >
> > "Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
> > news:O2t8tk5JEHA.2376@.tk2msftngp13.phx.gbl...
> > > Hi Jo,
> > >
> > > If you want a way to run a command against all fragmented indexes in a
> > > databases, check out example E in the Books Online for DBCC
SHOWCONTIG.
> > You
> > > should not use sp_msForEachTable.
> > >
> > > I'd like to go to the root of your problem, which is wanting to
reindex
> > all
> > > the tables in one of your database. I take it you're doing this for
> > > performance reasons. The only way you'll increase performance by doing
> > this
> > > is if the index (clustered or non-clustered) has logical fragmentation
> and
> > > is used for a range scan. I'm guessing that not all the indexes you're
> > > rebuilding are used in this fashion. Also, sometimes it can be better
to
> > run
> > > DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
> > >
> > > I recommend you read the whitepaper on this topic at
> > >
> >
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> > >
> > > Let me know if you have any more questions.
> > >
> > > Regards
> > >
> > > --
> > > Paul Randal
> > > Dev Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > > "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> > > news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> > > > Hi,
> > > >
> > > > Currently, I use sp_msForEachTable to reindex every table in one of
my
> > > > databases. However I did not find any information regarding this
> stored
> > > > procedure in BOL. Is it safe to use, or why isn't it documented?
> > > >
> > > > EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> > > >
> > > > Yours sincerely,
> > > >
> > > > Jo Segers.
> > > >
> > > >
> > >
> > >
> >
> >
>

database mail Vs. xp_smtp_sendmail

Hi,
we are in the process of upgrading SQL Server 2000 to 2005,
Now, a lot of emails are sent within stored procedures using
xp_smtp_sendmail.
Now this procedure is quite nice because it lets the caller
define diffrent From names and ReplyTo depending on certain conditions.
Since I can't reuse this procedure in SQL 2005, I am testing the
Database Mail using the sp_send_dbmail stored procedure along with
some profiles I created. It works great, but...
My problem here is that sp_send_dbmail doesn't let you dynamically change
the FROM and REPLYTO parameters, it uses the ones on the profile
sp_send_dbmail
recieves in parameter. This leaves me with a dilemna because the from and
replyTo
adresses possibility are too great (that is why it was made dynamic in our
SQL 2000 using xp_smtp_sendmail)
How should I proceed if I do not want to create 1 profile per possibility?
Is there another way or another SMTP send mail procedure or technique I
could use in SQL 2005
that will allow me to do the same thing as in XP_smtp_SendMail ?
Ideas anyone ?
Thanks
> Since I can't reuse this procedure in SQL 2005
Why not? As long as you are on 32-bit, you can install the DLL on 2005 the
same way you did on 2000.
A
|||Thanks, But it is a x64 bit SQL Server..
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%230invczaHHA.960@.TK2MSFTNGP03.phx.gbl...
> You should be able to use xp_smtp in 2005, as long as it is 32 bit SQL
> Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "msnews.microsoft.com" <spam@.spam.com> wrote in message
> news:e8h4gUzaHHA.5020@.TK2MSFTNGP05.phx.gbl...
>
|||> Thanks, But it is a x64 bit SQL Server..
In that case, you could learn the format and write a stored procedure that
creates a properly-formatted text file with a .msg extension and drops it
into the pickup folder of a local SMTP server (which doesn't necessarily
have to live on the same server, so could be 32-bit). This would allow you
to have full control over FROM/REPLYTO etc.
Another option is to set up a 32-bit edition of Express or Workgroup on
another server (even a web server), and route messages through it. As long
as the network path is reliable, not a huge difference between 'EXEC
master.dbo.xp_smtp_sendmail' and 'EXEC
linked_server_name.master.dbo.xp_smtp_sendmail'...
A
|||BTW, I submitted an enhancement request about this issue. You can vote on
it if you have a passport and feel strongly enough about it to have it
addressed in a future version of SQL Server:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=264498
A

database mail Vs. xp_smtp_sendmail

Hi,
we are in the process of upgrading SQL Server 2000 to 2005,
Now, a lot of emails are sent within stored procedures using
xp_smtp_sendmail.
Now this procedure is quite nice because it lets the caller
define diffrent From names and ReplyTo depending on certain conditions.
Since I can't reuse this procedure in SQL 2005, I am testing the
Database Mail using the sp_send_dbmail stored procedure along with
some profiles I created. It works great, but...
My problem here is that sp_send_dbmail doesn't let you dynamically change
the FROM and REPLYTO parameters, it uses the ones on the profile
sp_send_dbmail
recieves in parameter. This leaves me with a dilemna because the from and
replyTo
adresses possibility are too great (that is why it was made dynamic in our
SQL 2000 using xp_smtp_sendmail)
How should I proceed if I do not want to create 1 profile per possibility?
Is there another way or another SMTP send mail procedure or technique I
could use in SQL 2005
that will allow me to do the same thing as in XP_smtp_SendMail ?
Ideas anyone ?
Thanks> Since I can't reuse this procedure in SQL 2005
Why not? As long as you are on 32-bit, you can install the DLL on 2005 the
same way you did on 2000.
A|||> Since I can't reuse this procedure in SQL 2005, I am testing the
You should be able to use xp_smtp in 2005, as long as it is 32 bit SQL Serve
r.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"msnews.microsoft.com" <spam@.spam.com> wrote in message
news:e8h4gUzaHHA.5020@.TK2MSFTNGP05.phx.gbl...
> Hi,
> we are in the process of upgrading SQL Server 2000 to 2005,
> Now, a lot of emails are sent within stored procedures using
> xp_smtp_sendmail.
> Now this procedure is quite nice because it lets the caller
> define diffrent From names and ReplyTo depending on certain conditions.
>
> Since I can't reuse this procedure in SQL 2005, I am testing the
> Database Mail using the sp_send_dbmail stored procedure along with
> some profiles I created. It works great, but...
>
> My problem here is that sp_send_dbmail doesn't let you dynamically change
> the FROM and REPLYTO parameters, it uses the ones on the profile
> sp_send_dbmail
> recieves in parameter. This leaves me with a dilemna because the from and
> replyTo
> adresses possibility are too great (that is why it was made dynamic in our
> SQL 2000 using xp_smtp_sendmail)
>
> How should I proceed if I do not want to create 1 profile per possibility?
> Is there another way or another SMTP send mail procedure or technique I
> could use in SQL 2005
> that will allow me to do the same thing as in XP_smtp_SendMail ?
>
> Ideas anyone ?
>
> Thanks
>
>|||Thanks, But it is a x64 bit SQL Server..
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%230invczaHHA.960@.TK2MSFTNGP03.phx.gbl...
> You should be able to use xp_smtp in 2005, as long as it is 32 bit SQL
> Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "msnews.microsoft.com" <spam@.spam.com> wrote in message
> news:e8h4gUzaHHA.5020@.TK2MSFTNGP05.phx.gbl...
>|||> Thanks, But it is a x64 bit SQL Server..
Well, you can't use a 32 bit DLL in 64 bit code. See Russell's reply to Fred
's post for suggestion.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"msnews.microsoft.com" <spam@.spam.com> wrote in message
news:euuEqS8aHHA.1508@.TK2MSFTNGP06.phx.gbl...
> Thanks, But it is a x64 bit SQL Server..
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%230invczaHHA.960@.TK2MSFTNGP03.phx.gbl...
>|||> Thanks, But it is a x64 bit SQL Server..
In that case, you could learn the format and write a stored procedure that
creates a properly-formatted text file with a .msg extension and drops it
into the pickup folder of a local SMTP server (which doesn't necessarily
have to live on the same server, so could be 32-bit). This would allow you
to have full control over FROM/REPLYTO etc.
Another option is to set up a 32-bit edition of Express or Workgroup on
another server (even a web server), and route messages through it. As long
as the network path is reliable, not a huge difference between 'EXEC
master.dbo.xp_smtp_sendmail' and 'EXEC
linked_server_name.master.dbo.xp_smtp_sendmail'...
A|||BTW, I submitted an enhancement request about this issue. You can vote on
it if you have a passport and feel strongly enough about it to have it
addressed in a future version of SQL Server:
https://connect.microsoft.com/SQLSe...=2644
98
A

database mail Vs. xp_smtp_sendmail

Hi,
we are in the process of upgrading SQL Server 2000 to 2005,
Now, a lot of emails are sent within stored procedures using
xp_smtp_sendmail.
Now this procedure is quite nice because it lets the caller
define diffrent From names and ReplyTo depending on certain conditions.
Since I can't reuse this procedure in SQL 2005, I am testing the
Database Mail using the sp_send_dbmail stored procedure along with
some profiles I created. It works great, but...
My problem here is that sp_send_dbmail doesn't let you dynamically change
the FROM and REPLYTO parameters, it uses the ones on the profile
sp_send_dbmail
recieves in parameter. This leaves me with a dilemna because the from and
replyTo
adresses possibility are too great (that is why it was made dynamic in our
SQL 2000 using xp_smtp_sendmail)
How should I proceed if I do not want to create 1 profile per possibility?
Is there another way or another SMTP send mail procedure or technique I
could use in SQL 2005
that will allow me to do the same thing as in XP_smtp_SendMail ?
Ideas anyone ?
Thanks> Since I can't reuse this procedure in SQL 2005
Why not? As long as you are on 32-bit, you can install the DLL on 2005 the
same way you did on 2000.
A|||> Since I can't reuse this procedure in SQL 2005, I am testing the
You should be able to use xp_smtp in 2005, as long as it is 32 bit SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"msnews.microsoft.com" <spam@.spam.com> wrote in message
news:e8h4gUzaHHA.5020@.TK2MSFTNGP05.phx.gbl...
> Hi,
> we are in the process of upgrading SQL Server 2000 to 2005,
> Now, a lot of emails are sent within stored procedures using
> xp_smtp_sendmail.
> Now this procedure is quite nice because it lets the caller
> define diffrent From names and ReplyTo depending on certain conditions.
>
> Since I can't reuse this procedure in SQL 2005, I am testing the
> Database Mail using the sp_send_dbmail stored procedure along with
> some profiles I created. It works great, but...
>
> My problem here is that sp_send_dbmail doesn't let you dynamically change
> the FROM and REPLYTO parameters, it uses the ones on the profile
> sp_send_dbmail
> recieves in parameter. This leaves me with a dilemna because the from and
> replyTo
> adresses possibility are too great (that is why it was made dynamic in our
> SQL 2000 using xp_smtp_sendmail)
>
> How should I proceed if I do not want to create 1 profile per possibility?
> Is there another way or another SMTP send mail procedure or technique I
> could use in SQL 2005
> that will allow me to do the same thing as in XP_smtp_SendMail ?
>
> Ideas anyone ?
>
> Thanks
>
>|||Thanks, But it is a x64 bit SQL Server..
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%230invczaHHA.960@.TK2MSFTNGP03.phx.gbl...
>> Since I can't reuse this procedure in SQL 2005, I am testing the
> You should be able to use xp_smtp in 2005, as long as it is 32 bit SQL
> Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "msnews.microsoft.com" <spam@.spam.com> wrote in message
> news:e8h4gUzaHHA.5020@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> we are in the process of upgrading SQL Server 2000 to 2005,
>> Now, a lot of emails are sent within stored procedures using
>> xp_smtp_sendmail.
>> Now this procedure is quite nice because it lets the caller
>> define diffrent From names and ReplyTo depending on certain conditions.
>>
>> Since I can't reuse this procedure in SQL 2005, I am testing the
>> Database Mail using the sp_send_dbmail stored procedure along with
>> some profiles I created. It works great, but...
>>
>> My problem here is that sp_send_dbmail doesn't let you dynamically change
>> the FROM and REPLYTO parameters, it uses the ones on the profile
>> sp_send_dbmail
>> recieves in parameter. This leaves me with a dilemna because the from
>> and
>> replyTo
>> adresses possibility are too great (that is why it was made dynamic in
>> our
>> SQL 2000 using xp_smtp_sendmail)
>>
>> How should I proceed if I do not want to create 1 profile per
>> possibility?
>> Is there another way or another SMTP send mail procedure or technique I
>> could use in SQL 2005
>> that will allow me to do the same thing as in XP_smtp_SendMail ?
>>
>> Ideas anyone ?
>>
>> Thanks
>>
>>
>|||> Thanks, But it is a x64 bit SQL Server..
Well, you can't use a 32 bit DLL in 64 bit code. See Russell's reply to Fred's post for suggestion.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"msnews.microsoft.com" <spam@.spam.com> wrote in message
news:euuEqS8aHHA.1508@.TK2MSFTNGP06.phx.gbl...
> Thanks, But it is a x64 bit SQL Server..
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%230invczaHHA.960@.TK2MSFTNGP03.phx.gbl...
>> Since I can't reuse this procedure in SQL 2005, I am testing the
>> You should be able to use xp_smtp in 2005, as long as it is 32 bit SQL Server.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "msnews.microsoft.com" <spam@.spam.com> wrote in message
>> news:e8h4gUzaHHA.5020@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> we are in the process of upgrading SQL Server 2000 to 2005,
>> Now, a lot of emails are sent within stored procedures using
>> xp_smtp_sendmail.
>> Now this procedure is quite nice because it lets the caller
>> define diffrent From names and ReplyTo depending on certain conditions.
>>
>> Since I can't reuse this procedure in SQL 2005, I am testing the
>> Database Mail using the sp_send_dbmail stored procedure along with
>> some profiles I created. It works great, but...
>>
>> My problem here is that sp_send_dbmail doesn't let you dynamically change
>> the FROM and REPLYTO parameters, it uses the ones on the profile
>> sp_send_dbmail
>> recieves in parameter. This leaves me with a dilemna because the from and
>> replyTo
>> adresses possibility are too great (that is why it was made dynamic in our
>> SQL 2000 using xp_smtp_sendmail)
>>
>> How should I proceed if I do not want to create 1 profile per possibility?
>> Is there another way or another SMTP send mail procedure or technique I
>> could use in SQL 2005
>> that will allow me to do the same thing as in XP_smtp_SendMail ?
>>
>> Ideas anyone ?
>>
>> Thanks
>>
>>
>|||> Thanks, But it is a x64 bit SQL Server..
In that case, you could learn the format and write a stored procedure that
creates a properly-formatted text file with a .msg extension and drops it
into the pickup folder of a local SMTP server (which doesn't necessarily
have to live on the same server, so could be 32-bit). This would allow you
to have full control over FROM/REPLYTO etc.
Another option is to set up a 32-bit edition of Express or Workgroup on
another server (even a web server), and route messages through it. As long
as the network path is reliable, not a huge difference between 'EXEC
master.dbo.xp_smtp_sendmail' and 'EXEC
linked_server_name.master.dbo.xp_smtp_sendmail'...
A|||BTW, I submitted an enhancement request about this issue. You can vote on
it if you have a passport and feel strongly enough about it to have it
addressed in a future version of SQL Server:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=264498
A

Database Mail and Linked Servers

We have a stored procedure that executes sp_send_dbmail with an @.query parameter. The @. query executes a stored procedures that has a linked server query in it. The linked server is SQL 2000. The login/user used by the link is valid and running the sproc by itself will return the expected results but it fails with the following error if run by the sp_send_dbmail sproc:

Warning: Null value is eliminated by an aggregate or other SET operation.

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476

Query execution failed: OLE DB provider "SQLNCLI" for linked server "Link_Server" returned message "Communication link failure".

HResult 0x2746, Level 16, State 1

TCP Provider: An existing connection was forcibly closed by the remote host.

HResult 0x4818, Level 14, State 1

Login failed for user 'EPC'.

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

Any help will be greatly appreciated.

What selection are you using under the security option for the Linked Server setup in SQL Server Management?|||use logins' current security conttext. The user id and password are the same on both servers.|||

I started to get the same message as you, the first part that is: (I'm not using linked servers). So I don't know if this will help you or not.

Warning: Null value is eliminated by an aggregate or other SET operation.

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476

This site was helpful to me.

http://forums.databasejournal.com/archive/index.php/t-42981.html

Apparently, the problem is with the SET QUOTED_IDENTIFIER. I applied his logic to my script and now it seems to be working. I also needed to stop and start dbmail to release the queue. I don't know why but it gets clogged. I have begun to put the stop and start near the beginning of all my scripts.

--These two lines will release any emails "clogged" in SS queue

EXEC msdb.dbo.sysmail_stop_sp;

EXEC msdb.dbo.sysmail_start_sp;

--new logic begin

SETQUOTED_IDENTIFIEROFF

set @.myqry ='SET QUOTED_IDENTIFIER ON '

set @.myqry = @.myqry +char(13)

--new logic end

set @.myqry = @.myqry

+'

SELECT * FROM PPI_Reporting.DBRS.HpSyncCompareInvDate'

EXECUTE msdb.dbo.sp_send_dbmail @.profile_name=@.profile, @.recipients=@.recipients, @.importance='Normal'

,@.subject=@.subject, @.body=@.body

,@.query=@.myqry

GO

|||I don't know how it affects the login failure but it is a linked server afterall The sproc currently set nocount and ansi_warnings so we'll give quoted_identifiers a try. Thanks for the suggestion.|||

I'm sorry, but my solution only worked for about 10 times yesterday and then no longer works. I was excited to see it work after adding the SET QUOTED_IDENTIFIER statement so I posted here.

So I am back to square one. Let us know if you find a solution.

-Doug

|||See this http://sqlserver-qa.net/blogs/tools/archive/2007/04/20/tcp-provider-an-existing-connection-was-forcibly-closed-by-the-remote-host.aspx is any help.|||I appreciate the responses but none of them quite fit the issue. We found a workaround and have the sprocs running now. Rather than returning the output of the called sproc to the calling sproc, the data is inserted in a table and the calling sproc now queries the table.