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.

Hi All,

I am researching database mirroring in sql server 2005.

I came across in multiple places the following paragraph...

" Microsoft support policies do not apply to the database mirroring feature in SQL Server 2005. Database mirroring is currently disabled by default, but may be enabled for evaluation purposes only by using trace flag 1400 as a startup parameter. (For more information about trace flags, see Trace Flags (Transact-SQL).) Database mirroring should not be used in production environments, and Microsoft support services will not support databases or applications that use database mirroring. Database mirroring documentation is included in SQL Server 2005 for evaluation purposes only, and the Documentation Policy for SQL Server 2005 Support and Upgrade does not apply to the database mirroring documentation. "

I would not be worried if i did not find this in real microsoft documentation and the BOL!!

Would somebody please verify or discredit these statements?

thanks!

Hi Shelly,

Database Mirroring is fully supported with SQL Server 2005 SP1. Before SP1 it was not supported.

So once you have installed SP1 for SQL 2005, you should be able to use database mirroring without trace falg 1400.

regards

Jag

|||Jag is correct. Database Mirroring is fully supported by Microsoft as of SP1. It works very well, in my experience.

Database Mirroring with multiple websites

Hi, it's real strange that there are currently no questions in the whole of the SQL Server section. That has to change!

Anyway, here's a scenario. There's a website we are developing, which we expect to have a high load. the site is being developed with ASP.NET 2 & SQL Server 2005 and will probably be complete some time early next year. However, we've already started thinking about deployment issues as these will greatly affect the development.

Because it will be accessed from a few places around the world, for performance, security and resilience, the suggestion is to:

deploy the ASP.NET website on difference servers around the world (3-5) deploy at least (3-5) SQL Servers (each ASP.NET website mirror would be attached to a local sqlserver). put all the servers into the same active directory. This will enable us to run the SQL Server in Windows Authentication mode and therefore reduce possible authentication attacks

Issue #2 has given us a few headaches. The aim was for each ASP.NET to be attached to a SQL Server - for performance sake. It is also critical to keep the data in the two servers synchronised. Initially, (SQL 2k) we thought of using log shipping. However now, we are considering database mirroring to achieve this (SQL 2k5). I've been doing some research to understand the process. It seems to me now as contained in the two links below that we cannot use database mirroring and at the same time maintain a link between the local ASPNET and its local database server because if the local database server is in fact a mirror, it will be inaccessible:

http://www.databasejournal.com/features/mssql/article.php/3440511
http://www.sql-server-performance.com/sql_server_high_availability.asp

There is a mention in these articles that we could use a database snapshot to get around this problem. This would mean however, that we would be unable to write to the local database (if it is in mirror mode), which is a requirement of the application (basically, we would want any database server to be able to write).

We do not think log-shipping would satisfy the requirement either though, because it is important to have guaranteed (SAFE mode) commitment of data to the database where an update or insert is performed.

Are there any solutions that anyone can think of around this (hopefully by using SQL 2K5)?

Thanks

Oz

You are correct. If you need to keep all the servers in "update" mode neither the log shipping nor database mirroring are good solutions. You should be using merge replication for your task. There is plenty information about it already in SQL Server 2000. One possible source to start with is at http://www.databasejournal.com/features/mssql/article.php/1438231.sql

Database Mirroring - Multiple Endpoints

Hi All
Is it possible to create multiple endpoints for Database Mirroring in SQL
Server 2005?
After reading that only 10 databases should be mirrored per endpoint I tried
to create another endpoint and received the following error:
An endpoint already exists with the bindings specified. Only one endpoint
supported for a specific binding. Use ALTER ENDPOINT or DROP the existing
endpoint and execute the CREATE ENDPOINT statement.
Can someone please clarify how to create an additional endpoint and/or how
to mirror more than 10 databases.
Thanks
Can you point me to the document which talks about 10 mirrored database per
endpoint.
I have more than 10 endpoints in my server.
Thank you
"David" <David@.discussions.microsoft.com> wrote in message
news:2303C6A1-5317-4BBB-B00A-EA8E48DDC7FC@.microsoft.com...
> Hi All
> Is it possible to create multiple endpoints for Database Mirroring in SQL
> Server 2005?
> After reading that only 10 databases should be mirrored per endpoint I
> tried
> to create another endpoint and received the following error:
> An endpoint already exists with the bindings specified. Only one endpoint
> supported for a specific binding. Use ALTER ENDPOINT or DROP the existing
> endpoint and execute the CREATE ENDPOINT statement.
> Can someone please clarify how to create an additional endpoint and/or how
> to mirror more than 10 databases.
> Thanks
|||Hi David, please take a look at the below link:
http://www.sqlcommunity.com/default.aspx?tabid=77&id=79
All mirroring connections on a server instance use a single database
mirroring endpoint.
There's no hard limit on ho many databases can be mirrored per server. This
depends on the resources available on the server. The best practice however
is to limit your SQL Server with 10 but this is not an official limit.
You may explore more about database mirroring @. Database Mirroring clinic on
www.sqlcommunity.com (Below is the link)
http://sqlcommunity.com/Articles/SQLClinic/DatabaseMirroringClinic/tabid/117/language/en-US/Default.aspx
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
"David" wrote:

