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