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 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.