> Hi All
> Is it possible to create multiple endpoints for Database Mirroring in SQL
> Server 2005?
> After reading that only 10 databases should be mirrored per endpoint I tried
> to create another endpoint and received the following error:
> An endpoint already exists with the bindings specified. Only one endpoint
> supported for a specific binding. Use ALTER ENDPOINT or DROP the existing
> endpoint and execute the CREATE ENDPOINT statement.
> Can someone please clarify how to create an additional endpoint and/or how
> to mirror more than 10 databases.
> Thanks
|||Hi Shailesh, there's no limit on how many databases you can mirror per
server. This depends on the availability of the resorces on the server.
However, the best practice hs been to limit your server with 10.
More info on database mirroring can be found at Database Mirroring Clinic on
www.sqlcommunity.com :
http://sqlcommunity.com/Articles/SQLClinic/DatabaseMirroringClinic/tabid/117/language/en-US/Default.aspx
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
"Shailesh Khanal" wrote:

> Can you point me to the document which talks about 10 mirrored database per
> endpoint.
> I have more than 10 endpoints in my server.
> Thank you
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:2303C6A1-5317-4BBB-B00A-EA8E48DDC7FC@.microsoft.com...
>
>

Database Mirroring - Multiple Endpoints

Hi All
Is it possible to create multiple endpoints for Database Mirroring in SQL
Server 2005?
After reading that only 10 databases should be mirrored per endpoint I tried
to create another endpoint and received the following error:
An endpoint already exists with the bindings specified. Only one endpoint
supported for a specific binding. Use ALTER ENDPOINT or DROP the existing
endpoint and execute the CREATE ENDPOINT statement.
Can someone please clarify how to create an additional endpoint and/or how
to mirror more than 10 databases.
ThanksCan you point me to the document which talks about 10 mirrored database per
endpoint.
I have more than 10 endpoints in my server.
Thank you
"David" <David@.discussions.microsoft.com> wrote in message
news:2303C6A1-5317-4BBB-B00A-EA8E48DDC7FC@.microsoft.com...
> Hi All
> Is it possible to create multiple endpoints for Database Mirroring in SQL
> Server 2005?
> After reading that only 10 databases should be mirrored per endpoint I
> tried
> to create another endpoint and received the following error:
> An endpoint already exists with the bindings specified. Only one endpoint
> supported for a specific binding. Use ALTER ENDPOINT or DROP the existing
> endpoint and execute the CREATE ENDPOINT statement.
> Can someone please clarify how to create an additional endpoint and/or how
> to mirror more than 10 databases.
> Thanks|||Hi David, please take a look at the below link:
http://www.sqlcommunity.com/default.aspx?tabid=77&id=79
All mirroring connections on a server instance use a single database
mirroring endpoint.
There's no hard limit on ho many databases can be mirrored per server. This
depends on the resources available on the server. The best practice however
is to limit your SQL Server with 10 but this is not an official limit.
You may explore more about database mirroring @. Database Mirroring clinic on
www.sqlcommunity.com (Below is the link)
http://sqlcommunity.com/Articles/SQLClinic/DatabaseMirroringClinic/tabid/117/language/en-US/Default.aspx
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
"David" wrote:
> Hi All
> Is it possible to create multiple endpoints for Database Mirroring in SQL
> Server 2005?
> After reading that only 10 databases should be mirrored per endpoint I tried
> to create another endpoint and received the following error:
> An endpoint already exists with the bindings specified. Only one endpoint
> supported for a specific binding. Use ALTER ENDPOINT or DROP the existing
> endpoint and execute the CREATE ENDPOINT statement.
> Can someone please clarify how to create an additional endpoint and/or how
> to mirror more than 10 databases.
> Thanks|||Hi Shailesh, there's no limit on how many databases you can mirror per
server. This depends on the availability of the resorces on the server.
However, the best practice hs been to limit your server with 10.
More info on database mirroring can be found at Database Mirroring Clinic on
www.sqlcommunity.com :
http://sqlcommunity.com/Articles/SQLClinic/DatabaseMirroringClinic/tabid/117/language/en-US/Default.aspx
--
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
"Shailesh Khanal" wrote:
> Can you point me to the document which talks about 10 mirrored database per
> endpoint.
> I have more than 10 endpoints in my server.
> Thank you
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:2303C6A1-5317-4BBB-B00A-EA8E48DDC7FC@.microsoft.com...
> > Hi All
> >
> > Is it possible to create multiple endpoints for Database Mirroring in SQL
> > Server 2005?
> >
> > After reading that only 10 databases should be mirrored per endpoint I
> > tried
> > to create another endpoint and received the following error:
> >
> > An endpoint already exists with the bindings specified. Only one endpoint
> > supported for a specific binding. Use ALTER ENDPOINT or DROP the existing
> > endpoint and execute the CREATE ENDPOINT statement.
> >
> > Can someone please clarify how to create an additional endpoint and/or how
> > to mirror more than 10 databases.
> >
> > Thanks
>
>

Database Mirroring - Multiple databases

