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 migration from SQL 2000 to SQL 2005

Hi folks,
Can somebody post a link which has information about migrating databases
from SQL Server 2000 (32 bit) to SQL Server 2005 64 bit. Your help is
greatly appreciated..
Thanks in advance,
Venkat
--
*** Sent via Developersdex http://www.examnotes.net ***these could be a start, even though it states 2000 64bit and not 2005...
http://www.microsoft.com/sql/soluti...ingto64bit.mspx
http://searchsqlserver.techtarget.c...tml?bucket=NEWS
Immy
Immy
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:uCauqdpmGHA.4536@.TK2MSFTNGP04.phx.gbl...
> Hi folks,
> Can somebody post a link which has information about migrating databases
> from SQL Server 2000 (32 bit) to SQL Server 2005 64 bit. Your help is
> greatly appreciated..
> Thanks in advance,
> Venkat
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||I am pretty sure that moving a 2000 32 bit database to 2005 64 bit is just a
matter of detaching the DB from 2000 server and attaching it to the 2005
server. 2005 64 bit supports this...
"Immy" wrote:

> these could be a start, even though it states 2000 64bit and not 2005...
> http://www.microsoft.com/sql/soluti...ingto64bit.mspx
> http://searchsqlserver.techtarget.c...tml?bucket=NEWS
> Immy
> Immy
> "Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
> news:uCauqdpmGHA.4536@.TK2MSFTNGP04.phx.gbl...
>
>

Database Mail

Dear Folks,
I am trying to finish my configuration of Database Mail on my sp1 build 2153 sql server. I believe to have set it up properly but i still can't get a test message through. My error message reads:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2006-08-09T15:14:00). Exception Message: Cannot send mails to mail server. (Transaction failed. The server response was: Mail from <account email address> rejected for policy reasons.). )

Everything that needs to be enabled, i.e. service broker, SQL Server Agent properties, deafult profile in msdb table, services restarted. Is this message a result of Sql server policy or the smtp server? Is there a process in the transaction Sql server initiates that could be causing the policy violation that i need to tell the email admin about?
Thanks for your reply.
-C
This turned out to be a DNS or relay issue. When i got the right IP address, it worked fine. I tried IP previously, the same message came back. The mail server is in another domain, which complicated the issue.