I know database mirroirng is on the database level but is it possible to fail
all the databses in the server if one databses fails.
If I want to mirror db1 and db2 serverA to db1 and db2 in serverb. If db1 in
servera fails I want db2 in servera fails therefore both database will
rollover to serverb.No, first off database mirroring is not scalable to a large number of
databases. Secondly what will happen is each mirroring pair will failover on
its own schedule. All of them should fail over within several seconds
though.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"bashir" <bashir@.discussions.microsoft.com> wrote in message
news:1379FB0F-C815-4DEB-B0B0-CD25F03693ED@.microsoft.com...
>I know database mirroirng is on the database level but is it possible to
>fail
> all the databses in the server if one databses fails.
> If I want to mirror db1 and db2 serverA to db1 and db2 in serverb. If db1
> in
> servera fails I want db2 in servera fails therefore both database will
> rollover to serverb.
>

Database Mirroring - Multiple databases

I know database mirroirng is on the database level but is it possible to fai
l
all the databses in the server if one databses fails.
If I want to mirror db1 and DB2 serverA to db1 and DB2 in serverb. If db1 in
servera fails I want DB2 in servera fails therefore both database will
rollover to serverb.No, first off database mirroring is not scalable to a large number of
databases. Secondly what will happen is each mirroring pair will failover on
its own schedule. All of them should fail over within several seconds
though.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"bashir" <bashir@.discussions.microsoft.com> wrote in message
news:1379FB0F-C815-4DEB-B0B0-CD25F03693ED@.microsoft.com...
>I know database mirroirng is on the database level but is it possible to
>fail
> all the databses in the server if one databses fails.
> If I want to mirror db1 and DB2 serverA to db1 and DB2 in serverb. If db1
> in
> servera fails I want DB2 in servera fails therefore both database will
> rollover to serverb.
>sql

database mail to send mail to multiple recipient from table

I am using database mail to send emails to our Lotus Notes SMTP server using sp_send_dbmail. I want to accomplish the following.

I have maintained department-wise users email address in one table . Now I want to send mail to one particular department and there can be 1-15 users as recipient for that mail. How can I do that using sp_send_dbmail?

Well, I have found answer to it. The following way, we can accomplish. Hope that will help those, who are searching for something similar.

DECLARE @.email VARCHAR(4000)
SET @.email = ''
SELECT @.email = RTRIM(@.email) + RTRIM(email) + ';'
FROM Users
WHERE email <> '' AND DepCode = 'A'
PRINT @.email

EXECUTE msdb.dbo.sysmail_add_account_sp
@.account_name = 'custoerders',
@.description = 'Customer Address Account',
@.email_address = @.email
@.mailserver_name = 'mail.anywhere.com'

database mail to send mail to multiple recipient from table

I am using database mail to send emails to our Lotus Notes SMTP server using sp_send_dbmail. I want to accomplish the following.

I have maintained department-wise users email address in one table . Now I want to send mail to one particular department and there can be 1-15 users as recipient for that mail. How can I do that using sp_send_dbmail?

Well, I have found answer to it. The following way, we can accomplish. Hope that will help those, who are searching for something similar.

DECLARE @.email VARCHAR(4000)
SET @.email = ''
SELECT @.email = RTRIM(@.email) + RTRIM(email) + ';'
FROM Users
WHERE email <> '' AND DepCode = 'A'
PRINT @.email

EXECUTE msdb.dbo.sysmail_add_account_sp
@.account_name = 'custoerders',
@.description = 'Customer Address Account',
@.email_address = @.email
@.mailserver_name = 'mail.anywhere.com'

Database Logic

I am trying to figure out how to set up this database.

Basically, there are products with their associated fields. Each product can belong to multiple categories, and each category also has subcategories.
So far I have the following, but not sure if this is the best way to set it up...
TABLE Products:
product_id (int)(1-many relationship to product_id in Table Product_Category)
sku (int)
description
price

TABLE Category
category_id (int)(1-many relationship to category_id in Table Product_Category)
name

TABLE SubCategory
subcategory_id (int)(1-many relationship to subcategory_id in Table Product_Category)
category_id (int)
name

TABLE Product_Category
prodcat_id (int)
product_id (int)(many-1 relationship to product_id in Table Products)
category_id (int)(many-1 relationship to category_id in Table Category)
subcategory_id (int)(many-1 relationship to subcategory_id in Table SubCategory)

Thanks,
Mick

TABLE Products:
product_id (int)(1-many relationship to product_id in Table Product_Category)
sku (int)
description
price

TABLE Categories
category_id (int)(1-many relationship to category_id in Table Product_Category)
parent (int) (1-1 relationship with Category)
name

TABLE Product_Categories
prodcat_id (int)
product_id (int)(many-1 relationship to product_id in Table Products)
category_id (int)(many-1 relationship to category_id in Table Category)

Categories table would look like:

1,0,furnature

2,1,chairs

3,1,tables

4,0,automobiles

5,4,pickups

6,4,minivans

You can then also (If you need/want)

7,6,buick

Which would then be Category automobiles, Sub-Category minivans, Sub-Sub-Category buick